Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 278 lines (227 sloc) 7.541 kb
e019f2d @alecpl - s/RoundCube/Roundcube/
alecpl authored
1 -- Roundcube Webmail initial database structure
798ad5e @alecpl - remove version number from *.initial.sql
alecpl authored
2
977a295 @thomascube Usage of virtusertable; mail_domain for new users; Chinese and Turkish l...
thomascube authored
3 --
4 -- Sequence "user_ids"
5 -- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres
6 --
7
8 CREATE SEQUENCE user_ids
9 INCREMENT BY 1
10 NO MAXVALUE
11 NO MINVALUE
12 CACHE 1;
13
f5dc2a4 more changes to reflect postgres support
svncommit authored
14 --
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
15 -- Table "users"
16 -- Name: users; Type: TABLE; Schema: public; Owner: postgres
f5dc2a4 more changes to reflect postgres support
svncommit authored
17 --
18
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
19 CREATE TABLE users (
15a9d1c @thomascube Optimized loading time; added periodic mail check; added EXPUNGE command
thomascube authored
20 user_id integer DEFAULT nextval('user_ids'::text) PRIMARY KEY,
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
21 username varchar(128) DEFAULT '' NOT NULL,
22 mail_host varchar(128) DEFAULT '' NOT NULL,
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
23 created timestamp with time zone DEFAULT now() NOT NULL,
e2402ef @alecpl - Set DEFAULT NULL for users.last_login
alecpl authored
24 last_login timestamp with time zone DEFAULT NULL,
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
25 "language" varchar(5),
ace511a @alecpl - Add unique index on users.username+users.mail_host
alecpl authored
26 preferences text DEFAULT ''::text NOT NULL,
8381ec1 @alecpl - Force names of unique constraints in PostgreSQL DDL (postgres 9.x uses...
alecpl authored
27 CONSTRAINT users_username_key UNIQUE (username, mail_host)
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
28 );
f5dc2a4 more changes to reflect postgres support
svncommit authored
29
30
31 --
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
32 -- Table "session"
33 -- Name: session; Type: TABLE; Schema: public; Owner: postgres
f5dc2a4 more changes to reflect postgres support
svncommit authored
34 --
35
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
36 CREATE TABLE "session" (
b8ae0ec @alecpl - Fixed bug where session ID's length was limited to 40 characters. Sinc...
alecpl authored
37 sess_id varchar(128) DEFAULT '' PRIMARY KEY,
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
38 created timestamp with time zone DEFAULT now() NOT NULL,
39 changed timestamp with time zone DEFAULT now() NOT NULL,
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
40 ip varchar(41) NOT NULL,
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
41 vars text NOT NULL
42 );
f5dc2a4 more changes to reflect postgres support
svncommit authored
43
3e48d2e @alecpl - Increase speed of session destroy and garbage clean up
alecpl authored
44 CREATE INDEX session_changed_idx ON session (changed);
f5dc2a4 more changes to reflect postgres support
svncommit authored
45
46
47 --
b594741 @thomascube Add created date to message cache
thomascube authored
48 -- Sequence "identity_ids"
49 -- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres
50 --
51
52 CREATE SEQUENCE identity_ids
53 START WITH 1
54 INCREMENT BY 1
55 NO MAXVALUE
56 NO MINVALUE
57 CACHE 1;
58
59 --
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
60 -- Table "identities"
61 -- Name: identities; Type: TABLE; Schema: public; Owner: postgres
f5dc2a4 more changes to reflect postgres support
svncommit authored
62 --
63
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
64 CREATE TABLE identities (
15a9d1c @thomascube Optimized loading time; added periodic mail check; added EXPUNGE command
thomascube authored
65 identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY,
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
66 user_id integer NOT NULL
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
67 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
a35062a @thomascube Always set changed date when marking a DB record as deleted + provide a ...
thomascube authored
68 changed timestamp with time zone DEFAULT now() NOT NULL,
a493ea8 @alecpl - change few "boolean" columns type from integer to smallint
alecpl authored
69 del smallint DEFAULT 0 NOT NULL,
70 standard smallint DEFAULT 0 NOT NULL,
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
71 name varchar(128) NOT NULL,
72 organization varchar(128),
73 email varchar(128) NOT NULL,
74 "reply-to" varchar(128),
75 bcc varchar(128),
a0109c4 Initial TinyMCE editor support (still need to work on spellcheck and ski...
svncommit authored
76 signature text,
77 html_signature integer DEFAULT 0 NOT NULL
f5dc2a4 more changes to reflect postgres support
svncommit authored
78 );
79
94fe9ca @alecpl - re-fix (#1486474) + require MySQL 4.0.8 + add index/update in identiti...
alecpl authored
80 CREATE INDEX identities_user_id_idx ON identities (user_id, del);
565c472 @alecpl Removed users.alias column, added option ('user_aliases')
alecpl authored
81 CREATE INDEX identities_email_idx ON identities (email, del);
f5dc2a4 more changes to reflect postgres support
svncommit authored
82
b594741 @thomascube Add created date to message cache
thomascube authored
83
84 --
85 -- Sequence "contact_ids"
86 -- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres
87 --
88
89 CREATE SEQUENCE contact_ids
90 START WITH 1
91 INCREMENT BY 1
92 NO MAXVALUE
93 NO MINVALUE
94 CACHE 1;
95
f5dc2a4 more changes to reflect postgres support
svncommit authored
96 --
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
97 -- Table "contacts"
f5dc2a4 more changes to reflect postgres support
svncommit authored
98 -- Name: contacts; Type: TABLE; Schema: public; Owner: postgres
99 --
100
101 CREATE TABLE contacts (
15a9d1c @thomascube Optimized loading time; added periodic mail check; added EXPUNGE command
thomascube authored
102 contact_id integer DEFAULT nextval('contact_ids'::text) PRIMARY KEY,
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
103 user_id integer NOT NULL
3e26373 @thomascube Fulltext search over contact fields. Attention: DATABASE SCHEMA CHANGED\...
thomascube authored
104 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
105 changed timestamp with time zone DEFAULT now() NOT NULL,
a493ea8 @alecpl - change few "boolean" columns type from integer to smallint
alecpl authored
106 del smallint DEFAULT 0 NOT NULL,
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
107 name varchar(128) DEFAULT '' NOT NULL,
48be8f6 @alecpl - Fix SQL Error when saving a contact with many email addresses (#148828...
alecpl authored
108 email text DEFAULT '' NOT NULL,
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
109 firstname varchar(128) DEFAULT '' NOT NULL,
110 surname varchar(128) DEFAULT '' NOT NULL,
3e26373 @thomascube Fulltext search over contact fields. Attention: DATABASE SCHEMA CHANGED\...
thomascube authored
111 vcard text,
112 words text
f5dc2a4 more changes to reflect postgres support
svncommit authored
113 );
114
48be8f6 @alecpl - Fix SQL Error when saving a contact with many email addresses (#148828...
alecpl authored
115 CREATE INDEX contacts_user_id_idx ON contacts (user_id, del);
f5dc2a4 more changes to reflect postgres support
svncommit authored
116
117 --
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
118 -- Sequence "contactgroups_ids"
119 -- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres
120 --
121
122 CREATE SEQUENCE contactgroups_ids
123 INCREMENT BY 1
124 NO MAXVALUE
125 NO MINVALUE
126 CACHE 1;
127
128 --
129 -- Table "contactgroups"
130 -- Name: contactgroups; Type: TABLE; Schema: public; Owner: postgres
131 --
132
133 CREATE TABLE contactgroups (
134 contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY,
135 user_id integer NOT NULL
136 REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
137 changed timestamp with time zone DEFAULT now() NOT NULL,
138 del smallint NOT NULL DEFAULT 0,
139 name varchar(128) NOT NULL DEFAULT ''
140 );
141
142 CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del);
143
144 --
145 -- Table "contactgroupmembers"
146 -- Name: contactgroupmembers; Type: TABLE; Schema: public; Owner: postgres
147 --
ace511a @alecpl - Add unique index on users.username+users.mail_host
alecpl authored
148
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
149 CREATE TABLE contactgroupmembers (
150 contactgroup_id integer NOT NULL
151 REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE,
152 contact_id integer NOT NULL
153 REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
154 created timestamp with time zone DEFAULT now() NOT NULL,
155 PRIMARY KEY (contactgroup_id, contact_id)
156 );
157
3a5476d @alecpl - Add index on contactgroupmembers.contact_id column.
alecpl authored
158 CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_id);
159
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
160 --
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
161 -- Table "cache"
162 -- Name: cache; Type: TABLE; Schema: public; Owner: postgres
f5dc2a4 more changes to reflect postgres support
svncommit authored
163 --
164
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
165 CREATE TABLE "cache" (
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
166 user_id integer NOT NULL
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
167 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
168 cache_key varchar(128) DEFAULT '' NOT NULL,
f5dc2a4 more changes to reflect postgres support
svncommit authored
169 created timestamp with time zone DEFAULT now() NOT NULL,
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
170 data text NOT NULL
f5dc2a4 more changes to reflect postgres support
svncommit authored
171 );
172
edc63c2 @alecpl fix: there's no ALTER TABLE ... ADD INDEX in postgresql
alecpl authored
173 CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key);
e4d9f05 @alecpl - Added index on cache.created + small fixes in mssql DDL script
alecpl authored
174 CREATE INDEX cache_created_idx ON "cache" (created);
1cded85 @thomascube Re-design of caching (new database table added\!); some bugfixes; Postgr...
thomascube authored
175
f5dc2a4 more changes to reflect postgres support
svncommit authored
176 --
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
177 -- Table "cache_index"
178 -- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres
b594741 @thomascube Add created date to message cache
thomascube authored
179 --
180
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
181 CREATE TABLE cache_index (
182 user_id integer NOT NULL
183 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
184 mailbox varchar(255) NOT NULL,
185 changed timestamp with time zone DEFAULT now() NOT NULL,
609d392 @alecpl - Cache synchronization using QRESYNC/CONDSTORE
alecpl authored
186 valid smallint NOT NULL DEFAULT 0,
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
187 data text NOT NULL,
188 PRIMARY KEY (user_id, mailbox)
189 );
190
191 CREATE INDEX cache_index_changed_idx ON cache_index (changed);
b594741 @thomascube Add created date to message cache
thomascube authored
192
193 --
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
194 -- Table "cache_thread"
195 -- Name: cache_thread; Type: TABLE; Schema: public; Owner: postgres
f5dc2a4 more changes to reflect postgres support
svncommit authored
196 --
197
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
198 CREATE TABLE cache_thread (
22d6b53 @alecpl - contactgroups DDL for postgres
alecpl authored
199 user_id integer NOT NULL
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
200 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
201 mailbox varchar(255) NOT NULL,
202 changed timestamp with time zone DEFAULT now() NOT NULL,
203 data text NOT NULL,
204 PRIMARY KEY (user_id, mailbox)
205 );
206
207 CREATE INDEX cache_thread_changed_idx ON cache_thread (changed);
208
209 --
210 -- Table "cache_messages"
211 -- Name: cache_messages; Type: TABLE; Schema: public; Owner: postgres
212 --
213
214 CREATE TABLE cache_messages (
215 user_id integer NOT NULL
216 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
217 mailbox varchar(255) NOT NULL,
218 uid integer NOT NULL,
219 changed timestamp with time zone DEFAULT now() NOT NULL,
220 data text NOT NULL,
609d392 @alecpl - Cache synchronization using QRESYNC/CONDSTORE
alecpl authored
221 flags integer NOT NULL DEFAULT 0,
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
222 PRIMARY KEY (user_id, mailbox, uid)
f5dc2a4 more changes to reflect postgres support
svncommit authored
223 );
224
80152b3 @alecpl - Rewritten messages caching (merged devel-mcache branch):
alecpl authored
225 CREATE INDEX cache_messages_changed_idx ON cache_messages (changed);
66df084 @alecpl - Merge devel-spellcheck branch:
alecpl authored
226
227 --
228 -- Table "dictionary"
229 -- Name: dictionary; Type: TABLE; Schema: public; Owner: postgres
230 --
231
232 CREATE TABLE dictionary (
233 user_id integer DEFAULT NULL
234 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
235 "language" varchar(5) NOT NULL,
236 data text NOT NULL,
237 CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language")
238 );
f8e48df @alecpl - Merge devel-saved_search branch (Addressbook Saved Searches)
alecpl authored
239
240 --
241 -- Sequence "searches_ids"
242 -- Name: searches_ids; Type: SEQUENCE; Schema: public; Owner: postgres
243 --
244
245 CREATE SEQUENCE search_ids
246 INCREMENT BY 1
247 NO MAXVALUE
248 NO MINVALUE
249 CACHE 1;
250
251 --
252 -- Table "searches"
253 -- Name: searches; Type: TABLE; Schema: public; Owner: postgres
254 --
255
256 CREATE TABLE searches (
257 search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY,
258 user_id integer NOT NULL
259 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
260 "type" smallint DEFAULT 0 NOT NULL,
261 name varchar(128) NOT NULL,
262 data text NOT NULL,
263 CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name)
264 );
b7e7c8f @alecpl Added new database upgrade script, converted DDL scripts to new format
alecpl authored
265
266
267 --
268 -- Table "system"
269 -- Name: system; Type: TABLE; Schema: public; Owner: postgres
270 --
271
272 CREATE TABLE "system" (
273 name varchar(64) NOT NULL PRIMARY KEY,
274 value text
275 );
276
0ffb1a7 @alecpl Fix typo in DB version strings (there should be no .sql suffix)
alecpl authored
277 INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011700');
Something went wrong with that request. Please try again.