androidsqliteandroid-roomandroid-room-prepackageddatabase

createFromAsset Migration but keep specific Columns

I have kind of a quiz app and I have a database with all the questions in tables, for each question there is a column solved that I update if the answer was correct, so I can filter with SQL WHERE to only show unsolved questions. Now every once in a while I have to correct typos in the questions or might want to add some new ones, so

How do I employ the corrected database (questions.db) from the assets to the saved one on the user device while keeping the solved columns?

I thought of and tried the following things without success:

So in essence, this may be inspiration for the Room dev team, I would like to have a proper migration strategy for createFromAsset with ability to specify certain columns/tables to be kept. Thanks for your great work so far, I really enjoy using Android Jetpack and Room especially! Also, I'm happy about any workaround I could employ to resolve this issue :)

Solution

  • I believe the following does what you want

    @Database(version = DatabaseConstants.DBVERSION, entities = {Question.class})
public abstract class QuestionDatabase extends RoomDatabase {

    static final String DBNAME = DatabaseConstants.DBNAME;

    abstract QuestionDao questionsDao();

    public static QuestionDatabase getInstance(Context context) {
        copyFromAssets(context,false);
        if (getDBVersion(context,DatabaseConstants.DBNAME) < DatabaseConstants.DBVERSION) {
            copyFromAssets(context,true);
        }
        return Room.databaseBuilder(context,QuestionDatabase.class,DBNAME)
                .addCallback(callback)
                .allowMainThreadQueries()
                .addMigrations(Migration_1_2)
                .build();
    }

    private static RoomDatabase.Callback callback = new Callback() {
        @Override
        public void onCreate(@NonNull SupportSQLiteDatabase db) {
            super.onCreate(db);
        }

        @Override
        public void onOpen(@NonNull SupportSQLiteDatabase db) {
            super.onOpen(db);
        }

        @Override
        public void onDestructiveMigration(@NonNull SupportSQLiteDatabase db) {
            super.onDestructiveMigration(db);
        }
    };

