plugins/mod_storage_sql2.lua

   1
   2 local json = require "util.json";
   3 local xml_parse = require "util.xml".parse;
   4 local resolve_relative_path = require "core.configmanager".resolve_relative_path;
   5
   6 local stanza_mt = require"util.stanza".stanza_mt;
   7 local getmetatable = getmetatable;
   8 local function is_stanza(x) return getmetatable(x) == stanza_mt; end
   9
  10 local unpack = unpack
  11 local function iterator(result)
  12         return function(result)
  13                 local row = result();
  14                 if row ~= nil then
  15                         return unpack(row);
  16                 end
  17         end, result, nil;
  18 end
  19
  20 local mod_sql = module:require("sql");
  21 local params = module:get_option("sql");
  22
  23 local engine; -- TODO create engine
  24
  25 local function create_table()
  26         --[[local Table,Column,Index = mod_sql.Table,mod_sql.Column,mod_sql.Index;
  27         local ProsodyTable = Table {
  28                 name="prosody";
  29                 Column { name="host", type="TEXT", nullable=false };
  30                 Column { name="user", type="TEXT", nullable=false };
  31                 Column { name="store", type="TEXT", nullable=false };
  32                 Column { name="key", type="TEXT", nullable=false };
  33                 Column { name="type", type="TEXT", nullable=false };
  34                 Column { name="value", type="TEXT", nullable=false };
  35                 Index { name="prosody_index", "host", "user", "store", "key" };
  36         };
  37         engine:transaction(function()
  38                 ProsodyTable:create(engine);
  39         end);]]
  40         if not module:get_option("sql_manage_tables", true) then
  41                 return;
  42         end
  43
  44         local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);";
  45         if params.driver == "PostgreSQL" then
  46                 create_sql = create_sql:gsub("`", "\"");
  47         elseif params.driver == "MySQL" then
  48                 create_sql = create_sql:gsub("`value` TEXT", "`value` MEDIUMTEXT")
  49                         :gsub(";$", " CHARACTER SET 'utf8' COLLATE 'utf8_bin';");
  50         end
  51
  52         local index_sql = "CREATE INDEX `prosody_index` ON `prosody` (`host`, `user`, `store`, `key`)";
  53         if params.driver == "PostgreSQL" then
  54                 index_sql = index_sql:gsub("`", "\"");
  55         elseif params.driver == "MySQL" then
  56                 index_sql = index_sql:gsub("`([,)])", "`(20)%1");
  57         end
  58
  59         local success,err = engine:transaction(function()
  60                 engine:execute(create_sql);
  61                 engine:execute(index_sql);
  62         end);
  63         if not success then -- so we failed to create
  64                 if params.driver == "MySQL" then
  65                         success,err = engine:transaction(function()
  66                                 local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
  67                                 if result:rowcount() > 0 then
  68                                         module:log("info", "Upgrading database schema...");
  69                                         engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT");
  70                                         module:log("info", "Database table automatically upgraded");
  71                                 end
  72                                 return true;
  73                         end);
  74                         if not success then
  75                                 module:log("error", "Failed to check/upgrade database schema (%s), please see "
  76                                         .."http://prosody.im/doc/mysql for help",
  77                                         err or "unknown error");
  78                         end
  79                 end
  80         end
  81 end
  82 local function set_encoding()
  83         if params.driver ~= "SQLite3" then
  84                 local set_names_query = "SET NAMES 'utf8';";
  85                 if params.driver == "MySQL" then
  86                         set_names_query = set_names_query:gsub(";$", " COLLATE 'utf8_bin';");
  87                 end
  88                 local success,err = engine:transaction(function() return engine:execute(set_names_query); end);
  89                 if not success then
  90                         module:log("error", "Failed to set database connection encoding to UTF8: %s", err);
  91                         return;
  92                 end
  93                 if params.driver == "MySQL" then
  94                         -- COMPAT w/pre-0.9: Upgrade tables to UTF-8 if not already
  95                         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' );";
  96                         local success,err = engine:transaction(function()
  97                                 local result = engine:execute(check_encoding_query);
  98                                 local n_bad_columns = result:rowcount();
  99                                 if n_bad_columns > 0 then
 100                                         module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns);
 101                                         local fix_column_query1 = "ALTER TABLE `prosody` CHANGE `%s` `%s` BLOB;";
 102                                         local fix_column_query2 = "ALTER TABLE `prosody` CHANGE `%s` `%s` %s CHARACTER SET 'utf8' COLLATE 'utf8_bin';";
 103                                         for row in result:rows() do
 104                                                 local column_name, column_type = unpack(row);
 105                                                 engine:execute(fix_column_query1:format(column_name, column_name));
 106                                                 engine:execute(fix_column_query2:format(column_name, column_name, column_type));
 107                                         end
 108                                         module:log("info", "Database encoding upgrade complete!");
 109                                 end
 110                         end);
 111                         local success,err = engine:transaction(function() return engine:execute(check_encoding_query); end);
 112                         if not success then
 113                                 module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error");
 114                         end
 115                 end
 116         end
 117 end
 118
 119 do -- process options to get a db connection
 120         params = params or { driver = "SQLite3" };
 121
 122         if params.driver == "SQLite3" then
 123                 params.database = resolve_relative_path(prosody.paths.data or ".", params.database or "prosody.sqlite");
 124         end
 125
 126         assert(params.driver and params.database, "Both the SQL driver and the database need to be specified");
 127
 128         --local dburi = db2uri(params);
 129         engine = mod_sql:create_engine(params);
 130
 131         -- Encoding mess
 132         set_encoding();
 133
 134         -- Automatically create table, ignore failure (table probably already exists)
 135         create_table();
 136 end
 137
 138 local function serialize(value)
 139         local t = type(value);
 140         if t == "string" or t == "boolean" or t == "number" then
 141                 return t, tostring(value);
 142         elseif is_stanza(value) then
 143                 return "xml", tostring(value);
 144         elseif t == "table" then
 145                 local value,err = json.encode(value);
 146                 if value then return "json", value; end
 147                 return nil, err;
 148         end
 149         return nil, "Unhandled value type: "..t;
 150 end
 151 local function deserialize(t, value)
 152         if t == "string" then return value;
 153         elseif t == "boolean" then
 154                 if value == "true" then return true;
 155                 elseif value == "false" then return false; end
 156         elseif t == "number" then return tonumber(value);
 157         elseif t == "json" then
 158                 return json.decode(value);
 159         elseif t == "xml" then
 160                 return xml_parse(value);
 161         end
 162 end
 163
 164 local host = module.host;
 165 local user, store;
 166
 167 local function keyval_store_get()
 168         local haveany;
 169         local result = {};
 170         for row in engine:select("SELECT `key`,`type`,`value` FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?", host, user or "", store) do
 171                 haveany = true;
 172                 local k = row[1];
 173                 local v = deserialize(row[2], row[3]);
 174                 if k and v then
 175                         if k ~= "" then result[k] = v; elseif type(v) == "table" then
 176                                 for a,b in pairs(v) do
 177                                         result[a] = b;
 178                                 end
 179                         end
 180                 end
 181         end
 182         if haveany then
 183                 return result;
 184         end
 185 end
 186 local function keyval_store_set(data)
 187         engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?", host, user or "", store);
 188
 189         if data and next(data) ~= nil then
 190                 local extradata = {};
 191                 for key, value in pairs(data) do
 192                         if type(key) == "string" and key ~= "" then
 193                                 local t, value = serialize(value);
 194                                 assert(t, value);
 195                                 engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, user or "", store, key, t, value);
 196                         else
 197                                 extradata[key] = value;
 198                         end
 199                 end
 200                 if next(extradata) ~= nil then
 201                         local t, extradata = serialize(extradata);
 202                         assert(t, extradata);
 203                         engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, user or "", store, "", t, extradata);
 204                 end
 205         end
 206         return true;
 207 end
 208
 209 local keyval_store = {};
 210 keyval_store.__index = keyval_store;
 211 function keyval_store:get(username)
 212         user,store = username,self.store;
 213         return select(2, engine:transaction(keyval_store_get));
 214 end
 215 function keyval_store:set(username, data)
 216         user,store = username,self.store;
 217         return engine:transaction(function()
 218                 return keyval_store_set(data);
 219         end);
 220 end
 221 function keyval_store:users()
 222         local ok, result = engine:transaction(function()
 223                 return engine:select("SELECT DISTINCT `user` FROM `prosody` WHERE `host`=? AND `store`=?", host, self.store);
 224         end);
 225         if not ok then return ok, result end
 226         return iterator(result);
 227 end
 228
 229 local stores = {
 230         keyval = keyval_store;
 231 };
 232
 233 local driver = {};
 234
 235 function driver:open(store, typ)
 236         local store_mt = stores[typ or "keyval"];
 237         if store_mt then
 238                 return setmetatable({ store = store }, store_mt);
 239         end
 240         return nil, "unsupported-store";
 241 end
 242
 243 function driver:stores(username)
 244         local sql = "SELECT DISTINCT `store` FROM `prosody` WHERE `host`=? AND `user`" ..
 245                 (username == true and "!=?" or "=?");
 246         if username == true or not username then
 247                 username = "";
 248         end
 249         local ok, result = engine:transaction(function()
 250                 return engine:select(sql, host, username);
 251         end);
 252         if not ok then return ok, result end
 253         return iterator(result);
 254 end
 255
 256 function driver:purge(username)
 257         return engine:transaction(function()
 258                 local stmt,err = engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=?", host, username);
 259                 return true,err;
 260         end);
 261 end
 262
 263 module:provides("storage", driver);
 264
 265