Author: Nenad Rakocevic Date: 01/02/2003 Version: 1.0.0 Comments: [email protected]
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
Just 'do it for installation. (square brackets mean optionnal datas)Usage:
orread pgsql://[[user][:pass]@]host[:port]/database?qdata
wheredb: 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
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.
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.
Just 'do it for installation.
Now the driver is installed and ready to work.>> do %pgsql-protocol.r pgSQL protocol loadedYou 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] ^^^^^
Currently this driver supports the following authentication methods (keywords taken from the pg_hba.conf file) :
The 'crypt method is not supported, but this may change in future releases. (Anyone to make a port of 'crypt in REBOL ? ;-))
- trust
- password
- md5 (requires server version 7.2+)
To connect to a PostgresQL server, use the 'open function :
wheredb: open pgsql://[[user][:pass]@]host[:port]/database
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:
'open returns an opened port to the server which will be used for all subsequent actions.db: open pgsql://root@localhost/sales db: open pgsql://dockimbel:[email protected]/test db: open pgsql://127.0.0.1/billsIf 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: *********
Use the 'insert function :
whereinsert port data
port is an opened port to a PostgreSQL server. data is a string containing the SQL query. Examples:
'insert will return 'none if it's ok or else an error (which can be trapped with 'try).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 )}
Use the 'insert function :
whereinsert port [data word1 word2 ...]
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]'insert will return 'none if it's ok or else an error (which can be trapped with 'try).tables: [books movies cds games tmp] foreach table tables [ insert db ["Delete from ?" table] ]
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 :
wherecopy[/part] port [number] or first port
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:
Watch out when choosing the method to get the records, they don't behave the same :; col 1 col 2 ... col n [ [item1 item2 ... itemn] ; row 1 [... ... ... ... ] ; row 2 ... ; ... [... ... ... ... ] ; row m ]
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:
Examples:
- 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.
probe copy/part db 3 ; get 3 records probe first db ; get just 1 record probe copy db ; get all remaining recordsforeach row copy db [probe row] ; usefull for a fast screen dumpwhile [not empty? row: first db][probe row] ; a memory saving version
Closing the port connection is done, as always, with the 'close function :
whereclose port
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.
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 :
You should give the same params as for 'open, except for 'qdata :read pgsql://[[user][:pass]@]host[:port]/database?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]
You can get more informations about the server or the result of a query with the following object:
Here's a list of the currently available properties :port/locals ; port is an opened port to a PostgresQL server
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.
The following table shows the default conversions applied to each value when reading records :
All others types are converted to REBOL string! datatype.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!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.
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 :
All other REBOL types will be FORM-ed !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)'sql-escaped-string are REBOL strings converted to PostgresQL format.
You can change the default conversion applied to each PostgresQL type using the following globally-defined function :
wherechange-type-handler port type handler
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 :
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.converted value <= [handler] <= raw value (string!) or new-value: handler raw-valueExamples:
;--- 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]Note:; 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]
- 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!
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: [email protected]
- 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.
Copyright notice:
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.(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 [email protected]If you redistribute modified sources, i would appreciate that you include in the pgsql-protocol.r file history information documenting your changes.