[ruby-dev:38463] SQLiteライブラリ
From:
"NARUSE, Yui" <naruse@...>
Date:
2009-05-15 14:12:33 UTC
List:
ruby-dev #38463
成瀬です。
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>
Attachments (1)
sqlite.patch
(14.1 KB, text/x-diff)
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