Skip to content Skip to sidebar Skip to footer
Home / Android / Android Sqlite / Sqlite

Updating A Prepopulated Database

Post a Comment
I have created a trivia quiz for android devices. I have a database with questions in assets folder and dbHelper class. Everything works fine, but now i want to fix some typos and

Solution 1:

As per the comment from Gabe Sechan, the simplest method would be to copy the db from the assets folder every time the App is started, that is to change :-

private void createDB(){
    boolean dbExist = DBExists();

    if(!dbExist){
        this.getReadableDatabase();
        copyDBFromResource();

    }
    dbSglite=getReadableDatabase();
}

to :-

private void createDB(){
    copyDBFromResource();
    dbSglite=getReadableDatabase();
}

You apparently have concerns commenting

"Wouldn't that copy DB every time activity is started."

yes it would (would that be that bad? - rhetorical).

However, say you were to utilise the Database Version i.e. check the version in the assets folder against the current version. You still basically need to access the database from the assets folder, so you would be checking one database against another (at least opening them). So there would still be some overheads.

An option that may be less intensive would be check the asset file's last modified date against a the date of the last copied asset file in shared preferences. (File lastModified method) File - lastModified.

Another option, in a similar view, would be to check the package version against the last implemented, again stored in shared preferences.PackageInfo - versionCode.

Of course in both these options, replacing the database from the assets file only happening when there is a difference (increase).

Example using Package Version

The following example (all changes within the dbHelper class) will copy the Database from the assets if the package version is increased (or if the Database doesn't exist) :-

class dbHelper extends SQLiteOpenHelper {


    private static final String DATABASE_NAME = "questions.db";
    private  static final int SCHEMA_VERSION = 1;
    private static  final String SHARED_PREFS = "shared_prefs";
    private static final String SHARED_PREFKEY_QUESTIONSDBLASTUPDATED = "spkey_qdblastupdated";

    public SQLiteDatabase dbSglite;
    private String mDBPath;

    private final Context myContext;

    public dbHelper(Context context) {
        super(context, DATABASE_NAME, null, SCHEMA_VERSION);
        this.myContext=context;
        this.mDBPath=context.getDatabasePath(DATABASE_NAME).getParent();
    }

    @Override
    public void onCreate(SQLiteDatabase db){
        Log.d("ONCREATE","OnCreate Method Called.");
    }

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

    }

    public void createDatabase(){
        createDB();
    }

    private void createDB(){

        if (isQuestionsDBNew() || (!DBExists())) {
            Log.d("COPYFROMASSET", "Copying Questions From Assets");
            copyDBFromResource();
            setQuestionsDBNew(getPackageVersion());
        } else {
            Log.d("COPYFROMASSET",
                    "Questions not copied from Assets - New Questions Check result was " +
                            Boolean.toString(isQuestionsDBNew()) +
                            " DB Exists result was " + Boolean.toString(DBExists())
            );
        }
        dbSglite=getReadableDatabase();
    }

    private boolean DBExists(){

        SQLiteDatabase db = null;

        try {
            String databasePath = myContext.getDatabasePath(DATABASE_NAME).getPath();
            db = SQLiteDatabase.openDatabase(databasePath,null, SQLiteDatabase.OPEN_READWRITE);
            db.setLocale(Locale.getDefault());
            db.setLockingEnabled(true);
            db.setVersion(1);
        } catch (SQLiteException e) {

            Log.e("SqlHelper", "database not found");
        }

        if (db != null) {
            db.close();
        }
        return db != null;

    }

    private void copyDBFromResource() {
        InputStream inputStream = null;
        OutputStream outputStream = null;

        try {
            inputStream = myContext.getAssets().open(DATABASE_NAME);
            File databasedir = new File(myContext.getDatabasePath(DATABASE_NAME).getParent());
            databasedir.mkdirs();
            outputStream = new FileOutputStream(mDBPath+"/"+DATABASE_NAME);
            byte[] buffer = new byte[1024];
            int length;
            while ((length=inputStream.read(buffer))>0){
                outputStream.write(buffer, 0, length);
            }

            outputStream.flush();
            outputStream.close();
            inputStream.close();

        } catch (IOException e) {
            e.printStackTrace();
            throw new Error("Problem copying database.");
        }

    }

    public void openDataBase() throws SQLException {
        String myPath = myContext.getDatabasePath(DATABASE_NAME).getPath();
        dbSglite = SQLiteDatabase.openDatabase(myPath, null,
                SQLiteDatabase.OPEN_READWRITE);

    }

    private boolean isQuestionsDBNew() {
        SharedPreferences prefs = myContext.getSharedPreferences(SHARED_PREFS, Context.MODE_PRIVATE);
        long stored_lastused = prefs.getLong(SHARED_PREFKEY_QUESTIONSDBLASTUPDATED,-1);
        Log.d("NEWQUESTIONS?", "Result of testing package version " +
                String.valueOf(stored_lastused) +
                " against " +
                String.valueOf( getPackageVersion()) +
                " was " + String.valueOf(stored_lastused < getPackageVersion()));
        return  (stored_lastused < getPackageVersion());
    }

    private long getPackageVersion() {
        PackageInfo pi;
        try {
            pi = myContext.getPackageManager().getPackageInfo(myContext.getPackageName(),0);
        } catch (PackageManager.NameNotFoundException e) {
            return -1;
        }
        Log.d("PACKAGEVERSION", "The version of package " +
                myContext.getPackageName() +
                " was " +
                String.valueOf(pi.versionCode)
        );
        return pi.versionCode;
    }

    private void setQuestionsDBNew(long lastused) {
        SharedPreferences.Editor editor = myContext.getSharedPreferences(SHARED_PREFS, Context.MODE_PRIVATE).edit();
        editor.putLong(SHARED_PREFKEY_QUESTIONSDBLASTUPDATED,lastused);
        editor.apply();
    }
}

