1
|
|
2
|
|
3
|
|
4
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
5
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
6
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
7
|
/*!40101 SET NAMES utf8 */;
|
8
|
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
|
9
|
/*!40103 SET TIME_ZONE='+00:00' */;
|
10
|
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
|
11
|
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
12
|
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
13
|
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
|
14
|
|
15
|
|
16
|
|
17
|
|
18
|
|
19
|
CREATE DATABASE `roundcube`;
|
20
|
|
21
|
|
22
|
GRANT select,insert,update,delete ON roundcube.* TO roundcube@192.0.2.15 IDENTIFIED BY 'rcpassword';
|
23
|
|
24
|
USE `roundcube`;
|
25
|
|
26
|
|
27
|
|
28
|
|
29
|
|
30
|
CREATE TABLE `cache` (
|
31
|
`cache_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
32
|
`cache_key` varchar(128) CHARACTER SET ascii NOT NULL,
|
33
|
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
34
|
`data` longtext NOT NULL,
|
35
|
`user_id` int(10) unsigned NOT NULL DEFAULT '0',
|
36
|
PRIMARY KEY (`cache_id`),
|
37
|
KEY `created_index` (`created`),
|
38
|
KEY `user_cache_index` (`user_id`,`cache_key`),
|
39
|
CONSTRAINT `user_id_fk_cache` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
|
40
|
);
|
41
|
|
42
|
|
43
|
|
44
|
|
45
|
|
46
|
CREATE TABLE `contacts` (
|
47
|
`contact_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
48
|
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
49
|
`del` tinyint(1) NOT NULL DEFAULT '0',
|
50
|
`name` varchar(128) NOT NULL,
|
51
|
`email` varchar(128) NOT NULL,
|
52
|
`firstname` varchar(128) NOT NULL,
|
53
|
`surname` varchar(128) NOT NULL,
|
54
|
`vcard` text,
|
55
|
`user_id` int(10) unsigned NOT NULL DEFAULT '0',
|
56
|
PRIMARY KEY (`contact_id`),
|
57
|
KEY `user_contacts_index` (`user_id`,`email`),
|
58
|
CONSTRAINT `user_id_fk_contacts` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
|
59
|
);
|
60
|
|
61
|
|
62
|
|
63
|
|
64
|
|
65
|
CREATE TABLE `identities` (
|
66
|
`identity_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
67
|
`del` tinyint(1) NOT NULL DEFAULT '0',
|
68
|
`standard` tinyint(1) NOT NULL DEFAULT '0',
|
69
|
`name` varchar(128) NOT NULL,
|
70
|
`organization` varchar(128) NOT NULL DEFAULT '',
|
71
|
`email` varchar(128) NOT NULL,
|
72
|
`reply-to` varchar(128) NOT NULL DEFAULT '',
|
73
|
`bcc` varchar(128) NOT NULL DEFAULT '',
|
74
|
`signature` text,
|
75
|
`html_signature` tinyint(1) NOT NULL DEFAULT '0',
|
76
|
`user_id` int(10) unsigned NOT NULL DEFAULT '0',
|
77
|
PRIMARY KEY (`identity_id`),
|
78
|
KEY `user_id_fk_identities` (`user_id`),
|
79
|
CONSTRAINT `user_id_fk_identities` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
|
80
|
);
|
81
|
|
82
|
|
83
|
|
84
|
|
85
|
|
86
|
CREATE TABLE `messages` (
|
87
|
`message_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
88
|
`user_id` int(10) unsigned NOT NULL DEFAULT '0',
|
89
|
`del` tinyint(1) NOT NULL DEFAULT '0',
|
90
|
`cache_key` varchar(128) CHARACTER SET ascii NOT NULL,
|
91
|
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
92
|
`idx` int(11) unsigned NOT NULL DEFAULT '0',
|
93
|
`uid` int(11) unsigned NOT NULL DEFAULT '0',
|
94
|
`subject` varchar(255) NOT NULL,
|
95
|
`from` varchar(255) NOT NULL,
|
96
|
`to` varchar(255) NOT NULL,
|
97
|
`cc` varchar(255) NOT NULL,
|
98
|
`date` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
99
|
`size` int(11) unsigned NOT NULL DEFAULT '0',
|
100
|
`headers` text NOT NULL,
|
101
|
`structure` text,
|
102
|
PRIMARY KEY (`message_id`),
|
103
|
UNIQUE KEY `uniqueness` (`user_id`,`cache_key`,`uid`),
|
104
|
KEY `created_index` (`created`),
|
105
|
KEY `index_index` (`user_id`,`cache_key`,`idx`),
|
106
|
CONSTRAINT `user_id_fk_messages` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
|
107
|
);
|
108
|
|
109
|
|
110
|
|
111
|
|
112
|
|
113
|
CREATE TABLE `session` (
|
114
|
`sess_id` varchar(40) NOT NULL,
|
115
|
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
116
|
`changed` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
117
|
`ip` varchar(40) NOT NULL,
|
118
|
`vars` mediumtext NOT NULL,
|
119
|
PRIMARY KEY (`sess_id`),
|
120
|
KEY `changed_index` (`changed`)
|
121
|
);
|
122
|
|
123
|
|
124
|
|
125
|
|
126
|
|
127
|
CREATE TABLE `users` (
|
128
|
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
129
|
`username` varchar(128) NOT NULL,
|
130
|
`mail_host` varchar(128) NOT NULL,
|
131
|
`alias` varchar(128) NOT NULL,
|
132
|
`created` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
133
|
`last_login` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
|
134
|
`language` varchar(5) DEFAULT NULL,
|
135
|
`preferences` text,
|
136
|
PRIMARY KEY (`user_id`),
|
137
|
KEY `username_index` (`username`),
|
138
|
KEY `alias_index` (`alias`)
|
139
|
);
|
140
|
|