Ruby 学习 - DBI - Day 3 -- DBI::DatabaseHandle

一、本章开始学习 DBI::DatabaseHandle
通过 dbi.connect，我们可以获得一个 DBI::DatabaseHandle，就是我们常用的 dbh：

dbh = DBI.connect('dbi:OCI8:test','user','passwd')

dbh 的主要方法有： commit、rollback； prepare、execute、do；select_one、 select_all；disconnect等。

二、 DBI::DatabaseHandle 解析
参考：

1、  --- 提交事务

源代码：

# File lib/dbi/handles/database.rb, line 167
        def commit
            raise InterfaceError, "Database connection was already closed!" if @handle.nil?
            @handle.commit
        end

在 dbi 初始化时需要关闭 AutoCommit；

示例：

dbh['AutoCommit'] = false
dbh.do("update test set name='xxx' ")
dbh.commit

2、 --- 回滚事务
源代码;

# File lib/dbi/handles/database.rb, line 175
        def rollback
            raise InterfaceError, "Database connection was already closed!" if @handle.nil?
            @handle.rollback
        end

示例：

dbh['AutoCommit'] = false
dbh.do("update test set name='xxx' ")
dbh.rollback

3、 
一般用于执行 update、delete、insert、drop table、truncate table、alter table 等无需返回值的sql，返回值为变动或被修改记录数量。

源代码：

# File lib/dbi/handles/database.rb, line 94
        def do(stmt, *bindvars)
            raise InterfaceError, "Database connection was already closed!" if @handle.nil?
            @handle.do(stmt, *DBI::Utils::ConvParam.conv_param(driver_name, *bindvars))
        end

4、

准备一条 sql ，返回  ，就是前文学习的 DBI::StatementHandle，一般用 sth 表示。如果后面跟一个 block ,则将 sth 作为参数，代码块结束后会调用 finish ，这样代码会非常紧凑。

源代码：

# File lib/dbi/handles/database.rb, line 47
        def prepare(stmt)
            raise InterfaceError, "Database connection was already closed!" if @handle.nil?
            sth = StatementHandle.new(@handle.prepare(stmt), false, true, @convert_types)
            # FIXME trace sth.trace(@trace_mode, @trace_output)
            sth.dbh = self

            if block_given?
                begin
                    yield sth
                ensure
                    sth.finish unless sth.finished?
                end
            else
                return sth
            end
        end

示例：
程序结构看上去赏心悦目，比 perl 漂亮；其实我们可以再简化啦，就是用 execute

dbh = DBI.connect('dbi:OCI8:test','user','passwd')
dbh.prepare('select * from test') do |sth|
    sth.execute
    sth.fetch do |row|
        p row
    end
end

5、execute(stmt, *bindvars) {|sth| ...}
就是整合了 dbh.prepare 和 sth.execute ；同样后面可以跟 block。

源代码：

# File lib/dbi/handles/database.rb, line 67
        def execute(stmt, *bindvars)
            raise InterfaceError, "Database connection was already closed!" if @handle.nil?

            if @convert_types
                bindvars = DBI::Utils::ConvParam.conv_param(driver_name, *bindvars)
            end

            sth = StatementHandle.new(@handle.execute(stmt, *bindvars), true, false, @convert_types)
            # FIXME trace sth.trace(@trace_mode, @trace_output)
            sth.dbh = self

            if block_given?
                begin
                    yield sth
                ensure
                    sth.finish unless sth.finished?
                end
            else
                return sth
            end
        end

示例：我们可以舍弃 execute 了。

dbh = DBI.connect('dbi:OCI8:test','user','passwd')
dbh.prepare('select * from test') do |sth|
    sth.fetch do |row|
        p row
    end
end

如果只是简单的获取结果，我们可以使用 select_one 跟 select_all

6、
相当于整合了 dbh.execute、sth.fetch_all ；如果后面跟了一个 block ，则对每条记录进行遍历。

源代码：

# File lib/dbi/handles/database.rb, line 115
        def select_all(stmt, *bindvars, &p)
            raise InterfaceError, "Database connection was already closed!" if @handle.nil?
            rows = nil
            execute(stmt, *bindvars) do |sth|
                if block_given?
                    sth.each(&p)
                else
                    rows = sth.fetch_all
                end
            end
            return rows
        end

代码中调用 sth.each 执行 block ，结合前文中 DBI::StatementHandle each 的分析，可以看出，传入 block 处理数据，本质上是打开一个循环，逐条 fetch 数据后处理。因此当处理巨量数据时， select_all 最好跟上 block 进行处理，否则会消耗大量内存。

示例：

dbh = DBI.connect('dbi:OCI8:test','user','passwd')
dbh.select_all('select * from test') do |row|
    p row
end

7、  

同 select_all 差不多，区别在于只取一条记录。

源代码

# File lib/dbi/handles/database.rb, line 102
        def select_one(stmt, *bindvars)
            raise InterfaceError, "Database connection was already closed!" if @handle.nil?
            row = nil
            execute(stmt, *bindvars) do |sth|
                row = sth.fetch
            end
            row
        end

8、  

# File lib/dbi/handles/database.rb, line 159
        def quote(value)
            raise InterfaceError, "Database connection was already closed!" if @handle.nil?
            @handle.quote(value)
        end

9、  

# File lib/dbi/handles/database.rb, line 151
        def ping
            raise InterfaceError, "Database connection was already closed!" if @handle.nil?
            @handle.ping
        end

10、  

开启一个事务，执行其后的代码块，成功则提交，失败则回滚。

# File lib/dbi/handles/database.rb, line 185
        def transaction
            raise InterfaceError, "Database connection was already closed!" if @handle.nil?
            raise InterfaceError, "No block given" unless block_given?

            commit
            begin
                yield self
                commit
            rescue Exception
                rollback
                raise
            end
        end

11、  

提供一个表名，返回该表字段信息，返回值是元素为    的数组。
源代码：

# File lib/dbi/handles/database.rb, line 141
        def columns( table )
            raise InterfaceError, "Database connection was already closed!" if @handle.nil?
            @handle.columns( table ).collect {|col| ColumnInfo.new(col) }
        end

12、  

返回该   句柄下的所有表，返回值为字符串数组。
源代码：

# File lib/dbi/handles/database.rb, line 131
        def tables
            raise InterfaceError, "Database connection was already closed!" if @handle.nil?
            @handle.tables
        end

....