Db File in Assets Folder. Will It Be Updated

DB File in Assets Folder. Will it be Updated?

You can't replace database in onUpgrade() because in this method the database is already in use. You have to do it before the database is open, like in a constructor of your DatabaseHelper. As you can't use onUpgrade(), you have to manage database versioning by yourself. Using SharedPreferences is a good way for it. You check if your database exists (if it is already copied from assets directory) and check the version if database exists. Now you can delete old database and copy new one from assets.
See implementation below.

To mark your updated application is published with new database in assets just incerment DATABASE_VERSION constant.

private static class DatabaseHelper extends SQLiteOpenHelper {

private static final String DATABASE_NAME = "database.db";
private static final int DATABASE_VERSION = 1;
private static final String SP_KEY_DB_VER = "db_ver";
private final Context mContext;

public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
mContext = context;
initialize();
}

/**
* Initializes database. Creates database if doesn't exist.
*/
private void initialize() {
if (databaseExists()) {
SharedPreferences prefs = PreferenceManager
.getDefaultSharedPreferences(mContext);
int dbVersion = prefs.getInt(SP_KEY_DB_VER, 1);
if (DATABASE_VERSION != dbVersion) {
File dbFile = mContext.getDatabasePath(DATABASE_NAME);
if (!dbFile.delete()) {
Log.w(TAG, "Unable to update database");
}
}
}
if (!databaseExists()) {
createDatabase();
}
}

/**
* Returns true if database file exists, false otherwise.
* @return
*/
private boolean databaseExists() {
File dbFile = mContext.getDatabasePath(DATABASE_NAME);
return dbFile.exists();
}

/**
* Creates database by copying it from assets directory.
*/
private void createDatabase() {
String parentPath = mContext.getDatabasePath(DATABASE_NAME).getParent();
String path = mContext.getDatabasePath(DATABASE_NAME).getPath();

File file = new File(parentPath);
if (!file.exists()) {
if (!file.mkdir()) {
Log.w(TAG, "Unable to create database directory");
return;
}
}

InputStream is = null;
OutputStream os = null;
try {
is = mContext.getAssets().open(DATABASE_NAME);
os = new FileOutputStream(path);

byte[] buffer = new byte[1024];
int length;
while ((length = is.read(buffer)) > 0) {
os.write(buffer, 0, length);
}
os.flush();
SharedPreferences prefs = PreferenceManager
.getDefaultSharedPreferences(mContext);
SharedPreferences.Editor editor = prefs.edit();
editor.putInt(SP_KEY_DB_VER, DATABASE_VERSION);
editor.commit();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

@Override
public void onCreate(SQLiteDatabase db) {
}

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

How to Update Database from Assets Folder in App

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

  • How to read version number from a database file in Android which is placed in asset folder

  • Updating a prepopulated database

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.

  1. Determines the filenames and importantly makes the databases directory if it doesn't exist according to the parameters passed.
  2. 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.
  3. If the database file exists it gets the User_Version from the database file.
  4. 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 :-

  1. instantiates the DBHelper.
  2. gets an SQLiteDatabase object via the DBHelper.
  3. extracts the Schema from the database (i.e. gets all rows from the sqlite_master table)
  4. 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 :-

Sample Image

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 :-

Sample Image

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 android.database.sqlite.SQLiteCursor@cb3bb46
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 android.database.sqlite.SQLiteCursor@5dfcb21
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)) :-

Sample Image

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 android.database.sqlite.SQLiteCursor@cb3bb46
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.

Android : Update database in assets folder

What I want to know is how could I update the database which is in the assets folder after I inserting data.

Editing the Assets folder in runtime is not possible. source.

This Loads the database from the asset folder:

// Copies DB from assests
private void copyDataBase() throws IOException {
InputStream mInput = mContext.getAssets().open(DB_NAME);
String outFileName = DB_PATH + DB_NAME;
OutputStream mOutput = new FileOutputStream(outFileName);
byte[] mBuffer = new byte[1024];
int mLength;
while ((mLength = mInput.read(mBuffer)) > 0) {
mOutput.write(mBuffer, 0, mLength);
}
mOutput.flush();
mOutput.close();
mInput.close();
}

See this answer for updating your db from assets.

Copy database from assets folder to android , updating it and copying it back to asset folder

As the assets folder is part of the APK, it is read only. You would have to regenerate the APK using the updated database.

Accessing the updated database itself could be an issue as the App's data is protected in devices that aren't rooted.

If you are using a device connected to Android Studio then you may be able to copy the updated database file(s) using device explorer into the asses folder. Noting that if the database uses Write-Ahead logging (WAL) that you may have the added complication of the -wal and -shm files, if there are any uncommitted changes. Ideally you would ensure that the database is closed, this should commit all changes, the -wal file should should then be 0 bytes or not exist in which case it is safe to just copy the database file itself.

How to upgrade SQLite database in Assets folder in

Assets are read-only at runtime. You cannot modify the assets in an Android app from within the Android app. The only way to update assets is to ship a new version of the APK with the new assets.

SQLite DB created from Assets Folder update issue

You need to use the built-in lifecycle methods for onCreate() and onUpdate().

Something like this:

