SourceBrowser

util-sql.lua /size: 15 Kb last modification: 2021-10-28 13:50
1if
 not
 modules
 then
 modules
 =
 {
 }
 end
 modules
 [
'
util-sql
'
]
 =
 {
2    version
   =
 1
.
001
,
3    comment
   =
 "
companion to m-sql.mkiv
"
,
4    author
    =
 "
Hans Hagen, PRAGMA-ADE, Hasselt NL
"
,
5    copyright
 =
 "
PRAGMA ADE / ConTeXt Development Team
"
,
6    license
   =
 "
see context related readme files
"
7}
8
9-- todo: templates as table (saves splitting)
10
11-- Of course we could use a library but we don't want another depedency and there is
12-- a bit of flux in these libraries. Also, we want the data back in a way that we
13-- like.
14--
15-- This is the first of set of sql related modules that are providing functionality
16-- for a web based framework that we use for typesetting (related) services. We're
17-- talking of session management, job ticket processing, storage, (xml) file processing
18-- and dealing with data from databases (often ambitiously called database publishing).
19--
20-- There is no generic solution for such services, but from our perspective, as we use
21-- context in a regular tds tree (the standard distribution) it makes sense to put shared
22-- code in the context distribution. That way we don't need to reinvent wheels every time.
23
24-- We use the template mechanism from util-tpl which inturn is just using the dos cq
25-- windows convention of %whatever% variables that I've used for ages.
26
27-- util-sql-imp-client.lua
28-- util-sql-imp-library.lua
29-- util-sql-imp-lmxsql.lua
30
31-- local sql = require("util-sql")
32--
33-- local converter = sql.makeconverter {
34--     { name = "id",  type = "number" },
35--     { name = "data",type = "string" },
36-- }
37--
38-- local execute = sql.methods.library.execute
39-- -- local execute = sql.methods.client.execute
40-- -- local execute = sql.methods.lmxsql.execute
41--
42-- result = execute {
43--     presets = {
44--         host      = "localhost",
45--         username  = "root",
46--         password  = "test",
47--         database  = "test",
48--         id        = "test", -- forces persistent session
49--     },
50--     template  = "select * from `test` where `id` > %criterium% ;",
51--     variables = {
52--         criterium = 2,
53--     },
54--     converter = converter
55-- }
56--
57-- inspect(result)
58
59local
 format
,
 match
 =
 string
.
format
,
 string
.
match
60local
 random
 =
 math
.
random
61local
 rawset
,
 rawget
,
 setmetatable
,
 getmetatable
,
 load
,
 type
 =
 rawset
,
 rawget
,
 setmetatable
,
 getmetatable
,
 load
,
 type
62local
 P
,
 S
,
 V
,
 C
,
 Cs
,
 Ct
,
 Cc
,
 Cg
,
 Cf
,
 patterns
,
 lpegmatch
 =
 lpeg
.
P
,
 lpeg
.
S
,
 lpeg
.
V
,
 lpeg
.
C
,
 lpeg
.
Cs
,
 lpeg
.
Ct
,
 lpeg
.
Cc
,
 lpeg
.
Cg
,
 lpeg
.
Cf
,
 lpeg
.
patterns
,
 lpeg
.
match
63local
 concat
 =
 table
.
concat
64
65local
 osuuid
            =
 os
.
uuid
66local
 setmetatableindex
 =
 table
.
setmetatableindex
67
68local
 trace_sql
     =
 false
  trackers
.
register
(
"
sql.trace
"
,
  function
(
v
)
 trace_sql
     =
 v
 end
)
69local
 trace_queries
 =
 false
  trackers
.
register
(
"
sql.queries
"
,
function
(
v
)
 trace_queries
 =
 v
 end
)
70local
 report_state
  =
 logs
.
reporter
(
"
sql
"
)
71
72-- trace_sql     = true
73-- trace_queries = true
74
75utilities
.
sql
         =
 utilities
.
sql
 or
 {
 }
76local
 sql
             =
 utilities
.
sql
77
78local
 replacetemplate
 =
 utilities
.
templates
.
replace
79local
 loadtemplate
    =
 utilities
.
templates
.
load
80
81local
 methods
         =
 {
 }
82sql
.
methods
           =
 methods
83
84local
 helpers
         =
 {
 }
85sql
.
helpers
           =
 helpers
86
87local
 serialize
       =
 table
.
fastserialize
88local
 deserialize
     =
 table
.
deserialize
89
90sql
.
serialize
         =
 serialize
91sql
.
deserialize
       =
 deserialize
