messenger_oracle.sql 5.43 KB
Newer Older
1 2 3
REM // $RCSfile$
REM // $Revision$
REM // $Date$
Gaston Dombiak's avatar
Gaston Dombiak committed
4

5 6 7
REM // upgrades from Messenger 1.1.x to 2.0.x

REM // jiveUser: Adds new column "username". Removes "nameVisible" & "emailVisible". Changes primary key
8
ALTER TABLE jiveUser ADD username VARCHAR2(32) NOT NULL;
9 10 11 12 13 14 15
ALTER TABLE jiveUser DROP COLUMN nameVisible;
ALTER TABLE jiveUser DROP COLUMN emailVisible;
UPDATE jiveUser,jiveUserID SET jiveUser.username = jiveUserID.username where jiveUserID.objectID = jiveUser.userID;
ALTER TABLE jiveUser DROP PRIMARY KEY;
ALTER TABLE jiveUser ADD CONSTRAINT jiveUser_pk PRIMARY KEY (username);

REM // jiveUserProp: Adds new column "username". Changes primary key
16
ALTER TABLE jiveUserProp ADD username VARCHAR2(32) NOT NULL;
17 18 19 20 21
UPDATE jiveUserProp,jiveUser SET jiveUserProp.username = jiveUser.username where jiveUserProp.userID = jiveUser.userID;
ALTER TABLE jiveUserProp DROP PRIMARY KEY;
ALTER TABLE jiveUserProp ADD CONSTRAINT jiveUserProp_pk PRIMARY KEY (username, name);

REM // jiveGroupUser: Adds new column "username". Changes primary key
22
ALTER TABLE jiveGroupUser ADD username VARCHAR2(32) NOT NULL;
23 24 25 26 27
UPDATE jiveGroupUser,jiveUser SET jiveGroupUser.username = jiveUser.username where jiveGroupUser.userID = jiveUser.userID;
ALTER TABLE jiveGroupUser DROP PRIMARY KEY;
ALTER TABLE jiveGroupUser ADD CONSTRAINT jiveGroupUser PRIMARY KEY (groupID, username, administrator);

REM // jivePrivate: Adds new column "username". Changes primary key
28
ALTER TABLE jivePrivate ADD username VARCHAR2(32) NOT NULL;
29 30 31 32 33
UPDATE jivePrivate,jiveUser SET jivePrivate.username = jiveUser.username where jivePrivate.userID = jiveUser.userID;
ALTER TABLE jivePrivate DROP PRIMARY KEY;
ALTER TABLE jivePrivate ADD CONSTRAINT jivePrivate_pk PRIMARY KEY (username, name, namespace);

REM // jiveOffline: Adds new column "username". Changes primary key
34
ALTER TABLE jiveOffline ADD username VARCHAR2(32) NOT NULL;
35 36 37 38 39
UPDATE jiveOffline ,jiveUser SET jiveOffline.username = jiveUser.username where jiveOffline.userID = jiveUser.userID;
ALTER TABLE jiveOffline DROP PRIMARY KEY;
ALTER TABLE jiveOffline ADD CONSTRAINT jiveOffline_pk PRIMARY KEY (username, messageID);

REM // jiveRoster: Adds new column "username". Replaces old index with new one
40
ALTER TABLE jiveRoster ADD username VARCHAR2(32) NOT NULL;
41 42 43 44 45
UPDATE jiveRoster,jiveUser SET jiveRoster.username = jiveUser.username where jiveRoster.userID = jiveUser.userID;
DROP INDEX jiveRoster_userid_idx;
CREATE INDEX jiveRoster_username_idx ON jiveRoster (username ASC);

REM // jiveVCard: Adds new column "username". Changes primary key
46
ALTER TABLE jiveVCard ADD username VARCHAR2(32) NOT NULL;
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
UPDATE jiveVCard ,jiveUser SET jiveVCard.username = jiveUser.username where jiveVCard.userID = jiveUser.userID;
ALTER TABLE jiveVCard DROP PRIMARY KEY;
ALTER TABLE jiveVCard ADD CONSTRAINT jiveVCard_pk PRIMARY KEY (username, name);

