X-Git-Url: http://git.kpe.io/?p=clsql.git;a=blobdiff_plain;f=doc%2Fappendix.xml;h=1fdbed0961cc581db0e248cdca7f3a995e1221e3;hp=4c77eaad0dc24548136eb0b6c3847f5426a78e6b;hb=18e34efea688a6758b6e997401fbc3f241da98f3;hpb=eefedf3f7e0ace05e4c9724567f87dc88a3f1c2e diff --git a/doc/appendix.xml b/doc/appendix.xml index 4c77eaa..1fdbed0 100644 --- a/doc/appendix.xml +++ b/doc/appendix.xml @@ -1,87 +1,59 @@ + %myents; ]> Database Back-ends - - - MySQL + + + How CLSQL finds and loads foreign libraries + + For some database types CLSQL has to load external foreign + libaries. These are usually searched for in the standard + locations the operating system uses but you can tell &clsql; to + look into other directories as well by using the function + CLSQL:PUSH-LIBRARY-PATH or by directly + manipulating the special variable + CLSQL:*FOREIGN-LIBRARY-SEARCH-PATHS*. If, + say, the shared library libpq.so needed for PostgreSQL support + is located in the directory /opt/foo/ on + your machine you'd use + + (clsql:push-library-path "/opt/foo/") + + before loading the CLSQL-POSTGRESQL module. (Note the trailing + slash above!) + + If you want to combine this with fully automatic loading of + libraries via ASDF a technique like the following works: + + + (defmethod asdf:perform :after ((o asdf:load-op) + (c (eql (asdf:find-system 'clsql)))) + (funcall (find-symbol (symbol-name '#:push-library-path) + (find-package 'clsql)) + #p"/opt/foo/")) + + + + + Additionally, site-specific initialization can be done using an +initialization file. If the file /etc/clsql-init.lisp +exists, this file will be read after the &clsql; ASDF system is loaded. +This file can contain forms to set site-specific paths as well as change +&clsql; default values. + + + + PostgreSQL Libraries - The MySQL back-end needs access to the MySQL C - client library (libmysqlclient.so). - The location of this library is specified - via *mysql-so-load-path*, which defaults - to /usr/lib/libmysqlclient.so. - Additional flags to ld needed for - linking are specified via *mysql-so-libraries*, - which defaults to ("-lc"). - - - - Initialization - Use - (mk:load-system :clsql-mysql) - to load the MySQL back-end. The database type for the MySQL - back-end is :mysql. - - - Connection Specification - - Syntax of connection-spec - (host db user password) - - - Description of connection-spec - - - host - - String representing the hostname or IP address - the MySQL server resides on, or nil - to indicate the localhost. - - - - db - - String representing the name of the database on - the server to connect to. - - - - user - - String representing the user name to use for - authentication, or nil to use the - current Unix user ID. - - - - password - - String representing the unencrypted password to - use for authentication, or nil if - the authentication record has an empty password - field. - - - - - - - - - PostgreSQL - - Libraries - The PostgreSQL back-end needs access to the PostgreSQL C - client library (libpq.so). The - location of this library is specified via + The PostgreSQL back-end requires the PostgreSQL C + client library (libpq.so). The + location of this library is specified via *postgresql-so-load-path*, which defaults to /usr/lib/libpq.so. Additional flags to ld needed for linking are @@ -91,7 +63,9 @@ Initialization Use - (mk:load-system :clsql-postgresql) + +(asdf:operate 'asdf:load-op 'clsql-postgresql) + to load the PostgreSQL back-end. The database type for the PostgreSQL back-end is :postgresql. @@ -99,15 +73,19 @@ Connection Specification Syntax of connection-spec - (host db user password &optional port options tty) + + (host db user password &optional port options tty) + Description of connection-spec - For every parameter in the connection-spec, + + For every parameter in the connection-spec, nil indicates that the PostgreSQL default environment variables (see PostgreSQL documentation) will be used, or if those are unset, the compiled-in defaults - of the C client library are used. + of the C client library are used. + host @@ -163,6 +141,9 @@ + Notes + None. + @@ -179,17 +160,23 @@ Initialization - Use - (mk:load-system :clsql-postgresql-socket) - to load the PostgreSQL Socket back-end. The database type for the - PostgreSQL Socket back-end is - :postgresql-socket. + + Use + +(asdf:operate 'asdf:load-op 'clsql-postgresql-socket) + + to load the PostgreSQL Socket back-end. The database type + for the PostgreSQL Socket back-end is + :postgresql-socket. + Connection Specification Syntax of connection-spec - (host db user password &optional port options tty) + + (host db user password &optional port options tty) + Description of connection-spec @@ -258,21 +245,194 @@ + Notes + None. + - - AODBC + + MySQL + + Libraries + The &mysql; back-end requires the &mysql; C + client library (libmysqlclient.so). + The location of this library is specified + via *mysql-so-load-path*, which defaults + to /usr/lib/libmysqlclient.so. + Additional flags to ld needed for + linking are specified via *mysql-so-libraries*, + which defaults to ("-lc"). + + + + Initialization + + Use + +(asdf:operate 'asdf:load-op 'clsql-mysql) + + to load the &mysql; back-end. The database type for the MySQL + back-end is :mysql. + + - Libraries - The AODBC back-end requires access to the ODBC interface - of &acl;. + Connection Specification + + Syntax of connection-spec + (host db user password &optional port) + + + Description of connection-spec + + + host + + String representing the hostname or IP address + the &mysql; server resides on, or nil + to indicate the localhost. + + + + db + + String representing the name of the database on + the server to connect to. + + + + user + + String representing the user name to use for + authentication, or nil to use the + current Unix user ID. + + + + password + + String representing the unencrypted password to + use for authentication, or nil if + the authentication record has an empty password + field. + + + + port + + String representing the port to use for + communication with the MySQL server. + + + + + + Notes + FDDL + + + + drop-index + requires a table to be specified with the + :on keyword parameter. + + + + + views are not + supported by &mysql;. + + + + + The :transactions keyword argument to + create-table + controls whether or not the created table is an InnoDB + table which supports transactions. + + + + + The :owner keyword argument to the FDDL functions + for listing and testing for database objects is ignored. + + + + + FDML + + + + Prior to version 4.1, &mysql; does not support nested + subqueries in calls to select. + + + + + Symbolic SQL Syntax + + + + &mysql; does not support the || + concatenation operator. Use concat + instead. + + + + + &mysql; does not support the substr + operator. Use substring instead. + + + + + &mysql; does not support the + intersect and + except set operations. + + + + + &mysql; (version 4.0 and later) does not support string + table aliases unless the server is started with + ANSI_QUOTES enabled. + + + + + + + + + &odbc; + + Libraries + + The &odbc; back-end requires access to an &odbc; driver + manager as well as &odbc; drivers for the underlying + database server. &clsql; has been tested with + unixODBC ODBC Driver Manager as + well as Microsoft's ODBC manager. These driver managers + have been tested with the + psqlODBC driver for + &postgresql; and the + MyODBC driver for &mysql;. + Initialization - Use - (mk:load-system :clsql-aodbc) - to load the MySQL back-end. The database type for the AODBC - back-end is :aodbc. + + Use + +(asdf:operate 'asdf:load-op 'clsql-odbc) + + to load the &odbc; back-end. The database type for the &odbc; + back-end is :odbc. + Connection Specification @@ -306,6 +466,390 @@ + Notes + FDDL + + + + The :owner keyword argument to the FDDL functions + for listing and testing for database objects is ignored. + + + + + + + + + &aodbc; + + Libraries The &aodbc; back-end requires + access to the &odbc; interface of &acl; named DBI. This + interface is not available in the trial version of + &acl; + + + Initialization + + Use + +(require 'aodbc-v2) +(asdf:operate 'asdf:load-op 'clsql-aodbc) + + to load the &aodbc; back-end. The database type for the &aodbc; + back-end is :aodbc. + + + + Connection Specification + + Syntax of connection-spec + + (dsn user password) + + + + Description of connection-spec + + + dsn + + String representing the ODBC data source name. + + + + user + + String representing the user name to use for + authentication. + + + + password + + String representing the unencrypted password to + use for authentication. + + + + + + Notes + + None. + + + + + + &sqlite; + + Libraries The &sqlite; back-end requires + the &sqlite; shared library file. Its default file name is + /usr/lib/libsqlite.so. + + + Initialization + + Use + +(asdf:operate 'asdf:load-op 'clsql-sqlite) + + to load the &sqlite; back-end. The database type for the &sqlite; + back-end is :sqlite. + + + + Connection Specification + + Syntax of connection-spec + (filename) + + + Description of connection-spec + + + filename + + String representing the filename of the &sqlite; + database file. + + + + + + Notes + Connection + + + + Passing filename a value of + :memory: will create a database in + physical memory instead of using a file on disk. + + + + + Some operations will be many times faster if database + integrity checking is disabled by setting the SYNCHRONOUS + flag to OFF (see the SQLITE manual for details). + + + + + FDDL + + + + The :owner keyword argument to the FDDL functions + for listing and testing for database objects is ignored. + + + + + The :column-list keyword argument to + create-view + is not supported by &sqlite;. + + + + + Symbolic SQL Syntax + + + + &sqlite; does not support the all, + some, any and + exists subquery operations. + + + + + + + + + &sqlite3; + + Libraries The &sqlite3; back-end requires + the &sqlite3; shared library file. Its default file name is + /usr/lib/libsqlite3.so. + + + Initialization + + Use + +(asdf:operate 'asdf:load-op 'clsql-sqlite3) + + to load the &sqlite3; back-end. The database type for the &sqlite3; + back-end is :sqlite3. + + + + Connection Specification + + Syntax of connection-spec + (filename &optional init-function) + + + Description of connection-spec + + + filename + + String representing the filename of the &sqlite3; + database file. + + + + init-function + + + A function designator. + init-function takes a + single argument of type + sqlite3:sqlite3-db, a foreign pointer to + the C descriptor of the newly opened database. + init-function is called by + the back-end immediately after &sqlite3; + sqlite3_open library function, + and can be used to perform optional database + initializations by calling foreign functions in the + &sqlite3; library. + + + An example of an initialization function which + defines a new collating sequence for text columns is + provided in + ./examples/sqlite3/init-func/. + + + + + + + Notes + Connection + + + + Passing filename a value of + :memory: will create a database in + physical memory instead of using a file on disk. + + + + + Some operations will be many times faster if database + integrity checking is disabled by setting the SYNCHRONOUS + flag to OFF (see the SQLITE manual for details). + + + + + FDDL + + + + The :owner keyword argument to the FDDL functions + for listing and testing for database objects is ignored. + + + + + The :column-list keyword argument to + create-view + is not supported by &sqlite3;. + + + + + Symbolic SQL Syntax + + + + &sqlite3; does not support the all, + some, any and + exists subquery operations. + + + + + + + + + Oracle + + Libraries + The &oracle; back-end requires the &oracle; OCI client + library. (libclntsh.so). The location of + this library is specified relative to the + ORACLE_HOME value in the operating system + environment. + + + + Library Versions + + &clsql; has tested sucessfully using the client library from + Oracle 9i and Oracle 10g server installations as well as + Oracle's 10g Instant Client library. For Oracle 8 and earlier + versions, there is vestigial support by pushing the symbol + :oci7 onto cl:*features* + prior to loading the clsql-oracle &asdf; + system. + + (push :oci7 cl:*features*) + (asdf:operate 'asdf:load-op 'clsql-oracle) + + + + + Initialization + + Use + +(asdf:operate 'asdf:load-op 'clsql-oracle) + + to load the &oracle; back-end. The database type for the Oracle + back-end is :oracle. + + + + Connection Specification + + Syntax of connection-spec + (global-name user password) + + + Description of connection-spec + + + global-name + + String representing the global name of the Oracle database. + This is looked up through the tnsnames.ora file. + + + + user + + String representing the user name to use for + authentication. + + + + password + + String representing the password to + use for authentication.. + + + + + + Notes + Symbolic SQL Syntax + + + + The userenv operator is &oracle; specific. + + + + + &oracle; does not support the except + operator. Use minus instead. + + + + + &oracle; does not support the all, + some, any + subquery operations. + + + + + Transactions + + + + By default, &clsql; starts in transaction AUTOCOMMIT mode + (see set-autocommit). + To begin a transaction in autocommit mode, start-transaction + has to be called explicitly. + + + + +