(21)Ruby连接DBI

Ruby DBI 模块为 Ruby 脚本提供了类似于 Perl DBI 模块的独立于数据库的接口

DBI 即 Database independent interface，代表了 Ruby 独立于数据库的接口。 DBI 在 Ruby 代码与底层数据库之间提供了一个抽象层，允许您简单地实现数据库切换。 它定义了一系列方法、变量和规范，提供了一个独立于数据库的一致的数据库接口。

DBI 可与下列进行交互：

ADO (ActiveX Data Objects)
DB2
Frontbase
mSQL
MySQL
ODBC
Oracle
OCI8 (Oracle)
PostgreSQL
Proxy/Server
SQLite
SQLRelay

DBI 应用架构

DBI 独立于任何在后台中可用的数据库。无论您使用的是 Oracle、MySQL、Informix，您都可以使用 DBI。

下面的架构图清晰地说明了这点

Ruby DBI 一般的架构使用两个层：

• 数据库接口（DBI）层。该层是独立于数据库，并提供了一系列公共访问方法，方法的使用不分数据库服务器类型。

• 数据库驱动（DBD）层。该层是依赖于数据库，不同的驱动提供了对不同的数据库引擎的访问。

MySQL、PostgreSQL、InterBase、Oracle 等分别使用不同的驱动。每个驱动都负责解释来自 DBI 层的请求， 并把这些请求映射为适用于给定类型的数据库服务器的请求。

安装 ==》安装 Mysql 开发包

Ubuntu

sudo apt-get install mysql-client
sudo apt-get install libmysqlclient15-dev

Centos

yum install mysql-devel

Mac OS

系统需要修改 ~/.bash_profile 或 ~/.profile 文件，添加如下代码:

MYSQL=/usr/local/mysql/bin
export PATH=$PATH:$MYSQL
export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH

或者使用软连接：

sudo ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib
使用 RubyGems 安装 DBI（推荐）

RubyGems大约创建于2003年11月，从Ruby 1.9版起成为Ruby标准库的一部分。
更多详情可以查看：Ruby RubyGems

使用 gem 安装 dbi 与 dbd-mysql：
sudo gem install dbi ； sudo gem install mysql ；sudo gem install dbd-mysql

1、Ruby 连接 Mysql——DBI

假设我们使用的是 MySQL 数据库，在连接数据库之前，请确保：

1. 您已经创建了一个数据库 TESTDB。
2. 您已经在 TESTDB 中创建了表 EMPLOYEE。
3. 该表带有字段 FIRST_NAME、LAST_NAME、AGE、SEX 和 INCOME。
4. 设置用户 ID "testuser" 和密码 "test123" 来访问 TESTDB
5. 已经在您的机器上正确地安装了 Ruby 模块 DBI。

您已经看过 MySQL 教程，理解了 MySQL 基础操作。 下面是连接 MySQL 数据库 "TESTDB" 的实例：

#!/usr/bin/ruby -w
require "dbi"
begin
    # 连接到 MySQL 服务器
    dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
    # 获取服务器版本字符串，并显示
    row = dbh.select_one("SELECT VERSION()")
    puts "Server version: " + row[0]
rescue DBI::DatabaseError => e
    puts "An error occurred"
    puts "Error code:    #{e.err}"
    puts "Error message: #{e.errstr}"
ensure
    # 断开与服务器的连接
    dbh.disconnect if dbh
end

2、Ruby 连接 Mysql - MySql2

使用Ruby 连接 Mysql 更高效的驱动 mysql2，目前也推荐使用这种方式连接 MySql。

安装 mysql2 驱动：gem install mysql2

你需要使用 –with-mysql-config 配置 mysql_config 的路径， 如： –with-mysql-config=/some/random/path/bin/mysql_config。

连接数据库语法如下：

client = Mysql2::Client.new(:host => "localhost", :username => "root")

更多参数可以查看 http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/MysqlAdapter.html。

查询数据库语法如下：

