PostgresQL Driver Documentation

Author: Nenad Rakocevic
Date: 01/02/2003
Version: 1.0.0
Comments: pgsql@softinnov.com

Table of Contents

1. QuickStart
2. Introduction
3. Setup
4. Authentication modes
5. Connecting to the server
6. Sending commands
      6.1. Send an SQL query
      6.2. Send an SQL query inserting REBOL's values
7. Retrieving records
8. Closing connection
9. Compact format using 'read
10. Connection properties
11. Types conversions
      11.1. On reading records
      11.2. On inserting REBOL values
      11.3. User-defined conversion rules
12. Beta testing
13. Todo
14. Copyright

1. QuickStart

Just 'do it for installation. (square brackets mean optionnal datas)

Usage:

read pgsql://[[user][:pass]@]host[:port]/database?qdata

db: open pgsql://[[user][:pass]@]host[:port]/database
insert db "SQL query or server command"
copy[/part] db [rows [integer!]]
    or
first db        (basically, shortcut to copy/part db 1)
...
close db

where

user = a valid user name.
pass = a valid password or '?.
host = server address. (localhost or 127.0.0.1, if your server is local)
port = server port. (default is 5432)
database = a valid database name.
qdata = a URL-encoded valid SQL query or server command.

2. Introduction

The long awaited PostgresQL driver for REBOL is here !
It supports server protocol v2.0, so it will work with servers version 6.4 and up.
The driver is encapsulated in a new REBOL "scheme" (pgsql://) that will allow you to work with the database records as "external" series using insert, copy, first,... natives.
This driver use the same approach as our famous MySQL driver.
This driver should work correctly with all REBOL products.

3. Setup

Just 'do it for installation.
>> do %pgsql-protocol.r
pgSQL protocol loaded
Now the driver is installed and ready to work.
You can check at anytime if the driver have been loaded by doing:
>> probe first system/schemes
[self default Finger Whois Daytime SMTP POP HTTP FTP NNTP pgSQL]
                                                          ^^^^^

4. Authentication modes

Currently this driver supports the following authentication methods (keywords taken from the pg_hba.conf file) :

trust
password
md5 (requires server version 7.2+)
The 'crypt method is not supported, but this may change in future releases. (Anyone to make a port of 'crypt in REBOL ? ;-))

5. Connecting to the server

To connect to a PostgresQL server, use the 'open function :
db: open pgsql://[[user][:pass]@]host[:port]/database
where

user is a valid user name.

pass is a valid password or '?.

host is the server address. (localhost or 127.0.0.1, if your server is local)

port is the server port. (use this option if the server isn't listening on the default port 5432)

database is a valid database name.

Examples:
db: open pgsql://root@localhost/sales   
db: open pgsql://dockimbel:ytrfdzx6p@10.0.0.2/test
db: open pgsql://127.0.0.1/bills
'open returns an opened port to the server which will be used for all subsequent actions.
If the open process fails, it's most probably because you specified a wrong parameter or your user doesn't have the correct rights set. (check your pg_hba.conf file)
If you want to avoid showing your password in the console to everyone, you can use the '? character as replacement. You will be asked to enter your password in a hidden field.
Example:
>> db: open pgsql://dockimbel:?@localhost/test
Password: *********

6. Sending commands

6.1. Send an SQL query

Use the 'insert function :
insert port data
where

port is an opened port to a PostgreSQL server.

data is a string containing the SQL query.

Examples:
insert db "select * from user"
insert db "insert into mybooks values ('Elan', 'REBOL Official Guide')"
insert db {create table products (
    name        varchar(100),
    version     decimal(2, 2),
    released    date
)}
'insert will return 'none if it's ok or else an error (which can be trapped with 'try).

6.2. Send an SQL query inserting REBOL's values

Use the 'insert function :
insert port [data word1 word2 ...]
where

port is an opened port to a PostgresQL server.

data is a string containing the SQL query with '? characters.

word1 word2 ... list of defined REBOL words or values

All '? characters in the SQL query string will be replaced by the supplied REBOL values. If the number of '? characters is greater than the number of supplied values, the driver will send NULL values in place of the missing values. If the number of '? is lesser, the exceeding REBOL values will be ignored.
Look at "Type conversion" to see how the driver handles REBOL to SQL conversions.
Examples:
insert db ["insert into mybooks values (?,?)" "Elan" "REBOL Official Guide"]
author: "Elan"
title: "REBOL Official Guide"
insert db ["insert into mybooks values (?,?)" author title]
tables: [books movies cds games tmp]
foreach table tables [
    insert db ["Delete from ?" table]
]
'insert will return 'none if it's ok or else an error (which can be trapped with 'try).

7. Retrieving records

To get some data from the server, you have to, first, send a query or a command with 'insert. Then, you can retrieve records with the following functions :
copy[/part] port [number]
or
first port
where

port is an opened port to a PostgresQL server.

number is an integer, indicating the maximum number of records to retrieve.

