From af3f07ec571349201837f6824d992ab877b1b14d Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Mon, 28 Oct 2013 21:34:55 +0100 Subject: mod_storage_sql2: Split up setting of encoding and table upgrade code --- plugins/mod_storage_sql2.lua | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git a/plugins/mod_storage_sql2.lua b/plugins/mod_storage_sql2.lua index 174bad78..7e4de79b 100644 --- a/plugins/mod_storage_sql2.lua +++ b/plugins/mod_storage_sql2.lua @@ -108,8 +108,9 @@ local function set_encoding() local success,err = engine:transaction(function() return engine:execute(set_names_query); end); if not success then module:log("error", "Failed to set database connection encoding to UTF8: %s", err); - return; end +end +local function upgrade_table() if params.driver == "MySQL" then -- COMPAT w/pre-0.9: Upgrade tables to UTF-8 if not already local check_encoding_query = "SELECT `COLUMN_NAME`,`COLUMN_TYPE` FROM `information_schema`.`columns` WHERE `TABLE_NAME`='prosody' AND ( `CHARACTER_SET_NAME`!='utf8' OR `COLLATION_NAME`!='utf8_bin' );"; @@ -149,6 +150,7 @@ do -- process options to get a db connection -- Encoding mess set_encoding(); + upgrade_table(); -- Automatically create table, ignore failure (table probably already exists) create_table(); -- cgit v1.2.3 From d011ca8a325fc9bda520898a58a0c255d8c52968 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Mon, 28 Oct 2013 21:37:30 +0100 Subject: mod_storage_sql2: Move all schema upgrade code to the same place --- plugins/mod_storage_sql2.lua | 42 ++++++++++++++++++++---------------------- 1 file changed, 20 insertions(+), 22 deletions(-) diff --git a/plugins/mod_storage_sql2.lua b/plugins/mod_storage_sql2.lua index 7e4de79b..90e9a2f6 100644 --- a/plugins/mod_storage_sql2.lua +++ b/plugins/mod_storage_sql2.lua @@ -64,24 +64,7 @@ local function create_table() engine:execute(create_sql); engine:execute(index_sql); end); - if not success then -- so we failed to create - if params.driver == "MySQL" then - success,err = engine:transaction(function() - local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'"); - if result:rowcount() > 0 then - module:log("info", "Upgrading database schema..."); - engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT"); - module:log("info", "Database table automatically upgraded"); - end - return true; - end); - if not success then - module:log("error", "Failed to check/upgrade database schema (%s), please see " - .."http://prosody.im/doc/mysql for help", - err or "unknown error"); - end - end - end + local ProsodyArchiveTable = Table { name="prosodyarchive"; Column { name="sort_id", type="INTEGER PRIMARY KEY AUTOINCREMENT", nullable=false }; @@ -112,9 +95,24 @@ local function set_encoding() end local function upgrade_table() if params.driver == "MySQL" then + local success,err = engine:transaction(function() + local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'"); + if result:rowcount() > 0 then + module:log("info", "Upgrading database schema..."); + engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT"); + module:log("info", "Database table automatically upgraded"); + end + return true; + end); + if not success then + module:log("error", "Failed to check/upgrade database schema (%s), please see " + .."http://prosody.im/doc/mysql for help", + err or "unknown error"); + return false; + end -- COMPAT w/pre-0.9: Upgrade tables to UTF-8 if not already local check_encoding_query = "SELECT `COLUMN_NAME`,`COLUMN_TYPE` FROM `information_schema`.`columns` WHERE `TABLE_NAME`='prosody' AND ( `CHARACTER_SET_NAME`!='utf8' OR `COLLATION_NAME`!='utf8_bin' );"; - local success,err = engine:transaction(function() + success,err = engine:transaction(function() local result = engine:execute(check_encoding_query); local n_bad_columns = result:rowcount(); if n_bad_columns > 0 then @@ -129,7 +127,7 @@ local function upgrade_table() module:log("info", "Database encoding upgrade complete!"); end end); - local success,err = engine:transaction(function() return engine:execute(check_encoding_query); end); + success,err = engine:transaction(function() return engine:execute(check_encoding_query); end); if not success then module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error"); end @@ -148,12 +146,12 @@ do -- process options to get a db connection --local dburi = db2uri(params); engine = mod_sql:create_engine(params); - -- Encoding mess set_encoding(); - upgrade_table(); -- Automatically create table, ignore failure (table probably already exists) create_table(); + -- Encoding mess + upgrade_table(); end local function serialize(value) -- cgit v1.2.3 From e9935707592087e51ec0e6e63e3fd389407ebe9a Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Mon, 28 Oct 2013 22:07:16 +0100 Subject: mod_storage_sql2, util.sql: Move code for setting encoding to util.sql --- plugins/mod_storage_sql2.lua | 14 ++------------ util/sql.lua | 11 +++++++++++ 2 files changed, 13 insertions(+), 12 deletions(-) diff --git a/plugins/mod_storage_sql2.lua b/plugins/mod_storage_sql2.lua index 90e9a2f6..0e6aca3a 100644 --- a/plugins/mod_storage_sql2.lua +++ b/plugins/mod_storage_sql2.lua @@ -82,17 +82,7 @@ local function create_table() ProsodyArchiveTable:create(engine); end); end -local function set_encoding() - if params.driver == "SQLite3" then return end - local set_names_query = "SET NAMES 'utf8';"; - if params.driver == "MySQL" then - set_names_query = set_names_query:gsub(";$", " COLLATE 'utf8_bin';"); - end - local success,err = engine:transaction(function() return engine:execute(set_names_query); end); - if not success then - module:log("error", "Failed to set database connection encoding to UTF8: %s", err); - end -end + local function upgrade_table() if params.driver == "MySQL" then local success,err = engine:transaction(function() @@ -146,7 +136,7 @@ do -- process options to get a db connection --local dburi = db2uri(params); engine = mod_sql:create_engine(params); - set_encoding(); + engine:set_encoding(); -- Automatically create table, ignore failure (table probably already exists) create_table(); diff --git a/util/sql.lua b/util/sql.lua index b8c16e27..972940f7 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -276,6 +276,17 @@ function engine:_create_table(table) end return success; end +function engine:set_encoding() -- to UTF-8 + if self.params.driver == "SQLite3" then return end + local set_names_query = "SET NAMES 'utf8';"; + if self.params.driver == "MySQL" then + set_names_query = set_names_query:gsub(";$", " COLLATE 'utf8_bin';"); + end + local success,err = engine:transaction(function() return engine:execute(set_names_query); end); + if not success then + log("error", "Failed to set database connection encoding to UTF8: %s", err); + end +end local engine_mt = { __index = engine }; local function db2uri(params) -- cgit v1.2.3 From f925ba7723bf4336b6ad7089e5cfb8841d5ffa4a Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Mon, 28 Oct 2013 22:08:46 +0100 Subject: mod_storage_sql2: Move checking of the sql_manage_tables option so it also includes table upgrades (again) --- plugins/mod_storage_sql2.lua | 13 ++++++------- 1 file changed, 6 insertions(+), 7 deletions(-) diff --git a/plugins/mod_storage_sql2.lua b/plugins/mod_storage_sql2.lua index 0e6aca3a..4148024e 100644 --- a/plugins/mod_storage_sql2.lua +++ b/plugins/mod_storage_sql2.lua @@ -41,9 +41,6 @@ local function create_table() engine:transaction(function() ProsodyTable:create(engine); end);]] - if not module:get_option("sql_manage_tables", true) then - return; - end local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);"; if params.driver == "PostgreSQL" then @@ -138,10 +135,12 @@ do -- process options to get a db connection engine:set_encoding(); - -- Automatically create table, ignore failure (table probably already exists) - create_table(); - -- Encoding mess - upgrade_table(); + if module:get_option("sql_manage_tables", true) then + -- Automatically create table, ignore failure (table probably already exists) + create_table(); + -- Encoding mess + upgrade_table(); + end end local function serialize(value) -- cgit v1.2.3 From 6c769838e9a81a145796cb447c03967f5c6fdb7a Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Mon, 28 Oct 2013 23:18:54 +0100 Subject: util.sql: Allow creating unique indices --- util/sql.lua | 3 +++ 1 file changed, 3 insertions(+) diff --git a/util/sql.lua b/util/sql.lua index 972940f7..736417bb 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -251,6 +251,9 @@ function engine:_create_index(index) elseif self.params.driver == "MySQL" then sql = sql:gsub("`([,)])", "`(20)%1"); end + if index.unique then + sql = sql:gsub("^CREATE", "CREATE UNIQUE"); + end --print(sql); return self:execute(sql); end -- cgit v1.2.3 From 136139f068145195233ed7aeb444d1a9bb45f7d4 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Mon, 28 Oct 2013 23:19:47 +0100 Subject: util.sql: Allow columns to be marked the primary key --- util/sql.lua | 1 + 1 file changed, 1 insertion(+) diff --git a/util/sql.lua b/util/sql.lua index 736417bb..7c9743e1 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -262,6 +262,7 @@ function engine:_create_table(table) for i,col in ipairs(table.c) do 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 i ~= #table.c then sql = sql..", "; end end sql = sql.. ");" -- cgit v1.2.3 From 22be28318711e0846c9a00ef3a308119f1f3f1b1 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Mon, 28 Oct 2013 23:20:25 +0100 Subject: util.sql: Support incrementing columns --- util/sql.lua | 9 +++++++++ 1 file changed, 9 insertions(+) diff --git a/util/sql.lua b/util/sql.lua index 7c9743e1..cab1bcec 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -263,6 +263,15 @@ function engine:_create_table(table) 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 + if self.params.driver == "PostgreSQL" then + sql = sql.." SERIAL"; + elseif self.params.driver == "MySQL" then + sql = sql.." AUTO_INCREMENT"; + elseif self.params.driver == "SQLite3" then + sql = sql.." AUTOINCREMENT"; + end + end if i ~= #table.c then sql = sql..", "; end end sql = sql.. ");" -- cgit v1.2.3 From a0d18144edee980a55660b4c9dfe34462f1e1d17 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Tue, 29 Oct 2013 11:42:55 +0100 Subject: util.sql: Find out if MySQL supports utf8mb4 and use that --- util/sql.lua | 17 +++++++++++------ 1 file changed, 11 insertions(+), 6 deletions(-) diff --git a/util/sql.lua b/util/sql.lua index cab1bcec..4e63bed7 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -291,14 +291,19 @@ function engine:_create_table(table) end function engine:set_encoding() -- to UTF-8 if self.params.driver == "SQLite3" then return end - local set_names_query = "SET NAMES 'utf8';"; - if self.params.driver == "MySQL" then + local driver = self.params.driver; + local set_names_query = "SET NAMES '%s';" + local charset = "utf8"; + if driver == "MySQL" then set_names_query = set_names_query:gsub(";$", " COLLATE 'utf8_bin';"); + local ok, charsets = self:transaction(function() + return self:select"SELECT `CHARACTER_SET_NAME` FROM `CHARACTER_SETS` WHERE `CHARACTER_SET_NAME` LIKE 'utf8%' ORDER BY MAXLEN DESC LIMIT 1;"; + end); + local row = ok and charsets(); + charset = row and row[1] or charset; end - local success,err = engine:transaction(function() return engine:execute(set_names_query); end); - if not success then - log("error", "Failed to set database connection encoding to UTF8: %s", err); - end + self.charset = charset; + return self:transaction(function() return engine:execute(set_names_query:format(charset)); end); end local engine_mt = { __index = engine }; -- cgit v1.2.3 From 29988cfa70e9f0c0e8be63d3f6334a36fbe55ec3 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Tue, 29 Oct 2013 11:43:49 +0100 Subject: util.sql: Check what encoding SQLite3 uses --- util/sql.lua | 8 +++++++- 1 file changed, 7 insertions(+), 1 deletion(-) diff --git a/util/sql.lua b/util/sql.lua index 4e63bed7..8c870b64 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -290,8 +290,14 @@ function engine:_create_table(table) return success; end function engine:set_encoding() -- to UTF-8 - if self.params.driver == "SQLite3" then return end local driver = self.params.driver; + if driver == "SQLite3" then + return self:transaction(function() + if self:select"PRAGMA encoding;"()[1] == "UTF-8" then + self.charset = "utf8"; + end + end); + end local set_names_query = "SET NAMES '%s';" local charset = "utf8"; if driver == "MySQL" then -- cgit v1.2.3 From 1bcfdab54f7785d3db05c65ad5672406885d0b91 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Wed, 30 Oct 2013 10:24:35 +0100 Subject: util.sql: Rewrite MEDIUMTEXT to TEXT for drivers other than MySQL --- util/sql.lua | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) diff --git a/util/sql.lua b/util/sql.lua index 8c870b64..735fbce8 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -260,7 +260,11 @@ end function engine:_create_table(table) local sql = "CREATE TABLE `"..table.name.."` ("; for i,col in ipairs(table.c) do - sql = sql.."`"..col.name.."` "..col.type; + local col_type = col.type; + if col_type == "MEDIUMTEXT" and self.params.driver ~= "MySQL" then + col_type = "TEXT"; -- MEDIUMTEXT is MySQL-specific + end + 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 -- cgit v1.2.3 From 029256f34a7f430d379007f783d9f6c1a86da1f8 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Wed, 30 Oct 2013 14:33:15 +0100 Subject: mod_storage_sql2: Use MEDIUMTEXT fields for value columns (ie TEXT on non-MySQL) --- plugins/mod_storage_sql2.lua | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/plugins/mod_storage_sql2.lua b/plugins/mod_storage_sql2.lua index 4148024e..dcd7a45a 100644 --- a/plugins/mod_storage_sql2.lua +++ b/plugins/mod_storage_sql2.lua @@ -35,7 +35,7 @@ local function create_table() Column { name="store", type="TEXT", nullable=false }; Column { name="key", type="TEXT", nullable=false }; Column { name="type", type="TEXT", nullable=false }; - Column { name="value", type="TEXT", nullable=false }; + Column { name="value", type="MEDIUMTEXT", nullable=false }; Index { name="prosody_index", "host", "user", "store", "key" }; }; engine:transaction(function() @@ -72,7 +72,7 @@ local function create_table() Column { name="when", type="INTEGER", nullable=false }; -- timestamp Column { name="with", type="TEXT", nullable=false }; -- related id Column { name="type", type="TEXT", nullable=false }; - Column { name="value", type=params.driver == "MySQL" and "MEDIUMTEXT" or "TEXT", nullable=false }; + Column { name="value", type="MEDIUMTEXT", nullable=false }; Index { name="prosodyarchive_index", "host", "user", "store", "key" }; }; engine:transaction(function() -- cgit v1.2.3 From abb45cc639f5974aaf5c4fd61618886aa492425b Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Wed, 30 Oct 2013 22:27:22 +0100 Subject: mod_storage_sql2: Use primary_key and auto_increment flags instead of baking that into the type --- plugins/mod_storage_sql2.lua | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/plugins/mod_storage_sql2.lua b/plugins/mod_storage_sql2.lua index dcd7a45a..a4f47a87 100644 --- a/plugins/mod_storage_sql2.lua +++ b/plugins/mod_storage_sql2.lua @@ -64,7 +64,7 @@ local function create_table() local ProsodyArchiveTable = Table { name="prosodyarchive"; - Column { name="sort_id", type="INTEGER PRIMARY KEY AUTOINCREMENT", nullable=false }; + Column { name="sort_id", type="INTEGER", primary_key=true, auto_increment=true, nullable=false }; Column { name="host", type="TEXT", nullable=false }; Column { name="user", type="TEXT", nullable=false }; Column { name="store", type="TEXT", nullable=false }; -- cgit v1.2.3 From b3f9455c97ef20a674c774daa2490028ee168801 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Wed, 30 Oct 2013 22:37:07 +0100 Subject: mod_storage_sql2: The prosodyarchive_index should be unique --- plugins/mod_storage_sql2.lua | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/plugins/mod_storage_sql2.lua b/plugins/mod_storage_sql2.lua index a4f47a87..9be10d24 100644 --- a/plugins/mod_storage_sql2.lua +++ b/plugins/mod_storage_sql2.lua @@ -73,7 +73,7 @@ local function create_table() Column { name="with", type="TEXT", nullable=false }; -- related id Column { name="type", type="TEXT", nullable=false }; Column { name="value", type="MEDIUMTEXT", nullable=false }; - Index { name="prosodyarchive_index", "host", "user", "store", "key" }; + Index { name="prosodyarchive_index", unique = true, "host", "user", "store", "key" }; }; engine:transaction(function() ProsodyArchiveTable:create(engine); -- cgit v1.2.3 From 7769c9bc36dce87f4d4f51dc8578b1aa6c220b08 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Thu, 31 Oct 2013 00:53:59 +0100 Subject: mod_storage_sql2: Switch to the util.sql table definition for the main table --- plugins/mod_storage_sql2.lua | 22 +--------------------- 1 file changed, 1 insertion(+), 21 deletions(-) diff --git a/plugins/mod_storage_sql2.lua b/plugins/mod_storage_sql2.lua index 9be10d24..9b365a1e 100644 --- a/plugins/mod_storage_sql2.lua +++ b/plugins/mod_storage_sql2.lua @@ -27,7 +27,7 @@ local engine; -- TODO create engine local function create_table() local Table,Column,Index = mod_sql.Table,mod_sql.Column,mod_sql.Index; - --[[ + local ProsodyTable = Table { name="prosody"; Column { name="host", type="TEXT", nullable=false }; @@ -40,26 +40,6 @@ local function create_table() }; engine:transaction(function() ProsodyTable:create(engine); - end);]] - - local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);"; - if params.driver == "PostgreSQL" then - create_sql = create_sql:gsub("`", "\""); - elseif params.driver == "MySQL" then - create_sql = create_sql:gsub("`value` TEXT", "`value` MEDIUMTEXT") - :gsub(";$", " CHARACTER SET 'utf8' COLLATE 'utf8_bin';"); - end - - local index_sql = "CREATE INDEX `prosody_index` ON `prosody` (`host`, `user`, `store`, `key`)"; - if params.driver == "PostgreSQL" then - index_sql = index_sql:gsub("`", "\""); - elseif params.driver == "MySQL" then - index_sql = index_sql:gsub("`([,)])", "`(20)%1"); - end - - local success,err = engine:transaction(function() - engine:execute(create_sql); - engine:execute(index_sql); end); local ProsodyArchiveTable = Table { -- cgit v1.2.3 From 45ecf509edc30aa6f83d87323d87b2b9c8104bb4 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Thu, 31 Oct 2013 19:00:36 +0100 Subject: certmanager: Disable SSLv3 by default --- core/certmanager.lua | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/core/certmanager.lua b/core/certmanager.lua index 350fb837..e820c914 100644 --- a/core/certmanager.lua +++ b/core/certmanager.lua @@ -33,7 +33,7 @@ module "certmanager" local default_ssl_config = configmanager.get("*", "ssl"); local default_capath = "/etc/ssl/certs"; local default_verify = (ssl and ssl.x509 and { "peer", "client_once", }) or "none"; -local default_options = { "no_sslv2", luasec_has_noticket and "no_ticket" or nil }; +local default_options = { "no_sslv2", "no_sslv3", luasec_has_noticket and "no_ticket" or nil }; local default_verifyext = { "lsec_continue", "lsec_ignore_purpose" }; if ssl and not luasec_has_verifyext and ssl.x509 then -- cgit v1.2.3