Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 200 lines (151 sloc) 5.319 kb
e019f2d @alecpl - s/RoundCube/Roundcube/
alecpl authored
1 -- Roundcube Webmail initial database structure
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
2
3 --
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
4 -- Table structure for table cache
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
5 --
6
7 CREATE TABLE cache (
8 user_id integer NOT NULL default 0,
9 cache_key varchar(128) NOT NULL default '',
10 created datetime NOT NULL default '0000-00-00 00:00:00',
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
11 data text NOT NULL
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
12 );
13
ac26b96 @thomascube Optimize database schema; get rid of unnecessary indexes
thomascube authored
14 CREATE INDEX ix_cache_user_cache_key ON cache(user_id, cache_key);
e4d9f05 @alecpl - Added index on cache.created + small fixes in mssql DDL script
alecpl authored
15 CREATE INDEX ix_cache_created ON cache(created);
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
16
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
17
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
18 -- --------------------------------------------------------
19
20 --
a61bbb2 @thomascube Added basic contact groups feature
thomascube authored
21 -- Table structure for table contacts and related
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
22 --
23
24 CREATE TABLE contacts (
25 contact_id integer NOT NULL PRIMARY KEY,
48be8f6 @alecpl - Fix SQL Error when saving a contact with many email addresses (#148828...
alecpl authored
26 user_id integer NOT NULL,
58e3602 @thomascube Bugfixes for encoding and sending with attachments
thomascube authored
27 changed datetime NOT NULL default '0000-00-00 00:00:00',
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
28 del tinyint NOT NULL default '0',
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
29 name varchar(128) NOT NULL default '',
48be8f6 @alecpl - Fix SQL Error when saving a contact with many email addresses (#148828...
alecpl authored
30 email text NOT NULL default '',
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
31 firstname varchar(128) NOT NULL default '',
32 surname varchar(128) NOT NULL default '',
3e26373 @thomascube Fulltext search over contact fields. Attention: DATABASE SCHEMA CHANGED\...
thomascube authored
33 vcard text NOT NULL default '',
34 words text NOT NULL default ''
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
35 );
36
48be8f6 @alecpl - Fix SQL Error when saving a contact with many email addresses (#148828...
alecpl authored
37 CREATE INDEX ix_contacts_user_id ON contacts(user_id, del);
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
38
a61bbb2 @thomascube Added basic contact groups feature
thomascube authored
39
40 CREATE TABLE contactgroups (
41 contactgroup_id integer NOT NULL PRIMARY KEY,
42 user_id integer NOT NULL default '0',
43 changed datetime NOT NULL default '0000-00-00 00:00:00',
44 del tinyint NOT NULL default '0',
45 name varchar(128) NOT NULL default ''
46 );
47
48 CREATE INDEX ix_contactgroups_user_id ON contactgroups(user_id, del);
49
50
51 CREATE TABLE contactgroupmembers (
52 contactgroup_id integer NOT NULL,
53 contact_id integer NOT NULL default '0',
54 created datetime NOT NULL default '0000-00-00 00:00:00',
55 PRIMARY KEY (contactgroup_id, contact_id)
56 );
57
3a5476d @alecpl - Add index on contactgroupmembers.contact_id column.
alecpl authored
58 CREATE INDEX ix_contactgroupmembers_contact_id ON contactgroupmembers (contact_id);
59
a61bbb2 @thomascube Added basic contact groups feature
thomascube authored
60
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
61 -- --------------------------------------------------------
62
63 --
64 -- Table structure for table identities
65 --
66
67 CREATE TABLE identities (
68 identity_id integer NOT NULL PRIMARY KEY,
69 user_id integer NOT NULL default '0',
a35062a @thomascube Always set changed date when marking a DB record as deleted + provide a ...
thomascube authored
70 changed datetime NOT NULL default '0000-00-00 00:00:00',
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
71 del tinyint NOT NULL default '0',
72 standard tinyint NOT NULL default '0',
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
73 name varchar(128) NOT NULL default '',
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
74 organization varchar(128) default '',
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
75 email varchar(128) NOT NULL default '',
76 "reply-to" varchar(128) NOT NULL default '',
77 bcc varchar(128) NOT NULL default '',
a0109c4 Initial TinyMCE editor support (still need to work on spellcheck and ski...
svncommit authored
78 signature text NOT NULL default '',
79 html_signature tinyint NOT NULL default '0'
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
80 );
81
94fe9ca @alecpl - re-fix (#1486474) + require MySQL 4.0.8 + add index/update in identiti...
alecpl authored
82 CREATE INDEX ix_identities_user_id ON identities(user_id, del);
565c472 @alecpl Removed users.alias column, added option ('user_aliases')
alecpl authored
83 CREATE INDEX ix_identities_email ON identities(email, del);
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
84
85
86 -- --------------------------------------------------------
87
88 --
89 -- Table structure for table users
90 --
91
92 CREATE TABLE users (
93 user_id integer NOT NULL PRIMARY KEY,
94 username varchar(128) NOT NULL default '',
95 mail_host varchar(128) NOT NULL default '',
96 created datetime NOT NULL default '0000-00-00 00:00:00',
e2402ef @alecpl - Set DEFAULT NULL for users.last_login
alecpl authored
97 last_login datetime DEFAULT NULL,
debdda0 @alecpl #1485420: remove default (and not null) for users.language column in DDL
alecpl authored
98 language varchar(5),
79fe17f @thomascube Added SQLite supprt and MySQL update script
thomascube authored
99 preferences text NOT NULL default ''
100 );
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
101
ace511a @alecpl - Add unique index on users.username+users.mail_host
alecpl authored
102 CREATE UNIQUE INDEX ix_users_username ON users(username, mail_host);
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
103
104 -- --------------------------------------------------------
105
106 --
107 -- Table structure for table session
108 --
109
110 CREATE TABLE session (
b8ae0ec @alecpl - Fixed bug where session ID's length was limited to 40 characters. Sinc...
alecpl authored
111 sess_id varchar(128) NOT NULL PRIMARY KEY,
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
112 created datetime NOT NULL default '0000-00-00 00:00:00',
113 changed datetime NOT NULL default '0000-00-00 00:00:00',
84d06ed @thomascube IPv6 Compatability
thomascube authored
114 ip varchar(40) NOT NULL default '',
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
115 vars text NOT NULL
116 );
117
3e48d2e @alecpl - Increase speed of session destroy and garbage clean up
alecpl authored
118 CREATE INDEX ix_session_changed ON session (changed);
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
119
120 -- --------------------------------------------------------
121
66df084 @alecpl - Merge devel-spellcheck branch:
alecpl authored
122 --
123 -- Table structure for table dictionary
124 --
125
126 CREATE TABLE dictionary (
127 user_id integer DEFAULT NULL,
128 "language" varchar(5) NOT NULL,
129 data text NOT NULL
130 );
131
132 CREATE UNIQUE INDEX ix_dictionary_user_language ON dictionary (user_id, "language");
133
f8e48df @alecpl - Merge devel-saved_search branch (Addressbook Saved Searches)
alecpl authored
134 -- --------------------------------------------------------
135
136 --
137 -- Table structure for table searches
138 --
139
140 CREATE TABLE searches (
141 search_id integer NOT NULL PRIMARY KEY,
142 user_id integer NOT NULL DEFAULT '0',
143 "type" smallint NOT NULL DEFAULT '0',
144 name varchar(128) NOT NULL,
145 data text NOT NULL
146 );
147
bd9190a @alecpl - Fix broken CREATE INDEX queries in SQLite DDL files (#1488255)
alecpl authored
148 CREATE UNIQUE INDEX ix_searches_user_type_name ON searches (user_id, type, name);
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
149
150 -- --------------------------------------------------------
151
152 --
153 -- Table structure for table cache_index
154 --
155
156 CREATE TABLE cache_index (
157 user_id integer NOT NULL,
158 mailbox varchar(255) NOT NULL,
159 changed datetime NOT NULL default '0000-00-00 00:00:00',
609d392 @alecpl - Cache synchronization using QRESYNC/CONDSTORE
alecpl authored
160 valid smallint NOT NULL DEFAULT '0',
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
161 data text NOT NULL,
162 PRIMARY KEY (user_id, mailbox)
163 );
164
165 CREATE INDEX ix_cache_index_changed ON cache_index (changed);
166
167 -- --------------------------------------------------------
168
169 --
170 -- Table structure for table cache_thread
171 --
172
173 CREATE TABLE cache_thread (
174 user_id integer NOT NULL,
175 mailbox varchar(255) NOT NULL,
176 changed datetime NOT NULL default '0000-00-00 00:00:00',
177 data text NOT NULL,
178 PRIMARY KEY (user_id, mailbox)
179 );
180
181 CREATE INDEX ix_cache_thread_changed ON cache_thread (changed);
182
183 -- --------------------------------------------------------
184
185 --
186 -- Table structure for table cache_messages
187 --
188
189 CREATE TABLE cache_messages (
190 user_id integer NOT NULL,
191 mailbox varchar(255) NOT NULL,
192 uid integer NOT NULL,
193 changed datetime NOT NULL default '0000-00-00 00:00:00',
194 data text NOT NULL,
609d392 @alecpl - Cache synchronization using QRESYNC/CONDSTORE
alecpl authored
195 flags integer NOT NULL DEFAULT '0',
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
196 PRIMARY KEY (user_id, mailbox, uid)
197 );
198
199 CREATE INDEX ix_cache_messages_changed ON cache_messages (changed);
Something went wrong with that request. Please try again.