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 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(40) 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 varchar(255) DEFAULT '' NOT NULL,
111 firstname varchar(128) DEFAULT '' NOT NULL,
112 surname varchar(128) DEFAULT '' NOT NULL,
116 CREATE INDEX contacts_user_id_idx ON contacts (user_id, email);
119 -- Sequence "contactgroups_ids"
120 -- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres
123 CREATE SEQUENCE contactgroups_ids
130 -- Table "contactgroups"
131 -- Name: contactgroups; Type: TABLE; Schema: public; Owner: postgres
134 CREATE TABLE contactgroups (
135 contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY,
136 user_id integer NOT NULL
137 REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
138 changed timestamp with time zone DEFAULT now() NOT NULL,
139 del smallint NOT NULL DEFAULT 0,
140 name varchar(128) NOT NULL DEFAULT ''
143 CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del);
146 -- Table "contactgroupmembers"
147 -- Name: contactgroupmembers; Type: TABLE; Schema: public; Owner: postgres
150 CREATE TABLE contactgroupmembers (
151 contactgroup_id integer NOT NULL
152 REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE,
153 contact_id integer NOT NULL
154 REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
155 created timestamp with time zone DEFAULT now() NOT NULL,
156 PRIMARY KEY (contactgroup_id, contact_id)
160 -- Sequence "cache_ids"
161 -- Name: cache_ids; Type: SEQUENCE; Schema: public; Owner: postgres
164 CREATE SEQUENCE cache_ids
172 -- Name: cache; Type: TABLE; Schema: public; Owner: postgres
175 CREATE TABLE "cache" (
176 cache_id integer DEFAULT nextval('cache_ids'::text) PRIMARY KEY,
177 user_id integer NOT NULL
178 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
179 cache_key varchar(128) DEFAULT '' NOT NULL,
180 created timestamp with time zone DEFAULT now() NOT NULL,
184 CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key);
185 CREATE INDEX cache_created_idx ON "cache" (created);
188 -- Sequence "message_ids"
189 -- Name: message_ids; Type: SEQUENCE; Schema: public; Owner: postgres
192 CREATE SEQUENCE message_ids
200 -- Name: messages; Type: TABLE; Schema: public; Owner: postgres
203 CREATE TABLE messages (
204 message_id integer DEFAULT nextval('message_ids'::text) PRIMARY KEY,
205 user_id integer NOT NULL
206 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
207 del smallint DEFAULT 0 NOT NULL,
208 cache_key varchar(128) DEFAULT '' NOT NULL,
209 created timestamp with time zone DEFAULT now() NOT NULL,
210 idx integer DEFAULT 0 NOT NULL,
211 uid integer DEFAULT 0 NOT NULL,
212 subject varchar(128) DEFAULT '' NOT NULL,
213 "from" varchar(128) DEFAULT '' NOT NULL,
214 "to" varchar(128) DEFAULT '' NOT NULL,
215 cc varchar(128) DEFAULT '' NOT NULL,
216 date timestamp with time zone NOT NULL,
217 size integer DEFAULT 0 NOT NULL,
218 headers text NOT NULL,
220 UNIQUE (user_id, cache_key, uid)
223 CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx);
224 CREATE INDEX messages_created_idx ON messages (created);