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 --- util/sql.lua | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'util') 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 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(+) (limited to 'util') 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(+) (limited to 'util') 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(+) (limited to 'util') 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(-) (limited to 'util') 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(-) (limited to 'util') 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(-) (limited to 'util') 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