QITAB MySQL Native Driver
A MySQL client that uses the native network protocol to communicate with a MySQL server.
Like many libraries in Common Lisp,
QMyND is available
in Quicklisp. To load the driver
(ql:quickload 'qmynd) in the REPL.
QMyND is rather undocumented. The best resources for learning how it
works are its source code and the included tests.
There are two ways to connect to a MySQL database: the network socket and
the local socket. The second type of connection is possible only in case
of implementations supporting
AF_LOCAL sockets (at the time of
writing: CCL, ECL and SBCL) with the function
our example we will use the first type, but the usage is very much similar
in both cases.
All communication with the database is done through an opaque structure representing connection. The following invocation is self-explanatory:
> (defparameter *connection* (qmynd:mysql-connect :host "localhost" :username "jack" :password "daniel" :database "mydb")) *CONNECTION*
Driver supports both queries and prepared statements. To issue a simple
query we use the
> (qmynd:mysql-query *connection* "CREATE TABLE super_table (id INT, name VARCHAR(255), timestamp DATETIME)") #S(QMYND:RESPONSE-OK-PACKET :AFFECTED-ROWS 0 :LAST-INSERT-ID 0 :STATUS-FLAGS 2 :WARNINGS 0 :INFO "") > (qmynd:response-ok-packet-status-flags *) 2
If the query doesn't return rows, the function returns the
response-ok-packet structure that may be read with standard
accessors, which were automatically created for the structure as
depicted in the snippet above.
For efficiency and security the user may use a prepared statement. First we need to prepare it.
> (defparameter *st-insert* (qmynd:mysql-statement-prepare *connection* "INSERT INTO super_table VALUES (?, ?, NOW())")) *ST-INSERT*
Parameters to the statement are passed with the key argument
which needs to be a sequence (be it a vector or a list):
> (qmynd:mysql-statement-execute *st-insert* :parameters #(1 "foo")) #S(QMYND:RESPONSE-OK-PACKET :AFFECTED-ROWS 1 :LAST-INSERT-ID 0 :STATUS-FLAGS 2 :WARNINGS 0 :INFO "") > (dotimes (v 10) (qmynd:mysql-statement-execute *st-insert* :parameters (vector v "foo")) (sleep 1)) NIL
When we are done with the statement we should close it to release the server-side resources:
(qmynd:mysql-statement-close *st-insert*) ; no value
When we query database for rows, we receive two values: the resulting rows
sequence (try the key parameter
result-type to customize its type) and a
vector of column descriptors. The second value is not very interesting to
us because it is part of the internal implementation.
> (let ((results (qmynd:mysql-query *connection* "SELECT * FROM super_table LIMIT 3" :result-type 'list))) results) ((1 "foo" #<MYSQL-DATE-TIME 2017-08-30 14:47:07.000000>) (0 "foo" #<MYSQL-DATE-TIME 2017-08-30 14:49:17.000000>) (1 "foo" #<MYSQL-DATE-TIME 2017-08-30 14:49:18.000000>))
To process rows one-by-one with a function and discard them without
consing the result, the key parameter
row-fn should be used. To coerce
all columns to text, set
> (qmynd:mysql-query *connection* "SELECT * FROM super_table LIMIT 3" :as-text t :row-fn (lambda (row) (print row)) :result-type 'list) ("1" "foo" "2017-08-30 14:47:07") ("0" "foo" "2017-08-30 14:49:17") ("1" "foo" "2017-08-30 14:49:18") NIL
Note that only the returned value above is NIL; the preceding lists are printed statements appearing in the console.
interval types are represented as CLOS objects on
which some methods are defined.
> (qmynd:universal-time-to-mysql-date-time (get-universal-time)) #<MYSQL-DATE-TIME 2017-08-30 13:24:26.000000> > (qmynd:mysql-date-time-year *) 2017 > (qmynd:seconds-to-mysql-time-interval 100) #<MYSQL-TIME-INTERVAL 0:01:40.000000> > (cons (qmynd:mysql-time-interval-seconds *) (qmynd:mysql-time-interval-minutes *)) (40 . 1)
When we are done with working on our database, we may close the connection:
> (qmynd:mysql-disconnect *connection*) NIL
This short tutorial is meant to get your feet wet with using MySQL. For more sophisticated usage you should read the source code and docstrings.
This is a free software published under an MIT-like license (see
LICENSE). The project is hosted on GitHub and is developed by
individuals scattered around the world like many other FOSS projects.
To contribute you have to fork the repository located
and issue a pull request with your commits. Ideas for improvements
may be found in a
TODO file and issue entries on tracker hosted
with the project. Writing documentation and tests is also a useful
task which benefits the project's usefulness and stability.