From 06e7d7b489229a80e144ad883a2addde6d92c730 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Thu, 11 Jul 2013 22:07:55 +0200 Subject: util.sql: Set charset and collation for MySQL when creating tables --- util/sql.lua | 2 ++ 1 file changed, 2 insertions(+) (limited to 'util/sql.lua') diff --git a/util/sql.lua b/util/sql.lua index f360d6d0..771df7aa 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -264,6 +264,8 @@ function engine:_create_table(table) sql = sql.. ");" if self.params.driver == "PostgreSQL" then sql = sql:gsub("`", "\""); + elseif self.params.driver == "MySQL" then + sql = sql:gsub(";$", " CHARACTER SET 'utf8' COLLATE 'utf8_bin';"); end local success,err = self:execute(sql); if not success then return success,err; end -- cgit v1.2.3 From c41f00e53fd2adcda02df292b98d01cebabfa773 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Fri, 12 Jul 2013 17:03:09 +0200 Subject: util.sql: Don't fetch row count of result sets for queries that don't have result sets --- util/sql.lua | 3 +-- 1 file changed, 1 insertion(+), 2 deletions(-) (limited to 'util/sql.lua') diff --git a/util/sql.lua b/util/sql.lua index 771df7aa..475215bb 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -178,7 +178,6 @@ end local result_mt = { __index = { affected = function(self) return self.__affected; end; - rowcount = function(self) return self.__rowcount; end; } }; function engine:execute_query(sql, ...) @@ -200,7 +199,7 @@ function engine:execute_update(sql, ...) prepared[sql] = stmt; end assert(stmt:execute(...)); - return setmetatable({ __affected = stmt:affected(), __rowcount = stmt:rowcount() }, result_mt); + return setmetatable({ __affected = stmt:affected() }, result_mt); end engine.insert = engine.execute_update; engine.select = engine.execute_query; -- cgit v1.2.3 From 051779d694221adc06600418becbdeda0f48d63e Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Fri, 12 Jul 2013 17:41:54 +0200 Subject: Backed out changeset 3c57c2281087 --- util/sql.lua | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'util/sql.lua') diff --git a/util/sql.lua b/util/sql.lua index 475215bb..771df7aa 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -178,6 +178,7 @@ end local result_mt = { __index = { affected = function(self) return self.__affected; end; + rowcount = function(self) return self.__rowcount; end; } }; function engine:execute_query(sql, ...) @@ -199,7 +200,7 @@ function engine:execute_update(sql, ...) prepared[sql] = stmt; end assert(stmt:execute(...)); - return setmetatable({ __affected = stmt:affected() }, result_mt); + return setmetatable({ __affected = stmt:affected(), __rowcount = stmt:rowcount() }, result_mt); end engine.insert = engine.execute_update; engine.select = engine.execute_query; -- cgit v1.2.3 From f7acbbc7831ab1cd8f2bcdf66d284fb47d3ff04e Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Fri, 12 Jul 2013 17:44:30 +0200 Subject: util.sql: Do lazy fetching of affected/rowcount --- util/sql.lua | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'util/sql.lua') diff --git a/util/sql.lua b/util/sql.lua index 771df7aa..63c399ff 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -177,8 +177,8 @@ function engine:execute(sql, ...) end local result_mt = { __index = { - affected = function(self) return self.__affected; end; - rowcount = function(self) return self.__rowcount; end; + affected = function(self) return self.__stmt:affected(); end; + rowcount = function(self) return self.__stmt:rowcount(); end; } }; function engine:execute_query(sql, ...) @@ -200,7 +200,7 @@ function engine:execute_update(sql, ...) prepared[sql] = stmt; end assert(stmt:execute(...)); - return setmetatable({ __affected = stmt:affected(), __rowcount = stmt:rowcount() }, result_mt); + return setmetatable({ __stmt = stmt }, result_mt); end engine.insert = engine.execute_update; engine.select = engine.execute_query; -- cgit v1.2.3 From 1d833bb80779ed9c9e1d7ec6c7fab231ebf48182 Mon Sep 17 00:00:00 2001 From: Florian Zeitz Date: Fri, 9 Aug 2013 17:48:21 +0200 Subject: Remove all trailing whitespace --- util/sql.lua | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'util/sql.lua') diff --git a/util/sql.lua b/util/sql.lua index 63c399ff..b8c16e27 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -45,7 +45,7 @@ function String(n) return "String()" end }; local functions = { - + }; local cmap = { -- 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 --- util/sql.lua | 11 +++++++++++ 1 file changed, 11 insertions(+) (limited to 'util/sql.lua') 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/sql.lua') 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/sql.lua') 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/sql.lua') 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/sql.lua') 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/sql.lua') 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/sql.lua') 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 a80e00e16f382874e7caffd60b37160fc2d81f5b Mon Sep 17 00:00:00 2001 From: Matthew Wild Date: Sun, 10 Nov 2013 23:10:27 +0000 Subject: util.sql: Fix to call execute on 'self' rather than 'engine' (thanks eisensheng) --- util/sql.lua | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'util/sql.lua') diff --git a/util/sql.lua b/util/sql.lua index 735fbce8..07b1a4e0 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -313,7 +313,7 @@ function engine:set_encoding() -- to UTF-8 charset = row and row[1] or charset; end self.charset = charset; - return self:transaction(function() return engine:execute(set_names_query:format(charset)); end); + return self:transaction(function() return self:execute(set_names_query:format(charset)); end); end local engine_mt = { __index = engine }; -- cgit v1.2.3 From 95457cb25bd866e3e06cc4cf1116e8408f62b744 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Mon, 11 Nov 2013 23:09:18 +0100 Subject: util.sql: Rewrite auto increment columns to SERIAL for PostgreSQL --- util/sql.lua | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) (limited to 'util/sql.lua') diff --git a/util/sql.lua b/util/sql.lua index 07b1a4e0..a7f46421 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -264,13 +264,14 @@ function engine:_create_table(table) if col_type == "MEDIUMTEXT" and self.params.driver ~= "MySQL" then col_type = "TEXT"; -- MEDIUMTEXT is MySQL-specific end + if col.auto_increment == true and self.params.driver == "PostgreSQL" then + col_type = "BIGSERIAL"; + 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 + if self.params.driver == "MySQL" then sql = sql.." AUTO_INCREMENT"; elseif self.params.driver == "SQLite3" then sql = sql.." AUTOINCREMENT"; -- cgit v1.2.3 From a9e2cb510a760ac13bb8aeab47afdb64d19ea710 Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Tue, 12 Nov 2013 11:13:45 +0100 Subject: util.sql: Get character set info from the correct database. --- util/sql.lua | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'util/sql.lua') diff --git a/util/sql.lua b/util/sql.lua index a7f46421..5e441c10 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -308,7 +308,7 @@ function engine:set_encoding() -- to UTF-8 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;"; + return self:select"SELECT `CHARACTER_SET_NAME` FROM `CHARACTER_SETS` WHERE `information_schema`.`CHARACTER_SET_NAME` LIKE 'utf8%' ORDER BY MAXLEN DESC LIMIT 1;"; end); local row = ok and charsets(); charset = row and row[1] or charset; -- cgit v1.2.3 From 0dce82a095192a7a581511eb1455732e5a3da16c Mon Sep 17 00:00:00 2001 From: Kim Alvefur Date: Tue, 12 Nov 2013 11:38:52 +0100 Subject: util.sql: Fix previous commit --- util/sql.lua | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'util/sql.lua') diff --git a/util/sql.lua b/util/sql.lua index 5e441c10..5a1dda5d 100644 --- a/util/sql.lua +++ b/util/sql.lua @@ -308,7 +308,7 @@ function engine:set_encoding() -- to UTF-8 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 `information_schema`.`CHARACTER_SET_NAME` LIKE 'utf8%' ORDER BY MAXLEN DESC LIMIT 1;"; + return self:select"SELECT `CHARACTER_SET_NAME` FROM `information_schema`.`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; -- cgit v1.2.3