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
でない場合も出るとか出ないとか。