在基类的table中加入upgrade操作:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
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); } } |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
|
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方法:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
@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一个名字，把命名后的表中的数据拷贝到新表中)
全站熱搜
創作者介紹
留言列表
