サーバーサイドエンジニアの田実です！
YappliはSQLite3を使ったマルチテナントアーキテクチャを採用しています。
今回は、SQLite3の運用でよく使うコマンドやちょっとマニアックな仕様についてまとめてみました！
- CLIでクエリ結果を縦に表示したい
- スキーマやテーブル一覧を見たい
- JSON関数を使いたい
- 独自関数を使いたい
- 比較演算子に正規表現を使いたい
- 空き領域を開放したい
- ジャーナルモード
- 読み込みモードについて
- まとめ
CLIでクエリ結果を縦に表示したい
MySQLでいうところの \G 、PostgreSQLでいうところの \x をしたい場合、 .mode line を使って表示モードを切り替えれます。
sqlite> .mode line sqlite> select * from hoge; id = test code = hoge description = fuga
ちなみに指定できるモードは line 以外にも column list csv などがあります。
.mode MODE ?TABLE? Set output mode
MODE is one of:
ascii Columns/rows delimited by 0x1F and 0x1E
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by "|"
quote Escape answers as for SQL
tabs Tab-separated values
tcl TCL list elements
例えば、CSVエクスポートをしたい場合は以下のようなコマンドを実行します。
$ sqlite3 -header -csv {dbファイル} "SELECT * FROM xxx"
スキーマやテーブル一覧を見たい
CLIの .schema や .table コマンドを使ってテーブル情報を確認できます。
# 全テーブルのCREATE TABLE文を出力
.schema
# テーブル指定
.schema hoge
# テーブル一覧
.table
JSON関数を使いたい
JSON1拡張を組み込むとJSON関数が使えるようになります。
SELECT json_extract('{"foo":"bar"}', '$.foo'); -- => bar
PostgreSQLの jsonb_array_elements のような配列操作も可能です。
SELECT distinct t.id, t.body FROM json_table AS t, json_each(t.body, '$.items') AS v WHERE json_extract(v.value, '$.foo') = 'bar';
独自関数を使いたい
SQLite3はアプリケーション側でDBファイルの操作を行うため、呼び出し元のプログラミング言語を使った関数を簡単に定義することができます。
PHP（PDO）だと以下のようにして独自関数を定義することができます。
<?php $db = new \PDO('sqlite::memory:'); $db->sqliteCreateFunction('hello', function($v) { return 'hello '.$v; }); $stmt = $db->query('SELECT hello("world");'); echo $stmt->fetch()[0]; // => hello world
Go（mattn/go-sqlite3）だとこんな感じ。
package main import ( "database/sql" "fmt" sqlite "github.com/mattn/go-sqlite3" ) func main() { sql.Register("sqlite3_custom", &sqlite.SQLiteDriver{ ConnectHook: func(conn *sqlite.SQLiteConn) error { f := func(v string) string { return "hello " + v } if err := conn.RegisterFunc("hello", f, true); err != nil { return err } return nil }, }) db, err := sql.Open("sqlite3_custom", ":memory:") if err != nil { panic(err) } defer db.Close() var r string err = db.QueryRow("SELECT hello('world')").Scan(&r) if err != nil { panic(err) } fmt.Println(r) // => hello world }
比較演算子に正規表現を使いたい
SQLite3には比較演算子のREGEXPがありますが、REGEXPを利用するには事前にユーザ側でregexp()の独自関数を定義する必要があります。
<?php $db = new \PDO('sqlite::memory:'); $db->sqliteCreateFunction('regexp', function($pattern, $value) { mb_regex_encoding('UTF-8'); return (false !== mb_ereg($pattern, $value)) ? 1 : 0; }); $stmt = $db->query("SELECT 'hello world' REGEXP '^hello.*'"); echo $stmt->fetch()[0]; // => 1
空き領域を開放したい
他のRDBMSと同様、大量のINSERT＆DELETEを繰り返すと空き領域ができてしまいレコード数よりも多くの領域を取ってしまいます。 そこで、SQLite3でもバキュームの仕組みであるvacuumコマンドを使って空き領域を開放することができます。
$ wc -c test.db 38989824 test.db $ sqlite3 test.db 'vacuum' $ wc -c test.db 8192 test.db
ジャーナルモード
障害復旧時に利用されるロールバックジャーナルに関するジャーナルモードを選択できます。
| モード | 説明 |
|---|---|
| DELETE | ロールバックジャーナルである -journal ファイルを作成しコミット後に -journal ファイルを削除します。 |
| TRUNCATE | DELETEモードとほぼ同じですが、コミット後は -journal ファイルを削除せずサイズ０のファイルにします。*1 |
| WAL | -shm -wal ファイルを作成し、コミット内容は -wal ファイルに反映されます。 |
WALの場合は実行速度が早くコンカレントな実行ができるのがメリットですが、本体＋ -wal ファイルでDBの状態になるので扱い方に注意が必要です。*2
上記以外にもインメモリにロールバックジャーナルを保存する MEMORY やロールバックジャーナルを作成しない＝アトミックな処理が行えない OFF というモードがあったりします（OFFモードの利用用途はあるんだろうか…ｗ）
ちなみに、YappliではSQLite3ファイルのコピーを簡単にするため、 WALではなくDELETE/TRUNCATEを採用しています。
PRAGMA journal_mode=TRUNATE;
読み込みモードについて
SQLite3のファイルオープン時にクエリパラメータを指定することで、読み込みモードを指定することができます。
example.sqlite?mode=ro
以下のEFS移行では、SQLite3がReadOnly（mode=ro）でもファイルロックを行うため、DBの読み取りのパフォーマンスが落ちてしまう問題が発生していました。 tech.yappli.io
こちらの場合、nolockパラメータを設定することでファイルロックを行わないようにしてパフォーマンスの改善を行いました。
ファイルオープン時に指定できるパラメータは以下に記載されています。
ジャーナルモードのようにPRAGMAで設定できるパラメータもあります。
まとめ
SQLite3の基本的なものからちょっとマニアックな情報までご紹介しました！ YappliではSQLite3をプロダクションに導入しており、今のところ大きな問題なく（？）運用できています。*3
この記事が皆様のSQLite3ライフに役立てば幸いです！ｗ
