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="foreignlibs">
12 <title>How CLSQL finds and loads foreign libraries</title>
14 For some database types CLSQL has to load external foreign
15 libaries. These are usually searched for in the standard
16 locations the operating system uses but you can tell &clsql; to
17 look into other directories as well by using the function
18 <function>CLSQL:PUSH-LIBRARY-PATH</function> or by directly
19 manipulating the special variable
20 <varname>CLSQL:*FOREIGN-LIBRARY-SEARCH-PATHS*</varname>. If,
21 say, the shared library libpq.so needed for PostgreSQL support
22 is located in the directory <filename>/opt/foo/</filename> on
23 your machine you'd use
25 (clsql:push-library-path "/opt/foo/")
27 before loading the CLSQL-POSTGRESQL module. (Note the trailing
30 If you want to combine this with fully automatic loading of
31 libraries via ASDF a technique like the following works:
34 (defmethod asdf:perform :after ((o asdf:load-op)
35 (c (eql (asdf:find-system 'clsql))))
36 (funcall (find-symbol (symbol-name '#:push-library-path)
37 (find-package 'clsql))
43 Additionally, site-specific initialization can be done using an
44 initialization file. If the file <filename>/etc/clsql-init.lisp</filename>
45 exists, this file will be read after the &clsql; ASDF system is loaded.
46 This file can contain forms to set site-specific paths as well as change
47 &clsql; default values.
50 <sect1 id="postgresql">
51 <title>PostgreSQL</title>
53 <title>Libraries</title>
54 <para>The PostgreSQL back-end requires the PostgreSQL C
55 client library (<filename>libpq.so</filename>). The
56 location of this library is specified via
57 <symbol>*postgresql-so-load-path*</symbol>, which defaults
58 to <filename>/usr/lib/libpq.so</filename>. Additional flags
59 to <application>ld</application> needed for linking are
60 specified via <symbol>*postgresql-so-libraries*</symbol>,
61 which defaults to <symbol>("-lcrypt" "-lc")</symbol>.</para>
64 <title>Initialization</title>
67 (asdf:operate 'asdf:load-op 'clsql-postgresql)
69 to load the PostgreSQL back-end. The database type for the
70 PostgreSQL back-end is <symbol>:postgresql</symbol>.</para>
73 <title>Connection Specification</title>
75 <title>Syntax of connection-spec</title>
77 (<replaceable>host</replaceable> <replaceable>db</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable> &optional <replaceable>port</replaceable> <replaceable>options</replaceable> <replaceable>tty</replaceable>)
81 <title>Description of connection-spec</title>
83 For every parameter in the connection-spec,
84 <symbol>nil</symbol> indicates that the PostgreSQL default
85 environment variables (see PostgreSQL documentation) will
86 be used, or if those are unset, the compiled-in defaults
87 of the C client library are used.
91 <term><parameter>host</parameter></term>
93 <para>String representing the hostname or IP address
94 the PostgreSQL server resides on. Use the empty
95 string to indicate a connection to localhost via
96 Unix-Domain sockets instead of TCP/IP.</para>
100 <term><parameter>db</parameter></term>
102 <para>String representing the name of the database on
103 the server to connect to.</para>
107 <term><parameter>user</parameter></term>
109 <para>String representing the user name to use for
110 authentication.</para>
114 <term><parameter>password</parameter></term>
116 <para>String representing the unencrypted password to
117 use for authentication.</para>
121 <term><parameter>port</parameter></term>
123 <para>String representing the port to use for
124 communication with the PostgreSQL server.</para>
128 <term><parameter>options</parameter></term>
130 <para>String representing further runtime options for
131 the PostgreSQL server.</para>
135 <term><parameter>tty</parameter></term>
137 <para>String representing the tty or file to use for
138 debugging messages from the PostgreSQL server.</para>
144 <sect2><title>Notes</title>
149 <sect1 id="postgresql-socket">
150 <title>PostgreSQL Socket</title>
152 <title>Libraries</title>
153 <para>The PostgreSQL Socket back-end needs
154 <emphasis>no</emphasis> access to the PostgreSQL C
155 client library, since it communicates directly with the
156 PostgreSQL server using the published frontend/backend
157 protocol, version 2.0. This eases installation and makes it
158 possible to dump CMU CL images containing CLSQL and this
159 backend, contrary to backends which require FFI code.</para>
162 <title>Initialization</title>
166 (asdf:operate 'asdf:load-op 'clsql-postgresql-socket)
168 to load the PostgreSQL Socket back-end. The database type
169 for the PostgreSQL Socket back-end is
170 <symbol>:postgresql-socket</symbol>.
174 <title>Connection Specification</title>
176 <title>Syntax of connection-spec</title>
178 (<replaceable>host</replaceable> <replaceable>db</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable> &optional <replaceable>port</replaceable> <replaceable>options</replaceable> <replaceable>tty</replaceable>)
182 <title>Description of connection-spec</title>
185 <term><parameter>host</parameter></term>
187 <para>If this is a string, it represents the hostname or
188 IP address the PostgreSQL server resides on. In
189 this case communication with the server proceeds via
190 a TCP connection to the given host and port.</para>
192 If this is a pathname, then it is assumed to name the
193 directory that contains the server's Unix-Domain
194 sockets. The full name to the socket is then
195 constructed from this and the port number passed,
196 and communication will proceed via a connection to
197 this unix-domain socket.</para>
201 <term><parameter>db</parameter></term>
203 <para>String representing the name of the database on
204 the server to connect to.</para>
208 <term><parameter>user</parameter></term>
210 <para>String representing the user name to use for
211 authentication.</para>
215 <term><parameter>password</parameter></term>
217 <para>String representing the unencrypted password to
218 use for authentication. This can be the empty
219 string if no password is required for
220 authentication.</para>
224 <term><parameter>port</parameter></term>
226 <para>Integer representing the port to use for
227 communication with the PostgreSQL server. This
228 defaults to 5432.</para>
232 <term><parameter>options</parameter></term>
234 <para>String representing further runtime options for
235 the PostgreSQL server.</para>
239 <term><parameter>tty</parameter></term>
241 <para>String representing the tty or file to use for
242 debugging messages from the PostgreSQL server.</para>
248 <sect2><title>Notes</title>
256 <title>Libraries</title>
257 <para>The &mysql; back-end requires the &mysql; C
258 client library (<filename>libmysqlclient.so</filename>).
259 The location of this library is specified
260 via <symbol>*mysql-so-load-path*</symbol>, which defaults
261 to <filename>/usr/lib/libmysqlclient.so</filename>.
262 Additional flags to <application>ld</application> needed for
263 linking are specified via <symbol>*mysql-so-libraries*</symbol>,
264 which defaults to <symbol>("-lc")</symbol>.
268 <title>Initialization</title>
272 (asdf:operate 'asdf:load-op 'clsql-mysql)
274 to load the &mysql; back-end. The database type for the MySQL
275 back-end is <symbol>:mysql</symbol>.
279 <title>Connection Specification</title>
281 <title>Syntax of connection-spec</title>
282 <synopsis>(<replaceable>host</replaceable> <replaceable>db</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable> &optional <replaceable>port</replaceable>)</synopsis>
285 <title>Description of connection-spec</title>
288 <term><parameter>host</parameter></term>
290 <para>String representing the hostname or IP address
291 the &mysql; server resides on, or <symbol>nil</symbol>
292 to indicate the localhost.</para>
296 <term><parameter>db</parameter></term>
298 <para>String representing the name of the database on
299 the server to connect to.</para>
303 <term><parameter>user</parameter></term>
305 <para>String representing the user name to use for
306 authentication, or <symbol>nil</symbol> to use the
307 current Unix user ID.</para>
311 <term><parameter>password</parameter></term>
313 <para>String representing the unencrypted password to
314 use for authentication, or <symbol>nil</symbol> if
315 the authentication record has an empty password
320 <term><parameter>port</parameter></term>
322 <para>String representing the port to use for
323 communication with the MySQL server.</para>
329 <sect2><title>Notes</title>
330 <sect3><title>FDDL</title>
331 <itemizedlist mark='opencircle'>
335 linkend="drop-index"><function>drop-index</function></link>
336 requires a table to be specified with the
337 <symbol>:on</symbol> keyword parameter.
342 <glossterm linkend="gloss-view">views</glossterm> are not
343 supported by &mysql;.
348 The <symbol>:transactions</symbol> keyword argument to
350 linkend="create-table"><function>create-table</function></link>
351 controls whether or not the created table is an InnoDB
352 table which supports transactions.
357 The <symbol>:owner</symbol> keyword argument to the FDDL functions
358 for listing and testing for database objects is ignored.
363 <sect3><title>FDML</title>
364 <itemizedlist mark='opencircle'>
367 Prior to version 4.1, &mysql; does not support nested
368 subqueries in calls to <link
369 linkend="select"><function>select</function></link>.
374 <sect3><title>Symbolic SQL Syntax</title>
375 <itemizedlist mark='opencircle'>
378 &mysql; does not support the <function>||</function>
379 concatenation operator. Use <function>concat</function>
385 &mysql; does not support the <function>substr</function>
386 operator. Use <function>substring</function> instead.
391 &mysql; does not support the
392 <function>intersect</function> and
393 <function>except</function> set operations.
398 &mysql; (version 4.0 and later) does not support string
399 table aliases unless the server is started with
409 <title>&odbc;</title>
411 <title>Libraries</title>
413 The &odbc; back-end requires access to an &odbc; driver
414 manager as well as &odbc; drivers for the underlying
415 database server. &clsql; has been tested with
416 <application>unixODBC</application> ODBC Driver Manager as
417 well as Microsoft's ODBC manager. These driver managers
418 have been tested with the <ulink
419 url="http://odbc.postgresql.org">
420 <citetitle>psqlODBC</citetitle></ulink> driver for
421 &postgresql; and the <ulink
422 url="http://www.mysql.com/products/connector/odbc/">
423 <citetitle>MyODBC</citetitle></ulink> driver for &mysql;.
427 <title>Initialization</title>
431 (asdf:operate 'asdf:load-op 'clsql-odbc)
433 to load the &odbc; back-end. The database type for the &odbc;
434 back-end is <symbol>:odbc</symbol>.
438 <title>Connection Specification</title>
440 <title>Syntax of connection-spec</title>
441 <synopsis>(<replaceable>dsn</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable> &key <replaceable>connection-string</replaceable>)</synopsis>
444 <title>Description of connection-spec</title>
447 <term><parameter>dsn</parameter></term>
449 <para>String representing the ODBC data source name.</para>
453 <term><parameter>user</parameter></term>
455 <para>String representing the user name to use for
456 authentication.</para>
460 <term><parameter>password</parameter></term>
462 <para>String representing the unencrypted password to
463 use for authentication.</para>
467 <term><parameter>connection-string</parameter></term>
469 <para>Raw connection string passed to the underlying
470 ODBC driver. Allows bypassing creating a DSN on the
478 <sect2><title>Notes</title>
479 <sect3><title>FDDL</title>
480 <itemizedlist mark='opencircle'>
483 The <symbol>:owner</symbol> keyword argument to the FDDL functions
484 for listing and testing for database objects is ignored.
490 <sect2><title>Connect Examples</title>
493 ;; assumes a "mssql" DSN is configured on the lisp host, specifying database server
494 ;; and database name.
495 > (clsql:connect '("mssql" "database-user" "database-password")
496 :database-type :odbc)
497 => #<CLSQL-ODBC:ODBC-DATABASE mssql/database-user OPEN {100756D123}>
499 ;; no DSN on the lisp host, specify connection information via :connection-string
500 > (clsql:connect '("friendly-server-name" "friendly-username" ""
501 :connection-string "DRIVER={FreeTDS};SERVER=mssql-server;DATABASE=database-name;UID=database-user;PWD=database-password;PORT=1433;TDS_Version=8.0;APP=clsql")
502 :database-type :odbc)
503 => #<CLSQL-ODBC:ODBC-DATABASE friendly-server-name/friendly-username OPEN {100756D123}></screen>
505 The <symbol>friendly-server-name</symbol>
506 and <symbol>friendly-username</symbol> are only used when
507 printing the connection object to a stream.
513 <title>&aodbc;</title>
515 <title>Libraries</title> <para>The &aodbc; back-end requires
516 access to the &odbc; interface of &acl; named DBI. This
517 interface is not available in the trial version of
521 <title>Initialization</title>
526 (asdf:operate 'asdf:load-op 'clsql-aodbc)
528 to load the &aodbc; back-end. The database type for the &aodbc;
529 back-end is <symbol>:aodbc</symbol>.
533 <title>Connection Specification</title>
535 <title>Syntax of connection-spec</title>
537 (<replaceable>dsn</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable>)
541 <title>Description of connection-spec</title>
544 <term><parameter>dsn</parameter></term>
546 <para>String representing the ODBC data source name.</para>
550 <term><parameter>user</parameter></term>
552 <para>String representing the user name to use for
553 authentication.</para>
557 <term><parameter>password</parameter></term>
559 <para>String representing the unencrypted password to
560 use for authentication.</para>
566 <sect2><title>Notes</title>
574 <title>&sqlite;</title>
576 <title>Libraries</title> <para>The &sqlite; back-end requires
577 the &sqlite; shared library file. Its default file name is
578 <filename>/usr/lib/libsqlite.so</filename>.</para>
581 <title>Initialization</title>
585 (asdf:operate 'asdf:load-op 'clsql-sqlite)
587 to load the &sqlite; back-end. The database type for the &sqlite;
588 back-end is <symbol>:sqlite</symbol>.
592 <title>Connection Specification</title>
594 <title>Syntax of connection-spec</title>
595 <synopsis>(<replaceable>filename</replaceable>)</synopsis>
598 <title>Description of connection-spec</title>
601 <term><parameter>filename</parameter></term>
603 <para>String or pathname representing the filename of
604 the &sqlite; database file.</para>
610 <sect2><title>Notes</title>
611 <sect3><title>Connection</title>
612 <itemizedlist mark='opencircle'>
615 Passing <parameter>filename</parameter> a value of
616 <filename>:memory:</filename> will create a database in
617 physical memory instead of using a file on disk.
622 Some operations will be many times faster if database
623 integrity checking is disabled by setting the SYNCHRONOUS
624 flag to OFF (see the SQLITE manual for details).
629 <sect3><title>FDDL</title>
630 <itemizedlist mark='opencircle'>
633 The <symbol>:owner</symbol> keyword argument to the FDDL functions
634 for listing and testing for database objects is ignored.
639 The <symbol>:column-list</symbol> keyword argument to
641 linkend="create-view"><function>create-view</function></link>
642 is not supported by &sqlite;.
647 <sect3><title>Symbolic SQL Syntax</title>
648 <itemizedlist mark='opencircle'>
651 &sqlite; does not support the <function>all</function>,
652 <function>some</function>, <function>any</function> and
653 <function>exists</function> subquery operations.
662 <title>&sqlite3;</title>
664 <title>Libraries</title> <para>The &sqlite3; back-end requires
665 the &sqlite3; shared library file. Its default file name is
666 <filename>/usr/lib/libsqlite3.so</filename>.</para>
669 <title>Initialization</title>
673 (asdf:operate 'asdf:load-op 'clsql-sqlite3)
675 to load the &sqlite3; back-end. The database type for the &sqlite3;
676 back-end is <symbol>:sqlite3</symbol>.
680 <title>Connection Specification</title>
682 <title>Syntax of connection-spec</title>
683 <synopsis>(<replaceable>filename</replaceable> &optional <replaceable>init-function</replaceable>)</synopsis>
686 <title>Description of connection-spec</title>
689 <term><parameter>filename</parameter></term>
691 <para>String representing the filename of the &sqlite3;
692 database file.</para>
696 <term><parameter>init-function</parameter></term>
699 A function designator.
700 <replaceable>init-function</replaceable> takes a
701 single argument of type
702 <type>sqlite3:sqlite3-db</type>, a foreign pointer to
703 the C descriptor of the newly opened database.
704 <replaceable>init-function</replaceable> is called by
705 the back-end immediately after &sqlite3;
706 <function>sqlite3_open</function> library function,
707 and can be used to perform optional database
708 initializations by calling foreign functions in the
712 An example of an initialization function which
713 defines a new collating sequence for text columns is
715 <filename>./examples/sqlite3/init-func/</filename>.
722 <sect2><title>Notes</title>
723 <sect3><title>Connection</title>
724 <itemizedlist mark='opencircle'>
727 Passing <parameter>filename</parameter> a value of
728 <filename>:memory:</filename> will create a database in
729 physical memory instead of using a file on disk.
734 Some operations will be many times faster if database
735 integrity checking is disabled by setting the SYNCHRONOUS
736 flag to OFF (see the SQLITE manual for details).
741 <sect3><title>FDDL</title>
742 <itemizedlist mark='opencircle'>
745 The <symbol>:owner</symbol> keyword argument to the FDDL functions
746 for listing and testing for database objects is ignored.
751 The <symbol>:column-list</symbol> keyword argument to
753 linkend="create-view"><function>create-view</function></link>
754 is not supported by &sqlite3;.
759 <sect3><title>Symbolic SQL Syntax</title>
760 <itemizedlist mark='opencircle'>
763 &sqlite3; does not support the <function>all</function>,
764 <function>some</function>, <function>any</function> and
765 <function>exists</function> subquery operations.
774 <title>Oracle</title>
776 <title>Libraries</title>
777 <para>The &oracle; back-end requires the &oracle; OCI client
778 library. (<filename>libclntsh.so</filename>). The location of
779 this library is specified relative to the
780 <symbol>ORACLE_HOME</symbol> value in the operating system
785 <title>Library Versions</title>
787 &clsql; has tested sucessfully using the client library from
788 Oracle 9i and Oracle 10g server installations as well as
789 Oracle's 10g Instant Client library. For Oracle 8 and earlier
790 versions, there is vestigial support by pushing the symbol
791 <symbol>:oci7</symbol> onto <symbol>cl:*features*</symbol>
792 prior to loading the <filename>clsql-oracle</filename> &asdf;
795 (push :oci7 cl:*features*)
796 (asdf:operate 'asdf:load-op 'clsql-oracle)
801 <title>Initialization</title>
805 (asdf:operate 'asdf:load-op 'clsql-oracle)
807 to load the &oracle; back-end. The database type for the Oracle
808 back-end is <symbol>:oracle</symbol>.
812 <title>Connection Specification</title>
814 <title>Syntax of connection-spec</title>
815 <synopsis>(<replaceable>global-name</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable>)</synopsis>
818 <title>Description of connection-spec</title>
821 <term><parameter>global-name</parameter></term>
823 <para>String representing the global name of the Oracle database.
824 This is looked up through the tnsnames.ora file.</para>
828 <term><parameter>user</parameter></term>
830 <para>String representing the user name to use for
831 authentication.</para>
835 <term><parameter>password</parameter></term>
837 <para>String representing the password to
838 use for authentication..</para>
844 <sect2><title>Notes</title>
845 <sect3><title>Symbolic SQL Syntax</title>
846 <itemizedlist mark='opencircle'>
849 The <function>userenv</function> operator is &oracle; specific.
854 &oracle; does not support the <function>except</function>
855 operator. Use <function>minus</function> instead.
860 &oracle; does not support the <function>all</function>,
861 <function>some</function>, <function>any</function>
867 <sect3><title>Transactions</title>
868 <itemizedlist mark='opencircle'>
871 By default, &clsql; starts in transaction AUTOCOMMIT mode
873 linkend="set-autocommit"><function>set-autocommit</function></link>).
874 To begin a transaction in autocommit mode, <link
875 linkend="start-transaction"><function>start-transaction</function></link>
876 has to be called explicitly.