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
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
# $RCSfile$
# $Revision$
# $Date$
CREATE TABLE jiveUser (
username VARCHAR(32) NOT NULL,
password VARCHAR(32) NOT NULL,
name VARCHAR(100),
email VARCHAR(100),
creationDate CHAR(15) NOT NULL,
modificationDate CHAR(15) NOT NULL,
PRIMARY KEY (username),
INDEX jiveUser_cDate_idx (creationDate)
);
CREATE TABLE jiveUserProp (
username VARCHAR(32) NOT NULL,
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
PRIMARY KEY (username, name)
);
CREATE TABLE jiveGroup (
groupName VARCHAR(50) NOT NULL,
description VARCHAR(255),
PRIMARY KEY (groupName)
);
CREATE TABLE jiveGroupProp (
groupName VARCHAR(50) NOT NULL,
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
PRIMARY KEY (groupName, name)
);
CREATE TABLE jiveGroupUser (
groupName VARCHAR(50) NOT NULL,
username VARCHAR(32) NOT NULL,
administrator TINYINT NOT NULL,
PRIMARY KEY (groupName, username, administrator)
);
CREATE TABLE jivePrivate (
username VARCHAR(32) NOT NULL,
name VARCHAR(100) NOT NULL,
namespace VARCHAR(200) NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY (username, name, namespace)
);
CREATE TABLE jiveOffline (
username VARCHAR(32) NOT NULL,
messageID BIGINT NOT NULL,
creationDate CHAR(15) NOT NULL,
messageSize INTEGER NOT NULL,
message TEXT NOT NULL,
PRIMARY KEY (username, messageID)
);
CREATE TABLE jiveRoster (
rosterID BIGINT NOT NULL,
username VARCHAR(32) NOT NULL,
jid TEXT NOT NULL,
sub TINYINT NOT NULL,
ask TINYINT NOT NULL,
recv TINYINT NOT NULL,
nick VARCHAR(255),
PRIMARY KEY (rosterID),
INDEX jiveRoster_unameid_idx (username)
);
CREATE TABLE jiveRosterGroups (
rosterID BIGINT NOT NULL,
rank TINYINT NOT NULL,
groupName VARCHAR(255) NOT NULL,
PRIMARY KEY (rosterID, rank),
INDEX jiveRosterGroup_rosterid_idx (rosterID)
);
CREATE TABLE jiveVCard (
username VARCHAR(32) NOT NULL,
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
PRIMARY KEY (username, name)
);
CREATE TABLE jiveID (
idType INTEGER NOT NULL,
id BIGINT NOT NULL,
PRIMARY KEY (idType)
);
CREATE TABLE jiveProperty (
name VARCHAR(100) NOT NULL,
propValue TEXT NOT NULL,
PRIMARY KEY (name)
);
CREATE TABLE jiveVersion (
majorVersion INTEGER NOT NULL,
minorVersion INTEGER NOT NULL
);
# MUC Tables
CREATE TABLE mucRoom (
roomID BIGINT NOT NULL,
creationDate CHAR(15) NOT NULL,
modificationDate CHAR(15) NOT NULL,
name VARCHAR(50) NOT NULL,
naturalName VARCHAR(255) NOT NULL,
description VARCHAR(255),
lockedDate CHAR(15) NOT NULL,
emptyDate CHAR(15) NULL,
canChangeSubject TINYINT NOT NULL,
maxUsers INTEGER NOT NULL,
publicRoom TINYINT NOT NULL,
moderated TINYINT NOT NULL,
membersOnly TINYINT NOT NULL,
canInvite TINYINT NOT NULL,
password VARCHAR(50) NULL,
canDiscoverJID TINYINT NOT NULL,
logEnabled TINYINT NOT NULL,
subject VARCHAR(100) NULL,
rolesToBroadcast TINYINT NOT NULL,
PRIMARY KEY (name),
INDEX mucRoom_roomid_idx (roomID)
);
CREATE TABLE mucAffiliation (
roomID BIGINT NOT NULL,
jid TEXT NOT NULL,
affiliation TINYINT NOT NULL,
PRIMARY KEY (roomID,jid(70))
);
CREATE TABLE mucMember (
roomID BIGINT NOT NULL,
jid TEXT NOT NULL,
nickname VARCHAR(255) NULL,
firstName VARCHAR(100) NULL,
lastName VARCHAR(100) NULL,
url VARCHAR(100) NULL,
email VARCHAR(100) NULL,
faqentry VARCHAR(100) NULL,
PRIMARY KEY (roomID,jid(70))
);
CREATE TABLE mucConversationLog (
roomID BIGINT NOT NULL,
sender TEXT NOT NULL,
nickname VARCHAR(255) NULL,
time CHAR(15) NOT NULL,
subject VARCHAR(255) NULL,
body TEXT NULL,
INDEX mucLog_time_idx (time)
);
# Finally, insert default table values.
INSERT INTO jiveID (idType, id) VALUES (18, 1);
INSERT INTO jiveID (idType, id) VALUES (19, 1);
INSERT INTO jiveID (idType, id) VALUES (23, 1);
INSERT INTO jiveVersion (majorVersion, minorVersion) VALUES (2, 1);
# Entry for admin user
INSERT INTO jiveUser (username, password, name, email, creationDate, modificationDate)
VALUES ('admin', 'admin', 'Administrator', 'admin@example.com', '0', '0');