2014/01/07

Android SQLite Version Update

Android SQLite 是 Android 提供的簡易型 DataBase，相信對於有在開發 android app 的朋友們一定不陌生。
以前在開發 android app 時，只要 DB 欄位更新，就必須將 app 重新安裝，但在上架後呢？總不可能請使用者移除在安裝吧！因此 android 提供了一個 SQLite 的更新方式。下面就來介紹一下關於 SQLite 的版本更新。(本章節不討論如何建立 SQLite )
首先，您一定會有一個繼承 SQLiteOpenHelper 的物件，這裡暫時稱為 DBHelper，在此物間中必須有一個 DATABASE_VERSION 的 field，這個 field 將記錄您當前 SQLite 的版本號。
在您呼叫 super(context, DB_NAME, null, DATABASE_VERSION); 時必須將此版本號帶入。系統會根據此版本號判斷 DB 是否為新版本，在做相對應的更新。
接著，就來介紹一下 DBHelper 的內容：

DBHelper 幾個 method 介紹

  • onCreate() 在 DB 尚未建立時執行,只會執行一次
  • onUpgrade() DB version 升版時執行
  • onDowngrade() DB version 降版時執行
  • getReadableDatabase() 建立或開啟 DB ，呼叫後才會執行 onUpgrade() 或 onDowngrade()
  • getWritableDatabase () 建立或開啟一個可供讀寫的 DB ，執行較 getReadableDatabase() 久，呼叫後才會執行 onUpgrade() 或 onDowngrade()

SQLite 基本更新流程

  1. init 時，呼叫 super(context, DB_NAME, null, DATABASE_VERSION); 帶入當前 version
  2. 呼叫 getReadableDatabase() 或 getWritableDatabase ()
  3. 系統發現 version 有異動後，會自動呼叫 onUpgrade() 或 onDowngrade() 更新 DB

Code 解說

init

public DBHelper(Context context) {
 super(context, DB_NAME, null, DATABASE_VERSION);  //呼叫父類別並將當前 version 帶入
 mContext = context;

 File cache = mContext.getCacheDir();
 File appDir = new File(cache.getParent());
 dbPath = appDir.getAbsolutePath() + "/databases/";

}
createDatabase

