1 2CREATE TABLE ofUser ( 3 username VARCHAR2(64) NOT NULL, 4 storedKey VARCHAR(32), 5 serverKey VARCHAR(32), 6 salt VARCHAR(32), 7 iterations INTEGER, 8 plainPassword VARCHAR2(32), 9 encryptedPassword VARCHAR2(255), 10 name VARCHAR2(100), 11 email VARCHAR2(100), 12 creationDate CHAR(15) NOT NULL, 13 modificationDate CHAR(15) NOT NULL, 14 CONSTRAINT ofUser_pk PRIMARY KEY (username) 15); 16CREATE INDEX ofUser_cDate_idx ON ofUser (creationDate ASC); 17 18 19CREATE TABLE ofUserProp ( 20 username VARCHAR2(64) NOT NULL, 21 name VARCHAR2(100) NOT NULL, 22 propValue VARCHAR2(1024) NOT NULL, 23 CONSTRAINT ofUserProp_pk PRIMARY KEY (username, name) 24); 25 26 27CREATE TABLE ofUserFlag ( 28 username VARCHAR2(64) NOT NULL, 29 name VARCHAR2(100) NOT NULL, 30 startTime CHAR(15), 31 endTime CHAR(15), 32 CONSTRAINT ofUserFlag_pk PRIMARY KEY (username, name) 33); 34CREATE INDEX ofUserFlag_sTime_idx ON ofUserFlag (startTime ASC); 35CREATE INDEX ofUserFlag_eTime_idx ON ofUserFlag (endTime ASC); 36 37 38CREATE TABLE ofOffline ( 39 username VARCHAR2(64) NOT NULL, 40 messageID INTEGER NOT NULL, 41 creationDate CHAR(15) NOT NULL, 42 messageSize INTEGER NOT NULL, 43 stanza LONG NOT NULL, 44 CONSTRAINT ofOffline_pk PRIMARY KEY (username, messageID) 45); 46 47 48CREATE TABLE ofPresence ( 49 username VARCHAR2(64) NOT NULL, 50 offlinePresence LONG, 51 offlineDate CHAR(15) NOT NULL, 52 CONSTRAINT ofPresence_pk PRIMARY KEY (username) 53); 54 55 56CREATE TABLE ofRoster ( 57 rosterID INTEGER NOT NULL, 58 username VARCHAR2(64) NOT NULL, 59 jid VARCHAR2(1024) NOT NULL, 60 sub INTEGER NOT NULL, 61 ask INTEGER NOT NULL, 62 recv INTEGER NOT NULL, 63 nick VARCHAR2(255), 64 stanza VARCHAR2(4000), 65 CONSTRAINT ofRoster_pk PRIMARY KEY (rosterID) 66); 67CREATE INDEX ofRoster_username_idx ON ofRoster (username ASC); 68CREATE INDEX ofRoster_jid_idx ON ofRoster (jid ASC); 69 70 71CREATE TABLE ofRosterGroups ( 72 rosterID INTEGER NOT NULL, 73 rank INTEGER NOT NULL, 74 groupName VARCHAR2(255) NOT NULL, 75 CONSTRAINT ofRosterGroups_pk PRIMARY KEY (rosterID, rank) 76); 77CREATE INDEX ofRosterGroup_rosterid_idx ON ofRosterGroups (rosterID ASC); 78ALTER TABLE ofRosterGroups ADD CONSTRAINT ofRosterGroups_rosterID_fk FOREIGN KEY (rosterID) REFERENCES ofRoster INITIALLY DEFERRED DEFERRABLE; 79 80 81CREATE TABLE ofVCard ( 82 username VARCHAR2(64) NOT NULL, 83 vcard LONG NOT NULL, 84 CONSTRAINT ofVCard_pk PRIMARY KEY (username) 85); 86 87CREATE TABLE ofGroup ( 88 groupName VARCHAR2(50) NOT NULL, 89 description VARCHAR2(255), 90 CONSTRAINT ofGroup_pk PRIMARY KEY (groupName) 91); 92 93CREATE TABLE ofGroupProp ( 94 groupName VARCHAR(50) NOT NULL, 95 name VARCHAR2(100) NOT NULL, 96 propValue VARCHAR2(4000) NULL, 97 CONSTRAINT ofGroupProp_pk PRIMARY KEY (groupName, name) 98); 99 100CREATE TABLE ofGroupUser ( 101 groupName VARCHAR(50) NOT NULL, 102 username VARCHAR2(100) NOT NULL, 103 administrator INTEGER NOT NULL, 104 CONSTRAINT ofGroupUser_pk PRIMARY KEY (groupName, username, administrator) 105); 106 107CREATE TABLE ofID ( 108 idType INTEGER NOT NULL, 109 id INTEGER NOT NULL, 110 CONSTRAINT ofID_pk PRIMARY KEY (idType) 111); 112 113CREATE TABLE ofProperty ( 114 name VARCHAR2(100) NOT NULL, 115 propValue VARCHAR2(4000) NOT NULL, 116 encrypted INTEGER, 117 iv CHAR(24), 118 CONSTRAINT ofProperty_pk PRIMARY KEY (name) 119); 120 121CREATE TABLE ofVersion ( 122 name VARCHAR2(50) NOT NULL, 123 version INTEGER NOT NULL, 124 CONSTRAINT ofVersion_pk PRIMARY KEY (name) 125); 126 127CREATE TABLE ofExtComponentConf ( 128 subdomain VARCHAR2(255) NOT NULL, 129 wildcard INTEGER NOT NULL, 130 secret VARCHAR2(255), 131 permission VARCHAR2(10) NOT NULL, 132 CONSTRAINT ofExtComponentConf_pk PRIMARY KEY (subdomain) 133); 134 135CREATE TABLE ofRemoteServerConf ( 136 xmppDomain VARCHAR2(255) NOT NULL, 137 remotePort INTEGER, 138 permission VARCHAR2(10) NOT NULL, 139 CONSTRAINT ofRemoteServerConf_pk PRIMARY KEY (xmppDomain) 140); 141 142CREATE TABLE ofPrivacyList ( 143 username VARCHAR2(64) NOT NULL, 144 name VARCHAR2(100) NOT NULL, 145 isDefault INTEGER NOT NULL, 146 list LONG NOT NULL, 147 CONSTRAINT ofPrivacyList_pk PRIMARY KEY (username, name) 148); 149CREATE INDEX ofPrivacyList_default_idx ON ofPrivacyList (username, isDefault); 150 151CREATE TABLE ofSASLAuthorized ( 152 username VARCHAR(64) NOT NULL, 153 principal VARCHAR(4000) NOT NULL, 154 CONSTRAINT ofSASLAuthorized_pk PRIMARY KEY (username, principal) 155); 156 157CREATE TABLE ofSecurityAuditLog ( 158 msgID INTEGER NOT NULL, 159 username VARCHAR2(64) NOT NULL, 160 entryStamp INTEGER NOT NULL, 161 summary VARCHAR2(255) NOT NULL, 162 node VARCHAR2(255) NOT NULL, 163 details VARCHAR2(4000), 164 CONSTRAINT ofSecurityAuditLog_pk PRIMARY KEY (msgID) 165); 166CREATE INDEX ofSecurityAuditLog_tstamp_idx ON ofSecurityAuditLog (entryStamp); 167CREATE INDEX ofSecurityAuditLog_uname_idx ON ofSecurityAuditLog (username); 168 169-- MUC Tables 170 171CREATE TABLE ofMucService ( 172 serviceID INT NOT NULL, 173 subdomain VARCHAR2(255) NOT NULL, 174 description VARCHAR2(255), 175 isHidden INTEGER NOT NULL, 176 CONSTRAINT ofMucService_pk PRIMARY KEY (subdomain) 177); 178CREATE INDEX ofMucService_serviceid_idx ON ofMucService(serviceID); 179 180CREATE TABLE ofMucServiceProp ( 181 serviceID INT NOT NULL, 182 name VARCHAR2(100) NOT NULL, 183 propValue VARCHAR2(1024) NOT NULL, 184 CONSTRAINT ofMucServiceProp_pk PRIMARY KEY (serviceID, name) 185); 186 187CREATE TABLE ofMucRoom( 188 serviceID INT NOT NULL, 189 roomID INT NOT NULL, 190 creationDate CHAR(15) NOT NULL, 191 modificationDate CHAR(15) NOT NULL, 192 name VARCHAR2(50) NOT NULL, 193 naturalName VARCHAR2(255) NOT NULL, 194 description VARCHAR2(255), 195 lockedDate CHAR(15) NOT NULL, 196 emptyDate CHAR(15) NULL, 197 canChangeSubject INTEGER NOT NULL, 198 maxUsers INTEGER NOT NULL, 199 publicRoom INTEGER NOT NULL, 200 moderated INTEGER NOT NULL, 201 membersOnly INTEGER NOT NULL, 202 canInvite INTEGER NOT NULL, 203 roomPassword VARCHAR2(50) NULL, 204 canDiscoverJID INTEGER NOT NULL, 205 logEnabled INTEGER NOT NULL, 206 subject VARCHAR2(100) NULL, 207 rolesToBroadcast INTEGER NOT NULL, 208 useReservedNick INTEGER NOT NULL, 209 canChangeNick INTEGER NOT NULL, 210 canRegister INTEGER NOT NULL, 211 allowpm INTEGER NULL, 212 fmucEnabled INTEGER NULL, 213 fmucOutboundNode VARCHAR2(255) NULL, 214 fmucOutboundMode INTEGER NULL, 215 fmucInboundNodes VARCHAR2(4000) NULL, 216 CONSTRAINT ofMucRoom_pk PRIMARY KEY (serviceID, name) 217); 218CREATE INDEX ofMucRoom_roomid_idx ON ofMucRoom (roomID); 219CREATE INDEX ofMucRoom_serviceid_idx ON ofMucRoom (serviceID); 220 221CREATE TABLE ofMucRoomProp ( 222 roomID INT NOT NULL, 223 name VARCHAR2(100) NOT NULL, 224 propValue VARCHAR2(1024) NOT NULL, 225 CONSTRAINT ofMucRoomProp_pk PRIMARY KEY (roomID, name) 226); 227 228CREATE TABLE ofMucAffiliation ( 229 roomID INT NOT NULL, 230 jid VARCHAR2(1024) NOT NULL, 231 affiliation INTEGER NOT NULL, 232 CONSTRAINT ofMucAffiliation_pk PRIMARY KEY (roomID, jid) 233); 234 235CREATE TABLE ofMucMember ( 236 roomID INT NOT NULL, 237 jid VARCHAR2(1024) NOT NULL, 238 nickname VARCHAR2(255) NULL, 239 firstName VARCHAR2(100) NULL, 240 lastName VARCHAR2(100) NULL, 241 url VARCHAR2(100) NULL, 242 email VARCHAR2(100) NULL, 243 faqentry VARCHAR2(100) NULL, 244 CONSTRAINT ofMucMember_pk PRIMARY KEY (roomID, jid) 245); 246 247CREATE TABLE ofMucConversationLog ( 248 roomID INT NOT NULL, 249 messageID INT NOT NULL, 250 sender VARCHAR2(1024) NOT NULL, 251 nickname VARCHAR2(255) NULL, 252 logTime CHAR(15) NOT NULL, 253 subject VARCHAR2(255) NULL, 254 body VARCHAR2(4000) NULL, 255 stanza VARCHAR2(4000) NULL 256); 257CREATE INDEX ofMucConversationLog_time_idx ON ofMucConversationLog (logTime); 258CREATE INDEX ofMucConversationLog_msg_id ON ofMucConversationLog (messageID); 259 260 261-- PubSub Tables 262 263CREATE TABLE ofPubsubNode ( 264 serviceID VARCHAR2(100) NOT NULL, 265 nodeID VARCHAR2(100) NOT NULL, 266 leaf INTEGER NOT NULL, 267 creationDate CHAR(15) NOT NULL, 268 modificationDate CHAR(15) NOT NULL, 269 parent VARCHAR2(100) NULL, 270 deliverPayloads INTEGER NOT NULL, 271 maxPayloadSize INTEGER NULL, 272 persistItems INTEGER NULL, 273 maxItems INTEGER NULL, 274 notifyConfigChanges INTEGER NOT NULL, 275 notifyDelete INTEGER NOT NULL, 276 notifyRetract INTEGER NOT NULL, 277 presenceBased INTEGER NOT NULL, 278 sendItemSubscribe INTEGER NOT NULL, 279 publisherModel VARCHAR2(15) NOT NULL, 280 subscriptionEnabled INTEGER NOT NULL, 281 configSubscription INTEGER NOT NULL, 282 accessModel VARCHAR2(10) NOT NULL, 283 payloadType VARCHAR2(100) NULL, 284 bodyXSLT VARCHAR2(100) NULL, 285 dataformXSLT VARCHAR2(100) NULL, 286 creator VARCHAR2(1024) NOT NULL, 287 description VARCHAR2(255) NULL, 288 language VARCHAR2(255) NULL, 289 name VARCHAR2(50) NULL, 290 replyPolicy VARCHAR2(15) NULL, 291 associationPolicy VARCHAR2(15) NULL, 292 maxLeafNodes INTEGER NULL, 293 CONSTRAINT ofPubsubNode_pk PRIMARY KEY (serviceID, nodeID) 294); 295 296CREATE TABLE ofPubsubNodeJIDs ( 297 serviceID VARCHAR2(100) NOT NULL, 298 nodeID VARCHAR2(100) NOT NULL, 299 jid VARCHAR2(1024) NOT NULL, 300 associationType VARCHAR2(20) NOT NULL, 301 CONSTRAINT ofPubsubNodeJIDs_pk PRIMARY KEY (serviceID, nodeID, jid) 302); 303 304CREATE TABLE ofPubsubNodeGroups ( 305 serviceID VARCHAR2(100) NOT NULL, 306 nodeID VARCHAR2(100) NOT NULL, 307 rosterGroup VARCHAR2(100) NOT NULL 308); 309CREATE INDEX ofPubsubNodeGroups_idx ON ofPubsubNodeGroups (serviceID, nodeID); 310 311CREATE TABLE ofPubsubAffiliation ( 312 serviceID VARCHAR2(100) NOT NULL, 313 nodeID VARCHAR2(100) NOT NULL, 314 jid VARCHAR2(1024) NOT NULL, 315 affiliation VARCHAR2(10) NOT NULL, 316 CONSTRAINT ofPubsubAffiliation_pk PRIMARY KEY (serviceID, nodeID, jid) 317); 318 319CREATE TABLE ofPubsubItem ( 320 serviceID VARCHAR2(100) NOT NULL, 321 nodeID VARCHAR2(100) NOT NULL, 322 id VARCHAR2(100) NOT NULL, 323 jid VARCHAR2(1024) NOT NULL, 324 creationDate CHAR(15) NOT NULL, 325 payload CLOB NULL, 326 CONSTRAINT ofPubsubItem_pk PRIMARY KEY (serviceID, nodeID, id) 327); 328 329CREATE TABLE ofPubsubSubscription ( 330 serviceID VARCHAR2(100) NOT NULL, 331 nodeID VARCHAR2(100) NOT NULL, 332 id VARCHAR2(100) NOT NULL, 333 jid VARCHAR2(1024) NOT NULL, 334 owner VARCHAR2(1024) NOT NULL, 335 state VARCHAR(15) NOT NULL, 336 deliver INTEGER NOT NULL, 337 digest INTEGER NOT NULL, 338 digest_frequency INTEGER NOT NULL, 339 expire CHAR(15) NULL, 340 includeBody INTEGER NOT NULL, 341 showValues VARCHAR(30) NOT NULL, 342 subscriptionType VARCHAR(10) NOT NULL, 343 subscriptionDepth INTEGER NOT NULL, 344 keyword VARCHAR2(200) NULL, 345 CONSTRAINT ofPubsubSubscription_pk PRIMARY KEY (serviceID, nodeID, id) 346); 347 348CREATE TABLE ofPubsubDefaultConf ( 349 serviceID VARCHAR2(100) NOT NULL, 350 leaf INTEGER NOT NULL, 351 deliverPayloads INTEGER NOT NULL, 352 maxPayloadSize INTEGER NOT NULL, 353 persistItems INTEGER NOT NULL, 354 maxItems INTEGER NOT NULL, 355 notifyConfigChanges INTEGER NOT NULL, 356 notifyDelete INTEGER NOT NULL, 357 notifyRetract INTEGER NOT NULL, 358 presenceBased INTEGER NOT NULL, 359 sendItemSubscribe INTEGER NOT NULL, 360 publisherModel VARCHAR2(15) NOT NULL, 361 subscriptionEnabled INTEGER NOT NULL, 362 accessModel VARCHAR2(10) NOT NULL, 363 language VARCHAR2(255) NULL, 364 replyPolicy VARCHAR2(15) NULL, 365 associationPolicy VARCHAR2(15) NOT NULL, 366 maxLeafNodes INTEGER NOT NULL, 367 CONSTRAINT ofPubsubDefaultConf_pk PRIMARY KEY (serviceID, leaf) 368); 369 370-- Finally, insert default table values. 371 372INSERT INTO ofID (idType, id) VALUES (18, 1); 373INSERT INTO ofID (idType, id) VALUES (19, 1); 374INSERT INTO ofID (idType, id) VALUES (23, 1); 375INSERT INTO ofID (idType, id) VALUES (26, 2); 376INSERT INTO ofID (idType, id) VALUES (27, 1); 377 378INSERT INTO ofVersion (name, version) VALUES ('openfire', 33); 379 380-- Entry for admin user 381INSERT INTO ofUser (username, plainPassword, name, email, creationDate, modificationDate) 382 VALUES ('admin', 'admin', 'Administrator', 'admin@example.com', '0', '0'); 383 384-- Entry for default conference service 385INSERT INTO ofMucService (serviceID, subdomain, isHidden) VALUES (1, 'conference', 0); 386 387commit; 388