PostgreSQL Ruby tutorial
背景
转自 http://zetcode.com/db/postgresqlruby/
正文
This is a Ruby programming tutorial for the PostgreSQL database. It covers the basics of PostgreSQL programming with the Ruby language.
PostgreSQL
PostgreSQL is a powerful, open source, object-relational database system. It is a multi-user database management system. It runs on multiple platforms, including Linux, FreeBSD, Solaris, Microsoft Windows, and Mac OS X. PostgreSQL is developed by the PostgreSQL Global Development Group.
PostgreSQL has sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting.
Ruby
Ruby is a dynamic, reflective, general-purpose object-oriented programming language. The original author is a Japanese programmer Yukihiro Matsumoto. Ruby first appeared in 1995. Ruby supports various programming paradigms. This includes object orientation, reflection, imperative, and reflective programming.
Ruby pg
Ruby pg is a module that allows Ruby programs to interact with the PostgreSQL database engine. It supports the functions defined in the libpq C library.
Installation
We are going to install PostgreSQL database and additional necessary libraries.
$ sudo apt-get install postgresql
On a Debian-based system, we can install the PostgreSQL database from the packages using the above command.
$ 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
If we install the PostgreSQL database from packages, it is automatically added to the startup scripts of the operating system. If we are only learning to work with the database, it is unnecessary to start the database each time we boot the system. The above command removes any system startup links for the PostgreSQL database.
$ sudo apt-get install libpq-dev
To compile the Ruby pg module, we also need the development files of the C libpg library.
$ sudo -u postgres psql postgres
psql (9.3.9)
Type "help" for help.
postgres=# \password postgres
We set a password for the postgres user.
$ sudo apt-get install ruby-dev
We install the Ruby development libraries, which are needed for compiling Ruby extention modules.
$ sudo gem install pg
We install the Ruby pg module, which is the Ruby interface to the PostgreSQL database.
Starting and stopping PostgreSQL
In the next section, we are going to show how to start the PostgreSQL database, stop it, and query its status.
$ sudo service postgresql start
* Starting PostgreSQL 9.3 database server [ OK ]
On Debian-based Linux, we can start the server with the service postgresql start command.
$ sudo service postgresql status
9.3/main (port 5432): online
We use the service postgresql status command to check if PostgreSQL is running.
$ sudo service postgresql stop
* Stopping PostgreSQL 9.3 database server [ OK ]
We use the service postgresql stop command to stop PostgreSQL.
$ service postgresql status
9.3/main (port 5432): down
At this moment, the service postgresql status command reports that the PostgreSQL database is down.
Creating a user and a database
In the following steps, we create a new database user and database.
$ sudo -u postgres createuser janbodnar
We create a new role in the PostgreSQL system. We allow it to have ability to create new databases. A role is a user in a database world. Roles are separate from operating system users.
$ sudo -u postgres psql postgres
psql (9.3.9)
Type "help" for help.
postgres=# ALTER USER janbodnar WITH password 'pswd37';
ALTER ROLE
postgres=# \q
With the psql command, we add a password for the new user.
PostgreSQL often uses trust or peer authentication policies on local connections. In case of the trust authentication policy, PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify (even superuser names). When making connections to the database, no password is required. (The restrictions made in the database and user columns still apply.) The trust authentication is appropriate and very convenient for local connections on a single-user workstation. It is usually not appropriate on a multiuser machine. In case of the peer authentication policy, the database username must match the operating system username.
$ sudo -u postgres createdb testdb --owner janbodnar
With the createdb command, we create a new database called testdb. Its owner is the new database user.
The libpq library
The libpq library is the C interface to PostgreSQL. It is a set of library functions that allow client programs to interact with PostgreSQL. It is also the underlying engine for several other PostgreSQL application interfaces, including those written for C++, Perl, PHP, Ruby, Python, and Tcl.
Ruby pg module is a wrapper around the libpg library.
lib_version.rb
#!/usr/bin/ruby
require 'pg'
puts 'Version of libpg: ' + PG.library_version.to_s
The program prints the version of the libpq library.
require 'pg'
We include the pg module.
puts 'Version of libpg: ' + PG.library_version.to_s
The library_version method returns the version of the libpq being used.
$ ./lib_version.rb
Version of libpg: 90309
The version of the library is 9.3.9.
Server version
In the following example, we find out the version of the PostgreSQL database.
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
The example connects to the PostgreSQL database, executes a server_version method, prints the version, closes the connection, and cleans up.
...
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
...
In the pg_hba.conf, we have the peer default authentication method. In this method, the database user name must match the operating system user name. No password is required to make a connection.
con = PG.connect :dbname => 'testdb', :user => 'janbodnar'
With the connect method, we make a connection to the database. In the connection string, we provide the user name and the database name.
rescue PG::Error => e
puts e.message
We check for errors. This is important, since working with databases is error prone.
ensure
con.close if con
end
In the end, we release the resources.
$ ./server_version.rb
90309
Running the program, we get the database server version.
Authentication with a password
Next, we are going to authenticate to the database server with a password. In all other examples in this tutorial, we assume the peer or trust authentication mode. We change the authentication type for the local connection inside the pg_hba.conf file to md5.
$ sudo service postgresql restart
To apply the changes, the database server must be restarted.
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
In the example, we connect to the database with a password. We print the username, database name, and the password of the current database connection.
con = PG.connect :dbname => 'testdb', :user => 'janbodnar',
:password => 'pswd37'
In the connection string, we add the password option.
user = con.user
The user method returns the user name of the connection.
db_name = con.db
The db method returns the database name of the connection.
pswd = con.pass
The pass method returns the password of the connection.
$ ./password_authentication.rb
User: janbodnar
Database name: testdb
Password: pswd37
The program prints the database user, the database name, and the password used.
Creating a database table
In this section, we create a database table and fill it with data.
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
The created table is called Cars and it has three columns: the Id, the name of the car, and its price.
con.exec "DROP TABLE IF EXISTS Cars"
The exec method submits an SQL command to the server and waits for the result. Our SQL command drops a table if it already exists.
$ ./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)
We execute the program and verify the created table with the psql tool.
Simple query
In this section, we execute a simple query command.
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
The example gets the version of the database server.
rs = con.exec 'SELECT VERSION()'
The SELECT VERSION() SQL statement retrieves the version of the database.
puts rs.getvalue 0, 0
The getvalue method returns a single field value of one row of the returned result set.
$ ./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
The program prints this output.
Retrieving multiple rows of data
The following example executes a query that returns multiple rows of data.
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
The program prints the data of the first five rows of the Cars table.
rs = con.exec "SELECT * FROM Cars LIMIT 5"
This SQL query returns five rows of data.
rs.each do |row|
puts "%s %s %s" % [ row['id'], row['name'], row['price'] ]
end
With the each method, we go through the result set and print the fieds of a row.
$ ./multiple_rows.rb
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
This is the output of the multiple_rows.rb program.
Prepared statements
Prepared statements guard against SQL injections and increase performance. When using prepared statements, we use placeholders instead of directly writing the values into the statements.
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
The program takes a row Id as its argument. It fetches the data of the specified row and prints it. Since the program takes a value from a user, which cannot be trusted, it is necessary to use a prepared statement.
rowId = ARGV[0]
The command line argument is stored in the rowId variable.
con.prepare 'stm1', "SELECT * FROM Cars WHERE Id=$1"
The prepare method prepares an SQL statement with the given name to be executed later. Our SQL statement returns one row of the Cars table. The $1 is a placeholder, which is later filled with an actual value.
rs = con.exec_prepared 'stm1', [rowId]
The exec_prepared method executes a prepared named statement specified by the statement name. The second parameter is an array of bind parameters for the SQL query.
puts rs.values
The values method prints the field values of the row.
$ ./prepared_statement.rb 4
4
Volvo
29000
This is the output of the example.
The following example shows another way to create prepared statements.
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
The example uses exec_params to create and execute a prepared statement.
stm = "SELECT $1::int AS a, $2::int AS b, $3::int AS c"
In the statement, we append data types of the expected parameters to the placeholders.
rs = con.exec_params(stm, [1, 2, 3])
The exec_params method sends an SQL query request to the database using placeholders for parameters.
$ ./prepared_statement2.rb
1
2
3
This is the output of the example.
Metadata
Metadata is information about the data in the database. The following belongs to metadata: information about the tables and columns in which we store data, the number of rows affected by an SQL statement, or the number of rows and columns returned in a result set.
Column headers
In the first example, we print column headers.
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
The example prints the number of available columns and their names to the console.
rs = con.exec 'SELECT * FROM Cars WHERE Id=0'
In the SQL statement, we select all columns of a row.
puts "There are %d columns " % rs.nfields
The nfields method returns the number of columns in the row of the query result.
puts rs.fields
The fields method returns an array of strings representing the names of the fields in the result.
$ ./column_headers.rb
There are 3 columns
The column names are:
id
name
price
This is the output of the example.
Listing tables
The PostgreSQL’s information schema consists of a set of views that contain information about the objects defined in the current database. The tables view contains all tables and views defined in the current database.
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
The example prints all the tables in the testdb database.
rs = con.exec "SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public'"
This SQL statement selects all tables from the current database.
rs.each do |row|
puts row['table_name']
end
The tables are printed to the console.
$ ./list_tables.rb
authors
books
cars
The list_tables.rb program prints available tables in the testdb database.
Transactions
A transaction is an atomic unit of database operations against the data in one or more databases. SQL statements in a transaction can be either all committed to the database or all rolled back. SQL statements are put into transactions for data safety and integrity.
PostgreSQL operates in the autocommit mode. Every SQL statement is executed within a transaction: each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it.
An explicit transaction is started with the BEGIN command and ended with the COMMIT or ROLLBACK command.
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
In the example, we update the price of a car and insert a new car. The two operations are included in a single transaction. This means that either both operations are executed or none.
con.transaction do |con|
con.exec "UPDATE Cars SET Price=23700 WHERE Id=8"
con.exec "INSERT INTO Car VALUES(9,'Mazda',27770)"
end
The transaction method runs the code inside the block in a single transaction. It executes a BEGIN at the start of the block, and a COMMIT at the end of the block, or ROLLBACK if any exception occurs.