public void createDatabase() throws IOException {
 boolean dbExist = checkDataBase();
 if (dbExist) {
  // do nothing - database already exist
  SQLiteDatabase sqlitedb = this.getReadableDatabase();
  
  Log.i("ruby", "createDatabase Version="+sqlitedb.getVersion());
  //onUpgrade或onDowngrade寫入DB錯誤時要把版本號改回舊的版本,不然會有一版沒更新到
  if(!upgradeSuccess || !downSuccess){
   Log.i("ruby", "升版或降版失敗 oldVersion=" + oldVersion);
   sqlitedb.setVersion(oldVersion);
  }
  sqlitedb.close();
 } else {
  // By calling this method and empty database will be created into
  // the default system path
  // of your application so we are gonna be able to overwrite that
  // database with our database.
  SQLiteDatabase sqlitedb = this.getReadableDatabase();  //再dbPath下建立SQLiteDatabase(.sqlite跟.journal),路徑folder不存在會自動建立

  try {
   sqlitedb.close();
  } catch (Exception e) {
  }
 }
}
DB 升版

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 Log.i("ruby", "====================================================================================================");
 Log.i("ruby", "onUpgrade oldVersion="+oldVersion+" newVersion="+newVersion+" DATABASE_VERSION="+DATABASE_VERSION);
 this.oldVersion = oldVersion;
 
 //*********************************************************************************************************************
 //不會執行...
 if(oldVersion < 1) {
  Log.i("ruby", "oldVersion < 1");
  db.execSQL("DROP TABLE IF EXISTS profile");  //刪除profile table
 }
 //*********************************************************************************************************************
 
 if(oldVersion < 2){
  try {
   Log.i("ruby", "oldVersion < 2");
   db.execSQL("DROP TABLE IF EXISTS profile");  //刪除table1 table
   db.execSQL("CREATE TABLE profile (id INTEGER PRIMARY KEY  NOT NULL , name VARCHAR NOT NULL , addr VARCHAR, phone VARCHAR, email VARCHAR)");
  }catch(SQLiteException e) {
   Log.i("ruby", "oldVersion < 2 Upgrade fail... maybe a crappy rom...\n", e);
   db.execSQL("DROP TABLE IF EXISTS profile");  //新增失敗,刪除table1 table
   upgradeSuccess = false;  //設定之後DB準備降回舊版,onUpgrade後android會自動setVersion(newVersion)
  }
 }
 if(oldVersion < 3) {
  try {
   Log.i("ruby", "oldVersion < 3");
   db.execSQL("DROP TABLE IF EXISTS table1");  //刪除table1 table
   db.execSQL("Create TABLE table1 (First_Name char(50), Last_Name char(50), Address char(50), City char(50), Country char(25), Birth_Date datetime)");
  }catch(SQLiteException e) {
   Log.i("ruby", "oldVersion < 3 Upgrade fail... maybe a crappy rom...\n", e);
   db.execSQL("DROP TABLE IF EXISTS table1");  //新增失敗,刪除table1 table
   upgradeSuccess = false;  //設定之後DB準備降回舊版,onUpgrade後android會自動setVersion(newVersion)
  }
 }
 if(oldVersion < 4) {
  try {
   Log.i("ruby", "oldVersion < 4");
   db.execSQL("DROP TABLE IF EXISTS table2");  //刪除table2 table
   db.execSQL("Create TABLE table2 (Company char(50), Phone char(50), co_addr char(50))");
  }catch(SQLiteException e) {
   Log.i("ruby", "oldVersion < 4 Upgrade fail... maybe a crappy rom...\n", e);
   db.execSQL("DROP TABLE IF EXISTS table2");  //新增失敗,刪除table2 table
   upgradeSuccess = false;  //設定之後DB準備降回舊版,onUpgrade後android會自動setVersion(newVersion)
  }
 }
 if(oldVersion < 5) {
  try {
   Log.i("ruby", "oldVersion < 5");
   db.execSQL("DROP TABLE IF EXISTS table3");  //刪除table3 table
   db.execSQL("Create TABLE table3 (Company char(50), Phone char(50), co_addr char(50))");
  }catch(SQLiteException e) {
   Log.i("ruby", "oldVersion < 5 Upgrade fail... maybe a crappy rom...\n", e);
   db.execSQL("DROP TABLE IF EXISTS table3");  //新增失敗,刪除table3 table
   upgradeSuccess = false;  //設定之後DB準備降回舊版,onUpgrade後android會自動setVersion(newVersion)
  }
 }
 newExecSQL(db);
}

private void newExecSQL(SQLiteDatabase db){
 Log.i("ruby", "newExecSQL");
 //add DB新版本語法
}
(為方便測試每個 version 更新前會先刪除 table，避免出現Exception)
DB 降版

