Big Sky

なんとなくテーブルにデータがINSERTされたらGrowlされる...なんて仕組み作って見ようと思った。それだけ。
sqliteでextensionを作る。growlはWindowsのGNTPにも対応したmattn謹製gntp-sendを使う。

mattn's gntp-send at master - GitHub

command line program that send to growl using GNTP protocol.

http://github.com/mattn/gntp-send

gntp-sendはコマンドラインプログラムだけど、外部からライブラリとしても使える様にしてあります。

#include <stdlib.h>
#include <sqlite3ext.h>
#include <growl.h>

SQLITE_EXTENSION_INIT1
static void growl_func(sqlite3_context *context, int argc, sqlite3_value **argv) {
    if (argc == 1) {
        const char *text  = (const char *)sqlite3_value_text(argv[0]);
        growl("localhost", "sqlite3", "sqlite3-trigger", "database-update", text, NULL, NULL, NULL);
    }
}
__declspec(dllexport) int sqlite3_extension_init(sqlite3 *db, char **errmsg, const sqlite3_api_routines *api) {
    SQLITE_EXTENSION_INIT2(api);
    return sqlite3_create_function(db, "growl", 1, SQLITE_UTF8, (void*)db, growl_func, NULL, NULL);
}

こんなコード書いて

# gcc -shared -dll -I c:/sqlite3 -I headers growldb.c lib/libgrowl-static.a -lws2_32 -o growldb.dll

こんな風にコンパイル(Windowsの例)。
あとはテーブルにトリガー張って

sqlite> create table foo(comment text);
sqlite> select load_extension('growldb.dll');
sqlite> create trigger tri_foo
   ...> before
   ...>   insert on foo
   ...> begin
   ...>   select growl(new.comment);
   ...> end;

試してみよう！

sqlite> insert into foo values('hasegawa! xss xss');

xssキター！

ただしinsertする側は必ずload_extension('growldb.dll')しとかないといけないので、oracleの様には行かない。
真面目な話、この方法をうまく使えばsqliteでネットワークレプリケーションとか出来そう。
えっ？誰得？.......知りません！

SQLite便利！

SQLite3におけるREGEXP演算子 - anon_193の日記

SQLite では、load_extension 関数を用いて、外部の拡張モジュールをロードすることが出来る。拡張モジュールは、いわばユーザ関数ライブラリで、SQLite3 ODBC Driver には標準で BLOB二次元マッピング拡張(sqlite3_mod_blobtoxy.dll)、外部データ取込・出力拡張(sqlite3_mod_impexp.dll)、全文検索拡張(sqlite3_mod_fts3.dll) が付属している。これらと同様にして、正規表現マッチングを行う regexp ユーザ関数を持つ拡張モジュールを制作し、ロードすれば、お目当ての REGEXP 演算子が使えるわけだ。

http://d.hatena.ne.jp/anon_193/20090114/1231935112

sqlite3_mod_regexp.cxx

#include <boost/regex.hpp>
#include <sqlite3ext.h>
extern "C" {
    SQLITE_EXTENSION_INIT1
    static void regexp_func(sqlite3_context *context, int argc, sqlite3_value **argv) {
        if (argc >= 2) {
            const char *target  = (const char *)sqlite3_value_text(argv[1]);
            const char *pattern = (const char *)sqlite3_value_text(argv[0]);
            try {
                boost::regex ereg(pattern, boost::regex_constants::perl);
                sqlite3_result_int(context, boost::regex_search(target, ereg));
            } catch (boost::regex_error &e) {
                sqlite3_result_error(context, e.what(), 0);
            }
        }
    }
    __declspec(dllexport) int sqlite3_extension_init(sqlite3 *db, char **errmsg, const sqlite3_api_routines *api) {
        SQLITE_EXTENSION_INIT2(api);
        return sqlite3_create_function(db, "regexp", 2, SQLITE_UTF8, (void*)db, regexp_func, NULL, NULL);
    }
}

