diff options
Diffstat (limited to 'SQL')
| -rw-r--r-- | SQL/mysql.initial.sql | 44 | ||||
| -rw-r--r-- | SQL/mysql.update.sql | 21 |
2 files changed, 41 insertions, 24 deletions
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index f8145471f..cca908899 100644 --- a/SQL/mysql.initial.sql +++ b/SQL/mysql.initial.sql @@ -54,14 +54,13 @@ CREATE TABLE `messages` ( PRIMARY KEY(`message_id`), INDEX `created_index` (`created`), INDEX `index_index` (`user_id`, `cache_key`, `idx`), - UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`), - CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) - /*!40008 - ON DELETE CASCADE - ON UPDATE CASCADE */ + UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`) ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +/* create foreign keys outside of create table, because of MySQL bug #46293 */ +ALTER TABLE `messages` ADD CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */; + -- Table structure for table `cache` @@ -73,14 +72,13 @@ CREATE TABLE `cache` ( `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY(`cache_id`), INDEX `created_index` (`created`), - INDEX `user_cache_index` (`user_id`,`cache_key`), - CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) - /*!40008 - ON DELETE CASCADE - ON UPDATE CASCADE */ + INDEX `user_cache_index` (`user_id`,`cache_key`) ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +/* create foreign keys outside of create table, because of MySQL bug #46293 */ +ALTER TABLE `cache` ADD CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */; + -- Table structure for table `contacts` @@ -95,14 +93,13 @@ CREATE TABLE `contacts` ( `vcard` text NULL, `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY(`contact_id`), - INDEX `user_contacts_index` (`user_id`,`email`), - CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) - /*!40008 - ON DELETE CASCADE - ON UPDATE CASCADE */ + INDEX `user_contacts_index` (`user_id`,`email`) ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +/* create foreign keys outside of create table, because of MySQL bug #46293 */ +ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */; + -- Table structure for table `identities` @@ -118,13 +115,12 @@ CREATE TABLE `identities` ( `signature` text, `html_signature` tinyint(1) NOT NULL DEFAULT '0', `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', - PRIMARY KEY(`identity_id`), - CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) - /*!40008 - ON DELETE CASCADE - ON UPDATE CASCADE */ + PRIMARY KEY(`identity_id`) ) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +/* create foreign keys outside of create table, because of MySQL bug #46293 */ +ALTER TABLE `identities` ADD CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */; + /*!40014 SET FOREIGN_KEY_CHECKS=1 */; diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql index 17301ab05..5ad03d4f6 100644 --- a/SQL/mysql.update.sql +++ b/SQL/mysql.update.sql @@ -57,3 +57,24 @@ ALTER TABLE `session` ALTER TABLE `contacts` ADD INDEX `user_contacts_index` (`user_id`,`email`); + +-- Updates from version 0.3.1 + +/* MySQL bug workaround: http://bugs.mysql.com/bug.php?id=46293 */ +/*!40014 SET FOREIGN_KEY_CHECKS=0; */ + +ALTER TABLE `messages` DROP FOREIGN KEY `user_id_fk_messages`; +ALTER TABLE `cache` DROP FOREIGN KEY `user_id_fk_cache`; +ALTER TABLE `contacts` DROP FOREIGN KEY `user_id_fk_contacts`; +ALTER TABLE `identities` DROP FOREIGN KEY `user_id_fk_identities`; + +ALTER TABLE `messages` ADD CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */; +ALTER TABLE `cache` ADD CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */; +ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */; +ALTER TABLE `identities` ADD CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */; + +/*!40014 SET FOREIGN_KEY_CHECKS=1; */ |