92
93helpers
.
serialize
     =
 serialize
   -- bonus
94helpers
.
deserialize
   =
 deserialize
 -- bonus
95
96local
 defaults
     =
 {
 __index
 =
97    {
98        resultfile
     =
 "
result.dat
"
,
99        templatefile
   =
 "
template.sql
"
,
100        queryfile
      =
 "
query.sql
"
,
101        variables
      =
 {
 }
,
102        username
       =
 "
default
"
,
103        password
       =
 "
default
"
,
104        host
           =
 "
localhost
"
,
 -- 127.0.0.1 is sometimes more reliable
105        port
           =
 3306
,
106        database
       =
 "
default
"
,
107    }
,
108}
109
110if
 optional
 then
111
112    local
 methods
 =
 {
113        ffi
       =
 "
mysql
"
,
114        library
   =
 "
mysql
"
,
115        postgress
 =
 "
postgress
"
,
116        sqlite
    =
 "
sqlite
"
,
117        sqlite3
   =
 "
sqlite
"
,
118    }
119
120    setmetatableindex
(
sql
.
methods
,
function
(
t
,
k
)
121        local
 m
 =
 methods
[
k
]
122        if
 m
 then
123            report_state
(
"
start loading method %a as %a
"
,
k
,
m
)
124            require
(
"
libs-imp-
"
 .
.
 m
)
125            report_state
(
"
loading method %a done
"
,
k
)
126            return
 rawget
(
t
,
m
)
127        else
128            report_state
(
"
invalid method %a
"
,
tostring
(
k
)
)
129        end
130    end
)
131
132else
133
134    setmetatableindex
(
sql
.
methods
,
function
(
t
,
k
)
135        if
 type
(
k
)
 =
=
 "
string
"
 then
136            report_state
(
"
start loading method %a
"
,
k
)
137            require
(
"
util-sql-imp-
"
 .
.
 k
)
138            report_state
(
"
loading method %a done
"
,
k
)
139            return
 rawget
(
t
,
k
)
140        else
141            report_state
(
"
invalid method %a
"
,
tostring
(
k
)
)
142        end
143    end
)
144
145end
146
147-- converters
148
149local
 converters
 =
 {
 }
150sql
.
converters
   =
 converters
151
152local
 function
 makeconverter
(
entries
,
celltemplate
,
wraptemplate
)
153    local
 shortcuts
   =
 {
 }
154    local
 assignments
 =
 {
 }
155    local
 key
         =
 false
156    for
 i
=
1
,
#
entries
 do
157        local
 entry
 =
 entries
[
i
]
158        local
 name
  =
 entry
.
name
159        local
 kind
  =
 entry
.
type
 or
 entry
.
kind
160        local
 value
 =
 format
(
celltemplate
,
i
,
i
)
161        if
 kind
 =
=
 "
boolean
"
 then
162            assignments
[
#
assignments
+
1
]
 =
 format
(
"
[%q] = booleanstring(%s),
"
,
name
,
value
)
163        elseif
 kind
 =
=
 "
number
"
 then
164            assignments
[
#
assignments
+
1
]
 =
 format
(
"
[%q] = tonumber(%s),
"
,
name
,
value
)
165        elseif
 type
(
kind
)
 =
=
 "
function
"
 then
166            local
 c
 =
 #
converters
 +
 1
167            converters
[
c
]
 =
 kind
168            shortcuts
[
#
shortcuts
+
1
]
 =
 format
(
"
local fun_%s = converters[%s]
"
,
c
,
c
)
169            assignments
[
#
assignments
+
1
]
 =
 format
(
"
[%q] = fun_%s(%s),
"
,
name
,
c
,
value
)
170        elseif
 type
(
kind
)
 =
=
 "
table
"
 then
171            local
 c
 =
 #
converters
 +
 1
172            converters
[
c
]
 =
 kind
173            shortcuts
[
#
shortcuts
+
1
]
 =
 format
(
"
local tab_%s = converters[%s]
"
,
c
,
c
)
174            assignments
[
#
assignments
+
1
]
 =
 format
(
"
[%q] = tab_%s[%s],
"
,
name
,
#
converters
,
value
)
175        elseif
 kind
 =
=
 "
deserialize
"
 then
176            assignments
[
#
assignments
+
1
]
 =
 format
(
"
[%q] = deserialize(%s),
"
,
name
,
value
)
177        elseif
 kind
 =
=
 "
key
"
 then
178            -- hashed instead of indexed
179            key
 =
 value
180        elseif
 kind
 =
=
 "
entry
"
 then
