messenger_sqlserver.sql 2.19 KB
Newer Older
1 2 3
/* $RCSfile$ */
/* $Revision$                           */
/* $Date$               */
Gaston Dombiak's avatar
Gaston Dombiak committed
4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

/* upgrades from Messenger 2.0.x to 2.1.0 */

/* jiveGroup: Recreate table from scratch */
DROP TABLE jiveGroup;
CREATE TABLE jiveGroup (
  groupName             NVARCHAR(50)   NOT NULL,
  description           NVARCHAR(255),
  CONSTRAINT group_pk PRIMARY KEY (groupName)
);

/* jiveGroupProp: Recreate table from scratch */
DROP TABLE jiveGroupProp;
CREATE TABLE jiveGroupProp (
   groupName            NVARCHAR(50)   NOT NULL,
   name                 NVARCHAR(100)   NOT NULL,
   propValue            NVARCHAR(2000)  NOT NULL,
   CONSTRAINT jiveGroupProp_pk PRIMARY KEY (groupName, name)
);


/* jiveGroupUser: Recreate table from scratch */
DROP TABLE jiveGroupUser;
CREATE TABLE jiveGroupUser (
  groupName             NVARCHAR(50)   NOT NULL,
  username              NVARCHAR(32)    NOT NULL,
  administrator         INTEGER         NOT NULL,
  CONSTRAINT jiveGroupUser_pk PRIMARY KEY (groupName, username, administrator)
);

/* mucRoom: Add new columns: "lockedDate" and "emptyDate". Rename column "invitationRequired" to "membersOnly". Delete columns: "lastActiveDate" and "inMemory". */
ALTER TABLE mucRoom ADD lockedDate CHAR(15) NOT NULL;
ALTER TABLE mucRoom ADD emptyDate CHAR(15) NULL;
ALTER TABLE mucRoom ADD membersOnly INT NOT NULL;
UPDATE mucRoom SET membersOnly = invitationRequired;
ALTER TABLE mucRoom DROP COLUMN invitationRequired;
ALTER TABLE mucRoom DROP COLUMN lastActiveDate;
ALTER TABLE mucRoom DROP COLUMN inMemory;

/* mucMember: Add new columns */
ALTER TABLE mucMember ADD firstName NVARCHAR(100) NULL;
ALTER TABLE mucMember ADD lastName  NVARCHAR(100) NULL;
ALTER TABLE mucMember ADD url NVARCHAR(100) NULL;
ALTER TABLE mucMember ADD email NVARCHAR(100) NULL;
ALTER TABLE mucMember ADD faqentry NVARCHAR(100) NULL;

/* mucConversationLog: Add new index */
CREATE INDEX mucLog_time_idx ON mucConversationLog (time);

/* Deletes no longer needed entries */
DELETE FROM jiveID where idType = 3;
Matt Tucker's avatar
Matt Tucker committed
55 56 57 58 59 60 61 62
DELETE FROM jiveID where idType = 4;

/* Add jiveVersion table */
CREATE TABLE jiveVersion (
  majorVersion  INTEGER  NOT NULL,
  minorVersion  INTEGER  NOT NULL
);
INSERT INTO jiveVersion (majorVersion, minorVersion) VALUES (2, 1);