VCでコンパイルしました。

# cl /EHsc -Isrc -I. -I "c:\boost_1_35_0" sqlite3_mod_regexp.cxx /LD "C:\boost_1_35_0\libs\regex\build\vc80\libboost_regex-vc80-mt-s-1_35.lib"

サンプルデータ
foo.sql

CREATE TABLE foo(id integer primary key, value text);
INSERT INTO "foo" VALUES(1,'abc');
INSERT INTO "foo" VALUES(2,'def');
INSERT INTO "foo" VALUES(3,'あいうえお');
INSERT INTO "foo" VALUES(4,'かきくけこ');
INSERT INTO "foo" VALUES(5,'さしすせそ');

utf-8で保存して下さい

# cat foo.sql | sqlite3 foo.db

そしてPerlのコード

use strict;
use warnings;
use utf8;
use YAML;
use DBIx::Simple;

my $db = DBIx::Simple->connect("dbi:SQLite:dbname=c:/foo.db", "", "")
    or die DBIx::Simple->error;

$db->func(1, "enable_load_extension");

my $result = $db->query("select load_extension('/sqlite3_mod_regexp.dll')")
    or die DBIx::Simple->error;
warn Dump $db->query("select * from foo where value regexp '^[あか]'")->hashes;

dbのパスとdllのパスは指定して下さい
実行すると...

---
id: 3
value: あいうえお
---
id: 4
value: かきくけこ

スゲーーーー便利！

最近TheSchwartが賑わってる様ですね。

TheSchwartzで仕事をあとにまわす - bits and bytes

なければ勝手に作ってくれるのかなと思ってSYNOPSISのコードを実行してみたけどやっぱり自動でできたりはしなそうだったので調べたらCatalyst and TheSchwartz: Reliable JobQueue in a great framework - Voxにschema.sqlを使うといいと書いてありました。このスキーマをmysqlで実行すればTheSchwartzのキューを管理するためのテーブルが出来上がります。

http://labs.gmo.jp/blog/ku/2008/06/theschwartz.html

Twitter / ku: TheSchwartzおもしろかった sqliteで動...

TheSchwartzおもしろかった sqliteで動くようにしたいけどsqliteようのschemeかきかたわかんない

http://twitter.com/ku/statuses/843135311

って事でSQLiteでも動く様に出来ないか調べてみました。
schema.sqlは、MySQL用のスキーマで

CREATE TABLE funcmap (
        funcid         INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
        funcname       VARCHAR(255) NOT NULL,
        UNIQUE(funcname)
);

CREATE TABLE job (
        jobid           BIGINT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
        funcid          INT UNSIGNED NOT NULL,
        arg             MEDIUMBLOB,
        uniqkey         VARCHAR(255) NULL,
        insert_time     INTEGER UNSIGNED,
        run_after       INTEGER UNSIGNED NOT NULL,
        grabbed_until   INTEGER UNSIGNED NOT NULL,
        priority        SMALLINT UNSIGNED,
        coalesce        VARCHAR(255),
        INDEX (funcid, run_after),
        UNIQUE(funcid, uniqkey),
        INDEX (funcid, coalesce)
);

CREATE TABLE note (
        jobid           BIGINT UNSIGNED NOT NULL,
        notekey         VARCHAR(255),
        PRIMARY KEY (jobid, notekey),
        value           MEDIUMBLOB
);

CREATE TABLE error (
        error_time      INTEGER UNSIGNED NOT NULL,
        jobid           BIGINT UNSIGNED NOT NULL,
        message         VARCHAR(255) NOT NULL,
        funcid          INT UNSIGNED NOT NULL DEFAULT 0,
        INDEX (funcid, error_time),
        INDEX (error_time),
        INDEX (jobid)
);

