I'm in the progress of moving my Sqlite DB in my Android app to a Room implementation. On first setup the database needs to be populated from a CSV shipped with the apk (this can't be changed). This can take up to 30 seconds during which I display a progress bar to the user and block them from proceeding. I find this a better experience for the user then them waiting for data to appear on first query.
In my current implementation I subclass SQLiteOpenHelper, providing a Handler field that I can set before calling getReadableDatabase (which has always seemed a bit of a hack). Progress can then be returned to the calling Handler. However in Room implementation the database is initialised by a callback on the Dagger2 provided Room database. Question is how can I still show a progress dialog to the user during this setup?
DatabaseModule
package uk.colessoft.android.hilllist.modules;
import android.app.Application;
import android.arch.persistence.db.SupportSQLiteDatabase;
import android.arch.persistence.db.SupportSQLiteOpenHelper;
import android.arch.persistence.room.Room;
import android.arch.persistence.room.RoomDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.NonNull;
import javax.inject.Singleton;
import dagger.Binds;
import dagger.Module;
import dagger.Provides;
import uk.colessoft.android.hilllist.dao.HillDao;
import uk.colessoft.android.hilllist.dao.HillTypeDao;
import uk.colessoft.android.hilllist.dao.TypeLinkDao;
import uk.colessoft.android.hilllist.database.BaggingTable;
import uk.colessoft.android.hilllist.database.BritishHillsDatasource;
import uk.colessoft.android.hilllist.database.HillsDatabase;
import uk.colessoft.android.hilllist.database.HillsDatabaseHelper;
import uk.colessoft.android.hilllist.database.HillsLocalDatasource;
import uk.colessoft.android.hilllist.database.HillsTables;
import static android.arch.persistence.room.RoomDatabase.JournalMode.TRUNCATE;
@Module
public class DatabaseModule {
@Provides
@Singleton
HillsDatabase providesRoomDatabase(Application application) {
return Room.databaseBuilder(application, HillsDatabase.class, "hill-list.db").addMigrations(HillsDatabase.MIGRATION_2_3)
.addCallback(new RoomDatabase.Callback() {
@Override
public void onCreate(@NonNull SupportSQLiteDatabase db) {
HillsTables.onCreate(db, application);//, handler, 9999999);
}
}).setJournalMode(TRUNCATE).build();
}
@Provides
@Singleton
HillDao providesHillDao(HillsDatabase database) {
return database.hillDao();
}
@Provides
@Singleton
HillTypeDao providesHillTypeDao(HillsDatabase database) {
return database.hillTypeDao();
}
@Provides
@Singleton
TypeLinkDao providesTypeLinkDao(HillsDatabase database) {
return database.typeLinkDao();
}
}
HillsTables
package uk.colessoft.android.hilllist.database;
import android.arch.persistence.db.SupportSQLiteDatabase;
import android.arch.persistence.db.SupportSQLiteStatement;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.os.Handler;
import android.os.Message;
import android.util.Log;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import static android.content.ContentValues.TAG;
public class HillsTables {
private static final String HILLS_CSV = "DoBIH_v15.5.csv";
public static final String KEY_TITLE = "title";
public static final String HILLS_TABLE = "hills";
public static final String HILLTYPES_TABLE = "hilltypes";
public static final String TYPES_LINK_TABLE = "typeslink";
public static final String KEY_ID = "_id";
public static final String KEY_HILLNAME = "Name";
public static final String KEY_SECTION = "Section";
public static final String KEY_SECTIONNAME = "Section name";
public static final String KEY_AREA = "Area";
public static final String KEY_CLASSIFICATION = "Classification";
public static final String KEY_MAP = "Map 1:50k";
public static final String KEY_MAP25 = "Map 1:25k";
public static final String KEY_HEIGHTM = "Metres";
public static final String KEY_HEIGHTF = "Feet";
public static final String KEY_GRIDREF = "Grid ref";
public static final String KEY_GRIDREF10 = "Grid ref 10";
public static final String KEY_DROP = "Drop";
public static final String KEY_COLGRIDREF = "Col grid ref";
public static final String KEY_COLHEIGHT = "Col height";
public static final String KEY_FEATURE = "Feature";
public static final String KEY_OBSERVATIONS = "Observations";
public static final String KEY_SURVEY = "Survey";
public static final String KEY_CLIMBED = "Climbed";
public static final String KEY_REVISION = "Revision";
public static final String KEY_COMMENTS = "Comments";
public static final String KEY_STREETMAP = "Streetmap/OSiViewer";
public static final String KEY_GEOGRAPH = "Geograph/MountainViews";
public static final String KEY_HILLBAGGING = "Hill-bagging";
public static final String KEY_XCOORD = "Xcoord";
public static final String KEY_YCOORD = "Ycoord";
public static final String KEY_LATITUDE = "Latitude";
public static final String KEY_LONGITUDE = "Longitude";
public static final String KEY_XSECTION = "_Section";
public static final String KEY_HILL_ID = "hill_id";
public static final String KEY_TYPES_ID = "type_id";
private static final String HILLTYPES_CREATE = "CREATE TABLE "
+ HILLTYPES_TABLE + "(" + KEY_ID + " integer primary key," + KEY_TITLE + " unique)";
private static final String TYPESLINK_CREATE = "CREATE TABLE "
+ TYPES_LINK_TABLE + "(" + KEY_ID
+ " integer primary key autoincrement," + KEY_HILL_ID
+ " references " + HILLS_TABLE + "(" + KEY_ID + ")," + KEY_TYPES_ID
+ " references " + HILLTYPES_TABLE + "(" + KEY_ID + "))";
private static Handler handler;
public static void onCreate(SupportSQLiteDatabase database, Context context){//, Handler handler, int rows) {
//createSimpleTables(database);
//HillsTables.handler = handler;
long startTime = System.currentTimeMillis();
createAndPopulateHillsTable(database, context, startTime);//, rows);
populateHillTypes(database, startTime);
}
static void populateHillTypes(SupportSQLiteDatabase database, long startTime) {
Cursor c = database.query("select * from "+HILLS_TABLE);
SupportSQLiteStatement insertHillType = database.compileStatement("INSERT or IGNORE into " + HILLTYPES_TABLE + " VALUES(?,?)");
SupportSQLiteStatement insertHillTypeLink = database.compileStatement("INSERT into " + TYPES_LINK_TABLE + " (" + KEY_HILL_ID + "," + KEY_TYPES_ID + ") values (?,?)");
// Populate static hill types data
database.beginTransaction();
if (c.moveToFirst()) {
do {
String[] classifications = c.getString(c.getColumnIndex(KEY_CLASSIFICATION)).replace("\"", "").split(",");
for (String classification : classifications) {
insertHillType.bindString(2, classification);
insertHillType.bindLong(1, c.getColumnIndex(classification));
insertHillType.executeInsert();
insertHillTypeLink.bindLong(1, c.getInt(0));
insertHillTypeLink.bindLong(2, c.getColumnIndex(classification));
insertHillTypeLink.executeInsert();
}
} while (c.moveToNext());
}
database.setTransactionSuccessful();
database.endTransaction();
c.close();
Log.d(TAG, "populateHillTypes: Finished inserting hill types information after "
+ (System.currentTimeMillis() - startTime) / 1000);
}
private static void createAndPopulateHillsTable(SupportSQLiteDatabase database, Context context, long startTime){//, int rows) {
InputStream is;
Log.d(TAG, "createAndPopulateHillsTable: starting");
try {
is = context.getAssets().open(HILLS_CSV);
BufferedReader reader = new BufferedReader(
new InputStreamReader(is));
//createHillsTable(database, reader);
String headerRow = reader.readLine();
StringBuffer insertHillsBuffer;
// read main hill data into hills table
database.beginTransaction();
String line;
int count = 0;
while ((line = reader.readLine()) != null && count <= 23000) {
count++;
if(handler != null) {
handler.dispatchMessage(Message.obtain(handler, 0, 0, 1, 1));
}
insertHillsBuffer = new StringBuffer();
insertHillsBuffer.append("INSERT INTO " + HILLS_TABLE
+ " VALUES (");
String[] lineArray = line
.split(",(?=([^\"]*\"[^\"]*\")*[^\"]*$)");
for (String entry : lineArray) {
insertHillsBuffer.append("'").append(entry.replace("'", "''")).append("',");
}
insertHillsBuffer.deleteCharAt(insertHillsBuffer.length() - 1);
insertHillsBuffer.append(")");
database.execSQL(insertHillsBuffer.toString());
}
database.setTransactionSuccessful();
database.endTransaction();
Log.d(TAG,
"#################Finished inserting base hill information after "
+ (System.currentTimeMillis() - startTime) / 1000);
} catch (IOException e) {
Log.e(TAG,
"Failed to populate hills database table", e);
}
}
private static void createHillsTable(SupportSQLiteDatabase database, BufferedReader reader) throws IOException {
String headerRow = reader.readLine();
String hillsTableStarter = "CREATE TABLE '" + HILLS_TABLE
+ "' ('" + KEY_ID + "' integer primary key,";
String[] headerArray = headerRow.split(",");
StringBuilder createHillsTableBuilder = new StringBuilder();
for (String header : headerArray) {
header = header.replace("\uFEFF","");
if (!"Number".equals(header)) {
createHillsTableBuilder.append("'").append(header).append("',");
}
}
String c = createHillsTableBuilder.toString();
database.execSQL(hillsTableStarter + c.substring(0, c.length() - 1) + ")");
}
private static void createSimpleTables(SupportSQLiteDatabase database) {
database.execSQL("PRAGMA foreign_keys=ON;");
database.execSQL(HILLTYPES_CREATE);
database.execSQL(TYPESLINK_CREATE);
}
public static void onUpgrade(SupportSQLiteDatabase database, int oldVersion,
int newVersion, Context context, Handler handler, int rows) {
Log.w(HillsTables.class.getName(), "Upgrading database from version "
+ oldVersion + " to " + newVersion
+ ", which will destroy all old hills data");
database.execSQL("DROP TABLE IF EXISTS " + HILLS_TABLE);
database.execSQL("DROP TABLE IF EXISTS " + HILLTYPES_TABLE);
database.execSQL("DROP TABLE IF EXISTS " + TYPES_LINK_TABLE);
//onCreate(database, context, handler, rows);
}
}
SplashScreenActivity
package uk.colessoft.android.hilllist.activities;
import android.Manifest;
import android.app.AlertDialog;
import android.app.ProgressDialog;
import android.content.DialogInterface;
import android.content.Intent;
import android.content.pm.ActivityInfo;
import android.content.pm.PackageManager;
import android.os.Bundle;
import android.os.Handler;
import android.os.Message;
import android.support.v4.app.ActivityCompat;
import android.support.v4.content.ContextCompat;
import android.support.v7.app.AppCompatActivity;
import android.text.util.Linkify;
import android.widget.TextView;
import javax.inject.Inject;
import uk.colessoft.android.hilllist.BHApplication;
import uk.colessoft.android.hilllist.R;
import uk.colessoft.android.hilllist.database.BritishHillsDatasource;
public class SplashScreenActivity extends AppCompatActivity {
private static final int MY_PERMISSIONS_REQUEST_WRITE_EXTERNAL_STORAGE = 0x00001;
public static final int MAX = 20782;
private Thread splashThread;
private Handler handler;
private ProgressDialog progressDialog;
@Inject
BritishHillsDatasource dbAdapter;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
((BHApplication) getApplication()).getDbComponent().inject(this);
setContentView(R.layout.splash);
TextView link=(TextView) findViewById(R.id.TextView03);
Linkify.addLinks(link, Linkify.ALL);
handler = new Handler(){
@Override
public void handleMessage(Message msg) {
if(msg.arg1 == 0)
progressDialog.incrementProgressBy(1);
else {
SplashScreenActivity.this.setRequestedOrientation(ActivityInfo.SCREEN_ORIENTATION_USER);
progressDialog.cancel();
}
}
};
progressDialog = new ProgressDialog(SplashScreenActivity.this);
progressDialog.setTitle("Updating Database");
progressDialog.setProgressStyle(ProgressDialog.STYLE_HORIZONTAL);
progressDialog.setMax(MAX);
progressDialog.setCancelable(false);
progressDialog.setCanceledOnTouchOutside(false);
progressDialog.show();
splashThread = new Thread() {
@Override
public void run() {
SplashScreenActivity.this.setRequestedOrientation(ActivityInfo.SCREEN_ORIENTATION_NOSENSOR);
dbAdapter.touch(handler);
handler.sendMessage(Message.obtain(handler, 0, 1, 1, 1));
try {
int waited = 0;
while (waited < 1800) {
sleep(100);
waited += 100;
}
} catch (InterruptedException e) {
// do nothing
} finally {
finish();
Intent i = new Intent(SplashScreenActivity.this,
Main.class);
startActivity(i);
}
}
};
if (ContextCompat.checkSelfPermission(this,
Manifest.permission.WRITE_EXTERNAL_STORAGE)
!= PackageManager.PERMISSION_GRANTED) {
// Should we show an explanation?
if (ActivityCompat.shouldShowRequestPermissionRationale(this,
Manifest.permission.WRITE_EXTERNAL_STORAGE)) {
showMessageOKCancel("You need to allow access to the SD Card to read and write the Database",
(dialog, which) -> ActivityCompat.requestPermissions(SplashScreenActivity.this,
new String[]{Manifest.permission.WRITE_EXTERNAL_STORAGE},
MY_PERMISSIONS_REQUEST_WRITE_EXTERNAL_STORAGE));
} else {
// No explanation needed, we can request the permission.
ActivityCompat.requestPermissions(this,
new String[]{Manifest.permission.WRITE_EXTERNAL_STORAGE},
MY_PERMISSIONS_REQUEST_WRITE_EXTERNAL_STORAGE);
}
}else {
splashThread.start();
}
}
@Override
public void onRequestPermissionsResult(int requestCode,
String permissions[], int[] grantResults) {
switch (requestCode) {
case MY_PERMISSIONS_REQUEST_WRITE_EXTERNAL_STORAGE: {
// If request is cancelled, the result arrays are empty.
if (grantResults.length > 0
&& grantResults[0] == PackageManager.PERMISSION_GRANTED) {
splashThread.start();
} else {
finish();
}
}
}
}
private void showMessageOKCancel(String message, DialogInterface.OnClickListener okListener) {
new AlertDialog.Builder(SplashScreenActivity.this)
.setMessage(message)
.setPositiveButton("OK", okListener)
.create()
.show();
}
}
