ALTER TABLE `devices` MODIFY COLUMN `type` VARCHAR(100) NOT NULL DEFAULT 'unknown';
ALTER TABLE `users` MODIFY COLUMN `createdTime` BIGINT(20) NOT NULL DEFAULT 0,
 MODIFY COLUMN `displayName` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
 MODIFY COLUMN `email` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
 MODIFY COLUMN `foreignKey` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
 MODIFY COLUMN `modifiedTime` BIGINT(20) NOT NULL DEFAULT 0,
 CHANGE COLUMN `serverFileId` `profileImageId` BIGINT(20) NOT NULL DEFAULT 0,
 MODIFY COLUMN `password` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
 MODIFY COLUMN `viewLines` INTEGER NOT NULL DEFAULT 20,
 MODIFY COLUMN `location` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
 MODIFY COLUMN `timeZoneId` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
 MODIFY COLUMN `description` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
 MODIFY COLUMN `administrator` TINYINT(1) DEFAULT 0,
 ADD COLUMN `defaultDesign` TINYINT(1) NOT NULL DEFAULT 1 AFTER `protect`,
 ADD COLUMN `backgroundImageId` BIGINT(20) NOT NULL DEFAULT 0 AFTER `defaultDesign`,
 ADD COLUMN `backgroundColorHex` VARCHAR(45) NOT NULL DEFAULT '00A1D9' AFTER `backgroundImageId`,
 ADD COLUMN `colorHex` VARCHAR(45) NOT NULL DEFAULT '000000' AFTER `backgroundColorHex`,
 ADD COLUMN `nameColorHex` VARCHAR(45) NOT NULL DEFAULT '000000' AFTER `colorHex`,
 ADD COLUMN `linkColorHex` VARCHAR(45) NOT NULL DEFAULT '0000FF' AFTER `nameColorHex`,
 ADD COLUMN `sidebarBackgroundColorHex` VARCHAR(45) NOT NULL DEFAULT 'E0FF92' AFTER `linkColorHex`,
 ADD COLUMN `sidebarFrameColorHex` VARCHAR(45) NOT NULL DEFAULT '87BC44' AFTER `sidebarBackgroundColorHex`,
 ADD COLUMN `tile` TINYINT(1) NOT NULL DEFAULT 1 AFTER `sidebarFrameColorHex`,
 ADD COLUMN `growl` TINYINT(1) NOT NULL DEFAULT 0 AFTER `tile`,
 ADD COLUMN `growlHost` VARCHAR(100) NOT NULL default '' AFTER `growl`,
 ADD COLUMN `growlPassword` VARCHAR(45) NOT NULL default '' AFTER `growlHost`,
 DROP INDEX `idx_users_1`,
 ADD UNIQUE INDEX `idx_users_1` USING BTREE(`foreignKey`, `profileImageId`);
UPDATE devices
SET type = CASE WHEN type = '1' THEN type = 'gtalk'
                WHEN type = '2' THEN 'jabber'
                WHEN type = '3' THEN 'msnp'
                ELSE 'unknown' END;
UPDATE users AS a
SET statuses = (
    SELECT COUNT(statusId)
    FROM statuses AS b
    WHERE b.senderId = a.userId AND
          b.removedTime = 0
);
insert into mimetypes(extension, mimeType) values('.jpg', 'image/pjpeg');
insert into mimetypes(extension, mimeType) values('.jpeg', 'image/pjpeg');
insert into mimetypes(extension, mimeType) values('.png', 'image/x-png');