极客教程这是针对 PostgreSQL 数据库的 Ruby 编程教程。 它涵盖了使用 Ruby 语言进行 PostgreSQL 编程的基础。
PostgreSQL
PostgreSQL 是一个功能强大的开源对象关系数据库系统。 它是一个多用户数据库管理系统。 它可以在多个平台上运行，包括 Linux，FreeBSD，Solaris，Microsoft Windows 和 Mac OSX。PostgreSQL 由 PostgreSQL 全球开发小组开发。
PostgreSQL 具有复杂的功能，例如多版本并发控制（MVCC），时间点恢复，表空间，异步复制，嵌套事务（保存点），联机/热备份，复杂的查询计划程序/优化程序以及用于容错的预写日志记录 。 它支持国际字符集，多字节字符编码，Unicode，并且对于排序，区分大小写和格式具有区域设置意识。
Ruby
Ruby 是一种动态的，反射性的，通用的面向对象的编程语言。 原始作者是日本程序员松本行弘。 Ruby 于 1995 年首次出现。Ruby 支持各种编程范例。 这包括面向对象，反射，命令式和反射式编程。
Ruby pg
Ruby pg 是一个模块，允许 Ruby 程序与 PostgreSQL 数据库引擎进行交互。 它支持libpq C 库中定义的功能。
安装
我们将安装 PostgreSQL 数据库和其他必要的库。
$ sudo apt-get install postgresql
在基于 Debian 的系统上，我们可以使用上述命令从软件包中安装 PostgreSQL 数据库。
$ sudo update-rc.d -f postgresql remove
Removing any system startup links for /etc/init.d/postgresql ...
/etc/rc0.d/K21postgresql
/etc/rc1.d/K21postgresql
/etc/rc2.d/S19postgresql
/etc/rc3.d/S19postgresql
/etc/rc4.d/S19postgresql
/etc/rc5.d/S19postgresql
/etc/rc6.d/K21postgresql
如果我们从软件包中安装 PostgreSQL 数据库，它将自动添加到操作系统的启动脚本中。 如果我们仅学习使用数据库，则不必在每次引导系统时都启动数据库。 上面的命令删除 PostgreSQL 数据库的所有系统启动链接。
$ sudo apt-get install libpq-dev
要编译 Ruby pg模块，我们还需要 C libpq库的开发文件。
$ sudo -u postgres psql postgres
psql (9.3.9)
Type "help" for help.
postgres=# \password postgres
我们为postgres用户设置了密码。
$ sudo apt-get install ruby-dev
我们安装了 Ruby 开发库，这是编译 Ruby 扩展模块所必需的。
$ sudo gem install pg
我们安装了 Ruby pg 模块，它是 PostgreSQL 数据库的 Ruby 接口。
启动和停止 PostgreSQL
在下一节中，我们将展示如何启动 PostgreSQL 数据库，停止它以及查询它的状态。
$ sudo service postgresql start
* Starting PostgreSQL 9.3 database server [ OK ]
在基于 Debian 的 Linux 上，我们可以使用service postgresql start命令启动服务器。
$ sudo service postgresql status
9.3/main (port 5432): online
我们使用service postgresql status命令检查 PostgreSQL 是否正在运行。
$ sudo service postgresql stop
* Stopping PostgreSQL 9.3 database server [ OK ]
我们使用service postgresql stop命令停止 PostgreSQL。
$ service postgresql status
9.3/main (port 5432): down
此时，service postgresql status命令报告 PostgreSQL 数据库已关闭。
创建用户和数据库
在以下步骤中，我们将创建一个新的数据库用户和数据库。
$ sudo -u postgres createuser janbodnar
我们在 PostgreSQL 系统中创建了一个新角色。 我们允许它具有创建新数据库的能力。 角色是数据库世界中的用户。 角色与操作系统用户是分开的。
$ sudo -u postgres psql postgres
psql (9.3.9)
Type "help" for help.
postgres=# ALTER USER janbodnar WITH password 'pswd37';
ALTER ROLE
postgres=# \q
使用psql命令，为新用户添加密码。
PostgreSQL 通常在本地连接上使用 信任 或 对等 身份验证策略。 在 信任_ 身份验证策略的情况下，PostgreSQL 假定可以连接到服务器的任何人都可以使用他们指定的任何数据库用户名（甚至超级用户名）访问数据库。 与数据库建立连接时，不需要密码。 （在数据库和用户列中所做的限制仍然适用。）信任身份验证对于单用户工作站上的本地连接是适当的，并且非常方便。 它通常不适用于多用户计算机。 如果使用 对等 _ 身份验证策略，则数据库用户名必须与操作系统用户名匹配。
$ sudo -u postgres createdb testdb --owner janbodnar
使用createdb命令，我们创建了一个名为testdb的新数据库。 它的所有者是新的数据库用户。
libpq 库
libpq库是 PostgreSQL 的 C 接口。 它是一组库函数，允许客户端程序与 PostgreSQL 交互。 它也是其他 PostgreSQL 应用接口的基础引擎，包括为 C++ ，Perl，PHP，Ruby，Python 和 Tcl 编写的接口。
Ruby pg模块是libpg库的包装。
lib_version.rb
#!/usr/bin/ruby
require 'pg'
puts 'Version of libpg: ' + PG.library_version.to_s
该程序将打印libpq库的版本。
require 'pg'
我们包括pg模块。
puts 'Version of libpg: ' + PG.library_version.to_s
library_version方法返回正在使用的libpq的版本。
$ ./lib_version.rb
Version of libpg: 90309
该库的版本是 9.3.9。
服务器版本
在下面的示例中，我们找到了 PostgreSQL 数据库的版本。
server_version.rb
#!/usr/bin/ruby
require 'pg'
begin
con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
puts con.server_version
rescue PG::Error => e
puts e.message
ensure
con.close if con
end
该示例连接到 PostgreSQL 数据库，执行server_version方法，打印版本，关闭连接，然后清除。
...
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
...
在pg_hba.conf中，我们具有peer默认身份验证方法。 在这种方法中，数据库用户名必须与操作系统用户名匹配。 无需密码即可建立连接。
con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
使用connect方法，我们连接到数据库。 在连接字符串中，我们提供用户名和数据库名。
rescue PG::Error => e
puts e.message
我们检查错误。 这很重要，因为使用数据库容易出错。
ensure
con.close if con
end
最后，我们释放资源。
$ ./server_version.rb
90309
运行程序，我们获得数据库服务器版本。
密码认证
接下来，我们将使用密码对数据库服务器进行身份验证。 在本教程的所有其他示例中，我们假设使用peer或trust身份验证模式。 我们将pg_hba.conf文件中本地连接的身份验证类型更改为md5。
$ sudo service postgresql restart
要应用更改，必须重新启动数据库服务器。
password_authentication.rb
#!/usr/bin/ruby
require 'pg'
begin
con = PG.connect :dbname => 'testdb', :user => 'janbodnar',
:password => 'pswd37'
user = con.user
db_name = con.db
pswd = con.pass
puts "User: #{user}"
puts "Database name: #{db_name}"
puts "Password: #{pswd}"
rescue PG::Error => e
puts e.message
ensure
con.close if con
end
在示例中，我们使用密码连接到数据库。 我们打印用户名，数据库名称和当前数据库连接的密码。
con = PG.connect :dbname => 'testdb', :user => 'janbodnar',
:password => 'pswd37'
在连接字符串中，我们添加了密码选项。
user = con.user
user方法返回连接的用户名。
db_name = con.db
db方法返回连接的数据库名称。
pswd = con.pass
pass方法返回连接的密码。
$ ./password_authentication.rb
User: janbodnar
Database name: testdb
Password: pswd37
该程序将打印数据库用户，数据库名称和使用的密码。
创建数据库表
在本节中，我们将创建一个数据库表并用数据填充它。
create_table.rb
#!/usr/bin/ruby
require 'pg'
begin
con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
con.exec "DROP TABLE IF EXISTS Cars"
con.exec "CREATE TABLE Cars(Id INTEGER PRIMARY KEY,
Name VARCHAR(20), Price INT)"
con.exec "INSERT INTO Cars VALUES(1,'Audi',52642)"
con.exec "INSERT INTO Cars VALUES(2,'Mercedes',57127)"
con.exec "INSERT INTO Cars VALUES(3,'Skoda',9000)"
con.exec "INSERT INTO Cars VALUES(4,'Volvo',29000)"
con.exec "INSERT INTO Cars VALUES(5,'Bentley',350000)"
con.exec "INSERT INTO Cars VALUES(6,'Citroen',21000)"
con.exec "INSERT INTO Cars VALUES(7,'Hummer',41400)"
con.exec "INSERT INTO Cars VALUES(8,'Volkswagen',21600)"
rescue PG::Error => e
puts e.message
ensure
con.close if con
end
创建的表称为Cars，它具有三列：Id，汽车名称及其价格。
con.exec "DROP TABLE IF EXISTS Cars"
exec方法将 SQL 命令提交到服务器并等待结果。 如果表已经存在，我们的 SQL 命令将删除该表。
$ ./create_table.rb
$ psql testdb
psql (9.3.9)
Type "help" for help.
testdb=> SELECT * FROM Cars;
id | name | price
----+------------+--------
1 | Audi | 52642
2 | Mercedes | 57127
3 | Skoda | 9000
4 | Volvo | 29000
5 | Bentley | 350000
6 | Citroen | 21000
7 | Hummer | 41400
8 | Volkswagen | 21600
(8 rows)
我们执行程序，并使用psql工具验证创建的表。
简单查询
在本节中，我们执行一个简单的查询命令。
query_version.rb
#!/usr/bin/ruby
require 'pg'
begin
con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
rs = con.exec 'SELECT VERSION()'
puts rs.getvalue 0, 0
rescue PG::Error => e
puts e.message
ensure
con.close if con
end
该示例获取数据库服务器的版本。
rs = con.exec 'SELECT VERSION()'
SELECT VERSION() SQL 语句检索数据库的版本。
puts rs.getvalue 0, 0
getvalue方法返回返回结果集的一行的单个字段值。
$ ./query_version.rb
PostgreSQL 9.3.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04) 4.8.4, 64-bit
程序将打印此输出。
检索多行数据
以下示例执行一个查询，该查询返回多行数据。
multiple_rows.rb
#!/usr/bin/ruby
require 'pg'
begin
con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
rs = con.exec "SELECT * FROM Cars LIMIT 5"
rs.each do |row|
puts "%s %s %s" % [ row['id'], row['name'], row['price'] ]
end
rescue PG::Error => e
puts e.message
ensure
rs.clear if rs
con.close if con
end
该程序将打印Cars表的前五行的数据。
rs = con.exec "SELECT * FROM Cars LIMIT 5"
此 SQL 查询返回五行数据。
rs.each do |row|
puts "%s %s %s" % [ row['id'], row['name'], row['price'] ]
end
使用each方法，我们遍历结果集并打印一行的杂项。
$ ./multiple_rows.rb
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
这是multiple_rows.rb程序的输出。
预备语句
预备语句可防止 SQL 注入并提高性能。 在使用预备语句时，我们使用占位符，而不是直接将值写入语句中。
prepared_statement.rb
#!/usr/bin/ruby
require 'pg'
if ARGV.length != 1 then
puts "Usage: prepared_statement.rb rowId"
exit
end
rowId = ARGV[0]
begin
con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
con.prepare 'stm1', "SELECT * FROM Cars WHERE Id=$1"
rs = con.exec_prepared 'stm1', [rowId]
puts rs.values
rescue PG::Error => e
puts e.message
ensure
rs.clear if rs
con.close if con
end
该程序将一个行 ID 作为其参数。 它获取指定行的数据并进行打印。 由于程序从用户那里获取了一个不可信任的值，因此有必要使用预备语句。
rowId = ARGV[0]
命令行参数存储在rowId变量中。
con.prepare 'stm1', "SELECT * FROM Cars WHERE Id=$1"
prepare方法准备一个具有给定名称的 SQL 语句，以便稍后执行。 我们的 SQL 语句返回 Cars 表的一行。 $1是一个占位符，稍后会填充一个实际值。
rs = con.exec_prepared 'stm1', [rowId]
exec_prepared方法执行由语句名称指定的预备命名语句。 第二个参数是 SQL 查询的绑定参数数组。
puts rs.values
values方法打印行的字段值。
$ ./prepared_statement.rb 4
4
Volvo
29000
这是示例的输出。
以下示例显示了另一种创建预备语句的方法。
prepared_statement2.rb
#!/usr/bin/ruby
require 'pg'
begin
con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
stm = "SELECT 1::int AS a,2::int AS b, $3::int AS c"
rs = con.exec_params(stm, [1, 2, 3])
puts rs.values
rescue PG::Error => e
puts e.message
ensure
rs.clear if rs
con.close if con
end
该示例使用exec_params创建并执行预备语句。
stm = "SELECT 1::int AS a,2::int AS b, $3::int AS c"
在语句中，我们将预期参数的数据类型附加到占位符。
rs = con.exec_params(stm, [1, 2, 3])
exec_params方法使用占位符作为参数将 SQL 查询请求发送到数据库。
$ ./prepared_statement2.rb
1
2
3
这是示例的输出。
元数据
元数据是有关数据库中数据的信息。 以下内容属于元数据：有关存储数据的表和列的信息，受 SQL 语句影响的行数或结果集中返回的行和列数。
列标题
在第一个示例中，我们打印列标题。
column_headers.rb
#!/usr/bin/ruby
require 'pg'
begin
con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
rs = con.exec 'SELECT * FROM Cars WHERE Id=0'
puts 'There are %d columns ' % rs.nfields
puts 'The column names are:'
puts rs.fields
rescue PG::Error => e
puts e.message
ensure
rs.clear if rs
con.close if con
end
该示例将可用列的数量及其名称打印到控制台。
rs = con.exec 'SELECT * FROM Cars WHERE Id=0'
在 SQL 语句中，我们选择一行的所有列。
puts "There are %d columns " % rs.nfields
nfields方法返回查询结果行中的列数。
puts rs.fields
fields方法返回一个字符串数组，该字符串表示结果中字段的名称。
$ ./column_headers.rb
There are 3 columns
The column names are:
id
name
price
这是示例的输出。
表的清单
PostgreSQL 的 信息模式 由一组视图组成，这些视图包含有关当前数据库中定义的对象的信息。 tables视图包含当前数据库中定义的所有表和视图。
list_tables.rb
#!/usr/bin/ruby
require 'pg'
begin
con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
rs = con.exec "SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'"
rs.each do |row|
puts row['table_name']
end
rescue PG::Error => e
puts e.message
ensure
rs.clear if rs
con.close if con
end
该示例打印testdb数据库中的所有表。
rs = con.exec "SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'"
该 SQL 语句从当前数据库中选择所有表。
rs.each do |row|
puts row['table_name']
end
表将打印到控制台。
$ ./list_tables.rb
authors
books
cars
list_tables.rb程序在testdb数据库中打印可用表。
事务
事务 是针对一个或多个数据库中数据的数据库操作的基本单位。 事务中的 SQL 语句可以全部提交给数据库，也可以全部回滚。 为了数据安全和完整性，将 SQL 语句放入事务中。
PostgreSQL 在 自动提交 模式下运行。 每个 SQL 语句都在一个事务中执行：每个单独的语句周围都包含一个隐式BEGIN和（如果成功）COMMIT。
显式事务以BEGIN命令开始，以COMMIT或ROLLBACK命令结束。
transaction.rb
#!/usr/bin/ruby
require 'pg'
begin
con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
con.transaction do |con|
con.exec "UPDATE Cars SET Price=23700 WHERE Id=8"
con.exec "INSERT INTO Car VALUES(9,'Mazda',27770)"
end
rescue PG::Error => e
puts e.message
ensure
con.close if con
end
在示例中，我们更新了汽车的价格并插入了新汽车。 这两个操作包含在单个事务中。 这意味着要么执行两个操作，要么不执行任何操作。
con.transaction do |con|
con.exec "UPDATE Cars SET Price=23700 WHERE Id=8"
con.exec "INSERT INTO Car VALUES(9,'Mazda',27770)"
end
transaction方法在单个事务中在块内运行代码。 它在块的开头执行BEGIN，在块的末尾执行COMMIT，如果发生任何异常，则执行ROLLBACK。