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(-) (limited to 'plugins') 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(-) (limited to 'plugins') 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 ++------------ 1 file changed, 2 insertions(+), 12 deletions(-) (limited to 'plugins') 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(); -- 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(-) (limited to 'plugins') 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 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(-) (limited to 'plugins') 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(-) (limited to 'plugins') 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(-) (limited to 'plugins') 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(-) (limited to 'plugins') 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