doc/appendix.xml

   1 <?xml version='1.0' ?>
   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">
   5 %myents;
   6 ]>
   7
   8 <appendix id="appendix">
   9   <title>Database Back-ends</title>
  10
  11   <sect1 id="foreignlibs">
  12     <title>How CLSQL finds and loads foreign  libraries</title>
  13     <para>
  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
  24       <screen>
  25         (clsql:push-library-path "/opt/foo/")
  26       </screen>
  27   before loading the CLSQL-POSTGRESQL module.  (Note the trailing
  28   slash above!)
  29
  30   If you want to combine this with fully automatic loading of
  31   libraries via ASDF a technique like the following works:
  32
  33   <screen>
  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))
  38                #p"/opt/foo/"))
  39   </screen>
  40     </para>
  41
  42     <para>
  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.
  48     </para>
  49   </sect1>
  50   <sect1 id="postgresql">
  51     <title>PostgreSQL</title>
  52     <sect2>
  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>
  62       </sect2>
  63       <sect2>
  64         <title>Initialization</title>
  65         <para>Use
  66           <screen>
  67 (asdf:operate 'asdf:load-op 'clsql-postgresql)
  68         </screen>
  69           to load the PostgreSQL back-end.  The database type for the
  70           PostgreSQL back-end is <symbol>:postgresql</symbol>.</para>
  71       </sect2>
  72       <sect2>
  73         <title>Connection Specification</title>
  74         <sect3>
  75           <title>Syntax of connection-spec</title>
  76           <synopsis>
  77             (<replaceable>host</replaceable> <replaceable>db</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable> &amp;optional <replaceable>port</replaceable> <replaceable>options</replaceable> <replaceable>tty</replaceable>)
  78           </synopsis>
  79         </sect3>
  80         <sect3>
  81           <title>Description of connection-spec</title>
  82           <para>
  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.
  88           </para>
  89           <variablelist>
  90             <varlistentry>
  91               <term><parameter>host</parameter></term>
  92               <listitem>
  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>
  97               </listitem>
  98             </varlistentry>
  99             <varlistentry>
 100               <term><parameter>db</parameter></term>
 101               <listitem>
 102                 <para>String representing the name of the database on
 103                   the server to connect to.</para>
 104               </listitem>
 105             </varlistentry>
 106             <varlistentry>
 107               <term><parameter>user</parameter></term>
 108               <listitem>
 109                 <para>String representing the user name to use for
 110                   authentication.</para>
 111               </listitem>
 112             </varlistentry>
 113             <varlistentry>
 114               <term><parameter>password</parameter></term>
 115               <listitem>
 116                 <para>String representing the unencrypted password to
 117                   use for authentication.</para>
 118               </listitem>
 119             </varlistentry>
 120             <varlistentry>
 121               <term><parameter>port</parameter></term>
 122               <listitem>
 123                 <para>String representing the port to use for
 124                   communication with the PostgreSQL server.</para>
 125               </listitem>
 126             </varlistentry>
 127             <varlistentry>
 128               <term><parameter>options</parameter></term>
 129               <listitem>
 130                 <para>String representing further runtime options for
 131                   the PostgreSQL server.</para>
 132               </listitem>
 133             </varlistentry>
 134             <varlistentry>
 135               <term><parameter>tty</parameter></term>
 136               <listitem>
 137                 <para>String representing the tty or file to use for
 138                   debugging messages from the PostgreSQL server.</para>
 139               </listitem>
 140             </varlistentry>
 141           </variablelist>
 142         </sect3>
 143       </sect2>
 144       <sect2><title>Notes</title>
 145       <para>None.</para>
 146       </sect2>
 147     </sect1>
 148
 149     <sect1 id="postgresql-socket">
 150       <title>PostgreSQL Socket</title>
 151       <sect2>
 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>
 160       </sect2>
 161       <sect2>
 162         <title>Initialization</title>
 163         <para>
 164           Use
 165           <screen>
 166 (asdf:operate 'asdf:load-op 'clsql-postgresql-socket)
 167         </screen>
 168           to load the PostgreSQL Socket back-end.  The database type
 169           for the PostgreSQL Socket back-end is
 170           <symbol>:postgresql-socket</symbol>.
 171         </para>
 172       </sect2>
 173       <sect2>
 174         <title>Connection Specification</title>
 175         <sect3>
 176           <title>Syntax of connection-spec</title>
 177           <synopsis>
 178             (<replaceable>host</replaceable> <replaceable>db</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable> &amp;optional <replaceable>port</replaceable> <replaceable>options</replaceable> <replaceable>tty</replaceable>)
 179           </synopsis>
 180         </sect3>
 181         <sect3>
 182           <title>Description of connection-spec</title>
 183           <variablelist>
 184             <varlistentry>
 185               <term><parameter>host</parameter></term>
 186               <listitem>
 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>
 191                 <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>
 198               </listitem>
 199             </varlistentry>
 200             <varlistentry>
 201               <term><parameter>db</parameter></term>
 202               <listitem>
 203                 <para>String representing the name of the database on
 204                   the server to connect to.</para>
 205               </listitem>
 206             </varlistentry>
 207             <varlistentry>
 208               <term><parameter>user</parameter></term>
 209               <listitem>
 210                 <para>String representing the user name to use for
 211                   authentication.</para>
 212               </listitem>
 213             </varlistentry>
 214             <varlistentry>
 215               <term><parameter>password</parameter></term>
 216               <listitem>
 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>
 221               </listitem>
 222             </varlistentry>
 223             <varlistentry>
 224               <term><parameter>port</parameter></term>
 225               <listitem>
 226                 <para>Integer representing the port to use for
 227                   communication with the PostgreSQL server.  This
 228                   defaults to 5432.</para>
 229               </listitem>
 230             </varlistentry>
 231             <varlistentry>
 232               <term><parameter>options</parameter></term>
 233               <listitem>
 234                 <para>String representing further runtime options for
 235                   the PostgreSQL server.</para>
 236               </listitem>
 237             </varlistentry>
 238             <varlistentry>
 239               <term><parameter>tty</parameter></term>
 240               <listitem>
 241                 <para>String representing the tty or file to use for
 242                   debugging messages from the PostgreSQL server.</para>
 243               </listitem>
 244             </varlistentry>
 245           </variablelist>
 246         </sect3>
 247       </sect2>
 248       <sect2><title>Notes</title>
 249       <para>None.</para>
 250       </sect2>
 251     </sect1>
 252
 253   <sect1 id="mysql">
 254     <title>MySQL</title>
 255     <sect2>
 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>.
 265       </para>
 266     </sect2>
 267     <sect2>
 268       <title>Initialization</title>
 269       <para>
 270         Use
 271         <screen>
 272 (asdf:operate 'asdf:load-op 'clsql-mysql)
 273         </screen>
 274         to load the &mysql; back-end.  The database type for the MySQL
 275         back-end is <symbol>:mysql</symbol>.
 276       </para>
 277       </sect2>
 278       <sect2>
 279         <title>Connection Specification</title>
 280         <sect3>
 281           <title>Syntax of connection-spec</title>
 282           <synopsis>(<replaceable>host</replaceable> <replaceable>db</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable> &amp;optional <replaceable>port</replaceable>)</synopsis>
 283         </sect3>
 284         <sect3>
 285           <title>Description of connection-spec</title>
 286           <variablelist>
 287             <varlistentry>
 288               <term><parameter>host</parameter></term>
 289               <listitem>
 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>
 293               </listitem>
 294             </varlistentry>
 295             <varlistentry>
 296               <term><parameter>db</parameter></term>
 297               <listitem>
 298                 <para>String representing the name of the database on
 299                   the server to connect to.</para>
 300               </listitem>
 301             </varlistentry>
 302             <varlistentry>
 303               <term><parameter>user</parameter></term>
 304               <listitem>
 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>
 308               </listitem>
 309             </varlistentry>
 310             <varlistentry>
 311               <term><parameter>password</parameter></term>
 312               <listitem>
 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
 316                   field.</para>
 317               </listitem>
 318             </varlistentry>
 319             <varlistentry>
 320               <term><parameter>port</parameter></term>
 321               <listitem>
 322                 <para>String representing the port to use for
 323                   communication with the MySQL server.</para>
 324               </listitem>
 325             </varlistentry>
 326           </variablelist>
 327         </sect3>
 328       </sect2>
 329       <sect2><title>Notes</title>
 330       <sect3><title>FDDL</title>
 331       <itemizedlist mark='opencircle'>
 332         <listitem>
 333           <para>
 334             <link
 335             linkend="drop-index"><function>drop-index</function></link>
 336             requires a table to be specified with the
 337             <symbol>:on</symbol> keyword parameter.
 338           </para>
 339         </listitem>
 340         <listitem>
 341           <para>
 342             <glossterm linkend="gloss-view">views</glossterm> are not
 343             supported by &mysql;.
 344           </para>
 345         </listitem>
 346         <listitem>
 347           <para>
 348             The <symbol>:transactions</symbol> keyword argument to
 349             <link
 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.
 353           </para>
 354         </listitem>
 355         <listitem>
 356           <para>
 357             The <symbol>:owner</symbol> keyword argument to the FDDL functions
 358             for listing and testing for database objects is ignored.
 359           </para>
 360         </listitem>
 361       </itemizedlist>
 362       </sect3>
 363       <sect3><title>FDML</title>
 364       <itemizedlist mark='opencircle'>
 365         <listitem>
 366           <para>
 367             Prior to version 4.1, &mysql; does not support nested
 368             subqueries in calls to <link
 369             linkend="select"><function>select</function></link>.
 370           </para>
 371         </listitem>
 372       </itemizedlist>
 373       </sect3>
 374       <sect3><title>Symbolic SQL Syntax</title>
 375       <itemizedlist mark='opencircle'>
 376         <listitem>
 377           <para>
 378             &mysql; does not support the <function>||</function>
 379             concatenation operator. Use <function>concat</function>
 380             instead.
 381           </para>
 382         </listitem>
 383         <listitem>
 384           <para>
 385             &mysql; does not support the <function>substr</function>
 386             operator. Use <function>substring</function> instead.
 387           </para>
 388         </listitem>
 389         <listitem>
 390           <para>
 391             &mysql; does not support the
 392             <function>intersect</function> and
 393             <function>except</function> set operations.
 394           </para>
 395         </listitem>
 396         <listitem>
 397           <para>
 398             &mysql; (version 4.0 and later) does not support string
 399             table aliases unless the server is started with
 400             ANSI_QUOTES enabled.
 401           </para>
 402         </listitem>
 403       </itemizedlist>
 404       </sect3>
 405       </sect2>
 406     </sect1>
 407
 408     <sect1 id="odbc">
 409       <title>&odbc;</title>
 410       <sect2>
 411         <title>Libraries</title>
 412         <para>
 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;.
 424         </para>
 425       </sect2>
 426       <sect2>
 427         <title>Initialization</title>
 428         <para>
 429           Use
 430           <screen>
 431 (asdf:operate 'asdf:load-op 'clsql-odbc)
 432           </screen>
 433           to load the &odbc; back-end.  The database type for the &odbc;
 434           back-end is <symbol>:odbc</symbol>.
 435         </para>
 436       </sect2>
 437       <sect2>
 438         <title>Connection Specification</title>
 439         <sect3>
 440           <title>Syntax of connection-spec</title>
 441           <synopsis>(<replaceable>dsn</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable> &amp;key <replaceable>connection-string</replaceable>)</synopsis>
 442         </sect3>
 443         <sect3>
 444           <title>Description of connection-spec</title>
 445           <variablelist>
 446             <varlistentry>
 447               <term><parameter>dsn</parameter></term>
 448               <listitem>
 449                 <para>String representing the ODBC data source name.</para>
 450               </listitem>
 451             </varlistentry>
 452             <varlistentry>
 453               <term><parameter>user</parameter></term>
 454               <listitem>
 455                 <para>String representing the user name to use for
 456                   authentication.</para>
 457               </listitem>
 458             </varlistentry>
 459             <varlistentry>
 460               <term><parameter>password</parameter></term>
 461               <listitem>
 462                 <para>String representing the unencrypted password to
 463                   use for authentication.</para>
 464               </listitem>
 465             </varlistentry>
 466             <varlistentry>
 467               <term><parameter>connection-string</parameter></term>
 468               <listitem>
 469                 <para>Raw connection string passed to the underlying
 470                 ODBC driver. Allows bypassing creating a DSN on the
 471                 server.</para>
 472               </listitem>
 473             </varlistentry>
 474
 475           </variablelist>
 476         </sect3>
 477       </sect2>
 478       <sect2><title>Notes</title>
 479       <sect3><title>FDDL</title>
 480       <itemizedlist mark='opencircle'>
 481         <listitem>
 482           <para>
 483             The <symbol>:owner</symbol> keyword argument to the FDDL functions
 484             for listing and testing for database objects is ignored.
 485           </para>
 486         </listitem>
 487       </itemizedlist>
 488       </sect3>
 489       </sect2>
 490       <sect2><title>Connect Examples</title>
 491         <screen>
 492
 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 => #&lt;CLSQL-ODBC:ODBC-DATABASE mssql/database-user OPEN {100756D123}&gt;
 498
 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 => #&lt;CLSQL-ODBC:ODBC-DATABASE friendly-server-name/friendly-username OPEN {100756D123}&gt;</screen>
 504         <para>
 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.
 508         </para>
 509       </sect2>
 510     </sect1>
 511
 512     <sect1 id="aodbc">
 513       <title>&aodbc;</title>
 514       <sect2>
 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
 518         &acl;</para>
 519       </sect2>
 520       <sect2>
 521         <title>Initialization</title>
 522         <para>
 523           Use
 524           <screen>
 525 (require 'aodbc-v2)
 526 (asdf:operate 'asdf:load-op 'clsql-aodbc)
 527           </screen>
 528           to load the &aodbc; back-end.  The database type for the &aodbc;
 529           back-end is <symbol>:aodbc</symbol>.
 530         </para>
 531       </sect2>
 532       <sect2>
 533         <title>Connection Specification</title>
 534         <sect3>
 535           <title>Syntax of connection-spec</title>
 536           <synopsis>
 537             (<replaceable>dsn</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable>)
 538           </synopsis>
 539         </sect3>
 540         <sect3>
 541           <title>Description of connection-spec</title>
 542           <variablelist>
 543             <varlistentry>
 544               <term><parameter>dsn</parameter></term>
 545               <listitem>
 546                 <para>String representing the ODBC data source name.</para>
 547               </listitem>
 548             </varlistentry>
 549             <varlistentry>
 550               <term><parameter>user</parameter></term>
 551               <listitem>
 552                 <para>String representing the user name to use for
 553                   authentication.</para>
 554               </listitem>
 555             </varlistentry>
 556             <varlistentry>
 557               <term><parameter>password</parameter></term>
 558               <listitem>
 559                 <para>String representing the unencrypted password to
 560                   use for authentication.</para>
 561               </listitem>
 562             </varlistentry>
 563           </variablelist>
 564         </sect3>
 565       </sect2>
 566       <sect2><title>Notes</title>
 567       <para>
 568         None.
 569       </para>
 570       </sect2>
 571     </sect1>
 572
 573     <sect1 id="sqlite">
 574       <title>&sqlite;</title>
 575       <sect2>
 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>
 579       </sect2>
 580       <sect2>
 581         <title>Initialization</title>
 582         <para>
 583           Use
 584           <screen>
 585 (asdf:operate 'asdf:load-op 'clsql-sqlite)
 586           </screen>
 587           to load the &sqlite; back-end.  The database type for the &sqlite;
 588           back-end is <symbol>:sqlite</symbol>.
 589         </para>
 590       </sect2>
 591       <sect2>
 592         <title>Connection Specification</title>
 593         <sect3>
 594           <title>Syntax of connection-spec</title>
 595           <synopsis>(<replaceable>filename</replaceable>)</synopsis>
 596         </sect3>
 597         <sect3>
 598           <title>Description of connection-spec</title>
 599           <variablelist>
 600             <varlistentry>
 601               <term><parameter>filename</parameter></term>
 602               <listitem>
 603                 <para>String or pathname representing the filename of
 604                 the &sqlite; database file.</para>
 605               </listitem>
 606             </varlistentry>
 607           </variablelist>
 608         </sect3>
 609       </sect2>
 610       <sect2><title>Notes</title>
 611       <sect3><title>Connection</title>
 612       <itemizedlist mark='opencircle'>
 613         <listitem>
 614           <para>
 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.
 618           </para>
 619         </listitem>
 620         <listitem>
 621           <para>
 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).
 625           </para>
 626         </listitem>
 627       </itemizedlist>
 628       </sect3>
 629       <sect3><title>FDDL</title>
 630       <itemizedlist mark='opencircle'>
 631         <listitem>
 632           <para>
 633             The <symbol>:owner</symbol> keyword argument to the FDDL functions
 634             for listing and testing for database objects is ignored.
 635           </para>
 636         </listitem>
 637         <listitem>
 638           <para>
 639             The <symbol>:column-list</symbol> keyword argument to
 640             <link
 641             linkend="create-view"><function>create-view</function></link>
 642             is not supported by &sqlite;.
 643           </para>
 644         </listitem>
 645       </itemizedlist>
 646       </sect3>
 647       <sect3><title>Symbolic SQL Syntax</title>
 648       <itemizedlist mark='opencircle'>
 649         <listitem>
 650           <para>
 651             &sqlite; does not support the <function>all</function>,
 652             <function>some</function>, <function>any</function> and
 653             <function>exists</function> subquery operations.
 654           </para>
 655         </listitem>
 656       </itemizedlist>
 657       </sect3>
 658       </sect2>
 659     </sect1>
 660
 661     <sect1 id="sqlite3">
 662       <title>&sqlite3;</title>
 663       <sect2>
 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>
 667       </sect2>
 668       <sect2>
 669         <title>Initialization</title>
 670         <para>
 671           Use
 672           <screen>
 673 (asdf:operate 'asdf:load-op 'clsql-sqlite3)
 674           </screen>
 675           to load the &sqlite3; back-end.  The database type for the &sqlite3;
 676           back-end is <symbol>:sqlite3</symbol>.
 677         </para>
 678       </sect2>
 679       <sect2>
 680         <title>Connection Specification</title>
 681         <sect3>
 682           <title>Syntax of connection-spec</title>
 683           <synopsis>(<replaceable>filename</replaceable> &amp;optional <replaceable>init-function</replaceable>)</synopsis>
 684         </sect3>
 685         <sect3>
 686           <title>Description of connection-spec</title>
 687           <variablelist>
 688             <varlistentry>
 689               <term><parameter>filename</parameter></term>
 690               <listitem>
 691                 <para>String representing the filename of the &sqlite3;
 692                 database file.</para>
 693               </listitem>
 694             </varlistentry>
 695             <varlistentry>
 696               <term><parameter>init-function</parameter></term>
 697               <listitem>
 698                 <para>
 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
 709                  &sqlite3; library.
 710                 </para>
 711                 <para>
 712                  An example of an initialization function which
 713                  defines a new collating sequence for text columns is
 714                  provided in
 715                  <filename>./examples/sqlite3/init-func/</filename>.
 716                 </para>
 717               </listitem>
 718             </varlistentry>
 719           </variablelist>
 720         </sect3>
 721       </sect2>
 722       <sect2><title>Notes</title>
 723       <sect3><title>Connection</title>
 724       <itemizedlist mark='opencircle'>
 725         <listitem>
 726           <para>
 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.
 730           </para>
 731         </listitem>
 732         <listitem>
 733           <para>
 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).
 737           </para>
 738         </listitem>
 739       </itemizedlist>
 740       </sect3>
 741       <sect3><title>FDDL</title>
 742       <itemizedlist mark='opencircle'>
 743         <listitem>
 744           <para>
 745             The <symbol>:owner</symbol> keyword argument to the FDDL functions
 746             for listing and testing for database objects is ignored.
 747           </para>
 748         </listitem>
 749         <listitem>
 750           <para>
 751             The <symbol>:column-list</symbol> keyword argument to
 752             <link
 753             linkend="create-view"><function>create-view</function></link>
 754             is not supported by &sqlite3;.
 755           </para>
 756         </listitem>
 757       </itemizedlist>
 758       </sect3>
 759       <sect3><title>Symbolic SQL Syntax</title>
 760       <itemizedlist mark='opencircle'>
 761         <listitem>
 762           <para>
 763             &sqlite3; does not support the <function>all</function>,
 764             <function>some</function>, <function>any</function> and
 765             <function>exists</function> subquery operations.
 766           </para>
 767         </listitem>
 768       </itemizedlist>
 769       </sect3>
 770       </sect2>
 771     </sect1>
 772
 773   <sect1 id="oracle">
 774     <title>Oracle</title>
 775     <sect2>
 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
 781       environment.
 782       </para>
 783     </sect2>
 784     <sect2>
 785       <title>Library Versions</title>
 786       <para>
 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;
 793         system.
 794         <screen>
 795           (push :oci7 cl:*features*)
 796           (asdf:operate 'asdf:load-op 'clsql-oracle)
 797         </screen>
 798       </para>
 799     </sect2>
 800     <sect2>
 801       <title>Initialization</title>
 802       <para>
 803         Use
 804         <screen>
 805 (asdf:operate 'asdf:load-op 'clsql-oracle)
 806         </screen>
 807         to load the &oracle; back-end.  The database type for the Oracle
 808         back-end is <symbol>:oracle</symbol>.
 809       </para>
 810       </sect2>
 811       <sect2>
 812         <title>Connection Specification</title>
 813         <sect3>
 814           <title>Syntax of connection-spec</title>
 815           <synopsis>(<replaceable>global-name</replaceable> <replaceable>user</replaceable> <replaceable>password</replaceable>)</synopsis>
 816         </sect3>
 817         <sect3>
 818           <title>Description of connection-spec</title>
 819           <variablelist>
 820             <varlistentry>
 821               <term><parameter>global-name</parameter></term>
 822               <listitem>
 823                 <para>String representing the global name of the Oracle database.
 824                   This is looked up through the tnsnames.ora file.</para>
 825               </listitem>
 826             </varlistentry>
 827             <varlistentry>
 828               <term><parameter>user</parameter></term>
 829               <listitem>
 830                 <para>String representing the user name to use for
 831                   authentication.</para>
 832               </listitem>
 833             </varlistentry>
 834             <varlistentry>
 835               <term><parameter>password</parameter></term>
 836               <listitem>
 837                 <para>String representing the password to
 838                   use for authentication..</para>
 839               </listitem>
 840             </varlistentry>
 841           </variablelist>
 842         </sect3>
 843       </sect2>
 844       <sect2><title>Notes</title>
 845       <sect3><title>Symbolic SQL Syntax</title>
 846       <itemizedlist mark='opencircle'>
 847         <listitem>
 848           <para>
 849             The <function>userenv</function> operator is &oracle; specific.
 850           </para>
 851         </listitem>
 852         <listitem>
 853           <para>
 854             &oracle; does not support the <function>except</function>
 855             operator. Use <function>minus</function> instead.
 856           </para>
 857         </listitem>
 858         <listitem>
 859           <para>
 860             &oracle; does not support the <function>all</function>,
 861             <function>some</function>, <function>any</function>
 862             subquery operations.
 863           </para>
 864         </listitem>
 865       </itemizedlist>
 866       </sect3>
 867       <sect3><title>Transactions</title>
 868       <itemizedlist mark='opencircle'>
 869         <listitem>
 870           <para>
 871             By default, &clsql; starts in transaction AUTOCOMMIT mode
 872             (see <link
 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.
 877           </para>
 878         </listitem>
 879       </itemizedlist>
 880       </sect3>
 881       </sect2>
 882     </sect1>
 883
 884   </appendix>