pg.scm -- socket level interface to the PostgreSQL RDBMS for DrScheme Version: 0.18 Copyright (C) 1999,2000,2001,2002,2003 Eric Marsden DrScheme version: Zbigniew Jurkiewicz Copyright: (C) 2001,2002,2003 Zbigniew Jurkiewicz == Overview ========================================================= This module lets you access the PostgreSQL object-relational DBMS from DrScheme and provides a wrapper around the libpq module. The module is capable of type oercions from a range of SQL types to the equivalent Lisp type. Works with MzScheme and DrScheme. == Entry points ======================================================= (with-pg-connection ((con . open-args) . body) A macro which opens a connection to database DBNAME, executes the BODY forms then disconnects. See function `pg-connect' for details of the connection arguments OPEN-ARGS. (with-pg-transaction con . body) A macro which executes the BODY forms wrapped in an SQL transaction. CON is a connection to the database. If an error occurs during the execution of the forms, a ROLLBACK instruction is executed. (pg-connect dbname user [password host port]) -> connection Connect to the database DBNAME on HOST (defaults to localhost) at PORT (defaults to 5432), and log in as USER. If HOST is #f, attempt to connect to the localhost using a Unix domain socket; otherwise the connection is established using TCP/IP. If the database requires a password, send PASSWORD (as clear text unless the backend demands crypt() authentication). Set the output date type to 'ISO', and initialize our type parser tables. (pg-exec connection . sql) -> pgresult Concatenate the SQL strings and send to the backend. Retrieve all the information returned by the database and return it in an opaque record PGRESULT. (pg-result pgresult what . args) -> info Extract information from the PGRESULT. WHAT can be one of * 'connection * 'status * 'attributes * 'tuples * 'tuple tupleNumber * 'oid `connection' allows you to retrieve the database connection. `status' is a string returned by the backend to indicate the status of the command; it is normally "SELECT" for a select command, "DELETE 1" if the deletion affected a single row, etc. `attributes' is a list of tuples providing metadata: the first component of each tuple is the attribute's name as a string, the second an integer representing its PostgreSQL type, and the third an integer representing the size of that type. `tuples' returns all the data retrieved from the database, as a list of lists, each list corresponding to one row of data returned by the backend. `tuple num' can be used to extract a specific tuple. `oid' allows you to retrieve the OID returned by the backend if the command was an insertion; the OID is a unique identifier for that row in the database (this is PostgreSQL-specific, please refer to the documentation for more details). (pg-for-each connection select-form callback) Calls CALLBACK on each tuple returned by SELECT-FORM. Declares a cursor for SELECT-FORM, then fetches tuples using repeated executions of FETCH 1, until no results are left. The cursor is then closed. The work is performed within a transaction. When you have a large amount of data to handle, this usage is more efficient than fetching all the tuples in one go. (pg-disconnect connection) -> nil Close the database connection. (pg-databases connection) -> list of strings Return a list of the databases available at this site (a database is a set of tables; in a virgin PostgreSQL installation there is a single database named "template1"). (pg-tables connection) -> list of strings Return a list of the tables present in the database to which we are currently connected. Only include user tables: system tables are excluded. (pg-columns connection table) -> list of strings Return a list of the columns (or attributes) in TABLE, which must be a table in the database to which we are currently connected. We only include the column names; if you want more detailed information (attribute types, for example), it can be obtained from `pg-result' on a SELECT statement for that table. (pglo-create conn . args) -> oid Create a new large object (BLOB, or binary large object in other DBMSes parlance) in the database to which we are connected via CONN. Returns an OID (which is represented as a Scheme integer) which will allow you to use the large object. Optional ARGS are a Unix-style mode string which determines the permissions of the newly created large object, one of "r" for read-only permission, "w" for write-only, "rw" for read+write. Default is "r". Large-object functions MUST be used within a transaction (see the macro `with-pg-transaction'). (pglo-open conn oid . args) -> fd Open a large object whose unique identifier is OID (a Scheme integer) in the database to which we are connected via CONN. Optional ARGS is a Unix-style mode string as for pglo-create; which defaults to "r" read-only permissions. Returns a file descriptor (a Scheme integer) which can be used in other large-object functions. (pglo-close conn fd) Close the file descriptor FD which was associated with a large object. Note that this does not delete the large object; use PGLO-UNLINK for that. (pglo-read conn fd bytes) -> string Read BYTES from the file descriptor FD which is associated with a large object. Return a string which should be BYTES characters long. (pglo-write connection fd buf) Write the bytes contained in the string BUF to the large object associated with the file descriptor FD. (pglo-lseek conn fd offset whence) Do the equivalent of a lseek(2) on the file descriptor FD which is associated with a large object; ie reposition the read/write file offset for that large object to OFFSET (a Scheme integer). WHENCE has the same significance as in lseek(); it should be one of SEEK_SET (set the offset to the absolute position), SEEK_CUR (set the offset relative to the current offset) or SEEK_END (set the offset relative to the end of the file). WHENCE should be an integer whose values can be obtained from the header file (probably 0, 1 and 2 respectively). (pglo-tell conn oid) -> integer Do the equivalent of an ftell(3) on the file associated with the large object whose unique identifier is OID. Returns the current position of the file offset for the object's associated file descriptor, as a Scheme integer. (pglo-unlink conn oid) Remove the large object whose unique identifier is OID from the system (in the current implementation of large objects in PostgreSQL, each large object is associated with an object in the filesystem). (pglo-import conn filename) -> oid Create a new large object and initialize it to the data contained in the file whose name is FILENAME. Returns an OID (as a Scheme integer). Note that is operation is only syntactic sugar around the basic large-object operations listed above. (pglo-export conn oid filename) Create a new file named FILENAME and fill it with the contents of the large object whose unique identifier is OID. This operation is also syntactic sugar. Boolean variable `*PG-DISABLE-TYPE-COERCION*' which can be set to #t (before initiating a connection) to disable the library's type coercion facility. Default is #f. ;!!! SECURITY NOTE: please note that your postmaster has to be started with the `-i' option in order for it to accept TCP/IP connections (typically this is not the default setting). See the PostgreSQL documentation at for more information. Setting up PostgreSQL to accept TCP/IP connections has security implications; please consult the documentation for details. You can connect to the database using Unix domain sockets if you wish to avoid setting up PostgreSQL to listen on a TCP socket. pg.scm is able to use the crypt authentication method to avoid sending the password in cleartext over the wire (this assumes access to the `crypt' function via the FFI). It does not support the Kerberos authentication method, nor OpenSSL connections (though this should not be difficult if your Scheme implementation is able to open SSL streams). However, it is possible to use the port forwarding capabilities of ssh to establish a connection to the backend over TCP/IP, which provides both a secure authentication mechanism and encryption (and optionally compression) of data passing through the tunnel. Here's how to do it (thanks to Gene Selkov, Jr. for the description): 1. Establish a tunnel to the backend machine, like this: ssh -L 3333:backend.dom:5432 postgres@backend.dom The first number in the -L argument, 3333, is the port number of your end of the tunnel. The second number, 5432, is the remote end of the tunnel -- the port number your backend is using. The name or the address in between the port numbers belongs to the server machine, as does the last argument to ssh that also includes the optional user name. Without the user name, ssh will try the name you are currently logged on as on the client machine. You can use any user name the server machine will accept, not necessarily those related to postgres. 2. Now that you have a running ssh session, you can point pg.scm to the local host at the port number which you specified in step 1. For example, (pg-connect "dbname" "user" "password" "localhost" 3333) You can omit the last three arguments if you chose 5432 as the local end of the tunnel, since pg.scm defaults to this value. This code has been tested or reported to work with * MzScheme 209, DrScheme 209 on Linux/x86 * PostgreSQL 6.5, 7.0, 7.1.2, 7.2.