Finalized new message parsing. Attention: changes in database schema

8 files changed, 108 insertions, 70 deletions

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);