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

I have an Android app that uses a SQLite database and Active Android as ORM. On each app update I need to ship my database with new/updated data. This is what i've been doing

  1. I have a my_app.db database
  2. I make modifications on the rows, tables, etc. of the my_app.db
  3. I save the modified my_app.db as my_app_v2.db ( and so on )
  4. I replace the my_app.db file of the assets folder with the my_app_v2.db and set it as the default database
  5. I compile and run the program using the newly created my_app_v2.db

So when the user gets the app, it will be using the my_app_v2.db with new contents.

I know that Active Android supports migration scripts, but on each database update I need to add/update about 2000+ records. So for each database update I would need a migration script with 2000+ insert/update statements, it means that for 3+ consecutive upgrades the app would have to execute about 6000+ statements.

I want to know if my approach of replace the whole database with a new one is a bad practice and the migrations scripts should be prefered.

3 Answers

  1. Paul- Reply

    2019-11-16

    I am not sure you could apply this way on your app but here is what I am doing to retrieve new data from another database.

    For my apps I use a sync system that will daily check if a new database is available on GoogleDrive (in case the user is using different devices).

    When a new database backup is available (meaning I have to retrieve data for this device), I get back the database backup and attach it to the existing one by using:

    attach database database/path as new_db
    

    Then I just execute this command for every table in order to update the existing database with the records from the one I retrieved:

    INSERT OR REPLACE INTO table SELECT * FROM retrieved_database.table
    

    Of course it will replace all existing data, but this way I also handle the records that have been modified. This method avoids the full replacement of the existing database, I just run an integrity check at the end to be sure all is ok.

    This method is ok for me since I have a few tables and the data is light, it might be a bad idea for heavy databases.

  2. Patrick- Reply

    2019-11-16

    In my project, i used like 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 these code in your contest

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>