prosody
1c55403d06c4
util/sql.lua

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

Software / code / prosody

Annotate

util/sql.lua @ 7194:1c55403d06c4

certs/Makefile: Add target for generating DH params
author Kim Alvefur <zash@zash.se>
date Thu, 25 Feb 2016 15:36:12 +0100
parent 7180:0d7f80263813
child 7272:a23ca90d1984
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
1
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
2 local setmetatable, getmetatable = setmetatable, getmetatable;
7180
0d7f80263813 util.sql: Localize unpack() in Lua 5.2 compatible way
Kim Alvefur <zash@zash.se>
parents: 7174
diff changeset
3 local ipairs, unpack, select = ipairs, table.unpack or unpack, select; --luacheck: ignore 113
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
4 local tonumber, tostring = tonumber, tostring;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
5 local assert, xpcall, debug_traceback = assert, xpcall, debug.traceback;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
6 local t_concat = table.concat;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
7 local s_char = string.char;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
8 local log = require "util.logger".init("sql");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
9
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
10 local DBI = require "DBI";
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
11 -- This loads all available drivers while globals are unlocked
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
12 -- LuaDBI should be fixed to not set globals.
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
13 DBI.Drivers();
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
14 local build_url = require "socket.url".build;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
15
6777
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
16 local _ENV = nil;
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
17
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
18 local column_mt = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
19 local table_mt = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
20 local query_mt = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
21 --local op_mt = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
22 local index_mt = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
23
6777
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
24 local function is_column(x) return getmetatable(x)==column_mt; end
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
25 local function is_index(x) return getmetatable(x)==index_mt; end
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
26 local function is_table(x) return getmetatable(x)==table_mt; end
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
27 local function is_query(x) return getmetatable(x)==query_mt; end
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
28 local function Integer(n) return "Integer()" end
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
29 local function String(n) return "String()" end
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
30
6777
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
31 local function Column(definition)
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
32 return setmetatable(definition, column_mt);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
33 end
6777
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
34 local function Table(definition)
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
35 local c = {}
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
36 for i,col in ipairs(definition) do
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
37 if is_column(col) then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
38 c[i], c[col.name] = col, col;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
39 elseif is_index(col) then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
40 col.table = definition.name;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
41 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
42 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
43 return setmetatable({ __table__ = definition, c = c, name = definition.name }, table_mt);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
44 end
6777
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
45 local function Index(definition)
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
46 return setmetatable(definition, index_mt);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
47 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
48
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
49 function table_mt:__tostring()
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
50 local s = { 'name="'..self.__table__.name..'"' }
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
51 for i,col in ipairs(self.__table__) do
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
52 s[#s+1] = tostring(col);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
53 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
54 return 'Table{ '..t_concat(s, ", ")..' }'
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
55 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
56 table_mt.__index = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
57 function table_mt.__index:create(engine)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
58 return engine:_create_table(self);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
59 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
60 function table_mt:__call(...)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
61 -- TODO
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
62 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
63 function column_mt:__tostring()
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
64 return 'Column{ name="'..self.name..'", type="'..self.type..'" }'
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
65 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
66 function index_mt:__tostring()
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
67 local s = 'Index{ name="'..self.name..'"';
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
68 for i=1,#self do s = s..', "'..self[i]:gsub("[\\\"]", "\\%1")..'"'; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
69 return s..' }';
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
70 -- return 'Index{ name="'..self.name..'", type="'..self.type..'" }'
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
71 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
72
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
73 local function urldecode(s) return s and (s:gsub("%%(%x%x)", function (c) return s_char(tonumber(c,16)); end)); end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
74 local function parse_url(url)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
75 local scheme, secondpart, database = url:match("^([%w%+]+)://([^/]*)/?(.*)");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
76 assert(scheme, "Invalid URL format");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
77 local username, password, host, port;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
78 local authpart, hostpart = secondpart:match("([^@]+)@([^@+])");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
79 if not authpart then hostpart = secondpart; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
80 if authpart then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
81 username, password = authpart:match("([^:]*):(.*)");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
82 username = username or authpart;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
83 password = password and urldecode(password);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
84 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
85 if hostpart then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
86 host, port = hostpart:match("([^:]*):(.*)");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
87 host = host or hostpart;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
88 port = port and assert(tonumber(port), "Invalid URL format");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
89 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
90 return {
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
91 scheme = scheme:lower();
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
92 username = username; password = password;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
93 host = host; port = port;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
94 database = #database > 0 and database or nil;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
95 };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
96 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
97
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
98 local engine = {};
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
99 function engine:connect()
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
100 if self.conn then return true; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
101
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
102 local params = self.params;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
103 assert(params.driver, "no driver")
6765
0cbb09afa5c3 util.sql: Fix log level of debug message
Matthew Wild <mwild1@gmail.com>
parents: 6764
diff changeset
104 log("debug", "Connecting to [%s] %s...", params.driver, params.database);
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
105 local dbh, err = DBI.Connect(
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
106 params.driver, params.database,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
107 params.username, params.password,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
108 params.host, params.port
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
109 );
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
110 if not dbh then return nil, err; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
111 dbh:autocommit(false); -- don't commit automatically
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
112 self.conn = dbh;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
113 self.prepared = {};
6762
ea43a5af31ca util.sql: Return failure if set_encoding() fails
Matthew Wild <mwild1@gmail.com>
parents: 6760
diff changeset
114 local ok, err = self:set_encoding();
ea43a5af31ca util.sql: Return failure if set_encoding() fails
Matthew Wild <mwild1@gmail.com>
parents: 6760
diff changeset
115 if not ok then
ea43a5af31ca util.sql: Return failure if set_encoding() fails
Matthew Wild <mwild1@gmail.com>
parents: 6760
diff changeset
116 return ok, err;
ea43a5af31ca util.sql: Return failure if set_encoding() fails
Matthew Wild <mwild1@gmail.com>
parents: 6760
diff changeset
117 end
6758
88b89facc3c9 util.sql: Allow onconnect callback to fail connection to the DB by returning false, err
Matthew Wild <mwild1@gmail.com>
parents: 6748
diff changeset
118 local ok, err = self:onconnect();
88b89facc3c9 util.sql: Allow onconnect callback to fail connection to the DB by returning false, err
Matthew Wild <mwild1@gmail.com>
parents: 6748
diff changeset
119 if ok == false then
88b89facc3c9 util.sql: Allow onconnect callback to fail connection to the DB by returning false, err
Matthew Wild <mwild1@gmail.com>
parents: 6748
diff changeset
120 return ok, err;
88b89facc3c9 util.sql: Allow onconnect callback to fail connection to the DB by returning false, err
Matthew Wild <mwild1@gmail.com>
parents: 6748
diff changeset
121 end
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
122 return true;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
123 end
6748
ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set
Kim Alvefur <zash@zash.se>
parents: 6735
diff changeset
124 function engine:onconnect()
ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set
Kim Alvefur <zash@zash.se>
parents: 6735
diff changeset
125 -- Override from create_engine()
ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set
Kim Alvefur <zash@zash.se>
parents: 6735
diff changeset
126 end
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
127 function engine:execute(sql, ...)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
128 local success, err = self:connect();
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
129 if not success then return success, err; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
130 local prepared = self.prepared;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
131
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
132 local stmt = prepared[sql];
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
133 if not stmt then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
134 local err;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
135 stmt, err = self.conn:prepare(sql);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
136 if not stmt then return stmt, err; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
137 prepared[sql] = stmt;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
138 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
139
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
140 local success, err = stmt:execute(...);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
141 if not success then return success, err; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
142 return stmt;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
143 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
144
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
145 local result_mt = { __index = {
5744
253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount
Kim Alvefur <zash@zash.se>
parents: 5743
diff changeset
146 affected = function(self) return self.__stmt:affected(); end;
253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount
Kim Alvefur <zash@zash.se>
parents: 5743
diff changeset
147 rowcount = function(self) return self.__stmt:rowcount(); end;
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
148 } };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
149
7174
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
150 local function debugquery(where, sql, ...)
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
151 local i = 0; local a = {...}
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
152 log("debug", "[%s] %s", where, sql:gsub("%?", function () i = i + 1; local v = a[i]; if type(v) == "string" then v = ("%q"):format(v); end return tostring(v); end));
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
153 end
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
154
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
155 function engine:execute_query(sql, ...)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
156 if self.params.driver == "PostgreSQL" then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
157 sql = sql:gsub("`", "\"");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
158 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
159 local stmt = assert(self.conn:prepare(sql));
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
160 assert(stmt:execute(...));
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
161 return stmt:rows();
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
162 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
163 function engine:execute_update(sql, ...)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
164 if self.params.driver == "PostgreSQL" then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
165 sql = sql:gsub("`", "\"");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
166 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
167 local prepared = self.prepared;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
168 local stmt = prepared[sql];
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
169 if not stmt then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
170 stmt = assert(self.conn:prepare(sql));
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
171 prepared[sql] = stmt;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
172 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
173 assert(stmt:execute(...));
5744
253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount
Kim Alvefur <zash@zash.se>
parents: 5743
diff changeset
174 return setmetatable({ __stmt = stmt }, result_mt);
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
175 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
176 engine.insert = engine.execute_update;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
177 engine.select = engine.execute_query;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
178 engine.delete = engine.execute_update;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
179 engine.update = engine.execute_update;
7174
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
180 local function debugwrap(name, f)
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
181 return function (self, sql, ...)
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
182 debugquery(name, sql, ...)
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
183 return f(self, sql, ...)
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
184 end
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
185 end
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
186 function engine:debug(enable)
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
187 self._debug = enable;
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
188 if enable then
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
189 engine.insert = debugwrap("insert", engine.execute_update);
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
190 engine.select = debugwrap("select", engine.execute_query);
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
191 engine.delete = debugwrap("delete", engine.execute_update);
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
192 engine.update = debugwrap("update", engine.execute_update);
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
193 else
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
194 engine.insert = engine.execute_update;
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
195 engine.select = engine.execute_query;
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
196 engine.delete = engine.execute_update;
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
197 engine.update = engine.execute_update;
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
198 end
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
199 end
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
200 function engine:_transaction(func, ...)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
201 if not self.conn then
6733
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
202 local ok, err = self:connect();
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
203 if not ok then return ok, err; end
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
204 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
205 --assert(not self.__transaction, "Recursive transactions not allowed");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
206 local args, n_args = {...}, select("#", ...);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
207 local function f() return func(unpack(args, 1, n_args)); end
6805
c37633feaece util.sql: Log when transactions begin
Kim Alvefur <zash@zash.se>
parents: 6777
diff changeset
208 log("debug", "SQL transaction begin [%s]", tostring(func));
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
209 self.__transaction = true;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
210 local success, a, b, c = xpcall(f, debug_traceback);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
211 self.__transaction = nil;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
212 if success then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
213 log("debug", "SQL transaction success [%s]", tostring(func));
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
214 local ok, err = self.conn:commit();
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
215 if not ok then return ok, err; end -- commit failed
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
216 return success, a, b, c;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
217 else
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
218 log("debug", "SQL transaction failure [%s]: %s", tostring(func), a);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
219 if self.conn then self.conn:rollback(); end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
220 return success, a;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
221 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
222 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
223 function engine:transaction(...)
6733
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
224 local ok, ret = self:_transaction(...);
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
225 if not ok then
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
226 local conn = self.conn;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
227 if not conn or not conn:ping() then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
228 self.conn = nil;
6733
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
229 ok, ret = self:_transaction(...);
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
230 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
231 end
6733
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
232 return ok, ret;
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
233 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
234 function engine:_create_index(index)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
235 local sql = "CREATE INDEX `"..index.name.."` ON `"..index.table.."` (";
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
236 for i=1,#index do
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
237 sql = sql.."`"..index[i].."`";
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
238 if i ~= #index then sql = sql..", "; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
239 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
240 sql = sql..");"
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
241 if self.params.driver == "PostgreSQL" then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
242 sql = sql:gsub("`", "\"");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
243 elseif self.params.driver == "MySQL" then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
244 sql = sql:gsub("`([,)])", "`(20)%1");
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
245 end
5885
cbc25ae1eea0 util.sql: Allow creating unique indices
Kim Alvefur <zash@zash.se>
parents: 5883
diff changeset
246 if index.unique then
cbc25ae1eea0 util.sql: Allow creating unique indices
Kim Alvefur <zash@zash.se>
parents: 5883
diff changeset
247 sql = sql:gsub("^CREATE", "CREATE UNIQUE");
cbc25ae1eea0 util.sql: Allow creating unique indices
Kim Alvefur <zash@zash.se>
parents: 5883
diff changeset
248 end
7174
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
249 if self._debug then
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
250 debugquery("create", sql);
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
251 end
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
252 return self:execute(sql);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
253 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
254 function engine:_create_table(table)
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
255 local sql = "CREATE TABLE `"..table.name.."` (";
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
256 for i,col in ipairs(table.c) do
5890
544ca3d94596 util.sql: Rewrite MEDIUMTEXT to TEXT for drivers other than MySQL
Kim Alvefur <zash@zash.se>
parents: 5889
diff changeset
257 local col_type = col.type;
544ca3d94596 util.sql: Rewrite MEDIUMTEXT to TEXT for drivers other than MySQL
Kim Alvefur <zash@zash.se>
parents: 5889
diff changeset
258 if col_type == "MEDIUMTEXT" and self.params.driver ~= "MySQL" then
544ca3d94596 util.sql: Rewrite MEDIUMTEXT to TEXT for drivers other than MySQL
Kim Alvefur <zash@zash.se>
parents: 5889
diff changeset
259 col_type = "TEXT"; -- MEDIUMTEXT is MySQL-specific
544ca3d94596 util.sql: Rewrite MEDIUMTEXT to TEXT for drivers other than MySQL
Kim Alvefur <zash@zash.se>
parents: 5889
diff changeset
260 end
5912
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL
Kim Alvefur <zash@zash.se>
parents: 5910
diff changeset
261 if col.auto_increment == true and self.params.driver == "PostgreSQL" then
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL
Kim Alvefur <zash@zash.se>
parents: 5910
diff changeset
262 col_type = "BIGSERIAL";
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL
Kim Alvefur <zash@zash.se>
parents: 5910
diff changeset
263 end
5890
544ca3d94596 util.sql: Rewrite MEDIUMTEXT to TEXT for drivers other than MySQL
Kim Alvefur <zash@zash.se>
parents: 5889
diff changeset
264 sql = sql.."`"..col.name.."` "..col_type;
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
265 if col.nullable == false then sql = sql.." NOT NULL"; end
5886
1237f9cc3123 util.sql: Allow columns to be marked the primary key
Kim Alvefur <zash@zash.se>
parents: 5885
diff changeset
266 if col.primary_key == true then sql = sql.." PRIMARY KEY"; end
5887
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
267 if col.auto_increment == true then
5912
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL
Kim Alvefur <zash@zash.se>
parents: 5910
diff changeset
268 if self.params.driver == "MySQL" then
5887
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
269 sql = sql.." AUTO_INCREMENT";
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
270 elseif self.params.driver == "SQLite3" then
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
271 sql = sql.." AUTOINCREMENT";
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
272 end
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
273 end
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
274 if i ~= #table.c then sql = sql..", "; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
275 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
276 sql = sql.. ");"
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
277 if self.params.driver == "PostgreSQL" then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
278 sql = sql:gsub("`", "\"");
5739
2e9ad9cb206f util.sql: Set charset and collation for MySQL when creating tables
Kim Alvefur <zash@zash.se>
parents: 5494
diff changeset
279 elseif self.params.driver == "MySQL" then
6759
fb952032f83e util.sql: Create table with same charset as the charset we selected for our connection, also use corresponding _bin collation
Matthew Wild <mwild1@gmail.com>
parents: 6758
diff changeset
280 sql = sql:gsub(";$", (" CHARACTER SET '%s' COLLATE '%s_bin';"):format(self.charset, self.charset));
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
281 end
7174
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
282 if self._debug then
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
283 debugquery("create", sql);
d350e475f94e util.sql: Raw query debug logging (needs to be explicitly enabled by a plugin)
Kim Alvefur <zash@zash.se>
parents: 6806
diff changeset
284 end
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
285 local success,err = self:execute(sql);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
286 if not success then return success,err; end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
287 for i,v in ipairs(table.__table__) do
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
288 if is_index(v) then
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
289 self:_create_index(v);
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
290 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
291 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
292 return success;
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
293 end
5883
39b187e7e892 mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
Kim Alvefur <zash@zash.se>
parents: 5776
diff changeset
294 function engine:set_encoding() -- to UTF-8
5888
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
295 local driver = self.params.driver;
5889
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
296 if driver == "SQLite3" then
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
297 return self:transaction(function()
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
298 if self:select"PRAGMA encoding;"()[1] == "UTF-8" then
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
299 self.charset = "utf8";
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
300 end
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
301 end);
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
302 end
5888
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
303 local set_names_query = "SET NAMES '%s';"
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
304 local charset = "utf8";
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
305 if driver == "MySQL" then
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
306 local ok, charsets = self:transaction(function()
5919
d1cc67ed0767 util.sql: Fix previous commit
Kim Alvefur <zash@zash.se>
parents: 5918
diff changeset
307 return self:select"SELECT `CHARACTER_SET_NAME` FROM `information_schema`.`CHARACTER_SETS` WHERE `CHARACTER_SET_NAME` LIKE 'utf8%' ORDER BY MAXLEN DESC LIMIT 1;";
5888
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
308 end);
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
309 local row = ok and charsets();
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
310 charset = row and row[1] or charset;
6760
e45a58c72609 util.sql: Use appropriate collation for the chosen character set - fixes MySQL silently ignoring our SET NAMES command when we use utf8mb4
Matthew Wild <mwild1@gmail.com>
parents: 6759
diff changeset
311 set_names_query = set_names_query:gsub(";$", (" COLLATE '%s';"):format(charset.."_bin"));
5883
39b187e7e892 mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
Kim Alvefur <zash@zash.se>
parents: 5776
diff changeset
312 end
5888
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
313 self.charset = charset;
6763
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
314 log("debug", "Using encoding '%s' for database connection", charset);
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
315 local ok, err = self:transaction(function() return self:execute(set_names_query:format(charset)); end);
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
316 if not ok then
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
317 return ok, err;
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
318 end
6771
60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace
Kim Alvefur <zash@zash.se>
parents: 6766
diff changeset
319
6766
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL)
Matthew Wild <mwild1@gmail.com>
parents: 6765
diff changeset
320 if driver == "MySQL" then
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL)
Matthew Wild <mwild1@gmail.com>
parents: 6765
diff changeset
321 local ok, actual_charset = self:transaction(function ()
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL)
Matthew Wild <mwild1@gmail.com>
parents: 6765
diff changeset
322 return self:select"SHOW SESSION VARIABLES LIKE 'character_set_client'";
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL)
Matthew Wild <mwild1@gmail.com>
parents: 6765
diff changeset
323 end);
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL)
Matthew Wild <mwild1@gmail.com>
parents: 6765
diff changeset
324 for row in actual_charset do
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL)
Matthew Wild <mwild1@gmail.com>
parents: 6765
diff changeset
325 if row[2] ~= charset then
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL)
Matthew Wild <mwild1@gmail.com>
parents: 6765
diff changeset
326 log("error", "MySQL %s is actually %q (expected %q)", row[1], row[2], charset);
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL)
Matthew Wild <mwild1@gmail.com>
parents: 6765
diff changeset
327 return false, "Failed to set connection encoding";
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL)
Matthew Wild <mwild1@gmail.com>
parents: 6765
diff changeset
328 end
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL)
Matthew Wild <mwild1@gmail.com>
parents: 6765
diff changeset
329 end
b38db4b634d3 util.sql: Add safety check to ensure our chosen connection charset is actually being used (MySQL)
Matthew Wild <mwild1@gmail.com>
parents: 6765
diff changeset
330 end
6771
60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace
Kim Alvefur <zash@zash.se>
parents: 6766
diff changeset
331
6763
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
332 return true;
5883
39b187e7e892 mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
Kim Alvefur <zash@zash.se>
parents: 5776
diff changeset
333 end
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
334 local engine_mt = { __index = engine };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
335
6777
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
336 local function db2uri(params)
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
337 return build_url{
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
338 scheme = params.driver,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
339 user = params.username,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
340 password = params.password,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
341 host = params.host,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
342 port = params.port,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
343 path = params.database,
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
344 };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
345 end
6735
b553a30620b2 util.sql: Remove built-in engine caching. This is the wrong layer to do this, and unintentionally sharing connections could cause problems (e.g. when interleaving multiple queries and result fetching)
Matthew Wild <mwild1@gmail.com>
parents: 6734
diff changeset
346
6777
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
347 local function create_engine(self, params, onconnect)
6735
b553a30620b2 util.sql: Remove built-in engine caching. This is the wrong layer to do this, and unintentionally sharing connections could cause problems (e.g. when interleaving multiple queries and result fetching)
Matthew Wild <mwild1@gmail.com>
parents: 6734
diff changeset
348 return setmetatable({ url = db2uri(params), params = params, onconnect = onconnect }, engine_mt);
5494
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
349 end
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
350
6777
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
351 return {
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
352 is_column = is_column;
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
353 is_index = is_index;
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
354 is_table = is_table;
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
355 is_query = is_query;
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
356 Integer = Integer;
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
357 String = String;
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
358 Column = Column;
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
359 Table = Table;
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
360 Index = Index;
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
361 create_engine = create_engine;
6806
f824057189ed util.sql: Export db2uri (mod_storage_sql2 command uses it) (thanks mike)
Kim Alvefur <zash@zash.se>
parents: 6805
diff changeset
362 db2uri = db2uri;
6777
5de6b93d0190 util.*: Remove use of module() function, make all module functions local and return them in a table at the end
Kim Alvefur <zash@zash.se>
parents: 6771
diff changeset
363 };