createFromAsset 迁移但保留特定列 [英] createFromAsset Migration but keep specific Columns

问题描述

我有一个测验应用程序，我有一个包含表格中所有问题的数据库，对于每个问题，有一列 solved 如果答案正确，我会更新该列，以便我可以过滤使用 SQL WHERE 仅显示未解决的问题.现在每隔一段时间，我必须更正问题中的错别字，或者可能想添加一些新问题，所以

如何在保留已解决列的同时，将资产中已更正的数据库 (questions.db) 应用到用户设备上已保存的数据库?

我想到并尝试了以下事情但没有成功:

• 目前，我使用自制的解决方案来替换设备上的数据库(破坏性)，但在更新之间保留已解决的信息

• 部分代码(在调用活动中检查所有已解决的值是否为 0，如果是，则更改问题的已解决状态，id 为 2)

不复制数据库，而是在后续运行中当 DBVERSION 为 1 时使用现有数据库.

• ID 2 仍未解决.

新

• 将原始资产重命名为以 original_ 为前缀后，将数据库编辑为如下，并将其复制到资产文件后:-

• 在不改变 DBVERSION(仍为 1)的情况下运行并且原始数据库仍在使用中.

• 在将 DBVERSION 更改为 2 后运行复制更改的资产文件并恢复/保留已解决的状态.

• 对于后续运行，新数据的求解状态保持不变.

为了测试调用活动包括:-

公共类 MainActivity 扩展 AppCompatActivity {QuestionDatabase questionDatabase;@覆盖protected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_main);questionDatabase = QuestionDatabase.getInstance(this);INT解决计数= 0;for (问题q: questionDatabase.questionsDao().getAll()) {if (q.isSolved())solvedCount++;q.logQuestion();}如果(已解决计数 == 0){questionDatabase.questionsDao().setSolved(true,2);}for (问题q: questionDatabase.questionsDao().getAll()) {q.logQuestion();}}}

对于每次运行，它会将所有问题输出到日志两次.在第一个之后，如果没有已解决的问题，它会解决 id 为 2 的问题.

上次运行的输出是:-

<块引用>

2020-01-08 09:14:37.689 D/QUESTIONINFO: ID is 1 问题被编辑 x 是什么答案是:-一个乙×正确答案是3已解决为假2020-01-08 09:14:37.689 D/QUESTIONINFO:ID 是 2 问题被编辑 什么是答案是:-一个乙丙正确答案是1已解决为假2020-01-08 09:14:37.689 D/QUESTIONINFO:ID 是 3 问题被编辑 什么是 b答案是:-一个乙丙正确答案是2已解决为假2020-01-08 09:14:37.689 D/QUESTIONINFO:ID 是 4 问题是新问题 什么是 d答案是:-电子fd正确答案是3已解决为假2020-01-08 09:14:37.692 D/QUESTIONINFO:ID 为 1 问题已编辑 什么是 x答案是:-一个乙×正确答案是3已解决为假2020-01-08 09:14:37.692 D/QUESTIONINFO:ID 是 2 问题被编辑 什么是答案是:-一个乙丙正确答案是1是否解决为真2020-01-08 09:14:37.692 D/QUESTIONINFO:ID 是 3 问题被编辑 什么是 b答案是:-一个乙丙正确答案是2已解决为假2020-01-08 09:14:37.693 D/QUESTIONINFO:ID 是 4 问题是新问题 什么是 d答案是:-电子fd正确答案是3已解决为假

附加 - 改进版

这是一个经过批准的版本，可满足多个表和列的需求.为了满足表格的需要，添加了一个 TablePreserve 类，它允许一个表格、要保留的列、要提取的列和用于 where 子句的列.根据 :-

公共类 TablePreserve {字符串表名；String[] 保留列；字符串[]提取列；String[] whereColumns;公共表保留(字符串表，字符串 [] 保留列，字符串 [] 提取列，字符串 [] whereColumns){this.tableName = table;this.preserveColumns = preserveColumns;this.extractColumns = extractColumns;this.whereColumns = whereColumns;}公共字符串 getTableName() {返回表名；}公共字符串[] getPreserveColumns() {返回preserveColumns；}公共字符串[] getExtractColumns() {返回提取列；}公共字符串[] getWhereColumns() {返回 whereColumns;}}

您创建了一个 TablePreserve 对象数组，并循环遍历它们，例如

