/* mysql/2018021600.sql */
CREATE TABLE IF NOT EXISTS `filestore` (
    `file_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` int(10) UNSIGNED NOT NULL,
    `filename` varchar(128) NOT NULL,
    `mtime` int(10) NOT NULL,
    `data` longtext NOT NULL,
    PRIMARY KEY (`file_id`),
    CONSTRAINT `user_id_fk_filestore` FOREIGN KEY (`user_id`)
        REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE `uniqueness` (`user_id`, `filename`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;


/* mysql/2018122300.sql */

/* This is the equivalent of 'ALTER TABLE `filestore` ADD COLUMN IF NOT EXISTS `context` varchar(32) NOT NULL', if such a thing existed. */
/* A stored procedure defined via a BEGIN/END statement block might be nicer, but Cpanel::Email::RoundCube::DBI::_yield_statements can't parse that. */
SET @rcAddColumnStatement = (SELECT IF(
  (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA = DATABASE() AND
            TABLE_NAME = 'filestore' AND
            COLUMN_NAME = 'context'
  ) > 0,
  'DO 0',
  'ALTER TABLE `filestore` ADD COLUMN `context` varchar(32) NOT NULL;'
));
PREPARE rcAddColumn FROM @rcAddColumnStatement;
EXECUTE rcAddColumn;
DEALLOCATE PREPARE rcAddColumn;

UPDATE `filestore` SET `context` = 'enigma';
ALTER TABLE `filestore` DROP FOREIGN KEY `user_id_fk_filestore`;
ALTER TABLE `filestore` DROP INDEX `uniqueness`;
ALTER TABLE `filestore` ADD UNIQUE INDEX `uniqueness` (`user_id`, `context`, `filename`);
ALTER TABLE `filestore` ADD CONSTRAINT `user_id_fk_filestore` FOREIGN KEY (`user_id`)
    REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE;
