2 <!DOCTYPE book PUBLIC "-//OASIS//DTD DocBook XML V4.2//EN"
3 "http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd" [
4 <!ENTITY % myents SYSTEM "entities.inc">
8 <appendix id="appendix">
9 <title>Database Back-ends</title>
11 <sect1 id="postgresql">
12 <title>PostgreSQL</title>
14 <title>Libraries</title>
15 <para>The PostgreSQL back-end requires the PostgreSQL C
16 client library (<filename>libpq.so</filename>). The
17 location of this library is specified via
18 <symbol>*postgresql-so-load-path*</symbol>, which defaults
19 to <filename>/usr/lib/libpq.so</filename>. Additional flags
20 to <application>ld</application> needed for linking are
21 specified via <symbol>*postgresql-so-libraries*</symbol>,
22 which defaults to <symbol>("-lcrypt" "-lc")</symbol>.</para>
25 <title>Initialization</title>
28 (asdf:operate 'asdf:load-op 'clsql-postgresql)
30 to load the PostgreSQL back-end. The database type for the
31 PostgreSQL back-end is <symbol>:postgresql</symbol>.</para>
34 <title>Connection Specification</title>
36 <title>Syntax of connection-spec</title>
38 (<replaceable>host</replaceable> <replaceable>db</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable> &optional <replaceable>port</replaceable> <replaceable>options</replaceable> <replaceable>tty</replaceable>)
42 <title>Description of connection-spec</title>
44 For every parameter in the connection-spec,
45 <symbol>nil</symbol> indicates that the PostgreSQL default
46 environment variables (see PostgreSQL documentation) will
47 be used, or if those are unset, the compiled-in defaults
48 of the C client library are used.
52 <term><parameter>host</parameter></term>
54 <para>String representing the hostname or IP address
55 the PostgreSQL server resides on. Use the empty
56 string to indicate a connection to localhost via
57 Unix-Domain sockets instead of TCP/IP.</para>
61 <term><parameter>db</parameter></term>
63 <para>String representing the name of the database on
64 the server to connect to.</para>
68 <term><parameter>user</parameter></term>
70 <para>String representing the user name to use for
71 authentication.</para>
75 <term><parameter>password</parameter></term>
77 <para>String representing the unencrypted password to
78 use for authentication.</para>
82 <term><parameter>port</parameter></term>
84 <para>String representing the port to use for
85 communication with the PostgreSQL server.</para>
89 <term><parameter>options</parameter></term>
91 <para>String representing further runtime options for
92 the PostgreSQL server.</para>
96 <term><parameter>tty</parameter></term>
98 <para>String representing the tty or file to use for
99 debugging messages from the PostgreSQL server.</para>
105 <sect2><title>Notes</title>
110 <sect1 id="postgresql-socket">
111 <title>PostgreSQL Socket</title>
113 <title>Libraries</title>
114 <para>The PostgreSQL Socket back-end needs
115 <emphasis>no</emphasis> access to the PostgreSQL C
116 client library, since it communicates directly with the
117 PostgreSQL server using the published frontend/backend
118 protocol, version 2.0. This eases installation and makes it
119 possible to dump CMU CL images containing CLSQL and this
120 backend, contrary to backends which require FFI code.</para>
123 <title>Initialization</title>
127 (asdf:operate 'asdf:load-op 'clsql-postgresql-socket)
129 to load the PostgreSQL Socket back-end. The database type
130 for the PostgreSQL Socket back-end is
131 <symbol>:postgresql-socket</symbol>.
135 <title>Connection Specification</title>
137 <title>Syntax of connection-spec</title>
139 (<replaceable>host</replaceable> <replaceable>db</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable> &optional <replaceable>port</replaceable> <replaceable>options</replaceable> <replaceable>tty</replaceable>)
143 <title>Description of connection-spec</title>
146 <term><parameter>host</parameter></term>
148 <para>If this is a string, it represents the hostname or
149 IP address the PostgreSQL server resides on. In
150 this case communication with the server proceeds via
151 a TCP connection to the given host and port.</para>
153 If this is a pathname, then it is assumed to name the
154 directory that contains the server's Unix-Domain
155 sockets. The full name to the socket is then
156 constructed from this and the port number passed,
157 and communication will proceed via a connection to
158 this unix-domain socket.</para>
162 <term><parameter>db</parameter></term>
164 <para>String representing the name of the database on
165 the server to connect to.</para>
169 <term><parameter>user</parameter></term>
171 <para>String representing the user name to use for
172 authentication.</para>
176 <term><parameter>password</parameter></term>
178 <para>String representing the unencrypted password to
179 use for authentication. This can be the empty
180 string if no password is required for
181 authentication.</para>
185 <term><parameter>port</parameter></term>
187 <para>Integer representing the port to use for
188 communication with the PostgreSQL server. This
189 defaults to 5432.</para>
193 <term><parameter>options</parameter></term>
195 <para>String representing further runtime options for
196 the PostgreSQL server.</para>
200 <term><parameter>tty</parameter></term>
202 <para>String representing the tty or file to use for
203 debugging messages from the PostgreSQL server.</para>
209 <sect2><title>Notes</title>
217 <title>Libraries</title>
218 <para>The &mysql; back-end requires the &mysql; C
219 client library (<filename>libmysqlclient.so</filename>).
220 The location of this library is specified
221 via <symbol>*mysql-so-load-path*</symbol>, which defaults
222 to <filename>/usr/lib/libmysqlclient.so</filename>.
223 Additional flags to <application>ld</application> needed for
224 linking are specified via <symbol>*mysql-so-libraries*</symbol>,
225 which defaults to <symbol>("-lc")</symbol>.
229 <title>Initialization</title>
233 (asdf:operate 'asdf:load-op 'clsql-mysql)
235 to load the &mysql; back-end. The database type for the MySQL
236 back-end is <symbol>:mysql</symbol>.
240 <title>Connection Specification</title>
242 <title>Syntax of connection-spec</title>
243 <synopsis>(<replaceable>host</replaceable> <replaceable>db</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable>)</synopsis>
246 <title>Description of connection-spec</title>
249 <term><parameter>host</parameter></term>
251 <para>String representing the hostname or IP address
252 the &mysql; server resides on, or <symbol>nil</symbol>
253 to indicate the localhost.</para>
257 <term><parameter>db</parameter></term>
259 <para>String representing the name of the database on
260 the server to connect to.</para>
264 <term><parameter>user</parameter></term>
266 <para>String representing the user name to use for
267 authentication, or <symbol>nil</symbol> to use the
268 current Unix user ID.</para>
272 <term><parameter>password</parameter></term>
274 <para>String representing the unencrypted password to
275 use for authentication, or <symbol>nil</symbol> if
276 the authentication record has an empty password
283 <sect2><title>Notes</title>
284 <sect3><title>FDDL</title>
285 <itemizedlist mark='opencircle'>
289 linkend="drop-index"><function>drop-index</function></link>
290 requires a table to be specified with the
291 <symbol>:on</symbol> keyword parameter.
296 <glossterm linkend="gloss-view">views</glossterm> are not
297 supported by &mysql;.
302 The <symbol>:transactions</symbol> keyword argument to
304 linkend="create-table"><function>create-table</function></link>
305 controls whether or not the created table is an InnoDB
306 table which supports transactions.
311 The <symbol>:owner</symbol> keyword argument to the FDDL functions
312 for listing and testing for database objects is ignored.
317 <sect3><title>FDML</title>
318 <itemizedlist mark='opencircle'>
321 Prior to version 4.1, &mysql; does not support nested
322 subqueries in calls to <link
323 linkend="select"><function>select</function></link>.
328 <sect3><title>Symbolic SQL Syntax</title>
329 <itemizedlist mark='opencircle'>
332 &mysql; does not support the <function>||</function>
333 concatenation operator. Use <function>concat</function>
339 &mysql; does not support the <function>substr</function>
340 operator. Use <function>substring</function> instead.
345 &mysql; does not support the
346 <function>intersect</function> and
347 <function>except</function> set operations.
352 &mysql; (version 4.0 and later) does not support string
353 table aliases unless the server is started with
363 <title>&odbc;</title>
365 <title>Libraries</title>
367 The &odbc; back-end requires access to an &odbc; driver
368 manager as well as &odbc; drivers for the underlying
369 database server. &clsql; has been tested with
370 <application>unixODBC</application> ODBC Driver Manager as
371 well as Microsoft's ODBC manager. These driver managers
372 have been tested with the <ulink
373 url="http://odbc.postgresql.org">
374 <citetitle>psqlODBC</citetitle></ulink> driver for
375 &postgresql; and the <ulink
376 url="http://www.mysql.com/products/connector/odbc/">
377 <citetitle>MyODBC</citetitle></ulink> driver for &mysql;.
381 <title>Initialization</title>
385 (asdf:operate 'asdf:load-op 'clsql-odbc)
387 to load the &odbc; back-end. The database type for the &odbc;
388 back-end is <symbol>:odbc</symbol>.
392 <title>Connection Specification</title>
394 <title>Syntax of connection-spec</title>
395 <synopsis>(<replaceable>dsn</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable>)</synopsis>
398 <title>Description of connection-spec</title>
401 <term><parameter>dsn</parameter></term>
403 <para>String representing the ODBC data source name.</para>
407 <term><parameter>user</parameter></term>
409 <para>String representing the user name to use for
410 authentication.</para>
414 <term><parameter>password</parameter></term>
416 <para>String representing the unencrypted password to
417 use for authentication.</para>
423 <sect2><title>Notes</title>
424 <sect3><title>FDDL</title>
425 <itemizedlist mark='opencircle'>
428 The <symbol>:owner</symbol> keyword argument to the FDDL functions
429 for listing and testing for database objects is ignored.
438 <title>&aodbc;</title>
440 <title>Libraries</title> <para>The &aodbc; back-end requires
441 access to the &odbc; interface of &acl; named DBI. This
442 interface is not available in the trial version of
446 <title>Initialization</title>
451 (asdf:operate 'asdf:load-op 'clsql-aodbc)
453 to load the &aodbc; back-end. The database type for the &aodbc;
454 back-end is <symbol>:aodbc</symbol>.
458 <title>Connection Specification</title>
460 <title>Syntax of connection-spec</title>
462 (<replaceable>dsn</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable>)
466 <title>Description of connection-spec</title>
469 <term><parameter>dsn</parameter></term>
471 <para>String representing the ODBC data source name.</para>
475 <term><parameter>user</parameter></term>
477 <para>String representing the user name to use for
478 authentication.</para>
482 <term><parameter>password</parameter></term>
484 <para>String representing the unencrypted password to
485 use for authentication.</para>
491 <sect2><title>Notes</title>
499 <title>&sqlite;</title>
501 <title>Libraries</title> <para>The &sqlite; back-end requires
502 the &sqlite; shared library file. Its default file name is
503 <filename>/usr/lib/libsqlite.so</filename>.</para>
506 <title>Initialization</title>
510 (asdf:operate 'asdf:load-op 'clsql-sqlite)
512 to load the &sqlite; back-end. The database type for the &sqlite;
513 back-end is <symbol>:sqlite</symbol>.
517 <title>Connection Specification</title>
519 <title>Syntax of connection-spec</title>
520 <synopsis>(<replaceable>filename</replaceable>)</synopsis>
523 <title>Description of connection-spec</title>
526 <term><parameter>filename</parameter></term>
528 <para>String representing the filename of the &sqlite;
529 database file.</para>
535 <sect2><title>Notes</title>
536 <sect3><title>Connection</title>
537 <itemizedlist mark='opencircle'>
540 Passing <parameter>filename</parameter> a value of
541 <filename>:memory:</filename> will create a database in
542 physical memory instead of using a file on disk.
547 Some operations will be many times faster if database
548 integrity checking is disabled by setting the SYNCHRONOUS
549 flag to OFF (see the SQLITE manual for details).
554 <sect3><title>FDDL</title>
555 <itemizedlist mark='opencircle'>
558 The <symbol>:owner</symbol> keyword argument to the FDDL functions
559 for listing and testing for database objects is ignored.
564 The <symbol>:column-list</symbol> keyword argument to
566 linkend="create-view"><function>create-view</function></link>
567 is not supported by &sqlite;.
572 <sect3><title>Symbolic SQL Syntax</title>
573 <itemizedlist mark='opencircle'>
576 &sqlite; does not support the <function>all</function>,
577 <function>some</function>, <function>any</function> and
578 <function>exists</function> subquery operations.
587 <title>Oracle</title>
589 <title>Libraries</title>
590 <para>The &oracle; back-end requires the &oracle; OCI client
591 library. (<filename>libclntsh.so</filename>). The location of
592 this library is specified relative to the
593 <symbol>ORACLE_HOME</symbol> value in the operating system
598 <title>Library Versions</title>
600 &clsql; has tested sucessfully using the client library from
601 Oracle 9i and Oracle 10g server installations as well as
602 Oracle's 10g Instant Client library. For Oracle 8 and earlier
603 versions, there is vestigial support by pushing the symbol
604 <symbol>:oci7</symbol> onto <symbol>cl:*features*</symbol>
605 prior to loading the <filename>clsql-oracle</filename> &asdf;
608 (push :oci7 cl:*features*)
609 (asdf:operate 'asdf:load-op 'clsql-oracle)
614 <title>Initialization</title>
618 (asdf:operate 'asdf:load-op 'clsql-oracle)
620 to load the &oracle; back-end. The database type for the Oracle
621 back-end is <symbol>:oracle</symbol>.
625 <title>Connection Specification</title>
627 <title>Syntax of connection-spec</title>
628 <synopsis>(<replaceable>global-name</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable>)</synopsis>
631 <title>Description of connection-spec</title>
634 <term><parameter>global-name</parameter></term>
636 <para>String representing the global name of the Oracle database.
637 This is looked up through the tnsnames.ora file.</para>
641 <term><parameter>user</parameter></term>
643 <para>String representing the user name to use for
644 authentication.</para>
648 <term><parameter>password</parameter></term>
650 <para>String representing the password to
651 use for authentication..</para>
657 <sect2><title>Notes</title>
658 <sect3><title>Symbolic SQL Syntax</title>
659 <itemizedlist mark='opencircle'>
662 The <function>userenv</function> operator is &oracle; specific.
667 &oracle; does not support the <function>except</function>
668 operator. Use <function>minus</function> instead.
673 &oracle; does not support the <function>all</function>,
674 <function>some</function>, <function>any</function>
680 <sect3><title>Transactions</title>
681 <itemizedlist mark='opencircle'>
684 By default, &clsql; starts in transaction AUTOCOMMIT mode
686 linkend="set-autocommit"><function>set-autocommit</function></link>).
687 To begin a transaction in autocommit mode, <link
688 linkend="start-transaction"><function>start-transaction</function></link>
689 has to be called explicitly.