1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
--
-- ejabberd, Copyright (C) 2002-2008 ProcessOne
--
-- This program is free software; you can redistribute it and/or
-- modify it under the terms of the GNU General Public License as
-- published by the Free Software Foundation; either version 2 of the
-- License, or (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-- General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
-- 02111-1307 USA
--
-- Needs MySQL (at least 4.0.x) with innodb back-end
SET table_type=InnoDB;
CREATE TABLE users (
username varchar(250) PRIMARY KEY,
password text NOT NULL
) CHARACTER SET utf8;
CREATE TABLE last (
username varchar(250) PRIMARY KEY,
seconds text NOT NULL,
state text NOT NULl
) CHARACTER SET utf8;
CREATE TABLE rosterusers (
username varchar(250) NOT NULL,
jid varchar(250) NOT NULL,
nick text NOT NULL,
subscription character(1) NOT NULL,
ask character(1) NOT NULL,
askmessage text NOT NULL,
server character(1) NOT NULL,
subscribe text NOT NULL,
type text
) CHARACTER SET utf8;
CREATE UNIQUE INDEX i_rosteru_user_jid ON rosterusers(username(75), jid(75));
CREATE INDEX i_rosteru_username ON rosterusers(username);
CREATE INDEX i_rosteru_jid ON rosterusers(jid);
CREATE TABLE rostergroups (
username varchar(250) NOT NULL,
jid varchar(250) NOT NULL,
grp text NOT NULL
) CHARACTER SET utf8;
CREATE INDEX pk_rosterg_user_jid ON rostergroups(username(75), jid(75));
CREATE TABLE spool (
username varchar(250) NOT NULL,
xml text NOT NULL,
seq BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
) CHARACTER SET utf8;
CREATE INDEX i_despool USING BTREE ON spool(username);
CREATE TABLE vcard (
username varchar(250) PRIMARY KEY,
vcard text NOT NULL
) CHARACTER SET utf8;
CREATE TABLE vcard_search (
username varchar(250) NOT NULL,
lusername varchar(250) PRIMARY KEY,
fn text NOT NULL,
lfn varchar(250) NOT NULL,
family text NOT NULL,
lfamily varchar(250) NOT NULL,
given text NOT NULL,
lgiven varchar(250) NOT NULL,
middle text NOT NULL,
lmiddle varchar(250) NOT NULL,
nickname text NOT NULL,
lnickname varchar(250) NOT NULL,
bday text NOT NULL,
lbday varchar(250) NOT NULL,
ctry text NOT NULL,
lctry varchar(250) NOT NULL,
locality text NOT NULL,
llocality varchar(250) NOT NULL,
email text NOT NULL,
lemail varchar(250) NOT NULL,
orgname text NOT NULL,
lorgname varchar(250) NOT NULL,
orgunit text NOT NULL,
lorgunit varchar(250) NOT NULL
) CHARACTER SET utf8;
CREATE INDEX i_vcard_search_lfn ON vcard_search(lfn);
CREATE INDEX i_vcard_search_lfamily ON vcard_search(lfamily);
CREATE INDEX i_vcard_search_lgiven ON vcard_search(lgiven);
CREATE INDEX i_vcard_search_lmiddle ON vcard_search(lmiddle);
CREATE INDEX i_vcard_search_lnickname ON vcard_search(lnickname);
CREATE INDEX i_vcard_search_lbday ON vcard_search(lbday);
CREATE INDEX i_vcard_search_lctry ON vcard_search(lctry);
CREATE INDEX i_vcard_search_llocality ON vcard_search(llocality);
CREATE INDEX i_vcard_search_lemail ON vcard_search(lemail);
CREATE INDEX i_vcard_search_lorgname ON vcard_search(lorgname);
CREATE INDEX i_vcard_search_lorgunit ON vcard_search(lorgunit);
CREATE TABLE privacy_default_list (
username varchar(250) PRIMARY KEY,
name varchar(250) NOT NULL
) CHARACTER SET utf8;
CREATE TABLE privacy_list (
username varchar(250) NOT NULL,
name varchar(250) NOT NULL,
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE
) CHARACTER SET utf8;
CREATE INDEX i_privacy_list_username USING BTREE ON privacy_list(username);
CREATE UNIQUE INDEX i_privacy_list_username_name USING BTREE ON privacy_list (username(75), name(75));
CREATE TABLE privacy_list_data (
id bigint,
t character(1) NOT NULL,
value text NOT NULL,
action character(1) NOT NULL,
ord NUMERIC NOT NULL,
match_all boolean NOT NULL,
match_iq boolean NOT NULL,
match_message boolean NOT NULL,
match_presence_in boolean NOT NULL,
match_presence_out boolean NOT NULL
) CHARACTER SET utf8;
CREATE TABLE private_storage (
username varchar(250) NOT NULL,
namespace varchar(250) NOT NULL,
data text NOT NULL
) CHARACTER SET utf8;
CREATE INDEX i_private_storage_username USING BTREE ON private_storage(username);
CREATE UNIQUE INDEX i_private_storage_username_namespace USING BTREE ON private_storage(username(75), namespace(75));
--- To update from 1.x:
-- ALTER TABLE rosterusers ADD COLUMN askmessage text AFTER ask;
-- UPDATE rosterusers SET askmessage = '';
-- ALTER TABLE rosterusers ALTER COLUMN askmessage SET NOT NULL;
|
