messenger_hsqldb.sql 2.21 KB
Newer Older
Gaston Dombiak's avatar
Gaston Dombiak committed
1 2 3 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
// $RCSfile$
// $Revision$
// $Date$

// upgrades from Messenger 2.0.x to 2.1.0

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

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

// jiveGroupUser: Recreate table from scratch
DROP TABLE jiveGroupUser;
CREATE TABLE jiveGroupUser (
  groupName             VARCHAR(50)     NOT NULL,
  username              VARCHAR(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 COLUMN lockedDate CHAR(15) NOT NULL BEFORE canChangeSubject;
ALTER TABLE mucRoom ADD COLUMN emptyDate CHAR(15) NULL BEFORE canChangeSubject;
Matt Tucker's avatar
Matt Tucker committed
36
// ALTER TABLE <tablename> ALTER COLUMN <columnname> {RENAME TO <newname> };
Gaston Dombiak's avatar
Gaston Dombiak committed
37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
ALTER TABLE mucRoom ALTER COLUMN invitationRequired RENAME TO membersOnly;
ALTER TABLE mucRoom DROP COLUMN lastActiveDate;
ALTER TABLE mucRoom DROP COLUMN inMemory;

// mucMember: Add new columns
ALTER TABLE mucMember ADD COLUMN firstName VARCHAR(100) NULL;
ALTER TABLE mucMember ADD COLUMN lastName  VARCHAR(100) NULL;
ALTER TABLE mucMember ADD COLUMN url VARCHAR(100) NULL;
ALTER TABLE mucMember ADD COLUMN email VARCHAR(100) NULL;
ALTER TABLE mucMember ADD COLUMN faqentry VARCHAR(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
53 54 55 56 57 58 59 60
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);