1 -- Roundcube Webmail initial database structure
5 -- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres
8 CREATE SEQUENCE user_ids
16 -- Name: users; Type: TABLE; Schema: public; Owner: postgres
20 user_id integer DEFAULT nextval('user_ids'::text) PRIMARY KEY,
21 username varchar(128) DEFAULT '' NOT NULL,
22 mail_host varchar(128) DEFAULT '' NOT NULL,
23 alias varchar(128) DEFAULT '' NOT NULL,
24 created timestamp with time zone DEFAULT now() NOT NULL,
25 last_login timestamp with time zone DEFAULT NULL,
26 "language" varchar(5),
27 preferences text DEFAULT ''::text NOT NULL,
28 CONSTRAINT users_username_key UNIQUE (username, mail_host)
31 CREATE INDEX users_alias_id_idx ON users (alias);
36 -- Name: session; Type: TABLE; Schema: public; Owner: postgres
39 CREATE TABLE "session" (
40 sess_id varchar(128) DEFAULT '' PRIMARY KEY,
41 created timestamp with time zone DEFAULT now() NOT NULL,
42 changed timestamp with time zone DEFAULT now() NOT NULL,
43 ip varchar(41) NOT NULL,
47 CREATE INDEX session_changed_idx ON session (changed);
51 -- Sequence "identity_ids"
52 -- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres
55 CREATE SEQUENCE identity_ids
64 -- Name: identities; Type: TABLE; Schema: public; Owner: postgres
67 CREATE TABLE identities (
68 identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY,
69 user_id integer NOT NULL
70 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
71 changed timestamp with time zone DEFAULT now() NOT NULL,
72 del smallint DEFAULT 0 NOT NULL,
73 standard smallint DEFAULT 0 NOT NULL,
74 name varchar(128) NOT NULL,
75 organization varchar(128),
76 email varchar(128) NOT NULL,
77 "reply-to" varchar(128),
80 html_signature integer DEFAULT 0 NOT NULL
83 CREATE INDEX identities_user_id_idx ON identities (user_id, del);
87 -- Sequence "contact_ids"
88 -- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres
91 CREATE SEQUENCE contact_ids
100 -- Name: contacts; Type: TABLE; Schema: public; Owner: postgres
103 CREATE TABLE contacts (
104 contact_id integer DEFAULT nextval('contact_ids'::text) PRIMARY KEY,
105 user_id integer NOT NULL
106 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
107 changed timestamp with time zone DEFAULT now() NOT NULL,
108 del smallint DEFAULT 0 NOT NULL,
109 name varchar(128) DEFAULT '' NOT NULL,
110 email text DEFAULT '' NOT NULL,
111 firstname varchar(128) DEFAULT '' NOT NULL,
112 surname varchar(128) DEFAULT '' NOT NULL,
117 CREATE INDEX contacts_user_id_idx ON contacts (user_id, del);
120 -- Sequence "contactgroups_ids"
121 -- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres
124 CREATE SEQUENCE contactgroups_ids
131 -- Table "contactgroups"
132 -- Name: contactgroups; Type: TABLE; Schema: public; Owner: postgres
135 CREATE TABLE contactgroups (
136 contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY,
137 user_id integer NOT NULL
138 REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
139 changed timestamp with time zone DEFAULT now() NOT NULL,
140 del smallint NOT NULL DEFAULT 0,
141 name varchar(128) NOT NULL DEFAULT ''
144 CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del);
147 -- Table "contactgroupmembers"
148 -- Name: contactgroupmembers; Type: TABLE; Schema: public; Owner: postgres
151 CREATE TABLE contactgroupmembers (
152 contactgroup_id integer NOT NULL
153 REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE,
154 contact_id integer NOT NULL
155 REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
156 created timestamp with time zone DEFAULT now() NOT NULL,
157 PRIMARY KEY (contactgroup_id, contact_id)
160 CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_id);
163 -- Sequence "cache_ids"
164 -- Name: cache_ids; Type: SEQUENCE; Schema: public; Owner: postgres
167 CREATE SEQUENCE cache_ids
175 -- Name: cache; Type: TABLE; Schema: public; Owner: postgres
178 CREATE TABLE "cache" (
179 cache_id integer DEFAULT nextval('cache_ids'::text) PRIMARY KEY,
180 user_id integer NOT NULL
181 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
182 cache_key varchar(128) DEFAULT '' NOT NULL,
183 created timestamp with time zone DEFAULT now() NOT NULL,
187 CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key);
188 CREATE INDEX cache_created_idx ON "cache" (created);
191 -- Table "cache_index"
192 -- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres
195 CREATE TABLE cache_index (
196 user_id integer NOT NULL
197 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
198 mailbox varchar(255) NOT NULL,
199 changed timestamp with time zone DEFAULT now() NOT NULL,
200 valid smallint NOT NULL DEFAULT 0,
202 PRIMARY KEY (user_id, mailbox)
205 CREATE INDEX cache_index_changed_idx ON cache_index (changed);
208 -- Table "cache_thread"
209 -- Name: cache_thread; Type: TABLE; Schema: public; Owner: postgres
212 CREATE TABLE cache_thread (
213 user_id integer NOT NULL
214 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
215 mailbox varchar(255) NOT NULL,
216 changed timestamp with time zone DEFAULT now() NOT NULL,
218 PRIMARY KEY (user_id, mailbox)
221 CREATE INDEX cache_thread_changed_idx ON cache_thread (changed);
224 -- Table "cache_messages"
225 -- Name: cache_messages; Type: TABLE; Schema: public; Owner: postgres
228 CREATE TABLE cache_messages (
229 user_id integer NOT NULL
230 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
231 mailbox varchar(255) NOT NULL,
232 uid integer NOT NULL,
233 changed timestamp with time zone DEFAULT now() NOT NULL,
235 flags integer NOT NULL DEFAULT 0,
236 PRIMARY KEY (user_id, mailbox, uid)
239 CREATE INDEX cache_messages_changed_idx ON cache_messages (changed);
242 -- Table "dictionary"
243 -- Name: dictionary; Type: TABLE; Schema: public; Owner: postgres
246 CREATE TABLE dictionary (
247 user_id integer DEFAULT NULL
248 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
249 "language" varchar(5) NOT NULL,
251 CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language")
255 -- Sequence "searches_ids"
256 -- Name: searches_ids; Type: SEQUENCE; Schema: public; Owner: postgres
259 CREATE SEQUENCE search_ids
267 -- Name: searches; Type: TABLE; Schema: public; Owner: postgres
270 CREATE TABLE searches (
271 search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY,
272 user_id integer NOT NULL
273 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
274 "type" smallint DEFAULT 0 NOT NULL,
275 name varchar(128) NOT NULL,
277 CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name)