prosody
ccb030d988ac
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 @ 12848:ccb030d988ac

util.sqlite3: Skip prepared statements when no parameters are given Seems CREATE INDEX is unhappy as a prepared statement. Perhaps because the table has not been COMMIT-ed yet?
author Kim Alvefur <zash@zash.se>
date Mon, 01 Aug 2022 17:25:40 +0200
parent 12847:d6cdde74cd9b
child 12872:a20923f7d5fd
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;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
12 local log = require "util.logger".init("sql");
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
d6cdde74cd9b util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents: 12845
diff changeset
19 local sqlite_errors = require"util.error".init("util.sqlite3", {
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
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
162 function engine:execute(sql, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
163 local success, err = self:connect();
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
164 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
165 local prepared = self.prepared;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
166
12848
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents: 12847
diff changeset
167 if select('#', ...) == 0 then
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents: 12847
diff changeset
168 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
169 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
170 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
171 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
172 return err;
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents: 12847
diff changeset
173 end
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents: 12847
diff changeset
174 return true;
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents: 12847
diff changeset
175 end
ccb030d988ac util.sqlite3: Skip prepared statements when no parameters are given
Kim Alvefur <zash@zash.se>
parents: 12847
diff changeset
176
12845
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
177 local stmt = prepared[sql];
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
178 if not stmt then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
179 local err;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
180 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
181 if not stmt then
d6cdde74cd9b util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents: 12845
diff changeset
182 err = sqlite_errors.new(err);
d6cdde74cd9b util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents: 12845
diff changeset
183 err.text = self.conn:errmsg();
d6cdde74cd9b util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents: 12845
diff changeset
184 return stmt, err;
d6cdde74cd9b util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents: 12845
diff changeset
185 end
12845
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
186 prepared[sql] = stmt;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
187 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
188
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
189 local ret = stmt:bind_values(...);
12847
d6cdde74cd9b util.sqlite3: Create util.error registry from headers
Kim Alvefur <zash@zash.se>
parents: 12845
diff changeset
190 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
191 return stmt;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
192 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
193
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
194 local result_mt = {
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
195 __index = {
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
196 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
197 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
198 },
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
199 };
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
200
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
201 local function iterator(table)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
202 local i=0;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
203 return function()
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
204 i=i+1;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
205 local item=table[i];
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
206 if item ~= nil then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
207 return item;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
208 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
209 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
210 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
211
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
212 local function debugquery(where, sql, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
213 local i = 0; local a = {...}
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
214 sql = sql:gsub("\n?\t+", " ");
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
215 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
216 i = i + 1;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
217 local v = a[i];
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
218 if type(v) == "string" then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
219 v = ("'%s'"):format(v:gsub("'", "''"));
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
220 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
221 return tostring(v);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
222 end)));
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
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
225 function engine:execute_query(sql, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
226 local prepared = self.prepared;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
227 local stmt = prepared[sql];
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
228 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
229 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
230 else
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
231 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
232 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
233 local ret = stmt:bind_values(...);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
234 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
235 local data, ret = {}
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
236 while stmt:step() == ROW do
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
237 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
238 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
239 -- 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
240 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
241 prepared[sql] = stmt;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
242 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
243 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
244 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
245 function engine:execute_update(sql, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
246 local prepared = self.prepared;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
247 local stmt = prepared[sql];
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
248 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
249 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
250 else
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
251 prepared[sql] = nil;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
252 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
253 local ret = stmt:bind_values(...);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
254 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
255 local rowcount = 0;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
256 repeat
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
257 ret = stmt:step();
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
258 if ret == lsqlite3.ROW then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
259 rowcount = rowcount + 1;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
260 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
261 until ret ~= lsqlite3.ROW;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
262 local affected = self.conn:changes();
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
263 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
264 prepared[sql] = stmt;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
265 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
266 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
267 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
268 engine.insert = engine.execute_update;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
269 engine.select = engine.execute_query;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
270 engine.delete = engine.execute_update;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
271 engine.update = engine.execute_update;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
272 local function debugwrap(name, f)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
273 return function (self, sql, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
274 debugquery(name, sql, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
275 return f(self, sql, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
276 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
277 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
278 function engine:debug(enable)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
279 self._debug = enable;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
280 if enable then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
281 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
282 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
283 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
284 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
285 else
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
286 engine.insert = engine.execute_update;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
287 engine.select = engine.execute_query;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
288 engine.delete = engine.execute_update;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
289 engine.update = engine.execute_update;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
290 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
291 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
292 function engine:_(word)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
293 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
294 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
295 return true;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
296 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
297 function engine:_transaction(func, ...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
298 if not self.conn then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
299 local a,b = self:connect();
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
300 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
301 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
302 --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
303 local ok, err = self:_"BEGIN";
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
304 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
305 self.__transaction = true;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
306 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
307 self.__transaction = nil;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
308 if success then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
309 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
310 local ok, err = self:_"COMMIT";
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
311 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
312 return success, a, b, c;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
313 else
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
314 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
315 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
316 return success, a;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
317 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
318 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
319 function engine:transaction(...)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
320 local ok, ret = self:_transaction(...);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
321 if not ok then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
322 local conn = self.conn;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
323 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
324 self.conn = nil;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
325 ok, ret = self:_transaction(...);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
326 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
327 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
328 return ok, ret;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
329 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
330 function engine:_create_index(index)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
331 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
332 for i=1,#index do
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
333 sql = sql.."\""..index[i].."\"";
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
334 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
335 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
336 sql = sql..");"
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
337 if index.unique then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
338 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
339 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
340 if self._debug then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
341 debugquery("create", sql);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
342 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
343 return self:execute(sql);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
344 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
345 function engine:_create_table(table)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
346 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
347 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
348 local col_type = col.type;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
349 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
350 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
351 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
352 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
353 sql = sql.." AUTOINCREMENT";
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
354 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
355 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
356 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
357 sql = sql.. ");"
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
358 if self._debug then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
359 debugquery("create", sql);
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 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
362 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
363 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
364 if is_index(v) then
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
365 self:_create_index(v);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
366 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
367 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
368 return success;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
369 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
370 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
371 return self:transaction(function()
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
372 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
373 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
374 self.charset = "utf8";
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
375 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
376 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
377 end);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
378 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
379 local engine_mt = { __index = engine };
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
380
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
381 local function db2uri(params)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
382 return build_url{
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
383 scheme = params.driver,
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
384 user = params.username,
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
385 password = params.password,
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
386 host = params.host,
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
387 port = params.port,
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
388 path = params.database,
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
389 };
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
390 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
391
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
392 local function create_engine(_, params, onconnect)
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
393 assert(params.driver == "SQLite3", "Only SQLite3 is supported without LuaDBI");
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
394 return setmetatable({ url = db2uri(params), params = params, onconnect = onconnect }, engine_mt);
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
395 end
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
396
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
397 return {
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
398 is_column = is_column;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
399 is_index = is_index;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
400 is_table = is_table;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
401 is_query = is_query;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
402 Integer = Integer;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
403 String = String;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
404 Column = Column;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
405 Table = Table;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
406 Index = Index;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
407 create_engine = create_engine;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
408 db2uri = db2uri;
f306336b7e99 util.sqlite3: SQLite3-only variant of util.sql using LuaSQLite3
Kim Alvefur <zash@zash.se>
parents:
diff changeset
409 };