aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--plugins/mod_storage_sql.lua108
-rw-r--r--tools/migration/migrator/prosody_sql.lua12
-rw-r--r--util/sql.lua26
3 files changed, 72 insertions, 74 deletions
diff --git a/plugins/mod_storage_sql.lua b/plugins/mod_storage_sql.lua
index 216b3be4..5e93db57 100644
--- a/plugins/mod_storage_sql.lua
+++ b/plugins/mod_storage_sql.lua
@@ -58,9 +58,9 @@ local function keyval_store_get()
local haveany;
local result = {};
local select_sql = [[
- SELECT `key`,`type`,`value`
- FROM `prosody`
- WHERE `host`=? AND `user`=? AND `store`=?;
+ SELECT "key","type","value"
+ FROM "prosody"
+ WHERE "host"=? AND "user"=? AND "store"=?;
]]
for row in engine:select(select_sql, host, user or "", store) do
haveany = true;
@@ -80,14 +80,14 @@ local function keyval_store_get()
end
local function keyval_store_set(data)
local delete_sql = [[
- DELETE FROM `prosody`
- WHERE `host`=? AND `user`=? AND `store`=?
+ DELETE FROM "prosody"
+ WHERE "host"=? AND "user"=? AND "store"=?
]];
engine:delete(delete_sql, host, user or "", store);
local insert_sql = [[
- INSERT INTO `prosody`
- (`host`,`user`,`store`,`key`,`type`,`value`)
+ INSERT INTO "prosody"
+ ("host","user","store","key","type","value")
VALUES (?,?,?,?,?,?);
]]
if data and next(data) ~= nil then
@@ -130,9 +130,9 @@ end
function keyval_store:users()
local ok, result = engine:transaction(function()
local select_sql = [[
- SELECT DISTINCT `user`
- FROM `prosody`
- WHERE `host`=? AND `store`=?;
+ SELECT DISTINCT "user"
+ FROM "prosody"
+ WHERE "host"=? AND "store"=?;
]];
return engine:select(select_sql, host, self.store);
end);
@@ -149,9 +149,9 @@ map_store.remove = {};
function map_store:get(username, key)
local ok, result = engine:transaction(function()
local query = [[
- SELECT `type`, `value`
- FROM `prosody`
- WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?
+ SELECT "type", "value"
+ FROM "prosody"
+ WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
LIMIT 1
]];
local data;
@@ -177,18 +177,18 @@ end
function map_store:set_keys(username, keydatas)
local ok, result = engine:transaction(function()
local delete_sql = [[
- DELETE FROM `prosody`
- WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?;
+ DELETE FROM "prosody"
+ WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?;
]];
local insert_sql = [[
- INSERT INTO `prosody`
- (`host`,`user`,`store`,`key`,`type`,`value`)
+ INSERT INTO "prosody"
+ ("host","user","store","key","type","value")
VALUES (?,?,?,?,?,?);
]];
local select_extradata_sql = [[
- SELECT `type`, `value`
- FROM `prosody`
- WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?
+ SELECT "type", "value"
+ FROM "prosody"
+ WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?
LIMIT 1;
]];
for key, data in pairs(keydatas) do
@@ -227,12 +227,12 @@ function archive_store:append(username, key, value, when, with)
with = with or "";
local ok, ret = engine:transaction(function()
local delete_sql = [[
- DELETE FROM `prosodyarchive`
- WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?;
+ DELETE FROM "prosodyarchive"
+ WHERE "host"=? AND "user"=? AND "store"=? AND "key"=?;
]];
local insert_sql = [[
- INSERT INTO `prosodyarchive`
- (`host`, `user`, `store`, `when`, `with`, `key`, `type`, `value`)
+ INSERT INTO "prosodyarchive"
+ ("host", "user", "store", "when", "with", "key", "type", "value")
VALUES (?,?,?,?,?,?,?,?);
]];
if key then
@@ -253,27 +253,27 @@ local function archive_where(query, args, where)
-- Time range, inclusive
if query.start then
args[#args+1] = query.start
- where[#where+1] = "`when` >= ?"
+ where[#where+1] = "\"when\" >= ?"
end
if query["end"] then
args[#args+1] = query["end"];
if query.start then
- where[#where] = "`when` BETWEEN ? AND ?" -- is this inclusive?
+ where[#where] = "\"when\" BETWEEN ? AND ?" -- is this inclusive?
else
- where[#where+1] = "`when` <= ?"
+ where[#where+1] = "\"when\" <= ?"
end
end
-- Related name
if query.with then
- where[#where+1] = "`with` = ?";
+ where[#where+1] = "\"with\" = ?";
args[#args+1] = query.with
end
-- Unique id
if query.key then
- where[#where+1] = "`key` = ?";
+ where[#where+1] = "\"key\" = ?";
args[#args+1] = query.key
end
end
@@ -282,11 +282,11 @@ local function archive_where_id_range(query, args, where)
-- Before or after specific item, exclusive
if query.after then -- keys better be unique!
where[#where+1] = [[
- `sort_id` > COALESCE(
+ "sort_id" > COALESCE(
(
- SELECT `sort_id`
- FROM `prosodyarchive`
- WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ?
+ SELECT "sort_id"
+ FROM "prosodyarchive"
+ WHERE "key" = ? AND "host" = ? AND "user" = ? AND "store" = ?
LIMIT 1
), 0)
]];
@@ -295,16 +295,16 @@ local function archive_where_id_range(query, args, where)
end
if query.before then
where[#where+1] = [[
- `sort_id` < COALESCE(
+ "sort_id" < COALESCE(
(
- SELECT `sort_id`
- FROM `prosodyarchive`
- WHERE `key` = ? AND `host` = ? AND `user` = ? AND `store` = ?
+ SELECT "sort_id"
+ FROM "prosodyarchive"
+ WHERE "key" = ? AND "host" = ? AND "user" = ? AND "store" = ?
LIMIT 1
),
(
- SELECT MAX(`sort_id`)+1
- FROM `prosodyarchive`
+ SELECT MAX("sort_id")+1
+ FROM "prosodyarchive"
)
)
]]
@@ -318,19 +318,19 @@ function archive_store:find(username, query)
local total;
local ok, result = engine:transaction(function()
local sql_query = [[
- SELECT `key`, `type`, `value`, `when`, `with`
- FROM `prosodyarchive`
+ SELECT "key", "type", "value", "when", "with"
+ FROM "prosodyarchive"
WHERE %s
- ORDER BY `sort_id` %s%s;
+ ORDER BY "sort_id" %s%s;
]];
local args = { host, user or "", store, };
- local where = { "`host` = ?", "`user` = ?", "`store` = ?", };
+ local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", };
archive_where(query, args, where);
-- Total matching
if query.total then
- local stats = engine:select("SELECT COUNT(*) FROM `prosodyarchive` WHERE "
+ local stats = engine:select("SELECT COUNT(*) FROM \"prosodyarchive\" WHERE "
.. t_concat(where, " AND "), unpack(args));
if stats then
for row in stats do
@@ -365,9 +365,9 @@ function archive_store:delete(username, query)
query = query or {};
local user,store = username,self.store;
local ok, stmt = engine:transaction(function()
- local sql_query = "DELETE FROM `prosodyarchive` WHERE %s;";
+ local sql_query = "DELETE FROM \"prosodyarchive\" WHERE %s;";
local args = { host, user or "", store, };
- local where = { "`host` = ?", "`user` = ?", "`store` = ?", };
+ local where = { "\"host\" = ?", "\"user\" = ?", "\"store\" = ?", };
if user == true then
table.remove(args, 2);
table.remove(where, 2);
@@ -401,7 +401,7 @@ function driver:open(store, typ)
end
function driver:stores(username)
- local query = "SELECT DISTINCT `store` FROM `prosody` WHERE `host`=? AND `user`" ..
+ local query = "SELECT DISTINCT \"store\" FROM \"prosody\" WHERE \"host\"=? AND \"user\"" ..
(username == true and "!=?" or "=?");
if username == true or not username then
username = "";
@@ -415,7 +415,7 @@ end
function driver:purge(username)
return engine:transaction(function()
- local stmt,err = engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=?", host, username);
+ local stmt,err = engine:delete("DELETE FROM \"prosody\" WHERE \"host\"=? AND \"user\"=?", host, username);
return true, err;
end);
end
@@ -467,7 +467,7 @@ local function upgrade_table(engine, params, apply_changes) -- luacheck: ignore
changes = true;
if apply_changes then
module:log("info", "Upgrading database schema...");
- engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT");
+ engine:execute("ALTER TABLE prosody MODIFY COLUMN \"value\" MEDIUMTEXT");
module:log("info", "Database table automatically upgraded");
end
end
@@ -482,9 +482,9 @@ local function upgrade_table(engine, params, apply_changes) -- luacheck: ignore
-- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already
local check_encoding_query = [[
- SELECT `COLUMN_NAME`,`COLUMN_TYPE`,`TABLE_NAME`
- FROM `information_schema`.`columns`
- WHERE `TABLE_NAME` LIKE 'prosody%%' AND ( `CHARACTER_SET_NAME`!='%s' OR `COLLATION_NAME`!='%s_bin' );
+ SELECT "COLUMN_NAME","COLUMN_TYPE","TABLE_NAME"
+ FROM "information_schema"."columns"
+ WHERE "TABLE_NAME" LIKE 'prosody%%' AND ( "CHARACTER_SET_NAME"!='%s' OR "COLLATION_NAME"!='%s_bin' );
]];
check_encoding_query = check_encoding_query:format(engine.charset, engine.charset);
-- FIXME Is it ok to ignore the return values from this?
@@ -495,8 +495,8 @@ local function upgrade_table(engine, params, apply_changes) -- luacheck: ignore
changes = true;
if apply_changes then
module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns);
- local fix_column_query1 = "ALTER TABLE `%s` CHANGE `%s` `%s` BLOB;";
- local fix_column_query2 = "ALTER TABLE `%s` CHANGE `%s` `%s` %s CHARACTER SET '%s' COLLATE '%s_bin';";
+ local fix_column_query1 = "ALTER TABLE \"%s\" CHANGE \"%s\" \"%s\" BLOB;";
+ local fix_column_query2 = "ALTER TABLE \"%s\" CHANGE \"%s\" \"%s\" %s CHARACTER SET '%s' COLLATE '%s_bin';";
for row in result:rows() do
local column_name, column_type, table_name = unpack(row);
module:log("debug", "Fixing column %s in table %s", column_name, table_name);
diff --git a/tools/migration/migrator/prosody_sql.lua b/tools/migration/migrator/prosody_sql.lua
index 642b74c2..2e902fbb 100644
--- a/tools/migration/migrator/prosody_sql.lua
+++ b/tools/migration/migrator/prosody_sql.lua
@@ -92,9 +92,9 @@ local function needs_upgrade(engine, params)
-- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already
local check_encoding_query = [[
- SELECT `COLUMN_NAME`,`COLUMN_TYPE`,`TABLE_NAME`
- FROM `information_schema`.`columns`
- WHERE `TABLE_NAME` LIKE 'prosody%%' AND ( `CHARACTER_SET_NAME`!='%s' OR `COLLATION_NAME`!='%s_bin' );
+ SELECT "COLUMN_NAME","COLUMN_TYPE","TABLE_NAME"
+ FROM "information_schema"."columns"
+ WHERE "TABLE_NAME" LIKE 'prosody%%' AND ( "CHARACTER_SET_NAME"!='%s' OR "COLLATION_NAME"!='%s_bin' );
]];
check_encoding_query = check_encoding_query:format(engine.charset, engine.charset);
local result = engine:execute(check_encoding_query);
@@ -116,7 +116,7 @@ local function reader(input)
end));
local keys = {"host", "user", "store", "key", "type", "value"};
assert(engine:connect());
- local f,s,val = assert(engine:select("SELECT `host`, `user`, `store`, `key`, `type`, `value` FROM `prosody`;"));
+ local f,s,val = assert(engine:select("SELECT \"host\", \"user\", \"store\", \"key\", \"type\", \"value\" FROM \"prosody\";"));
-- get SQL rows, sorted
local iter = mtools.sorted {
reader = function() val = f(s, val); return val; end;
@@ -157,8 +157,8 @@ local function writer(output, iter)
create_table(engine);
end));
assert(engine:connect());
- assert(engine:delete("DELETE FROM prosody"));
- local insert_sql = "INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)";
+ assert(engine:delete("DELETE FROM \"prosody\""));
+ local insert_sql = "INSERT INTO \"prosody\" (\"host\",\"user\",\"store\",\"key\",\"type\",\"value\") VALUES (?,?,?,?,?,?)";
return function(item)
if not item then assert(engine.conn:commit()) return end -- end of input
diff --git a/util/sql.lua b/util/sql.lua
index eb562eb2..a859ddc9 100644
--- a/util/sql.lua
+++ b/util/sql.lua
@@ -128,8 +128,8 @@ function engine:onconnect()
end
function engine:prepquery(sql)
- if self.params.driver == "PostgreSQL" then
- sql = sql:gsub("`", "\"");
+ if self.params.driver == "MySQL" then
+ sql = sql:gsub("\"", "`");
end
return sql;
end
@@ -242,27 +242,26 @@ function engine:transaction(...)
return ok, ret;
end
function engine:_create_index(index)
- local sql = "CREATE INDEX `"..index.name.."` ON `"..index.table.."` (";
+ local sql = "CREATE INDEX \""..index.name.."\" ON \""..index.table.."\" (";
for i=1,#index do
- sql = sql.."`"..index[i].."`";
+ sql = sql.."\""..index[i].."\"";
if i ~= #index then sql = sql..", "; end
end
sql = sql..");"
- if self.params.driver == "PostgreSQL" then
- sql = sql:gsub("`", "\"");
- elseif self.params.driver == "MySQL" then
- sql = sql:gsub("`([,)])", "`(20)%1");
+ if self.params.driver == "MySQL" then
+ sql = sql:gsub("\"([,)])", "\"(20)%1");
end
if index.unique then
sql = sql:gsub("^CREATE", "CREATE UNIQUE");
end
+ sql = self:prepquery(sql);
if self._debug then
debugquery("create", sql);
end
return self:execute(sql);
end
function engine:_create_table(table)
- local sql = "CREATE TABLE `"..table.name.."` (";
+ local sql = "CREATE TABLE \""..table.name.."\" (";
for i,col in ipairs(table.c) do
local col_type = col.type;
if col_type == "MEDIUMTEXT" and self.params.driver ~= "MySQL" then
@@ -271,7 +270,7 @@ function engine:_create_table(table)
if col.auto_increment == true and self.params.driver == "PostgreSQL" then
col_type = "BIGSERIAL";
end
- sql = sql.."`"..col.name.."` "..col_type;
+ sql = sql.."\""..col.name.."\" "..col_type;
if col.nullable == false then sql = sql.." NOT NULL"; end
if col.primary_key == true then sql = sql.." PRIMARY KEY"; end
if col.auto_increment == true then
@@ -284,11 +283,10 @@ function engine:_create_table(table)
if i ~= #table.c then sql = sql..", "; end
end
sql = sql.. ");"
- if self.params.driver == "PostgreSQL" then
- sql = sql:gsub("`", "\"");
- elseif self.params.driver == "MySQL" then
+ if self.params.driver == "MySQL" then
sql = sql:gsub(";$", (" CHARACTER SET '%s' COLLATE '%s_bin';"):format(self.charset, self.charset));
end
+ sql = self:prepquery(sql);
if self._debug then
debugquery("create", sql);
end
@@ -316,7 +314,7 @@ function engine:set_encoding() -- to UTF-8
local charset = "utf8";
if driver == "MySQL" then
self:transaction(function()
- for row in self:select"SELECT `CHARACTER_SET_NAME` FROM `information_schema`.`CHARACTER_SETS` WHERE `CHARACTER_SET_NAME` LIKE 'utf8%' ORDER BY MAXLEN DESC LIMIT 1;" do
+ for row in self:select"SELECT \"CHARACTER_SET_NAME\" FROM \"information_schema\".\"CHARACTER_SETS\" WHERE \"CHARACTER_SET_NAME\" LIKE 'utf8%' ORDER BY MAXLEN DESC LIMIT 1;" do
charset = row and row[1] or charset;
end
end);
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-10 21:30:30 +0000