The initialization of Common SQL involves three stages. Firstly the SQL interface is loaded. Next, the database type (actually class) to be used is initialized. Finally, Common SQL is used to connect to a database. These stages are explained in more detail in this section.
The Lisp symbols introduced in this chapter are exported from the sql package. Application packages requiring convenient access to these facilities should therefore use the sql package.
The examples in this chapter assume that the sql package is visible.
Three steps are required to initialize the SQL interface:
The remainder of this section describes how you perform these steps.
require with the name of a database interface.Currently implemented interfaces are "oracle", "mysql", "odbc", "postgresql" and "sqlite". However, not all platforms support all interfaces, see Supported driver/client libraries for each interface-platform combination for details.
The same application can use more than one interface, and needs to call require to load each interface that it uses.
Loading is done at load time. In particular, if you are building an application, loading needs to be done before calling deliver.
Every connection has a database type, which defines the functionality to use when performing operations on it. Each interface defines one or more database types that can used as the database type. The database type must be initialized, which can be done either when connecting, or by explicitly calling initialize-database-type. Initializing a database type must be done at run time, in other words you should not save an image (by save-image or deliver) with an initialized database type.
Initializing a database type typically means that the system finds the library that implements the client, loads and initializes it. (Actually, there may be several libraries.) It is possible to delay the initialization until making the connection, but it is useful to do the initialization explicitly first as this allows you to catch errors in the initialization and report them.
The variable *default-database-type* holds the value of the default type, which is used when a database type is not supplied. The first database interface that is loaded sets *default-database-type* to its default database type. Therefore in a typical setup using one interface you do not need to specify the database type.
The database types currently supported are shown in Supported database types:
The main argument to connect is a connection-spec, which is interpreted in a database type specific way. See the entry for connect for details. By default, connect uses the database type in *default-database-type*, but it can be specified explicitly by the keyword argument :database-type. If the database type was not initialized yet, connect initializes it.
The result of connect is an object which is referred to as "database object", but it is really a connection object representing a connection to the server. It is possible to have multiple database objects connected independently to the same database server.
The database object is used by all the other Common SQL interface functions. connect sets the value of *default-database* to the result each time it is called, so a call to a SQL interface function without specifying the database always acts on the last connected database. That allows simpler code when there is only one connection. When there is more than one connection, you need to pass the database object to the interface function via the keyword argument :database.
When a connection is no longer required, it should be closed by calling disconnect.
The minimal code to initialize a connection is loading the code and connecting. For example, using only Oracle:
(require "oracle")
(sql:connect "scott/tiger")
However, if you deliver an application then the require call needs to happen at load time (before calling deliver), while the connect call must happen at run time after the delivered application started. So your code should have two parts:
To get better error handling, you may want to add a call to initialize-database-type, in the startup function:
(handler-case
(sql:initialize-database-type)
(error (cc)
;; tell the user of failure to initialize Oracle
))
Note: This section applies only to Unix-like operating systems.
To use a database, LispWorks needs to load foreign libraries, which is done when initializing the database type. To find the right libraries and initialize them, there may be vendor-specific environment variable(s) that need to be set, for example ORACLE_HOME for Oracle. Typically one of these will point to a directory where the database code is installed. You may need to ensure that these variables are set properly when your application is used.
In order to override the default loading of database library code, you may set *sql-libraries*. To control messages while loading the libraries, set *sql-loading-verbose*.
Database connections can be named by passing the :name argument to connect, allowing you to have more than one connection to a given database. If this is omitted, then a unique database name is constructed from connection-spec and a counter. Connection names are compared with equalp.
To find all the database connection instances, call the function connected-databases. To retrieve the name for a connection instance, call database-name, and to find a connection instance with a given name use find-database. To print status information about the existing connections, call status.
To close a connection to a database, use disconnect.
To reestablish a connection to a database, use reconnect.
The following example assumes that the :odbc database type has been initialized as described in Initialization steps. It connects to two databases, scott and personnel, and then prints out the connected databases.
(setf *default-database-type* :odbc)
(connect "scott")
(connect "personnel" :database-type :odbc)
(print *connected-databases*)
For database-type :oracle, connection-spec conforms to the canonical form described for connect. The connection part is the string used to establish the connection. When connecting to a local server, it may be the SID, otherwise it is an alias recognized by the names server, or in the tnsnames.ora file.
To connect to Oracle via SQL*Net, connection-spec is of the form username/password@host where host is an Oracle hostname.
Common SQL uses the Oracle Call Interface internally where this is available. For Oracle version 8, Common SQL automatically uses the same API as in LispWorks 4.4. On some platforms, this can also be obtained by using database-type :oracle8. Note that the :oracle8 database type is restricted because it cannot access or manipulate LOBs and all connections must use the same character set.
For database-type :odbc or :odbc-driver, connection-spec may take the canonical form described for connect, but an additional syntax is also allowed.
connect keyword arguments :encoding, :signal-rollback-errors and :date-string-format are all ignored.
connection-spec should have one of the forms:
The general form.
For backward compatibility.
The two forms of strings are distinguished by the presence (or absence) of the '@' character. In both forms, password can be omitted along with the preceding '/'. Also, username can simply be omitted.
Note that this means that "xyz" and "@xyz" are both interpreted to give the same values (username is null, password is null, dsn is "xyz").
For database-type :mysql, connection-spec may be in the canonical form described for connect, but it may also have the extensions described in this section.
In both the string and plist forms of connection-spec described below, any part that is omitted defaults to the MySQL default:
anonymous user
No password
No default database
localhost
3306 (unless using unix-socket).
connection-spec can be a string of the form:
username/password/dbname@hostname:port
where port is a decimal number specifying the port number to use. port can be omitted along with the preceding ':'.
hostname can be omitted. If port is omitted too, the '@' can be omitted as well. If port is supplied and hostname is not supplied, then both the '@' and the ':' are required, for example:
me/my-password/my-db@:3307
hostname may also specify a unix socket name, which must start with the character '/'.
dbname may be omitted along with the preceding '/'.
password may be omitted. If dbname is also omitted, the preceding '/' can be omitted too.
connection-spec can be a plist containing (some of) the keywords :username, :password, :dbname, :hostname, :port, :connection, :unix-socket.
Each of these keywords may be omitted.
If :unix-socket is specified, then none of :hostname, :port and :connection can be specified. If :hostname is specified then :connection must not be specified. The value supplied for :hostname can be a raw hostname, or a string of the form hostname:port. If :connection is specified then it can be a string conforming to one of these patterns:
Must start with '/'
That is, the value connection supplied in a plist connection-spec is interpreted just like the part of a string connection-spec following the '@' character.
The MySQL interface to initialize, it must find the appropriate MySQL client library. The special variables *mysql-library-path*, *mysql-library-directories* and *mysql-library-sub-directories* give you control over this.
Download the 32-bit or 64-bit MySQL package to match your LispWorks image.
The downloadable packages from the MySQL web site contain only static client libraries, but LispWorks needs a dynamic library. You need to create the dynamic library, for example by using the following shell command.
To build the 32-bit dynamic library:
gcc -dynamiclib -fno-common \
-o /usr/local/mysql/lib/libmysqlclient_r.dylib \
-all_load /usr/local/mysql/lib/libmysqlclient_r.a -lz
To build the 64-bit dynamic library:
gcc -m64 -dynamiclib -fno-common \
-o /usr/local/mysql/lib/libmysqlclient_r.dylib \
-all_load /usr/local/mysql/lib/libmysqlclient_r.a -lz
This command should be executed as the root user, or some other user with write permission to the /usr/local/mysql/lib directory and assumes that MySQL was installed in /usr/local/mysql, which is the location used by the prepackaged downloads.
An alternate way to create a dynamic library is to build MySQL from its source code with the --enable-shared flag.
By default, LispWorks expects to find the library either in /usr/local/mysql/lib or on the shared library path. This can be overridden by setting the special variable *mysql-library-directories*.
By default, LispWorks expects the library to be called libmysqlclient.*.dylib and it searches for a library that matches that pattern, where * is any version number. This search can be avoided by setting *mysql-library-path* to something other than the default ("-lmysqlclient"), for example, it is possible to force LispWorks to look for version 12 by evaluating
(setq *mysql-library-path* "libmysqlclient.12")
You can also set *mysql-library-path* to a full path, which avoids the need to set *mysql-library-directories*.
If the environment variable LW_MYSQL_LIBRARY. is set, then its value is used instead of the value of *mysql-library-path*.
For database-type :postgresql, connection-spec must be either a string in the format specified by the PostgreSQL libraries or a plist.
If connection-spec is a string then it should be in the format specified by
http://www.postgresql.org/docs/9.3/static/libpq-connect.html#LIBPQ-CONNSTRING
dbname=test user=scott password=tiger host=scandium
connection-spec can be a plist containing (some of) the keywords :username (or :user), :password, :dbname, :hostname (or :host), :port, :connection. Each of these keywords may be omitted, but if :connection is specified, then :hostname and :port must not be specified.
The value supplied for :hostname can be a raw hostname or a string of the form hostname:port. The value supplied for :post can be an integer or a string naming a service.
If :connection is specified then it can be a string conforming to one of these patterns:
The values should not be escaped or quoted: LispWorks will escape and quote it as needed before passing it to the PostgreSQL library.
LispWorks sets the PostgreSQL session variable standard_conforming_strings to on to match the escaping that Common SQL uses. Note that this variable is only available in PostgreSQL 8.2 and later, so escaping will not work correctly in older versions of PostgreSQL.
For database-type :sqlite, connection-spec is used to specify the filename of the SQLite database. connection-spec must be one of the following:
Specifies the filename as is.
A plist containing :dbname filename.
If filename is a string, it specifies the filename as is. Otherwise, the value of (namestring filename) is used as the filename.
This is equivalent to ":memory:" and specifies a private, temporary in-memory database.
This is equivalent to "" and specifies a private, temporary on-disk database.
The special variable sql:*sqlite-library-path* contains the FLI shared library name for SQLite. It defaults to "-lsqlite3" on non-Windows platforms, which should work if SQLite is installed. On Windows, it defaults to "sqlite3.dll", which requires that DLL to be on the path. Note that 64-bit and 32-bit LispWorks require different DLL files.
The Common SQL SQLite interface assumes that the library is compiled with standard options and that SQLite is not configured in an unusual way. Most importantly, if the threading mode is single-thread (either because the library is compiled as single-thread, or because sqlite3_config set it to single-thread), then the Common SQL SQLite interface is not thread-safe anymore. This situation is quite unlikely to happen.
By default, the connection is opened as a UTF-8 connection (using the C function sqlite3_open_v2). The :encoding argument to connect can have the value :default, :unicode or :utf-8 which all use the default (that is, have no effect), and :utf-16 or :utf-16-native, which opens the connection using UTF-16 in the native byte order (using the C function sqlite3_open16). It is not obvious in what circumstances UTF-16 is better and it is made available only because the underlying library supports it. When opening as UTF-16, the keywords :open-mode, :threading-mode, :uri and :vfs are ignored.
The sqlite-keywords keyword argument to connect allows you to specify several parameters controlling the behavior of the connection. sqlite-keywords is a property list, providing values for the SQLite-specific keywords :open-mode, :threading-mode, :uri, :cache-mode, :vfs or :uniform-type-per-column. These keywords affect the connection as follows.
By default, the connection is opened with opening modes create and readwrite, which means that the file is created if it does not exist, and the database can be modified. The SQLite-specific keyword :open-mode in sqlite-keywords can be used to change this. The value :readonly specifies that the file must exist (so connect fails if it does not exist) and is opened for reading only (so it is not possible to modify it). The value :readwrite means that the file must exist and the database can be modified.
By default, the threading mode of the connection is "serialized" (so it is thread-safe). You can change this by the SQLite-specific keyword :threading-mode in sqlite-keywords, which can take the values :multi-thread or :serialized. When the threading mode is :multi-thread (rather than :serialized), it is not actually thread-safe, and you can access it only on one thread at a time (but it can be accessed from different threads over time). The term "multi-thread" means that you can access different connections at the same time on different threads. :multi-thread is probably more efficient, but we do not know by how much.
By default, connection-spec can be a URI filename, which is a string starting with "file:" (see https://www.sqlite.org/uri.html "URI Filenames In SQLIte" for details). Whether this is allowed is controlled by the SQLite-specific keyword :uri in sqlite-keywords, which defaults to t, and can be switched off by passing :uri nil.
By default, the connection cache mode is the system default. The SQLite-specific keyword :cache-mode in sqlite-keywords can be used to change this to either :private or :shared. See https://www.sqlite.org/sharedcache.html "SQLite Shared-cache mode" for details. :Shared mode probably improves performance if you connect multiple times to the same file.
The SQLite-specific keyword :vfs in sqlite-keywords can be used to specify the name of the VFS (Virtual File System) that is used. You need to be an expert on SQLite to know when this is useful.
The SQLite-specific keyword :uniform-type-per-column in sqlite-keywords affects the default type for fields in the results of queries. See Tables containing a uniform type per column.
See Using SQLite for other SQLite-specific features.
LispWorks User Guide and Reference Manual - 20 Sep 2017