SQLiteException – file is encrypted or is not a database

SQLiteOpenHelper で表題の例外が出てしまったので対策メモ。
 LogCat:

02-22 17:26:58.351: ERROR/Database(23305): Failed to setLocale() when constructing, closing the database
02-22 17:26:58.351: ERROR/Database(23305): android.database.sqlite.SQLiteException: file is encrypted or is not a database
02-22 17:26:58.351: ERROR/Database(23305): at android.database.sqlite.SQLiteDatabase.native_setLocale(Native Method)
02-22 17:26:58.351: ERROR/Database(23305): at android.database.sqlite.SQLiteDatabase.setLocale(SQLiteDatabase.java:1751)
02-22 17:26:58.351: ERROR/Database(23305): at android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:1701)
02-22 17:26:58.351: ERROR/Database(23305): at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:739)
02-22 17:26:58.351: ERROR/Database(23305): at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:761)
02-22 17:26:58.351: ERROR/Database(23305): at android.database.sqlite.SQLiteDatabase.openOrCreateDatabase(SQLiteDatabase.java:754)
02-22 17:26:58.351: ERROR/Database(23305): at android.app.ApplicationContext.openOrCreateDatabase(ApplicationContext.java:497)
02-22 17:26:58.351: ERROR/Database(23305): at android.content.ContextWrapper.openOrCreateDatabase(ContextWrapper.java:193)
02-22 17:26:58.351: ERROR/Database(23305): at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:98)
// 以下アプリ内の getWritableDatabase() 参照箇所に続く

原因を調べたところ、どうもSQLiteのデータベースファイルが破損されているようなので、接続時にエラーの時はファイルをクリアして再接続するようにオーバーライド実装して対応しました。

@Override
public synchronized SQLiteDatabase getWritableDatabase() {
        try {
                return super.getWritableDatabase();
        } catch (SQLiteException e) {
                final File file = mContext.getDatabasePath(DATABASE_NAME);
                file.delete();
                return super.getWritableDatabase();
        }
}
 
@Override
public synchronized SQLiteDatabase getReadableDatabase() {
        try {
                return super.getReadableDatabase();
        } catch (SQLiteException e) {
                final File file = mContext.getDatabasePath(DATABASE_NAME);
                file.delete();
                return super.getReadableDatabase();
        }
}

参考:
http://d.hatena.ne.jp/esmasui/20091103/1257276598

SQLiteOpenHelper でテーブルカラム変更時にデータを引き継ぐ

SQLiteOpenHelper の実装サンプルです。
onUpgrade() の実装は DROP TABLE クエリを実行するのが簡単なのですが、
それだとDBバージョンアップごとにデータがリセットされてしまいます。
 
バージョンアップでカラム追加、削除を行った時も共通カラムについては引き継げるように、こちらの回答を参考に実装してみました。

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
 
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
 
class DatabaseHelper extends SQLiteOpenHelper {
/**
 * DBファイル名
 */
private static final String DATABASE_NAME = “sample.db”;
/**
 * DBバージョン
 * (既存のDBとバージョン値が異なる時だけ onUpgrade()が呼ばれる)
 */
private static final int DATABASE_VERSION = 1;
/**
 * コンストラクタ
 */
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
 
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(“CREATE TABLE table1 (”
+ “name text NOT NULL, ”
+ “birthdate INTEGER NOT NULL”
+ “)”);
}
 
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// 指定したテーブルのカラム構成をチェックし、
// 同名のカラムについてはアップグレード後もデータを引き継ぎます。
// 同名のカラムで型に互換性がない場合はエラーになるので注意。

