plugins/storage/ejabberd_init.lib.lua

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