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> &optional <replaceable>port</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
281 <term><parameter>port</parameter></term>
283 <para>String representing the port to use for
284 communication with the MySQL server.</para>
290 <sect2><title>Notes</title>
291 <sect3><title>FDDL</title>
292 <itemizedlist mark='opencircle'>
296 linkend="drop-index"><function>drop-index</function></link>
297 requires a table to be specified with the
298 <symbol>:on</symbol> keyword parameter.
303 <glossterm linkend="gloss-view">views</glossterm> are not
304 supported by &mysql;.
309 The <symbol>:transactions</symbol> keyword argument to
311 linkend="create-table"><function>create-table</function></link>
312 controls whether or not the created table is an InnoDB
313 table which supports transactions.
318 The <symbol>:owner</symbol> keyword argument to the FDDL functions
319 for listing and testing for database objects is ignored.
324 <sect3><title>FDML</title>
325 <itemizedlist mark='opencircle'>
328 Prior to version 4.1, &mysql; does not support nested
329 subqueries in calls to <link
330 linkend="select"><function>select</function></link>.
335 <sect3><title>Symbolic SQL Syntax</title>
336 <itemizedlist mark='opencircle'>
339 &mysql; does not support the <function>||</function>
340 concatenation operator. Use <function>concat</function>
346 &mysql; does not support the <function>substr</function>
347 operator. Use <function>substring</function> instead.
352 &mysql; does not support the
353 <function>intersect</function> and
354 <function>except</function> set operations.
359 &mysql; (version 4.0 and later) does not support string
360 table aliases unless the server is started with
370 <title>&odbc;</title>
372 <title>Libraries</title>
374 The &odbc; back-end requires access to an &odbc; driver
375 manager as well as &odbc; drivers for the underlying
376 database server. &clsql; has been tested with
377 <application>unixODBC</application> ODBC Driver Manager as
378 well as Microsoft's ODBC manager. These driver managers
379 have been tested with the <ulink
380 url="http://odbc.postgresql.org">
381 <citetitle>psqlODBC</citetitle></ulink> driver for
382 &postgresql; and the <ulink
383 url="http://www.mysql.com/products/connector/odbc/">
384 <citetitle>MyODBC</citetitle></ulink> driver for &mysql;.
388 <title>Initialization</title>
392 (asdf:operate 'asdf:load-op 'clsql-odbc)
394 to load the &odbc; back-end. The database type for the &odbc;
395 back-end is <symbol>:odbc</symbol>.
399 <title>Connection Specification</title>
401 <title>Syntax of connection-spec</title>
402 <synopsis>(<replaceable>dsn</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable>)</synopsis>
405 <title>Description of connection-spec</title>
408 <term><parameter>dsn</parameter></term>
410 <para>String representing the ODBC data source name.</para>
414 <term><parameter>user</parameter></term>
416 <para>String representing the user name to use for
417 authentication.</para>
421 <term><parameter>password</parameter></term>
423 <para>String representing the unencrypted password to
424 use for authentication.</para>
430 <sect2><title>Notes</title>
431 <sect3><title>FDDL</title>
432 <itemizedlist mark='opencircle'>
435 The <symbol>:owner</symbol> keyword argument to the FDDL functions
436 for listing and testing for database objects is ignored.
445 <title>&aodbc;</title>
447 <title>Libraries</title> <para>The &aodbc; back-end requires
448 access to the &odbc; interface of &acl; named DBI. This
449 interface is not available in the trial version of
453 <title>Initialization</title>
458 (asdf:operate 'asdf:load-op 'clsql-aodbc)
460 to load the &aodbc; back-end. The database type for the &aodbc;
461 back-end is <symbol>:aodbc</symbol>.
465 <title>Connection Specification</title>
467 <title>Syntax of connection-spec</title>
469 (<replaceable>dsn</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable>)
473 <title>Description of connection-spec</title>
476 <term><parameter>dsn</parameter></term>
478 <para>String representing the ODBC data source name.</para>
482 <term><parameter>user</parameter></term>
484 <para>String representing the user name to use for
485 authentication.</para>
489 <term><parameter>password</parameter></term>
491 <para>String representing the unencrypted password to
492 use for authentication.</para>
498 <sect2><title>Notes</title>
506 <title>&sqlite;</title>
508 <title>Libraries</title> <para>The &sqlite; back-end requires
509 the &sqlite; shared library file. Its default file name is
510 <filename>/usr/lib/libsqlite.so</filename>.</para>
513 <title>Initialization</title>
517 (asdf:operate 'asdf:load-op 'clsql-sqlite)
519 to load the &sqlite; back-end. The database type for the &sqlite;
520 back-end is <symbol>:sqlite</symbol>.
524 <title>Connection Specification</title>
526 <title>Syntax of connection-spec</title>
527 <synopsis>(<replaceable>filename</replaceable>)</synopsis>
530 <title>Description of connection-spec</title>
533 <term><parameter>filename</parameter></term>
535 <para>String representing the filename of the &sqlite;
536 database file.</para>
542 <sect2><title>Notes</title>
543 <sect3><title>Connection</title>
544 <itemizedlist mark='opencircle'>
547 Passing <parameter>filename</parameter> a value of
548 <filename>:memory:</filename> will create a database in
549 physical memory instead of using a file on disk.
554 Some operations will be many times faster if database
555 integrity checking is disabled by setting the SYNCHRONOUS
556 flag to OFF (see the SQLITE manual for details).
561 <sect3><title>FDDL</title>
562 <itemizedlist mark='opencircle'>
565 The <symbol>:owner</symbol> keyword argument to the FDDL functions
566 for listing and testing for database objects is ignored.
571 The <symbol>:column-list</symbol> keyword argument to
573 linkend="create-view"><function>create-view</function></link>
574 is not supported by &sqlite;.
579 <sect3><title>Symbolic SQL Syntax</title>
580 <itemizedlist mark='opencircle'>
583 &sqlite; does not support the <function>all</function>,
584 <function>some</function>, <function>any</function> and
585 <function>exists</function> subquery operations.
594 <title>&sqlite3;</title>
596 <title>Libraries</title> <para>The &sqlite3; back-end requires
597 the &sqlite3; shared library file. Its default file name is
598 <filename>/usr/lib/libsqlite3.so</filename>.</para>
601 <title>Initialization</title>
605 (asdf:operate 'asdf:load-op 'clsql-sqlite3)
607 to load the &sqlite3; back-end. The database type for the &sqlite3;
608 back-end is <symbol>:sqlite3</symbol>.
612 <title>Connection Specification</title>
614 <title>Syntax of connection-spec</title>
615 <synopsis>(<replaceable>filename</replaceable> &optional <replaceable>init-function</replaceable>)</synopsis>
618 <title>Description of connection-spec</title>
621 <term><parameter>filename</parameter></term>
623 <para>String representing the filename of the &sqlite3;
624 database file.</para>
628 <term><parameter>init-function</parameter></term>
631 A function designator.
632 <replaceable>init-function</replaceable> takes a
633 single argument of type
634 <type>sqlite3:sqlite3-db</type>, a foreign pointer to
635 the C descriptor of the newly opened database.
636 <replaceable>init-function</replaceable> is called by
637 the back-end immediately after &sqlite3;
638 <function>sqlite3_open</function> library function,
639 and can be used to perform optional database
640 initializations by calling foreign functions in the
644 An example of an initialization function which
645 defines a new collating sequence for text columns is
647 <filename>./examples/sqlite3/init-func/</filename>.
654 <sect2><title>Notes</title>
655 <sect3><title>Connection</title>
656 <itemizedlist mark='opencircle'>
659 Passing <parameter>filename</parameter> a value of
660 <filename>:memory:</filename> will create a database in
661 physical memory instead of using a file on disk.
666 Some operations will be many times faster if database
667 integrity checking is disabled by setting the SYNCHRONOUS
668 flag to OFF (see the SQLITE manual for details).
673 <sect3><title>FDDL</title>
674 <itemizedlist mark='opencircle'>
677 The <symbol>:owner</symbol> keyword argument to the FDDL functions
678 for listing and testing for database objects is ignored.
683 The <symbol>:column-list</symbol> keyword argument to
685 linkend="create-view"><function>create-view</function></link>
686 is not supported by &sqlite3;.
691 <sect3><title>Symbolic SQL Syntax</title>
692 <itemizedlist mark='opencircle'>
695 &sqlite3; does not support the <function>all</function>,
696 <function>some</function>, <function>any</function> and
697 <function>exists</function> subquery operations.
706 <title>Oracle</title>
708 <title>Libraries</title>
709 <para>The &oracle; back-end requires the &oracle; OCI client
710 library. (<filename>libclntsh.so</filename>). The location of
711 this library is specified relative to the
712 <symbol>ORACLE_HOME</symbol> value in the operating system
717 <title>Library Versions</title>
719 &clsql; has tested sucessfully using the client library from
720 Oracle 9i and Oracle 10g server installations as well as
721 Oracle's 10g Instant Client library. For Oracle 8 and earlier
722 versions, there is vestigial support by pushing the symbol
723 <symbol>:oci7</symbol> onto <symbol>cl:*features*</symbol>
724 prior to loading the <filename>clsql-oracle</filename> &asdf;
727 (push :oci7 cl:*features*)
728 (asdf:operate 'asdf:load-op 'clsql-oracle)
733 <title>Initialization</title>
737 (asdf:operate 'asdf:load-op 'clsql-oracle)
739 to load the &oracle; back-end. The database type for the Oracle
740 back-end is <symbol>:oracle</symbol>.
744 <title>Connection Specification</title>
746 <title>Syntax of connection-spec</title>
747 <synopsis>(<replaceable>global-name</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable>)</synopsis>
750 <title>Description of connection-spec</title>
753 <term><parameter>global-name</parameter></term>
755 <para>String representing the global name of the Oracle database.
756 This is looked up through the tnsnames.ora file.</para>
760 <term><parameter>user</parameter></term>
762 <para>String representing the user name to use for
763 authentication.</para>
767 <term><parameter>password</parameter></term>
769 <para>String representing the password to
770 use for authentication..</para>
776 <sect2><title>Notes</title>
777 <sect3><title>Symbolic SQL Syntax</title>
778 <itemizedlist mark='opencircle'>
781 The <function>userenv</function> operator is &oracle; specific.
786 &oracle; does not support the <function>except</function>
787 operator. Use <function>minus</function> instead.
792 &oracle; does not support the <function>all</function>,
793 <function>some</function>, <function>any</function>
799 <sect3><title>Transactions</title>
800 <itemizedlist mark='opencircle'>
803 By default, &clsql; starts in transaction AUTOCOMMIT mode
805 linkend="set-autocommit"><function>set-autocommit</function></link>).
806 To begin a transaction in autocommit mode, <link
807 linkend="start-transaction"><function>start-transaction</function></link>
808 has to be called explicitly.