SQLiteDatabaseのINSERT/UPDATE文でコンフリクト対応する話

ブログYukiの枝折 - SQLiteDatabaseとコンフリクトアルゴリズムからの一部転載です.

はじめに

SQLiteはデータのコンフリクトを解消するコンフリクトアルゴリズムをサポートしており, CREATE TABLE構文でON CONFLICT句として指定できる.
INSERT, UPDATE構文では文体をより自然にするためにON CONFLICTではなくOR句として指定される.

Androidでもこれを使うことができる.
SQLiteDatabaseクラスを使ってINSERT or UPDATEをする際にコンフリクトアルゴリズムを指定できるメソッドがAPI Lv.8から用意されている.

• SQL As Understood By SQLite
• [Android Developers - SQLiteDatabase.insertWithOnConflict](http://developer.android.com/intl/ja/reference/android/database/sqlite/SQLiteDatabase.html#insertWithOnConflict(java.lang.String, java.lang.String, android.content.ContentValues, int))
• [Android Developers - SQLiteDatabase.updateWithOnConflict](http://developer.android.com/intl/ja/reference/android/database/sqlite/SQLiteDatabase.html#updateWithOnConflict(java.lang.String, android.content.ContentValues, java.lang.String, java.lang.String[], int))

SQLiteにおけるコンフリクト要因としてはUNIQUE, NOT NULL, CHECK, PRIMARY KEYといったカラム制約がある. ただし, FOREIGN KEY制約についてはこれが適用されない.

CONFLICT_*

AndroidではSQLiteDatabase.CONFLICT_*にあたる各定数で`コンフリクトアルゴリズムを指定でき, 下記がサポートされている.

• CONFLICT_ROLLBACK
• CONFLICT_ABORT
• CONFLICT_FAIL
• CONFLICT_IGNORE
• CONFLICT_REPLACE
• CONFLICT_NONE

SQLiteDatabaseは内部で下記のようにSQLを構築・発行している.
つまり, SQLiteのON CONFLICT または ORの仕様を理解すればよい.

private static final String[] CONFLICT_VALUES = new String[]
            {"",              // 0: CONFLICT_NONE
             " OR ROLLBACK ", // 1: CONFLICT_ROLLBACK
             " OR FAIL ",     // 2: CONFLICT_FAIL
             " OR IGNORE ",   // 3: CONFLICT_IGNORE
             " OR REPLACE "}; // 4: CONFLICT_REPLACE

public long insertWithOnConflict(String table, 
                                 String nullColumnHack,
                                 ContentValues initialValues, 
                                 int conflictAlgorithm) {
        acquireReference();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT");
            sql.append(CONFLICT_VALUES[conflictAlgorithm]);
            sql.append(" INTO ");
            sql.append(table);
            sql.append('(');
            ...

検証

今回使用するテーブルスキーマは下記

sqlite> .schema test
CREATE TABLE test ( _id INTEGER PRIMARY KEY, data STRING);

次のテストデータを用意.
_idは, あえて2をskipしておく.

sqlite> INSERT INTO test VALUES (1, "A");
sqlite> INSERT INTO test VALUES (3, "B");
sqlite> INSERT INTO test VALUES (4, "C");
sqlite> SELECT * FROM test;
_id         data      
----------  ----------
1           A         
3           B         
4           C         

このテーブルにINSERTやUPDATE文を発行してコンフリクトを発生させた.

CONFLICT_ROLLBACK

OR ROLLBACKの指定.
コンフリクトが発生した場合, トランザクションはロールバックされる.

トランザクションをロールバックした後, SQLiteExceptionを発生させる.
一般的にtry-catch-finally節でデータベーストランザクションの操作を行うが, OR ROLLBACKを指定した場合, 発行先のSQLがロールバックまでを実行する.
そのため, OR ROLLBACK句によりSQLiteExceptionが発生したタイミングでは既にトランザクションが閉じているので注意が必要.
すでにトランザクションが閉じられたdbインスタンスへのdb.endTransaction()コールは新たなSQLiteExceptionを生む.

もしOR ROLLBACKがトランザクション内での実行ではなく, 単発のSQLである場合はCONFLICT_ABORTと同じ挙動になる.

try {
  db.beginTransaction();
  // ...
  // cv1はロールバックされ, cv2は失敗, cv3はSQLiteExceptionにより実行されない.
  db.insertWithOnConflict("test", null, cv1, CONFLICT_ROLLBACK);
  db.insertWithOnConflict("test", null, cv2, CONFLICT_ROLLBACK); // CONFLICT!
  db.insertWithOnConflict("test", null, cv3, CONFLICT_ROLLBACK);
  // ...
  db.setTransactionSuccessful();
  db.endTransaction();
} catch (SQLiteException e) {
  // Conflict occur

CONFLICT_ABORT

OR ABORTの指定.
コンフリクトが発生した場合, そのSQLコマンド自体が行った変更が取り消される.

SQLコマンドによる変更を取り消した後, SQLiteExceptionを発生させるがロールバックは実行されない.
これはSQLコマンドが複数行に作用する場合に効果がある.

OR ABORTはコマンドに閉じて作用するため, トランザクションで全体をロールバックするようなケースでは役に立たない.

try {
  // _idを1つずつインクリメントするが, 2レコード目でコンフリクトが発生する. 
  // クエリの結果, どのレコードも変更されないで終わる. 
  db.execSQL("UPDATE OR ABORT test SET _id=_id+1");
} catch (SQLiteException e) {
  // Conflict occur

CONFLICT_FAIL

OR FAILの指定.
コンフリクトが発生した場合でも, そのSQLコマンド自体がそこまでに行った変更は取り消さない.

コンフリクトが発生した後, SQLiteExceptionを発生させるがロールバックは実行されない.
これはSQLコマンドが複数行に作用する場合に効果がある.
OR ABORTがそのコマンドの失敗を取り消すのに対して, OR ABORTは取り消さない.

OR FAILはコマンドに閉じて作用するため, トランザクションで全体をロールバックするようなケースでは役に立たない.

try {
  // _idを1つずつインクリメントするが, 2レコード目でコンフリクトが発生する. 
  // クエリの結果, コンフリクトが発生しなかった最初のレコードだけ変更される. 
  db.execSQL("UPDATE OR ABORT test SET _id=_id+1");
} catch (SQLiteException e) {
  // Conflict occur

CONFLICT_IGNORE

OR IGNOREの指定.
コンフリクトが発生した場合でも, それを無視して処理を継続する.
複数行の更新を行うクエリの場合, コンフリクトが発生した1行に対しては無視され, その前後の処理は取り消されない.

コンフリクトが発生してもSQLiteExceptionは発生せず, ロールバックも実行されない.

try {
  // cv1は成功, cv2はコンフリクトで失敗するがエラーは無視される. cv3は成功.
  db.insertWithOnConflict("test", null, cv1, CONFLICT_IGNORE);
  db.insertWithOnConflict("test", null, cv2, CONFLICT_IGNORE);  // CONFLICT!
  db.insertWithOnConflict("test", null, cv3, CONFLICT_IGNORE);
} catch (SQLiteException e) {

CONFLICT_REPLACE

OR REPLACEの指定.
コンフリクトが発生した場合, 次のルールでコンフリクトの解消を試す.

1. コンフリクトの原因がUNIQUE or PRIMARY KEY制約によるものの場合, 既存の行を削除して新たにINSERT or UPDATEを試みる.
2. コンフリクトの原因がNOT NULL制約によるものの場合, NULL値をdefault valueに置き換えて試みる
3. コンフリクトの原因がNOT NULL制約によるもの, かつ, default vlaueが定義されていない場合, CONFLICT_ABORTと同じ振る舞いとする.
4. コンフリクトの原因がCHECK制約によるものの場合はCONFLICT_ABORTと同じ振る舞いとする.

コンフリクトの解消が成功した場合はSQLiteExceptionは発生しない.
コンフリクトの解消が失敗した場合はSQLiteExceptionが発生する.

  // cv1は成功, cv2はREPLACEされ成功, cv3は成功
  db.insertWithOnConflict("test", null, cv1, CONFLICT_REPLACE);
  db.insertWithOnConflict("test", null, cv2, CONFLICT_REPLACE);  // CONFLICT!
  db.insertWithOnConflict("test", null, cv3, CONFLICT_REPLACE);

CONFLICT_NONE

OR句の指定なし.
CONFLICT_ABORTと同じ挙動となる.

ちなみにON CONFLICT, OR句はSQL標準ではない.

以上.