Software /
code /
prosody
Annotate
util/sql.lua @ 5883:39b187e7e892
mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
author | Kim Alvefur <zash@zash.se> |
---|---|
date | Mon, 28 Oct 2013 22:07:16 +0100 |
parent | 5776:bd0ff8ae98a8 |
child | 5885:cbc25ae1eea0 |
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; |
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
|
3 local ipairs, unpack, select = ipairs, unpack, 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
|
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 |
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
|
16 module("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
|
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 |
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
|
24 function is_column(x) return getmetatable(x)==column_mt; 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
|
25 function is_index(x) return getmetatable(x)==index_mt; 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
|
26 function is_table(x) return getmetatable(x)==table_mt; 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
|
27 function is_query(x) return getmetatable(x)==query_mt; 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
|
28 --function is_op(x) return getmetatable(x)==op_mt; 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
|
29 --function expr(...) return setmetatable({...}, op_mt); 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
|
30 function Integer(n) return "Integer()" 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
|
31 function String(n) return "String()" 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
|
32 |
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 --[[local ops = { |
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
|
34 __add = function(a, b) return "("..a.."+"..b..")" 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
|
35 __sub = function(a, b) return "("..a.."-"..b..")" 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
|
36 __mul = function(a, b) return "("..a.."*"..b..")" 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
|
37 __div = function(a, b) return "("..a.."/"..b..")" 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
|
38 __mod = function(a, b) return "("..a.."%"..b..")" 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
|
39 __pow = function(a, b) return "POW("..a..","..b..")" 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
|
40 __unm = function(a) return "NOT("..a..")" 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
|
41 __len = function(a) return "COUNT("..a..")" 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 __eq = function(a, b) return "("..a.."=="..b..")" 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 __lt = function(a, b) return "("..a.."<"..b..")" 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
|
44 __le = function(a, b) return "("..a.."<="..b..")" 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
|
45 }; |
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 |
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 local functions = { |
5776
bd0ff8ae98a8
Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents:
5744
diff
changeset
|
48 |
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
|
49 }; |
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 |
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 local cmap = { |
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 [Integer] = Integer(); |
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 [String] = String(); |
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 };]] |
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 |
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 function Column(definition) |
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 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
|
58 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
|
59 function Table(definition) |
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 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
|
61 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
|
62 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
|
63 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
|
64 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
|
65 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
|
66 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
|
67 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
|
68 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
|
69 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
|
70 function Index(definition) |
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 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
|
72 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
|
73 |
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 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
|
75 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
|
76 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
|
77 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
|
78 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
|
79 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
|
80 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
|
81 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
|
82 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
|
83 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
|
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 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
|
86 -- 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
|
87 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
|
88 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
|
89 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
|
90 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
|
91 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
|
92 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
|
93 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
|
94 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
|
95 -- 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
|
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 |
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 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
|
100 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
|
101 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
|
102 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
|
103 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
|
104 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
|
105 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
|
106 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
|
107 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
|
108 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
|
109 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
|
110 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 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
|
112 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
|
113 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
|
114 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
|
115 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
|
116 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
|
117 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
|
118 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
|
119 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
|
120 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
|
121 }; |
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 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
|
123 |
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
|
124 --[[local session = {}; |
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
|
125 |
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
|
126 function session.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
|
127 local rets = {...}; |
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 query = setmetatable({ __rets = rets, __filters }, 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
|
129 return 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
|
130 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
|
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 |
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 local function db2uri(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
|
134 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
|
135 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
|
136 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
|
137 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
|
138 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
|
139 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
|
140 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
|
141 }; |
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 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
|
143 |
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 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
|
145 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
|
146 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
|
147 |
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 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
|
149 assert(params.driver, "no 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
|
150 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
|
151 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
|
152 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
|
153 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
|
154 ); |
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 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
|
156 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
|
157 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
|
158 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
|
159 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
|
160 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
|
161 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
|
162 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
|
163 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
|
164 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
|
165 |
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 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
|
167 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
|
168 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
|
169 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
|
170 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
|
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 |
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
|
174 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
|
175 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
|
176 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
|
177 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
|
178 |
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 local result_mt = { __index = { |
5744
253dfea0e3f6
util.sql: Do lazy fetching of affected/rowcount
Kim Alvefur <zash@zash.se>
parents:
5743
diff
changeset
|
180 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
|
181 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
|
182 } }; |
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
|
183 |
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
|
184 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
|
185 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
|
186 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
|
187 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
|
188 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
|
189 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
|
190 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
|
191 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
|
192 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
|
193 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
|
194 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
|
195 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
|
196 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
|
197 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
|
198 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
|
199 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
|
200 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
|
201 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
|
202 assert(stmt:execute(...)); |
5744
253dfea0e3f6
util.sql: Do lazy fetching of affected/rowcount
Kim Alvefur <zash@zash.se>
parents:
5743
diff
changeset
|
203 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
|
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 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
|
206 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
|
207 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
|
208 engine.update = 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
|
209 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
|
210 if not self.conn 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
|
211 local a,b = 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
|
212 if not a then return a,b; 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
|
213 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
|
214 --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
|
215 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
|
216 local function f() return func(unpack(args, 1, n_args)); 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
|
217 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
|
218 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
|
219 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
|
220 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
|
221 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
|
222 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
|
223 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
|
224 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
|
225 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
|
226 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
|
227 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
|
228 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
|
229 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
|
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 function engine:transaction(...) |
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
|
232 local a,b = self:_transaction(...); |
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 if not a 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
|
234 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
|
235 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
|
236 self.conn = 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
|
237 a,b = self:_transaction(...); |
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 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 return a,b; |
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 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
|
242 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
|
243 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
|
244 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
|
245 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
|
246 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
|
247 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
|
248 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
|
249 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
|
250 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
|
251 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
|
252 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
|
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 --print(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
|
255 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
|
256 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
|
257 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
|
258 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
|
259 for i,col in ipairs(table.c) 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
|
260 sql = sql.."`"..col.name.."` "..col.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
|
261 if col.nullable == false then sql = sql.." NOT NULL"; 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
|
262 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
|
263 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
|
264 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
|
265 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
|
266 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
|
267 elseif self.params.driver == "MySQL" then |
2e9ad9cb206f
util.sql: Set charset and collation for MySQL when creating tables
Kim Alvefur <zash@zash.se>
parents:
5494
diff
changeset
|
268 sql = sql:gsub(";$", " CHARACTER SET 'utf8' COLLATE 'utf8_bin';"); |
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
|
269 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
|
270 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
|
271 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
|
272 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
|
273 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
|
274 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
|
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 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
|
277 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
|
278 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
|
279 function engine:set_encoding() -- to UTF-8 |
39b187e7e892
mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
280 if self.params.driver == "SQLite3" then return end |
39b187e7e892
mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
281 local set_names_query = "SET NAMES 'utf8';"; |
39b187e7e892
mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
282 if self.params.driver == "MySQL" then |
39b187e7e892
mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
283 set_names_query = set_names_query:gsub(";$", " COLLATE 'utf8_bin';"); |
39b187e7e892
mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
284 end |
39b187e7e892
mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
285 local success,err = engine:transaction(function() return engine:execute(set_names_query); end); |
39b187e7e892
mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
286 if not success then |
39b187e7e892
mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
287 log("error", "Failed to set database connection encoding to UTF8: %s", err); |
39b187e7e892
mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
288 end |
39b187e7e892
mod_storage_sql2, util.sql: Move code for setting encoding to util.sql
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
289 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
|
290 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
|
291 |
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 local function db2uri(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
|
293 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
|
294 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
|
295 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
|
296 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
|
297 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
|
298 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
|
299 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
|
300 }; |
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
|
301 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
|
302 local engine_cache = {}; -- TODO make weak valued |
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
|
303 function create_engine(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
|
304 local url = db2uri(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
|
305 if not engine_cache[url] 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
|
306 local engine = setmetatable({ url = url, params = params }, engine_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
|
307 engine_cache[url] = 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
|
308 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
|
309 return engine_cache[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
|
310 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
|
311 |
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
|
312 |
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
|
313 --[[Users = 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
|
314 name="users"; |
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
|
315 Column { name="user_id", type=String(), primary_key=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
|
316 }; |
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
|
317 print(Users) |
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
|
318 print(Users.c.user_id)]] |
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
|
319 |
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
|
320 --local engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase'); |
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
|
321 --[[local engine = create_engine{ driver = "SQLite3", database = "./alchemy.sqlite" }; |
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
|
322 |
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
|
323 local i = 0; |
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
|
324 for row in assert(engine:execute("select * from sqlite_master")):rows(true) 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
|
325 i = i+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
|
326 print(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
|
327 for k,v in pairs(row) 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
|
328 print("",k,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
|
329 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
|
330 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
|
331 print("---") |
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
|
332 |
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
|
333 Prosody = 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
|
334 name="prosody"; |
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 Column { name="host", type="TEXT", nullable=false }; |
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
|
336 Column { name="user", type="TEXT", nullable=false }; |
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 Column { name="store", type="TEXT", nullable=false }; |
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 Column { name="key", type="TEXT", nullable=false }; |
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 Column { name="type", type="TEXT", nullable=false }; |
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 Column { name="value", type="TEXT", nullable=false }; |
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 Index { name="prosody_index", "host", "user", "store", "key" }; |
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 }; |
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 --print(Prosody); |
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 assert(engine:transaction(function() |
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 assert(Prosody: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
|
346 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
|
347 |
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
|
348 for row in assert(engine:execute("select user from prosody")):rows(true) 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
|
349 print("username:", row['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
|
350 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
|
351 --result.close();]] |
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
|
352 |
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
|
353 return _M; |