// 更新対象のテーブル
final String targetTable = “table1”;
db.beginTransaction();
try {
// 元カラム一覧
final List<String> columns = getColumns(db, targetTable);
// 初期化
db.execSQL(“ALTER TABLE ” + targetTable + ” RENAME TO temp_”
+ targetTable);
onCreate(db);
// 新カラム一覧
final List<String> newColumns = getColumns(db, targetTable);
 
// 変化しないカラムのみ抽出
columns.retainAll(newColumns);
 
// 共通データを移す。(OLDにしか存在しないものは捨てられ, NEWにしか存在しないものはNULLになる)
final String cols = join(columns, “,”);
db.execSQL(String.format(
“INSERT INTO %s (%s) SELECT %s from temp_%s”, targetTable,
cols, cols, targetTable));
// 終了処理
db.execSQL(“DROP TABLE temp_” + targetTable);
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
}
 
/**
 * 指定したテーブルのカラム名リストを取得する。
 *
 * @param db
 * @param tableName
 * @return カラム名のリスト
 */
private 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()));
}
} finally {
if (c != null)
c.close();
}
return ar;
}
 
/**
 * 文字列を任意の区切り文字で連結する。
 *
 * @param list
 * 文字列のリスト
 * @param delim
 * 区切り文字
 * @return 連結後の文字列
 */
private static String join(List<String> list, String delim) {
final StringBuilder buf = new StringBuilder();
final 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();
}
 
}

SQLiteOpenHelper(Android Developers)
SQLiteOpenHelper onUpgrade() Confusion Android

SQL の基礎 – JOIN (表結合)の意味の違い

MySQL / PostgreSQL / Oracle すべてのSQLサーバで基準になっているSQL標準のJOIN(LEFT JOINなど)について、基礎からしっかりまとめてみました。
続きを読む SQL の基礎 – JOIN (表結合)の意味の違い

SQLite でパフォーマンスチューニング – インデックス

クエリを実行して、インデックスの利用状況を調べるには、公式サイトからダウンロードできる idxchk を使います。

tclsqlite3 idxchk db_filename ‘SELECT … ‘

結果は次のような感じになります。

 table index(es) column(s)
 —— ———- ——————————
 foo (none)
 boo <pk> <integer primary key or ro>
 
 SQLite version: 3.2.8

この場合 boo テーブルの Primary Key がインデックスのように作用していて、boo, foo にはインデックスがない、あるいはこのクエリに影響を与えるインデックスがない、ということです。
参考:
IdxChk英語のオンラインマニュアル

SQLite でパフォーマンスチューニング – クエリのベンチマーク

パフォーマンスチューニングにおいて、クエリの実行時間を調べるのは基本ですが、
SQLite では SQL 文だけでは、かかった時間を調べることができなさそうなのでたとえば次のようにします。

time sqlite db_filename “SELECT …” > /dev/null

結果はこんな感じになります。

real 0m0.239s
user 0m0.050s
sys 0m0.060s

time は AT&T UNIX 系標準のコマンドで、引数に渡したプログラムの実行時間を出力します。
 
もちろん、SQLite にかぎらず、どんなプログラムに対しても実行可能です。
 
参照:
MANPAGE of TIME

SQLite3.x で COPY を使うとエラーになる

SQLite3.x で

COPY footable from ‘foo.csv’ USING DELIMITERS ‘,’;

などとして csv から読み込もうとすると、

SQL error: near “COPY”: syntax error

となり実行できません。
 
調べてみると、3.0 以降削除された機能だそうで、公式のバグトラックによると

3.0 以降、不自由ですが COPY コマンドは削除しました。
このコマンドは SQL 非標準であり、UTF-16 での実装方法についてどうすればいいか見出せなかったためです。

とのことでした。
 
2.x と 3.x を両方いれて、2.x で csv からコピーしてdumpしてやることで 3.x でも CSV の利用は可能です。

$ sqlite /tmp/foocsv.tmp “COPY foo FROM ‘foo.csv’ USING DELIMITERS ‘,'”
$ sqlite /tmp/foocsv.tmp ‘.dump’ > /tmp/foocsv.sql
$ sqlite3 ./applyTo.db < /tmp/foocsv.sql

 
参考:
Ticket 982: COPY command does not work in 3.0.8 as it does in 2.8.13
SQLite が認識できるクエリ言語