| Allegro CL version 10.1 Unrevised from 10.0 to 10.1. 10.0 version | ||||||||
This document contains the following sections:
1.0 Allegro MySQL introductionMySQL is a powerful, efficient, production ready open-source database that runs on all popular platforms. There are language bindings for most popular languages. This document describes the bindings for the Allegro Common Lisp language.
MySQL was written by the founders of the MySQL AB company. It supports and enhances MySQL and owns the code. They release an open source version with restrictions and will license you to use MySQL for commercial purposes. See http://www.mysql.com.
There exist ODBC drivers for MySQL. ODBC offers a method of programmatically accessing a database that's somewhat portable across very different databases.
Allegro CL users can access MySQL through the Allegro ODBC library. With this being the case one might ask why would anyone want to use the library we describe here? We'll describe the pros and cons of ODBC first:
Symbols implementing the Allegro MySQL facility are in the
dbi.mysql package. The Allegro MySQL module is the
:mysql module and can be loaded by evaluating
(require :mysql).
You can use this module with [MariaDB](https://mariadb.com/) databases. Just specify them wherever MySQL databases are called for.
To access a MySQL database you call connect to connect to the database. Then you repeatedly call sql to send commands to the database and get results back. Finally you call disconnect to disconnect from the database.
You can call connect more than once to establish multiple simultaneous connections to the database. This is useful, for example, if you want to create a pool of ready-to-use database connections for an AllegroServe web server application.
connect has an external-format keyword argument which allows you to specify the external format of the connection.
When data is returned it is converted to the appropriate format. See Section 5.0 Conversions below for details.
A single MySQL connection can only handle one query at a time (where is a query begins when an sql command is executed and ends when the last result row is returned). It is oaky to create more than one connection to a mysql database and run a query in each connection simultaneously. In this case you must be careful to pass the connection you want to use as the value of the :db keyword argument to the mysql functions.
MySQL errors have condition mysql-error or, when appropriate,
the mysql-protocol-error, a subclass
of mysql-error.
Examples of connecting to the database:
; if the server is on the same machine and is setup to
; accept anonymous connections then this is
; the simplest way to connect:
cl-user(23): (use-package :dbi.mysql)
t
cl-user(24): (connect)
#<mysql connected to localhost/3306 @ #x7199102a>
cl-user(25):
; to connect and set the current database:
cl-user(25): (connect :database "test")
#<mysql connected to localhost/3306 @ #x7199454a>
cl-user(26):
; to connect to MySQL running on machine tiger as user "jkf"
; with password "mypass" and to set the active database to "test"
cl-user(26): (connect :host "tiger" :user "jkf"
:password "mypass" :database "test")
#<mysql connected to tiger.franz.com/3306 @ #x71997bf2>
cl-user(27):
; to connect via the unix-domain socket
cl-user(27): (connect :file "/var/lib/mysql/mysql.sock")
#<mysql connected to "/var/lib/mysql/mysql.sock" @ #x7199e102>
cl-user(28):
Here we see how to connect to the MySQL server, tell it we'll be using the database "test", and then create a table in that database and populate it. Then we retrieve all rows from the table.
Note that we count on the fact that the connect function not only returns the
connection to the database but also sets the *mysql* variable to the value of that
connection. The value of *mysql* is used as the default value for
the db keyword argument, thus this last
connection is used for subsequent sql and disconnect functions. This makes it easy
to work interactively with the database
cl-user(13): (connect :user "jkf" :password "jkf" :database "test")
#<mysql connected to localhost/3306 @ #x71f4d082>
cl-user(14): (sql "create table sample(i int, j int)")
nil
cl-user(17): (sql "insert into sample values(1,2)")
nil
cl-user(18): (sql "insert into sample values(3,4)")
nil
cl-user(19): (sql "select * from sample")
((1 2) (3 4))
("i" "j")
cl-user(20): (disconnect)
nil
cl-user(21):
In this example we store the value of the connection in the variable db and use it in subsequent sql and disconnect calls. This is the preferred style when writing programs that use this interface. In a program it is best to be explicit about the database you're accessing and not depend on a global variable that some other component of your program may modify.
In this example we show how to build an sql statement at runtime using the format function. We populate the database with 100 rows using a dotimes statement.
cl-user(21): (setq db (connect :user "jkf" :password "jkf" :database "test"))
#<mysql connected to localhost/3306 @ #x71f50d72>
cl-user(22): (sql "create table samp2(val int, square int)" :db db)
nil
cl-user(23): (dotimes (i 100) (sql (format nil "insert into samp2 values(~d,~d)" i (* i i)) :db db))
nil
cl-user(24): (sql "select * from samp2" :db db)
((0 0) (1 1) (2 4) (3 9) (4 16) (5 25) (6 36) (7 49) (8 64) (9 81) ...)
("val" "square")
cl-user(25): (disconnect :db db)
nil
cl-user(26):
MySQL's version of the sql language supports a number of extensions which allow you to get information about the database itself. This information is returned as rows of data just like the data in the tables you create. Here is an example of the use of these sql extensions to find out the names of all the tables in the database and to find out the scheme of a particular table
cl-user(26): (connect :user "jkf" :password "jkf" :database "test")
#<mysql connected to localhost/3306 @ #x71f5725a>
cl-user(27): (sql "show tables")
(("bar") ("bblob") ("bigtab") ("foo") ("samp2") ("sample") ("snart") ("snob")
("testetab") ("testtab") ...)
("Tables_in_test")
cl-user(28): (sql "describe samp2")
(("val" "int(11)" "YES" "" #:null "") ("square" "int(11)" "YES" "" #:null ""))
("Field" "Type" "Null" "Key" "Default" "Extra")
cl-user(29): (disconnect)
nil
cl-user(30):
MySQL can store large binary objects (blobs) as well as numbers and strings. Here we see how to store a five element array of type (unsigned-byte 8) in the database. This is done by converting the array into a string with mysql-escape-sequence and then storing it like any other string. Note that mysql-escape-sequence must be used to escape strings as well as arrays of (unsigned-byte 8). This is because we are generating an sql statement at runtime and it is important that the MySQL parser be able to parse that sql statement. Certain characters appearing unescaped in the string would confuse the sql parser.
cl-user(30): (connect :user "jkf" :password "jkf" :database "test")
#<mysql connected to localhost/3306 @ #x71f5a4da>
cl-user(31): (setq val
(make-array 5
:element-type '(unsigned-byte 8)
:initial-contents '(10 20 30 40 50)))
#(10 20 30 40 50)
cl-user(32): (sql "create table btest(i int, bl blob)")
nil
cl-user(33): (sql (format nil "insert into btest values(1,'~a')"
(mysql-escape-sequence val)))
nil
;; even though we declared the type of the field to be blob
;; the default behavior of the sql statement is to return
;; the value as a string, and in this case the array data
;; returned as a string results in gibberish:
cl-user(34): (sql "select * from btest")
((1 "
^T^^(2"))
("i" "bl")
;; if we tell the sql function to return the field as a blob
;; then we see the correct array value returned:
cl-user(35): (sql "select * from btest" :blob t)
((1 #(10 20 30 40 50)))
("i" "bl")
cl-user(36): (disconnect)
nil
cl-user(37):
Often you would like for a row in one table to reference a row in another table. This is done using keys. If you want a row in table A to point to a row in table B then in table B you designate one column as a key column. No two rows in a table have the same value for a key column, thus a key column value can be used to find a distinct row. Thus in table B one column is designated a key column and in a column in table A you store a key column value from table B. The value in column A is called a 'foreign key' since it's a key value from some other table.
One problem you face then is how do you create a unique key value for each row in a table? MySQL makes this very easy. You designate a row to be auto_increment and every time you add a row MySQL will automatically give that column a value one higher that the last value it stored in that column. You can find out what value was stored in the auto_increment column by using the mysql-insert-id function right after the row was inserted. This saves you the trouble of doing a query to find out the key value for the last inserted row.
Here's an example of using an auto_increment column. We're setting up a contest between pairs of players. We want to record the players' first and last names and then record the pairings between the players.
We create a table usertab to hold the players names and we add a key column usernum to give each player a unique number. And we create a table pairs to record the matchups.
(defun setup-tables (db)
;; call this once to setup the tables
(sql "create table usertab(usernum int auto_increment primary key,
lastname varchar(30),
firstname varchar(30))"
:db db)
(sql "create table pairs(first int, second int)"
:db db))
(defun add-pair (db first second)
;; call this for each pair of players.
;; first and second are player names. A player name
;; is list ("lastname" "firstname")
(sql (format nil "insert into pairs values(~d,~d)"
(get-user-id db first)
(get-user-id db second))
:db db))
(defun get-user-id (db name)
;; retrieve the user identifier for the given name
;; if the user is not known, add the user to the database.
(let ((lastname (car name))
(firstname (cadr name)))
;; since we are going to check first for the user
;; and if not found, add him , we want to make sure
;; that no other process does the same thing and
;; he gets added twice. Thus we lock the table
;; while we do this two step process.
(sql "lock tables usertab write")
(with-locked-tables (db)
;; check first to see if the user is known. Note
;; we use mysql-escape-sequence to escape characters
;; in the names that could confuse the sql parser.
(let ((uid (caar
(sql
(format nil
"select usernum from usertab
where lastname = '~a' and
firstname = '~a'"
(mysql-escape-sequence lastname)
(mysql-escape-sequence firstname))
:db db))))
(if* uid
then (return-from get-user-id uid))
;; user is not known, add him.
(sql (format
nil
"insert into usertab(lastname, firstname) values('~a','~a')"
(mysql-escape-sequence lastname)
(mysql-escape-sequence firstname))
:db db)
;; return the value of usernum, the auto_increment column
(mysql-insert-id db)))))
;;; here it is in action
cl-user(54): (setq db (connect :user "jkf" :password "jkf" :database "test"))
#<mysql connected to localhost/3306 @ #x71f90552>
cl-user(55): (setup-tables db)
nil
cl-user(58): (add-pair db '("franklin" "joe") '("douglas" "mike"))
nil
cl-user(59): (add-pair db '("namath" "joe") '("montana" "joe"))
nil
;; here we refer to users we've seen before:
cl-user(60): (add-pair db '("franklin" "joe") '("montana" "joe"))
nil
;; we see that four users have been added:
cl-user(61): (sql "select * from usertab" :db db)
((1 "franklin" "joe") (2 "douglas" "mike") (3 "namath" "joe")
(4 "montana" "joe"))
("usernum" "lastname" "firstname")
;; and here are the matchups
cl-user(62): (sql "select * from pairs" :db db)
((1 2) (3 4) (1 4))
("first" "second")
cl-user(63): (disconnect :db db)
nil
cl-user(64):
The macro with-db-rows creates sql statements based on input, executes the statements, and processes the results. The description of with-db-rows has numerous examples.
All data returned by the MySQL server is in the form of characters. Even numbers like 385 are returned as a sequence of the characters #\3, #\8 and #\5. The application retrieving the data may want the data returned as a Lisp string of characters or it may want a more appropriate Lisp data object returned (e.g. the Lisp integer 385 is probably more useful to the application than the string "385").
The sql and sql-start functions send an sql command to the server. If this is a command that returns values the server will be responding with a table (that is a sequence of rows with each row comprising the same columns of data). Before the data is returned descriptions of the columns are returned. This description includes the column name and the type of data found in the column. Based on column type data, the sql function computes the conversion it will do for each column. All of the column information is stored in a list of mysql-field objects stored in the MySQL object that holds the current database connection (and accessible with mysql-fields). After the conversion is computed the rows are retrieved and the directed conversion done (unless overridden by arguments to the sql function).
If you use the sql
function you can only override the conversion process using the
keyword arguments: convert,
blob and float. If you call
sql-start instead then
when sql-start returns
the query has been sent to the database and the column information
returned. You can examine the column information and change the
conversion chosen by sql-start. Then you call get-next-row to retrieve each
row of data until it returns nil which means
no more rows are available.
At the end of this section, we describe the datatypes that might be converted and what they will be converted to. Briefly, integers, floats, and text or blobs are dataypes suitable for conversion.
The application can tell the MySQL interface whether it should convert
the data returned by the MySQL server. The
convert argument to sql and sql-start serves this purpose. The
convert argument is a list of values one for each
column to be returned. If the value for a column is true then the
column data is converted to the appropriate type. If the value is
nil then the data is returned as a string.
If there are more columns then elements in the list, no conversion is
done on the extra columns (as if the elements were nil).
convert can also be a symbol, in which case it is
treated as if it were a list with as many elements as columns, each
entry being the symbol. So if convert is t, it is treated as the list (t t t t
... t), and if convert is
:blob, it is treated as the list (:blob
:blob :blob ... :blob).
The typical values are nil (meaning convert
nothing) or t. Another meaningful value is
:blob. (Any other non-nil
non cons value is equivalent to t.) In the
remaining discussion, we assume convert is a
list.
The default value for the convert argument is
t.
The MySQL server returns the type of each column with the result set and it is this type that determines which conversions are possible on this data.
Because there are several types of columns suitable for conversion, it is somewhat complicated to make sure that each column is converted as you wish. (You may want integers converted but floats not, for example, and you probably do want blobs converted but text not, as the default string format is suitable for text but not for blobs.)
The blob and float arguments to sql and sql-start provide finer control over conversion, as described under the headings below.
Integer data in a column is converted when the corresponding element
of the convert list is non-nil.
MySQL supports single and double float values. The default conversion function for these column types will return an integer if the database returns an integer value (e.g. "385") and otherwise will return either a single-float or double-float value if the database returns a value with a fractional part (e.g. "385.44").
If the float argument is nil, no float columns are converted regardless of the
value of convert. If float
is non-nil, a float column is converted if
the element of the convert list corresponding to
the column is non-nil.
When the conversion is done to a float, as must be done for a number like "-123.12", the type of float (single or double) created is determined as follows:
float then the resulting value is a Lisp integer or
a single-float value.
double then the resulting value is a Lisp integer
or a double-float value.
decimal then the resulting value is a Lisp integer
or a double-float value. A decimal value is a fixed point number
(e.g. decimal(13,2) is a 13 digit number with two digits to the right
of the decimal point.)
MySQL supports both text and blob valued columns. The only difference between text and blob in MySQL is that text values are compared case insensitively and blob values are compared case sensitively. When a text or blob valued column is retrieved the MySQL interface is told only that the column is "text or blob", but not which one it is.
If the value is a text value then the application would typically like a Lisp string returned. If the value is a blob (e.g. a jpeg picture) then the application most likely wants an array of bytes (an (unsigned-byte 8) array in Lisp terms) returned.
The blob argument combined with convert determines how "text or blob" columns are converted. If blob is true, then if the element in the convert list corresponding to a "text or blob" column is also true, the string is converted to an array of (unsigned-byte 8) values.
If blob is nil, then
conversion of a text or blob column is still controlled by the value
of the corresponding element in the convert list:
if that value is :blob, conversion is done. If that
value is nil or any non-nil value other than :blob, no
conversion is done and contents are returned as a Lisp string.
The blob argument
defaults to nil.
Thus convert and blob together provide extra control for columns whose data is text or blobs. There are three cases:
In the first case, no conversion should be done (because all text or
blob columns are text and text is already in the correct format). Make
blob nil
make sure none of the relevant elements of the
convert list has value :blob.
In the second, assuming you want conversion at all, all text or blob
columns should be converted to (unsigned-byte 8) arrays (because all
text or blob columns are blobs and blobs need conversion). Make
blob t
and control conversion using the convert
argument.
In the third case, assuming you want conversion at all, some columns
should be converted and some not converted. If most are blobs, make
blob t
and make the value of convert a list with nil for each text column. If most columns are text,
make blob nil and make the value of
convert a list with :blob
for each blob column.
The following table summarizes what we have just said, showing whether or not conversion is done, given the value of blob and the value of the convert argument for the column in question. (Again, if convert is a symbol, it is interpreted to be a list all of whose elements are that symbol.)
| Value of convert for the column | Value of blob | Conversion performed? |
nil |
nil |
No |
nil |
t |
No |
t |
nil |
No |
t |
t |
Yes |
:blob |
nil |
Yes |
:blob |
t |
Yes |
These datatypes are converted to Lisp integers:
These datatypes are converted to Lisp integers or single-float or double-float:
These datatypes are converted to arrays of (unsigned-byte 8) if the
blob argument is true or
convert is a list whose value corresponding to a
column is :blob:
All other datatypes are not converted, they are returned as Lisp strings.
Allegro MySQL provides limited support for stored procedure calls. At
present, only stored procedures that return a single set of results
can be used. To turn on this support, you must establish a database
connection using dbi.mysql:connect that includes
dbi.mysql:CLIENT_MULTI_RESULTS in the set of flags
passed as the client-flags keyword argument.
If you require support for multiple result set query responses, please contact [email protected].
A database connection is a socket connection which is used to transmit requests to MySQL and to retrieve responses. Sending a request is not an atomic operation and retrieving a response is not an atomic operation.
Thus two separate Lisp threads using the same connection will likely overlap in their writes and reads leading to garbage being sent to the mysql server or incomplete results being read by the Lisp threads.
So there are two choices:
The programming interface contains the following operators, variables, and classes:
All are functions except with-locked-tables, which is a macro.
Copyright (c) 1998-2022, Franz Inc. Lafayette, CA., USA. All rights reserved.
This page was not revised from the 10.0 page.
Created 2019.8.20.
| Allegro CL version 10.1 Unrevised from 10.0 to 10.1. 10.0 version | ||||||||