From f7bfec96be8bf095ffc0af64a761f3866d5947b9 Mon Sep 17 00:00:00 2001 From: thomascube Date: Fri, 25 Aug 2006 11:51:11 +0000 Subject: Finalized new message parsing. Attention: changes in database schema --- SQL/mysql.initial.sql | 8 +++---- SQL/mysql.update-0.1a.sql | 51 ++++++++++++++++++++++++++++++++++++++++ SQL/mysql.update.sql | 60 +++++------------------------------------------ SQL/mysql5.initial.sql | 18 +++++++------- SQL/postgres.initial.sql | 3 ++- SQL/postgres.update.sql | 7 ++++++ SQL/sqlite.initial.sql | 4 ++-- SQL/sqlite.update.sql | 27 +++++++++++++++++++++ 8 files changed, 108 insertions(+), 70 deletions(-) create mode 100644 SQL/mysql.update-0.1a.sql create mode 100644 SQL/postgres.update.sql create mode 100644 SQL/sqlite.update.sql (limited to 'SQL') diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index 7546e52da..716268b1a 100644 --- a/SQL/mysql.initial.sql +++ b/SQL/mysql.initial.sql @@ -1,5 +1,5 @@ -- RoundCube Webmail initial database structure --- Version 0.1beta2 +-- Version 0.1-beta2 -- -- -------------------------------------------------------- @@ -116,12 +116,12 @@ CREATE TABLE `messages` ( `date` datetime NOT NULL default '0000-00-00 00:00:00', `size` int(11) unsigned NOT NULL default '0', `headers` text NOT NULL, - `body` longtext, + `structure` text, PRIMARY KEY (`message_id`), KEY `user_id` (`user_id`), - KEY `cache_key` (`cache_key`), KEY `idx` (`idx`), - KEY `uid` (`uid`) + KEY `uid` (`uid`), + UNIQUE `uniqueness` (`cache_key`, `uid`) ); diff --git a/SQL/mysql.update-0.1a.sql b/SQL/mysql.update-0.1a.sql new file mode 100644 index 000000000..054814af5 --- /dev/null +++ b/SQL/mysql.update-0.1a.sql @@ -0,0 +1,51 @@ +-- RoundCube Webmail update script for MySQL databases +-- Updates from version 0.1-20051007 + + +ALTER TABLE `session` ADD `ip` VARCHAR(15) 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 7fa296b0b..cfd5af361 100644 --- a/SQL/mysql.update.sql +++ b/SQL/mysql.update.sql @@ -1,57 +1,9 @@ -- RoundCube Webmail update script for MySQL databases --- Updates from version 0.1-20051007 +-- Updates from version 0.1-beta and 0.1-beta2 +ALTER TABLE `messages` + DROP `body`, + DROP INDEX `cache_key`, + ADD `structure` TEXT, + ADD UNIQUE `uniqueness` (`cache_key`, `uid`); -ALTER TABLE `session` ADD `ip` VARCHAR(15) 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, - `body` longtext, - PRIMARY KEY (`message_id`), - KEY `user_id` (`user_id`), - KEY `cache_key` (`cache_key`), - KEY `idx` (`idx`), - KEY `uid` (`uid`) -) TYPE=MyISAM; - - - --- RoundCube Webmail update script for MySQL databases --- Updates from version 0.1-20051216 - -ALTER TABLE `messages` ADD `created` DATETIME NOT NULL AFTER `cache_key` ; diff --git a/SQL/mysql5.initial.sql b/SQL/mysql5.initial.sql index 0116468ee..24e143119 100644 --- a/SQL/mysql5.initial.sql +++ b/SQL/mysql5.initial.sql @@ -1,5 +1,5 @@ -- RoundCube Webmail initial database structure --- Version 0.1beta2 +-- Version 0.1-beta2 -- -- -------------------------------------------------------- @@ -16,7 +16,7 @@ CREATE TABLE `session` ( `ip` varchar(15) NOT NULL, `vars` text NOT NULL, PRIMARY KEY(`sess_id`) -) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; +) TYPE=MYISAM CHARACTER SET ascii COLLATE ascii_general_ci; -- Table structure for table `users` @@ -31,15 +31,16 @@ CREATE TABLE `users` ( `language` varchar(5) NOT NULL DEFAULT 'en', `preferences` text NOT NULL, PRIMARY KEY(`user_id`) -) TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; +) TYPE=MYISAM CHARACTER SET ascii COLLATE ascii_general_ci; -- Table structure for table `messages` CREATE TABLE `messages` ( `message_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, + `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', `del` tinyint(1) NOT NULL DEFAULT '0', - `cache_key` varchar(128) NOT NULL, + `cache_key` varchar(128) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL, `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', @@ -50,12 +51,11 @@ CREATE TABLE `messages` ( `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `size` int(11) UNSIGNED NOT NULL DEFAULT '0', `headers` text NOT NULL, - `body` longtext, - `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', + `structure` text, PRIMARY KEY(`message_id`), - INDEX `cache_key`(`cache_key`), INDEX `idx`(`idx`), INDEX `uid`(`uid`), + UNIQUE `uniqueness` (`cache_key`, `uid`), CONSTRAINT `User_ID_FK_messages` FOREIGN KEY (`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE @@ -67,8 +67,8 @@ CREATE TABLE `messages` ( CREATE TABLE `cache` ( `cache_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, - `session_id` varchar(40), - `cache_key` varchar(128) NOT NULL, + `session_id` varchar(40) CHARACTER SET ascii COLLATE ascii_general_ci, + `cache_key` varchar(128) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL, `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `data` longtext NOT NULL, `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0', diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index 55e139603..029afaab3 100755 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -163,6 +163,7 @@ CREATE TABLE "messages" ( date timestamp with time zone NOT NULL, size integer DEFAULT 0 NOT NULL, headers text NOT NULL, - body text + structure text ); +ALTER TABLE "messages" ADD UNIQUE (cache_key, uid); diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql new file mode 100644 index 000000000..6ca855edc --- /dev/null +++ b/SQL/postgres.update.sql @@ -0,0 +1,7 @@ +-- 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 (cache_key, uid); + diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql index ae16a31d8..4ddc40d5d 100644 --- a/SQL/sqlite.initial.sql +++ b/SQL/sqlite.initial.sql @@ -1,5 +1,5 @@ -- RoundCube Webmail initial database structure --- Version 0.1a +-- Version 0.1-beta2 -- -- -------------------------------------------------------- @@ -118,7 +118,7 @@ CREATE TABLE messages ( date datetime NOT NULL default '0000-00-00 00:00:00', size integer NOT NULL default '0', headers text NOT NULL, - body text + structure text ); CREATE INDEX ix_messages_user_id ON messages(user_id); diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql new file mode 100644 index 000000000..e725729ad --- /dev/null +++ b/SQL/sqlite.update.sql @@ -0,0 +1,27 @@ +-- RoundCube Webmail update script for SQLite databases +-- Updates from version 0.1-beta2 and older + +DROP TABLE messages; + +CREATE TABLE messages ( + message_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + del tinyint NOT NULL default '0', + cache_key varchar(128) NOT NULL default '', + created datetime NOT NULL default '0000-00-00 00:00:00', + idx integer NOT NULL default '0', + uid integer 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 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); -- cgit v1.2.3