CREATE TABLE exitstatus (
        jobid           BIGINT UNSIGNED PRIMARY KEY NOT NULL,
        funcid          INT UNSIGNED NOT NULL DEFAULT 0,
        status          SMALLINT UNSIGNED,
        completion_time INTEGER UNSIGNED,
        delete_after    INTEGER UNSIGNED,
        INDEX (funcid),
        INDEX (delete_after)
);

というSQLなので、これをSQLite用に書き換えます。

cl.pocari.org - SQLite で auto-increment なフィールドを作成する方法

つまり，SQLite で auto-increment なフィールドを作りたければ，INTEGER PRIMARY KEY を指定してあげればいいらしい．

http://cl.pocari.org/2006-02-12-1.html

こちらの記事でも書かれている通り、SQLiteではINTEGER PRIMARY KEYを指定すればok。
※ちなみにUNSIGNEDとかNOT NULLとか付けると動かないです。

で書き換えたSQLは以下の通り

CREATE TABLE funcmap (
        funcid         INTEGER PRIMARY KEY,
        funcname       VARCHAR(255) NOT NULL,
        UNIQUE(funcname)
);

CREATE TABLE job (
        jobid           INTEGER PRIMARY KEY,
        funcid          INTEGER NOT NULL,
        arg             MEDIUMBLOB,
        uniqkey         VARCHAR(255) NULL,
        insert_time     INTEGER UNSIGNED,
        run_after       INTEGER UNSIGNED NOT NULL,
        grabbed_until   INTEGER UNSIGNED NOT NULL,
        priority        SMALLINT UNSIGNED,
        coalesce        VARCHAR(255),
        UNIQUE(funcid, uniqkey)
);
CREATE INDEX job_idx_1 ON job (funcid, run_after);
CREATE INDEX job_idx_2 ON job (funcid, coalesce);

CREATE TABLE note (
        jobid           INTEGER NOT NULL,
        notekey         VARCHAR(255),
        value           BLOB
);

