How to Update Database from Assets Folder in App
Posted By: Anonymous
How to Update database from assets folder onUpgrade()
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
try {
File f = new File(mContext.getApplicationInfo().dataDir + DB_PATH_SUFFIX + DATABASE_NAME);
if (f.exists())
f.delete();
openDataBase();
} catch (Exception e) {
System.out.println("=== onUpgrade Exception : " + e.getMessage());
e.printStackTrace();
}
}
above code is delete old database and copy new databse from assets folder, but it will not working, it will give error like :
attempt to write a readonly database (code 1032 SQLITE_READONLY_DBMOVED)
Solution
As onUpgrade is invoked when opening the database (In fact the database itself has already been opened (hence why it is passed to the onUpgrade method)) you will have nothing but issues trying to delete the database in the onUpgrade method as it is intended for upgrading the database though SQL (such as ALTER, CREATE, DROP INSERT etc).
If you have a new asset file then you need some way of detecting the new asset file and replacing the database file before it is opened by the database helper (much the same as when copying the database from the asset file initially).
These answers may assist
Working Example
Here’s an example that uses the SQlite User_Version (i.e. the version number as used by a Database Helper (subclass of SQLiteOpenHelper)).
It is based upon two classes the AssetHanlder class does all the work of handling whether the asset file is copied or not and doing the copy is necessary. The other class is a typical Database Helper with a subtle difference in that it instantiates a AssetHandler allowing it to do it’s business.
- Determines the filenames and importantly makes the databases directory if it doesn’t exist according to the parameters passed.
- Reads the first 64 bytes of the asset file (part of the SQLite Header). It extracts the 4 bytes from offset 60-63 i.e. the User_Version.
- If the database file exists it gets the User_Version from the database file.
- If the database doesn’t exist or if the asset’s User_Version is greater than the current database’s User_version it will copy the asset file after renaming the database file, if it exists, using a prefix of renamed_
- Note this has been hastily put together with limited testing so should be taken as potentially requiring improvements.
heres’ the Assethandler :-
public class AssetHandler {
private static final String DB_RENAME_PREFIX = "renamed_";
private int buffer_size = 4096;
/* Asset File related variables */
private int assetSQLiteVersion = -1;
private String assetFileName;
private byte[] assetSQLiteHeader = new byte[64];
/* Database (current) File related variables */
private int dbSQLiteVersion = -1;
private byte[] dbSQLiteHeader = new byte[64];
private final String databaseFileName;
private File databaseFile;
private boolean databaseExists = false;
private String databasePath;
private final Context context;
private boolean showStackTrace = false;
public AssetHandler(
Context context, /* Context */
String assetFileName, /* Name of the asset file (include subfolder if not in the assets folder) */
String databaseFileName, /* the name of the database aka the name of the database file */
/* NOTE typically assetFile name (less any sub folders) will be the same as the database file name*/
boolean performCopy, /* true if you want the copy to be performed */
boolean showStackTrace /* true if you want stack trace*/
/* NOTE All IO exceptions are trapped */
) {
this.context = context;
this.showStackTrace = showStackTrace;
this.assetFileName = assetFileName;
this.databaseFileName = databaseFileName;
this.databaseFile = context.getDatabasePath(databaseFileName);
this.databasePath = databaseFile.getPath();
this.databaseExists = databaseFile.exists();
if (!databaseExists) {
databaseFile.getParentFile().mkdirs();
}
/* Get the database version number */
InputStream assetFileInputStream;
try {
assetFileInputStream = context.getAssets().open(assetFileName);
assetFileInputStream.read(assetSQLiteHeader,0,64);
this.assetSQLiteVersion = getVersionFromHeader(assetSQLiteHeader);
assetFileInputStream.close();
Log.d("ASSETHANDLER","Asset SQLite Version Number is " + String.valueOf(assetSQLiteVersion));
} catch (IOException e) {
if (showStackTrace) {
e.printStackTrace();
}
}
/* Get the database file version number */
if (databaseExists) {
InputStream dbFileInputStream;
try {
dbFileInputStream = new FileInputStream(databasePath);
dbFileInputStream.read(dbSQLiteHeader, 0, 64);
this.dbSQLiteVersion = getVersionFromHeader(dbSQLiteHeader);
dbFileInputStream.close();
Log.d("ASSETHANDLER","Current Database SQLite Version Number is " + String.valueOf(dbSQLiteVersion));
} catch (IOException e) {
if (showStackTrace) {
e.printStackTrace();
}
}
} else {
Log.d("ASSETHANDLER","Database does not exist");
}
/* If the asset version number is greater than the database version number
or if the database does not exist copy the database from the asset
*/
if (performCopy && (assetSQLiteVersion > dbSQLiteVersion || !databaseExists)) {
Log.d("ASSETHANDLER","Initiating Copy of asset " + assetFileName + " to database " + databasePath);
performCopy();
} else {
Log.d("ASSETHANDLER",
"Copy not being performed as asset version (" + String.valueOf(assetSQLiteVersion) + ") = " +
"database version(" + String.valueOf(dbSQLiteVersion) + ")");
}
}
/* allow the retrieval of the version numbers and also the headers */
public int getAssetSQLiteVersionNumber() {
return this.assetSQLiteVersion;
}
public int getDbSQLiteVersion() {
return this.dbSQLiteVersion;
}
public byte[] getAssetSQLiteHeader() {
return this.assetSQLiteHeader;
}
public byte[] getDbSQLiteHeader() {
return this.dbSQLiteHeader;
}
/* Get the version number from the header */
private int getVersionFromHeader(byte[] header) {
byte[] versionNumber = new byte[]{0,0,0,0};
for(int i=60;i < 64;i++) {
versionNumber[i-60] = header[i];
}
return ByteBuffer.wrap(versionNumber).getInt();
}
/* Rename the database file */
private void renameDatabaseFile(String prefix) {
if (databaseFile.exists()) {
File newdbName = new File(databaseFile.getParent() + File.separator + prefix + databaseFile.getName());
if (newdbName.exists()) {
newdbName.delete();
}
databaseFile.renameTo(new File(databasePath + File.pathSeparator + prefix + databaseFile.getName()));
}
}
/* Copy the asset database to the default location */
private void performCopy() {
renameDatabaseFile(DB_RENAME_PREFIX);
byte[] buffer = new byte[buffer_size];
int read_count = 0;
int write_count = 0;
try {
InputStream is = context.getAssets().open(assetFileName);
OutputStream os = new FileOutputStream(databasePath);
int length;
while ((length=is.read(buffer))>0){
read_count++;
os.write(buffer, 0, length);
write_count++;
}
os.flush();
is.close();
os.close();
} catch (IOException e) {
if (showStackTrace) {
e.printStackTrace();
}
}
}
}
Here’s the Database Helper DBHelper used for testing that utilises AssetHandler :-
class DBHelper extends SQLiteOpenHelper {
private static final String DBNAME = "mydb";
private static int DBVERSION;
private static volatile DBHelper instance;
private static AssetHandler assetHandler;
private DBHelper(@Nullable Context context) {
super(context, DBNAME, null, DBVERSION);
}
public static DBHelper getInstance(Context context) {
/* instantiate the AsssetHandler if not instantiated */
/* aka only do this first time database is accessed when the App is run */
if (assetHandler == null) {
assetHandler = new AssetHandler(context, DBNAME, DBNAME, true, true);
DBVERSION = assetHandler.getAssetSQLiteVersionNumber(); /* Need to make the version number same as the asset version number */
}
/* return singleton DBHelper instance */
if (instance == null) {
instance = new DBHelper(context);
}
return instance;
}
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
super.onDowngrade(db, oldVersion, newVersion);
Log.e("ONDOWNGRADE","The onDowngrade method was called. This should not be called. Are the versions correct?");
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
Log.e("ONUPGRADE","The onUpgrade method was called. This should not be called. Are the versions correct?");
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
}
}
To test the following was used in an activity MainActivity
public class MainActivity extends AppCompatActivity {
DBHelper dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper = DBHelper.getInstance(this);
SQLiteDatabase db = dbHelper.getWritableDatabase();
Cursor csr = db.query("sqlite_master",null,null,null,null,null,null);
DatabaseUtils.dumpCursor(csr);
}
}
this :-
- instantiates the DBHelper.
- gets an SQLiteDatabase object via the DBHelper.
- extracts the Schema from the database (i.e. gets all rows from the sqlite_master table)
- dumps (outputs to the log) the Cursor. i.e. shows the schema.
Results
In the assets folder there are two copies of the same database but with different user_version numbers :-
As can be seen neither is named mydb which is what is expected.
Run 1
This is run with no existing database nor an asset (of the correct name). Two exceptions are trapped but the fails with a java.lang.IllegalArgumentException: Version must be >= 1, was -1 exception (this is relatively clean as no (empty) database is created.)
The two trapped exceptions (shown if showStackTrace is true) are both java.io.FileNotFoundException: mydb because there is no asset file (the first when trying to get the version number, the other trying to copy because the database doesn’t exist).
Two lines are written to the log:-
D/ASSETHANDLER: Database does not exist
D/ASSETHANDLER: Initiating Copy of asset mydb to database /data/user/0/a.a.so67803713javaupdateassetdb/databases/mydb
Run 2
mydb_V1 is renamed to mydb :-
and the App is run (no changes made to the App) and the Log shows :-
2021-06-03 22:47:40.691 D/ASSETHANDLER: Asset SQLite Version Number is 1
2021-06-03 22:47:40.692 D/ASSETHANDLER: Database does not exist
2021-06-03 22:47:40.692 D/ASSETHANDLER: Initiating Copy of asset mydb to database /data/user/0/a.a.so67803713javaupdateassetdb/databases/mydb
2021-06-03 22:47:40.716 I/System.out: >>>>> Dumping cursor [email protected]
2021-06-03 22:47:40.717 I/System.out: 0 {
2021-06-03 22:47:40.718 I/System.out: type=table
2021-06-03 22:47:40.718 I/System.out: name=android_metadata
2021-06-03 22:47:40.718 I/System.out: tbl_name=android_metadata
2021-06-03 22:47:40.718 I/System.out: rootpage=3
2021-06-03 22:47:40.718 I/System.out: sql=CREATE TABLE android_metadata (locale TEXT)
2021-06-03 22:47:40.718 I/System.out: }
2021-06-03 22:47:40.718 I/System.out: 1 {
2021-06-03 22:47:40.718 I/System.out: type=table
2021-06-03 22:47:40.718 I/System.out: name=shops
2021-06-03 22:47:40.718 I/System.out: tbl_name=shops
2021-06-03 22:47:40.718 I/System.out: rootpage=4
2021-06-03 22:47:40.718 I/System.out: sql=CREATE TABLE shops (_id INTEGER PRIMARY KEY , shoporder INTEGER DEFAULT 1000 , shopname TEXT , shopstreet TEXT , shopcity TEXT , shopstate TEXT , shopnotes TEXT )
2021-06-03 22:47:40.718 I/System.out: }
2021-06-03 22:47:40.718 I/System.out: 2 {
....
So the asset is copied and the database now exists.
Run 3
Nothing is changed and the App is rerun. The log includes:-
2021-06-03 22:51:10.291 D/ASSETHANDLER: Asset SQLite Version Number is 1
2021-06-03 22:51:10.291 D/ASSETHANDLER: Current Database SQLite Version Number is 1
2021-06-03 22:51:10.291 D/ASSETHANDLER: Copy not being performed as asset version (1) = database version(1)
2021-06-03 22:51:10.295 I/System.out: >>>>> Dumping cursor [email protected]
2021-06-03 22:51:10.296 I/System.out: 0 {
2021-06-03 22:51:10.296 I/System.out: type=table
2021-06-03 22:51:10.296 I/System.out: name=android_metadata
....
So no copy is done as the asset and database versions are the same.
Run 4
The mydb asset file is renamed to mydb_V1 and mydb_V2 is renamed to mydb (reflecting a new APK being distributed). Note that the user_version is 2 (set using PRAGMA user_version = 2; via an SQLite Tool (I used DB Browser for SQLite)) :-
Again the App is rerun. The log includes:-
2021-06-03 22:59:12.311 D/ASSETHANDLER: Asset SQLite Version Number is 2
2021-06-03 22:59:12.311 D/ASSETHANDLER: Current Database SQLite Version Number is 1
2021-06-03 22:59:12.311 D/ASSETHANDLER: Initiating Copy of asset mydb to database /data/user/0/a.a.so67803713javaupdateassetdb/databases/mydb
2021-06-03 22:59:12.325 I/System.out: >>>>> Dumping cursor [email protected]
2021-06-03 22:59:12.326 I/System.out: 0 {
2021-06-03 22:59:12.326 I/System.out: type=table
2021-06-03 22:59:12.326 I/System.out: name=android_metadata
So the updated asset file has been applied.
Run 5
The App is rerun without any changes. The log includes:-
2021-06-03 23:02:19.485 D/ASSETHANDLER: Asset SQLite Version Number is 2
2021-06-03 23:02:19.485 D/ASSETHANDLER: Current Database SQLite Version Number is 2
2021-06-03 23:02:19.485 D/ASSETHANDLER: Copy not being performed as asset version (2) = database version(2)
So everything is as expected.
Answered By: Anonymous
Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.
