prosody
6ac5ad578565
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 @ 13265:6ac5ad578565

mod_cron: Load last task run time inside task runner to fix async This ensures that all interactions with storage happen inside an async thread, allowing async waiting to be performed in storage drivers.
author Kim Alvefur <zash@zash.se>
date Sat, 14 Oct 2023 22:32:33 +0200
parent 13240:a378937103cb
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;
9616
61376a3c0c1d util.sql: Switch from hacky multi-arg xpcall implementation to util.xpcall
Kim Alvefur <zash@zash.se>
parents: 8555
diff changeset
3 local ipairs = ipairs;
8382
e5d00bf4a4d5 util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8380
diff changeset
4 local tostring = tostring;
7431
0d991d5659f0 util.sql: Import type too (fix global access)
Kim Alvefur <zash@zash.se>
parents: 7428
diff changeset
5 local type = type;
9616
61376a3c0c1d util.sql: Switch from hacky multi-arg xpcall implementation to util.xpcall
Kim Alvefur <zash@zash.se>
parents: 8555
diff changeset
6 local assert, pcall, debug_traceback = assert, pcall, debug.traceback;
12975
d10957394a3c util: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12872
diff changeset
7 local xpcall = require "prosody.util.xpcall".xpcall;
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
8 local t_concat = table.concat;
12975
d10957394a3c util: Prefix module imports with prosody namespace
Kim Alvefur <zash@zash.se>
parents: 12872
diff changeset
9 local log = require "prosody.util.logger".init("sql");
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
10
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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
12 -- 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
13 -- 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
14 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
15 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
16
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
17 local _ENV = nil;
8555
4f0f5b49bb03 vairious: Add annotation when an empty environment is set [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8389
diff changeset
18 -- luacheck: std none
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
19
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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
21 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
22 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
23 --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
24 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
25
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
26 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
27 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
28 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
29 local function is_query(x) return getmetatable(x)==query_mt; 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..'"' }
7513
8a6c7c4b15fb util.sql: remove unused one-letter loop variables [luacheck]
Anton Shestakov <av6@dwimlabs.net>
parents: 7431
diff changeset
51 for _, col in ipairs(self.__table__) do
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
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 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
61 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
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 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
64 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
65 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
66 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
67 -- 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
68 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
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 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
71 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
72 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
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 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
75 assert(params.driver, "no driver")
6765
0cbb09afa5c3 util.sql: Fix log level of debug message
Matthew Wild <mwild1@gmail.com>
parents: 6764
diff changeset
76 log("debug", "Connecting to [%s] %s...", params.driver, params.database);
7306
98c4c3a2b536 util.sql: Catch errors from LuaDBI connect (Fixes #568)
Kim Alvefur <zash@zash.se>
parents: 7276
diff changeset
77 local ok, dbh, err = pcall(DBI.Connect,
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
78 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
79 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
80 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
81 );
7306
98c4c3a2b536 util.sql: Catch errors from LuaDBI connect (Fixes #568)
Kim Alvefur <zash@zash.se>
parents: 7276
diff changeset
82 if not ok then return ok, dbh; 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
83 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
84 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
85 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
86 self.prepared = {};
6762
ea43a5af31ca util.sql: Return failure if set_encoding() fails
Matthew Wild <mwild1@gmail.com>
parents: 6760
diff changeset
87 local ok, err = self:set_encoding();
ea43a5af31ca util.sql: Return failure if set_encoding() fails
Matthew Wild <mwild1@gmail.com>
parents: 6760
diff changeset
88 if not ok then
ea43a5af31ca util.sql: Return failure if set_encoding() fails
Matthew Wild <mwild1@gmail.com>
parents: 6760
diff changeset
89 return ok, err;
ea43a5af31ca util.sql: Return failure if set_encoding() fails
Matthew Wild <mwild1@gmail.com>
parents: 6760
diff changeset
90 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
91 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
92 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
93 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
94 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
95 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
96 end
8382
e5d00bf4a4d5 util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8380
diff changeset
97 function engine:onconnect() -- luacheck: ignore 212/self
6748
ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set
Kim Alvefur <zash@zash.se>
parents: 6735
diff changeset
98 -- Override from create_engine()
ccf4fcfc2024 util.sql: Call onconnect, provide noop dummy if not set
Kim Alvefur <zash@zash.se>
parents: 6735
diff changeset
99 end
12872
a20923f7d5fd mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents: 10534
diff changeset
100 function engine:ondisconnect() -- luacheck: ignore 212/self
a20923f7d5fd mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents: 10534
diff changeset
101 -- Override from create_engine()
a20923f7d5fd mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents: 10534
diff changeset
102 end
7272
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method
Kim Alvefur <zash@zash.se>
parents: 7180
diff changeset
103
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method
Kim Alvefur <zash@zash.se>
parents: 7180
diff changeset
104 function engine:prepquery(sql)
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 7513
diff changeset
105 if self.params.driver == "MySQL" then
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 7513
diff changeset
106 sql = sql:gsub("\"", "`");
7272
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method
Kim Alvefur <zash@zash.se>
parents: 7180
diff changeset
107 end
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method
Kim Alvefur <zash@zash.se>
parents: 7180
diff changeset
108 return sql;
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method
Kim Alvefur <zash@zash.se>
parents: 7180
diff changeset
109 end
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method
Kim Alvefur <zash@zash.se>
parents: 7180
diff changeset
110
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
111 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
112 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
113 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
114 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
115
8076
6e0defa19ab9 util.sql: Apply quote transform in engine:execute so it is applied to eg encoding checks in mod_storage_sql (thanks Martin)
Kim Alvefur <zash@zash.se>
parents: 8073
diff changeset
116 sql = self:prepquery(sql);
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
117 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
118 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
119 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
120 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
121 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
122 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
123 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
124
8382
e5d00bf4a4d5 util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8380
diff changeset
125 -- luacheck: ignore 411/success
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
126 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
127 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
128 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
129 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
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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 local result_mt = { __index = {
5744
253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount
Kim Alvefur <zash@zash.se>
parents: 5743
diff changeset
132 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
133 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
134 } };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.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
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
136 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
137 local i = 0; local a = {...}
8077
29b3957db212 util.sql: Strip indentation from queries for debug logging
Kim Alvefur <zash@zash.se>
parents: 8076
diff changeset
138 sql = sql:gsub("\n?\t+", " ");
8380
a597ff326758 util.sql: Discard useless substitution count from string.gsub in SQL debug logs
Kim Alvefur <zash@zash.se>
parents: 8378
diff changeset
139 log("debug", "[%s] %s", where, (sql:gsub("%?", function ()
8084
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
140 i = i + 1;
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
141 local v = a[i];
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
142 if type(v) == "string" then
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
143 v = ("'%s'"):format(v:gsub("'", "''"));
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
144 end
655837e9eeeb util.sql: Produce more SQL-standard-like debug messages
Kim Alvefur <zash@zash.se>
parents: 8077
diff changeset
145 return tostring(v);
8380
a597ff326758 util.sql: Discard useless substitution count from string.gsub in SQL debug logs
Kim Alvefur <zash@zash.se>
parents: 8378
diff changeset
146 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
147 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
148
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
149 function engine:execute_query(sql, ...)
7272
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method
Kim Alvefur <zash@zash.se>
parents: 7180
diff changeset
150 sql = self:prepquery(sql);
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
151 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
152 assert(stmt:execute(...));
8186
becb593ed86d util.sql: Greedily read all rows so we can close queries early (fixes #391)
Matthew Wild <mwild1@gmail.com>
parents: 8084
diff changeset
153 local result = {};
becb593ed86d util.sql: Greedily read all rows so we can close queries early (fixes #391)
Matthew Wild <mwild1@gmail.com>
parents: 8084
diff changeset
154 for row in stmt:rows() do result[#result + 1] = row; end
becb593ed86d util.sql: Greedily read all rows so we can close queries early (fixes #391)
Matthew Wild <mwild1@gmail.com>
parents: 8084
diff changeset
155 stmt:close();
becb593ed86d util.sql: Greedily read all rows so we can close queries early (fixes #391)
Matthew Wild <mwild1@gmail.com>
parents: 8084
diff changeset
156 local i = 0;
becb593ed86d util.sql: Greedily read all rows so we can close queries early (fixes #391)
Matthew Wild <mwild1@gmail.com>
parents: 8084
diff changeset
157 return function() i=i+1; return result[i]; 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
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 function engine:execute_update(sql, ...)
7272
a23ca90d1984 util.sql: Move per-driver (currenly only PostgreSQL) query transform into its own method
Kim Alvefur <zash@zash.se>
parents: 7180
diff changeset
160 sql = self:prepquery(sql);
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
161 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
162 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
163 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
164 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
165 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
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 assert(stmt:execute(...));
5744
253dfea0e3f6 util.sql: Do lazy fetching of affected/rowcount
Kim Alvefur <zash@zash.se>
parents: 5743
diff changeset
168 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
169 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
170 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
171 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
172 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
173 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
174 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
175 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
176 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
177 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
178 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
179 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
180 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
181 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
182 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
183 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
184 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
185 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
186 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
187 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
188 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
189 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
190 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
191 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
192 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
193 end
7317
a2dce746599b util.sql: Log errors in transaction to error level with traceback but return only error message (fixes #464)
Kim Alvefur <zash@zash.se>
parents: 7312
diff changeset
194 local function handleerr(err)
8288
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok
Matthew Wild <mwild1@gmail.com>
parents: 8186
diff changeset
195 local trace = debug_traceback(err, 3);
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok
Matthew Wild <mwild1@gmail.com>
parents: 8186
diff changeset
196 log("debug", "Error in SQL transaction: %s", trace);
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok
Matthew Wild <mwild1@gmail.com>
parents: 8186
diff changeset
197 return { err = err, traceback = trace };
7317
a2dce746599b util.sql: Log errors in transaction to error level with traceback but return only error message (fixes #464)
Kim Alvefur <zash@zash.se>
parents: 7312
diff changeset
198 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
199 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
200 if not self.conn then
6733
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
201 local ok, err = self:connect();
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
202 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
203 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
204 --assert(not self.__transaction, "Recursive transactions not allowed");
10109
c59d384b0959 util.sql: Remove tostring call from logging
Kim Alvefur <zash@zash.se>
parents: 10038
diff changeset
205 log("debug", "SQL transaction begin [%s]", 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
206 self.__transaction = true;
9616
61376a3c0c1d util.sql: Switch from hacky multi-arg xpcall implementation to util.xpcall
Kim Alvefur <zash@zash.se>
parents: 8555
diff changeset
207 local success, a, b, c = xpcall(func, handleerr, ...);
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
208 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
209 if success then
10109
c59d384b0959 util.sql: Remove tostring call from logging
Kim Alvefur <zash@zash.se>
parents: 10038
diff changeset
210 log("debug", "SQL transaction success [%s]", 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
211 local ok, err = self.conn:commit();
8378
6a098961bc00 util.sql: Return an error message when a SQL commit fails (LuaDBI doesn't) (Thanks Ge0rG)
Kim Alvefur <zash@zash.se>
parents: 8288
diff changeset
212 -- LuaDBI doesn't actually return an error message here, just a boolean
6a098961bc00 util.sql: Return an error message when a SQL commit fails (LuaDBI doesn't) (Thanks Ge0rG)
Kim Alvefur <zash@zash.se>
parents: 8288
diff changeset
213 if not ok then return ok, err or "commit failed"; 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
214 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
215 else
10109
c59d384b0959 util.sql: Remove tostring call from logging
Kim Alvefur <zash@zash.se>
parents: 10038
diff changeset
216 log("debug", "SQL transaction failure [%s]: %s", func, a.err);
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
217 if self.conn then self.conn:rollback(); end
8288
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok
Matthew Wild <mwild1@gmail.com>
parents: 8186
diff changeset
218 return success, a.err;
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
219 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 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
221 function engine:transaction(...)
10275
a247fa8df7df util.sql: Preserve 3rd and 4th return values from transaction (fixes #1434) (thanks mrdoctorwho)
Kim Alvefur <zash@zash.se>
parents: 10109
diff changeset
222 local ok, ret, b, c = self:_transaction(...);
6733
36e2b35397b1 util.sql: Rename some variable to match conventions
Matthew Wild <mwild1@gmail.com>
parents: 6730
diff changeset
223 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
224 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
225 if not conn or not conn:ping() then
8288
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok
Matthew Wild <mwild1@gmail.com>
parents: 8186
diff changeset
226 log("debug", "Database connection was closed. Will reconnect and retry.");
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
227 self.conn = nil;
12872
a20923f7d5fd mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents: 10534
diff changeset
228 self:ondisconnect();
10109
c59d384b0959 util.sql: Remove tostring call from logging
Kim Alvefur <zash@zash.se>
parents: 10038
diff changeset
229 log("debug", "Retrying SQL transaction [%s]", (...));
10275
a247fa8df7df util.sql: Preserve 3rd and 4th return values from transaction (fixes #1434) (thanks mrdoctorwho)
Kim Alvefur <zash@zash.se>
parents: 10109
diff changeset
230 ok, ret, b, c = self:_transaction(...);
8288
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok
Matthew Wild <mwild1@gmail.com>
parents: 8186
diff changeset
231 log("debug", "SQL transaction retry %s", ok and "succeeded" or "failed");
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok
Matthew Wild <mwild1@gmail.com>
parents: 8186
diff changeset
232 else
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok
Matthew Wild <mwild1@gmail.com>
parents: 8186
diff changeset
233 log("debug", "SQL connection is up, so not retrying");
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok
Matthew Wild <mwild1@gmail.com>
parents: 8186
diff changeset
234 end
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok
Matthew Wild <mwild1@gmail.com>
parents: 8186
diff changeset
235 if not ok then
e9ac2d93de18 util.sql: Don't log at error level if a transaction failed and was retried ok
Matthew Wild <mwild1@gmail.com>
parents: 8186
diff changeset
236 log("error", "Error in SQL transaction: %s", 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
237 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
238 end
10275
a247fa8df7df util.sql: Preserve 3rd and 4th return values from transaction (fixes #1434) (thanks mrdoctorwho)
Kim Alvefur <zash@zash.se>
parents: 10109
diff changeset
239 return ok, ret, b, c;
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
240 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
241 function engine:_create_index(index)
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 7513
diff changeset
242 local sql = "CREATE INDEX \""..index.name.."\" ON \""..index.table.."\" (";
10038
7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064)
Kim Alvefur <zash@zash.se>
parents: 9616
diff changeset
243 if self.params.driver ~= "MySQL" then
7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064)
Kim Alvefur <zash@zash.se>
parents: 9616
diff changeset
244 sql = sql:gsub("^CREATE INDEX", "%1 IF NOT EXISTS");
7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064)
Kim Alvefur <zash@zash.se>
parents: 9616
diff changeset
245 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
246 for i=1,#index do
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 7513
diff changeset
247 sql = sql.."\""..index[i].."\"";
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
248 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
249 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
250 sql = sql..");"
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 7513
diff changeset
251 if self.params.driver == "MySQL" then
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 7513
diff changeset
252 sql = sql:gsub("\"([,)])", "\"(20)%1");
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
253 end
5885
cbc25ae1eea0 util.sql: Allow creating unique indices
Kim Alvefur <zash@zash.se>
parents: 5883
diff changeset
254 if index.unique then
cbc25ae1eea0 util.sql: Allow creating unique indices
Kim Alvefur <zash@zash.se>
parents: 5883
diff changeset
255 sql = sql:gsub("^CREATE", "CREATE UNIQUE");
cbc25ae1eea0 util.sql: Allow creating unique indices
Kim Alvefur <zash@zash.se>
parents: 5883
diff changeset
256 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
257 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
258 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
259 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
260 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
261 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 function engine:_create_table(table)
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 7513
diff changeset
263 local sql = "CREATE TABLE \""..table.name.."\" (";
10038
7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064)
Kim Alvefur <zash@zash.se>
parents: 9616
diff changeset
264 do
7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064)
Kim Alvefur <zash@zash.se>
parents: 9616
diff changeset
265 sql = sql:gsub("^CREATE TABLE", "%1 IF NOT EXISTS");
7dd0dddd8e02 util.sql: Ignore if tables and indices already exist on creation (fixes #1064)
Kim Alvefur <zash@zash.se>
parents: 9616
diff changeset
266 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
267 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
268 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
269 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
270 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
271 end
5912
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL
Kim Alvefur <zash@zash.se>
parents: 5910
diff changeset
272 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
273 col_type = "BIGSERIAL";
f6145e894569 util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL
Kim Alvefur <zash@zash.se>
parents: 5910
diff changeset
274 end
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 7513
diff changeset
275 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
276 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
277 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
278 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
279 if self.params.driver == "MySQL" then
5887
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
280 sql = sql.." AUTO_INCREMENT";
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
281 elseif self.params.driver == "SQLite3" then
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
282 sql = sql.." AUTOINCREMENT";
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
diff changeset
283 end
1f860279b2f8 util.sql: Support incrementing columns
Kim Alvefur <zash@zash.se>
parents: 5886
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 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
286 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 sql = sql.. ");"
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 7513
diff changeset
288 if 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
289 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
290 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
291 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
292 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
293 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
294 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
295 if not success then return success,err; end
7513
8a6c7c4b15fb util.sql: remove unused one-letter loop variables [luacheck]
Anton Shestakov <av6@dwimlabs.net>
parents: 7431
diff changeset
296 for _, v in ipairs(table.__table__) do
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
297 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
298 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
299 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
300 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
301 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
302 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
303 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
304 local driver = self.params.driver;
5889
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
305 if driver == "SQLite3" then
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
306 return self:transaction(function()
7275
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
307 for encoding in self:select"PRAGMA encoding;" do
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
308 if encoding[1] == "UTF-8" then
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
309 self.charset = "utf8";
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
310 end
5889
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
311 end
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
312 end);
ea6a3adb6a69 util.sql: Check what encoding SQLite3 uses
Kim Alvefur <zash@zash.se>
parents: 5888
diff changeset
313 end
5888
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
314 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
315 local charset = "utf8";
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
316 if driver == "MySQL" then
7275
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
317 self:transaction(function()
8382
e5d00bf4a4d5 util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8380
diff changeset
318 for row in self:select[[
e5d00bf4a4d5 util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8380
diff changeset
319 SELECT "CHARACTER_SET_NAME"
e5d00bf4a4d5 util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8380
diff changeset
320 FROM "information_schema"."CHARACTER_SETS"
e5d00bf4a4d5 util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8380
diff changeset
321 WHERE "CHARACTER_SET_NAME" LIKE 'utf8%'
e5d00bf4a4d5 util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8380
diff changeset
322 ORDER BY MAXLEN DESC LIMIT 1;
e5d00bf4a4d5 util: Various minor changes to please [luacheck]
Kim Alvefur <zash@zash.se>
parents: 8380
diff changeset
323 ]] do
7275
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
324 charset = row and row[1] or charset;
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
325 end
5888
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
326 end);
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
327 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
328 end
5888
f3e408ae59a6 util.sql: Find out if MySQL supports utf8mb4 and use that
Kim Alvefur <zash@zash.se>
parents: 5887
diff changeset
329 self.charset = charset;
6763
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
330 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
331 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
332 if not ok then
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
333 return ok, err;
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
334 end
6771
60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace
Kim Alvefur <zash@zash.se>
parents: 6766
diff changeset
335
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
336 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
337 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
338 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
339 end);
10534
8a42fd6702e6 util.sql: Handle failure to detect connection encoding
Kim Alvefur <zash@zash.se>
parents: 10275
diff changeset
340 if not ok then
8a42fd6702e6 util.sql: Handle failure to detect connection encoding
Kim Alvefur <zash@zash.se>
parents: 10275
diff changeset
341 return false, "Failed to detect connection encoding";
8a42fd6702e6 util.sql: Handle failure to detect connection encoding
Kim Alvefur <zash@zash.se>
parents: 10275
diff changeset
342 end
7312
b4e99602ae75 util.sql: Charset should be innocent until proven guilty (initialize charset_ok to true), fixes bug introduced in 187ba2e9c012
Matthew Wild <mwild1@gmail.com>
parents: 7306
diff changeset
343 local charset_ok = true;
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
344 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
345 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
346 log("error", "MySQL %s is actually %q (expected %q)", row[1], row[2], charset);
7275
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
347 charset_ok = false;
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
348 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
349 end
7275
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
350 if not charset_ok then
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
351 return false, "Failed to set connection encoding";
187ba2e9c012 util.sql: Don't break out of result retreival loops
Kim Alvefur <zash@zash.se>
parents: 7272
diff changeset
352 end
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
353 end
6771
60957dd5b41b util.{interpolation,prosodyctl,sql}: Trim trailing whitespace
Kim Alvefur <zash@zash.se>
parents: 6766
diff changeset
354
6763
e961ac4efbb3 util.sql: Make set_encoding() return failure of SET NAMES
Matthew Wild <mwild1@gmail.com>
parents: 6762
diff changeset
355 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
356 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
357 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
358
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
359 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
360 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
361 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
362 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
363 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
364 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
365 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
366 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
367 };
9916f0a2d178 mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff changeset
368 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
369
12872
a20923f7d5fd mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents: 10534
diff changeset
370 local function create_engine(_, params, onconnect, ondisconnect)
a20923f7d5fd mod_storage_sql: Record connection to database as module status
Kim Alvefur <zash@zash.se>
parents: 10534
diff changeset
371 return setmetatable({ url = db2uri(params); params = params; onconnect = onconnect; ondisconnect = ondisconnect }, 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
372 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
373
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
374 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
375 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
376 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
377 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
378 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
379 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
380 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
381 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
382 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
383 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
384 };