db-update.mysql   [plain text]

--
-- This updates and creates indexes for jabberd2 mysql databases created prior to 2.0s6. 
-- Run this using the mysql interactive terminal:
--
--     mysql> \. db-update.mysql
--

USE jabberd2;

-- Change the primary keys on collection-owner to normal indexes so as not to
-- enforce uniqueness on the first 255 chars of otherwise different JIDs 

ALTER TABLE `active` DROP PRIMARY KEY;
ALTER TABLE `active` ADD INDEX ( `collection-owner` ( 255 ) );

ALTER TABLE `logout` DROP PRIMARY KEY;
ALTER TABLE `logout` ADD INDEX ( `collection-owner` ( 255 ) );

ALTER TABLE `vcard` DROP PRIMARY KEY;
ALTER TABLE `vcard` ADD INDEX ( `collection-owner` ( 255 ) );

ALTER TABLE `motd-message` DROP PRIMARY KEY;
ALTER TABLE `motd-message` ADD INDEX ( `collection-owner` ( 255 ) );

ALTER TABLE `motd-times` DROP PRIMARY KEY;
ALTER TABLE `motd-times` ADD INDEX ( `collection-owner` ( 255 ) );

ALTER TABLE `privacy-default` DROP PRIMARY KEY;
ALTER TABLE `privacy-default` ADD INDEX ( `collection-owner` ( 255 ) );

-- Add indexes on collection-owner for tables that should have them

ALTER TABLE `disco-items` ADD INDEX ( `collection-owner` ( 255 ) );
ALTER TABLE `roster-items` ADD INDEX ( `collection-owner` ( 255 ) );
ALTER TABLE `roster-groups` ADD INDEX ( `collection-owner` ( 255 ) );
ALTER TABLE `privacy-items` ADD INDEX ( `collection-owner` ( 255 ) );
ALTER TABLE `private` ADD INDEX ( `collection-owner` ( 255 ) );
ALTER TABLE `queue` ADD INDEX ( `collection-owner` ( 255 ) );
ALTER TABLE `vacation-settings` ADD INDEX ( `collection-owner` ( 255 ) );

-- Add indexes on username and realm for authreg

ALTER TABLE `authreg` ADD INDEX ( `username` ( 255 ) );
ALTER TABLE `authreg` ADD INDEX ( `realm` ( 255 ) );

-- Change the field type of xml in queue and private to allow storage > 64K
-- (MEDIUMTEXT will allow up to 16M)

ALTER TABLE `queue` CHANGE `xml` `xml` MEDIUMTEXT DEFAULT NULL;
ALTER TABLE `private` CHANGE `xml` `xml` MEDIUMTEXT DEFAULT NULL;

-- Remove 256-char limit on username in authreg table

ALTER TABLE `authreg` CHANGE `username` `username` TEXT DEFAULT NULL;

--- Change keys on object-sequence to primary keys - note that mysql's index
--- limit of 255 characters prevents the possibility of using collection-owner
--- as the primary key (as a JID can be longer than that)

ALTER TABLE `active` DROP INDEX `object-sequence` , ADD PRIMARY KEY ( `object-sequence` );
ALTER TABLE `disco-items` DROP INDEX `object-sequence` , ADD PRIMARY KEY ( `object-sequence` );
ALTER TABLE `logout` DROP INDEX `object-sequence` , ADD PRIMARY KEY ( `object-sequence` );
ALTER TABLE `motd-message` DROP INDEX `object-sequence` , ADD PRIMARY KEY ( `object-sequence` );
ALTER TABLE `motd-times` DROP INDEX `object-sequence` , ADD PRIMARY KEY ( `object-sequence` );
ALTER TABLE `privacy-default` DROP INDEX `object-sequence` , ADD PRIMARY KEY ( `object-sequence` );
ALTER TABLE `privacy-items` DROP INDEX `object-sequence` , ADD PRIMARY KEY ( `object-sequence` );
ALTER TABLE `private` DROP INDEX `object-sequence` , ADD PRIMARY KEY ( `object-sequence` );
ALTER TABLE `queue` DROP INDEX `object-sequence` , ADD PRIMARY KEY ( `object-sequence` );
ALTER TABLE `roster-groups` DROP INDEX `object-sequence` , ADD PRIMARY KEY ( `object-sequence` );
ALTER TABLE `roster-items` DROP INDEX `object-sequence` , ADD PRIMARY KEY ( `object-sequence` );
ALTER TABLE `vacation-settings` DROP INDEX `object-sequence` , ADD PRIMARY KEY ( `object-sequence` );
ALTER TABLE `vcard` DROP INDEX `object-sequence` , ADD PRIMARY KEY ( `object-sequence` );