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 character varying(128) DEFAULT ''::character varying NOT NULL,
22 mail_host character varying(128) DEFAULT ''::character varying NOT NULL,
23 alias character varying(128) DEFAULT ''::character varying NOT NULL,
24 created timestamp with time zone DEFAULT now() NOT NULL,
25 last_login timestamp with time zone DEFAULT now() NOT NULL,
26 "language" character varying(5),
27 preferences text DEFAULT ''::text NOT NULL
30 CREATE INDEX users_username_id_idx ON users (username);
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 character varying(40) DEFAULT ''::character varying PRIMARY KEY,
41 created timestamp with time zone DEFAULT now() NOT NULL,
42 changed timestamp with time zone DEFAULT now() NOT NULL,
43 ip character varying(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 REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
70 del smallint DEFAULT 0 NOT NULL,
71 standard smallint DEFAULT 0 NOT NULL,
72 name character varying(128) NOT NULL,
73 organization character varying(128),
74 email character varying(128) NOT NULL,
75 "reply-to" character varying(128),
76 bcc character varying(128),
78 html_signature integer DEFAULT 0 NOT NULL
81 CREATE INDEX identities_user_id_idx ON identities (user_id);
85 -- Sequence "contact_ids"
86 -- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres
89 CREATE SEQUENCE contact_ids
98 -- Name: contacts; Type: TABLE; Schema: public; Owner: postgres
101 CREATE TABLE contacts (
102 contact_id integer DEFAULT nextval('contact_ids'::text) PRIMARY KEY,
103 user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
104 changed timestamp with time zone DEFAULT now() NOT NULL,
105 del smallint DEFAULT 0 NOT NULL,
106 name character varying(128) DEFAULT ''::character varying NOT NULL,
107 email character varying(128) DEFAULT ''::character varying NOT NULL,
108 firstname character varying(128) DEFAULT ''::character varying NOT NULL,
109 surname character varying(128) DEFAULT ''::character varying NOT NULL,
113 CREATE INDEX contacts_user_id_idx ON contacts (user_id, email);
116 -- Sequence "cache_ids"
117 -- Name: cache_ids; Type: SEQUENCE; Schema: public; Owner: postgres
120 CREATE SEQUENCE cache_ids
128 -- Name: cache; Type: TABLE; Schema: public; Owner: postgres
131 CREATE TABLE "cache" (
132 cache_id integer DEFAULT nextval('cache_ids'::text) PRIMARY KEY,
133 user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
134 cache_key character varying(128) DEFAULT ''::character varying NOT NULL,
135 created timestamp with time zone DEFAULT now() NOT NULL,
139 CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key);
140 CREATE INDEX cache_created_idx ON "cache" (created);
143 -- Sequence "message_ids"
144 -- Name: message_ids; Type: SEQUENCE; Schema: public; Owner: postgres
147 CREATE SEQUENCE message_ids
155 -- Name: messages; Type: TABLE; Schema: public; Owner: postgres
158 CREATE TABLE messages (
159 message_id integer DEFAULT nextval('message_ids'::text) PRIMARY KEY,
160 user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE,
161 del smallint DEFAULT 0 NOT NULL,
162 cache_key character varying(128) DEFAULT ''::character varying NOT NULL,
163 created timestamp with time zone DEFAULT now() NOT NULL,
164 idx integer DEFAULT 0 NOT NULL,
165 uid integer DEFAULT 0 NOT NULL,
166 subject character varying(128) DEFAULT ''::character varying NOT NULL,
167 "from" character varying(128) DEFAULT ''::character varying NOT NULL,
168 "to" character varying(128) DEFAULT ''::character varying NOT NULL,
169 cc character varying(128) DEFAULT ''::character varying NOT NULL,
170 date timestamp with time zone NOT NULL,
171 size integer DEFAULT 0 NOT NULL,
172 headers text NOT NULL,
176 ALTER TABLE messages ADD UNIQUE (user_id, cache_key, uid);
177 CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx);
178 CREATE INDEX messages_created_idx ON messages (created);