RDBI 学习 -- sqlite3

查阅资料 DBI 现在已停止支持，被 RDBI 取代了，又要学新东西了。

直接看测试程序，可以学到很多东西，而且涵盖了所有方法，有详细用法，入门捷径啊。

以下内容都来自 /test/*.rb

 

小技巧：

require 'rubygems'

# 将文件路径添加进搜索路径中，也可用用 $:<<’/your/lib/path’

$LOAD_PATH.unshift(File.dirname(__FILE__))

$LOAD_PATH.unshift(File.join(File.dirname(__FILE__), '..', 'lib'))

 

O、包含必要的 gem

require 'rdbi'

require 'rdbi/driver/sqlite3' 

一、连接、断开数据库，这里用的是内存数据库

dbh = RDBI.connect(:SQLite3, :database => ":memory:")

dbh.disconnect if(dbh and dbh.connected?) 

二、ping# 查看数据库名字

dbh.database_name # “:memory:”

dbh.ping # 返回为 0，表示连接成功 

三、execute  

1、执行单条 DDL 命令

dbh.execute(‘create table foo(bar integer)’) 

2、执行单条 DML 命令 # 不开启 transaction 的话，会自动提交

res = dbh.execute("insert into foo (bar) values (?)", 1) 

3、执行单条 DQL 命令

# res 是个 RDBH::Result 对象

res = dbh.execute("select * from foo") 

res.fetch(:all) # 返回结果是一个二维数组： [[1]] 

rows = res.as(:Struct).fetch(:all) # 返回结果是个一维数组，每个元素是个 hash

row = rows[0]row.bar # 结果为 1 

# 绑定参数进行查询

res = dbh.execute("select * from foo where bar = ?bar", { :bar => 1 }) 

四、prepare

1、 执行 DML 命令#sth 是 RDBI::Statement 对象

sth = dbh.prepare("insert into foo (bar) values (?)")

5.times { sth.execute(1) } 

2、assert_equal(dbh.last_statement.object_id, sth.object_id) 

3、执行 DQL 命令 

sth2 = dbh.prepare("select * from foo")

res = sth2.execute # 返回 RDBI::Result 对象res.fetch(:all) 

4、关闭 sthsth.finishsth2.finish 五、transaction 1、rollback

dbh.transaction do
      assert(dbh.in_transaction?)
      5.times { dbh.execute("insert into foo (bar) values (?)", 1) }
      dbh.rollback
      assert(!dbh.in_transaction?)
    end

 

2、commit

dbh.transaction do
      assert(dbh.in_transaction?)
      5.times { dbh.execute("insert into foo (bar) values (?)", 1) }
      assert_equal([[1]] * 5, dbh.execute("select * from foo").fetch(:all))
      dbh.commit
    end

 

3、异常处理

dbh.transaction do
  assert_raises(RDBI::TransactionError) do
    dbh.transaction
  end
end

assert_raises(RDBI::TransactionError) do
  dbh.rollback
end

assert_raises(RDBI::TransactionError) do
  dbh.commit
end

 

六、预处理查询： preprocess_query

 

#以下两者等价

dbh.preprocess_query("insert into foo (bar) values (?)", 1)

dbh.preprocess_query("insert into foo (bar) values (?bar)", { :bar => 1 })

 七、schema

dbh.execute("create table bar (foo varchar, bar integer)")
dbh.execute("insert into bar (foo, bar) values (?, ?)", "foo", 1)

 

res = dbh.execute("select * from bar")

assert_kind_of(RDBI::Schema, res.schema)

 

res.schema.columns.each { |x| assert_kind_of(RDBI::Column, x) }

 

八、datetime

 

dt = DateTime.now
dbh.execute('insert into time_test (my_date) values (?)', dt)

 

dt2 = dbh.execute('select * from time_test limit 1').fetch(1)[0][0]

assert_kind_of(DateTime, dt2)

assert_equal(dt2.to_s, dt.to_s)

 

九、basic_schema

 

def test_09_basic_schema
  self.dbh = init_database
  assert_respond_to(dbh, :schema)
  schema = dbh.schema

  tables = [:foo, :time_test, :multi_fields]
  columns = {
    :foo          => { :bar => :integer },
    :time_test    => { :my_date => :timestamp },
    :multi_fields => { :foo => :integer, :bar => :varchar }
  }

  schema.each do |sch|
    assert_kind_of(RDBI::Schema, sch)
    assert(tables.include?(sch.tables[0]))
    assert_equal(:table, sch.type)

    sch.columns.each do |col|
      assert_kind_of(RDBI::Column, col)
      assert_equal(columns[sch.tables[0]][col.name], col.type)
    end
  end
end

十、

def test_10_table_schema
  self.dbh = init_database
  assert_respond_to(dbh, :table_schema)

  schema = dbh.table_schema( :foo )
  columns = schema.columns
  assert_equal columns.size, 1
  c = columns[ 0 ]
  assert_equal c.name, :bar
  assert_equal c.type, :integer

  schema = dbh.table_schema(:multi_fields)
  columns = schema.columns
  assert_equal columns.size, 2
  columns.each do |col|
    case col.name
    when :foo
      assert_equal :integer, col.type
    when :bar
      assert_equal :varchar, col.type
    end
  end

 

十一、关闭数据库连接

self.dbh = init_database
sth = dbh.prepare("select 1")
sth.finish
dbh.disconnect

 

十二、再次连接

dbh.reconnect
dbh.disconnect
dbh.reconnect

 

十三、aggregates

聚集函数测试

self.dbh = init_database

res = dbh.execute("select count(*) from multi_fields")
assert_equal([0], res.fetch(:first))

sth = dbh.prepare("insert into multi_fields (foo, bar) values (?, ?)")
sth.execute(1, "foo")
sth.execute(2, "bar")
sth.finish

res = dbh.execute("select count(*) from multi_fields")
assert_equal([2], res.fetch(:first))

 

十四、

assert_equal(%q[1], dbh.quote(1))
assert_equal(%q['f'], dbh.quote(false))
assert_equal(%q['t'], dbh.quote(true))
assert_equal(%q[NULL], dbh.quote(nil))
assert_equal(%q['shit'], dbh.quote('shit'))
assert_equal(%q['shit''t'], dbh.quote('shit\'t'))