    private static Migration Migration_1_2 = new Migration(1, 2) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
        }
    };

    private static boolean doesDatabaseExist(Context context) {
        if (new File(context.getDatabasePath(DBNAME).getPath()).exists()) return true;
        if (!(new File(context.getDatabasePath(DBNAME).getPath()).getParentFile()).exists()) {
            new File(context.getDatabasePath(DBNAME).getPath()).getParentFile().mkdirs();
        }
        return false;
    }

    private static void copyFromAssets(Context context, boolean replaceExisting) {
        boolean dbExists = doesDatabaseExist(context);
        if (dbExists && !replaceExisting) return;
        //First Copy
        if (!replaceExisting) {
            copyAssetFile(context);
            return;
        }
        //Subsequent Copies

        File originalDBPath = new File(context.getDatabasePath(DBNAME).getPath());
        // Open and close the original DB so as to checkpoint the WAL file
        SQLiteDatabase originalDB = SQLiteDatabase.openDatabase(originalDBPath.getPath(),null,SQLiteDatabase.OPEN_READWRITE);
        originalDB.close();

        //1. Rename original database
        String preservedDBName = "preserved_" + DBNAME;
        File preservedDBPath = new File (originalDBPath.getParentFile().getPath() + preservedDBName);
        (new File(context.getDatabasePath(DBNAME).getPath()))
                .renameTo(preservedDBPath);

        //2. Copy the replacement database from the assets folder
        copyAssetFile(context);

        //3. Open the newly copied database
        SQLiteDatabase copiedDB = SQLiteDatabase.openDatabase(originalDBPath.getPath(),null,SQLiteDatabase.OPEN_READWRITE);
        SQLiteDatabase preservedDB = SQLiteDatabase.openDatabase(preservedDBPath.getPath(),null,SQLiteDatabase.OPEN_READONLY);

        //4. get the orignal data to be preserved
        Cursor csr = preservedDB.query(
                DatabaseConstants.QUESTION_TABLENAME,DatabaseConstants.EXTRACT_COLUMNS,
                null,null,null,null,null
        );

        //5. Apply preserved data to the newly copied data
        copiedDB.beginTransaction();
        ContentValues cv = new ContentValues();
        while (csr.moveToNext()) {
            cv.clear();
            for (String s: DatabaseConstants.PRESERVED_COLUMNS) {
                switch (csr.getType(csr.getColumnIndex(s))) {
                    case Cursor.FIELD_TYPE_INTEGER:
                        cv.put(s,csr.getLong(csr.getColumnIndex(s)));
                        break;
                    case Cursor.FIELD_TYPE_STRING:
                        cv.put(s,csr.getString(csr.getColumnIndex(s)));
                        break;
                    case Cursor.FIELD_TYPE_FLOAT:
                        cv.put(s,csr.getDouble(csr.getColumnIndex(s)));
                        break;
                    case Cursor.FIELD_TYPE_BLOB:
                        cv.put(s,csr.getBlob(csr.getColumnIndex(s)));
                        break;
                }
            }
            copiedDB.update(
                    DatabaseConstants.QUESTION_TABLENAME,
                    cv,
                    DatabaseConstants.QUESTION_ID_COLUMN + "=?",
                    new String[]{
                            String.valueOf(
                                    csr.getLong(
                                            csr.getColumnIndex(DatabaseConstants.QUESTION_ID_COLUMN
                                            )
                                    )
                            )
                    }
                    );
        }
        copiedDB.setTransactionSuccessful();
        copiedDB.endTransaction();
        csr.close();
        //6. Cleanup
        copiedDB.close();
        preservedDB.close();
        preservedDBPath.delete();
    }

    private static void copyAssetFile(Context context) {
        int buffer_size = 8192;
        byte[] buffer = new byte[buffer_size];
        int bytes_read = 0;
        try {
            InputStream fis = context.getAssets().open(DBNAME);
            OutputStream os = new FileOutputStream(new File(context.getDatabasePath(DBNAME).getPath()));
            while ((bytes_read = fis.read(buffer)) > 0) {
                os.write(buffer,0,bytes_read);
            }
            os.flush();
            os.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("Unable to copy from assets");
        }
    }

    private static int getDBVersion(Context context, String databaseName) {
        SQLiteDatabase db = SQLiteDatabase.openDatabase( context.getDatabasePath(databaseName).getPath(),null,SQLiteDatabase.OPEN_READONLY);
        int rv = db.getVersion();
        db.close();
        return rv;
    }
}

    This manages the Asset File copy (in this case directly from the assets folder) outside of Room and before the database is built doing it's own version and database existence checking. Although ATTACH could be used, the solution keeps the original and the new databases seperate when updating the new using a Cursor.

    Some flexibility/adaptability has been included in that the columns to be preserved can be expanded upon. In the test runs DatabaseConstants includes :-

    public static final String[] PRESERVED_COLUMNS = new String[]
        {
                QUESTION_SOLVED_COLUMN
        };
public static final String[] EXTRACT_COLUMNS = new String[]
        {
                QUESTION_ID_COLUMN,
                QUESTION_SOLVED_COLUMN
        };

    thus additional columns to be preserved can be added (of any type as per 5. in the copyFromAssets method). The columns to be extracted can also be specified, in the case above, the ID column uniquely identifies the question so that is extracted in addition to the solved column for use by the WHERE clause.

    Testing

    The above has been tested to :-

    Original

    New

    For testing the invoking activity consisted of :-

    public class MainActivity extends AppCompatActivity {

    QuestionDatabase questionDatabase;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        questionDatabase = QuestionDatabase.getInstance(this);
        int solvedCount = 0;
        for (Question q: questionDatabase.questionsDao().getAll()) {
            if (q.isSolved()) solvedCount++;
            q.logQuestion();
        }
        if (solvedCount == 0) {
            questionDatabase.questionsDao().setSolved(true,2);
        }
        for (Question q: questionDatabase.questionsDao().getAll()) {
            q.logQuestion();
        }
    }
}

    For each run it outputs all of the questions to the log twice. After the first if there are no solved questions it solves the question with an id of 2.

    The output from the last run was :-

    2020-01-08 09:14:37.689 D/QUESTIONINFO: ID is 1 Question is Editted What is x
      Answers Are :-
          a
          b
          x

    Correct Answer is 3

     Is Solved false
