plugins/storage/ejabberd_init.lib.lua

   1
   2 local t_concat = table.concat;
   3 local t_insert = table.insert;
   4 local pairs = pairs;
   5 local DBI = require "DBI";
   6
   7 local sqlite = true;
   8 local q = {};
   9
  10 local function set(key, val)
  11 --      t_insert(q, "SET "..key.."="..val..";\n")
  12 end
  13 local function create_table(name, fields)
  14         t_insert(q, "CREATE TABLE ".."IF NOT EXISTS "..name.." (\n");
  15         for _, field in pairs(fields) do
  16                 t_insert(q, "\t");
  17                 field = t_concat(field, " ");
  18                 if sqlite then
  19                         if field:lower():match("^primary key *%(") then field = field:gsub("%(%d+%)", ""); end
  20                 end
  21                 t_insert(q, field);
  22                 if _ ~= #fields then t_insert(q, ",\n"); end
  23                 t_insert(q, "\n");
  24         end
  25         if sqlite then
  26                 t_insert(q, ");\n");
  27         else
  28                 t_insert(q, ") CHARACTER SET utf8;\n");
  29         end
  30 end
  31 local function create_index(name, index)
  32         --t_insert(q, "CREATE INDEX "..name.." ON "..index..";\n");
  33 end
  34 local function create_unique_index(name, index)
  35         --t_insert(q, "CREATE UNIQUE INDEX "..name.." ON "..index..";\n");
  36 end
  37 local function insert(target, value)
  38         t_insert(q, "INSERT INTO "..target.."\nVALUES "..value..";\n");
  39 end
  40 local function foreign_key(name, fkey, fname, fcol)
  41         t_insert(q, "ALTER TABLE `"..name.."` ADD FOREIGN KEY (`"..fkey.."`) REFERENCES `"..fname.."` (`"..fcol.."`) ON DELETE CASCADE;\n");
  42 end
  43
  44 function build_query()
  45         q = {};
  46         set('table_type', 'InnoDB');
  47         create_table('hosts', {
  48                 {'clusterid','integer','NOT','NULL'};
  49                 {'host','varchar(250)','NOT','NULL','PRIMARY','KEY'};
  50                 {'config','text','NOT','NULL'};
  51         });
  52         insert("hosts (clusterid, host, config)", "(1, 'localhost', '')");
  53         create_table('users', {
  54                 {'host','varchar(250)','NOT','NULL'};
  55                 {'username','varchar(250)','NOT','NULL'};
  56                 {'password','text','NOT','NULL'};
  57                 {'created_at','timestamp','NOT','NULL','DEFAULT','CURRENT_TIMESTAMP'};
  58                 {'PRIMARY','KEY','(host, username)'};
  59         });
  60         create_table('last', {
  61                 {'host','varchar(250)','NOT','NULL'};
  62                 {'username','varchar(250)','NOT','NULL'};
  63                 {'seconds','text','NOT','NULL'};
  64                 {'state','text','NOT','NULL'};
  65                 {'PRIMARY','KEY','(host, username)'};
  66         });
  67         create_table('rosterusers', {
  68                 {'host','varchar(250)','NOT','NULL'};
  69                 {'username','varchar(250)','NOT','NULL'};
  70                 {'jid','varchar(250)','NOT','NULL'};
  71                 {'nick','text','NOT','NULL'};
  72                 {'subscription','character(1)','NOT','NULL'};
  73                 {'ask','character(1)','NOT','NULL'};
  74                 {'askmessage','text','NOT','NULL'};
  75                 {'server','character(1)','NOT','NULL'};
  76                 {'subscribe','text','NOT','NULL'};
  77                 {'type','text'};
  78                 {'created_at','timestamp','NOT','NULL','DEFAULT','CURRENT_TIMESTAMP'};
  79                 {'PRIMARY','KEY','(host(75), username(75), jid(75))'};
  80         });
  81         create_index('i_rosteru_username', 'rosterusers(username)');
  82         create_index('i_rosteru_jid', 'rosterusers(jid)');
  83         create_table('rostergroups', {
  84                 {'host','varchar(250)','NOT','NULL'};
  85                 {'username','varchar(250)','NOT','NULL'};
  86                 {'jid','varchar(250)','NOT','NULL'};
  87                 {'grp','text','NOT','NULL'};
  88                 {'PRIMARY','KEY','(host(75), username(75), jid(75))'};
  89         });
  90         --[[create_table('spool', {
  91                 {'host','varchar(250)','NOT','NULL'};
  92                 {'username','varchar(250)','NOT','NULL'};
  93                 {'xml','text','NOT','NULL'};
  94                 {'seq','BIGINT','UNSIGNED','NOT','NULL','AUTO_INCREMENT','UNIQUE'};
  95                 {'created_at','timestamp','NOT','NULL','DEFAULT','CURRENT_TIMESTAMP'};
  96                 {'PRIMARY','KEY','(host, username, seq)'};
  97         });]]
  98         create_table('vcard', {
  99                 {'host','varchar(250)','NOT','NULL'};
 100                 {'username','varchar(250)','NOT','NULL'};
 101                 {'vcard','text','NOT','NULL'};
 102                 {'created_at','timestamp','NOT','NULL','DEFAULT','CURRENT_TIMESTAMP'};
 103                 {'PRIMARY','KEY','(host, username)'};
 104         });
 105         create_table('vcard_search', {
 106                 {'host','varchar(250)','NOT','NULL'};
 107                 {'username','varchar(250)','NOT','NULL'};
 108                 {'lusername','varchar(250)','NOT','NULL'};
 109                 {'fn','text','NOT','NULL'};
 110                 {'lfn','varchar(250)','NOT','NULL'};
 111                 {'family','text','NOT','NULL'};
 112                 {'lfamily','varchar(250)','NOT','NULL'};
 113                 {'given','text','NOT','NULL'};
 114                 {'lgiven','varchar(250)','NOT','NULL'};
 115                 {'middle','text','NOT','NULL'};
 116                 {'lmiddle','varchar(250)','NOT','NULL'};
 117                 {'nickname','text','NOT','NULL'};
 118                 {'lnickname','varchar(250)','NOT','NULL'};
 119                 {'bday','text','NOT','NULL'};
 120                 {'lbday','varchar(250)','NOT','NULL'};
 121                 {'ctry','text','NOT','NULL'};
 122                 {'lctry','varchar(250)','NOT','NULL'};
 123                 {'locality','text','NOT','NULL'};
 124                 {'llocality','varchar(250)','NOT','NULL'};
 125                 {'email','text','NOT','NULL'};
 126                 {'lemail','varchar(250)','NOT','NULL'};
 127                 {'orgname','text','NOT','NULL'};
 128                 {'lorgname','varchar(250)','NOT','NULL'};
 129                 {'orgunit','text','NOT','NULL'};
 130                 {'lorgunit','varchar(250)','NOT','NULL'};
 131                 {'PRIMARY','KEY','(host, lusername)'};
 132         });
 133         create_index('i_vcard_search_lfn      ', 'vcard_search(lfn)');
 134         create_index('i_vcard_search_lfamily  ', 'vcard_search(lfamily)');
 135         create_index('i_vcard_search_lgiven   ', 'vcard_search(lgiven)');
 136         create_index('i_vcard_search_lmiddle  ', 'vcard_search(lmiddle)');
 137         create_index('i_vcard_search_lnickname', 'vcard_search(lnickname)');
 138         create_index('i_vcard_search_lbday    ', 'vcard_search(lbday)');
 139         create_index('i_vcard_search_lctry    ', 'vcard_search(lctry)');
 140         create_index('i_vcard_search_llocality', 'vcard_search(llocality)');
 141         create_index('i_vcard_search_lemail   ', 'vcard_search(lemail)');
 142         create_index('i_vcard_search_lorgname ', 'vcard_search(lorgname)');
 143         create_index('i_vcard_search_lorgunit ', 'vcard_search(lorgunit)');
 144         create_table('privacy_default_list', {
 145                 {'host','varchar(250)','NOT','NULL'};
 146                 {'username','varchar(250)'};
 147                 {'name','varchar(250)','NOT','NULL'};
 148                 {'PRIMARY','KEY','(host, username)'};
 149         });
 150         --[[create_table('privacy_list', {
 151                 {'host','varchar(250)','NOT','NULL'};
 152                 {'username','varchar(250)','NOT','NULL'};
 153                 {'name','varchar(250)','NOT','NULL'};
 154                 {'id','BIGINT','UNSIGNED','NOT','NULL','AUTO_INCREMENT','UNIQUE'};
 155                 {'created_at','timestamp','NOT','NULL','DEFAULT','CURRENT_TIMESTAMP'};
 156                 {'PRIMARY','KEY','(host, username, name)'};
 157         });]]
 158         create_table('privacy_list_data', {
 159                 {'id','bigint'};
 160                 {'t','character(1)','NOT','NULL'};
 161                 {'value','text','NOT','NULL'};
 162                 {'action','character(1)','NOT','NULL'};
 163                 {'ord','NUMERIC','NOT','NULL'};
 164                 {'match_all','boolean','NOT','NULL'};
 165                 {'match_iq','boolean','NOT','NULL'};
 166                 {'match_message','boolean','NOT','NULL'};
 167                 {'match_presence_in','boolean','NOT','NULL'};
 168                 {'match_presence_out','boolean','NOT','NULL'};
 169         });
 170         create_table('private_storage', {
 171                 {'host','varchar(250)','NOT','NULL'};
 172                 {'username','varchar(250)','NOT','NULL'};
 173                 {'namespace','varchar(250)','NOT','NULL'};
 174                 {'data','text','NOT','NULL'};
 175                 {'created_at','timestamp','NOT','NULL','DEFAULT','CURRENT_TIMESTAMP'};
 176                 {'PRIMARY','KEY','(host(75), username(75), namespace(75))'};
 177         });
 178         create_index('i_private_storage_username USING BTREE', 'private_storage(username)');
 179         create_table('roster_version', {
 180                 {'username','varchar(250)','PRIMARY','KEY'};
 181                 {'version','text','NOT','NULL'};
 182         });
 183         --[[create_table('pubsub_node', {
 184                 {'host','text'};
 185                 {'node','text'};
 186                 {'parent','text'};
 187                 {'type','text'};
 188                 {'nodeid','bigint','auto_increment','primary','key'};
 189         });
 190         create_index('i_pubsub_node_parent', 'pubsub_node(parent(120))');
 191         create_unique_index('i_pubsub_node_tuple', 'pubsub_node(host(20), node(120))');
 192         create_table('pubsub_node_option', {
 193                 {'nodeid','bigint'};
 194                 {'name','text'};
 195                 {'val','text'};
 196         });
 197         create_index('i_pubsub_node_option_nodeid', 'pubsub_node_option(nodeid)');
 198         foreign_key('pubsub_node_option', 'nodeid', 'pubsub_node', 'nodeid');
 199         create_table('pubsub_node_owner', {
 200                 {'nodeid','bigint'};
 201                 {'owner','text'};
 202         });
 203         create_index('i_pubsub_node_owner_nodeid', 'pubsub_node_owner(nodeid)');
 204         foreign_key('pubsub_node_owner', 'nodeid', 'pubsub_node', 'nodeid');
 205         create_table('pubsub_state', {
 206                 {'nodeid','bigint'};
 207                 {'jid','text'};
 208                 {'affiliation','character(1)'};
 209                 {'subscriptions','text'};
 210                 {'stateid','bigint','auto_increment','primary','key'};
 211         });
 212         create_index('i_pubsub_state_jid', 'pubsub_state(jid(60))');
 213         create_unique_index('i_pubsub_state_tuple', 'pubsub_state(nodeid, jid(60))');
 214         foreign_key('pubsub_state', 'nodeid', 'pubsub_node', 'nodeid');
 215         create_table('pubsub_item', {
 216                 {'nodeid','bigint'};
 217                 {'itemid','text'};
 218                 {'publisher','text'};
 219                 {'creation','text'};
 220                 {'modification','text'};
 221                 {'payload','text'};
 222         });
 223         create_index('i_pubsub_item_itemid', 'pubsub_item(itemid(36))');
 224         create_unique_index('i_pubsub_item_tuple', 'pubsub_item(nodeid, itemid(36))');
 225         foreign_key('pubsub_item', 'nodeid', 'pubsub_node', 'nodeid');
 226         create_table('pubsub_subscription_opt', {
 227                 {'subid','text'};
 228                 {'opt_name','varchar(32)'};
 229                 {'opt_value','text'};
 230         });
 231         create_unique_index('i_pubsub_subscription_opt', 'pubsub_subscription_opt(subid(32), opt_name(32))');]]
 232         return t_concat(q);
 233 end
 234
 235 local function init(dbh)
 236         local q = build_query();
 237         for statement in q:gmatch("[^;]*;") do
 238                 statement = statement:gsub("\n", ""):gsub("\t", " ");
 239                 if sqlite then
 240                         statement = statement:gsub("AUTO_INCREMENT", "AUTOINCREMENT");
 241                         statement = statement:gsub("auto_increment", "autoincrement");
 242                 end
 243                 local result, err = DBI.Do(dbh, statement);
 244                 if not result then
 245                         print("X", result, err);
 246                         print("Y", statement);
 247                 end
 248         end
 249 end
 250
 251 local _M = { init = init };
 252 return _M;