results = client.query("SELECT * FROM users WHERE group='githubbers'")

特殊字符转义

escaped = client.escape("gi'thu"bbe\0r's") results = client.query("SELECT * FROM users WHERE group='#{escaped}'")

计算结果集返回的数量: results.count

INSERT 操作

当您想要在数据库表中创建记录时，需要用到 INSERT 操作。一旦建立了数据库连接: 我们就可以准备使用 do 方法或 prepare 和 execute 方法创建表或创建插入数据表中的记录。

1）使用 DBI 的 do 语句 ==> 该方法带有一个语句字符串参数，并返回该语句所影响的行数

dbh.do("DROP TABLE IF EXISTS EMPLOYEE")
dbh.do("CREATE TABLE EMPLOYEE (
    FIRST_NAME  CHAR(20) NOT NULL,
    LAST_NAME  CHAR(20),
    AGE INT,  
    SEX CHAR(1),
    INCOME FLOAT )" );

2）使用 DBI 的 prepare 和 execute 方法来执行 Ruby 代码中的 SQL 语句

创建记录的步骤如下：

• 使用 prepare 方法来完成 ==> 执行带有 INSERT 语句的 SQL 语句
• 使用 execute 方法来完成 ==> 执行 SQL 查询

如果一切进展顺利，则 commit 该操作，否则您可以 rollback 完成交易。 下面是使用这两种方法的语法：

实例

sth = dbh.prepare(statement)
sth.execute
  ... zero or more SQL operations ...
sth.finish

这两种方法可用于传 bind 值给 SQL 语句。 有时候被输入的值可能未事先给出，在这种情况下，则会用到绑定值。 *使用问号（?）替代实际值，实际值通过 execute() API 来传递*。

READ 操作

获取记录的步骤如下：

1. 基于所需的条件准备 SQL 查询。这将通过使用 prepare 方法来完成。
2. 执行 SQL 查询，从数据库中选择所有的结果。这将通过使用 execute 方法来完成。
3. 逐一获取结果，并输出这些结果。这将通过使用 fetch 方法来完成。
4. 释放语句句柄。这将通过使用 finish 方法来完成。

Update 操作

1. 基于所需的条件准备 SQL 查询。这将通过使用 prepare 方法来完成。
2. 执行 SQL 查询，从数据库中选择所有的结果。这将通过使用 execute 方法来完成。
3. 释放语句句柄。这将通过使用 finish 方法来完成
4. 如果一切进展顺利，则 commit 该操作，否则您可以 rollback 完成交易。

DELETE 操作

当您想要从数据库中删除记录时，需要用到 DELETE 操作。下 面的实例从 EMPLOYEE 中删除 AGE 超过 20 的所有记录。该操作的步骤如下：

1. 于所需的条件准备 SQL 查询。这将通过使用 prepare 方法来完成。
2. 执行 SQL 查询，从数据库中删除所需的记录。这将通过使用 execute 方法来完成。
3. 释放语句句柄。这将通过使用 finish 方法来完成。
4. 如果一切进展顺利，则 commit 该操作，否则您可以 rollback 完成交易。

执行事务

事务是一种确保交易一致性的机制。事务应具有下列四种属性：

• 原子性（Atomicity）：事务的原子性指的是，事务中包含的程序作为数据库的逻辑工作单位， 它所做的对数据修改操作要么全部执行，要么完全不执行。

• 一致性（Consistency）：事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。 假如数据库的状态满足所有的完整性约束，就说该数据库是一致的。

• 隔离性（Isolation）：事务的隔离性指并发的事务是相互隔离的，即一个事务内部的操作及正在 操作的数据必须封锁起来，不被其它企图进行修改的事务看到。

• 持久性（Durability）：事务的持久性意味着当系统或介质发生故障时，确保已提交事务的更新不能丢失。 即一旦一个事务提交，它对数据库中数据的改变应该是永久性的，耐得住任何数据库系统故障。 持久性通过数据库备份和恢复来保证。

DBI 提供了两种执行事务的方法。一种是 commit 或 rollback 方法，用于提交或回滚事务。 还有一种是 transaction 方法，可用于实现事务。接下来我们来介绍这两种简单的实现事务的方法：

方法 I

一种方法使用 DBI 的 commit 和 rollback 方法来显式地提交或取消事务：

方法 II

二种方法使用 transaction 方法。这个方法相对简单些，因为它需要一个包含构成事务语句的代码块。

transaction 方法执行块，然后根据块是否执行成功，自动调用 commit 或 rollback：

COMMIT 操作

Commit 是一种标识数据库已完成更改的操作，在这个操作后，所有的更改都不可恢复。

下面是一个调用 commit 方法的简单实例。

dbh.commit

ROLLBACK 操作

如果您不满意某个或某几个更改，您想要完全恢复这些更改，则使用 rollback 方法。

调用 rollback 方法的简单实例：dbh:rollback 调用 disconnect 方法简单实例: dbh.disconnect

处理错误

有许多不同的错误来源。比如在执行 SQL 语句时的语法错误，或者是连接失败，又或者是对一个已经取消的或完成的语句句柄调用 fetch 方法。

如果某个 DBI 方法失败，DBI 会抛出异常。DBI 方法会抛出任何类型的异常，但是最重要的两种异常类是 DBI::InterfaceError 和 DBI::DatabaseError。

这些类的 Exception 对象有 err、errstr 和 state 三种属性，分表代表了错误号、一个描述性的错误字符串和一个标准的错误代码。属性具体说明如下：

err：返回所发生的错误的整数表示法，如果 DBD 不支持则返回 nil。例如，Oracle DBD 返回 ORA-XXXX 错误消息的数字部分。 errstr：返回所发生的错误的字符串表示法。 state：返回所发生的错误的 SQLSTATE 代码。SQLSTATE 是五字符长度的字符串。大多数的 DBD 并不支持它，所以会返回 nil。

方法的代码块

一些创建句柄的方法。这些方法通过代码块调用。使用带有方法的代码块的优点是， 它们为代码块提供了句柄作为参数，当块终止时会自动清除句柄。下面是一些实例，有助于理解这个概念。

**DBI.connect **：该方法生成一个数据库句柄，建议在块的末尾调用 disconnect 来断开数据库。

**dbh.prepare **：该方法生成一个语句句柄，建议在块的末尾调用 finish。在块内，您必须调用 execute 方法来执行语句。

**dbh.execute **：该方法与 dbh.prepare 类似，但是 dbh.execute 不需要在块内调用 execute 方法。语句句柄会自动执行。

特定驱动程序的函数和属性

BI 让数据库驱动程序提供了额外的特定数据库的函数，这些函数可被用户通过任何 Handle 对象的 func 方法进行调用。 使用 []= or [] 方法可以设置或获取特定驱动程序的属性。

**DBD::Mysql **实现了下列特定驱动程序的函数：

1 dbh.func(:createdb, db_name)

创建一个新的数据库。

2 dbh.func(:dropdb, db_name)

删除一个数据库。

3 dbh.func(:reload)

执行重新加载操作。

4 dbh.func(:shutdown)

关闭服务器。

5 dbh.func(:insert_id) => Fixnum

返回该连接的最近 AUTO_INCREMENT 值。

6 dbh.func(:client_info) => String

根据版本返回 MySQL 客户端信息。

7 dbh.func(:client_version) => Fixnum

根据版本返回客户端信息。这与 :client_info 类似，但是它会返回一个 fixnum，而不是返回字符串

8 dbh.func(:host_info) => String

返回主机信息。

9 dbh.func(:proto_info) => Fixnum

返回用于通信的协议。

10 dbh.func(:server_info) => String

根据版本返回 MySQL 服务器端信息。

11 dbh.func(:stat) => Stringb>

返回数据库的当前状态。

12 dbh.func(:thread_id) => Fixnum

返回当前线程的 ID。