--- 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 */;
projects / roundcube.git / blobdiff