openfire_sqlserver.sql 7.31 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           INT           NOT NULL,
  subdomain           NVARCHAR(255) NOT NULL,
  description         NVARCHAR(255),
  CONSTRAINT mucService_pk PRIMARY KEY (subdomain)
);
CREATE INDEX mucService_serviceid_idx ON mucService(serviceID);

/* create table mucServiceProp */
CREATE TABLE mucServiceProp (
  serviceID           INT           NOT NULL,
  name                NVARCHAR(100) NOT NULL,
  propValue           NVARCHAR(2000) NOT NULL,
  CONSTRAINT mucServiceProp_pk PRIMARY KEY (serviceID, name)
);

/* add new indexed column to mucRoom */
19
ALTER TABLE mucRoom ADD serviceID INT NOT NULL DEFAULT 1;
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;
ALTER TABLE mucRoom ADD CONSTRAINT mucRoom__pk 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 there is a custom one set */
31 32 33 34
UPDATE mucService SET mucService.subdomain =
    ( SELECT CONVERT(NVARCHAR(255),jiveProperty.propValue) FROM jiveProperty WHERE jiveProperty.name = 'xmpp.muc.service' )
  WHERE EXISTS
    ( SELECT CONVERT(NVARCHAR(255),jiveProperty.propValue) FROM jiveProperty WHERE jiveProperty.name = 'xmpp.muc.service' );
35
DELETE FROM jiveProperty WHERE name = 'xmpp.muc.service';
36

37 38 39 40
UPDATE mucService SET mucService.description =
    ( SELECT CONVERT(NVARCHAR(255),jiveProperty.propValue) FROM jiveProperty WHERE jiveProperty.name = 'muc.service-name' )
  WHERE EXISTS
    ( SELECT CONVERT(NVARCHAR(255),jiveProperty.propValue) FROM jiveProperty WHERE jiveProperty.name = 'muc.service-name' );
41
DELETE FROM jiveProperty WHERE name = 'muc.service-name';
42 43

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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