public final class DatabaseConstants {public static final String DBNAME = "question.db";public static final int DBVERSION = 2;公共静态最终字符串QUESTION_TABLENAME =问题"；公共静态最终字符串 QUESTION_ID_COLUMN = "id";公共静态最终字符串QUESTION_QUESTION_COLUMN = QUESTION_TABLENAME;公共静态最终字符串 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";公共静态最终字符串QUESTION_SOLVED_COLUMN =已解决"；public static final TablePreserve questionTablePreserve = new TablePreserve(QUESTION_TABLENAME，新字符串[]{QUESTION_SOLVED_COLUMN}，新字符串[]{QUESTION_ID_COLUMN,QUESTION_SOLVED_COLUMN},新字符串[]{QUESTION_ID_COLUMN});public static final TablePreserve[] TABLE_PRESERVELIST = new TablePreserve[] {问题表保留};}

然后 QuestionsDatabase 变成 :-

@Database(version = DatabaseConstants.DBVERSION, entity = {Question.class})公共抽象类 QuestionDatabase 扩展 RoomDatabase {static final String DBNAME = DatabaseConstants.DBNAME;抽象 QuestionDao questionsDao();公共静态问题数据库 getInstance(上下文上下文){如果(！doesDatabaseExist(上下文)){copyFromAssets(context,false);}如果(getDBVersion(上下文，DatabaseConstants.DBNAME) 0) {os.write(buffer,0,bytes_read);}os.flush();os.close();fis.close();} catch (IOException e) {e.printStackTrace();throw new RuntimeException("无法从资产复制");}}私有静态 int getDBVersion(上下文上下文，字符串数据库名称){SQLiteDatabase db = SQLiteDatabase.openDatabase(context.getDatabasePath(databaseName).getPath(),null,SQLiteDatabase.OPEN_READONLY);int rv = db.getVersion();db.close();返回房车；}private static void setDBVersion(Context context, String databaseName, int version) {SQLiteDatabase db = SQLiteDatabase.openDatabase(context.getDatabasePath(databaseName).getPath(),null,SQLiteDatabase.OPEN_READWRITE);db.setVersion(版本);db.close();}私有静态布尔值preserveTableColumns(SQLiteDatabase 原始数据库，SQLiteDatabase 新建数据库，字符串表名，String[] columnsToPreserve,String[] columnsToExtract，String[] whereClauseColumns,布尔型 failWithException) {StringBuilder sb = new StringBuilder();Cursor csr = originalDatabase.query("sqlite_master",new String[]{"name"},"name=? AND type=?",new String[]{tableName,"table"},null,null,null);如果 (!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);如果 (!csr.moveToFirst()) {sb.append("\n\tTable ").append(tableName).append(" not found in database ").append(originalDatabase.getPath());}如果(sb.length()> 0){如果(failWithException){throw new RuntimeException("两个数据库都需要有一个名为" + tableName + sb.toString());}返回假；}for (String pc: columnsToPreserve) {boolean preserveColumnInExtractedColumn = false;for (String ec: columnsToExtract) {如果(pc.equals(ec))preserveColumnInExtractedColumn = true;}如果(！preserveColumnInExtractedColumn){如果(failWithException){StringBuilder sbpc = new StringBuilder().append("未在要提取的列中找到要保留的列中的列.无法继续." +"\n\t要保留的列是 :-");}throw new RuntimeException("Column " + pc + " is not int the Columns to Extract.");}返回假；}sb = 新的 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 = 新的 ContentValues();而 (csr.moveToNext()) {cv.clear();for (String pc: columnsToPreserve) {开关 (csr.getType(csr.getColumnIndex(pc))) {案例 Cursor.FIELD_TYPE_INTEGER:cv.put(pc,csr.getLong(csr.getColumnIndex(pc)));打破;案例 Cursor.FIELD_TYPE_STRING:cv.put(pc,csr.getString(csr.getColumnIndex(pc)));打破;案例 Cursor.FIELD_TYPE_FLOAT:cv.put(pc,csr.getDouble(csr.getColumnIndex(pc)));打破;案例 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();返回真；}}

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:

• Currently, i use a self-crafted solution to replace the database on the device (destructive) but between updates keep the solved info https://github.com/ueen/RoomAsset

• Put solved info (question id solved y/n) in a separate table and LEFT JOIN to filter unsolved questions, this only complicated matters

• Have an extra database for the solved questions, it seems there's no easy way to attach two Room Databases

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

解决方案

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

• Copy the first version of the database from the assets when DBVERSION is 1.

  • Note that this originaly contains 3 questions as per

  • Part of the code (in the invoking activity checks to to see if all the solved values are 0 if so, then it chages the solved status of the question with an id of 2)
• Not copy the database, but use the existing database when DBVERSION is 1 on a subseuent run(s).
  • ID 2 remains solved.

New

• After renaming the original asset from to be prefixed with original_, editing the database to be as below and after copying it to the assets file :-

• Without changing the DBVERSION (still 1) run and the original database is still in use.

• After changing DBVERSION to 2 running copies the changed asset file and restores/preserves the solved status.

• For subsequent runs the solved status for the new data remains.

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;
    }
}

这篇关于createFromAsset 迁移但保留特定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！