Allegro MySQL: a new interface from Allegro CL to databases (added 2/4/03)

MySQL is a powerful, efficient, production ready open-source database that runs on all popular platforms. There are language bindings for most popular languages. The document doc/mysql.htm describes the bindings for the Allegro Common Lisp language. The Allegro MySQL module was provided as a test release in the fall of 2002. It is now available to all Allegro Enterprise customers. The Allegro MySQL module will be downloaded when you run sys:update-allegro to download patches and updates for Allegro CL 6.2.

The Allegro CL 6.2 documentation has been updated to include documentation for Allegro MySQL. Again, doc/mysql.htm links to the description on the website. The Support/Documentation page has information on downloading an updated copy of the documentation to install in your Allegro CL 6.2 distribution.

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:

Using ODBC:

  1. ODBC offers a portable framework for accessing varying databases. Once connected to a database you direct the database via SQL commands in the database's native format. Thus if you want portability between database vendors you still have to be careful not to use SQL commands that are not portable.
  2. ODBC imposes its own model of what a database is and this may not be a good match to a particular database. Thus you may lose efficiency when interacting with certain databases.
  3. Since ODBC was started by Microsoft, ODBC is available for free on Windows and well supported by database vendors. On Unix, ODBC drivers are generally not database vendor written, can be expensive (although one could use ODBC with MySQL using totally free libraries.)
  4. Using ODBC requires using ODBC driver manager libraries which are usually written in C. When a command is sent to a database the C library sits and waits for a response to be returned. While it is waiting Lisp is blocked from running.

Using Allegro MySQL Direct Connect:

  1. This library connects directly to the MySQL server and speaks the same protocol used by all clients in all languages that work with the MySQL server. Therefore there is no more efficient way to programmatically access a MySQL database.
  2. When data is returned by the MySQL server Lisp has access to the network buffers in which the data arrives. Lisp can convert the data into the desired final form immediately without allocating space for intermediate forms of the data needed in a multi-level MySQL interface.
  3. Each database has important and unique features and MySQL is no exception. MySQL has the notion of an autoincrement column that's ideal for automatically creating a unique number to represent a row in a table. Inserting a new row and retrieving the unique id assigned is a complex process using ODBC. With this library however the response to the insert command includes the unique id value, thus it's trivial to use this feature.
  4. Because normal Lisp socket I/O is used to communicate with the MySQL server, only one Lisp thread will block while awaiting a response from the server.
  5. The downside of using this library is that the code will only work with MySQL. However, the code looks very similar to Allegro ODBC so if there were a need to port to Allegro ODBC it wouldn't be too difficult (depending on how many unique features of MySQL (like autoincrement columns) were used.

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) (once the module has been downloaded).

Some MySQL examples

These examples are from section MySQL examples section of doc/mysql.htm.

These examples show you how to construct sql statements to insert data in a table and to retrieve data from tables. Note that the MySQL module has macros that will help you build these sql expressions and will result in more readable code.

Look at insert-db for a easier way to insert data in a table and at with-db-rows for an easier way to retrieve data from tables.

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 :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): 

MySQL interface examples

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 number 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): 
Copyright © 2023 Franz Inc., All Rights Reserved | Privacy Statement Twitter