LoginSignup
1
0

More than 3 years have passed since last update.

@Kray-G

Kinx ライブラリ - Database (SQLite3)

Posted at

Database / SQLite3

はじめに

「見た目は JavaScript、頭脳(中身)は Ruby、（安定感は AC/DC）」 でお届けしているスクリプト言語 Kinx。言語はライブラリが命。ということでライブラリの使い方編。

今回は SQLite3 です。実際には後述するように Database オブジェクトを使用したほうが良いです。

SQLite ライブラリ自体は開発初期の段階からあったのですが、イマイチ使いづらかったので改善を考えてました。今回、Database クラスを用意したのでようやく公開です。

SQLite3

SQLite3 はポータブルで使いやすい組み込み RDB。よくお世話になっています。ファイル 1 つで完結すること、そのファイルがプラットホーム非依存なことにより、色々な用途に使える。

SQLite

コア・ライブラリとして SQLite オブジェクトがあるが、実際には後述する Database オブジェクトを使うのが良い。Database クラスは SQLite クラスをラップしてより使いやすいインターフェースを提供するクラス。

DB 接続

SQLite オブジェクトを new SQLite(filename[, timeout]) で作成してファイルに接続。

var db = new SQLite("sample.db", 30000);

Exec/Query

基本的に SQLite オブジェクトには基本メソッドとして exec、prepare、transaction の 3 つのメソッドがある。ただし、後述する Database クラスで、より使いやすいインターフェースを用意している。

SELECT 以外の単発コマンドで exec を使い、それ以外では prepare で SQL オブジェクトを作成して実行する。

exec

db.exec("CREATE TABLE IF NOT EXISTS mytable "
    "("
        "id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "name TEXT NOT NULL"
    ")"
);

prepare

prepare で返された SQL オブジェクトは bind、exec、query、next というメソッドが存在する。全てのメソッドは処理後に自分自身のオブジェクトを返す。

また、next メソッドは query メソッド呼び出し後に有効。next の結果は抽出した行オブジェクトであり、以下の形式となっている。

{
    "columnName": [ "col1", "col2", "col3" ],
    "value": [ val1, val2, val3 ]
}

例えば以下のように使う。

var c, r = db.prepare("SELECT * FROM mytable WHERE (id > ? AND id < ?) OR id = ?")
            .bind(2)
            .bind(8, 10)    // bind() は複数回に分けて実行できる。
            .query();       // これまで設定した値を実際にバインディングして next() 実行可能にする。

while (c = r.next()) {
    var cols = c.columnName;
    var vals = c.value;
    System.println("Record[%d]" % vals[0]);
    for (var i = 0, len = cols.length(); i < len; ++i) {
        System.println("    %-8s = %s" % cols[i] % vals[i]);
    }
}

尚、query は SELECT でのみ有効。SELECT 以外で exec を使用した場合、バインディングして実行した後、バインディングされていた値はその都度リセットされて次の exec に備える。

transaction

transacxtion は一連の操作をロックして行う。例えば、複数の値を一気にインサートしたいときなどは以下のようにする。ロックするので 1 つずつ実施するより高速に実施できる。

var ins = db.prepare("INSERT INTO mytable (name) VALUES (?)");
db.transaction(&{
    for (var i = 0; i < 20; ++i) {
        ins.bind("name(%{i})").exec();
    }
});

Database

クラス Database は上記 SQLite のラッパークラス。以下のメソッドがある。

  • transaction ... トランザクション処理を行う。
  • createTable ... テーブルを作成
  • dropTable ... テーブルを削除
  • insert ... データを挿入
  • delete ... データを削除
  • query ... データを抽出するための cursor オブジェクトを返す
  • queryAll ... データを全て抽出

インスタンス化

普通に new する。

var db = new Database(dbfile[, options]);

インスタンス化する際のオプションは以下のとおり。

  • force ... true の場合、ファイルが存在なければファイルを新規に作成する。false の場合はエラーとして例外を送出する。デフォルトは false。
  • timeout ... データベースの各種操作のタイムアウト値をミリ秒で指定。デフォルトは 10 秒。

以下のように指定する。

var db = new Database("dbfile.db", { force: true });

さらに、データベース名を複数のファイル名の配列として渡すと、最初の db をマスターとしてそれ以降の DB を自動的に ATTACH DATABASE する。その際、エイリアスとして自動的にファイル名の拡張子を除いた部分（いわゆる stem 部分、例えば abc.db であれば abc）を登録する。マスター DB は main で指定し、それ以外はエイリアスで指定する。

transaction

SQLite クラスの transaction より細かく指定できる。通常は同じように利用する。

db.transaction(&{
    ...
});

トランザクション処理が成功した場合は自動的にコミットする。途中で例外が発生した場合はロールバックを試みる。

また、Database クラスの transaction メソッドでは第一引数にモードを指定できる。モードは文字列で指定し、以下を指定可能。

  • 'EXCLUSIVE' ... BEGIN EXCLUSIVE でトランザクションを開始する。
  • 'IMMEDIATE' ... BEGIN IMMEDIATE でトランザクションを開始する。

未指定、または上記以外が指定された場合、BEGIN（BEGIN DEFERRED）である。サンプルは以下の通り。

db.transaction('EXCLUSIVE', &{
    ...
});

createTable

createTable にはテーブル名とスキーマを渡す。スキーマは直接文字列（(id INTEGER, name TEXT) のような形）で渡すか、配列で渡す（単に , で結合されて () で括られる）。

