From: Hirotsugu Asari <asari.ruby@...>
Date: 2009-05-15T23:21:35+09:00
Subject: [ruby-dev:38464] Re: SQLiteライブラリ

浅里と申します。

JRubyを使う身分としてはFFI経由でやってくれると有り難いです。
「標準」という訳には行かなくなっちゃいますけど。

--  
Hirotsugu Asari



On May 15, 2009, at 9:12 AM, NARUSE, Yui wrote:

> 成瀬です。
>
> Ruby 1.9でSQLiteライブラリを標準添付しませんか？
>
> 旧来からsqlite3-rubyという物は存在していましたが、
> http://rubyforge.org/projects/sqlite-ruby
> http://sqlite-ruby.rubyforge.org/sqlite3/
>
> * nativeでなくdl経由
> * 作者がメンテナンス終了を宣言
>  http://weblog.jamisbuck.org/2009/2/25/net-ssh-capistrano-and-saying-goodbye
> という致命的な物を含む問題があります。
>
>
> これに対して今回新しく起こしたライブラリでは、
> * nativeかつコンパクトな実装
> * それなりにやる気のあるメンテナ
> という利点があります。
>
> 使う際には以下のような感じになります。
> 多段transactionやbackupが特徴でしょうか。
>
>  SQLite.open(":memory:") do |db|
>    db.transaction do
>    db.exec("CREATE TABLE foo (int INTEGER, text TEXT)")
>      10.times do |i|
>        db.exec("INSERT INTO foo VALUES (#{i}, 'foo')")
>      end
>
>      p db.exec("SELECT * FROM foo")[0][9999]
>      p db.exec("SELECT * FROM foo")[0].colnames
>
>      db.transaction do
>        10.times do |i|
>          db.exec("INSERT INTO foo VALUES (#{i}, 'foo')")
>        end
>        db.rollback
>      end
>
>      SQLite.open(":memory:") do |db2|
>        db.backup(db2)
>      end
>    end
>  end
>
> ご意見等あればよろしくお願いします。
>
> --  
> NARUSE, Yui  <naruse@airemix.jp>
> diff --git a/ext/sqlite/extconf.rb b/ext/sqlite/extconf.rb
> new file mode 100644
> index 0000000..e042531
> --- /dev/null
> +++ b/ext/sqlite/extconf.rb
> @@ -0,0 +1,5 @@
> +require 'mkmf'
> +
> +dir_config('sqlite')
> +have_library('sqlite3')
> +create_makefile('sqlite')
> diff --git a/ext/sqlite/sqlite.c b/ext/sqlite/sqlite.c
> new file mode 100644
> index 0000000..d4d5644
> --- /dev/null
> +++ b/ext/sqlite/sqlite.c
> @@ -0,0 +1,421 @@
> +/************************************************
> + *
> + * sqlite.c -
> + *
> + ************************************************/
> +
> +#include "ruby/ruby.h"
> +#include "ruby/encoding.h"
> +#include "sqlite3.h"
> +
> +static VALUE rb_cSQLite;
> +
> +struct rb_sqlite {
> +    sqlite3 *db;
> +    VALUE transactions;
> +    int success;
> +};
> +
> +static void
> +rb_sqlite_closed()
> +{
> +    rb_raise(rb_eArgError, "closed SQLite Database");
> +}
> +
> +static struct rb_sqlite *
> +rb_sqlite_struct(VALUE obj)
> +{
> +    struct rb_sqlite *sdb;
> +    Data_Get_Struct(obj, struct rb_sqlite, sdb);
> +    if (sdb == 0) rb_sqlite_closed();
> +    if (sdb->db == 0) rb_sqlite_closed();
> +    return sdb;
> +}
> +
> +static void
> +rb_sqlite_mark(void *p)
> +{
> +    struct rb_sqlite *sdb = p;
> +    if (sdb) {
> +        rb_gc_mark(sdb->transactions);
> +    }
> +}
> +
> +static int
> +rb_sqlite_p(VALUE obj)
> +{
> +    struct rb_sqlite *ptr;
> +    if (TYPE(time) == T_DATA && RDATA(obj)->dmark !=  
> rb_sqlite_mark) {
> +        return Qfalse;
> +    }
> +    return Qtrue;
> +}
> +
> +#define RSQLITE(obj) rb_sqlite_struct(obj)
> +#define RSQLITE_DB(obj) (RSQLITE(obj)->db)
> +#define RSQLITE_P(obj) rb_sqlite_p(obj)
> +#define rb_sqlite_transactions(obj) (RSQLITE(obj)->transactions)
> +
> +static void
> +rb_sqlite_free(struct rb_sqlite *sdb)
> +{
> +    if (sdb->db) sqlite3_close(sdb->db);
> +    xfree(sdb);
> +}
> +
> +static VALUE
> +rb_sqlite_alloc(VALUE klass)
> +{
> +    struct rb_sqlite *sdb;
> +    VALUE obj = Data_Make_Struct(klass, struct rb_sqlite,
> +            rb_sqlite_mark, rb_sqlite_free, sdb);
> +    sdb->db = NULL;
> +    sdb->transactions = rb_ary_new();
> +    sdb->success = Qfalse;
> +    return obj;
> +}
> +
> +static int
> +rb_sqlite_sleep_callback(void *arg, int count)
> +{
> +    if (count > 4) return 0;
> +    rb_thread_sleep(250);
> +    return 1;
> +}
> +
> +static VALUE
> +rb_sqlite_initialize(VALUE obj, VALUE path)
> +{
> +    sqlite3 *db;
> +    struct rb_sqlite *sdb;
> +    int rc;
> +
> +    FilePathValue(path);
> +
> +    rc = sqlite3_open(RSTRING_PTR(path), &db);
> +    if (rc != SQLITE_OK) {
> +        VALUE msg = rb_str_new_cstr(sqlite3_errmsg(db));
> +        sqlite3_close(db);
> +        rb_raise(rb_eArgError, "Can't open database: %s (%s)",
> +                RSTRING_PTR(path), RSTRING_PTR(msg));
> +    }
> +    rc = sqlite3_busy_handler(db, rb_sqlite_sleep_callback, NULL);
> +    Data_Get_Struct(obj, struct rb_sqlite, sdb);
> +    sdb->db = db;
> +    return obj;
> +}
> +
> +/*
> + * call-seq: SQLite#close
> + *
> + * close database.
> + */
> +static VALUE
> +rb_sqlite_close(VALUE obj)
> +{
> +    struct rb_sqlite *sdb = RSQLITE(obj);
> +    sqlite3_close(sdb->db);
> +    sdb->db = 0;
> +
> +    return Qnil;
> +}
> +
> +/*
> + * call-seq: SQLite.open(filename)
> + *
> + * open SQLite database.
> + */
> +static VALUE
> +rb_sqlite_s_open(VALUE klass, VALUE path)
> +{
> +    VALUE obj = rb_sqlite_alloc(klass);
> +
> +    if (NIL_P(rb_sqlite_initialize(obj, path))) {
> +        return Qnil;
> +    }
> +
> +    if (rb_block_given_p()) {
> +        return rb_ensure(rb_yield, obj, rb_sqlite_close, obj);
> +    }
> +
> +    return obj;
> +}
> +
> +static VALUE
> +rb_sqlite_cols_colnames(VALUE self)
> +{
> +    return rb_ivar_get(self, rb_intern("colnames"));
> +}
> +
> +static VALUE
> +rb_sqlite_cols_aref(int argc, VALUE *argv, VALUE self)
> +{
> +    if (argc == 1 && TYPE(argv[0]) == T_STRING) {
> +        VALUE colname = argv[0];
> +        int i;
> +        VALUE colnames = rb_sqlite_cols_colnames(self);
> +        for (i = 0; i < RARRAY_LEN(colnames); i++) {
> +            if (rb_str_equal(colname, rb_ary_entry(colnames, i))) {
> +                return rb_ary_entry(self, i);
> +            }
> +        }
> +    }
> +    else {
> +        return rb_ary_aref(argc, argv, self);
> +    }
> +}
> +
> +static void
> +rb_sqlite_work_init(VALUE work, sqlite3_stmt *stmt)
> +{
> +    VALUE colnames, proto_cols;
> +    int i, count = sqlite3_column_count(stmt);
> +
> +    colnames = rb_ary_new2(count);
> +    for (i = 0; i < count; i++) {
> +        rb_ary_push(colnames,  
> rb_str_new_cstr(sqlite3_column_name(stmt, i)));
> +    }
> +
> +    proto_cols = rb_ary_new2(count);
> +    rb_ivar_set(proto_cols, rb_intern("colnames"), colnames);
> +    rb_define_singleton_method(proto_cols, "[]",  
> rb_sqlite_cols_aref, -1);
> +    rb_define_singleton_method(proto_cols, "colnames",  
> rb_sqlite_cols_colnames, 0);
> +    rb_ary_push(work, proto_cols);
> +}
> +
> +#define rb_sqlite_work_proto_cols(work) rb_ary_entry(work, 0)
> +
> +static VALUE
> +rb_sqlite_work_new(sqlite3_stmt *stmt)
> +{
> +    VALUE work = rb_ary_new2(1); /* [proto_cols] */
> +    rb_sqlite_work_init(work, stmt);
> +    return work;
> +}
> +
> +static VALUE
> +rb_sqlite_work_cols_new(VALUE work, sqlite3_stmt *stmt)
> +{
> +    VALUE proto_cols, cols;
> +    int i, count = sqlite3_column_count(stmt);
> +
> +    proto_cols = rb_sqlite_work_proto_cols(work);
> +    cols = rb_obj_clone(proto_cols);
> +    for (i = 0; i < count; i++) {
> +        VALUE value;
> +        int type = sqlite3_column_type(stmt, i);
> +        switch (type) {
> +          case SQLITE_INTEGER:
> +#ifdef HAVE_LONG_LONG
> +            {
> +                sqlite3_int64 intval = sqlite3_column_int64(stmt, i);
> +                value = LL2NUM((LONG_LONG)intval);
> +            }
> +#else
> +            value = sqlite3_column_int(stmt, i);
> +#endif
> +            break;
> +          case SQLITE_FLOAT:
> +            {
> +                double dblval = sqlite3_column_double(stmt, i);
> +                value = DBL2NUM(dblval);
> +            }
> +            break;
> +          case SQLITE_TEXT:
> +          case SQLITE_BLOB:
> +            {
> +                int len = sqlite3_column_bytes(stmt, i);
> +                const unsigned char *text =  
> sqlite3_column_text(stmt, i);
> +                rb_encoding *enc =
> +                    type == SQLITE_TEXT ? rb_utf8_encoding() :  
> rb_ascii8bit_encoding();
> +                value = rb_str_new_cstr(text);
> +            }
> +            break;
> +          case SQLITE_NULL:
> +            value = Qnil;
> +            break;
> +        }
> +        rb_ary_push(cols, value);
> +    }
> +
> +    return cols;
> +}
> +
> +static VALUE
> +rb_sqlite_exec(VALUE obj, VALUE sql)
> +{
> +    VALUE work = Qnil, result = Qnil;
> +    sqlite3_stmt *stmt;
> +    int i, rc;
> +
> +    rc = sqlite3_prepare_v2(RSQLITE_DB(obj),
> +            RSTRING_PTR(sql), RSTRING_LEN(sql), &stmt, NULL);
> +    if (rc != SQLITE_OK) {
> +        rb_raise(rb_eArgError, "SQL error: %d %s", rc,  
> sqlite3_errmsg(RSQLITE_DB(obj)));
> +    }
> +
> +    for (;;) {
> +        rc = sqlite3_step(stmt);
> +        switch (rc) {
> +          case SQLITE_ROW:
> +            {
> +                VALUE cols;
> +                if (NIL_P(work)) {
> +                    work = rb_sqlite_work_new(stmt);
> +                    if (!rb_block_given_p()) {
> +                        result =  
> rb_ary_new2(sqlite3_data_count(stmt));
> +                    }
> +                }
> +                cols = rb_sqlite_work_cols_new(work, stmt);
> +                if (rb_block_given_p()) {
> +                    rb_yield(cols);
> +                }
> +                else {
> +                    rb_ary_push(result, cols);
> +                }
> +            }
> +            continue;
> +          case SQLITE_DONE:
> +            break;
> +          case SQLITE_BUSY:
> +            /* after process busy handler */
> +          default:
> +            sqlite3_finalize(stmt);
> +            rb_raise(rb_eArgError, "SQL error: %d %s", rc,  
> sqlite3_errmsg(RSQLITE_DB(obj)));
> +        }
> +        break;
> +    }
> +
> +    sqlite3_finalize(stmt);
> +    return result;
> +}
> +
> +static VALUE
> +rb_sqlite_release(VALUE self)
> +{
> +    int rc;
> +    char *errmsg;
> +    VALUE transactions, sql, savepoint;
> +
> +    transactions = rb_sqlite_transactions(self);
> +    savepoint = rb_ary_entry(transactions, -1);
> +    if (NIL_P(savepoint)) return Qnil;
> +
> +    sql = rb_str_append(rb_str_new_cstr("RELEASE  
> RSQLITE_SAVEPOINT_"), savepoint);
> +    rc = sqlite3_exec(RSQLITE_DB(self), RSTRING_PTR(sql), NULL,  
> NULL, &errmsg);
> +    if (rc != SQLITE_OK) {
> +        VALUE msg = rb_str_new_cstr(errmsg);
> +        sqlite3_free(errmsg);
> +        rb_raise(rb_eRuntimeError, "Can't release (%s)",  
> RSTRING_PTR(msg));
> +    }
> +    rb_ary_pop(transactions);
> +    RSQLITE(self)->success = Qfalse;
> +    return Qnil;
> +}
> +
> +static VALUE
> +rb_sqlite_rollback(VALUE self)
> +{
> +    int rc;
> +    char *errmsg;
> +    VALUE transactions, sql, savepoint;
> +
> +    transactions = rb_sqlite_transactions(self);
> +    savepoint = rb_ary_entry(transactions, -1);
> +    if (NIL_P(savepoint)) return Qnil;
> +
> +    sql = rb_str_append(rb_str_new_cstr("ROLLBACK TO  
> RSQLITE_SAVEPOINT_"), savepoint);
> +    rc = sqlite3_exec(RSQLITE_DB(self), RSTRING_PTR(sql), NULL,  
> NULL, &errmsg);
> +    if (rc != SQLITE_OK) {
> +        VALUE msg = rb_str_new_cstr(errmsg);
> +        sqlite3_free(errmsg);
> +        rb_raise(rb_eRuntimeError, "Can't rollback (%s)",  
> RSTRING_PTR(msg));
> +    }
> +    return Qnil;
> +}
> +
> +static VALUE
> +rb_sqlite_transaction_end(VALUE self)
> +{
> +    if (RSQLITE(self)->success == Qfalse) {
> +        rb_sqlite_rollback(self);
> +    }
> +    return rb_sqlite_release(self);
> +}
> +
> +static VALUE
> +rb_sqlite_transaction_i(VALUE self)
> +{
> +    VALUE res = rb_yield(self);
> +    RSQLITE(self)->success = Qtrue;
> +    return res;
> +}
> +
> +static VALUE
> +rb_sqlite_transaction(VALUE self)
> +{
> +    VALUE result, transactions, sql, savepoint;
> +    int rc;
> +    char *errmsg;
> +
> +    RETURN_ENUMERATOR(self, 0, 0);
> +
> +    transactions = rb_sqlite_transactions(self);
> +    savepoint = rb_sprintf("%d", RARRAY_LEN(transactions));
> +
> +    sql = rb_str_append(rb_str_new_cstr("SAVEPOINT  
> RSQLITE_SAVEPOINT_"), savepoint);
> +    rc = sqlite3_exec(RSQLITE_DB(self), RSTRING_PTR(sql), NULL,  
> NULL, &errmsg);
> +
> +    if (rc != SQLITE_OK) {
> +        VALUE msg = rb_str_new_cstr(errmsg);
> +        sqlite3_free(errmsg);
> +        rb_raise(rb_eRuntimeError, "Can't begin transaction (%s)",  
> RSTRING_PTR(msg));
> +    }
> +
> +    rb_ary_push(transactions, savepoint);
> +    RSQLITE(self)->success = Qfalse;
> +
> +    return rb_ensure(rb_sqlite_transaction_i, self,  
> rb_sqlite_transaction_end, self);
> +}
> +
> +/*
> + * call-seq: SQLite#backup(database)
> + *
> + */
> +static VALUE
> +rb_sqlite_backup(VALUE self, VALUE dst)
> +{
> +    sqlite3 *db, *dstdb;
> +    sqlite3_backup *backup;
> +    int rc = 0;
> +
> +    db = RSQLITE_DB(self);
> +    dstdb = RSQLITE_DB(dst);
> +
> +    backup = sqlite3_backup_init(dstdb, "main", db, "main");
> +    if (backup) {
> +        sqlite3_backup_step(backup, -1);
> +        sqlite3_backup_finish(backup);
> +    }
> +    rc = sqlite3_errcode(dstdb);
> +    if (rc != SQLITE_OK) {
> +        rb_raise(rb_eArgError, "Can't backup: %s",  
> sqlite3_errmsg(dstdb));
> +    }
> +
> +    return Qnil;
> +}
> +
> +void
> +Init_sqlite()
> +{
> +    rb_cSQLite = rb_define_class("SQLite", rb_cObject);
> +    rb_define_alloc_func(rb_cSQLite, rb_sqlite_alloc);
> +    rb_define_singleton_method(rb_cSQLite, "open",  
> rb_sqlite_s_open, 1);
> +    rb_define_method(rb_cSQLite, "initialize",  
> rb_sqlite_initialize, 1);
> +    rb_define_method(rb_cSQLite, "close", rb_sqlite_close, 0);
> +    rb_define_method(rb_cSQLite, "exec", rb_sqlite_exec, 1);
> +    rb_define_method(rb_cSQLite, "rollback", rb_sqlite_rollback, 0);
> +    rb_define_method(rb_cSQLite, "transaction",  
> rb_sqlite_transaction, 0);
> +    rb_define_method(rb_cSQLite, "backup", rb_sqlite_backup, 1);
> +    rb_define_const(rb_cSQLite, "VERSION",   
> rb_str_new_cstr(SQLITE_VERSION));
> +}
> diff --git a/test/sqlite/test_sqlite.rb b/test/sqlite/test_sqlite.rb
> new file mode 100644
> index 0000000..06a5973
> --- /dev/null
> +++ b/test/sqlite/test_sqlite.rb
> @@ -0,0 +1,86 @@
> +require 'test/unit'
> +
> +begin
> +  require 'sqlite'
> +rescue LoadError
> +end
> +
> +class TestSQLite < Test::Unit::TestCase
> +  def test_sqlite
> +    assert_kind_of String, SQLite::VERSION
> +    SQLite.open(":memory:") do |db|
> +      assert_nil db.exec("CREATE TABLE foo (int INTEGER, text TEXT)")
> +      10.times do |i|
> +        assert_nil db.exec("INSERT INTO foo VALUES (#{i}, 'foo')")
> +      end
> +
> +      assert_nil db.exec("SELECT * FROM foo")[0][9999]
> +
> +      p db.exec("SELECT * FROM foo")[0].colnames
> +      assert_equal %w/int text/, db.exec("SELECT * FROM foo") 
> [0].colnames
> +
> +      pp rows = db.exec("SELECT * FROM foo")
> +      db.exec("SELECT * FROM foo") do |cols|
> +       assert_kind_of Array, cols
> +      end
> +
> +      db.transaction do
> +        4294967295.upto(4294967297) do |i|
> +          assert_nil db.exec("INSERT INTO foo VALUES (#{i}, 'foo')")
> +        end
> +
> +        i = (1 << 63) - 1
> +        assert_nil db.exec("INSERT INTO foo VALUES (#{i},  
> '63bit-1')")
> +        value = db.exec("SELECT int from foo where text='63bit-1'") 
> [0][0]
> +        assert_kind_of Integer, value
> +        assert_equal i, value
> +
> +        i = (1 << 63)
> +        assert_nil db.exec("INSERT INTO foo VALUES (#{i}, '63bit')")
> +        value = db.exec("SELECT int from foo where text='63bit'")[0] 
> [0]
> +        assert_kind_of Float, value
> +        assert_equal i, value
> +        db.rollback
> +      end
> +      assert_equal rows, db.exec("SELECT * FROM foo")
> +
> +      db.transaction do
> +        assert_nil db.exec("INSERT INTO foo VALUES (11, '11')")
> +      end
> +      assert_not_equal rows, db.exec("SELECT * FROM foo")
> +
> +      rows = db.exec("SELECT * FROM foo")
> +      rows1 = nil
> +      db.transaction do
> +        assert_nil db.exec("INSERT INTO foo VALUES (12, '12')")
> +        rows1 = db.exec("SELECT * FROM foo")
> +        assert_not_equal rows, db.exec("SELECT * FROM foo")
> +        db.transaction do
> +          assert_nil db.exec("INSERT INTO foo VALUES (13, '13')")
> +          assert_not_equal rows1, db.exec("SELECT * FROM foo")
> +          db.rollback
> +          db.rollback
> +        end
> +        assert_equal rows1, db.exec("SELECT * FROM foo")
> +      end
> +      assert_not_equal rows, db.exec("SELECT * FROM foo")
> +      assert_equal rows1, db.exec("SELECT * FROM foo")
> +    end
> +  end
> +
> +  def test_backup
> +    SQLite.open(":memory:") do |db1|
> +      SQLite.open(":memory:") do |db2|
> +        db1.transaction do
> +          assert_nil db1.exec("CREATE TABLE foo (int INTEGER, text  
> TEXT)")
> +          10.times do |i|
> +            assert_nil db1.exec("INSERT INTO foo VALUES (#{i},  
> 'foo')")
> +          end
> +        end
> +        assert_nil db1.backup(db2)
> +        rows = db1.exec("SELECT * FROM foo")
> +        assert_equal rows, db2.exec("SELECT * FROM foo")
> +      end
> +    end
> +  end
> +end