PostgreSQL Ruby tutorial

13 minute read

背景

转自 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.

Flag Counter

digoal’s 大量PostgreSQL文章入口