Kinx ライブラリ - Database (SQLite3)

# Database / SQLite3

## はじめに

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

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

* 参考
    * 最初の動機 ... [スクリプト言語 KINX（ご紹介）](https://qiita.com/Kray-G/items/ca08b6fb40d15dd0ec76)
        * 個別記事へのリンクは全てここに集約してあります。
    * リポジトリ ... [https://github.com/Kray-G/kinx](https://github.com/Kray-G/kinx)
        * Pull Request 等お待ちしております。 

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

## SQLite3

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

## SQLite

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

### DB 接続

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

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

### Exec/Query

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

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

#### exec

```javascript
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` の結果は抽出した行オブジェクトであり、以下の形式となっている。

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

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

```javascript
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 つずつ実施するより高速に実施できる。

```javascript
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` する。

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

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

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

以下のように指定する。

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

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

### transaction

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

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

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

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

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

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

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

### createTable

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

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

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

### dropTable

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

```javascript
db.dropTable("mytable");
```

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

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

### insert

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

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

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

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

### delete

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

```javascript
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 句                                                             |

以下がサンプル。

```javascript
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 を返すまでフェッチすることで全抽出データを取得できる。

```javascript
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 つずつイテレートしてコールバックするように動作する。

#### 一括で取得する場合

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

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

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

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

#### コールバックさせる場合

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

```javascript
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 を返すとイテレーションを中止して制御を戻す。

```javascript
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](https://github.com/Kray-G/kinx/blob/master/examples/database.kx) にあります。お試しください。

## おわりに

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

ではでは、また次回。