Records are returned in a 2-dimensional block array, that looks like this:
; col 1 col 2 ... col n
[
 [item1 item2 ... itemn] ; row 1
 [...   ...   ...  ... ] ; row 2
 ...                     ; ...
 [...   ...   ...  ... ] ; row m
]
Watch out when choosing the method to get the records, they don't behave the same :

copy Will return all the available records. The next call to 'copy will return 'none.

copy/part Will return the specified number of records. If you get less records than excepted, it's because you've reached the end of the recordset and there's no more record available.

first Will return the next available record. If there's no more records, 'first will return an empty block [].

Notes:

If you don't read all the available records, they will be flushed on next call to 'insert or 'close.
When dealing with big recordsets, memory consumption should be an issue. In this case just avoid using 'copy ! Use 'first or 'copy/part to get the records, especially if records contains blob binaries.
Examples:
probe copy/part db 3    ; get 3 records
probe first db          ; get just 1 record
probe copy db           ; get all remaining records
foreach row copy db [probe row] ; usefull for a fast screen dump
while [not empty? row: first db][probe row] ; a memory saving version

8. Closing connection

Closing the port connection is done, as always, with the 'close function :
close port
where

port is an opened port to a PostgresQL server.

Don't forget to close the connection, even in a CGI script, because the server has allocated ressources for your connection and it has to free them.

9. Compact format using 'read

REBOL allows a compact format for getting data from an url. The miracle is done by the 'read function. Here is the syntax for the PostgresQL driver :
read pgsql://[[user][:pass]@]host[:port]/database?qdata
You should give the same params as for 'open, except for 'qdata :

qdata an URL-encoded valid SQL query or server command.

'read will open the connection to the specified PostgresQL server, send the given query, retrieve the resulting recordset and close the connection ! This all-in-one format should be usefull in simple CGI scripts or for testing purpose from the console.
Note:
Don't forget that 'qdata have to be in a URL compliant format, so a good way to ensure that is to use 'join.
Examples:
probe read join pgsql://root@localhost/test? "select * from pg_types"
probe read join pgsql://root@localhost/test? "select oid from pg_attribute"
probe read join pgsql://root@localhost/test? "select * from pg_class"
; The most powerfull 1-line script ! ;-)
foreach row read join pgsql://root@localhost/books? "select * from authors" [print row]

10. Connection properties

You can get more informations about the server or the result of a query with the following object:
port/locals ; port is an opened port to a PostgresQL server
Here's a list of the currently available properties :

max-rows (not yet supported, use copy/part instead)

auto-conv? Set to 'on will automatically convert types when decoding records. Set to 'off will produce records with all values of type string!.

auto-ping? (not yet supported)

matched-rows (read-only) Will give the number of affected rows after an 'insert, 'update or 'delete SQL query.

conv-list (read-only) Description list used for type conversion.

columns (read-only) Block of columns objects.

process-id (read-only) ID of the backend process handling the current connection.

cursor (read-only) Last cursor returned by the server

last-notice (read-only) Last warning message from the server.

error-code (read-only) Error code for the last error.

error-msg (read-only) Error description for the last error.

Notes:

Just do a 'probe port/locals after sending a query to see an example. The properties not described here are reserved for driver's internal use.

11. Types conversions

11.1. On reading records

The following table shows the default conversions applied to each value when reading records :
PostgresQL types        REBOL types
-----------             -----------
    bool        =>        logic!
    bytea       =>        binary!
    char        =>        char!
    int8        =>        decimal!
    int2        =>        integer!
    int4        =>        integer!
    oid         =>        integer!
    float4      =>        decimal!
    float8      =>        decimal!
    money       =>        money!
    inet        =>        date! or [date! integer!]
    date        =>        date!
All others types are converted to REBOL string! datatype.
PostgresQL 'NULL value is handled separately at low-level in the driver. 'NULL is always converted to none!.
Changing the default conversion can be done with the 'change-type-handler function.

11.2. On inserting REBOL values

When you use the '? character in your SQL statement, the driver will automatically do some encoding for you on passed REBOL values. Here is the list of the currently implemented conversion rules :
REBOL types     SQL
-----------     ---
  none!     =>  NULL
  date!     =>  'yyyy-mm-jj' or 'yyyy-mm-jj hh:mm:ss'
  time!     =>  'hh:mm:ss'
  money!    =>  '$##.##'
  tuple!    =>  'a.b.c.d'
  string!   =>  'sql-escaped-string'
  binary!   =>  'sql-escaped-string'
  pair!     =>  '(x,y)'
All other REBOL types will be FORM-ed !
sql-escaped-string are REBOL strings converted to PostgresQL format.

11.3. User-defined conversion rules

You can change the default conversion applied to each PostgresQL type using the following globally-defined function :
change-type-handler port type handler
where

port is an opened port to a PostgresQL server.

type is a valid PostgresQL type name. (See this table for allowed names)