public class DataBaseHelper extends SQLiteOpenHelper {
private static String TAG = "DataBaseHelper"; // Tag just for the LogCat
// window
// destination path (location) of our database on device
private static final int DATABASE_VERSION = 3;
private static String DB_PATH = "";
private static String DB_NAME = "";// Database name
private SQLiteDatabase mDataBase;
private final Context mContext;

public DataBaseHelper(Context context, String nomeDB) {
super(context, nomeDB, null, DATABASE_VERSION);
DB_NAME = nomeDB;
DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
this.mContext = context;
}

// Copy DB from assests
private void copyDataBase() throws IOException {
InputStream mInput = mContext.getAssets().open(DB_NAME);
String outFileName = DB_PATH + DB_NAME;
OutputStream mOutput = new FileOutputStream(outFileName);
byte[] mBuffer = new byte[1024];
int mLength;
while ((mLength = mInput.read(mBuffer)) > 0) {
mOutput.write(mBuffer, 0, mLength);
}
mOutput.flush();
mOutput.close();
mInput.close();
}

public boolean openDataBase() throws SQLException {
String mPath = DB_PATH + DB_NAME;
mDataBase = SQLiteDatabase.openDatabase(mPath, null,
SQLiteDatabase.CREATE_IF_NECESSARY);
return mDataBase != null;
}

@Override
public synchronized void close() {
if (mDataBase != null)
mDataBase.close();
super.close();
}

/**
* This method is called when the app doesn't have a database and a new one needs to be created
*/
@Override
public void onCreate(SQLiteDatabase arg0) {
try {
// Copy the db from assests
copyDataBase();
Log.e(TAG, "database created");
} catch (IOException mIOException) {
Log.e(TAG, mIOException.toString());
throw mIOException;
}
}

/**
* This method is called when the app's database version is < the value passed into the constructor
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
try {
// delete existing?

// Copy the db from assests
copyDataBase();
Log.e(TAG, "database updated");
} catch (IOException mIOException) {
Log.e(TAG, mIOException.toString());
throw mIOException;
}
}

}

Assets folder not upgraded when installing new app

Solved as I stated in the edit: copy the file from the assets folder with a different name (in my case with a timestamp).

Reading a database from the assets folder

To utilise a packaged database (i.e one included as an asset) for full use the database must be unzipped (automatic) and copied to a suitable location (most often data/data/<package_name>/databases/<database_name> where <package_name> and <database_name> will be according to the App's package name and the database name respectiviely).

To "package" the database is should be included in the assets folder and preferably to a databases folder (required if using SQLiteAssetHelper without modification).

Additionally the copy must be done before actually opening the database after which it can then be opened.

Utilising SQLiteAssetHelper

  1. The very first step is to create the database to be packaged, this is not going to be covered, as there are numerous tools available. For this example the database is a file named test.db

  2. You should then create your project in this case the Project has been called DBtest with a Compnay Domian as com.DBtest so the package name is dbtest.com.dbtest.

  3. The next stage is to copy the database into the assets folder.

    1. Creating the assets folder in the src/main folder, if it doesn't already exist.
    2. Creating the databases"" folder in the **assets folder, if it doesn't already exist.
    3. Copying the database file (test.db in this example) into the database folder.

      • Sample Image
  4. The next stage is to setup the project to utilise the SQLiteAssetHelper by including it in the App's build.gradle.

    1. Edit the build.gradle in the App folder.
    2. Add the line implementation 'com.readystatesoftware.sqliteasset:sqliteassethelper:2.0.1' within the dependencies section.
    3. Click Sync Now

Sample Image



  1. Create a class that is a subclass of the newly/now available SQLiteAssethelper class. For this exercise it will be called DBHelper.

    1. Right Click the MainActivity java class, select New and then Java Class.
    2. In the Name field input DBHelper.
    3. In the SuperClass field start typing SQLiteAsset (by now the SQliteAssetHelper class will be selectable), so select it. It should resolve to be:-
    4. Click OK.
      Sample Image
  2. Create the constructor for the DBHelper class along the lines of

:-

public class DBHelper extends SQLiteAssetHelper {

public static final String DBNAME = "test.db"; //<<<< must be same as file name
public static final int DBVERSION = 1;

public DBHelper(Context context) {
super(context,DBNAME,null,DBVERSION);
}
}

  1. Create an instance of the DBHelper and then access the database.

    1. Note for ease another class called CommonSQLiteUtilities, as copied from Are there any methods that assist with resolving common SQLite issues?
    2. Create an instance of the DBHelper cclass using something along the lines of

      • DBHelper mDBHlpr = new DBHelper(this);
    3. using the CommonSQLiteUtilities the database was accessed using :-

      • CommonSQLiteUtilities.logDatabaseInfo(mDBHlpr.getWritableDatabase());
    4. The MainActivity in full became

:-

public class MainActivity extends AppCompatActivity {

@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);

DBHelper mDBHlpr = new DBHelper(this);
CommonSQLiteUtilities.logDatabaseInfo(mDBHlpr.getWritableDatabase());
}
}

The result was a successful run logging :-

04-11 06:12:55.091 1401-1401/dbtest.com.dbtest W/SQLiteAssetHelper: copying database from assets...
database copy complete
04-11 06:12:55.123 1401-1401/dbtest.com.dbtest I/SQLiteAssetHelper: successfully opened database test.db
04-11 06:12:55.127 1401-1401/dbtest.com.dbtest D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/dbtest.com.dbtest/databases/test.db
Database Version = 1
Table Name = mytable Created Using = CREATE TABLE mytable (
_id INTEGER PRIAMRY KEY,
mydata TEXT,
inserted INTEGER DEFAULT CURRENT_TIMESTAMP
)
Table = mytable ColumnName = _id ColumnType = INTEGER PRIAMRY KEY Default Value = null PRIMARY KEY SEQUENCE = 0
Table = mytable ColumnName = mydata ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
Table = mytable ColumnName = inserted ColumnType = INTEGER Default Value = CURRENT_TIMESTAMP PRIMARY KEY SEQUENCE = 0
Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
  • The first two lines are from SQliteAssethelper, the rest are from the logDatabaseInfo method of the CommonSQLiteUtilities class.
  • On subsequnt runs the database will not be copied as it already exists.


Related Topics



Leave a reply



Submit