|
0
|
1 -- Roundcube Webmail initial database structure
|
|
|
2
|
|
|
3 --
|
|
|
4 -- Table structure for table contacts and related
|
|
|
5 --
|
|
|
6
|
|
|
7 CREATE TABLE contacts (
|
|
|
8 contact_id integer NOT NULL PRIMARY KEY,
|
|
|
9 user_id integer NOT NULL,
|
|
|
10 changed datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
11 del tinyint NOT NULL default '0',
|
|
|
12 name varchar(128) NOT NULL default '',
|
|
|
13 email text NOT NULL default '',
|
|
|
14 firstname varchar(128) NOT NULL default '',
|
|
|
15 surname varchar(128) NOT NULL default '',
|
|
|
16 vcard text NOT NULL default '',
|
|
|
17 words text NOT NULL default ''
|
|
|
18 );
|
|
|
19
|
|
|
20 CREATE INDEX ix_contacts_user_id ON contacts(user_id, del);
|
|
|
21
|
|
|
22
|
|
|
23 CREATE TABLE contactgroups (
|
|
|
24 contactgroup_id integer NOT NULL PRIMARY KEY,
|
|
|
25 user_id integer NOT NULL default '0',
|
|
|
26 changed datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
27 del tinyint NOT NULL default '0',
|
|
|
28 name varchar(128) NOT NULL default ''
|
|
|
29 );
|
|
|
30
|
|
|
31 CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del);
|
|
|
32
|
|
|
33
|
|
|
34 CREATE TABLE contactgroupmembers (
|
|
|
35 contactgroup_id integer NOT NULL,
|
|
|
36 contact_id integer NOT NULL default '0',
|
|
|
37 created datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
38 PRIMARY KEY (contactgroup_id, contact_id)
|
|
|
39 );
|
|
|
40
|
|
|
41 CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id);
|
|
|
42
|
|
|
43 --
|
|
|
44 -- Table structure for table identities
|
|
|
45 --
|
|
|
46
|
|
|
47 CREATE TABLE identities (
|
|
|
48 identity_id integer NOT NULL PRIMARY KEY,
|
|
|
49 user_id integer NOT NULL default '0',
|
|
|
50 changed datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
51 del tinyint NOT NULL default '0',
|
|
|
52 standard tinyint NOT NULL default '0',
|
|
|
53 name varchar(128) NOT NULL default '',
|
|
|
54 organization varchar(128) default '',
|
|
|
55 email varchar(128) NOT NULL default '',
|
|
|
56 "reply-to" varchar(128) NOT NULL default '',
|
|
|
57 bcc varchar(128) NOT NULL default '',
|
|
|
58 signature text NOT NULL default '',
|
|
|
59 html_signature tinyint NOT NULL default '0'
|
|
|
60 );
|
|
|
61
|
|
|
62 CREATE INDEX ix_identities_user_id ON identities(user_id, del);
|
|
|
63 CREATE INDEX ix_identities_email ON identities(email, del);
|
|
|
64
|
|
|
65 --
|
|
|
66 -- Table structure for table users
|
|
|
67 --
|
|
|
68
|
|
|
69 CREATE TABLE users (
|
|
|
70 user_id integer NOT NULL PRIMARY KEY,
|
|
|
71 username varchar(128) NOT NULL default '',
|
|
|
72 mail_host varchar(128) NOT NULL default '',
|
|
|
73 created datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
74 last_login datetime DEFAULT NULL,
|
|
|
75 language varchar(5),
|
|
|
76 preferences text NOT NULL default ''
|
|
|
77 );
|
|
|
78
|
|
|
79 CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
|
|
|
80
|
|
|
81 --
|
|
|
82 -- Table structure for table session
|
|
|
83 --
|
|
|
84
|
|
|
85 CREATE TABLE session (
|
|
|
86 sess_id varchar(128) NOT NULL PRIMARY KEY,
|
|
|
87 created datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
88 changed datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
89 ip varchar(40) NOT NULL default '',
|
|
|
90 vars text NOT NULL
|
|
|
91 );
|
|
|
92
|
|
|
93 CREATE INDEX ix_session_changed ON session (changed);
|
|
|
94
|
|
|
95 --
|
|
|
96 -- Table structure for table dictionary
|
|
|
97 --
|
|
|
98
|
|
|
99 CREATE TABLE dictionary (
|
|
|
100 user_id integer DEFAULT NULL,
|
|
|
101 "language" varchar(5) NOT NULL,
|
|
|
102 data text NOT NULL
|
|
|
103 );
|
|
|
104
|
|
|
105 CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language");
|
|
|
106
|
|
|
107 --
|
|
|
108 -- Table structure for table searches
|
|
|
109 --
|
|
|
110
|
|
|
111 CREATE TABLE searches (
|
|
|
112 search_id integer NOT NULL PRIMARY KEY,
|
|
|
113 user_id integer NOT NULL DEFAULT '0',
|
|
|
114 "type" smallint NOT NULL DEFAULT '0',
|
|
|
115 name varchar(128) NOT NULL,
|
|
|
116 data text NOT NULL
|
|
|
117 );
|
|
|
118
|
|
|
119 CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name);
|
|
|
120
|
|
|
121 --
|
|
|
122 -- Table structure for table cache
|
|
|
123 --
|
|
|
124
|
|
|
125 CREATE TABLE cache (
|
|
|
126 user_id integer NOT NULL default 0,
|
|
|
127 cache_key varchar(128) NOT NULL default '',
|
|
|
128 created datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
129 expires datetime DEFAULT NULL,
|
|
|
130 data text NOT NULL
|
|
|
131 );
|
|
|
132
|
|
|
133 CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key);
|
|
|
134 CREATE INDEX ix_cache_expires ON cache(expires);
|
|
|
135
|
|
|
136 --
|
|
|
137 -- Table structure for table cache_shared
|
|
|
138 --
|
|
|
139
|
|
|
140 CREATE TABLE cache_shared (
|
|
|
141 cache_key varchar(255) NOT NULL,
|
|
|
142 created datetime NOT NULL default '0000-00-00 00:00:00',
|
|
|
143 expires datetime DEFAULT NULL,
|
|
|
144 data text NOT NULL
|
|
|
145 );
|
|
|
146
|
|
|
147 CREATE INDEX ix_cache_shared_cache_key ON cache_shared(cache_key);
|
|
|
148 CREATE INDEX ix_cache_shared_expires ON cache_shared(expires);
|
|
|
149
|
|
|
150 --
|
|
|
151 -- Table structure for table cache_index
|
|
|
152 --
|
|
|
153
|
|
|
154 CREATE TABLE cache_index (
|
|
|
155 user_id integer NOT NULL,
|
|
|
156 mailbox varchar(255) NOT NULL,
|
|
|
157 expires datetime DEFAULT NULL,
|
|
|
158 valid smallint NOT NULL DEFAULT '0',
|
|
|
159 data text NOT NULL,
|
|
|
160 PRIMARY KEY (user_id, mailbox)
|
|
|
161 );
|
|
|
162
|
|
|
163 CREATE INDEX ix_cache_index_expires ON cache_index (expires);
|
|
|
164
|
|
|
165 --
|
|
|
166 -- Table structure for table cache_thread
|
|
|
167 --
|
|
|
168
|
|
|
169 CREATE TABLE cache_thread (
|
|
|
170 user_id integer NOT NULL,
|
|
|
171 mailbox varchar(255) NOT NULL,
|
|
|
172 expires datetime DEFAULT NULL,
|
|
|
173 data text NOT NULL,
|
|
|
174 PRIMARY KEY (user_id, mailbox)
|
|
|
175 );
|
|
|
176
|
|
|
177 CREATE INDEX ix_cache_thread_expires ON cache_thread (expires);
|
|
|
178
|
|
|
179 --
|
|
|
180 -- Table structure for table cache_messages
|
|
|
181 --
|
|
|
182
|
|
|
183 CREATE TABLE cache_messages (
|
|
|
184 user_id integer NOT NULL,
|
|
|
185 mailbox varchar(255) NOT NULL,
|
|
|
186 uid integer NOT NULL,
|
|
|
187 expires datetime DEFAULT NULL,
|
|
|
188 data text NOT NULL,
|
|
|
189 flags integer NOT NULL DEFAULT '0',
|
|
|
190 PRIMARY KEY (user_id, mailbox, uid)
|
|
|
191 );
|
|
|
192
|
|
|
193 CREATE INDEX ix_cache_messages_expires ON cache_messages (expires);
|
|
|
194
|
|
|
195 --
|
|
|
196 -- Table structure for table system
|
|
|
197 --
|
|
|
198
|
|
|
199 CREATE TABLE system (
|
|
|
200 name varchar(64) NOT NULL PRIMARY KEY,
|
|
|
201 value text NOT NULL
|
|
|
202 );
|
|
|
203
|
|
|
204 INSERT INTO system (name, value) VALUES ('roundcube-version', '2015030800');
|