181            -- so we can (efficiently) extend the hashed table
182            local
 default
 =
 entry
.
default
 or
 "
"
183            if
 type
(
default
)
 =
=
 "
string
"
 then
184                assignments
[
#
assignments
+
1
]
 =
 format
(
"
[%q] = %q,
"
,
name
,
default
)
185            else
186                assignments
[
#
assignments
+
1
]
 =
 format
(
"
[%q] = %s,
"
,
name
,
tostring
(
default
)
)
187            end
188        else
189            assignments
[
#
assignments
+
1
]
 =
 format
(
"
[%q] = %s,
"
,
name
,
value
)
190        end
191    end
192    local
 code
 =
 format
(
wraptemplate
,
concat
(
shortcuts
,
"
\n
"
)
,
key
 and
 "
{ }
"
 or
 "
data
"
,
key
 or
 "
i
"
,
concat
(
assignments
,
"
\n        
"
)
)
193    local
 func
 =
 load
(
code
)
194    return
 func
 and
 func
(
)
195end
196
197function
 sql
.
makeconverter
(
entries
)
198    local
 fields
 =
 {
 }
199    for
 i
=
1
,
#
entries
 do
200        fields
[
i
]
 =
 format
(
"
`%s`
"
,
entries
[
i
]
.
name
)
201    end
202    fields
 =
 concat
(
fields
,
 "
, 
"
)
203    local
 converter
 =
 {
204        fields
 =
 fields
205    }
206    setmetatableindex
(
converter
,
 function
(
t
,
k
)
207        local
 sqlmethod
 =
 methods
[
k
]
208        local
 v
 =
 makeconverter
(
entries
,
sqlmethod
.
celltemplate
,
sqlmethod
.
wraptemplate
)
209        t
[
k
]
 =
 v
210        return
 v
211    end
)
212    return
 converter
,
 fields
213end
214
215-- helper for libraries:
216
217local
 function
 validspecification
(
specification
)
218    local
 presets
 =
 specification
.
presets
219    if
 type
(
presets
)
 =
=
 "
string
"
 then
220        presets
 =
 dofile
(
presets
)
221    end
222    if
 type
(
presets
)
 =
=
 "
table
"
 then
223        setmetatable
(
presets
,
defaults
)
224        setmetatable
(
specification
,
{
 __index
 =
 presets
 }
)
225    else
226        setmetatable
(
specification
,
defaults
)
227    end
228    return
 true
229end
230
231helpers
.
validspecification
 =
 validspecification
232
233local
 whitespace
 =
 patterns
.
whitespace
^
0
234local
 eol
        =
 patterns
.
eol
235local
 separator
  =
 P
(
"
;
"
)
236local
 escaped
    =
 patterns
.
escaped
237local
 dquote
     =
 patterns
.
dquote
238local
 squote
     =
 patterns
.
squote
239local
 dsquote
    =
 squote
 *
 squote
240----  quoted     = patterns.quoted
241local
 quoted
     =
 dquote
 *
 (
escaped
 +
 (
1
-
dquote
)
)
^
0
 *
 dquote
242                 +
 squote
 *
 (
escaped
 +
 dsquote
 +
 (
1
-
squote
)
)
^
0
 *
 squote
243local
 comment
    =
 P
(
"
--
"
)
 *
 (
1
-
eol
)
 /
 "
"
244local
 query
      =
 whitespace
245                 *
 Cs
(
(
quoted
 +
 comment
 +
 1
 -
 separator
)
^
1
 *
 Cc
(
"
;
"
)
)
246                 *
 whitespace
247local
 splitter
   =
 Ct
(
query
 *
 (
separator
 *
 query
)
^
0
)
248
249helpers
.
querysplitter
 =
 splitter
250
251-- I will add a bit more checking.
252
253local
 function
 validspecification
(
specification
)
254    local
 presets
 =
 specification
.
presets
255    if
 type
(
presets
)
 =
=
 "
string
"
 then
256        presets
 =
 dofile
(
presets
)
257    end
258    if
 type
(
presets
)
 =
=
 "
table
"
 then
259        local
 m
 =
 getmetatable
(
presets
)
260        if
 m
 then
261            setmetatable
(
m
,
defaults
)
262        else
263            setmetatable
(
presets
,
defaults
)
264        end
265        setmetatable
(
specification
,
{
 __index
 =
 presets
 }
)
266    else
267        setmetatable
(
specification
,
defaults
)
268    end
269    local
 templatefile
 =
 specification
.
templatefile
 or
 "
query
"
270    local
 name
         =
 file
