Software /
code /
prosody
Annotate
util/sqlite3.lua @ 13107:9c4dc1e6d2c9
mod_http: Add way to retrieve internal URL instead of external
This could be of help when configuring reverse proxies, as it is the
internal URL the proxy must point at.
Argument treated as an enum "internal" "external"(default) to allow for
future extensibility.
author | Kim Alvefur <zash@zash.se> |
---|---|
date | Wed, 24 May 2023 14:43:45 +0200 |
parent | 12975:d10957394a3c |
child | 13145:af251471d5ae |
rev | line source |
---|---|
12845
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
1 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
2 -- luacheck: ignore 113/unpack 211 212 411 213 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
3 local setmetatable, getmetatable = setmetatable, getmetatable; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
4 local ipairs, unpack, select = ipairs, table.unpack or unpack, select; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
5 local tonumber, tostring = tonumber, tostring; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
6 local assert, xpcall, debug_traceback = assert, xpcall, debug.traceback; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
7 local error = error |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
8 local type = type |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
9 local t_concat = table.concat; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
10 local t_insert = table.insert; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
11 local s_char = string.char; |
12975
d10957394a3c
util: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents:
12872
diff
changeset
|
12 local log = require "prosody.util.logger".init("sql"); |
12845
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
13 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
14 local lsqlite3 = require "lsqlite3"; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
15 local build_url = require "socket.url".build; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
16 local ROW, DONE = lsqlite3.ROW, lsqlite3.DONE; |
12847
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
17 |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
18 -- from sqlite3.h, no copyright claimed |
12975
d10957394a3c
util: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents:
12872
diff
changeset
|
19 local sqlite_errors = require"prosody.util.error".init("util.sqlite3", { |
12847
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
20 -- FIXME xmpp error conditions? |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
21 [1] = { code = 1; type = "modify"; condition = "ERROR"; text = "Generic error" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
22 [2] = { code = 2; type = "cancel"; condition = "INTERNAL"; text = "Internal logic error in SQLite" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
23 [3] = { code = 3; type = "auth"; condition = "PERM"; text = "Access permission denied" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
24 [4] = { code = 4; type = "cancel"; condition = "ABORT"; text = "Callback routine requested an abort" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
25 [5] = { code = 5; type = "wait"; condition = "BUSY"; text = "The database file is locked" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
26 [6] = { code = 6; type = "wait"; condition = "LOCKED"; text = "A table in the database is locked" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
27 [7] = { code = 7; type = "wait"; condition = "NOMEM"; text = "A malloc() failed" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
28 [8] = { code = 8; type = "cancel"; condition = "READONLY"; text = "Attempt to write a readonly database" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
29 [9] = { code = 9; type = "cancel"; condition = "INTERRUPT"; text = "Operation terminated by sqlite3_interrupt()" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
30 [10] = { code = 10; type = "wait"; condition = "IOERR"; text = "Some kind of disk I/O error occurred" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
31 [11] = { code = 11; type = "cancel"; condition = "CORRUPT"; text = "The database disk image is malformed" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
32 [12] = { code = 12; type = "modify"; condition = "NOTFOUND"; text = "Unknown opcode in sqlite3_file_control()" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
33 [13] = { code = 13; type = "wait"; condition = "FULL"; text = "Insertion failed because database is full" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
34 [14] = { code = 14; type = "auth"; condition = "CANTOPEN"; text = "Unable to open the database file" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
35 [15] = { code = 15; type = "cancel"; condition = "PROTOCOL"; text = "Database lock protocol error" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
36 [16] = { code = 16; type = "continue"; condition = "EMPTY"; text = "Internal use only" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
37 [17] = { code = 17; type = "modify"; condition = "SCHEMA"; text = "The database schema changed" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
38 [18] = { code = 18; type = "modify"; condition = "TOOBIG"; text = "String or BLOB exceeds size limit" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
39 [19] = { code = 19; type = "modify"; condition = "CONSTRAINT"; text = "Abort due to constraint violation" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
40 [20] = { code = 20; type = "modify"; condition = "MISMATCH"; text = "Data type mismatch" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
41 [21] = { code = 21; type = "modify"; condition = "MISUSE"; text = "Library used incorrectly" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
42 [22] = { code = 22; type = "cancel"; condition = "NOLFS"; text = "Uses OS features not supported on host" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
43 [23] = { code = 23; type = "auth"; condition = "AUTH"; text = "Authorization denied" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
44 [24] = { code = 24; type = "modify"; condition = "FORMAT"; text = "Not used" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
45 [25] = { code = 25; type = "modify"; condition = "RANGE"; text = "2nd parameter to sqlite3_bind out of range" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
46 [26] = { code = 26; type = "cancel"; condition = "NOTADB"; text = "File opened that is not a database file" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
47 [27] = { code = 27; type = "continue"; condition = "NOTICE"; text = "Notifications from sqlite3_log()" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
48 [28] = { code = 28; type = "continue"; condition = "WARNING"; text = "Warnings from sqlite3_log()" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
49 [100] = { code = 100; type = "continue"; condition = "ROW"; text = "sqlite3_step() has another row ready" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
50 [101] = { code = 101; type = "continue"; condition = "DONE"; text = "sqlite3_step() has finished executing" }; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
51 }); |
12845
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
52 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
53 local assert = function(cond, errno, err) |
12847
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
54 return assert(sqlite_errors.coerce(cond, err or errno)); |
12845
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
55 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
56 local _ENV = nil; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
57 -- luacheck: std none |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
58 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
59 local column_mt = {}; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
60 local table_mt = {}; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
61 local query_mt = {}; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
62 --local op_mt = {}; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
63 local index_mt = {}; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
64 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
65 local function is_column(x) return getmetatable(x)==column_mt; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
66 local function is_index(x) return getmetatable(x)==index_mt; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
67 local function is_table(x) return getmetatable(x)==table_mt; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
68 local function is_query(x) return getmetatable(x)==query_mt; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
69 local function Integer(n) return "Integer()" end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
70 local function String(n) return "String()" end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
71 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
72 local function Column(definition) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
73 return setmetatable(definition, column_mt); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
74 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
75 local function Table(definition) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
76 local c = {} |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
77 for i,col in ipairs(definition) do |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
78 if is_column(col) then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
79 c[i], c[col.name] = col, col; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
80 elseif is_index(col) then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
81 col.table = definition.name; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
82 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
83 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
84 return setmetatable({ __table__ = definition, c = c, name = definition.name }, table_mt); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
85 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
86 local function Index(definition) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
87 return setmetatable(definition, index_mt); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
88 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
89 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
90 function table_mt:__tostring() |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
91 local s = { 'name="'..self.__table__.name..'"' } |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
92 for i,col in ipairs(self.__table__) do |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
93 s[#s+1] = tostring(col); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
94 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
95 return 'Table{ '..t_concat(s, ", ")..' }' |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
96 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
97 table_mt.__index = {}; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
98 function table_mt.__index:create(engine) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
99 return engine:_create_table(self); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
100 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
101 function table_mt:__call(...) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
102 -- TODO |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
103 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
104 function column_mt:__tostring() |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
105 return 'Column{ name="'..self.name..'", type="'..self.type..'" }' |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
106 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
107 function index_mt:__tostring() |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
108 local s = 'Index{ name="'..self.name..'"'; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
109 for i=1,#self do s = s..', "'..self[i]:gsub("[\\\"]", "\\%1")..'"'; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
110 return s..' }'; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
111 -- return 'Index{ name="'..self.name..'", type="'..self.type..'" }' |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
112 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
113 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
114 local function urldecode(s) return s and (s:gsub("%%(%x%x)", function (c) return s_char(tonumber(c,16)); end)); end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
115 local function parse_url(url) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
116 local scheme, secondpart, database = url:match("^([%w%+]+)://([^/]*)/?(.*)"); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
117 assert(scheme, "Invalid URL format"); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
118 local username, password, host, port; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
119 local authpart, hostpart = secondpart:match("([^@]+)@([^@+])"); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
120 if not authpart then hostpart = secondpart; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
121 if authpart then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
122 username, password = authpart:match("([^:]*):(.*)"); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
123 username = username or authpart; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
124 password = password and urldecode(password); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
125 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
126 if hostpart then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
127 host, port = hostpart:match("([^:]*):(.*)"); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
128 host = host or hostpart; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
129 port = port and assert(tonumber(port), "Invalid URL format"); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
130 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
131 return { |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
132 scheme = scheme:lower(); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
133 username = username; password = password; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
134 host = host; port = port; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
135 database = #database > 0 and database or nil; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
136 }; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
137 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
138 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
139 local engine = {}; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
140 function engine:connect() |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
141 if self.conn then return true; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
142 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
143 local params = self.params; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
144 assert(params.driver == "SQLite3", "Only sqlite3 is supported"); |
12847
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
145 local dbh, err = sqlite_errors.coerce(lsqlite3.open(params.database)); |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
146 if not dbh then return nil, err; end |
12845
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
147 self.conn = dbh; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
148 self.prepared = {}; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
149 local ok, err = self:set_encoding(); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
150 if not ok then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
151 return ok, err; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
152 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
153 local ok, err = self:onconnect(); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
154 if ok == false then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
155 return ok, err; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
156 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
157 return true; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
158 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
159 function engine:onconnect() |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
160 -- Override from create_engine() |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
161 end |
12872
a20923f7d5fd
mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents:
12848
diff
changeset
|
162 function engine:ondisconnect() -- luacheck: ignore 212/self |
a20923f7d5fd
mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents:
12848
diff
changeset
|
163 -- Override from create_engine() |
a20923f7d5fd
mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents:
12848
diff
changeset
|
164 end |
12845
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
165 function engine:execute(sql, ...) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
166 local success, err = self:connect(); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
167 if not success then return success, err; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
168 local prepared = self.prepared; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
169 |
12848
ccb030d988ac
util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents:
12847
diff
changeset
|
170 if select('#', ...) == 0 then |
ccb030d988ac
util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents:
12847
diff
changeset
|
171 local ret = self.conn:exec(sql); |
ccb030d988ac
util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents:
12847
diff
changeset
|
172 if ret ~= lsqlite3.OK then |
ccb030d988ac
util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents:
12847
diff
changeset
|
173 local err = sqlite_errors.new(err); |
ccb030d988ac
util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents:
12847
diff
changeset
|
174 err.text = self.conn:errmsg(); |
ccb030d988ac
util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents:
12847
diff
changeset
|
175 return err; |
ccb030d988ac
util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents:
12847
diff
changeset
|
176 end |
ccb030d988ac
util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents:
12847
diff
changeset
|
177 return true; |
ccb030d988ac
util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents:
12847
diff
changeset
|
178 end |
ccb030d988ac
util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents:
12847
diff
changeset
|
179 |
12845
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
180 local stmt = prepared[sql]; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
181 if not stmt then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
182 local err; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
183 stmt, err = self.conn:prepare(sql); |
12847
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
184 if not stmt then |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
185 err = sqlite_errors.new(err); |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
186 err.text = self.conn:errmsg(); |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
187 return stmt, err; |
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
188 end |
12845
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
189 prepared[sql] = stmt; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
190 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
191 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
192 local ret = stmt:bind_values(...); |
12847
d6cdde74cd9b
util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents:
12845
diff
changeset
|
193 if ret ~= lsqlite3.OK then return nil, sqlite_errors.new(ret, { message = self.conn:errmsg() }); end |
12845
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
194 return stmt; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
195 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
196 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
197 local result_mt = { |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
198 __index = { |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
199 affected = function(self) return self.__affected; end; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
200 rowcount = function(self) return self.__rowcount; end; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
201 }, |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
202 }; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
203 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
204 local function iterator(table) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
205 local i=0; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
206 return function() |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
207 i=i+1; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
208 local item=table[i]; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
209 if item ~= nil then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
210 return item; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
211 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
212 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
213 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
214 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
215 local function debugquery(where, sql, ...) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
216 local i = 0; local a = {...} |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
217 sql = sql:gsub("\n?\t+", " "); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
218 log("debug", "[%s] %s", where, (sql:gsub("%?", function () |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
219 i = i + 1; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
220 local v = a[i]; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
221 if type(v) == "string" then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
222 v = ("'%s'"):format(v:gsub("'", "''")); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
223 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
224 return tostring(v); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
225 end))); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
226 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
227 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
228 function engine:execute_query(sql, ...) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
229 local prepared = self.prepared; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
230 local stmt = prepared[sql]; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
231 if stmt and stmt:isopen() then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
232 prepared[sql] = nil; -- Can't be used concurrently |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
233 else |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
234 stmt = assert(self.conn:prepare(sql)); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
235 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
236 local ret = stmt:bind_values(...); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
237 if ret ~= lsqlite3.OK then error(self.conn:errmsg()); end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
238 local data, ret = {} |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
239 while stmt:step() == ROW do |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
240 t_insert(data, stmt:get_values()); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
241 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
242 -- FIXME Error handling, BUSY, ERROR, MISUSE |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
243 if stmt:reset() == lsqlite3.OK then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
244 prepared[sql] = stmt; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
245 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
246 return setmetatable({ __data = data }, { __index = result_mt.__index, __call = iterator(data) }); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
247 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
248 function engine:execute_update(sql, ...) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
249 local prepared = self.prepared; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
250 local stmt = prepared[sql]; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
251 if not stmt or not stmt:isopen() then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
252 stmt = assert(self.conn:prepare(sql)); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
253 else |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
254 prepared[sql] = nil; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
255 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
256 local ret = stmt:bind_values(...); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
257 if ret ~= lsqlite3.OK then error(self.conn:errmsg()); end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
258 local rowcount = 0; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
259 repeat |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
260 ret = stmt:step(); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
261 if ret == lsqlite3.ROW then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
262 rowcount = rowcount + 1; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
263 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
264 until ret ~= lsqlite3.ROW; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
265 local affected = self.conn:changes(); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
266 if stmt:reset() == lsqlite3.OK then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
267 prepared[sql] = stmt; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
268 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
269 return setmetatable({ __affected = affected, __rowcount = rowcount }, result_mt); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
270 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
271 engine.insert = engine.execute_update; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
272 engine.select = engine.execute_query; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
273 engine.delete = engine.execute_update; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
274 engine.update = engine.execute_update; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
275 local function debugwrap(name, f) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
276 return function (self, sql, ...) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
277 debugquery(name, sql, ...) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
278 return f(self, sql, ...) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
279 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
280 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
281 function engine:debug(enable) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
282 self._debug = enable; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
283 if enable then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
284 engine.insert = debugwrap("insert", engine.execute_update); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
285 engine.select = debugwrap("select", engine.execute_query); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
286 engine.delete = debugwrap("delete", engine.execute_update); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
287 engine.update = debugwrap("update", engine.execute_update); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
288 else |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
289 engine.insert = engine.execute_update; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
290 engine.select = engine.execute_query; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
291 engine.delete = engine.execute_update; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
292 engine.update = engine.execute_update; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
293 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
294 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
295 function engine:_(word) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
296 local ret = self.conn:exec(word); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
297 if ret ~= lsqlite3.OK then return nil, self.conn:errmsg(); end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
298 return true; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
299 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
300 function engine:_transaction(func, ...) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
301 if not self.conn then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
302 local a,b = self:connect(); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
303 if not a then return a,b; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
304 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
305 --assert(not self.__transaction, "Recursive transactions not allowed"); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
306 local ok, err = self:_"BEGIN"; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
307 if not ok then return ok, err; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
308 self.__transaction = true; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
309 local success, a, b, c = xpcall(func, debug_traceback, ...); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
310 self.__transaction = nil; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
311 if success then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
312 log("debug", "SQL transaction success [%s]", tostring(func)); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
313 local ok, err = self:_"COMMIT"; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
314 if not ok then return ok, err; end -- commit failed |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
315 return success, a, b, c; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
316 else |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
317 log("debug", "SQL transaction failure [%s]: %s", tostring(func), a); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
318 if self.conn then self:_"ROLLBACK"; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
319 return success, a; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
320 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
321 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
322 function engine:transaction(...) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
323 local ok, ret = self:_transaction(...); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
324 if not ok then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
325 local conn = self.conn; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
326 if not conn or not conn:isopen() then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
327 self.conn = nil; |
12872
a20923f7d5fd
mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents:
12848
diff
changeset
|
328 self:ondisconnect(); |
12845
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
329 ok, ret = self:_transaction(...); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
330 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
331 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
332 return ok, ret; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
333 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
334 function engine:_create_index(index) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
335 local sql = "CREATE INDEX IF NOT EXISTS \""..index.name.."\" ON \""..index.table.."\" ("; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
336 for i=1,#index do |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
337 sql = sql.."\""..index[i].."\""; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
338 if i ~= #index then sql = sql..", "; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
339 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
340 sql = sql..");" |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
341 if index.unique then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
342 sql = sql:gsub("^CREATE", "CREATE UNIQUE"); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
343 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
344 if self._debug then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
345 debugquery("create", sql); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
346 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
347 return self:execute(sql); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
348 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
349 function engine:_create_table(table) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
350 local sql = "CREATE TABLE IF NOT EXISTS \""..table.name.."\" ("; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
351 for i,col in ipairs(table.c) do |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
352 local col_type = col.type; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
353 sql = sql.."\""..col.name.."\" "..col_type; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
354 if col.nullable == false then sql = sql.." NOT NULL"; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
355 if col.primary_key == true then sql = sql.." PRIMARY KEY"; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
356 if col.auto_increment == true then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
357 sql = sql.." AUTOINCREMENT"; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
358 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
359 if i ~= #table.c then sql = sql..", "; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
360 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
361 sql = sql.. ");" |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
362 if self._debug then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
363 debugquery("create", sql); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
364 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
365 local success,err = self:execute(sql); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
366 if not success then return success,err; end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
367 for i,v in ipairs(table.__table__) do |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
368 if is_index(v) then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
369 self:_create_index(v); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
370 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
371 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
372 return success; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
373 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
374 function engine:set_encoding() -- to UTF-8 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
375 return self:transaction(function() |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
376 for encoding in self:select"PRAGMA encoding;" do |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
377 if encoding[1] == "UTF-8" then |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
378 self.charset = "utf8"; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
379 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
380 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
381 end); |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
382 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
383 local engine_mt = { __index = engine }; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
384 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
385 local function db2uri(params) |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
386 return build_url{ |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
387 scheme = params.driver, |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
388 user = params.username, |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
389 password = params.password, |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
390 host = params.host, |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
391 port = params.port, |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
392 path = params.database, |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
393 }; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
394 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
395 |
12872
a20923f7d5fd
mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents:
12848
diff
changeset
|
396 local function create_engine(_, params, onconnect, ondisconnect) |
12845
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
397 assert(params.driver == "SQLite3", "Only SQLite3 is supported without LuaDBI"); |
12872
a20923f7d5fd
mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents:
12848
diff
changeset
|
398 return setmetatable({ url = db2uri(params); params = params; onconnect = onconnect; ondisconnect = ondisconnect }, engine_mt); |
12845
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
399 end |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
400 |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
401 return { |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
402 is_column = is_column; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
403 is_index = is_index; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
404 is_table = is_table; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
405 is_query = is_query; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
406 Integer = Integer; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
407 String = String; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
408 Column = Column; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
409 Table = Table; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
410 Index = Index; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
411 create_engine = create_engine; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
412 db2uri = db2uri; |
f306336b7e99
util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff
changeset
|
413 }; |