2020-01-08 09:14:37.689 D/QUESTIONINFO: ID is 2 Question is Edited What is a
      Answers Are :-
          a
          b
          c

    Correct Answer is 1

     Is Solved false
2020-01-08 09:14:37.689 D/QUESTIONINFO: ID is 3 Question is Edited What is b
      Answers Are :-
          a
          b
          c

    Correct Answer is 2

     Is Solved false
2020-01-08 09:14:37.689 D/QUESTIONINFO: ID is 4 Question is New Question What is d
      Answers Are :-
          e
          f
          d

    Correct Answer is 3

     Is Solved false
2020-01-08 09:14:37.692 D/QUESTIONINFO: ID is 1 Question is Editted What is x
      Answers Are :-
          a
          b
          x

    Correct Answer is 3

     Is Solved false
2020-01-08 09:14:37.692 D/QUESTIONINFO: ID is 2 Question is Edited What is a
      Answers Are :-
          a
          b
          c

    Correct Answer is 1

     Is Solved true
2020-01-08 09:14:37.692 D/QUESTIONINFO: ID is 3 Question is Edited What is b
      Answers Are :-
          a
          b
          c

    Correct Answer is 2

     Is Solved false
2020-01-08 09:14:37.693 D/QUESTIONINFO: ID is 4 Question is New Question What is d
      Answers Are :-
          e
          f
          d

    Correct Answer is 3

     Is Solved false

    Additional - Improved Version

    This is an approved version that caters for multiple tables and columns. To cater for tables a class TablePreserve has been added that allows a table, the columns to preserve, the columns to extract and the columns for the where clause. As per :-

    public class TablePreserve {
    String tableName;
    String[] preserveColumns;
    String[] extractColumns;
    String[] whereColumns;

    public TablePreserve(String table, String[] preserveColumns, String[] extractColumns, String[] whereColumns) {
        this.tableName = table;
        this.preserveColumns = preserveColumns;
        this.extractColumns = extractColumns;
        this.whereColumns = whereColumns;
    }

    public String getTableName() {
        return tableName;
    }

    public String[] getPreserveColumns() {
        return preserveColumns;
    }

    public String[] getExtractColumns() {
        return extractColumns;
    }

    public String[] getWhereColumns() {
        return whereColumns;
    }
}

    You create an Array of TablePreserve objects and they are looped through e.g.

    public final class DatabaseConstants {
    public static final String DBNAME = "question.db";
    public static final int DBVERSION = 2;
    public static final String QUESTION_TABLENAME = "question";
    public static final String QUESTION_ID_COLUMN = "id";
    public static final String QUESTION_QUESTION_COLUMN = QUESTION_TABLENAME;
    public static final String QUESTION_ANSWER1_COLUMN = "answer1";
    public static final String QUESTION_ANSWER2_COLUMN = "answer2";
    public static final String QUESTION_ANSWER3_COLUMN = "answer3";
    public static final String QUESTION_CORRECTANSWER_COLUMN = "correctAsnwer";
    public static final String QUESTION_SOLVED_COLUMN = "solved";

    public static final TablePreserve questionTablePreserve = new TablePreserve(
            QUESTION_TABLENAME,
            new String[]{QUESTION_SOLVED_COLUMN},
            new String[]{QUESTION_ID_COLUMN,QUESTION_SOLVED_COLUMN},
            new String[]{QUESTION_ID_COLUMN}
    );

    public static final TablePreserve[] TABLE_PRESERVELIST = new TablePreserve[] {
            questionTablePreserve
    };
}

    Then QuestionsDatabase becomes :-

    @Database(version = DatabaseConstants.DBVERSION, entities = {Question.class})
public abstract class QuestionDatabase extends RoomDatabase {