.
nameonly
(
templatefile
)
271    local
 queryfile
    =
 specification
.
queryfile
  or
 presets
.
queryfile
  or
 format
(
"
%s-temp.sql
"
,
name
)
272    local
 resultfile
   =
 specification
.
resultfile
 or
 presets
.
resultfile
 or
 format
(
"
%s-temp.dat
"
,
name
)
273    specification
.
queryfile
  =
 queryfile
274    specification
.
resultfile
 =
 resultfile
275    if
 trace_sql
 then
276        report_state
(
"
template file: %s
"
,
templatefile
 or
 "
<none>
"
)
277        report_state
(
"
query file: %s
"
,
queryfile
)
278        report_state
(
"
result file: %s
"
,
resultfile
)
279    end
280    return
 true
281end
282
283local
 function
 preparetemplate
(
specification
)
284    local
 template
 =
 specification
.
template
285    if
 template
 then
286        local
 query
 =
 replacetemplate
(
template
,
specification
.
variables
,
'
sql
'
)
287        if
 not
 query
 then
288            report_state
(
"
error in template: %s
"
,
template
)
289        elseif
 trace_queries
 then
290            report_state
(
"
query from template: %s
"
,
query
)
291        end
292        return
 query
293    end
294    local
 templatefile
 =
 specification
.
templatefile
295    if
 templatefile
 then
296        local
 query
 =
 loadtemplate
(
templatefile
,
specification
.
variables
,
'
sql
'
)
297        if
 not
 query
 then
298            report_state
(
"
error in template file %a
"
,
templatefile
)
299        elseif
 trace_queries
 then
300            report_state
(
"
query from template file %a: %s
"
,
templatefile
,
query
)
301        end
302        return
 query
303    end
304    report_state
(
"
no query template or templatefile
"
)
305end
306
307helpers
.
preparetemplate
 =
 preparetemplate
308
309-- -- -- we delay setting this -- -- --
310
311local
 currentmethod
312local
 currentserver
313
314local
 function
 firstexecute
(
...
)
315    local
 method
 =
 methods
[
currentmethod
]
316    if
 not
 method
 then
317        report_state
(
"
invalid sql method
"
)
318        sql
.
execute
 =
 function
(
)
 end
319        return
 nil
320    end
321    local
 execute
 =
 method
.
execute
322    sql
.
execute
 =
 execute
323    return
 execute
(
...
)
324end
325
326function
 sql
.
setmethod
(
method
)
327    currentmethod
 =
 method
328    sql
.
execute
 =
 firstexecute
329end
330
331function
 sql
.
setserver
(
server
)
332    currentserver
 =
 server
333end
334
335function
 sql
.
getmethod
(
)
336    return
 currentmethod
337end
338
339function
 sql
.
getserver
(
)
340    return
 currentserver
341end
342
343sql
.
setmethod
(
"
library
"
)
344sql
.
setserver
(
"
mysql
"
)
345
346-- helper:
347
348local
 sqlmethods
 =
 sql
.
methods
349
350function
 sql
.
usedatabase
(
presets
,
datatable
)
351    local
 name
 =
 datatable
 or
 presets
.
datatable
352    if
 name
 then
353        local
 usedmethod
 =
 presets
.
method
354        local
 method
     =
 usedmethod
 and
 sqlmethods
[
usedmethod
]
355        if
 not
 method
 then
356            usedmethod
 =
 currentmethod
357            method
     =
 usedmethod
 and
 sqlmethods
[
usedmethod
]
358        end
359        if
 not
 method
 then
360            usedmethod
 =
 sql
.
methods
.
client
361            method
     =
 usedmethod
 and
 sqlmethods
[
usedmethod
]
362            if
 not
 method
 then
363                report_state
(
"
invalid method
"
)
364                return
365            end
366        end
367        local
 base
      =
 presets
.
database
 or
 "
test
"
368        local
 basename
  =
 format
(
"
`%s`.`%s`
"
,
base
,
name
)
369        local
 execute
   =
 nil
370        local
 m_execute
 =
 method
.
execute
371        if
 not
 m_execute
 then
372            execute
 =
 function
(
)
373                report_state
(
"
no valid execute handler
"
)
374            end
375        elseif
 method
.
usesfiles
 then
376            local
 queryfile
   =
 presets
.
queryfile
  or
 format
(
"
%s-temp.sql
"
,
name
)
377            local
 resultfile
  =
 presets
.
resultfile
 or
 format
(
"
%s-temp.dat
"
,
name
)
378            execute
 =
 function
(
specification
)
 -- variables template