Notes

  • The code is very much based upon the code from the question. There are :-
    • Two additional class variables (constants) for the shared preferences handling.
    • Three new methods :-
    • isQuestionsDBNew that returns true if the package version is greater than the version store in shared preferences (nothing in shared preferences results in -1, so should be less than any package version).
    • getPackageVersion returns the package version as a long.
    • setQuestionsDBNew which updates the applicable shared preference.
    • Changes to the createDB to check for the package version change and to then copy the database from the assets. There is an additional check to see if the database exists, although this would only be needed if just the database file were deleted. Deleting the App's data would delete the shared preferences so result in the database being copied.
  • The code includes some diagnostic logging which I've left in.
  • This hasn't been extensively tested (i.e. I've not gone to the extent of increasing the package version).

Output from the example - App being installed :-

01-05 19:46:44.849 26692-26692/m.com.so48103235_updateprepopdb D/PACKAGEVERSION: The version of package m.com.so48103235_updateprepopdb was 1
01-05 19:46:44.850 26692-26692/m.com.so48103235_updateprepopdb D/PACKAGEVERSION: The version of package m.com.so48103235_updateprepopdb was 1
01-05 19:46:44.850 26692-26692/m.com.so48103235_updateprepopdb D/NEWQUESTIONS?: Result of testing package version -1 against 1 was true
01-05 19:46:44.850 26692-26692/m.com.so48103235_updateprepopdb D/PACKAGEVERSION: The version of package m.com.so48103235_updateprepopdb was 1
01-05 19:46:44.850 26692-26692/m.com.so48103235_updateprepopdb D/COPYFROMASSET: Copying Questions From Assets
01-05 19:46:44.855 26692-26692/m.com.so48103235_updateprepopdb D/PACKAGEVERSION: The version of package m.com.so48103235_updateprepopdb was 1

Output from the example - Subsequent Run :-

01-05 19:48:10.375 26755-26755/m.com.so48103235_updateprepopdb D/PACKAGEVERSION: The version of package m.com.so48103235_updateprepopdb was 1
01-05 19:48:10.376 26755-26755/m.com.so48103235_updateprepopdb D/PACKAGEVERSION: The version of package m.com.so48103235_updateprepopdb was 1
01-05 19:48:10.376 26755-26755/m.com.so48103235_updateprepopdb D/NEWQUESTIONS?: Result of testing package version 1 against 1 was false
01-05 19:48:10.376 26755-26755/m.com.so48103235_updateprepopdb D/PACKAGEVERSION: The version of package m.com.so48103235_updateprepopdb was 1
01-05 19:48:10.381 26755-26755/m.com.so48103235_updateprepopdb D/PACKAGEVERSION: The version of package m.com.so48103235_updateprepopdb was 1
01-05 19:48:10.381 26755-26755/m.com.so48103235_updateprepopdb D/PACKAGEVERSION: The version of package m.com.so48103235_updateprepopdb was 1
01-05 19:48:10.381 26755-26755/m.com.so48103235_updateprepopdb D/NEWQUESTIONS?: Result of testing package version 1 against 1 was false
01-05 19:48:10.382 26755-26755/m.com.so48103235_updateprepopdb D/PACKAGEVERSION: The version of package m.com.so48103235_updateprepopdb was 1
01-05 19:48:10.387 26755-26755/m.com.so48103235_updateprepopdb D/COPYFROMASSET: Questions not copied from Assets - New Questions Check result was false DB Exists result was true
  • Extra messages due the more extensive message used when reporting that the database was not copied calling methods again which add log messages.

You may like these posts

Post a Comment for "Updating A Prepopulated Database"