CREATE TABLE error (
        error_time      INTEGER UNSIGNED NOT NULL,
        jobid           INTEGER NOT NULL,
        message         VARCHAR(255) NOT NULL,
        funcid          INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX error_idx_1 ON error (funcid, error_time);
CREATE INDEX error_idx_2 ON error (error_time);
CREATE INDEX error_idx_3 ON error (jobid);

CREATE TABLE exitstatus (
        jobid           INTEGER PRIMARY KEY,
        funcid          INTEGER NOT NULL DEFAULT 0,
        status          SMALLINT UNSIGNED,
        completion_time INTEGER UNSIGNED,
        delete_after    INTEGER UNSIGNED
);
CREATE INDEX exitstatus_idx_1 ON exitstatus (funcid);
CREATE INDEX exitstatus_idx_2 ON exitstatus (delete_after);

あとはコレを

# sqlite3 the_schwartz < theschwartz_schema.sql

としてDBファイルを作る。
さてサーバのコードはDSNを"dbi:SQLite:the_schwartz"にするだけ。

#!/usr/bin/env perl
package MyWorker;
use strict;
use utf8;
use warnings;
use base qw( TheSchwartz::Worker );
use Data::Dumper;

binmode STDERR, ":encoding(cp932)" if $^O eq "MSWin32";

sub work {
    my ($class, $job) = @_;
    warn "お仕事ですよ！ @{[ Dumper($job->arg) ]}\n";
    $job->completed();
}

package main;
use strict;
use warnings;
use TheSchwartz;

my $client = TheSchwartz->new(
    databases => [ +{ dsn => 'dbi:SQLite:the_schwartz' } ]
);
$client->can_do('MyWorker');
$client->work();

さらにクライアントのコード

#!/usr/bin/env perl
use strict;
use warnings;
use TheSchwartz;

my $client = TheSchwartz->new(
    databases => [ +{ dsn => 'dbi:SQLite:the_schwartz' } ]
);
$client->insert('MyWorker' => +{ hoge => "fuga" });

実行すると

お仕事ですよ！ $VAR1 = {
          'hoge' => 'fuga'
        };

という風にサーバ側で表示されます。 ちなみに、DBD::SQLiteのバグで

closing dbh with active statement handles at .../Data/ObjectDriver/Driver/DBI.pm line 566.

という表示が出る様ならばココにある"issue-17603.tar.gz"のパッチを当てるといいです。どうやらこのバグ、デグレっぽいですね。

ま、結局SQLiteでやっちゃったら同一マシンだし負荷分散にはならない(UIロックを避けるという意味では有用)んですけどね。

ローカルDBやメモリDBとして使えるSQLiteは、開発者にとってかなり有用なツールです。 私はよく「あーーーあのSQLどう書こう」なんて悩む時にSQLiteのshell版を使います。今日はそのWin32 SQLite3のshell版sqlite3.exeの作り方をご紹介。

まず、ダウンロードページから

sqlite-3_5_0.zip

というファイルをダウンロードします。
次に、ソースツリー内の「src」ディレクトリに移動し、以下のMakefileを置きます。

.SUFFIXES: .c .obj

all : sqlite3.exe

sqlite3.exe :  alter.obj analyze.obj attach.obj auth.obj btree.obj build.obj callback.obj complete.obj date.obj delete.obj expr.obj func.obj hash.obj insert.obj legacy.obj loadext.obj main.obj opcodes.obj os.obj os_os2.obj os_unix.obj os_win.obj pager.obj parse.obj pragma.obj prepare.obj printf.obj random.obj select.obj shell.obj table.obj tokenize.obj trigger.obj update.obj utf.obj util.obj vacuum.obj vdbe.obj vdbeblob.obj vdbeapi.obj vdbeaux.obj vdbefifo.obj vdbemem.obj vtab.obj where.obj mutex.obj mem1.obj malloc.obj
    cl /Fesqlite3.exe alter.obj analyze.obj attach.obj auth.obj btree.obj build.obj callback.obj complete.obj date.obj delete.obj expr.obj func.obj hash.obj insert.obj legacy.obj loadext.obj main.obj opcodes.obj os.obj os_os2.obj os_unix.obj os_win.obj pager.obj parse.obj pragma.obj prepare.obj printf.obj random.obj select.obj shell.obj table.obj tokenize.obj trigger.obj update.obj utf.obj util.obj vacuum.obj vdbe.obj vdbeblob.obj vdbeapi.obj vdbeaux.obj vdbefifo.obj vdbemem.obj vtab.obj where.obj mutex.obj mem1.obj malloc.obj

.c.obj :
    cl /c /DSQLITE_THREADSAFE=0 $<

後は、Visual Studioのコンパイラにパスが通ってるならば「nmake」でコンパイルします。 なおSQLiteは、内部処理がUTF-8でやり取りされており、Win32版の場合はNTかどうかでCreateFileA/CreateFileW等、ワイド文字APIの呼び方を変えています。
しかし、コマンドライン引数には対応出来ておらず

C:¥> sqlite3.exe データベース.db

なんて事すると、エラーが発生します。ちなみにNTでない場合には変換無しにCreateFileAを使いますから問題なく動きます。
これって、コマンドラインをUTF-8にすれば動くんだろうけど

C:¥> chcp 65001

やってUTF-8にしても文字は化けるし、IME動かないし意味無いですよね。
適当に

--- os_win.c.orig   Mon Sep 03 22:39:38 2007
+++ os_win.c    Fri Sep 28 22:17:50 2007
@@ -137,6 +137,8 @@
 # define isNT()  (1)
 #else
   static int isNT(void){
+    if( GetACP() != CP_UTF8 )
+      return FALSE;
     if( sqlite3_os_type==0 ){
       OSVERSIONINFO sInfo;
       sInfo.dwOSVersionInfoSize = sizeof(sInfo);

こんなパッチ当てて、実行すると日本語のファイル名も扱える様になります。
このEXEをUSBなんかで常備しておくと、いざという時に助けられるかもしれませんね。