1
2-- Drop old columns of pubSubNode
3ALTER TABLE pubsubNode DROP COLUMN contacts;
4ALTER TABLE pubsubNode DROP COLUMN rosterGroups;
5ALTER TABLE pubsubNode DROP COLUMN replyRooms;
6ALTER TABLE pubsubNode DROP COLUMN replyTo;
7ALTER TABLE pubsubNode DROP COLUMN associationTrusted;
8
9-- Add new pubsub tables
10CREATE TABLE pubsubNodeJIDs (
11  serviceID           VARCHAR(100)  NOT NULL,
12  nodeID              VARCHAR(100)  NOT NULL,
13  jid                 VARCHAR(1024) NOT NULL,
14  associationType     VARCHAR(20)   NOT NULL,
15  CONSTRAINT pubsubJID_pk PRIMARY KEY (serviceID, nodeID, jid)
16);
17
18CREATE TABLE pubsubNodeGroups (
19  serviceID           VARCHAR(100)  NOT NULL,
20  nodeID              VARCHAR(100)  NOT NULL,
21  rosterGroup         VARCHAR(100)  NOT NULL
22);
23CREATE INDEX pubsubNodeGroups_idx ON pubsubNodeGroups (serviceID, nodeID);
24
25UPDATE jiveVersion set version=8 where name = 'openfire';
26