How to set data size limit of sqlite database in android?

Question

I am developing an android application in which I want to store limit data according to some size (i.e. 5mb). If data-size in database increased from the limit it shows some message.

I found this solution "File f = context.getDatabasePath(dbName); long dbSize = f.length();" from the source: How to get the current sqlite database size or package size in Android?

but it didn't work for me. It gives the same result after adding the more that in database.

Answer 1

When a database is created blocks of data (pages) are assigned to the various entities (tables being an entity). Pages can store numerous rows per table as such adding a row may simply place that row into an existing page and often will.

The database file size will only grow, when inserting data, when an additional page or pages are required.

It would be quite difficult to exactly know when a size limit was reached.

However the following is an example where the limit of 5Mb is not exceeded.

This basically checks the file size before inserting a row, noting that as the example inserts in batches of 1000 rows at a time within a transaction, then a number of pages are added per batch and hence the reduction by 4 pages 5MB (another 100 byte reduction for the database header). Using this the database will not exceed 5Mb.

The DatabaseHelper DatabaseHelper.java :-

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "mydb";
    public static final int DBVERSION = 1;
    public static final long DBHEADERSIZE = 100;
    public static final long MAXDBSIZE = 5 * 1024 * 1024 ; // 5MB

    public static final String TBL_MYTABLE = "mytable";
    public static final String COL_MYTABLE_ID = BaseColumns._ID;
    public static final String COl_MYTABLE_MYDATA = "mydata";

    private static final String crt_mytable_sql = "CREATE TABLE IF NOT EXISTS " + TBL_MYTABLE + "(" +
            COL_MYTABLE_ID + " INTEGER PRIMARY KEY, " +
            COl_MYTABLE_MYDATA + " TEXT" +
            ")";

    SQLiteDatabase mDB;
    File mDBFile;
    Context mContext;
    private static int mPageSize;

    public DatabaseHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();
        mContext = context;
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        mPageSize = getDBPageSize(db);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(crt_mytable_sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insertRowIntoMyTable(String mydata){
        long rv;
        if (!isDBFull()) {
            ContentValues cv = new ContentValues();
            cv.put(COl_MYTABLE_MYDATA,mydata);
            rv = mDB.insert(TBL_MYTABLE,null,cv);
        } else {
            rv = -999;
        }
        return rv;
    }

    private int getDBPageSize(SQLiteDatabase db) {
        int rv = 0;
        Cursor csr = db.rawQuery("PRAGMA page_size",null);
        if (csr.moveToFirst()) {
            rv = csr.getInt(csr.getColumnIndex("page_size"));
        }
        csr.close();
        return rv;
    }

    private long getDBFileSize() {
        if (mDBFile == null) {
            mDBFile = new File(mContext.getDatabasePath(DBNAME).toString());
        }
        return mDBFile.length();
    }

    public long getFileSize() {
        return mDBFile.length() ;
    }

    public boolean isDBFull() {
        long fsz = getDBFileSize();
        return (fsz >= MAXDBSIZE - (mPageSize * 4) - DBHEADERSIZE);
    }
}

The above was tested using the following MainActivity.java :-

public class MainActivity extends AppCompatActivity {

    DatabaseHelper mDBHlpr;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDBHlpr = new DatabaseHelper(this);
        long id = 0;
        for (int i1=0; i1 < 1000000; i1++) {
            mDBHlpr.getWritableDatabase().beginTransaction();
            Log.d("DBINSERT","Inserting batch " + String.valueOf(i1) + " of 1000 rows");
            for (int i2=0; i2 < 1000; i2++) {
                id = mDBHlpr.insertRowIntoMyTable("MYDATA_I1" + String.valueOf(i1) + "_I2" + String.valueOf(i2));
                if (id <= -999) {
                    long current_row = ((long) i2 + ((long) i1) * 1000);
                    Log.d("DBLIMITREACHED","Database limit reached when processing " + String.valueOf(current_row));
                    break;
                }
            }
            if (id > -999) {
                Log.d("DBCOMMIT","Committing batch");
                mDBHlpr.getWritableDatabase().setTransactionSuccessful();
            }
            mDBHlpr.getWritableDatabase().endTransaction();
            if (id <= -999) {
                break;
            }
        }
        Log.d("DBSIZEINFO","Database Full = " + String.valueOf(mDBHlpr.isDBFull()) + " Size is " + mDBHlpr.getFileSize());
    }

• 1000000 batches of 1000 rows is well over the 197 batches needed to hit the 5MB limit.

When run the log includes :-

07-24 12:19:54.288 D/DBINSERT: Inserting batch 197 of 1000 rows
07-24 12:19:54.288 D/DBLIMITREACHED: Database limit reached when processing 197000
07-24 12:19:54.288 D/DBSIZEINFO: Database Full = true Size is 5226496

i.e. 5226496 is slightly less than 5MB (4.98MB).

Device Explorer shows :-