Mercurial > prosody-hg

comparison tools/migration/migrator/prosody_sql.lua @ 8073:7361412a9664

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
author Kim Alvefur <zash@zash.se>
date Mon, 10 Apr 2017 23:13:39 +0200
parents 5eec340c75fb
children 4b403f881176
comparison
equal deleted inserted replaced
8072:194409dcba22 8073:7361412a9664
90 local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'"); 90 local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
91 assert(result:rowcount() == 0); 91 assert(result:rowcount() == 0);
92 92
93 -- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already 93 -- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already
94 local check_encoding_query = [[ 94 local check_encoding_query = [[
95 SELECT `COLUMN_NAME`,`COLUMN_TYPE`,`TABLE_NAME` 95 SELECT "COLUMN_NAME","COLUMN_TYPE","TABLE_NAME"
96 FROM `information_schema`.`columns` 96 FROM "information_schema"."columns"
97 WHERE `TABLE_NAME` LIKE 'prosody%%' AND ( `CHARACTER_SET_NAME`!='%s' OR `COLLATION_NAME`!='%s_bin' ); 97 WHERE "TABLE_NAME" LIKE 'prosody%%' AND ( "CHARACTER_SET_NAME"!='%s' OR "COLLATION_NAME"!='%s_bin' );
98 ]]; 98 ]];
99 check_encoding_query = check_encoding_query:format(engine.charset, engine.charset); 99 check_encoding_query = check_encoding_query:format(engine.charset, engine.charset);
100 local result = engine:execute(check_encoding_query); 100 local result = engine:execute(check_encoding_query);
101 assert(result:rowcount() == 0) 101 assert(result:rowcount() == 0)
102 end); 102 end);
114 error("Old database format detected. Please run: prosodyctl mod_storage_sql upgrade"); 114 error("Old database format detected. Please run: prosodyctl mod_storage_sql upgrade");
115 end 115 end
116 end)); 116 end));
117 local keys = {"host", "user", "store", "key", "type", "value"}; 117 local keys = {"host", "user", "store", "key", "type", "value"};
118 assert(engine:connect()); 118 assert(engine:connect());
119 local f,s,val = assert(engine:select("SELECT `host`, `user`, `store`, `key`, `type`, `value` FROM `prosody`;")); 119 local f,s,val = assert(engine:select("SELECT \"host\", \"user\", \"store\", \"key\", \"type\", \"value\" FROM \"prosody\";"));
120 -- get SQL rows, sorted 120 -- get SQL rows, sorted
121 local iter = mtools.sorted { 121 local iter = mtools.sorted {
122 reader = function() val = f(s, val); return val; end; 122 reader = function() val = f(s, val); return val; end;
123 filter = function(x) 123 filter = function(x)
124 for i=1,#keys do 124 for i=1,#keys do
155 error("Old database format detected. Please run: prosodyctl mod_storage_sql upgrade"); 155 error("Old database format detected. Please run: prosodyctl mod_storage_sql upgrade");
156 end 156 end
157 create_table(engine); 157 create_table(engine);
158 end)); 158 end));
159 assert(engine:connect()); 159 assert(engine:connect());
160 assert(engine:delete("DELETE FROM prosody")); 160 assert(engine:delete("DELETE FROM \"prosody\""));
161 local insert_sql = "INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)"; 161 local insert_sql = "INSERT INTO \"prosody\" (\"host\",\"user\",\"store\",\"key\",\"type\",\"value\") VALUES (?,?,?,?,?,?)";
162 162
163 return function(item) 163 return function(item)
164 if not item then assert(engine.conn:commit()) return end -- end of input 164 if not item then assert(engine.conn:commit()) return end -- end of input
165 local host = item.host or ""; 165 local host = item.host or "";
166 local user = item.user or ""; 166 local user = item.user or "";