How to upgrade the Android database

Add the upgrade operation to the table in the base class:

Public abstract class Dbbasetable {private static final String TAG = "dbbasetable";/** * @return the DB table name */abstr Act String getName ();/** * creates the DB table according to the DB scheme * * @param db */abstract void OnCreate (sqlited Atabase db), void Onupgrade (sqlitedatabase db, int oldversion, int newversion, String tempname) {//rename O  LD table to temporary name<strong>dbutils.renametable (db, GetName (), tempname);</strong>//create Clear  Table According to the new scheme <strong> onCreate (db);</strong>//copy content of the matching                Columns from the old table to the new one <strong> joincolumns (db, Tempname, GetName ());</strong> Delete Old table <strong> dbutils.droptable (db, Tempname);</strong>//This is a few updates    The contents of the table inittablecontent (db); } void Inittablecontent (Sqlitedatabase db) {} void Joincolumns (Sqlitedatabase db, String temPName, String tableName) {<strong>dbutils.joincolumns (db, Tempname, TableName);</strong>}} 

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 N    OT like ' sqlite_% ' and ' name ' is not a 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&LT ;  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.forma        T (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.E    Xecsql (String.Format (sqlite_stmt_template_drop_table, TABLE)); } static void Renametable (Sqlitedatabase db, String oldname, String newName) {Db.execsql (String.Format (SQLI    Te_stmt_template_rename_table, Oldname, NewName)); } static void Joincolumns (Sqlitedatabase db, String oldtable, String newtable) {//delete all re                Cords 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> (listcol        Umns (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:<span style= "FOnt-family: Microsoft Jacob Black; ><strong><span style= "font-size:10px;" >insert to Mytest1 (_ID,ACCOUNT_ID,TEST1,TEST3) SELECT _id,account_id,test1,test3 from Mytest1_temp_</span ></strong></span> Db.execsql (String.Format (Sqlite_stmt_template_copy_columns, newtable, Common_    Columns, Common_columns, oldtable)); }}

Then overload the Onupgrade method in DBHelper:

 @Override public void Onupgrade (sqlitedatabase db, int oldversion, int. newversion) {//get table names in the O        LD 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 is not in the new DB scheme hashset<string> obsolete_tables = new Hashset<s            Tring> (); 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 (); }    }

The central idea is:

Compare the old database structure, if there is a table in the old table in the new database, then delete the

If there is a table update in the new database, you need to update the structure of the table and then copy the data from the old table.

(Rename the old table to a name and copy the data from the named table to the new table)

Code can be downloaded in http://download.csdn.net/detail/baidu_nod/7684479