-- restore cache table with primary key

drop table if exists `cache_new`;

CREATE TABLE `cache_new` (
  `cache_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cache_key` varchar(128) CHARACTER SET ascii NOT NULL,
  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `data` longtext NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`cache_id`),
  KEY `created_index` (`created`),
  KEY `user_cache_index` (`user_id`,`cache_key`),
  CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
 );

-- not needed as we can start with an empty cache table
-- insert into cache_new ( `cache_key`, `created`, `data`, `user_id` ) select `cache_key`, `created`, `data`, `user_id` from `cache`;

RENAME TABLE `cache` TO `cache_old`, `cache_new` TO `cache`;

drop table if exists `cache_old`;

-- restore alias field in users tables

alter table `users` add `alias` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL AFTER `mail_host`;

-- remove index on identities table

ALTER TABLE `identities` DROP INDEX `email_identities_index`;

-- remove system table
drop table if exists `system`;
