diff options
| -rw-r--r-- | core/certmanager.lua | 2 | ||||
| -rw-r--r-- | plugins/mod_storage_sql2.lua | 95 | ||||
| -rw-r--r-- | util/sql.lua | 41 |
3 files changed, 73 insertions, 65 deletions
diff --git a/core/certmanager.lua b/core/certmanager.lua index e8030581..dc22bb46 100644 --- a/core/certmanager.lua +++ b/core/certmanager.lua @@ -37,7 +37,7 @@ local core_defaults = { capath = "/etc/ssl/certs"; protocol = "sslv23"; verify = (ssl and ssl.x509 and { "peer", "client_once", }) or "none"; - options = { "no_sslv2", luasec_has_noticket and "no_ticket" or nil }; + options = { "no_sslv2", "no_sslv3", luasec_has_noticket and "no_ticket" or nil }; verifyext = { "lsec_continue", "lsec_ignore_purpose" }; curve = "secp384r1"; ciphers = "HIGH:!DSS:!aNULL@STRENGTH"; diff --git a/plugins/mod_storage_sql2.lua b/plugins/mod_storage_sql2.lua index 174bad78..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 }; @@ -35,56 +35,16 @@ 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() 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 - 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); - 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 }; + 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 }; @@ -92,28 +52,34 @@ 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 }; - Index { name="prosodyarchive_index", "host", "user", "store", "key" }; + Column { name="value", type="MEDIUMTEXT", nullable=false }; + Index { name="prosodyarchive_index", unique = true, "host", "user", "store", "key" }; }; engine:transaction(function() 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); - return; - 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 @@ -128,7 +94,7 @@ local function set_encoding() 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 @@ -147,11 +113,14 @@ do -- process options to get a db connection --local dburi = db2uri(params); engine = mod_sql:create_engine(params); - -- Encoding mess - set_encoding(); + engine:set_encoding(); - -- Automatically create table, ignore failure (table probably already exists) - create_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) diff --git a/util/sql.lua b/util/sql.lua index b8c16e27..735fbce8 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -251,14 +251,31 @@ 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 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 + 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.. ");" @@ -276,6 +293,28 @@ function engine:_create_table(table) end return success; end +function engine:set_encoding() -- to UTF-8 + 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 + 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 + self.charset = charset; + return self:transaction(function() return engine:execute(set_names_query:format(charset)); end); +end local engine_mt = { __index = engine }; local function db2uri(params) |