Source Browser ?

1% language=us runpath=texruns:manuals/sql
2
3% author    : Hans Hagen
4% copyright : ConTeXt Development Team
5% license   : Creative Commons Attribution ShareAlike 4.0 International
6% reference : pragma-ade.nl | contextgarden.net | texlive (related) distributions
7% origin    : the ConTeXt distribution
8%
9% comment   : Because this manual is distributed with TeX distributions it comes with a rather
10%             liberal license. We try to adapt these documents to upgrades in the (sub)systems
11%             that they describe. Using parts of the content otherwise can therefore conflict
12%             with existing functionality and we cannot be held responsible for that. Many of
13%             the manuals contain characteristic graphics and personal notes or examples that
14%             make no sense when used out-of-context.
15%
16% comment   : Some chapters might have been published in TugBoat, the NTG Maps, the ConTeXt
17%             Group journal or otherwise. Thanks to the editors for corrections. Also thanks
18%             to users for testing, feedback and corrections.
19
20\usemodule[art-01,abr-02]
21
22\definecolor
23  [maincolor]
24  [r=.4]
25
26\setupbodyfont
27  [10pt]
28
29\setuptype
30  [color=maincolor]
31
32\setuptyping
33  [color=maincolor]
34
35\definefont
36  [TitlePageFont]
37  [file:lmmonolt10-bold.otf]
38
39\setuphead
40  [color=maincolor]
41
42\usesymbols
43  [cc]
44
45\setupinteraction
46  [hidden]
47
48\startdocument
49  [metadata:author=Hans Hagen,
50   metadata:title=SQL in ConTeXt,
51   author=Hans Hagen,
52   affiliation=PRAGMA ADE,
53   location=Hasselt NL,
54   title=SQL in \CONTEXT,
55   support=www.contextgarden.net,
56   website=www.pragma-ade.nl]
57
58\startMPpage
59
60    StartPage ;
61
62    numeric w ; w := bbwidth(Page) ;
63    numeric h ; h := bbheight(Page) ;
64
65    fill Page withcolor \MPcolor{maincolor} ;
66
67    draw textext.urt("\TitlePageFont Q")       xysized (1.1   w,0.9 h) shifted (-.05w,.05h) withcolor .20white ;
68    draw textext.top("\TitlePageFont SQL")     xysized (0.4725w,0.13h) shifted (.675w,.24w) withcolor .60white ;
69    draw textext.top("\TitlePageFont CONTEXT") xsized  (0.6   w)       shifted (.675w,.10w) withcolor .60white ;
70
71    StopPage ;
72
73\stopMPpage
74
75\startsubject[title=Contents]
76
77\placelist[section][alternative=a]
78
79\stopsubject
80
81\startsection[title=Introduction]
82
83Although \CONTEXT\ is a likely candidate for typesetting content that comes from
84databases it was only in 2011 that I ran into a project where a connection was
85needed. After all, much document related typesetting happens on files or
86dedicated storage systems.
87
88Because we run most projects in an infrastructure suitable for \TEX, it made
89sense to add some helper scripts to the \CONTEXT\ core distribution that deal
90with getting data from (in our case) \MYSQL\ databases. That way we can use the
91already stable infrastructure for installing and updating files that comes with
92\CONTEXT.
93
94As \LUA\ support is nicely integrated in \CONTEXT, and as dealing with
95information from databases involves some kind of programming anyway, there is (at
96least currently) no \TEX\ interface. The examples shown here work in \CONTEXT,
97but you need to keep in mind that \LUA\ scripts can also use this interface.
98
99{\em Although this code is under construction the interfaces are unlikely to
100change, if only because we use it in production.}
101
102\stopsection
103
104\startsection[title=Presets]
105
106In order to consult a database you need to provide credentials. You also need
107to reach the database server, either by using some client program or via a
108library. More about that later.
109
110Because we don't want to key in all that information again and again, we will
111collect it in a table. This also permits us to store it in a file and load it
112on demand. For instance:
113
114\starttyping
115local presets = {
116    database = "test",
117    username = "root",
118    password = "none",
119    host     = "localhost",
120    port     = 3306,
121}
122\stoptyping
123
124You can put a table in a file \type {presets.lua} like this:
125
126\starttyping
127return {
128    database = "test",
129    username = "root",
130    password = "none",
131    host     = "localhost",
132    port     = 3306,
133}
134\stoptyping
135
136and then load it as follows:
137
138\starttyping
139local presets = table.load("presets.lua")
140\stoptyping
141
142A \type {sqlite} database has a much simpler preset. The default suffix of the
143file is \type {db}. The other fields are just ignored.
144
145\starttyping
146return {
147    database = "test",
148}
149\stoptyping
150
151If you really want, you can use some library to open a connection, execute a
152query, collect results and close the connection, but here we use just one
153function that does it all. The presets are used to access the database and the
154same presets will be used more often it makes sense to keep a connection open as
155long as possible. That way you can execute much more queries per second,
156something that makes sense when there are many small ones, as in web related
157services. A connection is made persistent when the presets have an \type {id}
158key, like
159
160\starttyping
161presets.id = "myproject"
162\stoptyping
163
164\stopsection
165
166\startsection[title=Templates]
167
168A query often looks like this:
169
170\starttyping
171SELECT
172    `artist`, `title`
173FROM
174    `cd`
175WHERE
176    `artist` = 'archive' ;
177\stoptyping
178
179However, often you want to use the same query for multiple lookups, in which case
180you can do this:
181
182\starttyping
183SELECT
184    `artist`, `title`
185FROM
186    `cd`
187WHERE
188    `artist` = '%artist%' ;
189\stoptyping
190
191In the next section we will see how \type {%artist%} can be replaced by a more
192meaningful value. You can a percent sign by entering two in a row: \type {%%}.
193
194As with any programming language that deals with strings natively, you need a
195way to escape the characters that fence the string. In \SQL\ a field name is
196fenced by \type {``} and a string by \type {''}. Field names can often be
197used without \type {``} but you can better play safe.
198
199\starttyping
200`artist` = 'Chilly Gonzales'
201\stoptyping
202
203Escaping of the \type {'} is simple:
204
205\starttyping
206`artist` = 'Jasper van''t Hof'
207\stoptyping
208
209When you use templates you often pass a string as variable and you don't want to
210be bothered with escaping them. In the previous example we used:
211
212\starttyping
213`artist` = '%artist%'
214\stoptyping
215
216When you expect embedded quotes you can use this:
217
218\starttyping
219`artist` = '%[artist]%'
220\stoptyping
221
222In this case the variable {artist} will be escaped. When we reuse a template we
223store it in a variable:
224
225\starttyping
226local template = [[
227    SELECT
228        `artist`, `title`
229    FROM
230        `cd`
231    WHERE
232        `artist` = '%artist%' ;
233]]
234\stoptyping
235
236\stopsection
237
238\startsection[title=Queries]
239
240In order to execute a query you need to pass the previously discussed presets
241as well as the query itself.
242
243\starttyping
244local data, keys = utilities.sql.execute {
245    presets   = presets,
246    template  = template,
247    variables = {
248        artist = "Porcupine Tree",
249    },
250}
251\stoptyping
252
253The variables in the presets table can also be passed at the outer
254level. In fact there are three levels of inheritance: settings, presets
255and module defaults.
256
257\starttabulate
258\NC presets        \NC a table with values \NC \NR
259\NC template       \NC a query string \NC \NR
260\NC templatefile   \NC a file containing a template \NC \NR
261\NC \em resultfile \NC a (temporary) file to store the result \NC \NR
262\NC \em queryfile  \NC a (temporary) file to store a query \NC \NR
263\NC variables      \NC variables that are subsituted in the template \NC \NR
264\NC username       \NC used to connect to the database \NC \NR
265\NC password       \NC used to connect to the database \NC \NR
266\NC host           \NC the \quote {machine} where the database server runs on \NC \NR
267\NC port           \NC the port where the database server listens to \NC \NR
268\NC database       \NC the name of the database \NC \NR
269\stoptabulate
270
271The \type {resultfile} and \type {queryfile} parameters are used when a client
272approach is used. When a library is used all happens in memory.
273
274When the query succeeds two tables are returned: \type {data} and \type {keys}. The
275first is an indexed table where each entry is a hash. So, if we have only one
276match and that match has only one field, you get something like this:
277
278\starttyping
279data = {
280    {
281        key = "value"
282    }
283}
284
285keys = {
286    "key"
287}
288\stoptyping
289
290\stopsection
291
292\startsection[title=Converters]
293
294All values in the result are strings. Of course we could have provided some
295automatic type conversion but there are more basetypes in \MYSQL\ and some are
296not even standard \SQL. Instead the module provides a converter mechanism
297
298\starttyping
299local converter = utilities.sql.makeconverter {
300    { name = "id",      type = "number" },
301    { name = "name",    type = "string" },
302    { name = "enabled", type = "boolean" },
303}
304\stoptyping
305
306You can pass the converter to the execute function:
307
308\starttyping
309local data, keys = utilities.sql.execute {
310    presets   = presets,
311    template  = template,
312    converter = converter,
313    variables = {
314        name = "Hans Hagen",
315    },
316}
317\stoptyping
318
319In addition to numbers, strings and booleans you can also use a function
320or table:
321
322\starttyping
323local remap = {
324    ["1"] = "info"
325    ["2"] = "warning"
326    ["3"] = "debug"
327    ["4"] = "error"
328}
329
330local converter = utilities.sql.makeconverter {
331    { name = "id",     type = "number" },
332    { name = "status", type = remap },
333}
334\stoptyping
335
336I use this module for managing \CONTEXT\ jobs in web services. In that case we
337need to store jobtickets and they have some common properties. The definition of
338the table looks as follows: \footnote {The tickets manager is part of the
339\CONTEXT\ distribution.}
340
341\starttyping
342CREATE TABLE IF NOT EXISTS %basename% (
343    `id`        int(11)     NOT NULL AUTO_INCREMENT,
344    `token`     varchar(50) NOT NULL,
345    `subtoken`  INT(11)     NOT NULL,
346    `created`   int(11)     NOT NULL,
347    `accessed`  int(11)     NOT NULL,
348    `category`  int(11)     NOT NULL,
349    `status`    int(11)     NOT NULL,
350    `usertoken` varchar(50) NOT NULL,
351    `data`      longtext    NOT NULL,
352    `comment`   longtext    NOT NULL,
353
354    PRIMARY KEY                     (`id`),
355    UNIQUE INDEX `id_unique_index`  (`id` ASC),
356    KEY          `token_unique_key` (`token`)
357)
358DEFAULT CHARSET = utf8 ;
359\stoptyping
360
361We can register a ticket from (for instance) a web service and use an independent
362watchdog to consult the database for tickets that need to be processed. When the
363job is finished we register this in the database and the web service can poll for
364the status.
365
366It's easy to imagine more fields, for instance the way \CONTEXT\ is called, what
367files to use, what results to expect, what extra data to pass, like style
368directives, etc. Instead of putting that kind of information in fields we store
369them in a \LUA\ table, serialize that table, and put that in the data field.
370
371The other way around is that we take this data field and convert it back to \LUA.
372For this you can use a helper:
373
374\starttyping
375local results = utilities.sql.execute { ... }
376
377for i=1,#results do
378    local result = results[i]
379    result.data = utilities.sql.deserialize(result.data)
380end
381\stoptyping
382
383Much more efficient is to use a converter:
384
385\starttyping
386local converter = utilities.sql.makeconverter {
387    ...
388    { name = "data", type = "deserialize" },
389    ...
390}
391\stoptyping
392
393This way you don't need to loop over the result and deserialize each data
394field which not only takes less runtime (often neglectable) but also takes
395less (intermediate) memory. Of course in some cases it can make sense to
396postpone the deserialization.
397
398A variant is not to store a serialized data table, but to store a key|-|value
399list, like:
400
401\starttyping
402data = [[key_1="value_1" key_2="value_2"]]
403\stoptyping
404
405Such data fields can be converted with:
406
407\starttyping
408local converter = utilities.sql.makeconverter {
409    ...
410    { name = "data", type = utilities.parsers.keq_to_hash },
411    ...
412}
413\stoptyping
414
415You can imagine more converters like this, and if needed you can use them to
416preprocess data as well.
417
418\starttabulate[|Tl|p|]
419\NC "boolean"     \NC This converts a string into the value \type {true} or \type {false}.
420                      Valid values for \type {true} are: \type {1}, \type {true}, \type
421                      {yes}, \type {on} and \type {t} \NC \NR
422\NC "number"      \NC This one does a straightforward \type {tonumber} on the value. \NC \NR
423\NC function      \NC The given function is applied to value. \NC \NR
424\NC table         \NC The value is resolved via the given table. \NC \NR
425\NC "deserialize" \NC The value is deserialized into \LUA\ code. \NC \NR
426\NC "key"         \NC The value is used as key which makes the result table is now hashed
427                      instead of indexed. \NC \NR
428\NC "entry"       \NC An entry is added with the given name and optionally with a default
429                      value. \NC \NR
430\stoptabulate
431
432\stopsection
433
434\startsection[title=Typesetting]
435
436For good reason a \CONTEXT\ job often involves multiple passes. Although the
437database related code is quite efficient it can be considered a waste of time
438and bandwidth to fetch the data several times. For this reason there is
439another function:
440
441\starttyping
442local data, keys = utilities.sql.prepare {
443    tag = "table-1",
444    ...
445}
446
447-- do something useful with the result
448
449local data, keys = utilities.sql.prepare {
450    tag = "table-2",
451    ...
452}
453
454-- do something useful with the result
455\stoptyping
456
457The \type {prepare} alternative stores the result in a file and reuses
458it in successive runs.
459
460\stopsection
461
462\startsection[title=Methods]
463
464Currently we have several methods for accessing a database:
465
466\starttabulate
467\NC client  \NC use the command line tool, pass arguments and use files \NC \NR
468\NC library \NC use the standard library (somewhat tricky in \LUATEX\ as we need to work around bugs) \NC \NR
469\NC lmxsql  \NC use the library with a \LUA\ based pseudo client (stay in the \LUA\ domain) \NC \NR
470\NC swiglib \NC use the (still experimental) library that comes with \LUATEX \NC \NR
471\stoptabulate
472
473All methods use the same interface (\type {execute}) and hide the dirty details
474for the user. All return the data and keys tables and all take care of the proper
475escaping and parsing.
476
477\stopsection
478
479\startsection[title=Helpers]
480
481There are some helper functions and extra modules that will be described when
482they are stable.
483
484There is an \quote {extra} option to the \type {context} command that can be used
485to produce an overview of a database. You can get more information about this
486with the command:
487
488\starttyping
489context --extra=sql-tables --help
490\stoptyping
491
492\stopsection
493
494\startsection[title=Example]
495
496The distribution has a few examples, for instance a logger. The following code
497shows a bit of this (we assume that \SQLITE\ is installed):
498
499\startbuffer
500require("util-sql")
501utilities.sql.setmethod("sqlite")
502require("util-sql-loggers")
503
504local loggers = utilities.sql.loggers
505
506local presets = {
507 -- method    = "sqlite",
508    database  = "loggertest",
509    datatable = "loggers",
510    id        = "loggers",
511}
512
513os.remove("loggertest.db") -- start fresh
514
515local db = loggers.createdb(presets)
516
517loggers.save(db, { -- variant 1: data subtable
518    type   = "error",
519    action = "process",
520    data   = { filename = "test-1", message = "whatever a" }
521} )
522
523loggers.save(db, { -- variant 2: flat table
524    type     = "warning",
525    action   = "process",
526    filename = "test-2",
527    message  = "whatever b"
528} )
529
530local result = loggers.collect(db, {
531    start = {
532        day   = 1,
533        month = 1,
534        year  = 2016,
535    },
536    stop = {
537        day   = 31,
538        month = 12,
539        year  = 2116,
540    },
541    limit  = 1000000,
542 -- type   = "error",
543    action = "process"
544})
545
546context.starttabulate { "||||||" }
547for i=1,#result do
548    local r = result[i]
549    context.NC() context(r.time)
550    context.NC() context(r.type)
551    context.NC() context(r.action)
552    if r.data then
553        context.NC() context(r.data.filename)
554        context.NC() context(r.data.message)
555    else
556        context.NC()
557        context.NC()
558    end
559    context.NC() context.NR()
560end
561context.stoptabulate()
562
563-- local result = loggers.cleanup(db, {
564--     before = {
565--         day   = 1,
566--         month = 1,
567--         year  = 2117,
568--     },
569-- })
570\stopbuffer
571
572\typebuffer
573
574In this example we typeset the (small) table):
575
576\ctxluabuffer
577
578\stopsection
579
580\startsection[title=Colofon]
581
582\starttabulate[|B|p|]
583\NC author    \NC \documentvariable{author}, \documentvariable{affiliation}, \documentvariable{location} \NC \NR
584\NC version   \NC \currentdate \NC \NR
585\NC website   \NC \documentvariable{website} \endash\ \documentvariable{support} \NC \NR
586\NC copyright \NC \symbol[cc][cc-by-sa] \NC \NR
587\stoptabulate
588
589\stopsection
590
591\stopdocument
592