prosody
fbba2997aabb
plugins/mod_storage_sql2.lua

Find changesets by keywords by author, files, the commit message, revision number or hash, or revset expression.

Software / code / prosody

Comparison

plugins/mod_storage_sql2.lua @ 5882:fbba2997aabb

mod_storage_sql2: Move all schema upgrade code to the same place
author Kim Alvefur <zash@zash.se>
date Mon, 28 Oct 2013 21:37:30 +0100
parent 5881:12d12bda4b8c
child 5883:39b187e7e892
comparison
equal deleted inserted replaced
5881:12d12bda4b8c 5882:fbba2997aabb
62 62
63 local success,err = engine:transaction(function() 63 local success,err = engine:transaction(function()
64 engine:execute(create_sql); 64 engine:execute(create_sql);
65 engine:execute(index_sql); 65 engine:execute(index_sql);
66 end); 66 end);
67 if not success then -- so we failed to create 67
68 if params.driver == "MySQL" then
69 success,err = engine:transaction(function()
70 local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
71 if result:rowcount() > 0 then
72 module:log("info", "Upgrading database schema...");
73 engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT");
74 module:log("info", "Database table automatically upgraded");
75 end
76 return true;
77 end);
78 if not success then
79 module:log("error", "Failed to check/upgrade database schema (%s), please see "
80 .."http://prosody.im/doc/mysql for help",
81 err or "unknown error");
82 end
83 end
84 end
85 local ProsodyArchiveTable = Table { 68 local ProsodyArchiveTable = Table {
86 name="prosodyarchive"; 69 name="prosodyarchive";
87 Column { name="sort_id", type="INTEGER PRIMARY KEY AUTOINCREMENT", nullable=false }; 70 Column { name="sort_id", type="INTEGER PRIMARY KEY AUTOINCREMENT", nullable=false };
88 Column { name="host", type="TEXT", nullable=false }; 71 Column { name="host", type="TEXT", nullable=false };
89 Column { name="user", type="TEXT", nullable=false }; 72 Column { name="user", type="TEXT", nullable=false };
110 module:log("error", "Failed to set database connection encoding to UTF8: %s", err); 93 module:log("error", "Failed to set database connection encoding to UTF8: %s", err);
111 end 94 end
112 end 95 end
113 local function upgrade_table() 96 local function upgrade_table()
114 if params.driver == "MySQL" then 97 if params.driver == "MySQL" then
98 local success,err = engine:transaction(function()
99 local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
100 if result:rowcount() > 0 then
101 module:log("info", "Upgrading database schema...");
102 engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT");
103 module:log("info", "Database table automatically upgraded");
104 end
105 return true;
106 end);
107 if not success then
108 module:log("error", "Failed to check/upgrade database schema (%s), please see "
109 .."http://prosody.im/doc/mysql for help",
110 err or "unknown error");
111 return false;
112 end
115 -- COMPAT w/pre-0.9: Upgrade tables to UTF-8 if not already 113 -- COMPAT w/pre-0.9: Upgrade tables to UTF-8 if not already
116 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' );"; 114 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' );";
117 local success,err = engine:transaction(function() 115 success,err = engine:transaction(function()
118 local result = engine:execute(check_encoding_query); 116 local result = engine:execute(check_encoding_query);
119 local n_bad_columns = result:rowcount(); 117 local n_bad_columns = result:rowcount();
120 if n_bad_columns > 0 then 118 if n_bad_columns > 0 then
121 module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns); 119 module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns);
122 local fix_column_query1 = "ALTER TABLE `prosody` CHANGE `%s` `%s` BLOB;"; 120 local fix_column_query1 = "ALTER TABLE `prosody` CHANGE `%s` `%s` BLOB;";
127 engine:execute(fix_column_query2:format(column_name, column_name, column_type)); 125 engine:execute(fix_column_query2:format(column_name, column_name, column_type));
128 end 126 end
129 module:log("info", "Database encoding upgrade complete!"); 127 module:log("info", "Database encoding upgrade complete!");
130 end 128 end
131 end); 129 end);
132 local success,err = engine:transaction(function() return engine:execute(check_encoding_query); end); 130 success,err = engine:transaction(function() return engine:execute(check_encoding_query); end);
133 if not success then 131 if not success then
134 module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error"); 132 module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error");
135 end 133 end
136 end 134 end
137 end 135 end
146 assert(params.driver and params.database, "Both the SQL driver and the database need to be specified"); 144 assert(params.driver and params.database, "Both the SQL driver and the database need to be specified");
147 145
148 --local dburi = db2uri(params); 146 --local dburi = db2uri(params);
149 engine = mod_sql:create_engine(params); 147 engine = mod_sql:create_engine(params);
150 148
151 -- Encoding mess
152 set_encoding(); 149 set_encoding();
153 upgrade_table();
154 150
155 -- Automatically create table, ignore failure (table probably already exists) 151 -- Automatically create table, ignore failure (table probably already exists)
156 create_table(); 152 create_table();
153 -- Encoding mess
154 upgrade_table();
157 end 155 end
158 156
159 local function serialize(value) 157 local function serialize(value)
160 local t = type(value); 158 local t = type(value);
161 if t == "string" or t == "boolean" or t == "number" then 159 if t == "string" or t == "boolean" or t == "number" then