diff options
Diffstat (limited to 'SQL')
| -rw-r--r-- | SQL/mysql.initial.sql | 27 | ||||
| -rw-r--r-- | SQL/mysql.update.sql | 10 | ||||
| -rw-r--r-- | SQL/postgres.initial.sql | 2 | ||||
| -rw-r--r-- | SQL/postgres.update.sql | 5 | ||||
| -rw-r--r-- | SQL/sqlite.initial.sql | 2 | ||||
| -rw-r--r-- | SQL/sqlite.update.sql | 6 |
6 files changed, 28 insertions, 24 deletions
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index 25d11b50f..7a22a60f6 100644 --- a/SQL/mysql.initial.sql +++ b/SQL/mysql.initial.sql @@ -52,15 +52,13 @@ CREATE TABLE `messages` ( `headers` text NOT NULL, `structure` text, PRIMARY KEY(`message_id`), + CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, INDEX `created_index` (`created`), INDEX `index_index` (`user_id`, `cache_key`, `idx`), 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` @@ -71,14 +69,12 @@ CREATE TABLE `cache` ( `data` longtext NOT NULL, `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY(`cache_id`), + CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, INDEX `created_index` (`created`), 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` @@ -93,13 +89,11 @@ CREATE TABLE `contacts` ( `vcard` text NULL, `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY(`contact_id`), + CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) 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` @@ -115,12 +109,11 @@ 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`) + PRIMARY KEY(`identity_id`), + CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) + REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE, + INDEX `user_identities_index` (`user_id`, `del`) ) /*!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 495b600ed..8d86867ce 100644 --- a/SQL/mysql.update.sql +++ b/SQL/mysql.update.sql @@ -69,16 +69,18 @@ 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 */; + REFERENCES `users`(`user_id`); ALTER TABLE `cache` ADD CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */; + REFERENCES `users`(`user_id`); ALTER TABLE `contacts` ADD CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */; + REFERENCES `users`(`user_id`); ALTER TABLE `identities` ADD CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) - REFERENCES `users`(`user_id`) /*!40008 ON DELETE CASCADE ON UPDATE CASCADE */; + REFERENCES `users`(`user_id`); ALTER TABLE `contacts` ALTER `name` SET DEFAULT ''; ALTER TABLE `contacts` ALTER `firstname` SET DEFAULT ''; ALTER TABLE `contacts` ALTER `surname` SET DEFAULT ''; +ALTER TABLE `identities` INDEX `user_identities_index` (`user_id`, `del`); + /*!40014 SET FOREIGN_KEY_CHECKS=1 */; diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index a52a01f91..c634b4b3b 100644 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -78,7 +78,7 @@ CREATE TABLE identities ( html_signature integer DEFAULT 0 NOT NULL ); -CREATE INDEX identities_user_id_idx ON identities (user_id); +CREATE INDEX identities_user_id_idx ON identities (user_id, del); -- diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql index cb83f511b..0b926d5e1 100644 --- a/SQL/postgres.update.sql +++ b/SQL/postgres.update.sql @@ -43,3 +43,8 @@ TRUNCATE messages; CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx); DROP INDEX contacts_user_id_idx; CREATE INDEX contacts_user_id_idx ON contacts (user_id, email); + +-- Updates from version 0.3.1 + +DROP INDEX identities_user_id_idx; +CREATE INDEX identities_user_id_idx ON identities (user_id, del); diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql index cccad7a20..30546bbb2 100644 --- a/SQL/sqlite.initial.sql +++ b/SQL/sqlite.initial.sql @@ -56,7 +56,7 @@ CREATE TABLE identities ( html_signature tinyint NOT NULL default '0' ); -CREATE INDEX ix_identities_user_id ON identities(user_id); +CREATE INDEX ix_identities_user_id ON identities(user_id, del); -- -------------------------------------------------------- diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql index 329e1911c..fd90d48f6 100644 --- a/SQL/sqlite.update.sql +++ b/SQL/sqlite.update.sql @@ -22,7 +22,6 @@ CREATE TABLE messages ( ); CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid); - CREATE INDEX ix_users_username ON users(username); CREATE INDEX ix_users_alias ON users(alias); @@ -43,3 +42,8 @@ CREATE UNIQUE INDEX ix_messages_user_cache_uid ON messages (user_id,cache_key,ui CREATE INDEX ix_messages_index ON messages (user_id,cache_key,idx); DROP INDEX ix_contacts_user_id; CREATE INDEX ix_contacts_user_id ON contacts(user_id, email); + +-- Updates from version 0.3.1 + +DROP INDEX ix_identities_user_id; +CREATE INDEX ix_identities_user_id ON identities (user_id, del); |