prosody
18ffe7833446
util/sqlite3.lua

Find changesets by keywords by author, files, the commit message, revision number or hash, or revset expression.

Software / code / prosody

Annotate

util/sqlite3.lua @ 13100:18ffe7833446

net.http.server: Assign an ID to each request, shared with response Goal is improve tracking of individual HTTP requests throughout its life-cycle. Having a single ID to use in logging should help here.
author Kim Alvefur <zash@zash.se>
date Sun, 14 May 2023 18:38:22 +0200
parent 12975:d10957394a3c
child 13145:af251471d5ae
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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 };