X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=SQL%2Fmysql.initial.sql;h=94679f20288346ad6e950fbf08380df0e537f40e;hb=76507f7c63a660742e76889ad6e3919f3dde3bb0;hp=ae195e4387ec8ac0d87df78041d3c51dc47b440c;hpb=c55c762910acc6b77b1aab2b6e28d5bbf522b920;p=roundcube.git

diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql
index ae195e4..94679f2 100644
--- a/SQL/mysql.initial.sql
+++ b/SQL/mysql.initial.sql
@@ -1,123 +1,192 @@
--- RoundCube Webmail initial database structure
--- Version 0.1
--- 
+-- Roundcube Webmail initial database structure
+
+
+/*!40014  SET FOREIGN_KEY_CHECKS=0 */;
+
+-- Table structure for table `session`
+
+CREATE TABLE `session` (
+ `sess_id` varchar(128) NOT NULL,
+ `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `ip` varchar(40) NOT NULL,
+ `vars` mediumtext NOT NULL,
+ PRIMARY KEY(`sess_id`),
+ INDEX `changed_index` (`changed`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+
+-- Table structure for table `users`
+
+CREATE TABLE `users` (
+ `user_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ `username` varchar(128) BINARY NOT NULL,
+ `mail_host` varchar(128) NOT NULL,
+ `alias` varchar(128) BINARY NOT NULL,
+ `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `last_login` datetime DEFAULT NULL,
+ `language` varchar(5),
+ `preferences` text,
+ PRIMARY KEY(`user_id`),
+ UNIQUE `username` (`username`, `mail_host`),
+ INDEX `alias_index` (`alias`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
 
--- --------------------------------------------------------
 
--- 
 -- Table structure for table `cache`
--- 
 
 CREATE TABLE `cache` (
-  `cache_id` int(10) unsigned NOT NULL auto_increment,
-  `user_id` int(10) unsigned NOT NULL default '0',
-  `session_id` varchar(40) default NULL,
-  `cache_key` varchar(128) NOT NULL default '',
-  `created` datetime NOT NULL default '0000-00-00 00:00:00',
-  `data` longtext NOT NULL,
-  PRIMARY KEY  (`cache_id`),
-  INDEX `user_cache_index` (`user_id`,`cache_key`)
-);
+ `cache_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ `cache_key` varchar(128) /*!40101 CHARACTER SET ascii COLLATE ascii_general_ci */ NOT NULL ,
+ `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `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 */;
+
+
+-- Table structure for table `cache_index`
+
+CREATE TABLE `cache_index` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `mailbox` varchar(255) BINARY NOT NULL,
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `valid` tinyint(1) NOT NULL DEFAULT '0',
+ `data` longtext NOT NULL,
+ CONSTRAINT `user_id_fk_cache_index` FOREIGN KEY (`user_id`)
+   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `changed_index` (`changed`),
+ PRIMARY KEY (`user_id`, `mailbox`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+
+-- Table structure for table `cache_thread`
+
+CREATE TABLE `cache_thread` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `mailbox` varchar(255) BINARY NOT NULL,
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `data` longtext NOT NULL,
+ CONSTRAINT `user_id_fk_cache_thread` FOREIGN KEY (`user_id`)
+   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `changed_index` (`changed`),
+ PRIMARY KEY (`user_id`, `mailbox`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+
+-- Table structure for table `cache_messages`
+
+CREATE TABLE `cache_messages` (
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `mailbox` varchar(255) BINARY NOT NULL,
+ `uid` int(11) UNSIGNED NOT NULL DEFAULT '0',
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `data` longtext NOT NULL,
+ `flags` int(11) NOT NULL DEFAULT '0',
+ CONSTRAINT `user_id_fk_cache_messages` FOREIGN KEY (`user_id`)
+   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ INDEX `changed_index` (`changed`),
+ PRIMARY KEY (`user_id`, `mailbox`, `uid`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
 
--- --------------------------------------------------------
 
--- 
 -- Table structure for table `contacts`
--- 
 
 CREATE TABLE `contacts` (
-  `contact_id` int(10) unsigned NOT NULL auto_increment,
-  `user_id` int(10) unsigned NOT NULL default '0',
-  `changed` datetime NOT NULL default '0000-00-00 00:00:00',
-  `del` tinyint(1) NOT NULL default '0',
-  `name` varchar(128) NOT NULL default '',
-  `email` varchar(128) NOT NULL default '',
-  `firstname` varchar(128) NOT NULL default '',
-  `surname` varchar(128) NOT NULL default '',
-  `vcard` text NOT NULL,
-  PRIMARY KEY  (`contact_id`),
-  KEY `user_id` (`user_id`)
-);
-
--- --------------------------------------------------------
-
--- 
+ `contact_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `del` tinyint(1) NOT NULL DEFAULT '0',
+ `name` varchar(128) NOT NULL DEFAULT '',
+ `email` varchar(255) NOT NULL,
+ `firstname` varchar(128) NOT NULL DEFAULT '',
+ `surname` varchar(128) NOT NULL DEFAULT '',
+ `vcard` longtext NULL,
+ `words` 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 */;
+
+-- Table structure for table `contactgroups`
+
+CREATE TABLE `contactgroups` (
+  `contactgroup_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+  `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+  `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+  `del` tinyint(1) NOT NULL DEFAULT '0',
+  `name` varchar(128) NOT NULL DEFAULT '',
+  PRIMARY KEY(`contactgroup_id`),
+  CONSTRAINT `user_id_fk_contactgroups` FOREIGN KEY (`user_id`)
+    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  INDEX `contactgroups_user_index` (`user_id`,`del`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `contactgroupmembers` (
+  `contactgroup_id` int(10) UNSIGNED NOT NULL,
+  `contact_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+  `created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+  PRIMARY KEY (`contactgroup_id`, `contact_id`),
+  CONSTRAINT `contactgroup_id_fk_contactgroups` FOREIGN KEY (`contactgroup_id`)
+    REFERENCES `contactgroups`(`contactgroup_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT `contact_id_fk_contacts` FOREIGN KEY (`contact_id`)
+    REFERENCES `contacts`(`contact_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  INDEX `contactgroupmembers_contact_index` (`contact_id`)
+) /*!40000 ENGINE=INNODB */;
+
+
 -- Table structure for table `identities`
--- 
 
 CREATE TABLE `identities` (
-  `identity_id` int(10) unsigned NOT NULL auto_increment,
-  `user_id` int(10) unsigned NOT NULL default '0',
-  `del` tinyint(1) NOT NULL default '0',
-  `standard` tinyint(1) NOT NULL default '0',
-  `name` varchar(128) NOT NULL default '',
-  `organization` varchar(128) NOT NULL default '',
-  `email` varchar(128) NOT NULL default '',
-  `reply-to` varchar(128) NOT NULL default '',
-  `bcc` varchar(128) NOT NULL default '',
-  `signature` text NOT NULL,
-  `html_signature` tinyint(1) NOT NULL default '0',
-  PRIMARY KEY  (`identity_id`),
-  KEY `user_id` (`user_id`)
-);
-
--- --------------------------------------------------------
-
--- 
--- Table structure for table `session`
--- 
+ `identity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
+ `del` tinyint(1) NOT NULL DEFAULT '0',
+ `standard` tinyint(1) NOT NULL DEFAULT '0',
+ `name` varchar(128) NOT NULL,
+ `organization` varchar(128) NOT NULL DEFAULT '',
+ `email` varchar(128) NOT NULL,
+ `reply-to` varchar(128) NOT NULL DEFAULT '',
+ `bcc` varchar(128) NOT NULL DEFAULT '',
+ `signature` text,
+ `html_signature` tinyint(1) NOT NULL DEFAULT '0',
+ 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 */;
+
+
+-- Table structure for table `dictionary`
+
+CREATE TABLE `dictionary` (
+  `user_id` int(10) UNSIGNED DEFAULT NULL,
+  `language` varchar(5) NOT NULL,
+  `data` longtext NOT NULL,
+  CONSTRAINT `user_id_fk_dictionary` FOREIGN KEY (`user_id`)
+    REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+  UNIQUE `uniqueness` (`user_id`, `language`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
 
-CREATE TABLE `session` (
-  `sess_id` varchar(40) NOT NULL default '',
-  `created` datetime NOT NULL default '0000-00-00 00:00:00',
-  `changed` datetime NOT NULL default '0000-00-00 00:00:00',
-  `ip` VARCHAR(40) NOT NULL default '',
-  `vars` text NOT NULL,
-  PRIMARY KEY  (`sess_id`)
-);
 
--- --------------------------------------------------------
+-- Table structure for table `searches`
 
--- 
--- Table structure for table `users`
--- 
-
-CREATE TABLE `users` (
-  `user_id` int(10) unsigned NOT NULL auto_increment,
-  `username` varchar(128) NOT NULL default '',
-  `mail_host` varchar(128) NOT NULL default '',
-  `alias` varchar(128) NOT NULL default '',
-  `created` datetime NOT NULL default '0000-00-00 00:00:00',
-  `last_login` datetime NOT NULL default '0000-00-00 00:00:00',
-  `language` varchar(5) NOT NULL default 'en',
-  `preferences` text,
-  PRIMARY KEY  (`user_id`)
-);
-
--- --------------------------------------------------------
-
--- 
--- Table structure for table `messages`
--- 
-
-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`),
-  UNIQUE `uniqueness` (`user_id`, `cache_key`, `uid`)
-);
+CREATE TABLE `searches` (
+ `search_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+ `user_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
+ `type` int(3) NOT NULL DEFAULT '0',
+ `name` varchar(128) NOT NULL,
+ `data` text,
+ PRIMARY KEY(`search_id`),
+ CONSTRAINT `user_id_fk_searches` FOREIGN KEY (`user_id`)
+   REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
+ UNIQUE `uniqueness` (`user_id`, `type`, `name`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
 
 
+/*!40014 SET FOREIGN_KEY_CHECKS=1 */;