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(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,
117 CREATE INDEX contacts_user_id_idx ON contacts (user_id, email);
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 -- Sequence "message_ids"
192 -- Name: message_ids; Type: SEQUENCE; Schema: public; Owner: postgres
195 CREATE SEQUENCE message_ids
203 -- Name: messages; Type: TABLE; Schema: public; Owner: postgres
206 CREATE TABLE messages (
207 message_id integer DEFAULT nextval('message_ids'::text) PRIMARY KEY,
208 user_id integer NOT NULL
209 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
210 del smallint DEFAULT 0 NOT NULL,
211 cache_key varchar(128) DEFAULT '' NOT NULL,
212 created timestamp with time zone DEFAULT now() NOT NULL,
213 idx integer DEFAULT 0 NOT NULL,
214 uid integer DEFAULT 0 NOT NULL,
215 subject varchar(128) DEFAULT '' NOT NULL,
216 "from" varchar(128) DEFAULT '' NOT NULL,
217 "to" varchar(128) DEFAULT '' NOT NULL,
218 cc varchar(128) DEFAULT '' NOT NULL,
219 date timestamp with time zone NOT NULL,
220 size integer DEFAULT 0 NOT NULL,
221 headers text NOT NULL,
223 CONSTRAINT messages_user_id_key UNIQUE (user_id, cache_key, uid)
226 CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx);
227 CREATE INDEX messages_created_idx ON messages (created);