Can't Copy Pre-created Db From Assets
Solution 1:
You have a number of issues, assuming that you want to replace an existing pre-existing database with another copy.
The issue you are facing is that as a database exists then the copy will not proceed i.e. the checkDatabase() will return true.
If you were to simply invoke the copyDatabase() then the database would be copied every time the App is run, which would be inefficient and destructive if the database can be modified by the user.
What you need to do is have an indicator, that can be tested, to see if the pre-existing database has been changed. There are various ways but the most likely/common way would be to utilise the SQLite user_version. This is an integer value and is frequently used to update the current database via the onUpgrade method.
As part of opening the database, the SQLiteOpenHelper (and therefore a subclass thereof) it compares the user_version stored in the database against the supplied version number (4th parameter to the SQLiteOpenHelper super call) and if the latter is greater than the value stored in the database then the onUpgrade method is called. (if the reverse then the onDowngrade method will be called and without it being coded an exception occurs).
The user_version can be set in the SQLite management tool user the SQL PRAGMA user_version = n.
Another issue is that from Android 9, the database is opened in WAL (Write-Ahead Logging) mode by default. The above code by using this.getReadableDatabase(); results in the -shm and -wal files being created. Their existence results in a trapped error (as they then do not match the copied database) that then results in the SQLiteOpenHelper creating an empty (theoretically usable database) basically wiping the copied database (I believe this is what happens).
The reason why this.getReadableDatabase(); has been used is that it get's around the issue that when there is no App data, the databases folder/directory doesn't exist and using the above creates it. The correct way is to create the databases directory/folder if it doesn't exist. As such the -wal and -shm files are not created.
The following is an example DatabseHelper that overcomes the issues and additionally allows modified versions of the pre-existing database to be copied based upon changing the user_version.
publicclassDBHelperV001extendsSQLiteOpenHelper {
publicstaticfinalStringDBNAME="test.db"; //<<<<<<<<<< obviously change accordingly//privatestaticint db_user_version, asset_user_version, user_version_offset = 60, user_version_length = 4;
privatestaticStringstck_trc_msg=" (see stack-trace above)";
privatestaticStringsqlite_ext_journal="-journal";
privatestaticStringsqlite_ext_shm="-shm";
privatestaticStringsqlite_ext_wal="-wal";
privatestaticintcopy_buffer_size=1024 * 8; //Copy data in 8k chucks, change if wanted.
SQLiteDatabase mDB;
/**
* Instantiate the DBHelper, copying the databse from the asset folder if no DB exists
* or if the user_version is greater than the user_version of the current database.
* NOTE The pre-existing database copied into the assets folder MUST have the user version set
* to 1 or greater. If the user_version in the assets folder is increased above the
*
* @param context
*/publicDBHelperV001(Context context) {
// Note get the version according to the asset file// avoid having to maintain the version number passedsuper(context, DBNAME, null, setUserVersionFromAsset(context,DBNAME));
if (!ifDbExists(context,DBNAME)) {
copyDBFromAssets(context, DBNAME,DBNAME);
} else {
setUserVersionFromAsset(context,DBNAME);
setUserVersionFromDB(context,DBNAME);
if (asset_user_version > db_user_version) {
copyDBFromAssets(context,DBNAME,DBNAME);
}
}
// Force open (and hence copy attempt) when constructing helper
mDB = this.getWritableDatabase();
}
@OverridepublicvoidonCreate(SQLiteDatabase db) {
}
@OverridepublicvoidonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
@OverridepublicvoidonDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
/**
* Check to see if the databse file exists
* @param context The Context
* @param dbname The databse name
* @return true id database file exists, else false
*/privatestaticbooleanifDbExists(Context context, String dbname) {
Filedb= context.getDatabasePath(dbname);
if (db.exists()) returntrue;
if (!db.getParentFile().exists()) {
db.getParentFile().mkdirs();
}
returnfalse;
}
/**
* set the db_user_version according to the user_version obtained from the current database file
* @param context The Context
* @param dbname The database (file) name
* @return The user_version
*/privatestaticintsetUserVersionFromDB(Context context, String dbname) {
Filedb= context.getDatabasePath(dbname);
InputStream is;
try {
is = newFileInputStream(db);
} catch (IOException e) {
thrownewRuntimeException("IOError Opening " + db.getPath() + " as an InputStream" + stck_trc_msg);
}
db_user_version = getUserVersion(is);
Log.d("DATABASEUSERVERSION","Obtained user_version from current DB, it is " + String.valueOf(db_user_version)); //TODO remove for live Appreturn db_user_version;
}
/**
* set the asset_user_version according to the user_version from the asset file
* @param context
* @param assetname
* @return
*/privatestaticintsetUserVersionFromAsset(Context context, String assetname) {
InputStream is;
try {
is = context.getAssets().open(assetname);
} catch (IOException e) {
e.printStackTrace();
thrownewRuntimeException("IOError Getting asset " + assetname + " as an InputStream" + stck_trc_msg);
}
asset_user_version = getUserVersion(is);
Log.d("ASSETUSERVERSION","Obtained user_version from asset, it is " + String.valueOf(asset_user_version)); //TODO remove for Live Appreturn asset_user_version;
}
/**
* Retrieve SQLite user_version from the provied InputStream
* @param is The InputStream
* @return the user_version
*/privatestaticintgetUserVersion(InputStream is) {
Stringioerrmsg="Reading DB header bytes(60-63) ";
int rv;
byte[] buffer = newbyte[user_version_length];
byte[] header = newbyte[64];
try {
is.skip(user_version_offset);
is.read(buffer,0,user_version_length);
ByteBufferbb= ByteBuffer.wrap(buffer);
rv = ByteBuffer.wrap(buffer).getInt();
ioerrmsg = "Closing DB ";
is.close();
return rv;
} catch (IOException e) {
e.printStackTrace();
thrownewRuntimeException("IOError " + ioerrmsg + stck_trc_msg);
}
}
/**
* Copy the database file from the assets
* Note backup of existing files may not be required
* @param context The Context
* @param dbname The database (file)name
* @param assetname The asset name (may therefore be different but )
*/privatestaticvoidcopyDBFromAssets(Context context, String dbname, String assetname) {
Stringtag="COPYDBFROMASSETS";
Log.d(tag,"Copying Database from assets folder");
Stringbackup_base="bkp_" + String.valueOf(System.currentTimeMillis());
Stringioerrmsg="Opening Asset " + assetname;
// Prepare Files that could be usedFiledb= context.getDatabasePath(dbname);
Filedbjrn=newFile(db.getPath() + sqlite_ext_journal);
Filedbwal=newFile(db.getPath() + sqlite_ext_wal);
Filedbshm=newFile(db.getPath() + sqlite_ext_shm);
Filedbbkp=newFile(db.getPath() + backup_base);
Filedbjrnbkp=newFile(db.getPath() + backup_base);
Filedbwalbkp=newFile(db.getPath() + backup_base);
Filedbshmbkp=newFile(db.getPath() + backup_base);
byte[] buffer = newbyte[copy_buffer_size];
intbytes_read=0;
inttotal_bytes_read=0;
inttotal_bytes_written=0;
// Backup existing sqlite filesif (db.exists()) {
db.renameTo(dbbkp);
dbjrn.renameTo(dbjrnbkp);
dbwal.renameTo(dbwalbkp);
dbshm.renameTo(dbshmbkp);
}
// ALWAYS delete the additional sqlite log files
dbjrn.delete();
dbwal.delete();
dbshm.delete();
//Attempt the copytry {
ioerrmsg = "Open InputStream for Asset " + assetname;
InputStreamis= context.getAssets().open(assetname);
ioerrmsg = "Open OutputStream for Databse " + db.getPath();
OutputStreamos=newFileOutputStream(db);
ioerrmsg = "Read/Write Data";
while((bytes_read = is.read(buffer)) > 0) {
total_bytes_read = total_bytes_read + bytes_read;
os.write(buffer,0,bytes_read);
total_bytes_written = total_bytes_written + bytes_read;
}
ioerrmsg = "Flush Written data";
os.flush();
ioerrmsg = "Close DB OutputStream";
os.close();
ioerrmsg = "Close Asset InputStream";
is.close();
Log.d(tag,"Databsse copied from the assets folder. " + String.valueOf(total_bytes_written) + " bytes were copied.");
// Delete the backups
dbbkp.delete();
dbjrnbkp.delete();
dbwalbkp.delete();
dbshmbkp.delete();
} catch (IOException e) {
e.printStackTrace();
thrownewRuntimeException("IOError attempting to " + ioerrmsg + stck_trc_msg);
}
}
}
Example usage
Consider the following assets files (sqlite databases) (warning as they are the App would fail) :-
So there are two databases (identical bar the user_version as set using PRAGMA user_version = 1 and PRAGMA user_version = 2 respectively/according to the file names)
For the brand new, first time run App (i.e. uninstalled) then file test.dbV1 is renamed to test.db and the following activity is used :-
publicclassMainActivityextendsAppCompatActivity {
DBHelperV001 mDbhlpr;
@OverrideprotectedvoidonCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDbhlpr = newDBHelperV001(this);
DatabaseUtils.dumpCursor(
mDbhlpr.getWritableDatabase().query(
"sqlite_master",
null,null,null,null,null,null
)
);
}
}
- This simply instantiates the Database Helper (which will copy or use the database) and then dumps the sqlite_master table.
The log contains :-
04-02 12:55:36.258 644-644/aaa.so55441840 D/ASSETUSERVERSION: Obtained user_version from asset, it is 1
04-02 12:55:36.258 644-644/aaa.so55441840 D/COPYDBFROMASSETS: Copying Database from assets folder
04-02 12:55:36.262 644-644/aaa.so55441840 D/COPYDBFROMASSETS: Databsse copied from the assets folder. 69632 bytes were copied.
04-02 12:55:36.265 644-644/aaa.so55441840 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@d121f9c
04-02 12:55:36.265 644-644/aaa.so55441840 I/System.out: 0 {
04-02 12:55:36.265 644-644/aaa.so55441840 I/System.out: type=table
04-02 12:55:36.265 644-644/aaa.so55441840 I/System.out: name=android_metadata
04-02 12:55:36.265 644-644/aaa.so55441840 I/System.out: tbl_name=android_metadata
04-02 12:55:36.265 644-644/aaa.so55441840 I/System.out: rootpage=3
04-02 12:55:36.265 644-644/aaa.so55441840 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
04-02 12:55:36.266 644-644/aaa.so55441840 I/System.out: }
04-02 12:55:36.266 644-644/aaa.so55441840 I/System.out: 1 {
04-02 12:55:36.266 644-644/aaa.so55441840 I/System.out: type=table
04-02 12:55:36.266 644-644/aaa.so55441840 I/System.out: name=shops
..........
When the new version of the DB is introduced, which has a user_version of 2
- i.e. test.db which was test.dbV1 is renamed to test.dbV1 AND then,
- (effectively deleteing it)
- test.dbV2 is then renamed test.db
- (effectively introducing the new asset file) then :-
And the App is then rerun then the log contains :-
04-0213:04:25.044758-758/? D/ASSETUSERVERSION: Obtained user_version from asset, it is204-0213:04:25.046758-758/? D/ASSETUSERVERSION: Obtained user_version from asset, it is204-0213:04:25.046758-758/? D/DATABASEUSERVERSION: Obtained user_version from current DB, it is104-0213:04:25.047758-758/? D/COPYDBFROMASSETS: Copying Database from assets folder
04-0213:04:25.048758-758/? D/COPYDBFROMASSETS: Databsse copied from the assets folder. 69632 bytes were copied.
04-0213:04:25.051758-758/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@25012da5
04-0213:04:25.052758-758/? I/System.out: 0 {
04-0213:04:25.052758-758/? I/System.out: type=table
04-0213:04:25.052758-758/? I/System.out: name=android_metadata
04-0213:04:25.052758-758/? I/System.out: tbl_name=android_metadata
04-0213:04:25.052758-758/? I/System.out: rootpage=304-0213:04:25.052758-758/? I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
04-02 13:04:25.052 758-758/? I/System.out: }
04-02 13:04:25.052 758-758/? I/System.out: 1 {
04-0213:04:25.052758-758/? I/System.out: type=table
04-0213:04:25.052758-758/? I/System.out: name=shops
Lastly, with a subsequent run i.e. no updated asset, the log shows :-
04-02 13:05:50.197 840-840/aaa.so55441840 D/ASSETUSERVERSION: Obtained user_version from asset, it is 2
04-02 13:05:50.198 840-840/aaa.so55441840 D/ASSETUSERVERSION: Obtained user_version from asset, it is 2
04-02 13:05:50.198 840-840/aaa.so55441840 D/DATABASEUSERVERSION: Obtained user_version from current DB, it is 2
04-02 13:05:50.201 840-840/aaa.so55441840 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@d121f9c
04-02 13:05:50.202 840-840/aaa.so55441840 I/System.out: 0 {
04-02 13:05:50.202 840-840/aaa.so55441840 I/System.out: type=table
04-02 13:05:50.202 840-840/aaa.so55441840 I/System.out: name=android_metadata
04-02 13:05:50.202 840-840/aaa.so55441840 I/System.out: tbl_name=android_metadata
04-02 13:05:50.202 840-840/aaa.so55441840 I/System.out: rootpage=3
04-02 13:05:50.202 840-840/aaa.so55441840 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
04-02 13:05:50.202 840-840/aaa.so55441840 I/System.out: }
04-02 13:05:50.202 840-840/aaa.so55441840 I/System.out: 1 {
04-02 13:05:50.202 840-840/aaa.so55441840 I/System.out: type=table
04-02 13:05:50.202 840-840/aaa.so55441840 I/System.out: name=shops
i.e. no copy done as the asset is effectively the same
Post a Comment for "Can't Copy Pre-created Db From Assets"