379                if
 not
 specification
.
presets
    then
 specification
.
presets
    =
 presets
    end
380                if
 not
 specification
.
queryfile
  then
 specification
.
queryfile
  =
 queryfile
  end
381                if
 not
 specification
.
resultfile
 then
 specification
.
resultfile
 =
 resultfile
 end
382                return
 m_execute
(
specification
)
383            end
384        else
385            execute
 =
 function
(
specification
)
 -- variables template
386                if
 not
 specification
.
presets
 then
 specification
.
presets
 =
 presets
 end
387                return
 m_execute
(
specification
)
388            end
389        end
390        local
 function
 unpackdata
(
records
,
name
)
391            if
 records
 then
392                name
 =
 name
 or
 "
data
"
393                for
 i
=
1
,
#
records
 do
394                    local
 record
 =
 records
[
i
]
395                    local
 data
 =
 record
[
name
]
396                    if
 data
 then
397                        record
[
name
]
 =
 deserialize
(
data
)
398                    end
399                end
400            end
401        end
402        return
 {
403            usedmethod
  =
 usedmethod
,
404            presets
     =
 preset
,
405            base
        =
 base
,
406            name
        =
 name
,
407            basename
    =
 basename
,
408            execute
     =
 execute
,
409            serialize
   =
 serialize
,
410            deserialize
 =
 deserialize
,
411            unpackdata
  =
 unpackdata
,
412        }
413    else
414        report_state
(
"
missing name in usedatabase specification
"
)
415    end
416end
417
418-- local data = utilities.sql.prepare {
419--     templatefile = "test.sql",
420--     variables    = { },
421--     host         = "...",
422--     username     = "...",
423--     password     = "...",
424--     database     = "...",
425-- }
426
427-- local presets = {
428--     host     = "...",
429--     username = "...",
430--     password = "...",
431--     database = "...",
432-- }
433--
434-- local data = utilities.sql.prepare {
435--     templatefile = "test.sql",
436--     variables    = { },
437--     presets      = presets,
438-- }
439
440-- local data = utilities.sql.prepare {
441--     templatefile = "test.sql",
442--     variables    = { },
443--     presets      = dofile(...),
444-- }
445
446-- local data = utilities.sql.prepare {
447--     templatefile = "test.sql",
448--     variables    = { },
449--     presets      = "...",
450-- }
451
452-- for i=1,10 do
453--     local dummy = uuid() -- else same every time, don't ask
454-- end
455
456sql
.
tokens
 =
 {
457    length
 =
 42
,
 -- but in practice we will reserve some 50 characters
458    new
    =
 function
(
)
459        return
 format
(
"
%s-%x06
"
,
osuuid
(
)
,
random
(
0xFFFFF
)
)
 -- 36 + 1 + 6 = 42
460    end
,
461}
462
463-- -- --
464
465-- local func, code = sql.makeconverter {
466--     { name = "a", type = "number" },
467--     { name = "b", type = "string" },
468--     { name = "c", type = "boolean" },
469--     { name = "d", type = { x = "1" } },
470--     { name = "e", type = os.fulltime },
471-- }
472--
473-- print(code)
474
475-- -- --
476
477if
 tex
 and
 tex
.
systemmodes
 then
478
479    local
 droptable
 =
 table
.
drop
480    local
 threshold
 =
 16
 *
 1024
 -- use slower but less memory hungry variant
481
482    function
 sql
.
prepare
(
specification
,
tag
)
483        -- could go into tuc if needed
484        -- todo: serialize per column
485        local
 tag
 =
 tag
 or
 specification
.
tag
 or
 "
last
"
486        local
 filename
 =
 format
(
"
%s-sql-result-%s.tuc
"
,
tex
.
jobname
,
tag
)
487        if
 tex
.
systemmodes
[
"
first
"
]
 then
488            local
 data
,
 keys
 =
 sql
.
execute
(
specification
)
489            if
 not
 data
 then
490                data
 =
 {
 }
491            end
492            if
 not
 keys
 then
493                keys
 =
 {
 }
494            end
495            io
.
savedata
(
filename
,
droptable
(
{
 data
 =
 data
,
 keys
 =
 keys
 }
,
#
keys
*
#
data
>
threshold
)
)
496            return
 data
,
 keys
497        else
498            local
 result
 =
 table
.
load
(
filename
)
499            return
 result
.
data
,
 result
.
keys
500        end
501    end
502
503else
504
505    sql
.
prepare
 =
 sql
.
execute
506
507end
508
509return
 sql
510
Source Browser ?
