Android SQLiteの使用とmigration

schemaの作成

スキーマはSQLiteOpenHelperを実装したクラスで作成する。 DB_VERSIONはmigrationを行う際にインクリメントすることでonUpgrade()に渡される。

val DB_NAME = "MyApplication"
val DB_VERSION = 1
class MyApplicationDBOpenHelper(context: Context): SQLiteOpenHelper(context, DB_NAME, null, DB_VERSION) {
    override fun onCreate(db: SQLiteDatabase?) {
        db?.execSQL( "create table foo ( _id INTEGER PRIMARY KEY AUTOINCREMENT, titleId integer, title text, created_at timestamp  )" )
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
    }
}

onCreate

onCreate()はDB_NAMEのデータベースがアプリ上にまだ作成されていない場合に呼び出される。また実行時に現在のDB_VERSIONを実行済みバージョンとして記録する。

Primary Keyは_idとするのがお作法らしい。違っていると思わぬところでハマることがあるとか。

尚、onCreate()とonUpgrade()は同時に呼び出されることはないのでmigration時には考慮する必要がある。

onUpgrade / migration

onUpgrade()は実行済みバージョンとソース上のDB_VERSIONに差異があった場合に実行される。但し、過去に渡ってのすべてのmigrationは管理しておらず、あくまで、そのアプリが以前に利用していたDB_VERSION:oldVersionが渡されるのみである。よって、実装上で過去に行ったすべてのmigrationを管理しておく必要がある。

例えば、DB_VERSION 1 -> 2 -> 3 と3度のmigrationを行った、以下のパターンを考慮する必要がある。

DB_VERSION:1 から 2を介さずに3のアプリをインストール
DB_VERSION:3からの新規ユーザ

DB_VERSION:1 から 2を介さずに3のアプリをインストール

private fun version2(db: SQLiteDatabase?){
    db?.execSQL("alter table foo add t1 text" )
}

private fun version3(db: SQLiteDatabase?){
    db?.execSQL("alter table foo add t2 text" )
}

override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
    if( oldVersion < 2 ){
        version2(db)
    }
    if( oldVersion < 3 ){
        version3(db)
    }
}

こうすることでDB_VERSION:1からのユーザは履歴を追ってmigrationを完了させることができる。

DB_VERSION:3からの新規ユーザ

また、DB_VERSION:3からの新規ユーザはononUpgrade()はトリガされないので、onCreate()```も同様で最新のスキーマに更新する必要がある。

override fun onCreate(db: SQLiteDatabase?) {
    db?.execSQL( "create table foo ( _id INTEGER PRIMARY KEY AUTOINCREMENT, titleId integer, title text, created_at timestamp  )" )
    version2(db)
    version3(db)
}

SQLクエリの実行

INSERT

try {
    //thisはContext
    val db = MyApplicationDBOpenHelper(this).writableDatabase
    db.execSQL(
        "INSERT INTO foo ( titleId, title, created_at ) values (?, ?)", 
        arrayOf( 1, "タイトル名", SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSZ").format(Date()) )
     )
}catch (e:IllegalArgumentException){
     Log.d("LOG_ERROR", e.stackTrace.toString() )
}

SQLiteのTIMESTAMP型は内部的には文字列で扱っている？よう。

SELECT

rawQuery()のバインドするパラメータはすべてStringである必要があるようで、例えintegerであっても文字列として渡す。 TIMESTAMP型はgetString()で取り出せる。

val db = MyApplicationDBOpenHelper(this).readableDatabase
val cursor = db.rawQuery("select titleId, created_at from foo where titleId = ?", arrayOf(1.toString()))
while (cursor.moveToNext()) {
    Log.d( "FOO_RECORD", cursor.getInt(cursor.getColumnIndex("titleId")).toString() )
    Log.d( "FOO_RECORD", cursor.getString(cursor.getColumnIndex("created_at")).toString() )
}
cursor.close()

尚、SELECT時に

Failed to read row 0, column -1 from a CursorWindow which has 1 rows, 1 columns.

のようなエラーが出た場合、getColumnIndex("String")に渡すカラム名はDDLに記載したカラム名と大文字小文字も含めて一致してない場合がある。上記の例でいうと以下のようなケース。

cursor.getInt(cursor.getColumnIndex("titleID") )
/*
正しくは
cursor.getInt(cursor.getColumnIndex("titleId") )
*/

また、Primary keyが_idでない場合も出るとか出ないとか。

リンク

追憶行