handler is a block! containing the conversion code.

The handler works like a pipe :
converted value <= [handler] <= raw value (string!)
  or
new-value: handler raw-value
So you have to make a correct "piping" REBOL code, which takes a value from the right side and returns the converted value at the left side. You could make your own conversions after retrieving records with 'copy, but using 'change-type-handler will ensure you the best performances because your conversion code will be executed at low-level in a special 1-pass conversion process. This is especially true if you're retrieving a lot of records.
Examples:
;--- Convert 'int2 values to REBOL hex strings :
change-type-handler db 'int2 [to-hex to-integer]
;--- Convert 'bytea (blob) to REBOL objects :
to-obj: func [value][first reduce load to string! decompress]
change-type-handler db 'bytea [to-obj]
; assuming that your objects have been stored in the following way :
to-blob: func [obj [object!]][compress mold obj]
insert db ["Insert into table1 values (?)" to-blob my-obj]
Note:

You can suspend at any time the conversion engine by setting the port/locals/auto-conv? property to 'off. This will speed up the driver a lot but all values will be strings !.
You can check at any time the type conversion list with:
>> probe your-port-name/locals/conv-list
The type handlers are specific to a connection. So if you install your own handlers for a connection , they won't affect any previous or new connection handlers!

12. Beta testing

This driver is still under Beta. It may have some bugs. I need some beta-testers with good PostgresQL skills to help me debug and fine-tune this driver. My goal is to get a robust and stable v1.0 which could be used in production.(mainly for websites)
If you feel comfortable with PostgresQL, give a try to the driver and report me all bugs and your comments about it! If you need a better support for some features (or features i didn't implement), feel free to send us all your needs/ideas.
If you need a commercial support, contact us to express your needs.
(If you find some bugs in this driver, you can add to your report a copy of the console output with trace/net activated.)
My e-mail: nr@softinnov.com

13. Todo

Reach a 1.0 rock-solid production version.
Clean-up and optimize the code.
Better support the various 'time' types.
Add command dialect.
Support SSL connection mode (/Command only)
Add support for large data import/export (SQL command: COPY).
See how to handle types more dynamically.
Support 'crypt authentication mode (?).
Complete the documentation. (add a chapter on stored procedures)
Add a command-line client (psql like).
Make a graphical client and admin tool.

14. Copyright

Copyright notice:
(C) 2003 SOFTINNOV / Nenad Rakocevic

 This software is provided 'as-is', without any express or implied
 warranty.  In no event will the author be held liable for any damages
 arising from the use of this software.

 Permission is granted to anyone to use this software for any purpose,
 including commercial applications, and to alter it and redistribute it
 freely, subject to the following restrictions:

 1. The origin of this software must not be misrepresented; you must not
 claim that you wrote the original software. If you use this software
 in a product, an acknowledgment in the product documentation would be
 appreciated but is not required.
 2. Altered source versions must be plainly marked as such, and must not be
 misrepresented as being the original software.
 3. This notice may not be removed or altered from any source distribution.

 SOFTINNOV/Nenad Rakocevic
 nr@softinnov.com
If you use the PostgresQL driver in a product, i would appreciate *not* receiving lengthy legal documents to sign. The sources are provided for free but without warranty of any kind. The driver has been entirely written by Nenad Rakocevic; it does not include third-party code.
If you redistribute modified sources, i would appreciate that you include in the pgsql-protocol.r file history information documenting your changes.

	user	is a valid user name.
	pass	is a valid password or '?.
	host	is the server address. (localhost or 127.0.0.1, if your server is local)
	port	is the server port. (use this option if the server isn't listening on the default port 5432)
	database	is a valid database name.

	port	is an opened port to a PostgreSQL server.
	data	is a string containing the SQL query.

	port	is an opened port to a PostgresQL server.
	number	is an integer, indicating the maximum number of records to retrieve.

	copy	Will return all the available records. The next call to 'copy will return 'none.
	copy/part	Will return the specified number of records. If you get less records than excepted, it's because you've reached the end of the recordset and there's no more record available.
	first	Will return the next available record. If there's no more records, 'first will return an empty block [].

	max-rows	(not yet supported, use copy/part instead)
	auto-conv?	Set to 'on will automatically convert types when decoding records. Set to 'off will produce records with all values of type string!.
	auto-ping?	(not yet supported)
	matched-rows	(read-only) Will give the number of affected rows after an 'insert, 'update or 'delete SQL query.
	conv-list	(read-only) Description list used for type conversion.
	columns	(read-only) Block of columns objects.
	process-id	(read-only) ID of the backend process handling the current connection.
	cursor	(read-only) Last cursor returned by the server
	last-notice	(read-only) Last warning message from the server.
	error-code	(read-only) Error code for the last error.
	error-msg	(read-only) Error description for the last error.

	port	is an opened port to a PostgresQL server.
	type	is a valid PostgresQL type name. (See this table for allowed names)
	handler	is a block! containing the conversion code.