android - 我想删除数据，直到数据库达到特定大小

Question

我正在尝试删除 Room 数据库中的数据，直到它达到特定大小。

例如，数据库大小为25MB，我想删除一些数据直到它的大小为20MB。所以我在 for 循环中删除了一些数据，但数据库大小没有减少......

我的代码是

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);

我也尝试保存 VACUUM，但它不起作用。

---

致 future 的读者

关闭和重新打开可能会产生意想不到的问题，所以我找到了另一种方法来使用 WAL 检查点获取准确的数据库文件大小。

@RawQuery
public int walCheckPoint(SupportSQLiteQuery supportSQLiteQuery);

dao.walCheckPoint(new SimpleSQLiteQuery("pragma wal_checkpoint(full)"));

在你检查数据库大小之前，这个查询强制检查点 wal 文件。然后你可以获得适当的数据库大小并且不再需要关闭它。

Answer 1

我认为您的核心问题是您没有检查点(将更改应用到数据库文件)，因此更改保存在等待应用的 -wal 文件中，因此数据库文件大小将是不变(如果没有自动检查点)。

关闭数据库将检查文件并应用删除。或者，您可以禁用 WriteAheadLogging，因为日志模式以相反的方式工作(更改应用于数据库文件和对数据库文件所做更改的记录(日志)将写入日志文件)。

例子

也许考虑以下(基于可以收集到的代码):-

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);
    }
}

• 注意 包含可以利用 VACUUM INTO 的代码(注释掉)但这需要 SQLite 3.27.0，目前还不适用于 Android。
• 注意关闭/重建数据库的次数可能过大，可以进行更改以提高效率。

结果:-

简而言之:-

• 启动时数据库大小为 974848(小于 1MB，MAX_DB_SIZE)。
• 添加了 10000 行，数据库增长到 2347008(超过限制)。
• 删除是逐步进行的(每次迭代删除大约五分之一的现有行)，最后数据库在 909312 低于 1Mb(40 次迭代后)，
• 然后执行 VACUUM，将大小减小到 880640。

运行日志示例:-

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