Common Lisp Package: COM.HACKINGHAT.CL-MYSQL

README:

FUNCTION

Public

CLIENT-VERSION

Returns a three part list containing the decoded client version information

CONNECT (&KEY HOST USER PASSWORD DATABASE PORT SOCKET (CLIENT-FLAG (LIST +CLIENT-COMPRESS+ +CLIENT-MULTI-STATEMENTS+ +CLIENT-MULTI-RESULTS+)) (MIN-CONNECTIONS 1) (MAX-CONNECTIONS 1))

Connect will present to MySQL sensible defaults for all the connection items. The following code will attach you to a MySQL instance running on localhost, as the current user with no password. It will automatically turn on compression between client-and-server and also enable multiple-result sets if possible. <pre><code>CL-USER> (connect)</code></pre> If unsuccesful connect will raise a <strong>CL-MYSQL-ERROR</strong>, otherwise it will place the connection into a pool, note that all connections are pool-able, a single connection is simply the special case of a pool with only one connection. The pool has two slots, <strong>min-connections</strong> and <srong>max-connections</strong>. There will always be min-connections available in the pool. If you are using all min-connections and max-connections is greater than min-connections, the pool will continue to allocate connections until max-connections are used. After this an attempt to aquire more connections from the pool should block (if your implementation supports it). When a connection is released (this is done automatically unless you explicity disable it) the connection to the server is closed if we have allocated more connections than min-connections. The last allocated pool object is placed into a special variable <strong>*last-database*</strong> which is defaulted from the higher level API functions.

DISCONNECT (&OPTIONAL (DATABASE *LAST-DATABASE*))

This method will disconnect all the connections in the pool. Note that if you attempt to use the pool again after calling this method it will re-allocate upto min-connections before servicing your request.

ESCAPE-STRING (STRING &KEY DATABASE)

Given a string, encode it appropriately. This function relies on the fact that the character set encoding was set to UTF-8 when the connection is made.

NTH-ROW (RESULT-SET-LIST N &OPTIONAL NTH-RESULT-SET)

Return the nth-row of the nth-result set.

OPTION (OPTION VALUE &KEY DATABASE)

Use this to set a client specific connection option. CL-USER> (option :opt-reconnect 1)

PING (&KEY DATABASE)

Check whether a connection is established or not. If :opt-reconnect is set and there is no connection then MySQL's C API attempts a reconnection.

QUERY (QUERY &KEY (TYPE-MAP *TYPE-MAP*) DATABASE (STORE T))

For a SELECT query or stored procedure that returns data, query will return a list of result sets. Each result set will have 1 or more sublists where the first sublist contains the column names and the remaining lists represent the rows of the result set. If the query does not return a result set (for example if the query is an INSERT, UPDATE) the return value is the number of rows affected. Because cl-mysql supports multiple-statements you can execute code like the following: <pre><code>CL-MYSQL-SYSTEM> (query "CREATE TABLE a(a INT); INSERT INTO a (a) VALUES (1); DELETE FROM a; DROP TABLE a") ((0) (1) (1) (0))</code></pre> The type-map, if set will alter the decoding into CL types. If you set this to nil it will have the effect of disabling all CL type conversions and return either character or binary data only. This might be useful for performance reasons, (cl-mysql is much faster when it doesn't need to translate types) but it also might be all you need. Consider for instance if you're displaying a lot of numerics on a web-page. If you do not need to convert the data into floats/integers before displaying them on a page then raw could be useful here too. cl-mysql attempts to convert all numeric types to their closest CL representation. For very large numerics, or numerics that have very high precision this might not be what you want. In this case you could attempt to write your own conversion routine and inject it into cl-mysql through the type-map. The currented supported conversions are as follows (MySQL type -> CL type): <ul><li><strong>DECIMAL/NUMERIC</strong> -> RATIO</li> <li><strong>INT/TINYINT/SMALLINT/MEDIUMINT/BIGINT/YEAR</strong> -> INTEGER</li> <li><strong>FLOAT/REAL/DOUBLE PRECISION</strong> -> DOUBLE-FLOAT</li> <li><strong>DATE/DATETIME/TIMESTAMP</strong> -> INTEGER (Universal time)</li> <li><strong>TIME</strong> -> INTEGER (Seconds)</li> <li><strong>CHAR/VARCHAR/TEXT/TINYTEXT/MEDIUMTEXT/LONGTEXT</strong> -> STRING</li> <li><strong>BIT/BLOB/MEDIUMBLOB/LONGBLOB/TINYBLOB/GEOMETRY</strong> -> SIMPLE-ARRAY '(UNSIGNED-BYTE 8 )</li> </ul> If :store is T query returns a list of result sets. Each result set is a list with the first element set to the data and the second elements set to the column data. Since this structure can be a little awkward to handle you can use nth-row to manipulate the structure more sanely. If :store is NIL query returns the allocated connection object. You should use next-result-set and next-row to step through the results.

SERVER-VERSION (&KEY DATABASE)

Returns a three part list containing the decoded server version information

USE (NAME &KEY DATABASE)

Equivalent to running: CL-USER> (query "USE <name>")

Undocumented

LIST-DBS (&KEY DATABASE)

LIST-FIELDS (TABLE &KEY DATABASE)

LIST-PROCESSES (&KEY DATABASE)

LIST-TABLES (&KEY DATABASE)

MACRO

Public

WITH-ROWS ((VAR-ROW QUERY-STRING &KEY (VAR-RESULT (GENSYM)) (DATABASE '*LAST-DATABASE*) (TYPE-MAP '*TYPE-MAP*)) &BODY BODY)

Takes a query-string and iterates over the result sets assigning var-row to each individual row. If you supply var-result it will hold the result set sequence number. This macro generates code that does not store the complete result so should be suitable for working with very large data sets.

GENERIC-FUNCTION

Public

Undocumented

NEXT-RESULT-SET (SELF &KEY DONT-RELEASE STORE)

NEXT-ROW (SELF &KEY (TYPE-MAP *TYPE-MAP*))

PROCESS-RESULT-SET (SELF TYPE-MAP)

SLOT-ACCESSOR

Public

Undocumented

RESULT-SET-FIELDS (OBJECT)

SETFRESULT-SET-FIELDS (NEW-VALUE OBJECT)

VARIABLE

Public

Undocumented

*TYPE-MAP*

CONSTANT

Public

Undocumented

+CLIENT-COMPRESS+

+CLIENT-FOUND-ROWS+

+CLIENT-IGNORE-SIGPIPE+

+CLIENT-IGNORE-SPACE+

+CLIENT-INTERACTIVE+

+CLIENT-LOCAL-FILES+

+CLIENT-MULTI-RESULTS+

+CLIENT-MULTI-STATEMENTS+

+CLIENT-NO-SCHEMA+

+CLIENT-REMEMBER-OPTIONS+

+CLIENT-SSL+