Mercurial > prosody-hg

comparison plugins/mod_storage_sql2.lua @ 6761:b20efae224c9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
mod_storage_sql2: Upgrade table if its charset does not match our connection's charset (thanks Zash)
author Matthew Wild <mwild1@gmail.com>
date Wed, 08 Jul 2015 15:07:23 +0100
parents fc9c1a566a19
children d01c29b62b16
comparison
equal deleted inserted replaced
6760:e45a58c72609 6761:b20efae224c9
349 err or "unknown error"); 349 err or "unknown error");
350 return false; 350 return false;
351 end 351 end
352 352
353 -- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already 353 -- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already
354 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' );"; 354 local check_encoding_query = "SELECT `COLUMN_NAME`,`COLUMN_TYPE`,`TABLE_NAME` FROM `information_schema`.`columns` WHERE `TABLE_NAME` LIKE 'prosody%%' AND ( `CHARACTER_SET_NAME`!='%s' OR `COLLATION_NAME`!='%s_bin' );";
355 check_encoding_query = check_encoding_query:format(engine.charset, engine.charset);
355 success,err = engine:transaction(function() 356 success,err = engine:transaction(function()
356 local result = engine:execute(check_encoding_query); 357 local result = engine:execute(check_encoding_query);
357 local n_bad_columns = result:rowcount(); 358 local n_bad_columns = result:rowcount();
358 if n_bad_columns > 0 then 359 if n_bad_columns > 0 then
359 changes = true; 360 changes = true;
360 if apply_changes then 361 if apply_changes then
361 module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns); 362 module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns);
362 local fix_column_query1 = "ALTER TABLE `prosody` CHANGE `%s` `%s` BLOB;"; 363 local fix_column_query1 = "ALTER TABLE `%s` CHANGE `%s` `%s` BLOB;";
363 local fix_column_query2 = "ALTER TABLE `prosody` CHANGE `%s` `%s` %s CHARACTER SET 'utf8' COLLATE 'utf8_bin';"; 364 local fix_column_query2 = "ALTER TABLE `%s` CHANGE `%s` `%s` %s CHARACTER SET '%s' COLLATE '%s_bin';";
364 for row in result:rows() do 365 for row in result:rows() do
365 local column_name, column_type = unpack(row); 366 local column_name, column_type, table_name = unpack(row);
366 engine:execute(fix_column_query1:format(column_name, column_name)); 367 module:log("debug", "Fixing column %s in table %s", column_name, table_name);
367 engine:execute(fix_column_query2:format(column_name, column_name, column_type)); 368 engine:execute(fix_column_query1:format(table_name, column_name, column_name));
369 engine:execute(fix_column_query2:format(table_name, column_name, column_name, column_type, engine.charset, engine.charset));
368 end 370 end
369 module:log("info", "Database encoding upgrade complete!"); 371 module:log("info", "Database encoding upgrade complete!");
370 end 372 end
371 end 373 end
372 end); 374 end);