/**
 * API level 11
 * @see android.database.sqlite.SQLiteOpenHelper#onDowngrade(android.database.sqlite.SQLiteDatabase, int, int)
 */
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 Log.i("ruby", "====================================================================================================");
 Log.i("ruby", "onDowngrade oldVersion="+oldVersion+" newVersion="+newVersion+" DATABASE_VERSION="+DATABASE_VERSION);
 this.oldVersion = oldVersion;
 
 if(newVersion < 2){
  try {
   Log.i("ruby", "newVersion < 2");
   db.execSQL("DROP TABLE IF EXISTS profile");  //刪除table1 table
   db.execSQL("CREATE TABLE profile (id INTEGER PRIMARY KEY  NOT NULL , name VARCHAR NOT NULL , addr VARCHAR, phone VARCHAR, email VARCHAR)");
  }catch(SQLiteException e) {
   Log.i("ruby", "newVersion < 2 Upgrade fail... maybe a crappy rom...\n", e);
   db.execSQL("DROP TABLE IF EXISTS profile");  //新增失敗,刪除table1 table
   downSuccess = false;  //設定之後DB準備降回舊版,onDowngrade後android會自動setVersion(newVersion)
  }
 }
 if(newVersion < 3) {
  try {
   Log.i("ruby", "newVersion < 3");
   db.execSQL("DROP TABLE IF EXISTS table1");  //刪除table1 table
   db.execSQL("Create TABLE table1 (First_Name char(50), Last_Name char(50), Address char(50), City char(50), Country char(25), Birth_Date datetime)");
  }catch(SQLiteException e) {
   Log.i("ruby", "newVersion < 3 Upgrade fail... maybe a crappy rom...\n", e);
   db.execSQL("DROP TABLE IF EXISTS table1");  //新增失敗,刪除table1 table
   downSuccess = false;  //設定之後DB準備降回舊版,onDowngrade後android會自動setVersion(newVersion)
  }
 }
 if(newVersion < 4) {
  try {
   Log.i("ruby", "newVersion < 4");
   db.execSQL("DROP TABLE IF EXISTS table2");  //刪除table2 table
   db.execSQL("Create TABLE table2 (Company char(50), Phone char(50), co_addr char(50))");
  }catch(SQLiteException e) {
   Log.i("ruby", "newVersion < 4 Upgrade fail... maybe a crappy rom...\n", e);
   db.execSQL("DROP TABLE IF EXISTS table2");  //新增失敗,刪除table2 table
   downSuccess = false;  //設定之後DB準備降回舊版,onDowngrade後android會自動setVersion(newVersion)
  }
 }
 if(newVersion < 5) {
  try {
   Log.i("ruby", "newVersion < 5");
   db.execSQL("DROP TABLE IF EXISTS table3");  //刪除table3 table
   db.execSQL("Create TABLE table3 (Company char(50), Phone char(50), co_addr char(50))");
  }catch(SQLiteException e) {
   Log.i("ruby", "newVersion < 5 Upgrade fail... maybe a crappy rom...\n", e);
   db.execSQL("DROP TABLE IF EXISTS table3");  //新增失敗,刪除table3 table
   downSuccess = false;  //設定之後DB準備降回舊版,onDowngrade後android會自動setVersion(newVersion)
  }
 }
}
(為方便測試每個 version 更新前會先刪除 table，避免出現Exception)

注意事項

  • 呼叫 getReadableDatabase() 或 getWritableDatabase () 後必須關閉 DB 連線，可使用 sqlitedb.close();
  • 執行 onUpgrade() 或 onDowngrade() 時，若 DB 更新失敗必須將 DB version 設為舊的 version，避免有某個版本沒有更新到，可用 sqlitedb.setVersion(oldVersion);
  • DB 必須用 db.execSQL() 等語法建立,不可用 assets/XXX.sqlite 的 SQL 複製出

SQLiteOpenHelper.getWritableDatabase() 源碼片斷


