SQLiteOpenHelper onUpgrade () Confusion Android

Question

I am making my first database application, and I have a little problem understanding onUpgrade function. My database has a table with items and a favorite column so that the user can like the item. Most of the implementations that I see just drop the table and reconstruct it, but I don't want to do this. I want to be able to add additional elements to the table.

When an application is updated through the Android market, does the database know its version number? Can I increase the number of versions in the code and then export it to the market, and when the user first downloads the updated version, then onUpgrade will be called?

If that is the case, my onUpgrade will simply pull out the file and add the database items. Is this the standard way to do something, or is there a better way to handle this on Android. I try to stay as standard as possible.

thank

Answer 1

Well, before you run into big problems, you should know that SQLite is limited by the ALTER TABLE command, it allows add and rename delete only the delete / delete that is performed with the table recreated.

You should always have a new table creation request and use it to update and migrate any existing data. Note: the onUpgrade methods run one for your helper sqlite object, and you need to process all its tables.

So, what is recommended on Upgrade:

• BeginTransaction
• start the creation of the table with if not exists (we are updating, so the table may not exist yet, it will not be changed and canceled)
• enter the existing columns in the list List<String> columns = DBUtils.GetColumns(db, TableName);
• backup table ( ALTER table " + TableName + " RENAME TO 'temp_" + TableName )
• create a new table (latest table creation scheme)
• get the intersection with the new columns, this time the columns taken from the updated table ( columns.retainAll(DBUtils.GetColumns(db, TableName)); )
• restore data ( String cols = StringUtils.join(columns, ","); db.execSQL(String.format( "INSERT INTO %s (%s) SELECT %s from temp_%s", TableName, cols, cols, TableName)); )
• delete backup table ( DROP table 'temp_" + TableName )
• setTransactionSuccessful

(This does not handle dropping the table; if you rename a column, you do not get the existing data passed in because the column names do not match).

.

 public static List<String> GetColumns(SQLiteDatabase db, String tableName) { List<String> ar = null; Cursor c = null; try { c = db.rawQuery("select * from " + tableName + " limit 1", null); if (c != null) { ar = new ArrayList<String>(Arrays.asList(c.getColumnNames())); } } catch (Exception e) { Log.v(tableName, e.getMessage(), e); e.printStackTrace(); } finally { if (c != null) c.close(); } return ar; } public static String join(List<String> list, String delim) { StringBuilder buf = new StringBuilder(); int num = list.size(); for (int i = 0; i < num; i++) { if (i != 0) buf.append(delim); buf.append((String) list.get(i)); } return buf.toString(); } 

Answer 2

There is an excellent answer next to Pentium10, here are some good examples from live code:

• Android AOSP: com.android.providers.calendar.CalendarDatabaseHelper.java

• Android AOSP: com.android.browser.BrowserProvider.java

• OpenIntents Notepad: org.openintents.notepad.NotePadProvider.java

Answer 3

Thank you for clarifying that onUpgrade () will not support Remove / Drop @Pentium 10 statements

For those of you who would like to know the exact moment when onUpgrade () is called, it is at the time of calling either getReadableDatabase () or getWriteableDatabase ().

For those who are not clear how it ensures its launch ... the answer is this: it starts when the database version provided by the SqLiteOpenHelper constructor is updated. Here is an example

 public class dbSchemaHelper extends SQLiteOpenHelper { private String sql; private final String D_TAG = "FundExpense"; //update this to get onUpgrade() method of sqliteopenhelper class called static final int DB_VERSION = 2; static final String DB_NAME = "fundExpenseManager"; public dbSchemaHelper(Context context) { super(context, DB_NAME, null, DB_VERSION); // TODO Auto-generated constructor stub } 

now ... onUpgrade ()

 @Override public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) { sql = "ALTER TABLE " + fundExpenseSchema.Expense.TABLE_NAME + " ADD COLUMN " + fundExpenseSchema.Expense.FUNDID + " INTEGER"; arg0.execSQL(sql); } 

Answer 4

I have been using the solution offered by @ Pentium10 for a long time, but today I had a problem, after executing the alter table, getColumns from the source table still returns the same columns (in the new version of the db table, the mayor’s structure will suffer, some columns added some others), I really don’t know why the select statement does not reflect structure changes, moreover, before creating the table again, the select statement still returns the columns! When the table has not yet been recreated!

So, I solve the problem by updating the getColumns method with pragma table_info , for example:

  /** * Get a list of column base_dictionary for the selected table * * @param db * Database that contains the table * @param tableName * Table name to be used * @return A List of column name */ public static List<String> getColumns(SQLiteDatabase db, String tableName) { List<String> ar = null; Cursor c = null; try { c = db.rawQuery("pragma table_info(" + tableName + ")", null); ar = new ArrayList<String>(); if (c != null && c.moveToFirst()) { do { ar.add(c.getString(c.getColumnIndexOrThrow("name"))); } while (c.moveToNext()); c.close(); } } catch (Exception e) { Log.v(tableName, e.getMessage(), e); e.printStackTrace(); } finally { if (c != null) c.close(); } return ar; }