android数据库的升级的写法

jopen 10年前

在基类的table中加入upgrade操作:

    public abstract class DbBaseTable {                    private static final String TAG = "DbBaseTable";                    /**            * @return the DB table name            */            abstract String getName();                    /**            * Creates the DB table according to the DB scheme            *             * @param db            */            abstract void onCreate(SQLiteDatabase db);                        void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion, String tempName) {                                //Rename old table to temporary name            DbUtils.renameTable(db, getName(), tempName);                                //Create clear table according to the new scheme               onCreate(db);               //Copy content of the matching columns from the old table to the new one              joinColumns(db, tempName, getName());                               //Delete old table              DbUtils.dropTable(db, tempName);                               //这个是更新一些表的内容                initTableContent(db);            }                        void initTableContent(SQLiteDatabase db) {            }                       void joinColumns(SQLiteDatabase db, String tempName, String tableName) {              DbUtils.joinColumns(db, tempName, tableName);            }                }  

        final class DbUtils {                        private static final String TAG = "DbUtils";            private static final boolean DEBUG = false;                        private static final String SQLITE_STMT_LIST_TABLES =                "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name NOT LIKE 'android%'";            private static final String SQLITE_TABLE_NAME_COLUMN = "name";            private static final String SQLITE_STMT_TEMPLATE_LIST_COLUMNS = "SELECT * FROM %s LIMIT 1";            private static final String SQLITE_STMT_TEMPLATE_DROP_TABLE = "DROP TABLE IF EXISTS %s";            private static final String SQLITE_STMT_TEMPLATE_RENAME_TABLE = "ALTER TABLE %s RENAME TO %s";            private static final String SQLITE_STMT_TEMPLATE_COPY_COLUMNS = "INSERT INTO %s (%s) SELECT %s FROM %s";                                /**            * @param db            * @return Collection object containing table names in the database             */            static Collection<String> listTables(SQLiteDatabase db) {                        Cursor cursor = db.rawQuery(SQLITE_STMT_LIST_TABLES, null);                if (cursor == null || !cursor.moveToFirst()) {                            if (cursor != null) {                        cursor.close();                    }                    return null;                }                                int table_name_column = cursor.getColumnIndex(SQLITE_TABLE_NAME_COLUMN);                HashSet<String> tables = new HashSet<String>(cursor.getCount());                do {                    tables.add(cursor.getString(table_name_column));                } while (cursor.moveToNext());                cursor.close();                                        return tables;            }                                /**            * @param db            * @param table            * @return List of column names in the DB table            */            public static List<String> listColumns(SQLiteDatabase db, String table) {                                Cursor cursor = db.rawQuery(String.format(SQLITE_STMT_TEMPLATE_LIST_COLUMNS, table), null);                if (cursor == null) {                    return null;                }                                List<String> columns = Arrays.asList(cursor.getColumnNames());                cursor.close();                        return columns;            }                                    /**            * @param db            * @param table            */            static void dropTable(SQLiteDatabase db, String table) {                        db.execSQL(String.format(SQLITE_STMT_TEMPLATE_DROP_TABLE, table));            }                                static void renameTable(SQLiteDatabase db, String oldName, String newName) {                        db.execSQL(String.format(SQLITE_STMT_TEMPLATE_RENAME_TABLE, oldName, newName));            }                                    static void joinColumns(SQLiteDatabase db, String oldTable, String newTable) {                                //Delete all records in the new table before copying from the old table                db.delete(newTable, null, null);                                //Find columns which exist in both tables                ArrayList<String> old_columns = new ArrayList<String>(listColumns(db, oldTable));                List<String> new_columns = listColumns(db, newTable);                old_columns.retainAll(new_columns);                        String common_columns = TextUtils.join(",", old_columns);                                //Copy records from old table to new table  example:         INSERT INTO Mytest1 (_id,account_id,test1,test3) SELECT _id,account_id,test1,test3 FROM Mytest1_temp_               db.execSQL(String.format(SQLITE_STMT_TEMPLATE_COPY_COLUMNS, newTable, common_columns, common_columns, oldTable));            }                }  

    然后在DBHelper中重载onUpgrade方法:
        @Override           public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {                       //Get table names in the old DB               Collection<String> old_tables = DbUtils.listTables(db);               if (old_tables == null || old_tables.size() == 0) {                   onCreate(db);                   return;               }                              //Get table names in the new DB               Set<String> new_tables = DataBaseClass.sRCMDbTables.keySet();                              try {                   db.beginTransaction();                   //Remove old tables which are not in the new DB scheme                   HashSet<String> obsolete_tables = new HashSet<String>();                   for (String table : old_tables) {                       if (!new_tables.contains(table)) {                        System.out.println("====DBHelp onUpgrade droptable table="+table);                           DbUtils.dropTable(db, table);                           obsolete_tables.add(table);                       }                   }                   old_tables.removeAll(obsolete_tables);                              //Create and upgrade new tables                    DbBaseTable table_descriptor;                    for (String table : new_tables) {                       table_descriptor = DataBaseClass.sRCMDbTables.get(table);                                              //Check if the new table exists in the old DB                       if (old_tables.contains(table)) {                           String temp_name = getTempTableName(table, old_tables, new_tables);                           System.out.println("====DBHelp onUpgrade temp_name ="+temp_name);                           table_descriptor.onUpgrade(db, oldVersion, newVersion, temp_name);                       } else {                           table_descriptor.onCreate(db);                       }                   }                   db.setTransactionSuccessful();               } catch (Throwable e) {                                      throw new RuntimeException("DB upgrade failed: " + e.getMessage());               } finally {                   db.endTransaction();               }           }  


    中心思想是:

    对比新旧的database结构,如果旧表中有table新的database里没有,则删除旧表里的

    如果新database中有一个table更新,则需要更新这个表的结构,然后把旧表中数据拷贝过来

    (把旧表rename一个名字,把命名后的表中的数据拷贝到新表中)