REM // Drops no longer needed tables
DROP TABLE jiveUserID;
DROP TABLE jiveChatbot;
DROP TABLE jiveDomain;
DROP TABLE jiveUserPerm;

REM // Deletes no longer needed entries
DELETE FROM jiveID where idType = 0;
DELETE FROM jiveID where idType = 1;
DELETE FROM jiveID where idType = 2;
DELETE FROM jiveID where idType = 13;
DELETE FROM jiveID where idType = 14;

REM // Finally remove "userID" column
ALTER TABLE jiveUserProp DROP COLUMN userID;
ALTER TABLE jiveUser DROP COLUMN userID;
ALTER TABLE jiveGroupUser DROP COLUMN userID;
ALTER TABLE jivePrivate DROP COLUMN userID;
ALTER TABLE jiveOffline DROP COLUMN userID;
ALTER TABLE jiveRoster DROP COLUMN userID;
ALTER TABLE jiveVCard DROP COLUMN userID;

REM // Create new tables
Gaston Dombiak's avatar
Gaston Dombiak committed
74 75 76 77 78 79 80 81 82 83 84 85 86 87

CREATE TABLE jiveProperty (
  name        VARCHAR2(100) NOT NULL,
  propValue   VARCHAR2(4000) NOT NULL,
  CONSTRAINT jiveProperty_pk PRIMARY KEY (name)
);

REM // MUC Tables

CREATE TABLE mucRoom(
  roomID              INT           NOT NULL,
  creationDate        CHAR(15)      NOT NULL,
  modificationDate    CHAR(15)      NOT NULL,
  name                VARCHAR2(50)  NOT NULL,
88
  naturalName         VARCHAR2(255) NOT NULL,
Gaston Dombiak's avatar
Gaston Dombiak committed
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
  description         VARCHAR2(255),
  canChangeSubject    INTEGER       NOT NULL,
  maxUsers            INTEGER       NOT NULL,
  publicRoom          INTEGER       NOT NULL,
  moderated           INTEGER       NOT NULL,
  invitationRequired  INTEGER       NOT NULL,
  canInvite           INTEGER       NOT NULL,
  password            VARCHAR2(50)  NULL,
  canDiscoverJID      INTEGER       NOT NULL,
  logEnabled          INTEGER       NOT NULL,
  subject             VARCHAR2(100) NULL,
  rolesToBroadcast    INTEGER       NOT NULL,
  lastActiveDate      CHAR(15)      NULL,
  inMemory            INTEGER       NOT NULL,
  CONSTRAINT mucRoom_pk PRIMARY KEY (name)
);
CREATE INDEX mucRoom_roomid_idx ON mucRoom (roomID);

CREATE TABLE mucAffiliation (
  roomID              INT            NOT NULL,
  jid                 VARCHAR2(4000) NOT NULL,
  affiliation         INTEGER        NOT NULL,
  CONSTRAINT mucAffiliation_pk PRIMARY KEY (roomID, jid)
);

CREATE TABLE mucMember (
  roomID              INT            NOT NULL,
  jid                 VARCHAR2(4000) NOT NULL,
  nickname            VARCHAR2(255)  NULL,
  CONSTRAINT mucMember_pk PRIMARY KEY (roomID, jid)
);

CREATE TABLE mucConversationLog (
  roomID              INT            NOT NULL,
  sender              VARCHAR2(4000) NOT NULL,
  nickname            VARCHAR2(255)  NULL,
  time                CHAR(15)       NOT NULL,
  subject             VARCHAR2(255)  NULL,
  body                VARCHAR2(4000) NULL
);

130
REM // Unique ID entry for mucRoom
Gaston Dombiak's avatar
Gaston Dombiak committed
131
INSERT INTO jiveID (idType, id) VALUES (23, 1);