2 Connections
This section describes functions for creating connections as well as
administrative functions for managing connections.
2.1 Base Connections
There are four kinds of base connection, and they are divided into two
groups: wire-based connections and FFI-based
connections. PostgreSQL and MySQL connections are wire-based, and
SQLite and ODBC connections are FFI-based.
Wire-based connections communicate using ports, which
do not cause other Racket threads to block. In contrast, an FFI call
causes all Racket threads to block until it completes, so FFI-based
connections can degrade the interactivity of a Racket program,
particularly if long-running queries are performed using the
connection. This problem can be avoided by creating the FFI-based
connection in a separate place using the
#:use-place keyword argument. Such a connection will not
block all Racket threads during queries; the disadvantage is the cost
of creating and communicating with a separate place.
Base connections are made using the following functions.
| (postgresql-connect | | | | | #:user user | | | | | #:database database | | | | [ | #:server server | | | | | #:port port | | | | | #:socket socket | | | | | #:password password | | | | | #:allow-cleartext-password? allow-cleartext-password? | | | | | #:ssl ssl | | | | | #:ssl-context ssl-context | | | | | #:notice-handler notice-handler | | | | | #:notification-handler notification-handler]) | |
|
| → connection? |
| user : string? |
| database : string? |
| server : string? = "localhost" |
| port : exact-positive-integer? = 5432 |
| socket : (or/c path-string? 'guess #f) = #f |
| password : (or/c string? #f) = #f |
| allow-cleartext-password? : boolean? = #f |
| ssl : (or/c 'yes 'optional 'no) = 'no |
|
|
| | notification-handler | | : | | | | | | | = | | void |
|
Creates a connection to a PostgreSQL server. Only the
database and user arguments are mandatory.
By default, the connection is made via TCP to "localhost"
at port 5432. To make a different TCP connection, provide
one or both of the server and port arguments.
To connect via a local socket, specify the socket path as the
socket argument. You must not supply the socket
argument if you have also supplied either of the TCP arguments. See
also Local Sockets for PostgreSQL and MySQL Servers for notes on socket
paths. Supplying a socket argument of 'guess is
the same as supplying (postgresql-guess-socket-path).
Sockets are only available under Linux (x86) and Mac OS X.
If the server requests password authentication, the
password argument must be present; otherwise an exception
is raised. If the server does not request password authentication,
the password argument is ignored and may be omitted. A
connection normally only sends password hashes (using the md5
authentication method). If the server requests a password sent as
cleartext (un-hashed), the connection is aborted unless
allow-cleartext-password? is true.
If the ssl argument is either 'yes or
'optional, the connection attempts to negotiate an SSL
connection. If the server refuses SSL, the connection raises an
exception if ssl was set to 'yes or continues with
an unencrypted connection if ssl was set to
'optional. By default, SSL provides encryption but does not
verify the identity of the server (see
this
explanation). Host verification can be required via the
ssl-context argument; see ssl-set-verify!. Some
servers use SSL certificates to authenticate clients; see
ssl-load-certificate-chain! and
ssl-load-private-key!. SSL may only be used with TCP
connections, not with local sockets.
The notice-handler is called on notice messages
received asynchronously from the server. A common example is notice
of an index created automatically for a table’s primary key. The
notice-handler function takes two string arguments: the
condition’s SQLSTATE and a message. The
notification-handler is called in response to an event
notification (see the LISTEN and NOTIFY statements); its
argument is the name of the event as a string. An output port may be
supplied instead of a procedure, in which case a message is printed
to the given port. Finally, the symbol 'output causes the
message to be printed to the current output port, and
'error causes the message to be printed to the current
error port.
If the connection cannot be made, an exception is raised.
Examples: |
| > (postgresql-connect #:server "db.mysite.com" | | #:port 5432 | | #:database "webappdb" | | #:user "webapp" | | #:password "ultra5ecret") |
| (object:connection% ...) | | (object:connection% ...) | | > (postgresql-connect ; Typical socket path | | #:socket "/var/run/postgresql/.s.PGSQL.5432" | | #:user "me" | | #:database "me") |
| (object:connection% ...) | | > (postgresql-connect #:socket 'guess ; or (postgresql-guess-socket-path) | | #:user "me" | | #:database "me") |
| (object:connection% ...) |
|
Attempts to guess the path for the socket based on conventional
locations. This function returns the first such path that exists in
the filesystem. It does not check that the path is a socket file,
nor that the path is connected to a PostgreSQL server.
If none of the attempted paths exist, an exception is raised.
Creates a connection to a MySQL server. If database is
#f, the connection is established without setting the
current database; it should be subsequently set with the USE
SQL command.
The meaning of the other keyword arguments is similar to those of
the postgresql-connect function, except that the first
argument to a notice-handler function is a MySQL-specific
integer code rather than a SQLSTATE string, and a socket
argument of 'guess is the same as supplying
(mysql-guess-socket-path).
If the connection cannot be made, an exception is raised.
Examples: |
| > (mysql-connect #:server "db.mysite.com" | | #:port 3306 | | #:database "webappdb" | | #:user "webapp" | | #:password "ultra5ecret") |
| (object:connection% ...) | | > (mysql-connect #:user "me" | | #:database "me" | | #:password "icecream") |
| (object:connection% ...) | | > (mysql-connect ; Typical socket path | | #:socket "/var/run/mysqld/mysqld.sock" | | #:user "me" | | #:database "me") |
| (object:connection% ...) | | (object:connection% ...) |
|
Attempts to guess the path for the socket based on conventional
locations. This function returns the first such path that exists in
the filesystem. It does not check that the path is a socket file,
nor that the path is connected to a MySQL server.
If none of the attempted paths exist, an exception is raised.
Opens the SQLite database at the file named by database, if
database is a string or path. If database is
'temporary, a private disk-based database is created. If
database is 'memory, a private memory-based
database is created.
If mode is 'read-only, the database is opened in
read-only mode. If mode is 'read/write (the
default), the database is opened for reading and writing (if
filesystem permissions permit). The 'create mode is like
'read/write, except that if the given file does not exist,
it is created as a new database.
SQLite uses coarse-grained
locking, and many internal operations fail with the
SQLITE_BUSY condition when a lock cannot be acquired. When an
internal operation fails because the database is busy, the
connection sleeps for busy-retry-delay seconds and retries
the operation, up to busy-retry-limit additional times. If
busy-retry-limit is 0, the operation is only
attempted once. If after busy-retry-limit retries the
operation still does not succeed, an exception is raised.
If use-place is true, the actual connection is created in
a distinct place for database connections and a
proxy is returned.
If the connection cannot be made, an exception is raised.
Examples: |
| > (sqlite3-connect #:database "/path/to/my.db") | (object:connection% ...) | | (object:connection% ...) |
|
| (odbc-connect | | | | | #:dsn dsn | | | | [ | #:user user | | | | | #:password password | | | | | #:notice-handler notice-handler | | | | | #:strict-parameter-types? strict-parameter-types? | | | | | #:character-mode character-mode | | | | | #:use-place use-place]) | |
|
| → connection? |
| dsn : string? |
| user : (or/c string? #f) = #f |
| password : (or/c string? #f) = #f |
|
| strict-parameter-types? : boolean? = #f |
| character-mode : (or/c 'wchar 'utf-8 'latin-1) = 'wchar |
| use-place : boolean? = #f |
Creates a connection to the ODBC Data Source named
dsn. The
user and
password arguments are optional, since
that information may be incorporated into the data source
definition, or it might not be relevant to the data source’s driver.
The
notice-handler argument behaves the same as in
postgresql-connect.
If strict-parameter-types? is true, then the connection
attempts to determine and enforce specific types for query
parameters. See ODBC Types for more details.
By default, connections use ODBC’s SQL_C_WCHAR-based character
encoding (as UTF-16) to send and receive Unicode character
data. Unfortunately, some drivers’ support for this method is
buggy. To use SQL_C_CHAR instead, set character-mode
to 'utf-8 or 'latin-1, depending on which encoding
the driver uses.
See ODBC Status for notes on specific ODBC drivers and
recommendations for connection options.
If use-place is true, the actual connection is created in
a distinct place for database connections and a
proxy is returned.
If the connection cannot be made, an exception is raised.
| (odbc-driver-connect | | | | | connection-string | | | | [ | #:notice-handler notice-handler | | | | | #:strict-parameter-types? strict-parameter-types? | | | | | #:character-mode character-mode | | | | | #:use-place use-place]) | |
|
| → connection? |
| connection-string : string? |
|
| strict-parameter-types? : boolean? = #f |
| character-mode : (or/c 'wchar 'utf-8 'latin-1) = 'wchar |
| use-place : boolean? = #f |
Creates a connection using an ODBC connection string containing a
sequence of keyword and value connection parameters. The syntax of
connection strings is described in
SQLDriverConnect
(see Comments section); supported attributes depend on the
driver. The other arguments are the same as in
odbc-connect.
If the connection cannot be made, an exception is raised.
Returns a list of known ODBC Data Sources. Each data souce is
represented by a list of two strings; the first string is the name
of the data source, and the second is the name of its associated
driver.
Returns a list of known ODBC Drivers. Each driver is represented by
a list, the first element of which is the name of the driver. The
contents of the rest of each entry is currently undefined.
2.2 Connection Pooling
Creating a database connection can be a costly operation; it may
involve steps such as process creation and SSL negotiation. A
connection pool helps reduce connection costs by reusing
connections.
Creates a
connection pool. The pool consists of up to
max-connections, divided between leased connections and up to
max-idle-connections idle connections. The pool uses
connect to create new connections when needed; the
connect function must return a fresh connection each time it
is called.
See also virtual-connection for a mechanism that eliminates
the need to explicitly call connection-pool-lease and
disconnect.
Returns #t if x is a connection pool, #f
otherwise.
Obtains a connection from the connection pool, using an existing idle
connection in pool if one is available. If no idle connection
is available and the pool contains fewer than its maximum allowed
connections, a new connection is created; otherwise an exception is
raised.
Calling disconnect on the connection obtained causes the
connection to be released back to the connection pool. The connection
is also released if release becomes available, if it is a
synchronizable event, or if release is shutdown, if it is a
custodian. The default for release is the current thread, so
the resulting connection is released when the thread that requested it
terminates.
When a connection is released, it is kept as an idle connection if
pool’s idle connection limit would not be exceeded;
otherwise, it is disconnected. In either case, if the connection is in
a transaction, the transaction is rolled back.
2.3 Virtual Connections
A virtual connection creates actual connections on demand and
automatically releases them when they are no longer needed.
Creates a
virtual connection that creates actual connections on
demand using the
connect function, or by calling
(connection-pool-lease connect) if
connect is a
connection pool. A virtual connection encapsulates a mapping
of threads to actual connections. When a query function is called with
a virtual connection, the current thread’s associated actual
connection is used to execute the query. If there is no actual
connection associated with the current thread, one is obtained by
calling
connect. An actual connection is disconnected when
its associated thread dies.
Virtual connections are especially useful in contexts such as web
servlets, where each request is handled in a fresh thread. A single
global virtual connection can be defined, freeing each servlet request
from explicitly opening and closing its own connections. In
particular, a virtual connection backed by a connection
pool combines convenience with efficiency:
The resulting virtual connection leases a connection from the pool on
demand for each servlet request thread and releases it when the thread
terminates (that is, when the request has been handled).
When given a connection produced by virtual-connection,
connected? indicates whether there is an actual connection
associated with the current thread. Likewise, disconnect
causes the current actual connection associated with the thread (if
there is one) to be disconnected, but the connection will be recreated
if a query function is executed.
Connections produced by virtual-connection may not be used
with the prepare function. However, they may still be used to
execute parameterized queries expressed as strings or encapsulated via
virtual-statement.
2.4 Kill-safe Connections
Creates a proxy for connection
c. All queries performed
through the proxy are kill-safe; that is, if a thread is killed during
a call to a query function such as
query, the connection will
not become locked or damaged. (Connections are normally thread-safe but
not kill-safe.)
Note: A kill-safe connection whose underlying connection uses ports to
communicate with a database server is not protected from a custodian
shutting down its ports.
2.5 Data Source Names
A DSN (data source name) is a symbol associated with a connection
specification in a DSN file. They are inspired by, but distinct from,
ODBC’s DSNs.
Represents a data source. The
connector field determines
which connection function is used to create the connection. The
args field is a partial list of arguments passed to the
connection function; additional arguments may be added when
dsn-connect is called. The
extensions field
contains additional information about a connection; for example,
this library’s testing framework uses it to store SQL dialect
flags.
Data sources can also be created using the
postgresql-data-source, etc auxiliary functions.
Makes a connection using the connection information associated with
dsn in dsn-file. The given args and
kw-args are added to those specified by dsn to
form the complete arguments supplied to the connect function.
If dsn-file does not exist, or if it contains no entry
for dsn, an exception is raised. If dsn is a
data-source, then dsn-file is ignored.
A parameter holding the location of the default DSN file. The
initial value is a file located immediately within
(find-system-path 'prefs-dir).
Returns the
data-source associated with
dsn in
dsn-file.
If dsn-file does not exist, an exception is raised. If
dsn-file does not have an entry for dsn,
default is called if it is a function or returned
otherwise.
Associates dsn with the given data source ds in
dsn-file, replacing the previous association, if one
exists.
| (postgresql-data-source | | | | [ | #:user user | | | | | #:database database | | | | | #:server server | | | | | #:port port | | | | | #:socket socket | | | | | #:password password | | | | | #:allow-cleartext-password? allow-cleartext-password? | | | | | #:ssl ssl | | | | | #:notice-handler notice-handler | | | | | #:notification-handler notification-handler]) | |
| | → data-source? | | user : string? = absent | | database : string? = absent | | server : string? = absent | | port : exact-positive-integer? = absent | | socket : (or/c path-string? 'guess #f) = absent | | password : (or/c string? #f) = absent | | allow-cleartext-password? : boolean? = absent | | ssl : (or/c 'yes 'optional 'no) = absent | | notice-handler : (or/c 'output 'error) = absent | | notification-handler : (or/c 'output 'error) = absent |
|
|
| (sqlite3-data-source | [ | #:database database | | | | | #:mode mode | | | | | #:busy-retry-limit busy-retry-limit | | | | | #:busy-retry-delay busy-retry-delay | | | | | #:use-place use-place]) | |
| | → data-source? | | database : (or/c path-string? 'memory 'temporary) = absent | | mode : (or/c 'read-only 'read/write 'create) = absent | | | | use-place : boolean? = absent |
|
| (odbc-data-source | | | | [ | #:dsn dsn | | | | | #:database database | | | | | #:user user | | | | | #:password password | | | | | #:notice-handler notice-handler | | | | | #:strict-parameter-types? strict-parameter-types? | | | | | #:character-mode character-mode]) | |
| | → data-source? | | dsn : (or/c string? #f) = absent | | database : (or/c string? #f) = absent | | user : (or/c string? #f) = absent | | password : (or/c string? #f) = absent | | notice-handler : (or/c 'output 'error) = absent | | strict-parameter-types? : boolean? = absent | | character-mode : (or/c 'wchar 'utf-8 'latin-1) = absent |
|
2.6 Mangaging Connections
Returns #t if x is a connection, #f otherwise.
Closes the connection.
Returns #t if connection is connected, #f
otherwise.
Gets an object encapsulating information about the database system of
connection.
Predicate for objects representing database systems.
Returns a symbol that identifies the database system. Currently one of the
following:
'postgresql
'mysql
'sqlite3
'odbc
Returns a list of symbols identifying types supported by the database
system. See
SQL Type Conversions.
2.7 System-specific Modules
The db module exports all of the
functions listed in this manual except those described in
Utilities. The database system-specific connection modules are
loaded lazily to avoid unnecessary dependencies on foreign libraries.
The following modules provide subsets of the bindings described in
this manual.
Provides all generic connection operations (those described in
Mangaging Connections and Queries) and SQL data
support (SQL Types and Conversions).
Provides only postgresql-connect and
postgresql-guess-socket-path.
Provides only mysql-connect and
mysql-guess-socket-path.
Provides only sqlite3-connect. In contrast to
db, this module immediately attempts to
load the SQLite native library when required, and it raises an
exception if it cannot be found.
Provides only odbc-connect, odbc-driver-connect,
odbc-data-sources, and odbc-drivers. In contrast to
db, this module immediately attempts to
load the ODBC native library when required, and it raises an exception
if it cannot be found.