summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--SQL/mysql.initial.sql11
-rw-r--r--SQL/mysql.update-0.1a.sql51
-rw-r--r--SQL/mysql.update.sql22
-rw-r--r--SQL/mysql5.initial.sql19
-rw-r--r--SQL/postgres.initial.sql2
-rw-r--r--SQL/postgres.update.sql14
-rw-r--r--SQL/sqlite.initial.sql15
-rw-r--r--SQL/sqlite.update.sql12
8 files changed, 32 insertions, 114 deletions
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index 926a83eab..ae195e438 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -1,5 +1,5 @@
-- RoundCube Webmail initial database structure
--- Version 0.1-rc1
+-- Version 0.1
--
-- --------------------------------------------------------
@@ -16,9 +16,7 @@ CREATE TABLE `cache` (
`created` datetime NOT NULL default '0000-00-00 00:00:00',
`data` longtext NOT NULL,
PRIMARY KEY (`cache_id`),
- KEY `user_id` (`user_id`),
- KEY `cache_key` (`cache_key`),
- KEY `session_id` (`session_id`)
+ INDEX `user_cache_index` (`user_id`,`cache_key`)
);
-- --------------------------------------------------------
@@ -118,10 +116,7 @@ CREATE TABLE `messages` (
`size` int(11) unsigned NOT NULL default '0',
`headers` text NOT NULL,
`structure` text,
- PRIMARY KEY (`message_id`),
- KEY `user_id` (`user_id`),
- KEY `idx` (`idx`),
- KEY `uid` (`uid`),
+ PRIMARY KEY (`message_id`),
UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`)
);
diff --git a/SQL/mysql.update-0.1a.sql b/SQL/mysql.update-0.1a.sql
deleted file mode 100644
index 60f22b3d6..000000000
--- a/SQL/mysql.update-0.1a.sql
+++ /dev/null
@@ -1,51 +0,0 @@
--- RoundCube Webmail update script for MySQL databases
--- Updates from version 0.1-20051007
-
-
-ALTER TABLE `session` ADD `ip` VARCHAR(40) NOT NULL AFTER changed;
-ALTER TABLE `users` ADD `alias` VARCHAR(128) NOT NULL AFTER mail_host;
-
-
-
--- RoundCube Webmail update script for MySQL databases
--- Updates from version 0.1-20051021
-
-ALTER TABLE `session` CHANGE `sess_id` `sess_id` VARCHAR(40) NOT NULL;
-
-ALTER TABLE `contacts` CHANGE `del` `del` TINYINT(1) NOT NULL;
-ALTER TABLE `contacts` ADD `changed` DATETIME NOT NULL AFTER `user_id`;
-
-UPDATE `contacts` SET `del`=0 WHERE `del`=1;
-UPDATE `contacts` SET `del`=1 WHERE `del`=2;
-
-ALTER TABLE `identities` CHANGE `default` `standard` TINYINT(1) NOT NULL;
-ALTER TABLE `identities` CHANGE `del` `del` TINYINT(1) NOT NULL;
-
-UPDATE `identities` SET `del`=0 WHERE `del`=1;
-UPDATE `identities` SET `del`=1 WHERE `del`=2;
-UPDATE `identities` SET `standard`=0 WHERE `standard`=1;
-UPDATE `identities` SET `standard`=1 WHERE `standard`=2;
-
-CREATE TABLE `messages` (
- `message_id` int(11) unsigned NOT NULL auto_increment,
- `user_id` int(11) unsigned NOT NULL default '0',
- `del` tinyint(1) NOT NULL default '0',
- `cache_key` varchar(128) NOT NULL default '',
- `created` datetime NOT NULL default '0000-00-00 00:00:00',
- `idx` int(11) unsigned NOT NULL default '0',
- `uid` int(11) unsigned NOT NULL default '0',
- `subject` varchar(255) NOT NULL default '',
- `from` varchar(255) NOT NULL default '',
- `to` varchar(255) NOT NULL default '',
- `cc` varchar(255) NOT NULL default '',
- `date` datetime NOT NULL default '0000-00-00 00:00:00',
- `size` int(11) unsigned NOT NULL default '0',
- `headers` text NOT NULL,
- `structure` text,
- PRIMARY KEY (`message_id`),
- KEY `user_id` (`user_id`),
- KEY `idx` (`idx`),
- KEY `uid` (`uid`),
- UNIQUE `uniqueness` (`cache_key`, `uid`)
-) TYPE=MyISAM;
-
diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql
index e904be278..70230784b 100644
--- a/SQL/mysql.update.sql
+++ b/SQL/mysql.update.sql
@@ -1,21 +1,13 @@
-- RoundCube Webmail update script for MySQL databases
--- Updates from version 0.1-beta and 0.1-beta2
+-- Updates from version 0.1-stable to 0.1.1
TRUNCATE TABLE `messages`;
ALTER TABLE `messages`
- DROP `body`,
- DROP INDEX `cache_key`,
- ADD `structure` TEXT,
- ADD UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`);
-
-ALTER TABLE `identities`
- ADD `html_signature` tinyint(1) default 0 NOT NULL;
+ DROP INDEX `idx`,
+ DROP INDEX `uid`;
-ALTER TABLE `session` CHANGE `ip` `ip` VARCHAR(40)
-
--- Uncomment these lines if you're using MySQL 4.1 or higher
--- ALTER TABLE `users`
--- DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci,
--- CHANGE `username` `username` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
--- CHANGE `alias` `alias` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
+ALTER TABLE `cache`
+ DROP INDEX `cache_key`,
+ DROP INDEX `session_id`,
+ ADD INDEX `user_cache_index` (`user_id`,`cache_key`);
diff --git a/SQL/mysql5.initial.sql b/SQL/mysql5.initial.sql
index 1a6ef1990..58e220f7d 100644
--- a/SQL/mysql5.initial.sql
+++ b/SQL/mysql5.initial.sql
@@ -1,5 +1,5 @@
-- RoundCube Webmail initial database structure
--- Version 0.1-rc1
+-- Version 0.1
-- --------------------------------------------------------
@@ -15,7 +15,7 @@ CREATE TABLE `session` (
`ip` varchar(40) NOT NULL,
`vars` text NOT NULL,
PRIMARY KEY(`sess_id`)
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Table structure for table `users`
@@ -30,7 +30,7 @@ CREATE TABLE `users` (
`language` varchar(5) NOT NULL DEFAULT 'en',
`preferences` text,
PRIMARY KEY(`user_id`)
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Table structure for table `messages`
@@ -52,14 +52,12 @@ CREATE TABLE `messages` (
`headers` text NOT NULL,
`structure` text,
PRIMARY KEY(`message_id`),
- INDEX `idx`(`idx`),
- INDEX `uid`(`uid`),
UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`),
CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`)
ON DELETE CASCADE
ON UPDATE CASCADE
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Table structure for table `cache`
@@ -72,13 +70,12 @@ CREATE TABLE `cache` (
`data` longtext NOT NULL,
`user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY(`cache_id`),
- INDEX `cache_key`(`cache_key`),
- INDEX `session_id`(`session_id`),
+ INDEX `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
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Table structure for table `contacts`
@@ -98,7 +95,7 @@ CREATE TABLE `contacts` (
REFERENCES `users`(`user_id`)
ON DELETE CASCADE
ON UPDATE CASCADE
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
-- Table structure for table `identities`
@@ -120,7 +117,7 @@ CREATE TABLE `identities` (
REFERENCES `users`(`user_id`)
ON DELETE CASCADE
ON UPDATE CASCADE
-) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
+) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci;
SET FOREIGN_KEY_CHECKS=1;
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql
index 356a579fc..e8513af73 100644
--- a/SQL/postgres.initial.sql
+++ b/SQL/postgres.initial.sql
@@ -131,7 +131,7 @@ CREATE TABLE "cache" (
data text NOT NULL
);
-
+ALTER TABLE "cache" ADD INDEX (user_id, cache_key);
--
-- Sequence "message_ids"
diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql
index 0d63cf934..add454ab8 100644
--- a/SQL/postgres.update.sql
+++ b/SQL/postgres.update.sql
@@ -1,12 +1,4 @@
--- RoundCube Webmail update script for Postres databases
--- Updates from version 0.1-beta and older
-
-ALTER TABLE "messages" DROP body;
-ALTER TABLE "messages" ADD structure TEXT;
-ALTER TABLE "messages" ADD UNIQUE (user_id, cache_key, uid);
-
-ALTER TABLE "identities" ADD html_signature INTEGER;
-ALTER TABLE "identities" ALTER html_signature SET DEFAULT 0;
-UPDATE identities SET html_signature = 0;
-ALTER TABLE "identities" ALTER html_signature SET NOT NULL;
+-- RoundCube Webmail update script for Postgres databases
+-- Updates from version 0.1-stable to 0.1.1
+ALTER TABLE "cache" ADD INDEX (user_id, cache_key);
diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql
index 4ae752983..a1eaeb8be 100644
--- a/SQL/sqlite.initial.sql
+++ b/SQL/sqlite.initial.sql
@@ -1,5 +1,5 @@
-- RoundCube Webmail initial database structure
--- Version 0.1-rc1
+-- Version 0.1
--
-- --------------------------------------------------------
@@ -17,9 +17,7 @@ CREATE TABLE cache (
data longtext NOT NULL
);
-CREATE INDEX ix_cache_user_id ON cache(user_id);
-CREATE INDEX ix_cache_cache_key ON cache(cache_key);
-CREATE INDEX ix_cache_session_id ON cache(session_id);
+CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key);
-- --------------------------------------------------------
@@ -115,14 +113,11 @@ CREATE TABLE messages (
subject varchar(255) NOT NULL default '',
"from" varchar(255) NOT NULL default '',
"to" varchar(255) NOT NULL default '',
- cc varchar(255) NOT NULL default '',
- date datetime NOT NULL default '0000-00-00 00:00:00',
+ "cc" varchar(255) NOT NULL default '',
+ "date" datetime NOT NULL default '0000-00-00 00:00:00',
size integer NOT NULL default '0',
headers text NOT NULL,
structure text
);
-CREATE INDEX ix_messages_user_id ON messages(user_id);
-CREATE INDEX ix_messages_cache_key ON messages(cache_key);
-CREATE INDEX ix_messages_idx ON messages(idx);
-CREATE INDEX ix_messages_uid ON messages(uid);
+CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid);
diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql
index e725729ad..ed0ad2012 100644
--- a/SQL/sqlite.update.sql
+++ b/SQL/sqlite.update.sql
@@ -1,5 +1,5 @@
-- RoundCube Webmail update script for SQLite databases
--- Updates from version 0.1-beta2 and older
+-- Updates from version 0.1-stable to 0.1.1
DROP TABLE messages;
@@ -14,14 +14,12 @@ CREATE TABLE messages (
subject varchar(255) NOT NULL default '',
"from" varchar(255) NOT NULL default '',
"to" varchar(255) NOT NULL default '',
- cc varchar(255) NOT NULL default '',
- date datetime NOT NULL default '0000-00-00 00:00:00',
+ "cc" varchar(255) NOT NULL default '',
+ "date" datetime NOT NULL default '0000-00-00 00:00:00',
size integer NOT NULL default '0',
headers text NOT NULL,
structure text
);
-CREATE INDEX ix_messages_user_id ON messages(user_id);
-CREATE INDEX ix_messages_cache_key ON messages(cache_key);
-CREATE INDEX ix_messages_idx ON messages(idx);
-CREATE INDEX ix_messages_uid ON messages(uid);
+CREATE INDEX ix_messages_user_cache_uid ON messages(user_id,cache_key,uid);
+
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-10 18:27:07 +0000