Android SQLite: replace a new database with a new one or use migration scripts

Question

I have an Android application that uses SQLite database and Active Android as ORM. In each application update I need to send my database with new / updated data. This is what I did

• I have my_app.db database
• I make changes to rows, tables, etc. my_app.db
• I save the modified my_app.db as my_app_v2.db (etc.)
• I replace the my_app.db file in the resource folder my_app_v2.db and set it as the default database
• I compile and run the program using the newly created my_app_v2.db

So, when the user receives the application, he will use my_app_v2.db with the new content.

I know that Active Android supports migration scenarios , but with every database update I need to add / update about 2000+ records. Therefore, for each database update, I will need a script migration with 2000+ insert / update instructions, which means that for 3+ consecutive updates the application would have to execute about 6000+ statements.

I want to know if my approach is to replace the entire database with a new one, it is bad practice, and migration scripts are recommended.

Answer 1

You do not need to do this (renaming the material or anything else)

You just need to change the version of your database and write the sql command to change the previous table to switch from version A to B.

Take a look at this link:

Android: updating the database version and adding a new table

Answer 2

I'm not sure that you can apply this method in your application, but here is what I do to get new data from another database.

For my applications, I use a synchronization system that will check daily whether a new database is available in GoogleDrive (in case the user uses different devices).

When a new database backup is available (which means I need to get data for this device), I return the database backup and attach it to the existing one using:

attach database database/path as new_db 

Then I just execute this command for each table to update the existing database using the records from the one I received:

 INSERT OR REPLACE INTO table SELECT * FROM retrieved_database.table 

Of course, it will replace all existing data, but in this way I also process records that have been changed. This method avoids the complete replacement of the existing database, I just run an integrity check at the end to make sure everything is in order.

This method is suitable for me, since I have several tables and the data is light, this might be a bad idea for heavy databases.

Answer 3

In my project, I used this

 public class DatabaseHelper extends SQLiteOpenHelper { private final static String TAG = DatabaseHelper.class.getSimpleName(); private static DatabaseHelper sInstance; private Context mContext; private static final String DATABASE_NAME = "xxxx"; private static final String DATABASE_NAME_OLD = "xxxx_old"; private static final int DATABASE_VERSION = 12; private String pathToSaveDBFile, absolutepathToSaveDBFile; private SQLiteDatabase db; private Cursor cursor; public static synchronized DatabaseHelper getInstance(Context mContext) { if (sInstance == null) { sInstance = new DatabaseHelper(mContext); } return sInstance; } /** * initialization constructor * * @param context */ private DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); this.mContext = context; pathToSaveDBFile = new StringBuffer(context.getFilesDir().getAbsolutePath()).append("/").append(DATABASE_NAME).toString(); absolutepathToSaveDBFile = new StringBuffer(context.getFilesDir().getAbsolutePath()).append("/").append(DATABASE_NAME_OLD).toString(); } /** * prepare database related process * * @throws IOException */ public void prepareDatabase() throws IOException { //boolean dbExist = checkDataBase(); if (checkDataBase()) { Log.d(TAG, "Database exists."); // int currentDBVersion = getVersionId(); if (DATABASE_VERSION > getVersionId()) { Log.d(TAG, "Database version is higher than old."); if (renameDatabase()) { Log.d(TAG, "renameDatabase() "); try { if (copyDataBase()) { deleteDb(); setVersionId(DATABASE_VERSION); } } catch (Exception e) { Log.e(TAG, e.getMessage()); } } } } else { try { /// copy db copyDataBase(); } catch (Exception e) { Log.e(TAG, e.getMessage()); } } } /** * db exist or not? * * @return db checked status */ private boolean checkDataBase() { Log.d(TAG, "checkDataBase()"); boolean checkDB = false; try { File file = new File(pathToSaveDBFile); checkDB = file.exists(); } catch (SQLiteException e) { Log.d(TAG, e.getMessage()); } Log.d(TAG, "checkDataBase: " + checkDB); return checkDB; } /** * db copying * * @return db copy status */ private Boolean copyDataBase() { try { Log.d(TAG, "copyDataBase()"); OutputStream os = new FileOutputStream(pathToSaveDBFile); InputStream is = mContext.getAssets().open("db/" + DATABASE_NAME); byte[] buffer = new byte[1024]; int length; while ((length = is.read(buffer)) > 0) { os.write(buffer, 0, length); } is.close(); os.flush(); os.close(); return true; } catch (IOException e) { e.getMessage(); return false; } } /** * db renaming * * @return boolean status */ private boolean renameDatabase() { try { Log.d(TAG, "renameDatabase: "); File from = new File(pathToSaveDBFile); File to = new File(absolutepathToSaveDBFile); if (from.renameTo(to)) { return true; } return false; } catch (Exception e) { e.getMessage(); return false; } } /** * * * @return boolen status */ private boolean revertBack_to_OlderName() { try { Log.d(TAG, "renameDatabase: "); File from = new File(absolutepathToSaveDBFile); File to = new File(pathToSaveDBFile); if (from.renameTo(to)) { return true; } return false; } catch (Exception e) { e.getMessage(); return false; } } /** * db deletion * * delete db */ public void deleteDb() { File file = new File(absolutepathToSaveDBFile); if (file.exists()) { file.delete(); Log.d(TAG, "Database deleted."); } } @Override public void onCreate(SQLiteDatabase sqLiteDatabase) { } @Override public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) { } @Override public synchronized void close() { db.close(); super.close(); } /** * get db version info * * @return version no */ private int getVersionId() { try { db = SQLiteDatabase.openDatabase(pathToSaveDBFile, null, SQLiteDatabase.OPEN_READONLY); String query = "SELECT " + AS_DB_VERSION_NUMBER + " FROM " + AS_DB_VERSION_TABLE; cursor = db.rawQuery(query, null); cursor.moveToFirst(); int v = cursor.getInt(0); cursor.close(); close(); return v; } catch (SQLiteException e) { e.getMessage(); return 0; } } /** * set db version no to * @param version * * @return status */ private boolean setVersionId(int version) { try { db = SQLiteDatabase.openDatabase(pathToSaveDBFile, null, SQLiteDatabase.OPEN_READWRITE); ContentValues values = new ContentValues(); values.put(AS_DB_VERSION_NUMBER, version); db.update(AS_DB_VERSION_NUMBER, values, AS_DB_VERSION_ID + " = 1", null); close(); return true; } catch (SQLiteException e) { e.getMessage(); return false; } } } 

you can use this code in your competition