db.createTable("mytable", [
    "id INTEGER PRIMARY KEY AUTOINCREMENT",
    "name TEXT NOT NULL"
]);

尚、常に IF NOT EXISTS で実行される。

dropTable

dropTable には単にテーブル名を渡す。

db.dropTable("mytable");

尚、常に IF EXISTS で実行される。

注意

ステートメントが残っていると database is locked の RuntimeException 例外が発生するので、ステートメントへの参照が残っていない状態で実施すること。

insert

insert には以下のパラメータを指定する。

パラメータ 内容
into 必須、テーブル名
replace true or false, 省略時 false
column 必須、カラム名（文字列）、またはカラム名の配列
bind 必須、column に対するバインディング・データを配列で指定、column で指定したカラムの数だけ必要

複数 insert する場合は transaction で囲んだほうが高速に実施される。

db.transaction(&{
    for (var i = 0; i < 20; ++i) {
        db.insert({
            into: "mytable",
            column: ["name"],
            bind: ["name(%{i})"],
        });
    }
});

delete

from、where、bind で指定。全て必須（必ず bind も必要）。

db.delete({
    from: "mytable",
    where: "id = ?",
    bind: [4]
});

query

条件に合うデータを DatabaseCursor で返す。初回の DatabaseCursor オブジェクトはデータをフェッチしていない状態で返される。

query() に指定できるパラメータは以下の通り。

パラメータ 内容
distinct true or false, 省略時 false
select 省略時は *、もしくはカラム名（文字列） or カラム名の配列
from 必須、テーブル名
innerJoin INNER JOIN 句 （innerJoin、outerJoin は同時指定不可）
outerJoin OUTER JOIN 句
on innerJoin または outerJoin を指定したときだけ有効
where WHERE 句、文字列、または配列（AND で結合）
bind WHERE 句に対するバインディング・データを配列で指定、? の数だけ必要
groupby GROUP BY 句
having HAVING 句
orderby ORDER BY 句 （orderby、orderbyAsc、orderbyDesc は同時指定不可）
orderbyAsc orderby と同様
orderbyDesc ORDER BY 句、DESC 指定
limit LIMIT 句
offset OFFSET 句

以下がサンプル。

var c = db.query({
    select: "*",
    from: "mytable",
    where: "(id > ? AND id < ?) OR id = ?",
    bind: [2, 8, 10]
});

上記復帰値 c は DatabaseCursor オブジェクトで next()、columns()、values() メソッドを持つ。columns() はカラム名の配列を返し、values() は値の配列を返す。

DatabaseCursor オブジェクトは以下のように c.next() が null を返すまでフェッチすることで全抽出データを取得できる。

while (c.next()) {
    var cols = c.columns();
    var vals = c.values();
    System.println("Record[%d]" % vals[0]);
    for (var i = 0, len = cols.length(); i < len; ++i) {
        System.println("    %-8s = %s" % cols[i] % vals[i]);
    }
}

queryAll

queryAll は、コールバックを指定する方法と指定しない方法の 2 通りの使い方がある。コールバックを指定しなければ、全ての抽出データを一括で配列形式で取得できるが、データ量が多い場合メモリを消費してしまう可能性がある。それを回避するため、コールバック方式では 1 つずつイテレートしてコールバックするように動作する。

一括で取得する場合

一括で取得した場合は以下のようにコールバックを記載しない。

var res = db.queryAll({
    select: "*",
    from: "mytable",
    where: "(id > ? AND id < ?) OR id = ?",
    bind: [2, 8, 10]
});

以下のようなデータ構造で返る。

{
    "columns": ["id", "name"],
    "values": [
        [3, "name(2)"],
        [5, "name(4)"],
        [6, "name(5)"],
        [7, "name(6)"],
        [10, "name(9)"]
    ]
}

コールバックさせる場合

コールバックさせる場合は、以下のように関数を渡す。

db.queryAll({
    select: "*",
    from: "mytable",
    where: "(id > ? AND id < ?) OR id = ?",
    bind: [2, 8, 10]
}, &(c, i) => {
    System.println("%2d => " % i, c.toJsonString());
});

以下のようにそれぞれの行のオブジェクトが返される。この場合は DatabaseCursor オブジェクト ではない ので注意。

 0 => {"columns":["id","name"],"values":[3,"name(2)"]}
 1 => {"columns":["id","name"],"values":[5,"name(4)"]}
 2 => {"columns":["id","name"],"values":[6,"name(5)"]}
 3 => {"columns":["id","name"],"values":[7,"name(6)"]}
 4 => {"columns":["id","name"],"values":[10,"name(9)"]}

尚、コールバック関数で明示的に false を返すとイテレーションを中止して制御を戻す。

db.queryAll({
    select: "*",
    from: "mytable",
    where: "(id > ? AND id < ?) OR id = ?",
    bind: [2, 8, 10]
}, &(c, i) => {
    System.println("%2d => " % i, c.toJsonString());
    return false if (i == 2);
});

以下のようになる。

 0 => {"columns":["id","name"],"values":[3,"name(2)"]}
 1 => {"columns":["id","name"],"values":[5,"name(4)"]}
 2 => {"columns":["id","name"],"values":[6,"name(5)"]}

サンプル

サンプル・コードは GitHub にあります。お試しください。

おわりに

SQLite3 使いやすいですね。単純にデータ・ストレージに使ってもいいですし、ロガーのバックエンドに使ったり、ポータブルな設定ファイルとして使ったりと色々使えます。何より RDB や SQL の基本的なことを学習するのにも良いかなー、と思います。

ではでは、また次回。

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0