Splitting sqlite files for syncing

Question

I am writing an Android app that collects data from sensors and stores it in a local SQLite database.

What I'd like to do is sync this data (read: upload) with a backend application (I am using Android's Sync Adapter for this).

Since the data can be acquired during the sync process, I think it is reasonable to set the maximum size for the sqlite file to say 700 Kb (doesn't really matter), so the sync adapter will synchronise those 700 Kb files (via POST request) excluding the active one. And once the sqlite file reaches the limit, I shall create a new active sqlite db and write to it.

How could this be implemented? Perhaps, there is a better solution?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Eventually I decided to have just one database file with a 'synced_at' field.
I also used WAL for reading data (to be synced) while writing new data to db.
MySQLiteHelper class is implemented using Singleton pattern, so that you access your db concurrently in my case from the Main activity and the Sync adapter.
I also zip my data before sending it to the server (for text I achieve a 5x compression using gzip).

Data pojo:

@AllArgsConstructor
@NoArgsConstructor
@Builder
@Getter
@Setter
public class Data {
    private long id;
    private float a;
    private float b;
    private float c;
    private long timestamp;
    private long synced_at;
}

MySQLiteHelper:

public class MySQLiteHelper extends SQLiteOpenHelper {

    private static MySQLiteHelper sInstance;

    public static final String DB_NAME = "db.sqlite";
    public static final int DB_VERSION = 1;
    public static final String TABLE_NAME = "data";

    public static synchronized MySQLiteHelper getInstance(Context context) {
        if (sInstance == null) {
            sInstance = new MySQLiteHelper(context.getApplicationContext());
        }
        return sInstance;
    }

    private MySQLiteHelper(Context context) {
        super(context, context.getExternalFilesDir(null).getAbsolutePath() + "/" + DB_NAME,
                null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String createDbSql = "...";
        sqLiteDatabase.execSQL(createDbSql);
        Log.d("log", "db created");
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        Log.d("log", "db opened");
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        Log.d("log", "db upgraded");
    }
}

Splitting sqlite files for syncing

Sign up