ActiveRecord-JDBC で sqlite3 のアクセス

以前、jruby で Activerecord-jdbc で sqlite3 アクセスしようとした際は、
sqlite3 アダプターが無かったので、実現できずにいた。
たまたま、今日 sqlite3 アダプタができていることを知った。
- http://weblogs.java.net/blog/arungupta/archive/2008/07/totd_37_sqlite3.html
> Arun Gupta's Blog: TOTD #37: SQLite3 with Ruby-on-Rails on GlassFish Gem

そこで、ruby + activerecord + sqlite3 のプログラムを探し、次のサイトをみつけて、これを jruby + acriverecord0jdbc + sqlite3 に書き換えてみた。

まずは ruby 版。
$ cat ruby-01.rb
# See http://d.hatena.ne.jp/urekat/20080923/1222169086

require "pp"
require "rubygems"
require "active_record"

commands = <<END
ruby -v
which sqlite3
sqlite3 --version
gem list | grep sqlite3-ruby
gem list | grep activerecord
END
commands.each do |cmd|
  puts "'#{ cmd.strip}' => '#{`#{ cmd}`.strip}'"
end

ActiveRecord::Base.logger = Logger.new($stderr)
ActiveRecord::Base.establish_connection(
  :adapter  => "sqlite3",
  :database => "_sqlite3_ar_test.sqlite3",
  :timeout  => 5000
)

begin
  ActiveRecord::Migration.create_table :users do |t|
    t.column :name       , :string
    t.column :nickname   , :string
    t.column :profile    , :text
    t.column :created_at , :datetime
    t.column :updated_at , :datetime
  end
rescue
end

class User < ActiveRecord::Base
end

User.create(
  :name     => "sasaki takeru",
  :nickname => "urekat",
  :profile  => "hehe hoho."
)

puts "User.count=#{User.count}"
pp User.find(:all)

次が jruby 版。
$ cat jruby-01.rb
# See http://d.hatena.ne.jp/urekat/20080923/1222169086

require "pp"
require "rubygems"
require "active_record"

commands = <<END
jruby -v
which sqlite3
sqlite3 --version
jruby -S gem list | grep activerecord-jdbcsqlite3-adapter
jruby -S gem list | grep ActiveRecord-JDBC
END
commands.each do |cmd|
  puts "'#{ cmd.strip}' => '#{`#{ cmd}`.strip}'"
end

ActiveRecord::Base.logger = Logger.new($stderr)
ActiveRecord::Base.establish_connection(
  :adapter  => "jdbcsqlite3",
  :database => "_sqlite3_ar_test.sqlite3",
  :timeout  => 5000
)

begin
  ActiveRecord::Migration.create_table :users do |t|
    t.column :name       , :string
    t.column :nickname   , :string
    t.column :profile    , :text
    t.column :created_at , :datetime
    t.column :updated_at , :datetime
  end
rescue
end

class User < ActiveRecord::Base
end

User.create(
  :name     => "sasaki takeru",
  :nickname => "urekat",
  :profile  => "hehe hoho."
)

puts "User.count=#{User.count}"
pp User.find(:all)

では、走らせてみよう。

$ ruby ruby-01.rb
ruby -v' => 'ruby 1.8.7 (2008-08-11 patchlevel 72) [i686-darwin9]'
'which sqlite3' => '/opt/local/bin/sqlite3'
'sqlite3 --version' => '3.6.4'
'gem list | grep sqlite3-ruby' => 'sqlite3-ruby (1.2.4)'
'gem list | grep activerecord' => 'activerecord (2.1.2, 2.1.0)
activerecord-jdbc-adapter (0.8.2)
activerecord-jdbcsqlite3-adapter (0.8.2)'
-- create_table(:users)
  SQL (0.000417)   select sqlite_version(*)
  SQL (0.003166)   CREATE TABLE "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(255), "nickname" varchar(255), "profile" text, "created_at" datetime, "updated_at" datetime)
   -> 0.0382s
  User Create (0.000448)   INSERT INTO "users" ("name", "created_at", "profile", "nickname", "updated_at") VALUES('sasaki takeru', '2008-11-08 13:30:46', 'hehe hoho.', 'urekat', '2008-11-08 13:30:46')
  SQL (0.000230)   SELECT count(*) AS count_all FROM "users"
User.count=1
  User Load (0.000269)   SELECT * FROM "users"
[#<User id: 1, name: "sasaki takeru", nickname: "urekat", profile: "hehe hoho.", created_at: "2008-11-08 13:30:46", updated_at: "2008-11-08 13:30:46">]

続けて、jruby 版。
$ jruby jruby-01.rb
jruby -v' => 'jruby 1.1.5 (ruby 1.8.6 patchlevel 114) (2008-11-08 rev 6586) [i386-java]'
'which sqlite3' => '/opt/local/bin/sqlite3'
'sqlite3 --version' => '3.6.4'
'jruby -S gem list | grep activerecord-jdbcsqlite3-adapter' => 'activerecord-jdbcsqlite3-adapter (0.8.2)'
'jruby -S gem list | grep ActiveRecord-JDBC' => 'ActiveRecord-JDBC (0.5)'
-- create_table(:users)
  SQL (0.000000)   ActiveRecord::ActiveRecordError: table users already exists: CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name text(0), nickname text(0), profile text, created_at INTEGER, updated_at INTEGER)
  User Create (0.005822)   INSERT INTO users (name, nickname, profile, created_at, updated_at) VALUES('sasaki takeru', 'urekat', 'hehe hoho.', '2008-11-08 13:31:27', '2008-11-08 13:31:27')
  SQL (0.003723)   SELECT SEQ FROM SQLITE_SEQUENCE WHERE NAME = 'users'
  SQL (0.003101)   SELECT count(*) AS count_all FROM users
User.count=2
  User Load (0.006008)   SELECT * FROM users
[#<User id: 1, name: "sasaki takeru", nickname: "urekat", profile: "hehe hoho.", created_at: "2008-11-08 13:30:46", updated_at: "2008-11-08 13:30:46">,
#<User id: 2, name: "sasaki takeru", nickname: "urekat", profile: "hehe hoho.", created_at: "2008-11-08 13:31:27", updated_at: "2008-11-08 13:31:27">]

ちゃんと データ総数が増えている。