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 (表結合)の意味の違い

SQL 標準では JOIN 句による結合構文は次のような種類があります。

INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
CROSS JOIN

LEFT JOIN, RIGHT JOIN など、よく使われる構文は上記の省略形です。

・ただの JOIN は INNER JOIN の省略形。
・LEFT JOIN は LEFT OUTER JOIN の省略形。
・RIGHT JOIN は RIGHT OUTER JOIN の省略形。

それぞれの JOIN 構文の意味は次のとおりです。

 

INNER JOIN: 内部結合

指定したカラムについて同じ値を持つレコード同士を結びつける。
内積なので指定したカラムの値がどちらかにしかないレコードについては結果に含まれません。

SELECT * FROM table1 INNER JOIN table2 USING(id)

は、次の SQL 文と同じ結果になります。

SELECT * FROM table1, table2 WHERE table1.id = table2.id

 

LEFT OUTER JOIN: 外部結合

左のテーブルを基準にして、指定したカラムについて同じ値を持つレコード同士を結びつける。
値が右のテーブルにあり左のテーブルにない場合は INNER JOIN 同様結果に含まれませんが、
値が左のテーブルにあり右のテーブルにない場合は INNER JOIN と異なり 右のテーブルのカラムには全て NULL がパディングされ、結果に含まれます。
 
このため、

SELECT * FROM table1 LEFT OUTER JOIN table2 USING(id) WHERE table2.id IS NOT NULL

は、

SELECT * FROM table1 INNER JOIN table2 USING(id)

と同じ結果になります。
 

RIGHT OUTER JOIN: 外部結合

LEFT OUTER JOIN の左右の意味を入れ換えたもの。

SELECT * FROM table1 RIGHT OUTER JOIN table2 USING(id)

SELECT * FROM table2 LEFT OUTER JOIN table1 USING(id)

と同じ結果になります。
 

CROSS JOIN: 交差結合

ON, USING で条件を指定しない場合、左右のテーブルの直積を返します。
つまり、左右のテーブルにそれぞれ2行のレコード、3行のレコードが格納されている場合、結合結果は全てのレコードの組合せになり合計 2×3=6 行のレコードになります。
PostgreSQL や MySQL では ON, USING を指定すると INNER JOIN と等価になります。

SELECT * FROM table1 CROSS JOIN table2

は次の SQL 文と等価です。

SELECT * FROM table1, table2

また、PostgreSQL, MySQL では

SELECT * FROM table1 CROSS JOIN table2 USING(id)

は次の SQL 文と等価です。

SELECT * FROM table1 INNER JOIN table2 USING(id)

 

■ON, USING, NATURALによる結合条件指定

CROSS JOIN をのぞき、JOIN 句には ON, USING, NATURAL のいずれかを指定します。

INNER JOIN … ON 結合条件
INNER JOIN … USING(結合カラム, … )
NATURAL INNER JOIN …

 

ON:

WHERE 句と同じ書式で結合条件を絞り込む。
書式:

SELECT … FROM table1 JOIN table2 ON 条件式

table1, table2 のidが同じカラム同士を結合したい場合は次のようにします。

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

AND, OR など複雑な条件で絞り込む事も可能です。

SELECT * FROM table1 AS t1
  LEFT JOIN table2 AS t2 ON (
    (t1.id = t2.id OR t1.name = t2.name)
    AND t1.salary >= t2.age * 1000
  )

ON に指定する結合条件には、 WHERE 同様どんな条件式でも指定することが可能ですが、テーブル結合に関係のない条件式はWHERE句に書くようにしましょう。
 

USING:

カラム名で結合条件を絞り込む。
書式:

SELECT … FROM table1 JOIN table2 USING(カラム1 , カラム2, …)

両方のテーブルに同じカラム名がある場合に有効です。

SELECT * FROM table1 LEFT JOIN table2 USING(id)

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

と同等です。複数指定した場合は全てのカラムの値が一致するレコードを結合します。
 

NATURAL:

両方のテーブルを比較して名前が同じ全てのカラムを結合条件にして絞り込む。
書式:

SELECT … FROM table1 NATURAL JOIN table2

USING に両テーブルの共通カラムを全て指定したものと同じです。すなわち、

SELECT * FROM table1 NATURAL LEFT JOIN table2

は、両方のテーブルに id, name, age が存在する場合、

SELECT * FROM table1 LEFT JOIN table2 USING (id, name, age)

を指定したのと同等の結果になります。
 

■複数のJOIN句を組み合わせる

3つ以上のテーブルを複数のJOIN 句で結合する事もできます。
例として、table1, table2. table3 全てのテーブルに同じidが存在するレコードのみ抽出する場合は次のようになります。

SELECT * FROM table1
  INNER JOIN table2 USING(id)
  INNER JOIN table3 USING(id)

複数のJOIN を組み合わせる時の結合順序は左から順に評価されます。結合順序を制御したい場合、カッコが使用できます。

SELECT * FROM table1 AS t1
  LEFT JOIN ( table2 AS t2 INNER JOIN table3 USING(id)) ON t1.value = t2.tag
)

この例では table2, table3の同じidのレコードをINNER JOIN 句を使い結合し、その後 table1 と LEFT JOIN 句で結合されます。
 

参考:

SELECT(PostgreSQL 8.2.5 マニュアル)
MySQL 5.1 リファレンスマニュアル 12.2.7.1 JOIN 構文(MySQL AB)
特殊な結合演算子 SQL(TELCHSCORE by 4DD)
SQL92

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 が認識できるクエリ言語