File: README.md

package info (click to toggle)
cl-qmynd 20180131-1.1
  • links: PTS, VCS
  • area: main
  • in suites: bookworm, bullseye, sid
  • size: 388 kB
  • sloc: lisp: 3,985; makefile: 11
file content (176 lines) | stat: -rw-r--r-- 5,144 bytes parent folder | download | duplicates (2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176

# QITAB MySQL Native Driver

A MySQL client that uses the native network protocol to communicate with a MySQL
server.

## Installation

Like many libraries in Common Lisp, `QMyND` is available
in [Quicklisp](https://www.quicklisp.org/beta/). To load the driver
type `(ql:quickload 'qmynd)` in the REPL.

## Basic usage

`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 `mysql-local-connect`. In
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 `mysql-query` function:

```
> (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 `parameters`,
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 `as-text` to `T`:

```
> (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.

MySQL `date` and `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.

## Contributing

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
at [https://github.com/qitab/qmynd](https://github.com/qitab/qmynd)
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.

<!-- ## Architecture -->