int version = db.getVersion();
if (version != mNewVersion) {
    db.beginTransaction();
    try {
        if (version == 0) {
            onCreate(db);
        } else {
            if (version > mNewVersion) {
                onDowngrade(db, version, mNewVersion);
            } else {
                onUpgrade(db, version, mNewVersion);
            }
        }
        db.setVersion(mNewVersion);
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}
onOpen(db);
由上可知，在呼叫 onUpgrade() 或 onDowngrade() 後會將 DB version 設為新傳入的 mNewVersion，所以若在 onUpgrade() 或 onDowngrade() 中更新 DB 失敗，建議重設 DB Version 避免有版本未更新到

測試結果

copy assets/test.sqlite 的 SQL

/*************************************************************************************************************************************/
/* copy assets/test.sqlite 的 SQL(順號升版)                                                                                          */
/*************************************************************************************************************************************/
01-06 17:01:10.722: I/ruby(7190): onCreate------------------------------------------------------>version:1
01-06 17:01:10.753: I/ruby(7190): onOpen-------------------------------------------------------->version:1
01-06 17:01:58.768: I/ruby(7254): onCreate------------------------------------------------------>version:2
01-06 17:01:58.776: I/ruby(7254): onOpen-------------------------------------------------------->version:2
01-06 17:01:58.780: I/ruby(7254): createDatabase Version=2-------------------------------------->version:2
01-06 17:02:13.042: I/ruby(7316): ====================================================================================================
01-06 17:02:13.042: I/ruby(7316): onUpgrade oldVersion=2 newVersion=3 DATABASE_VERSION=3-------->version:3
01-06 17:02:13.042: I/ruby(7316): oldVersion < 3------------------------------------------------>version:3
01-06 17:02:13.046: I/ruby(7316): oldVersion < 4------------------------------------------------>version:3
01-06 17:02:13.046: I/ruby(7316): oldVersion < 5------------------------------------------------>version:3
01-06 17:02:13.050: I/ruby(7316): newExecSQL---------------------------------------------------->version:3
01-06 17:02:13.089: I/ruby(7316): onOpen-------------------------------------------------------->version:3
01-06 17:02:13.089: I/ruby(7316): createDatabase Version=3-------------------------------------->version:3
01-06 17:02:52.229: I/ruby(7377): ====================================================================================================
01-06 17:02:52.229: I/ruby(7377): onUpgrade oldVersion=3 newVersion=4 DATABASE_VERSION=4-------->version:4
01-06 17:02:52.229: I/ruby(7377): oldVersion < 4------------------------------------------------>version:4
01-06 17:02:52.229: I/ruby(7377): oldVersion < 5------------------------------------------------>version:4
01-06 17:02:52.233: I/ruby(7377): newExecSQL---------------------------------------------------->version:4
01-06 17:02:52.265: I/ruby(7377): onOpen-------------------------------------------------------->version:4
01-06 17:02:52.265: I/ruby(7377): createDatabase Version=4-------------------------------------->version:4
execSQL 語法建立的 SQL

/*************************************************************************************************************************************/
/* execSQL 語法建立的 SQL(順號升版)                                                                                                  */
/*************************************************************************************************************************************/
01-06 17:11:56.530: I/ruby(7588): onCreate------------------------------------------------------>version:1
01-06 17:11:56.542: I/ruby(7588): onOpen-------------------------------------------------------->version:1
01-06 17:12:09.565: I/ruby(7649): ====================================================================================================
01-06 17:12:09.565: I/ruby(7649): onUpgrade oldVersion=1 newVersion=2 DATABASE_VERSION=2-------->version:2
01-06 17:12:09.565: I/ruby(7649): oldVersion < 2------------------------------------------------>version:2
01-06 17:12:09.569: I/ruby(7649): oldVersion < 3------------------------------------------------>version:2
01-06 17:12:09.569: I/ruby(7649): oldVersion < 4------------------------------------------------>version:2
01-06 17:12:09.573: I/ruby(7649): oldVersion < 5------------------------------------------------>version:2
01-06 17:12:09.577: I/ruby(7649): newExecSQL---------------------------------------------------->version:2
01-06 17:12:09.612: I/ruby(7649): onOpen-------------------------------------------------------->version:2
01-06 17:12:09.612: I/ruby(7649): createDatabase Version=2-------------------------------------->version:2
01-06 17:12:21.765: I/ruby(7710): ====================================================================================================
01-06 17:12:21.765: I/ruby(7710): onUpgrade oldVersion=2 newVersion=3 DATABASE_VERSION=3-------->version:3
01-06 17:12:21.768: I/ruby(7710): oldVersion < 3------------------------------------------------>version:3
01-06 17:12:21.768: I/ruby(7710): oldVersion < 4------------------------------------------------>version:3
01-06 17:12:21.772: I/ruby(7710): oldVersion < 5------------------------------------------------>version:3
01-06 17:12:21.776: I/ruby(7710): newExecSQL---------------------------------------------------->version:3
01-06 17:12:21.811: I/ruby(7710): onOpen-------------------------------------------------------->version:3
01-06 17:12:21.815: I/ruby(7710): createDatabase Version=3-------------------------------------->version:3
01-06 17:12:37.155: I/ruby(7772): ====================================================================================================
01-06 17:12:37.155: I/ruby(7772): onUpgrade oldVersion=3 newVersion=4 DATABASE_VERSION=4-------->version:4
01-06 17:12:37.159: I/ruby(7772): oldVersion < 4------------------------------------------------>version:4
01-06 17:12:37.159: I/ruby(7772): oldVersion < 5------------------------------------------------>version:4
01-06 17:12:37.163: I/ruby(7772): newExecSQL---------------------------------------------------->version:4
01-06 17:12:37.210: I/ruby(7772): onOpen-------------------------------------------------------->version:4
01-06 17:12:37.210: I/ruby(7772): createDatabase Version=4-------------------------------------->version:4
由上測試結果可看出，若 SQLite 是用 copy assets/xxx.sqlite 產生的 DB，在第一次版本更新時不會執行 onUpgrade() 或 onDowngrade()
version 跳號升版

/*************************************************************************************************************************************/
/* version往上跳號測試(跳號升版)                                                                                                     */
/*************************************************************************************************************************************/
01-06 17:15:54.608: I/ruby(7973): onCreate------------------------------------------------------>version:1
01-06 17:15:54.624: I/ruby(7973): onOpen-------------------------------------------------------->version:1
01-06 17:16:10.718: I/ruby(8037): ====================================================================================================
01-06 17:16:10.718: I/ruby(8037): onUpgrade oldVersion=1 newVersion=3 DATABASE_VERSION=3-------->version:3
01-06 17:16:10.722: I/ruby(8037): oldVersion < 2------------------------------------------------>version:3
01-06 17:16:10.726: I/ruby(8037): oldVersion < 3------------------------------------------------>version:3
01-06 17:16:10.726: I/ruby(8037): oldVersion < 4------------------------------------------------>version:3
01-06 17:16:10.726: I/ruby(8037): oldVersion < 5------------------------------------------------>version:3
01-06 17:16:10.729: I/ruby(8037): newExecSQL---------------------------------------------------->version:3
01-06 17:16:10.796: I/ruby(8037): onOpen-------------------------------------------------------->version:3
01-06 17:16:10.800: I/ruby(8037): createDatabase Version=3-------------------------------------->version:3
01-06 17:16:28.800: I/ruby(8100): ====================================================================================================
01-06 17:16:28.800: I/ruby(8100): onUpgrade oldVersion=3 newVersion=7 DATABASE_VERSION=7-------->version:7
01-06 17:16:28.804: I/ruby(8100): oldVersion < 4------------------------------------------------>version:7
01-06 17:16:28.808: I/ruby(8100): oldVersion < 5------------------------------------------------>version:7
01-06 17:16:28.808: I/ruby(8100): newExecSQL---------------------------------------------------->version:7
01-06 17:16:28.874: I/ruby(8100): onOpen-------------------------------------------------------->version:7
01-06 17:16:28.878: I/ruby(8100): createDatabase Version=7-------------------------------------->version:7
version 順號降版

/*************************************************************************************************************************************/
/* version往下順號測試(順號降版)                                                                                                     */
/*************************************************************************************************************************************/
01-06 17:32:11.253: I/ruby(8454): onCreate------------------------------------------------------>version:1
01-06 17:32:11.284: I/ruby(8454): onOpen-------------------------------------------------------->version:1
01-06 17:32:35.495: I/ruby(8517): ====================================================================================================
01-06 17:32:35.495: I/ruby(8517): onDowngrade oldVersion=5 newVersion=4 DATABASE_VERSION=4------>version:4
01-06 17:32:35.499: I/ruby(8517): newVersion < 5------------------------------------------------>version:4
01-06 17:32:35.526: I/ruby(8517): onOpen-------------------------------------------------------->version:4
01-06 17:32:35.530: I/ruby(8517): createDatabase Version=4-------------------------------------->version:4
01-06 17:32:53.999: I/ruby(8578): ====================================================================================================
01-06 17:32:53.999: I/ruby(8578): onDowngrade oldVersion=4 newVersion=3 DATABASE_VERSION=3------>version:3
01-06 17:32:53.999: I/ruby(8578): newVersion < 4------------------------------------------------>version:3
01-06 17:32:54.003: I/ruby(8578): newVersion < 5------------------------------------------------>version:3
01-06 17:32:54.050: I/ruby(8578): onOpen-------------------------------------------------------->version:3
01-06 17:32:54.054: I/ruby(8578): createDatabase Version=3-------------------------------------->version:3
01-06 17:33:07.343: I/ruby(8640): ====================================================================================================
01-06 17:33:07.343: I/ruby(8640): onDowngrade oldVersion=3 newVersion=2 DATABASE_VERSION=2------>version:2
01-06 17:33:07.343: I/ruby(8640): newVersion < 3------------------------------------------------>version:2
01-06 17:33:07.347: I/ruby(8640): newVersion < 4------------------------------------------------>version:2
01-06 17:33:07.347: I/ruby(8640): newVersion < 5------------------------------------------------>version:2
01-06 17:33:07.417: I/ruby(8640): onOpen-------------------------------------------------------->version:2
01-06 17:33:07.417: I/ruby(8640): createDatabase Version=2-------------------------------------->version:2
version 跳號降版

/*************************************************************************************************************************************/
/* version往下跳號測試(跳號降版)                                                                                                     */
/*************************************************************************************************************************************/
01-06 17:36:54.026: I/ruby(8707): onCreate------------------------------------------------------>version:5
01-06 17:36:54.034: I/ruby(8707): onOpen-------------------------------------------------------->version:5
01-06 17:37:20.085: I/ruby(8771): ====================================================================================================
01-06 17:37:20.089: I/ruby(8771): onDowngrade oldVersion=5 newVersion=3 DATABASE_VERSION=3------>version:3
01-06 17:37:20.089: I/ruby(8771): newVersion < 4------------------------------------------------>version:3
01-06 17:37:20.089: I/ruby(8771): newVersion < 5------------------------------------------------>version:3
01-06 17:37:20.132: I/ruby(8771): onOpen-------------------------------------------------------->version:3
01-06 17:37:20.136: I/ruby(8771): createDatabase Version=3-------------------------------------->version:3
01-06 17:37:33.558: I/ruby(8833): ====================================================================================================
01-06 17:37:33.561: I/ruby(8833): onDowngrade oldVersion=3 newVersion=1 DATABASE_VERSION=1------>version:1
01-06 17:37:33.561: I/ruby(8833): newVersion < 2------------------------------------------------>version:1
01-06 17:37:33.565: I/ruby(8833): newVersion < 3------------------------------------------------>version:1
01-06 17:37:33.569: I/ruby(8833): newVersion < 4------------------------------------------------>version:1
01-06 17:37:33.569: I/ruby(8833): newVersion < 5------------------------------------------------>version:1
01-06 17:37:33.644: I/ruby(8833): onOpen-------------------------------------------------------->version:1
01-06 17:37:33.647: I/ruby(8833): createDatabase Version=1-------------------------------------->version:1
由上述測試結果可看出，execSQL 語法建立的 SQL，version 不管順號還是跳號都可以正常更新 DB

References

範例下載
張貼者： 於
標籤： ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)