On Upgrade Method Not Getting Called In Android Sqlite
I'm using prepopulated sqlite database in my app and i'm trying to update my database in my android application but the onUpgrade method doesn't get called when I increment the ver
Solution 1:
I believe that your issue is that you don't use one of the SQLiteOpenHelper's methods (getWritableDatabse or getReadableDatabase) to open the database.
Rather you use the SQLiteDatabase's OPEN method. Which doesn't do the version check and set.
If I uncomment this.getReadableDatabase() in my constructor the onUpgrade method gets called but I can't query the data and returns error.
Introducing the getReadableDatabase, then does the check and attempts to copy the newer version, but the older version is open and so you likely get a conflict as a result.
You may find the answers to the question Which methods can be used to manage differing versions of pre-existing databases? helpful (perhaps the 2nd).
Working Example
The following working example utilises the code from the linked answer but is based upon the code and database from the question (as is ascertainable).
The core underlying class is DatabaseAssetHandler.java, which includes a number of static methods for checking and copying the database, all notably as a file rather than as an SQLite database.
A subclass of SQLiteOpenHelper, based upon the question's DatabaseHelper.java class but utilising the DatabaseAssethandler methods to check the coded version againast the database file version (negating the need to use the onUpgrade method and also the onCreate method)
Finally the example includes an Activity, MainActivity.java as you'd typically find. This class additionally extracts all rows from the database and dumps the Cursor to the log.
DatabaseAssetManager.java
publicclassDatabaseAssetHandler {
staticfinal String[] tempfiles = newString[]{"-journal","-wal","-shm"}; // temporary files to renamepublicstaticfinalStringbackup="-backup"; //value to be appended to file name when renaming (psuedo delete)publicstaticfinalintOUCH= -666666666;
/**
* Check if the database already exists. NOTE will create the databases folder is it doesn't exist
* @return true if it exists, false if it doesn't
*/publicstaticbooleancheckDataBase(Context context, String dbname) {
Filedb=newFile(context.getDatabasePath(dbname).getPath()); //Get the file name of the database
Log.d("DBPATH","DB Path is " + db.getPath()); //TODO remove if publish Appif (db.exists()) returntrue; // If it exists then return doing nothing// Get the parent (directory in which the database file would be)Filedbdir= db.getParentFile();
// If the directory does not exits then make the directory (and higher level directories)if (!dbdir.exists()) {
db.getParentFile().mkdirs();
dbdir.mkdirs();
}
returnfalse;
}
/**
* Copy database file from the assets folder
* (long version caters for asset file name being different to the database name)
* @param context Context is needed to get the applicable package
* @param dbname name of the database file
* @param assetfilename name of the asset file
* @param deleteExistingDB true if an existing database file should be deleted
* note will delete journal and wal files
* note doen't actually delete the files rater it renames
* the files by appended -backup to the file name
* SEE/USE clearForceBackups below to delete the renamed files
*/publicstaticvoidcopyDataBase(Context context, String dbname, String assetfilename, boolean deleteExistingDB, int version) {
checkpointIfWALEnabled(context,dbname);
finalStringTAG="COPYDATABASE";
intstage=0, buffer_size = 4096, blocks_copied = 0, bytes_copied = 0;
Filef=newFile(context.getDatabasePath(dbname).toString());
InputStream is;
OutputStream os;
/**
* If forcing then effectively delete (rename) current database files
*/if (deleteExistingDB) {
f.renameTo(context.getDatabasePath(dbname + backup));
for (String s: tempfiles) {
Filetmpf=newFile(context.getDatabasePath(dbname + s).toString());
if (tmpf.exists()) {
tmpf.renameTo(context.getDatabasePath(dbname + s + backup));
}
}
}
//Open your local db as the input stream
Log.d(TAG,"Initiated Copy of the database file " + assetfilename + " from the assets folder."); //TODO remove if publishingtry {
is = context.getAssets().open(assetfilename); // Open the Asset file
stage++;
Log.d(TAG, "Asset file " + assetfilename + " found so attmepting to copy to " + f.getPath()); //TODO remove if publishing
os = newFileOutputStream(f);
stage++;
//transfer bytes from the inputfile to the outputfilebyte[] buffer = newbyte[buffer_size];
int length;
while ((length = is.read(buffer)) > 0) {
blocks_copied++;
Log.d(TAG, "Attempting copy of block " + String.valueOf(blocks_copied) + " which has " + String.valueOf(length) + " bytes."); //TODO remove if publishing
os.write(buffer, 0, length);
bytes_copied += length;
}
stage++;
Log.d(TAG,
"Finished copying Database " + dbname +
" from the assets folder, to " + f.getPath() +
String.valueOf(bytes_copied) + "were copied, in " +
String.valueOf(blocks_copied) + " blocks of size " +
String.valueOf(buffer_size) + "."
); //TODO remove if publishing//Close the streams
os.flush();
stage++;
os.close();
stage++;
is.close();
Log.d(TAG, "All Streams have been flushed and closed.");
if (version > 0) {
setVersion(context,dbname,version);
}
} catch (IOException e) {
Stringexception_message="";
e.printStackTrace();
switch (stage) {
case0:
exception_message = "Error trying to open the asset " + dbname;
break;
case1:
exception_message = "Error opening Database file for output, path is " + f.getPath();
break;
case2:
exception_message = "Error flushing written database file " + f.getPath();
break;
case3:
exception_message = "Error closing written database file " + f.getPath();
break;
case4:
exception_message = "Error closing asset file " + f.getPath();
}
thrownewRuntimeException("Unable to copy the database from the asset folder." + exception_message + " see starck-trace above.");
}
}
/**
* Copy the databsse from the assets folder where asset name and dbname are the same
* @param context
* @param dbname
* @param deleteExistingDB
*/publicstaticvoidcopyDataBase(Context context, String dbname, boolean deleteExistingDB, int version) {
copyDataBase(context, dbname,dbname,deleteExistingDB, version);
}
/**
* Get the SQLite_user_vesrion from the DB in the asset folder
*
* @param context needed to get the appropriate package assets
* @param assetfilename the name of the asset file (assumes/requires name matches database)
* @return the version number as stored in the asset DB
*/publicstaticintgetVersionFromDBInAssetFolder(Context context, String assetfilename) {
InputStream is;
try {
is = context.getAssets().open(assetfilename);
} catch (IOException e) {
return OUCH;
}
return getDBVersionFromInputStream(is);
}
/**
* Get the version from the database itself without opening the database as an SQliteDatabase
* @param context Needed to ascertain package
* @param dbname the name of the dataabase
* @return the version number extracted
*/publicstaticintgetVersionFromDBFile(Context context, String dbname) {
InputStream is;
try {
is = newFileInputStream(newFile(context.getDatabasePath(dbname).toString()));
} catch (IOException e) {
return OUCH;
}
return getDBVersionFromInputStream(is);
}
/**
* Get the Database Version (user_version) from an inputstream
* Note the inputstream is closed
* @param is The Inputstream
* @return The extracted version number
*/privatestaticintgetDBVersionFromInputStream(InputStream is) {
intrv= -1, dbversion_offset = 60, dbversion_length = 4 ;
byte[] dbfileheader = newbyte[64];
byte[] dbversion = newbyte[4];
try {
is.read(dbfileheader);
is.close();
} catch (IOException e) {
e.printStackTrace();
return rv;
}
for (inti=0; i < dbversion_length; i++ ) {
dbversion[i] = dbfileheader[dbversion_offset + i];
}
return ByteBuffer.wrap(dbversion).getInt();
}
/**
* Check to see if the asset file exists
*
* @param context needed to get the appropriate package
* @param assetfilename the name of the asset file to check
* @return true if the asset file exists, else false
*/publicstaticbooleanifAssetFileExists(Context context, String assetfilename) {
try {
context.getAssets().open(assetfilename);
} catch (IOException e) {
returnfalse;
}
returntrue;
}
/**
* Delete the backup
* @param context
* @param dbname
*/publicstaticvoidclearForceBackups(Context context, String dbname) {
String[] fulllist = newString[tempfiles.length + 1];
for (inti=0;i < tempfiles.length; i++) {
fulllist[i] = tempfiles[i];
}
fulllist[tempfiles.length] = ""; // Add "" so database file backup is also deletedfor (String s: fulllist) {
Filetmpf=newFile(context.getDatabasePath(dbname + s + backup).toString());
if (tmpf.exists()) {
tmpf.delete();
}
}
}
/**
*
* @param context The context so that the respective package is used
* @param dbname The name of the database (the old will have -backup appended)
* @param table The table from which to copy the data
*/publicstaticvoidrestoreTable(Context context, String dbname, String table) {
ContentValuescv=newContentValues();
SQLiteDatabasedbnew= SQLiteDatabase.openDatabase(context.getDatabasePath(dbname).toString(), null,SQLiteDatabase.OPEN_READWRITE);
SQLiteDatabasedbold= SQLiteDatabase.openDatabase(context.getDatabasePath(dbname + backup).toString(),null,SQLiteDatabase.OPEN_READONLY);
Cursorcsr= dbold.query(table,null,null,null,null,null,null);
dbnew.beginTransaction();
while (csr.moveToNext()) {
cv.clear();
intoffset=0;
for (String column: csr.getColumnNames()) {
switch (csr.getType(offset++)){
case Cursor.FIELD_TYPE_NULL:
break;
case Cursor.FIELD_TYPE_INTEGER:
cv.put(column,csr.getLong(csr.getColumnIndex(column)));
break;
case Cursor.FIELD_TYPE_FLOAT:
cv.put(column,csr.getFloat(csr.getColumnIndex(column)));
break;
case Cursor.FIELD_TYPE_STRING:
cv.put(column,csr.getString(csr.getColumnIndex(column)));
break;
case Cursor.FIELD_TYPE_BLOB:
cv.put(column,csr.getBlob(csr.getColumnIndex(column)));
}
}
dbnew.insert(DatabaseHelper.TABLE_BOOKMARK,null,cv);
}
dbnew.setTransactionSuccessful();
dbnew.endTransaction();
csr.close();
dbnew.close();
dbold.close();
}
privatestaticvoidcheckpointIfWALEnabled(Context context, String dbname) {
finalStringTAG="WALCHKPNT";
Cursor csr;
intwal_busy= -99, wal_log = -99, wal_checkpointed = -99;
if (!newFile(context.getDatabasePath(dbname).getPath()).exists()) {
return;
}
SQLiteDatabasedb= SQLiteDatabase.openDatabase(context.getDatabasePath(dbname).getPath(),null,SQLiteDatabase.OPEN_READWRITE);
csr = db.rawQuery("PRAGMA journal_mode",null);
if (csr.moveToFirst()) {
Stringmode= csr.getString(0);
//Log.d(TAG, "Mode is " + mode);if (mode.toLowerCase().equals("wal")) {
csr = db.rawQuery("PRAGMA wal_checkpoint",null);
if (csr.moveToFirst()) {
wal_busy = csr.getInt(0);
wal_log = csr.getInt(1);
wal_checkpointed = csr.getInt(2);
}
//Log.d(TAG,"Checkpoint pre checkpointing Busy = " + String.valueOf(wal_busy) + " LOG = " + String.valueOf(wal_log) + " CHECKPOINTED = " + String.valueOf(wal_checkpointed) );
csr = db.rawQuery("PRAGMA wal_checkpoint(TRUNCATE)",null);
csr.getCount();
csr = db.rawQuery("PRAGMA wal_checkpoint",null);
if (csr.moveToFirst()) {
wal_busy = csr.getInt(0);
wal_log = csr.getInt(1);
wal_checkpointed = csr.getInt(2);
}
//Log.d(TAG,"Checkpoint post checkpointing Busy = " + String.valueOf(wal_busy) + " LOG = " + String.valueOf(wal_log) + " CHECKPOINTED = " + String.valueOf(wal_checkpointed) );
}
}
csr.close();
db.close();
}
privatestaticvoidsetVersion(Context context, String dbname, int version) {
SQLiteDatabasedb= SQLiteDatabase.openDatabase(context.getDatabasePath(dbname).getPath(),null,SQLiteDatabase.OPEN_READWRITE);
db.setVersion(version);
db.close();
}
}
- Note that not all methods are utilised.
- Note modified (2019-05-08) to specifically set the version number after the copy of the asset file call to new setVersion method.
- Note modified (2019-05-08) to call the new checkpointIfWALEnabled method prior to the copy, so that the database is checkpointed.
DatabaseHelper.java
publicclassDatabaseHelperextendsSQLiteOpenHelper {
privatestaticfinalStringDB_NAME="dictionary.db";
privatestaticfinalintDB_VERSION=1;
//private String DB_PATH = null; //<<<<<<<<<< NOT NEEDEDpublicstaticfinalStringTABLE_DICTIONARY="dictionary";
publicstaticfinal String TABLE_BOOKMARK= "bookmark";
publicstaticfinalStringCOL_ID="id";
publicstaticfinalStringCOL_WORD="word";
publicstaticfinalStringCOL_DEFINITION="definition";
public Context mcontext;
public SQLiteDatabase mDatabase;
publicDatabaseHelper(Context context) {
super(context, DB_NAME, null, DB_VERSION);
this.mcontext = context;
Log.d("DBVERSION","The Database Version (as hard coded) is " + String.valueOf(DB_VERSION));
intdbversion= DatabaseAssetHandler.getVersionFromDBFile(context,DB_NAME);
Log.d("DBVERSION","The Database Version (as per the database file) is " + String.valueOf(dbversion));
// Copy the Database if no database existsif (!DatabaseAssetHandler.checkDataBase(context,DB_NAME)) {
DatabaseAssetHandler.copyDataBase(context,DB_NAME,true,DB_VERSION);
} else {
if (DB_VERSION > dbversion && DatabaseAssetHandler.checkDataBase(context, DB_NAME)) {
DatabaseAssetHandler.copyDataBase(context, DB_NAME, true,DB_VERSION);
DatabaseAssetHandler.clearForceBackups(context, DB_NAME); // Clear the backups
}
}
mDatabase = this.getWritableDatabase(); //<<<<<<<<<<<<<
}
@OverridepublicvoidonCreate(SQLiteDatabase db) {
}
@OverridepublicvoidonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
publicvoidopenDatabase()throws SQLException {
mDatabase = this.getWritableDatabase();
}
@Overridepublicsynchronizedvoidclose() {
if (mDatabase != null)
mDatabase.close();
super.close();
}
}
- Note modified (2019-05-08) to pass the version number to the copyDatabase method.
MainActivity.java
publicclassMainActivityextendsAppCompatActivity {
DatabaseHelper mDBHlpr;
@OverrideprotectedvoidonCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDBHlpr = newDatabaseHelper(this);
Cursorcsr= mDBHlpr.getWritableDatabase().query(
DatabaseHelper.TABLE_DICTIONARY,
null,null,null,null,null,null
);
DatabaseUtils.dumpCursor(csr);
csr.close();
}
}
Results
First a database was created using an external tool with 2 rows in the dictionary table and copied into the assets folder.
Run 1.
The first run, copies the database from the assets folder and results in the log containing :-
04-17 19:24:54.249 3233-3233/m.example.so55711282dictionary D/DBVERSION: The Database Version (as hard coded) is 1
04-17 19:24:54.249 3233-3233/m.example.so55711282dictionary D/DBVERSION: The Database Version (as per the database file) is -666666666
04-17 19:24:54.249 3233-3233/m.example.so55711282dictionary D/DBPATH: DB Path is /data/data/m.example.so55711282dictionary/databases/dictionary.db
04-17 19:24:54.250 3233-3233/m.example.so55711282dictionary D/COPYDATABASE: Initiated Copy of the database file dictionary.db from the assets folder.
04-17 19:24:54.251 3233-3233/m.example.so55711282dictionary D/COPYDATABASE: Asset file dictionary.db found so attmepting to copy to /data/data/m.example.so55711282dictionary/databases/dictionary.db
04-17 19:24:54.251 3233-3233/m.example.so55711282dictionary D/COPYDATABASE: Attempting copy of block 1 which has 4096 bytes.
04-17 19:24:54.251 3233-3233/m.example.so55711282dictionary D/COPYDATABASE: Attempting copy of block 2 which has 4096 bytes.
04-17 19:24:54.251 3233-3233/m.example.so55711282dictionary D/COPYDATABASE: Attempting copy of block 3 which has 4096 bytes.
04-17 19:24:54.251 3233-3233/m.example.so55711282dictionary D/COPYDATABASE: Finished copying Database dictionary.db from the assets folder, to /data/data/m.example.so55711282dictionary/databases/dictionary.db12288were copied, in 3 blocks of size 4096.
04-17 19:24:54.251 3233-3233/m.example.so55711282dictionary D/COPYDATABASE: All Streams have been flushed and closed.
04-17 19:24:54.273 3233-3233/m.example.so55711282dictionary I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@22ee92e7
04-17 19:24:54.273 3233-3233/m.example.so55711282dictionary I/System.out: 0 {
04-17 19:24:54.273 3233-3233/m.example.so55711282dictionary I/System.out: id=1
04-17 19:24:54.273 3233-3233/m.example.so55711282dictionary I/System.out: word=Apple
04-17 19:24:54.273 3233-3233/m.example.so55711282dictionary I/System.out: definition=Thing that drops from an Apple Tree.
04-17 19:24:54.273 3233-3233/m.example.so55711282dictionary I/System.out: }
04-17 19:24:54.273 3233-3233/m.example.so55711282dictionary I/System.out: 1 {
04-17 19:24:54.273 3233-3233/m.example.so55711282dictionary I/System.out: id=2
04-17 19:24:54.273 3233-3233/m.example.so55711282dictionary I/System.out: word=Bucket
04-17 19:24:54.273 3233-3233/m.example.so55711282dictionary I/System.out: definition=Hand held container with carrying hanlde.
04-17 19:24:54.273 3233-3233/m.example.so55711282dictionary I/System.out: }
04-17 19:24:54.273 3233-3233/m.example.so55711282dictionary I/System.out: <<<<<
i.e. The database has been copied from the assets folder and the expected rows have been dumped.
Run 2
Without making any changes the App was rerun (to test that it doesn't recopy the database) :- This time the logcat contains :-
04-1719:30:57.4443343-3343/?D/DBVERSION:TheDatabaseVersion(ashardcoded)is104-1719:30:57.4453343-3343/?D/DBVERSION:TheDatabaseVersion(asperthedatabasefile)is104-1719:30:57.4453343-3343/?D/DBPATH:DBPathis/data/data/m.example.so55711282dictionary/databases/dictionary.db04-1719:30:57.4493343-3343/?I/System.out:>>>>>Dumpingcursorandroid.database.sqlite.SQLiteCursor@22ee92e704-1719:30:57.4493343-3343/?I/System.out:0 {
04-1719:30:57.4493343-3343/?I/System.out:id=104-1719:30:57.4493343-3343/?I/System.out:word=Apple04-1719:30:57.4503343-3343/?I/System.out:definition=ThingthatdropsfromanAppleTree.04-1719:30:57.4503343-3343/?I/System.out: }
04-1719:30:57.4503343-3343/?I/System.out:1 {
04-1719:30:57.4503343-3343/?I/System.out:id=204-1719:30:57.4503343-3343/?I/System.out:word=Bucket04-1719:30:57.4503343-3343/?I/System.out:definition=Handheldcontainerwithcarryinghanlde.04-1719:30:57.4503343-3343/?I/System.out: }
04-1719:30:57.4503343-3343/?I/System.out:<<<<<i.e. the database, as it exists, has not been copied.
Run 3.
The database has been modified by adding another two rows using an external tool and then copied into the assets folder replacing the older database file and DB_VERSION is changed to 2.
The log contains :-
04-17 19:35:16.661 3459-3459/m.example.so55711282dictionary D/DBVERSION: The Database Version (as hard coded) is 2
04-17 19:35:16.661 3459-3459/m.example.so55711282dictionary D/DBVERSION: The Database Version (as per the database file) is 1
04-17 19:35:16.661 3459-3459/m.example.so55711282dictionary D/DBPATH: DB Path is /data/data/m.example.so55711282dictionary/databases/dictionary.db
04-17 19:35:16.661 3459-3459/m.example.so55711282dictionary D/DBPATH: DB Path is /data/data/m.example.so55711282dictionary/databases/dictionary.db
04-17 19:35:16.661 3459-3459/m.example.so55711282dictionary D/COPYDATABASE: Initiated Copy of the database file dictionary.db from the assets folder.
04-17 19:35:16.661 3459-3459/m.example.so55711282dictionary D/COPYDATABASE: Asset file dictionary.db found so attmepting to copy to /data/data/m.example.so55711282dictionary/databases/dictionary.db
04-17 19:35:16.662 3459-3459/m.example.so55711282dictionary D/COPYDATABASE: Attempting copy of block 1 which has 4096 bytes.
04-17 19:35:16.662 3459-3459/m.example.so55711282dictionary D/COPYDATABASE: Attempting copy of block 2 which has 4096 bytes.
04-17 19:35:16.662 3459-3459/m.example.so55711282dictionary D/COPYDATABASE: Attempting copy of block 3 which has 4096 bytes.
04-17 19:35:16.662 3459-3459/m.example.so55711282dictionary D/COPYDATABASE: Finished copying Database dictionary.db from the assets folder, to /data/data/m.example.so55711282dictionary/databases/dictionary.db12288were copied, in 3 blocks of size 4096.
04-17 19:35:16.662 3459-3459/m.example.so55711282dictionary D/COPYDATABASE: All Streams have been flushed and closed.
04-17 19:35:16.689 3459-3459/m.example.so55711282dictionary I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@16011e94
04-17 19:35:16.689 3459-3459/m.example.so55711282dictionary I/System.out: 0 {
04-17 19:35:16.689 3459-3459/m.example.so55711282dictionary I/System.out: id=1
04-17 19:35:16.689 3459-3459/m.example.so55711282dictionary I/System.out: word=Apple
04-17 19:35:16.689 3459-3459/m.example.so55711282dictionary I/System.out: definition=Thing that drops from an Apple Tree.
04-17 19:35:16.689 3459-3459/m.example.so55711282dictionary I/System.out: }
04-17 19:35:16.689 3459-3459/m.example.so55711282dictionary I/System.out: 1 {
04-17 19:35:16.689 3459-3459/m.example.so55711282dictionary I/System.out: id=2
04-17 19:35:16.689 3459-3459/m.example.so55711282dictionary I/System.out: word=Bucket
04-17 19:35:16.689 3459-3459/m.example.so55711282dictionary I/System.out: definition=Hand held container with carrying hanlde.
04-17 19:35:16.689 3459-3459/m.example.so55711282dictionary I/System.out: }
04-17 19:35:16.689 3459-3459/m.example.so55711282dictionary I/System.out: 2 {
04-17 19:35:16.690 3459-3459/m.example.so55711282dictionary I/System.out: id=3
04-17 19:35:16.690 3459-3459/m.example.so55711282dictionary I/System.out: word=Yelllow
04-17 19:35:16.690 3459-3459/m.example.so55711282dictionary I/System.out: definition=A colour.
04-17 19:35:16.690 3459-3459/m.example.so55711282dictionary I/System.out: }
04-17 19:35:16.690 3459-3459/m.example.so55711282dictionary I/System.out: 3 {
04-17 19:35:16.690 3459-3459/m.example.so55711282dictionary I/System.out: id=4
04-17 19:35:16.690 3459-3459/m.example.so55711282dictionary I/System.out: word=Zebra
04-17 19:35:16.690 3459-3459/m.example.so55711282dictionary I/System.out: definition=A balck and white, horse-like animal.
04-17 19:35:16.690 3459-3459/m.example.so55711282dictionary I/System.out: }
04-17 19:35:16.690 3459-3459/m.example.so55711282dictionary I/System.out: <<<<<
Run 4.
The App is rerun (doesn't recopy the database and dumps the 4 rows)
Run 5.
The App is uninstalled and rerun (reflecting a new installation of the App when the Database version is at 2 (e.g. new download/install of the App from playstore)) :-
The Database (version with 4 rows) is copied and the 4 rows are dumped.
- Note that the The Database Version (as per the database file) is -666666666 message is included. This is shown when there is no database to be replaced (the number could easily be changed to suit).
Post a Comment for "On Upgrade Method Not Getting Called In Android Sqlite"