    static final String DBNAME = DatabaseConstants.DBNAME;

    abstract QuestionDao questionsDao();

    public static QuestionDatabase getInstance(Context context) {
        if (!doesDatabaseExist(context)) {
            copyFromAssets(context,false);
        }
        if (getDBVersion(context, DatabaseConstants.DBNAME) < DatabaseConstants.DBVERSION) {
            copyFromAssets(context, true);
        }

        return Room.databaseBuilder(context,QuestionDatabase.class,DBNAME)
                .addCallback(callback)
                .allowMainThreadQueries()
                .addMigrations(Migration_1_2)
                .build();
    }

    private static RoomDatabase.Callback callback = new Callback() {
        @Override
        public void onCreate(@NonNull SupportSQLiteDatabase db) {
            super.onCreate(db);
        }

        @Override
        public void onOpen(@NonNull SupportSQLiteDatabase db) {
            super.onOpen(db);
        }

        @Override
        public void onDestructiveMigration(@NonNull SupportSQLiteDatabase db) {
            super.onDestructiveMigration(db);
        }
    };

    private static Migration Migration_1_2 = new Migration(1, 2) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
        }
    };

    private static boolean doesDatabaseExist(Context context) {
        if (new File(context.getDatabasePath(DBNAME).getPath()).exists()) return true;
        if (!(new File(context.getDatabasePath(DBNAME).getPath()).getParentFile()).exists()) {
            new File(context.getDatabasePath(DBNAME).getPath()).getParentFile().mkdirs();
        }
        return false;
    }

    private static void copyFromAssets(Context context, boolean replaceExisting) {
        boolean dbExists = doesDatabaseExist(context);
        if (dbExists && !replaceExisting) return;
        //First Copy
        if (!replaceExisting) {
            copyAssetFile(context);
            setDBVersion(context,DBNAME,DatabaseConstants.DBVERSION);
            return;
        }
        //Subsequent Copies

        File originalDBPath = new File(context.getDatabasePath(DBNAME).getPath());
        // Open and close the original DB so as to checkpoint the WAL file
        SQLiteDatabase originalDB = SQLiteDatabase.openDatabase(originalDBPath.getPath(),null,SQLiteDatabase.OPEN_READWRITE);
        originalDB.close();

        //1. Rename original database
        String preservedDBName = "preserved_" + DBNAME;
        File preservedDBPath = new File (originalDBPath.getParentFile().getPath() + File.separator + preservedDBName);
        (new File(context.getDatabasePath(DBNAME).getPath()))
                .renameTo(preservedDBPath);

        //2. Copy the replacement database from the assets folder
        copyAssetFile(context);

        //3. Open the newly copied database
        SQLiteDatabase copiedDB = SQLiteDatabase.openDatabase(originalDBPath.getPath(),null,SQLiteDatabase.OPEN_READWRITE);
        SQLiteDatabase preservedDB = SQLiteDatabase.openDatabase(preservedDBPath.getPath(),null,SQLiteDatabase.OPEN_READONLY);

        //4. Apply preserved data to the newly copied data
        copiedDB.beginTransaction();
        for (TablePreserve tp: DatabaseConstants.TABLE_PRESERVELIST) {
            preserveTableColumns(
                    preservedDB,
                    copiedDB,
                    tp.getTableName(),
                    tp.getPreserveColumns(),
                    tp.getExtractColumns(),
                    tp.getWhereColumns(),
                    true
            );
        }
        copiedDB.setVersion(DatabaseConstants.DBVERSION);
        copiedDB.setTransactionSuccessful();
        copiedDB.endTransaction();
        //5. Cleanup
        copiedDB.close();
        preservedDB.close();
        preservedDBPath.delete();
    }

    private static void copyAssetFile(Context context) {
        int buffer_size = 8192;
        byte[] buffer = new byte[buffer_size];
        int bytes_read = 0;
        try {
            InputStream fis = context.getAssets().open(DBNAME);
            OutputStream os = new FileOutputStream(new File(context.getDatabasePath(DBNAME).getPath()));
            while ((bytes_read = fis.read(buffer)) > 0) {
                os.write(buffer,0,bytes_read);
            }
            os.flush();
            os.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
            throw new RuntimeException("Unable to copy from assets");
        }
    }

    private static int getDBVersion(Context context, String databaseName) {
        SQLiteDatabase db = SQLiteDatabase.openDatabase( context.getDatabasePath(databaseName).getPath(),null,SQLiteDatabase.OPEN_READONLY);
        int rv = db.getVersion();
        db.close();
        return rv;
    }
    private static void setDBVersion(Context context, String databaseName, int version) {
        SQLiteDatabase db = SQLiteDatabase.openDatabase( context.getDatabasePath(databaseName).getPath(),null,SQLiteDatabase.OPEN_READWRITE);
        db.setVersion(version);
        db.close();
    }

    private static boolean preserveTableColumns(
            SQLiteDatabase originalDatabase,
            SQLiteDatabase newDatabase,
            String tableName,
            String[] columnsToPreserve,
            String[] columnsToExtract,
            String[] whereClauseColumns,
            boolean failWithException) {

        StringBuilder sb = new StringBuilder();
        Cursor csr = originalDatabase.query("sqlite_master",new String[]{"name"},"name=? AND type=?",new String[]{tableName,"table"},null,null,null);
        if (!csr.moveToFirst()) {
            sb.append("\n\tTable ").append(tableName).append(" not found in database ").append(originalDatabase.getPath());
        }
        csr = newDatabase.query("sqlite_master",new String[]{"name"},"name=? AND type=?",new String[]{tableName,"table"},null,null,null);
        if (!csr.moveToFirst()) {
            sb.append("\n\tTable ").append(tableName).append(" not found in database ").append(originalDatabase.getPath());
        }
        if (sb.length() > 0) {
            if (failWithException) {
                throw new RuntimeException("Both databases are required to have a table named " + tableName + sb.toString());
            }
            return false;
        }
        for (String pc: columnsToPreserve) {
            boolean preserveColumnInExtractedColumn = false;
            for (String ec: columnsToExtract) {
                if (pc.equals(ec)) preserveColumnInExtractedColumn = true;
            }
            if (!preserveColumnInExtractedColumn) {
                if (failWithException) {
                    StringBuilder sbpc = new StringBuilder().append("Column in Columns to Preserve not found in Columns to Extract. Cannot continuue." +
                            "\n\tColumns to Preserve are :-");

                    }
                throw new RuntimeException("Column " + pc + " is not int the Columns to Extract.");
            }
            return false;
        }
        sb = new StringBuilder();
        for (String c: whereClauseColumns) {
            sb.append(c).append("=? ");
        }
        String[] whereargs = new String[whereClauseColumns.length];
        csr = originalDatabase.query(tableName,columnsToExtract,sb.toString(),whereClauseColumns,null,null,null);
        ContentValues cv = new ContentValues();
        while (csr.moveToNext()) {
            cv.clear();
            for (String pc: columnsToPreserve) {
                switch (csr.getType(csr.getColumnIndex(pc))) {
                    case Cursor.FIELD_TYPE_INTEGER:
                        cv.put(pc,csr.getLong(csr.getColumnIndex(pc)));
                        break;
                    case Cursor.FIELD_TYPE_STRING:
                        cv.put(pc,csr.getString(csr.getColumnIndex(pc)));
                        break;
                    case Cursor.FIELD_TYPE_FLOAT:
                        cv.put(pc,csr.getDouble(csr.getColumnIndex(pc)));
                        break;
                    case Cursor.FIELD_TYPE_BLOB:
                        cv.put(pc,csr.getBlob(csr.getColumnIndex(pc)));
                }
            }
            int waix = 0;
            for (String wa: whereClauseColumns) {
                whereargs[waix] = csr.getString(csr.getColumnIndex(wa));
            }
            newDatabase.update(tableName,cv,sb.toString(),whereargs);
        }
        csr.close();
        return true;
    }
}