SQLite3の備忘録

SQLite3 の備忘録

手軽に独自関数を定義できる

組み込みなので独自関数をプラグインとして事前ビルドすることなくアプリケーション内で定義して利用できる。
以下、PHPでやる例↓

<?php
 
  $db = new \PDO('sqlite::memory:');
  $db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
  // ユーザー定義関数 regexp を登録
  $db->sqliteCreateFunction('hello', function() {
    return 'world';
  });
  $stmt = $db->query('SELECT hello();');
  var_dump($stmt->fetchAll());

SQLite3にはLIKEのような比較演算子としてREGEXPが使えるのだが、これを利用するには事前にユーザ側でregexp()の独自関数を定義する必要がある。

<?php

$db = new \PDO('sqlite::memory:');
$db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$db->sqliteCreateFunction('regexp', function($pattern, $value) {
  mb_regex_encoding('UTF-8');
  return (false !== mb_ereg($pattern, $value)) ? 1 : 0;
});
$stmt = $db->query("SELECT 'world' REGEXP '^worl.*'");
var_dump($stmt->fetchAll());

mode=roでもflockされる

Goで以下のURI filenamesでシステムコールを見てみると… file:example.sqlite?mode=ro&nolock=0

$ sudo dtruss ./main 2>&1 | grep fcntl

fcntl(0x0, 0x3, 0x0)             = 2 0
fcntl(0x1, 0x3, 0x0)             = 1 0
fcntl(0x2, 0x3, 0x0)             = 1 0
fcntl_nocancel(0x3, 0x32, 0x7FFEEFBFEAE0)                = 0 0
fcntl(0x3, 0x7, 0x7FFEEFBFDDA0)          = 0 0
fcntl(0x3, 0x8, 0x7FFEEFBFE858)          = 0 0
fcntl(0x3, 0x8, 0x7FFEEFBFE858)          = 0 0
fcntl(0x3, 0x8, 0x7FFEEFBFE858)          = 0 0
fcntl(0x3, 0x8, 0x7FFEEFBFE340)          = 0 0
fcntl(0x3, 0x8, 0x7FFEEFBFEE58)          = 0 0
fcntl(0x3, 0x8, 0x7FFEEFBFEE58)          = 0 0
fcntl(0x3, 0x8, 0x7FFEEFBFEE58)          = 0 0
fcntl(0x3, 0x8, 0x7FFEEFBFEE60)          = 0 0

nolock file:example.sqlite?mode=ro&nolock=1 の場合

$ sudo dtruss ./main 2>&1 | grep fcntl

fcntl(0x0, 0x3, 0x0)             = 2 0
fcntl(0x1, 0x3, 0x0)             = 1 0
fcntl(0x2, 0x3, 0x0)             = 1 0
fcntl_nocancel(0x3, 0x32, 0x7FFEEFBFEAE0)                = 0 0
fcntl(0x3, 0x7, 0x7FFEEFBFDDA0)          = 0 0

fcntlの第２引数が0x8となっているのは F_SETLK を指定しているところで、nolockしていない場合は頻繁にflockされる。

EFSとかだとflockが多いとパフォーマンス的に厳しいのでreadonlyなアプリケーションであればnolock=1を指定するとパフォーマンスが改善されるかも？

組み込み関数はビルド時に指定可能

JSON関数を使うにはビルド時に指定する必要がある。Go言語だとこんな感じでビルドする。

$ go run --tags "json1" main.go 

SELECT json_extract('{"foo":"bar"}', '$.foo');
-- => bar

jsonb_array_elements的なことをやる

JSONの中に配列が入っている場合、その配列を展開してテーブル化できる。

SELECT distinct t.id, t.body 
FROM json_table AS t, json_each(t.body, '$.items') AS v
WHERE json_extract(v.value, '$.foo') LIKE '%bar%';

その他

PHPのpdo_sqliteでURI Filenamesを今まで指定できず、上記nolockなどのパラメータをURI指定できなかったのだが、先日URI Filenamesを指定できるようにするPR を投げて無事マージされたりしたので、次のバージョンで入るかも？