openfire_mysql.sql 7.09 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
# create table mucService
CREATE TABLE mucService (
  serviceID           BIGINT        NOT NULL,
  subdomain           VARCHAR(255)  NOT NULL,
  description         VARCHAR(255),
  PRIMARY KEY (subdomain),
  INDEX mucService_serviceid_idx (serviceID)
);

# create table mucServiceProp
CREATE TABLE mucServiceProp (
  serviceID           BIGINT        NOT NULL,
  name                VARCHAR(100)  NOT NULL,
  propValue           TEXT          NOT NULL,
  PRIMARY KEY (serviceID, name)
);

# add new indexed column to mucRoom
19
ALTER TABLE mucRoom ADD COLUMN serviceID BIGINT NOT NULL DEFAULT 1 FIRST;
20 21
ALTER TABLE mucRoom ADD INDEX mucRoom_serviceid_idx (serviceID);

22
# change mucRooms primary key to be referenced around serviceID
23
ALTER TABLE mucRoom DROP PRIMARY KEY;
24
ALTER TABLE mucRoom ADD PRIMARY KEY (serviceID,name);
25

26
# add default entry for conference service and associated jiveID value
27
INSERT INTO mucService (serviceID, subdomain) VALUES (1, 'conference');
28 29
INSERT INTO jiveID (idType, id) VALUES (26, 1);

30
# update conference name/desc if theres a custom one set
31 32 33
UPDATE mucService SET mucService.subdomain = ( SELECT jiveProperty.propValue FROM jiveProperty WHERE jiveProperty.name = 'xmpp.muc.service' )
  WHERE EXISTS ( SELECT jiveProperty.propValue FROM jiveProperty WHERE jiveProperty.name = 'xmpp.muc.service' );
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.service';
34

35 36 37
UPDATE mucService SET mucService.description = ( SELECT jiveProperty.propValue FROM jiveProperty WHERE jiveProperty.name = 'muc.service-name' )
  WHERE EXISTS ( SELECT jiveProperty.propValue FROM jiveProperty WHERE jiveProperty.name = 'muc.service-name' );
DELETE FROM jiveProperty WHERE name = 'muc.service-name';
38 39

# transfer all system properties to muc specific properties
40 41
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.canOccupantsChangeSubject',propValue FROM jiveProperty WHERE name = 'muc.room.canOccupantsChangeSubject';
DELETE FROM jiveProperty WHERE name = 'muc.room.canOccupantsChangeSubject';
42

43 44
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.maxUsers',propValue FROM jiveProperty WHERE name = 'muc.room.maxUsers';
DELETE FROM jiveProperty WHERE name = 'muc.room.maxUsers';
45

46 47
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.publicRoom',propValue FROM jiveProperty WHERE name = 'muc.room.publicRoom';
DELETE FROM jiveProperty WHERE name = 'muc.room.publicRoom';
48

49 50
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.persistent',propValue FROM jiveProperty WHERE name = 'muc.room.persistent';
DELETE FROM jiveProperty WHERE name = 'muc.room.persistent';
51

52 53
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.moderated',propValue FROM jiveProperty WHERE name = 'muc.room.moderated';
DELETE FROM jiveProperty WHERE name = 'muc.room.moderated';
54

55 56
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.membersOnly',propValue FROM jiveProperty WHERE name = 'muc.room.membersOnly';
DELETE FROM jiveProperty WHERE name = 'muc.room.membersOnly';
57

58 59
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.canOccupantsInvite',propValue FROM jiveProperty WHERE name = 'muc.room.canOccupantsInvite';
DELETE FROM jiveProperty WHERE name = 'muc.room.canOccupantsInvite';
60

61 62
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.canAnyoneDiscoverJID',propValue FROM jiveProperty WHERE name = 'muc.room.canAnyoneDiscoverJID';
DELETE FROM jiveProperty WHERE name = 'muc.room.canAnyoneDiscoverJID';
63

64 65
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.logEnabled',propValue FROM jiveProperty WHERE name = 'muc.room.logEnabled';
DELETE FROM jiveProperty WHERE name = 'muc.room.logEnabled';
66

67 68
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.loginRestrictedToNickname',propValue FROM jiveProperty WHERE name = 'muc.room.loginRestrictedToNickname';
DELETE FROM jiveProperty WHERE name = 'muc.room.loginRestrictedToNickname';
69

70 71
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.canChangeNickname',propValue FROM jiveProperty WHERE name = 'muc.room.canChangeNickname';
DELETE FROM jiveProperty WHERE name = 'muc.room.canChangeNickname';
72

73 74
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'room.registrationEnabled',propValue FROM jiveProperty WHERE name = 'muc.room.registrationEnabled';
DELETE FROM jiveProperty WHERE name = 'muc.room.registrationEnabled';
75

76 77
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'tasks.user.timeout',propValue FROM jiveProperty WHERE name = 'xmpp.muc.tasks.user.timeout';
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.tasks.user.timeout';
78

79 80
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'tasks.user.idle',propValue FROM jiveProperty WHERE name = 'xmpp.muc.tasks.user.idle';
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.tasks.user.idle';
81

82 83
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'tasks.log.timeout',propValue FROM jiveProperty WHERE name = 'xmpp.muc.tasks.log.timeout';
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.tasks.log.timeout';
84

85 86
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'tasks.log.batchsize',propValue FROM jiveProperty WHERE name = 'xmpp.muc.tasks.log.batchsize';
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.tasks.log.batchsize';
87

88 89
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'sysadmin.jid',propValue FROM jiveProperty WHERE name = 'xmpp.muc.sysadmin.jid';
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.sysadmin.jid';
90

91 92
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'discover.locked',propValue FROM jiveProperty WHERE name = 'xmpp.muc.discover.locked';
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.discover.locked';
93

94 95
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'create.anyone',propValue FROM jiveProperty WHERE name = 'xmpp.muc.create.anyone';
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.create.anyone';
96

97 98
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'create.jid',propValue FROM jiveProperty WHERE name = 'xmpp.muc.create.jid';
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.create.jid';
99

100 101
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'enabled',propValue FROM jiveProperty WHERE name = 'xmpp.muc.enabled';
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.enabled';
102

103 104
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'unload.empty_days',propValue FROM jiveProperty WHERE name = 'xmpp.muc.unload.empty_days';
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.unload.empty_days';
105

106 107
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'discover.locked',propValue FROM jiveProperty WHERE name = 'xmpp.muc.discover.locked';
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.discover.locked';
108

109 110
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'history.maxNumber',propValue FROM jiveProperty WHERE name = 'xmpp.muc.history.maxNumber';
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.history.maxNumber';
111

112 113
INSERT INTO mucServiceProp(serviceID,name,propValue) SELECT 1,'history.type',propValue FROM jiveProperty WHERE name = 'xmpp.muc.history.type';
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.history.type';
114 115 116


UPDATE jiveVersion set version=17 where name = 'openfire';