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