libs-imp-postgress.lmt /size: 6293 b    last modification: 2024-01-16 09:02
1if not modules then modules = { } end modules ['libs-imp-postgress'] = {
2    version   = 1.001,
3    comment   = "companion to util-sql.lua",
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-- Not yet untested !
10
11-- c:/data/develop/tex-context/tex/texmf-win64/bin/lib/luametatex/lua/copies/postgress/libpq.dll
12
13-- we can now share the code between mysql, postgress, sqlite ... todo
14
15local libname = "postgress"
16local libfile = "libpq"
17
18local postgresslib = resolvers.libraries.validoptional(libname)
19
20if not postgresslib then return end
21
22local function okay()
23    if resolvers.libraries.optionalloaded(libname,libfile) then
24        okay = function() return true end
25    else
26        okay = function() return false end
27    end
28    return okay()
29end
30
31local lpegmatch = lpeg.match
32local setmetatable = setmetatable
33
34local sql                  = utilities.sql or require("util-sql")
35local report               = logs.reporter(libname)
36
37local trace_sql            = false  trackers.register("sql.trace",  function(v) trace_sql     = v end)
38local trace_queries        = false  trackers.register("sql.queries",function(v) trace_queries = v end)
39
40local postgress_open       = postgresslib.open
41local postgress_close      = postgresslib.close
42local postgress_execute    = postgresslib.execute
43local postgress_getmessage = postgresslib.getmessage
44
45local helpers              = sql.helpers
46local methods              = sql.methods
47local validspecification   = helpers.validspecification
48local preparetemplate      = helpers.preparetemplate
49local querysplitter        = helpers.querysplitter -- not needed
50local cache                = { }
51
52local function connect(specification)
53    return postgress_open(
54        specification.database or "",
55        specification.username or "",
56        specification.password or "",
57        specification.host     or "",
58        specification.port
59    )
60end
61
62local function execute_once(specification,retry)
63    if okay() then
64        if trace_sql then
65            report("executing postgress")
66        end
67        if not validspecification(specification) then
68            report("error in specification")
69        end
70        local query = preparetemplate(specification)
71        if not query then
72            report("error in preparation")
73            return
74        else
75            query = lpegmatch(querysplitter,query) -- not needed
76        end
77        local base = specification.database -- or specification.presets and specification.presets.database
78        if not base then
79            report("no database specified")
80            return
81        end
82        local result = { }
83        local keys   = { }
84        local id     = specification.id
85        local db     = nil
86        if id then
87            local session = cache[id]
88            if session then
89                db = session.db
90            else
91                db = connect(specification)
92                if not db then
93                    report("no session database specified")
94                else
95                    cache[id] = {
96                        specification = specification,
97                        db            = db,
98                    }
99                end
100            end
101        else
102            db = connect(specification)
103        end
104        if not db then
105            report("no database opened")
106        else
107            local converter = specification.converter
108            local nofrows   = 0
109            local callback  = nil
110            if converter then
111                local convert = converter.postgress
112                callback = function(nofcolumns,values,fields)
113                    nofrows = nofrows + 1
114                    result[nofrows] = convert(values)
115                end
116            else
117                callback = function(nofcolumns,values,fields)
118                    local column = { }
119                    for i=1,nofcolumns do
120                        local field
121                        if fields then
122                            field = fields[i]
123                            keys[i] = field
124                        else
125                            field = keys[i]
126                        end
127                        if field then
128                            column[field] = values[i]
129                        end
130                    end
131                    nofrows  = nofrows + 1
132                    result[nofrows] = column
133                end
134            end
135            for i=1,#query do
136                local okay = postgress_execute(db,query[i],callback)
137                if not okay then
138                    if id and retry and i == 1 then
139                        report("error: %s, retrying to connect",postgress_getmessage(db))
140                        postgress_close(db)
141                        cache[id] = nil
142                        return execute_once(specification,false)
143                    else
144                        report("error: %s",postgress_getmessage(db))
145                    end
146                end
147            end
148        end
149        if db and not id then
150            postgress_close(db)
151        end
152        -- bonus
153        local one = result[1]
154        if one then
155            setmetatable(result,{ __index = one } )
156        end
157        --
158        return result, keys
159    else
160        report("error: ","no library loaded")
161    end
162end
163
164local function execute(specification)
165    return execute_once(specification,true)
166end
167
168-- Here we build the dataset stepwise so we don't use the data hack that
169-- is used in the client variant.
170
171local wraptemplate = [[
172local converters    = utilities.sql.converters
173local deserialize   = utilities.sql.deserialize
174local fromjson      = utilities.sql.fromjson
175
176local tostring      = tostring
177local tonumber      = tonumber
178local booleanstring = string.booleanstring
179
180%s
181
182return function(cells)
183    -- %s (not needed)
184    -- %s (not needed)
185    return {
186        %s
187    }
188end
189]]
190
191local celltemplate = "cells[%s]"
192
193methods.postgress = {
194    execute      = execute,
195    usesfiles    = false,
196    wraptemplate = wraptemplate,
197    celltemplate = celltemplate,
198}
199
200package.loaded["util-sql-imp-postgress"] = methods.postgress
201package.loaded[libname]                  = methods.postgress
202