PostgreSQL binding for R7RS Scheme

This is a PostgreSQL socket frontend interface library written in pure R7RS Scheme.

NOTE: it's still working state.

Supported implementations

• Sagittarius 0.5.9 or later
• Gauche 0.9.4
• Chibi Scheme 0.7

This library should work in R7RS implementations which support the following SRFIs:

• SRFI-60/SRFI-33 or R6RS library (rnrs)
• SRFI-106
• SRFI-19 (Optional)

High level APIs

Library

• (postgresql) The library provides high level APIs to communicate with PostgreSQL.

Procedures

• (postgresql-connection? obj)

  Returns #t if obj is an PostgreSQL connection object.

• (make-postgresql-connection host port database username password)

  database can be #f.

  All arguments must be a string except database. Creates a PostgreSQL connection. At this moment, the connection to the server is not established.

• (postgresql-open-connection! conn)

  Establishes a connection with specified conn object.

• (postgresql-login! conn)

  Logging in to the PostgreSQL server.

• (postgresql-terminate! conn)

  Terminates the session and disconnects from the server.

• (postgresql-prepared-statement? obj)

  Return #t if obj is a PostgreSQL prepared statement.

• (postgresql-prepared-statement conn sql)

  Creates a prepared statement object.

• (postgresql-close-prepared-statement! prepared-statement)

  Closes the prepared statement prepared-statement.

• (postgresql-bind-parameters! prepared-statement . params)

  Binds parameter params to given prepared-statement.

• (postgresql-execute! prepared-statement)

  Executes the given prepared-statement and returns either PostgreSQL query object for SELECT statement or affected row count.

  To retrieve the result, use the postgresql-fetch-query! procedure.

• (postgresql-query? obj)

  Returns #t if obj is a PostgreSQL query object.

• (postgresql-execute-sql! conn sql)

  Executes the given sql statement. If sql is a select statement then the value returned is a PostgreSQL query object. Otherwise #t. This procedure retrieves all results in one go if sql is a SELECT statement. So it may cause memory explosion if the result set is too big.

• (postgresql-fetch-query! query)

  Fetch a row as a vector. If no more data are available, then returns #f.

• (postgresql-start-transaction! conn mode)

  Issue START TRANSACTION statement to start a transaction. mode specifies how the transation should be.

  The argument mode must be either a PostgreSQL transaction mode object or #f.

• (postgresql-transaction-mode alist)

  Creates a PostgreSQL transaction mode object. The alist specifies how the transaction mode is created. It may have the following symbols as its key.

  • isolation-level
  • access-mode
  • deferrable

  Each key must have one of the followings:

  For isolation-level:

  • Variable: postgresql-isolation-level-serializable
  • Variable: postgresql-isolation-level-repeatable-read
  • Variable: postgresql-isolation-level-read-committed
  • Variable: postgresql-isolation-level-read-uncommitted

  For access-mode:

  • Variable: postgresql-access-mode-read-write
  • Variable: postgresql-access-mode-read-only

  For deferrable:

  • Variable: postgresql-deferrable-on
  • Variable: postgresql-deferrable-off

• (postgresql-commit! conn)

  Issue COMMIT statement.

• (postgresql-rollback! conn)

  Issue ROLLBACK statement.

Parameters

• *postgresql-maximum-results*

  Configuration parameter for how many result it should fetch. Default value is 50.

• *postgresql-copy-data-handler*

  Handler of COPY to stdout command. The value must be a procedure and takes 2 arguments, data type and data. The data type should be one of the the following symbols:

  • header
  • data
  • complete

  When the data type is header then the given data is a list of data information. It contains 3 elements, the format of overall COPY command, 0 is textual, 1 is binary.

  When the data type is data then the given data is a bytevector whose content is the result of COPY command.

  When the data type is complete then the given data is #f. This indicates the COPY command is done.

• *postgresql-write-data-handler*

Handler of COPY from stdin command. The value must be a procedure and take 2 arguments, data type and data. The data type could be one of the following symbols;

• header
• data
• complete

When the data type is header then the given data is a list of data information. It contains 3 elements, the format of overall COPY command, 0 is textual, 1 is binary.

When the data type is data then the given data is a #f. When there is no more data to send, then the handler must return #f otherwise it would go into inifinite loop.

When the data type is complete then the given data is #t. This indicates the COPY command is done.

These handlers are currently a thin wrapper of the COPY command. Using them, users need to know about how the data is sent. For more detail, please refer the PostgreSQL manual.

• *postgresql-unknown-type-handler*

  Handler of unknown type, which is the library default couldn't handle converting the value according to the type identifier. The value must be aprocedure and take 2 arguments; type and value. The type is an integer which represents PostgreSQL internal type defined in catalog/pg_type.h header file of PostgreSQL source. The value is a raw value of SQL query, bytevector.

Low level APIs

TBD

Data conversion

Data conversion is done automatically by high level APIs. The following table describes how it's done.

PostgreSQL type	Scheme type
Integers	Number
Float	Inexact number
Characters	String
Date	SRFI-19 date
Time	SRFI-19 date
Timestamp	SRFI-19 time
UUID	String

Note: If the implementation doesn't support SRFI-19, the scheme type will be string.

Copyright and lincense

Copyright 2014-2015 Takashi Kato. Code released under the BSD-style license. See COPYING.

TODO

• Data conversion is not done properly
  • need some document but couldn't find it...
• Prepared statement
• Maybe buffering
  • currently it can't execute second query unless it fetches all records.