openfire_hsqldb.sql 7.37 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),
  CONSTRAINT mucService_pk PRIMARY KEY (subdomain)
);
CREATE INDEX mucService_serviceid_idx ON mucService(serviceID);

// create table mucServiceProp
CREATE TABLE mucServiceProp (
  serviceID           BIGINT        NOT NULL,
  name                VARCHAR(100)  NOT NULL,
  propValue           VARCHAR(4000) NOT NULL,
  CONSTRAINT mucServiceProp_pk PRIMARY KEY (serviceID, name)
);

// add new indexed column to mucRoom
19
ALTER TABLE mucRoom ADD COLUMN serviceID BIGINT BEFORE roomID;
20 21
CREATE INDEX mucRoom_serviceid_idx ON mucRoom(serviceID);

22
// change mucRoom primary key to be referenced around serviceID
23 24
ALTER TABLE mucRoom DROP CONSTRAINT mucRoom_pk;

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

// update all entries in mucRoom to be set to the default conference service
UPDATE mucRoom set serviceID = 1;

32 33 34
// finish changing mucRoom primary key to be referenced around serviceID
ALTER TABLE mucRoom ADD CONSTRAINT mucRoom_pk PRIMARY KEY (serviceID, name);

35
// update conference name/desc if there is a custom one set
36 37 38
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';
39

40 41 42
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';
43 44

// transfer all system properties to muc specific properties
45 46
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';
47

48 49
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';
50

51 52
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';
53

54 55
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';
56

57 58
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';
59

60 61
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';
62

63 64
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';
65

66 67
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';
68

69 70
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';
71

72 73
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';
74

75 76
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';
77

78 79
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';
80

81 82
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';
83

84 85
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';
86

87 88
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';
89

90 91
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';
92

93 94
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';
95

96 97
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';
98

99 100
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';
101

102 103
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';
104

105 106
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';
107

108 109
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';
110

111 112
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';
113

114 115
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';
116

117 118
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';
119 120 121


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