I'm trying to delete data in Room database until it's a specific size.
For example, the database size is 25MB and I want to delete some data until its size is 20MB. So I delete some data in for loop but the DB size is not reduced...
My code is
if (size > MAX_DB_SIZE) {
for(int j = 0; j < 4; j++){
switch(j) {
case 0:
dbErase(begin, 3, true);
break;
case 1:
dbErase(begin, 3, false);
break;
case 2:
dbErase(begin, 2, true);
break;
case 3:
dbErase(begin, 2, false);
break;
default:
break;
}
vacuumDb(new SimpleSQLiteQuery("VACUUM"));
Log.d(TAG, "size " + j + " : " + size);
File file2 = new File(db_path);
size = file2.length();
if(size == 0L || ((double) size / MAX_DB_SIZE) <= 0.8) {
break;
}
}
}
private void dbErase(Patch begin, int offset, boolean isLat){
...
...
if(offset == 0) {
} else if (isLat) {
//delete Lon range
if (supCluLon + offset > 2000) {
deletePatchByBetweenLonIndex(patchMin, patchMax);
} else if (supCluLon - offset < 0) {
deletePatchByBetweenLonIndex(patchMin, patchMax);
} else {
deletePatchByOrLonIndex(patchMin, patchMax);
}
} else {
deletePatchByOrLatIndex(patchMin, patchMax);
}
}
@DAO
@Query("DELETE FROM Patch WHERE patchLonIndex BETWEEN :patchMin AND :patchMax")
abstract void deletePatchByBetweenLonIndex(int patchMin, int patchMax);
@Query("DELETE FROM Patch WHERE patchLonIndex <= :patchMin OR patchLonIndex >= :patchMax")
abstract void deletePatchByOrLonIndex(int patchMin, int patchMax);
@Query("DELETE FROM Patch WHERE patchLatIndex <= :patchMin OR patchLatIndex >= :patchMax")
abstract void deletePatchByOrLatIndex(int patchMin, int patchMax);
Also I tried to save VACUUM but it doesn't work.
Closing and Re-opening can make unexpected problems so I found the other way to get exact Database file size with WAL check point.
@RawQuery
public int walCheckPoint(SupportSQLiteQuery supportSQLiteQuery);
dao.walCheckPoint(new SimpleSQLiteQuery("pragma wal_checkpoint(full)"));
Before u check the database size, this query force checkpoint wal file. Then u can get proper database size and don't need to close it anymore.
I believe that your core issue is that you are not check-pointing (applying the changes to the database file) and therefore that the changes are held in the -wal file awaiting to be applied and thus the database file size will be unchanged (if not auto-checkpointed).
Closing the database will checkpoint the file and apply the deletions. Alternately you could disable WriteAheadLogging as Journal mode works the opposite way around (the changes are applied to the database file and a record(log) of those changes made to the database file are written to the journal file).
Perhaps consider the following (which is based upon what can be gleaned your code) :-
public class MainActivity extends AppCompatActivity {
PatchDatabase mDB;
PatchDao mPatchDao;
private final long MAX_DB_SIZE = 1024 * 1024 * 1;
private final String patchdata = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" +
"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"; // make rows take up some space
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDB = Room.databaseBuilder(this,PatchDatabase.class,PatchDatabase.DBNAME)
.allowMainThreadQueries()
.build();
mPatchDao = mDB.patchDao();
resizeDB();
addSomeData(10000);
resizeDB(100,20); // Delete 1 5th of rows
}
private void addSomeData(int numberToAdd) {
final String TAG = "ADDSOMEDATA";
Log.d(TAG, "Row count before Add is " + mPatchDao.getRowCount());
long currentLongitude = 0;
long currentLatitude = 0;
if (mPatchDao.getRowCount() > 0) {
currentLatitude = mPatchDao.getMaxLat() + 1;
currentLatitude = mPatchDao.getMaxLat() + 1;
}
List<Patch> patches = new ArrayList<>();
for (int i = 0; i < numberToAdd; i++) {
patches.add(new Patch(currentLongitude++, currentLatitude++, patchdata));
}
for (Patch p: patches) {
mPatchDao.addPatch(p);
}
}
private long getDBSize() {
mDB.close();
long dbsize = 0;
File db = new File(this.getDatabasePath(PatchDatabase.DBNAME).getPath());
if (db.exists()) {
dbsize = db.length();
}
Log.d("DBSIZE","Database Size was " + String.valueOf(dbsize));
mDB = Room.databaseBuilder(this,PatchDatabase.class,PatchDatabase.DBNAME)
.allowMainThreadQueries()
.build();
return dbsize;
}
private void resizeDB(int attemptLimit, long factor) {
final String TAG = "RESIZE";
long dbsize = getDBSize();
Log.d(TAG,"Rowcount before deletions is " + mPatchDao.getRowCount());
if (dbsize < MAX_DB_SIZE) return;
int attempts = 0;
while (getDBSize() > MAX_DB_SIZE && attempts++ < attemptLimit) {
long minLon = mPatchDao.getMinLon();
long maxLon = mPatchDao.getMaxLon();
long upperRange = ((maxLon - minLon) / factor) + minLon;
Log.d(TAG,"Deleting rows from " + minLon + " to " + upperRange + " thus approx " + (upperRange - minLon) + " rows. Attempt is " + attempts + " out of " + attemptLimit);
Log.d(TAG, mPatchDao.deletePatchByBetweenLonIndex(minLon,upperRange) + " rows deleted.");
}
Log.d(TAG,"Rowcount after deletions is " + mPatchDao.getRowCount());
reSizeDBFile();
//mDB.close();
//File db = new File(this.getDatabasePath(PatchDatabase.DBNAME).getPath());
}
private void reSizeDBFile() {
final String tempPrefix = "temp_";
final String renamedPrefix = "renamed_";
final String TAG = "RSZDBFILE";
long endSize = 0;
Log.d(TAG,"Starting DB FILE RESIZE (Closing Database to ROOM)");
mDB.close(); //<<<<< Close should checkpoint and thus apply deletions
File originalDBFile = new File(this.getDatabasePath(PatchDatabase.DBNAME).getPath());
Log.d(TAG,"File Size before VACUUM is " + originalDBFile.length());
//File tempDBFile = new File(originalDBFile.getParent() + File.separator + tempPrefix + PatchDatabase.DBNAME);
//File renamedOriginal = new File(originalDBFile.getParent() + File.separator + renamedPrefix + PatchDatabase.DBNAME);
SQLiteDatabase db = SQLiteDatabase.openDatabase(originalDBFile.getPath(),null,SQLiteDatabase.OPEN_READWRITE);
Log.d(TAG,"Performing VACUUM. Original DB File Size is " + originalDBFile.length());
//db.execSQL("VACUUM INTO '" + tempDBFile.getPath() + "'");
db.execSQL("VACUUM");
db.close();
Log.d(TAG,"File Size after VACUUM is " + originalDBFile.length());
/* Can't use VACUUM INTO as not yet introduced ELSE :-
if (tempDBFile.exists()) {
endSize = tempDBFile.length();
Log.d(TAG,"TEMP FILE EXISTS AFTER VACUUM. SIZE is " + endSize);
if (tempDBFile.length() > 1024 * 4 && tempDBFile.length() < originalDBFile.length()) {
originalDBFile.renameTo(renamedOriginal);
tempDBFile.renameTo(originalDBFile);
if (originalDBFile.length() == endSize) {
renamedOriginal.delete();
}
}
} else {
Log.d(TAG,"TEMP FILE NOT FOUND?????????");
}
*/
mDB = Room.databaseBuilder(this,PatchDatabase.class,PatchDatabase.DBNAME)
.allowMainThreadQueries()
.build();
}
private void resizeDB(long factor) {
resizeDB(10,factor);
}
private void resizeDB() {
resizeDB(10,100);
}
}
In short :-
An example log for a run being :-
2019-09-26 08:37:17.610 D/DBSIZE: Database Size was 974848
2019-09-26 08:37:17.638 D/RESIZE: Rowcount before deletions is 7100
2019-09-26 08:37:17.639 D/ADDSOMEDATA: Row count before Add is 7100
2019-09-26 08:37:29.367 D/DBSIZE: Database Size was 2347008
2019-09-26 08:37:29.369 D/RESIZE: Rowcount before deletions is 17100
2019-09-26 08:37:29.369 D/DBSIZE: Database Size was 2347008
2019-09-26 08:37:29.370 D/RESIZE: Deleting rows from 0 to 499 thus approx 499 rows. Attempt is 1 out of 100
2019-09-26 08:37:29.371 D/RESIZE: 500 rows deleted.
2019-09-26 08:37:29.371 D/DBSIZE: Database Size was 2347008
2019-09-26 08:37:29.373 D/RESIZE: Deleting rows from 500 to 974 thus approx 474 rows. Attempt is 2 out of 100
2019-09-26 08:37:29.382 D/RESIZE: 475 rows deleted.
2019-09-26 08:37:29.382 D/DBSIZE: Database Size was 2228224
2019-09-26 08:37:29.384 D/RESIZE: Deleting rows from 975 to 1426 thus approx 451 rows. Attempt is 3 out of 100
2019-09-26 08:37:29.386 D/RESIZE: 452 rows deleted.
2019-09-26 08:37:29.386 D/DBSIZE: Database Size was 2228224
2019-09-26 08:37:29.387 D/RESIZE: Deleting rows from 1427 to 1855 thus approx 428 rows. Attempt is 4 out of 100
2019-09-26 08:37:29.389 D/RESIZE: 429 rows deleted.
2019-09-26 08:37:29.389 D/DBSIZE: Database Size was 2228224
2019-09-26 08:37:29.391 D/RESIZE: Deleting rows from 1856 to 2263 thus approx 407 rows. Attempt is 5 out of 100
2019-09-26 08:37:29.392 D/RESIZE: 408 rows deleted.
2019-09-26 08:37:29.392 D/DBSIZE: Database Size was 2228224
2019-09-26 08:37:29.394 D/RESIZE: Deleting rows from 2264 to 2650 thus approx 386 rows. Attempt is 6 out of 100
2019-09-26 08:37:29.395 D/RESIZE: 387 rows deleted.
2019-09-26 08:37:29.395 D/DBSIZE: Database Size was 2228224
2019-09-26 08:37:29.396 D/RESIZE: Deleting rows from 2651 to 3018 thus approx 367 rows. Attempt is 7 out of 100
2019-09-26 08:37:29.406 D/RESIZE: 368 rows deleted.
2019-09-26 08:37:29.406 D/DBSIZE: Database Size was 1953792
2019-09-26 08:37:29.407 D/RESIZE: Deleting rows from 3019 to 3368 thus approx 349 rows. Attempt is 8 out of 100
2019-09-26 08:37:29.409 D/RESIZE: 350 rows deleted.
2019-09-26 08:37:29.409 D/DBSIZE: Database Size was 1953792
2019-09-26 08:37:29.411 D/RESIZE: Deleting rows from 3369 to 3700 thus approx 331 rows. Attempt is 9 out of 100
2019-09-26 08:37:29.411 D/RESIZE: 332 rows deleted.
2019-09-26 08:37:29.411 D/DBSIZE: Database Size was 1953792
2019-09-26 08:37:29.413 D/RESIZE: Deleting rows from 3701 to 4015 thus approx 314 rows. Attempt is 10 out of 100
2019-09-26 08:37:29.414 D/RESIZE: 315 rows deleted.
2019-09-26 08:37:29.414 D/DBSIZE: Database Size was 1953792
2019-09-26 08:37:29.415 D/RESIZE: Deleting rows from 4016 to 4315 thus approx 299 rows. Attempt is 11 out of 100
2019-09-26 08:37:29.415 D/RESIZE: 300 rows deleted.
2019-09-26 08:37:29.416 D/DBSIZE: Database Size was 1953792
2019-09-26 08:37:29.417 D/RESIZE: Deleting rows from 4316 to 4600 thus approx 284 rows. Attempt is 12 out of 100
2019-09-26 08:37:29.418 D/RESIZE: 285 rows deleted.
2019-09-26 08:37:29.418 D/DBSIZE: Database Size was 1953792
2019-09-26 08:37:29.419 D/RESIZE: Deleting rows from 4601 to 4870 thus approx 269 rows. Attempt is 13 out of 100
2019-09-26 08:37:29.429 D/RESIZE: 270 rows deleted.
2019-09-26 08:37:29.429 D/DBSIZE: Database Size was 1699840
2019-09-26 08:37:29.430 D/RESIZE: Deleting rows from 4871 to 5127 thus approx 256 rows. Attempt is 14 out of 100
2019-09-26 08:37:29.432 D/RESIZE: 257 rows deleted.
2019-09-26 08:37:29.432 D/DBSIZE: Database Size was 1699840
2019-09-26 08:37:29.437 D/RESIZE: Deleting rows from 5128 to 5371 thus approx 243 rows. Attempt is 15 out of 100
2019-09-26 08:37:29.438 D/RESIZE: 244 rows deleted.
2019-09-26 08:37:29.438 D/DBSIZE: Database Size was 1699840
2019-09-26 08:37:29.440 D/RESIZE: Deleting rows from 5372 to 5603 thus approx 231 rows. Attempt is 16 out of 100
2019-09-26 08:37:29.441 D/RESIZE: 232 rows deleted.
2019-09-26 08:37:29.441 D/DBSIZE: Database Size was 1699840
2019-09-26 08:37:29.444 D/RESIZE: Deleting rows from 5604 to 5823 thus approx 219 rows. Attempt is 17 out of 100
2019-09-26 08:37:29.445 D/RESIZE: 220 rows deleted.
2019-09-26 08:37:29.445 D/DBSIZE: Database Size was 1699840
2019-09-26 08:37:29.447 D/RESIZE: Deleting rows from 5824 to 6032 thus approx 208 rows. Attempt is 18 out of 100
2019-09-26 08:37:29.447 D/RESIZE: 209 rows deleted.
2019-09-26 08:37:29.448 D/DBSIZE: Database Size was 1699840
2019-09-26 08:37:29.450 D/RESIZE: Deleting rows from 6033 to 6231 thus approx 198 rows. Attempt is 19 out of 100
2019-09-26 08:37:29.451 D/RESIZE: 199 rows deleted.
2019-09-26 08:37:29.451 D/DBSIZE: Database Size was 1699840
2019-09-26 08:37:29.453 D/RESIZE: Deleting rows from 6232 to 6420 thus approx 188 rows. Attempt is 20 out of 100
2019-09-26 08:37:29.454 D/RESIZE: 189 rows deleted.
2019-09-26 08:37:29.454 D/DBSIZE: Database Size was 1699840
2019-09-26 08:37:29.457 D/RESIZE: Deleting rows from 6421 to 6599 thus approx 178 rows. Attempt is 21 out of 100
2019-09-26 08:37:29.457 D/RESIZE: 179 rows deleted.
2019-09-26 08:37:29.458 D/DBSIZE: Database Size was 1699840
2019-09-26 08:37:29.458 D/RESIZE: Deleting rows from 6600 to 6769 thus approx 169 rows. Attempt is 22 out of 100
2019-09-26 08:37:29.466 D/RESIZE: 170 rows deleted.
2019-09-26 08:37:29.466 D/DBSIZE: Database Size was 1433600
2019-09-26 08:37:29.467 D/RESIZE: Deleting rows from 6770 to 6931 thus approx 161 rows. Attempt is 23 out of 100
2019-09-26 08:37:29.469 D/RESIZE: 162 rows deleted.
2019-09-26 08:37:29.469 D/DBSIZE: Database Size was 1433600
2019-09-26 08:37:29.470 D/RESIZE: Deleting rows from 6932 to 7085 thus approx 153 rows. Attempt is 24 out of 100
2019-09-26 08:37:29.471 D/RESIZE: 154 rows deleted.
2019-09-26 08:37:29.471 D/DBSIZE: Database Size was 1433600
2019-09-26 08:37:29.472 D/RESIZE: Deleting rows from 7086 to 7231 thus approx 145 rows. Attempt is 25 out of 100
2019-09-26 08:37:29.473 D/RESIZE: 146 rows deleted.
2019-09-26 08:37:29.474 D/DBSIZE: Database Size was 1433600
2019-09-26 08:37:29.476 D/RESIZE: Deleting rows from 7232 to 7370 thus approx 138 rows. Attempt is 26 out of 100
2019-09-26 08:37:29.477 D/RESIZE: 139 rows deleted.
2019-09-26 08:37:29.478 D/DBSIZE: Database Size was 1433600
2019-09-26 08:37:29.479 D/RESIZE: Deleting rows from 7371 to 7502 thus approx 131 rows. Attempt is 27 out of 100
2019-09-26 08:37:29.480 D/RESIZE: 132 rows deleted.
2019-09-26 08:37:29.480 D/DBSIZE: Database Size was 1433600
2019-09-26 08:37:29.481 D/RESIZE: Deleting rows from 7503 to 7627 thus approx 124 rows. Attempt is 28 out of 100
2019-09-26 08:37:29.482 D/RESIZE: 125 rows deleted.
2019-09-26 08:37:29.482 D/DBSIZE: Database Size was 1433600
2019-09-26 08:37:29.484 D/RESIZE: Deleting rows from 7628 to 7746 thus approx 118 rows. Attempt is 29 out of 100
2019-09-26 08:37:29.485 D/RESIZE: 119 rows deleted.
2019-09-26 08:37:29.485 D/DBSIZE: Database Size was 1433600
2019-09-26 08:37:29.489 D/RESIZE: Deleting rows from 7747 to 7859 thus approx 112 rows. Attempt is 30 out of 100
2019-09-26 08:37:29.490 D/RESIZE: 113 rows deleted.
2019-09-26 08:37:29.490 D/DBSIZE: Database Size was 1433600
2019-09-26 08:37:29.492 D/RESIZE: Deleting rows from 7860 to 7966 thus approx 106 rows. Attempt is 31 out of 100
2019-09-26 08:37:29.500 D/RESIZE: 107 rows deleted.
2019-09-26 08:37:29.500 D/DBSIZE: Database Size was 1273856
2019-09-26 08:37:29.502 D/RESIZE: Deleting rows from 7967 to 8068 thus approx 101 rows. Attempt is 32 out of 100
2019-09-26 08:37:29.504 D/RESIZE: 102 rows deleted.
2019-09-26 08:37:29.504 D/DBSIZE: Database Size was 1273856
2019-09-26 08:37:29.505 D/RESIZE: Deleting rows from 8069 to 8165 thus approx 96 rows. Attempt is 33 out of 100
2019-09-26 08:37:29.506 D/RESIZE: 97 rows deleted.
2019-09-26 08:37:29.506 D/DBSIZE: Database Size was 1273856
2019-09-26 08:37:29.508 D/RESIZE: Deleting rows from 8166 to 8257 thus approx 91 rows. Attempt is 34 out of 100
2019-09-26 08:37:29.509 D/RESIZE: 224 rows deleted.
2019-09-26 08:37:29.509 D/DBSIZE: Database Size was 1273856
2019-09-26 08:37:29.510 D/RESIZE: Deleting rows from 8258 to 8345 thus approx 87 rows. Attempt is 35 out of 100
2019-09-26 08:37:29.511 D/RESIZE: 440 rows deleted.
2019-09-26 08:37:29.511 D/DBSIZE: Database Size was 1273856
2019-09-26 08:37:29.513 D/RESIZE: Deleting rows from 8346 to 8428 thus approx 82 rows. Attempt is 36 out of 100
2019-09-26 08:37:29.522 D/RESIZE: 415 rows deleted.
2019-09-26 08:37:29.522 D/DBSIZE: Database Size was 1110016
2019-09-26 08:37:29.523 D/RESIZE: Deleting rows from 8429 to 8507 thus approx 78 rows. Attempt is 37 out of 100
2019-09-26 08:37:29.525 D/RESIZE: 395 rows deleted.
2019-09-26 08:37:29.526 D/DBSIZE: Database Size was 1110016
2019-09-26 08:37:29.528 D/RESIZE: Deleting rows from 8508 to 8582 thus approx 74 rows. Attempt is 38 out of 100
2019-09-26 08:37:29.529 D/RESIZE: 375 rows deleted.
2019-09-26 08:37:29.529 D/DBSIZE: Database Size was 1110016
2019-09-26 08:37:29.532 D/RESIZE: Deleting rows from 8583 to 8653 thus approx 70 rows. Attempt is 39 out of 100
2019-09-26 08:37:29.533 D/RESIZE: 355 rows deleted.
2019-09-26 08:37:29.533 D/DBSIZE: Database Size was 1110016
2019-09-26 08:37:29.534 D/RESIZE: Deleting rows from 8654 to 8721 thus approx 67 rows. Attempt is 40 out of 100
2019-09-26 08:37:29.543 D/RESIZE: 340 rows deleted.
2019-09-26 08:37:29.544 D/DBSIZE: Database Size was 909312
2019-09-26 08:37:29.545 D/RESIZE: Rowcount after deletions is 6390
2019-09-26 08:37:29.545 D/RSZDBFILE: Starting DB FILE RESIZE (Closing Database to ROOM)
2019-09-26 08:37:29.545 D/RSZDBFILE: File Size before VACUUM is 909312
2019-09-26 08:37:29.546 D/RSZDBFILE: Performing VACUUM. Original DB File Size is 909312
2019-09-26 08:37:29.568 D/RSZDBFILE: File Size after VACUUM is 880640