util-sql-tracers.lua /size: 2284 b    last modification: 2021-10-28 13:50
1if not modules then modules = { } end modules ['util-sql-tracers'] = {
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
9local sql     = utilities.sql
10local tracers = { }
11sql.tracers   = tracers
12
13sql.setmethod("library")
14
15local gsub, lower = string.gsub, string.lower
16
17local t_names = {
18    mysql = [[SHOW TABLES FROM `%database%`]],
19    mssql = [[SELECT table_name FROM %database%.information_schema.tables;]],
20    mssql = [[SELECT "name" FROM "%database%"."sys"."databases" ORDER BY "name";]],
21    mssql = [[SELECT name FROM "%database%"."sys"."objects" WHERE "type" IN ('P', 'U', 'V', 'TR', 'FN', 'TF');]],
22}
23
24local t_fields = {
25    mysql = [[SHOW FIELDS FROM `%database%`.`%table%` ]],
26    mssql = [[SELECT column_name "field", data_type "type", column_default "default", is_nullable "null" FROM %database%.information_schema.columns WHERE table_name='%table%']],
27}
28
29function sql.tracers.gettables(presets)
30    local servertype = sql.getserver()
31
32    local results, keys = sql.execute {
33        presets   = presets,
34        template  = t_names[servertype],
35        variables = {
36            database = presets.database,
37        },
38    }
39
40    local key    = keys and keys[1]
41    local tables = { }
42
43    if keys then
44        for i=1,#results do
45            local name = results[i][key]
46            local results, keys = sql.execute {
47                presets   = presets,
48                template  = t_fields[servertype],
49                variables = {
50                    database = presets.database,
51                    table    = name
52                },
53            }
54            if #results > 0 then
55                for i=1,#results do
56                    local result = table.loweredkeys(results[i])
57                    -- ms cleanup
58                    result.default = gsub(result.default,"^[%(']+(.-)[%)']+$","%1")
59                    result.null    = lower(result.null)
60                    --
61                    results[i] = result
62                end
63                tables[name] = results
64            else
65                -- a view
66            end
67        end
68    end
69
70    return tables
71end
72