X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=SQL%2Fpostgres.initial.sql;h=089cae036c584eee08caaaab8e21144e5838800b;hb=d832c6b37ce0cba5a2bc015b1c806ba6990c4253;hp=bf82b9b5df1d1ec6b72ca30a57f779b4f7e142f3;hpb=8bb10dbda276f4c5f1050a05786ba5a5a6cc787d;p=roundcube.git diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql old mode 100755 new mode 100644 index bf82b9b..089cae0 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -1,3 +1,5 @@ +-- Roundcube Webmail initial database structure + -- -- Sequence "user_ids" -- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres @@ -16,15 +18,17 @@ CREATE SEQUENCE user_ids CREATE TABLE users ( user_id integer DEFAULT nextval('user_ids'::text) PRIMARY KEY, - username character varying(128) DEFAULT ''::character varying NOT NULL, - mail_host character varying(128) DEFAULT ''::character varying NOT NULL, - alias character varying(128) DEFAULT ''::character varying NOT NULL, + username varchar(128) DEFAULT '' NOT NULL, + mail_host varchar(128) DEFAULT '' NOT NULL, + alias varchar(128) DEFAULT '' NOT NULL, created timestamp with time zone DEFAULT now() NOT NULL, - last_login timestamp with time zone DEFAULT now() NOT NULL, - "language" character varying(5) DEFAULT 'en'::character varying NOT NULL, - preferences text DEFAULT ''::text NOT NULL + last_login timestamp with time zone DEFAULT NULL, + "language" varchar(5), + preferences text DEFAULT ''::text NOT NULL, + UNIQUE (username, mail_host) ); +CREATE INDEX users_alias_id_idx ON users (alias); -- @@ -33,13 +37,14 @@ CREATE TABLE users ( -- CREATE TABLE "session" ( - sess_id character varying(40) DEFAULT ''::character varying PRIMARY KEY, + sess_id varchar(40) DEFAULT '' PRIMARY KEY, created timestamp with time zone DEFAULT now() NOT NULL, changed timestamp with time zone DEFAULT now() NOT NULL, - ip character varying(16) NOT NULL, + ip varchar(41) NOT NULL, vars text NOT NULL ); +CREATE INDEX session_changed_idx ON session (changed); -- @@ -61,18 +66,21 @@ CREATE SEQUENCE identity_ids CREATE TABLE identities ( identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY, - user_id integer NOT NULL REFERENCES users (user_id), - del integer DEFAULT 0 NOT NULL, - standard integer DEFAULT 0 NOT NULL, - name character varying(128) NOT NULL, - organization character varying(128), - email character varying(128) NOT NULL, - "reply-to" character varying(128), - bcc character varying(128), + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + changed timestamp with time zone DEFAULT now() NOT NULL, + del smallint DEFAULT 0 NOT NULL, + standard smallint DEFAULT 0 NOT NULL, + name varchar(128) NOT NULL, + organization varchar(128), + email varchar(128) NOT NULL, + "reply-to" varchar(128), + bcc varchar(128), signature text, html_signature integer DEFAULT 0 NOT NULL ); +CREATE INDEX identities_user_id_idx ON identities (user_id, del); -- @@ -94,17 +102,59 @@ CREATE SEQUENCE contact_ids CREATE TABLE contacts ( contact_id integer DEFAULT nextval('contact_ids'::text) PRIMARY KEY, - user_id integer NOT NULL REFERENCES users (user_id), + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, changed timestamp with time zone DEFAULT now() NOT NULL, - del integer DEFAULT 0 NOT NULL, - name character varying(128) DEFAULT ''::character varying NOT NULL, - email character varying(128) DEFAULT ''::character varying NOT NULL, - firstname character varying(128) DEFAULT ''::character varying NOT NULL, - surname character varying(128) DEFAULT ''::character varying NOT NULL, + del smallint DEFAULT 0 NOT NULL, + name varchar(128) DEFAULT '' NOT NULL, + email varchar(255) DEFAULT '' NOT NULL, + firstname varchar(128) DEFAULT '' NOT NULL, + surname varchar(128) DEFAULT '' NOT NULL, vcard text ); +CREATE INDEX contacts_user_id_idx ON contacts (user_id, email); + +-- +-- Sequence "contactgroups_ids" +-- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- +CREATE SEQUENCE contactgroups_ids + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + +-- +-- Table "contactgroups" +-- Name: contactgroups; Type: TABLE; Schema: public; Owner: postgres +-- + +CREATE TABLE contactgroups ( + contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY, + user_id integer NOT NULL + REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE, + changed timestamp with time zone DEFAULT now() NOT NULL, + del smallint NOT NULL DEFAULT 0, + name varchar(128) NOT NULL DEFAULT '' +); + +CREATE INDEX contactgroups_user_id_idx ON contactgroups (user_id, del); + +-- +-- Table "contactgroupmembers" +-- Name: contactgroupmembers; Type: TABLE; Schema: public; Owner: postgres +-- + +CREATE TABLE contactgroupmembers ( + contactgroup_id integer NOT NULL + REFERENCES contactgroups(contactgroup_id) ON DELETE CASCADE ON UPDATE CASCADE, + contact_id integer NOT NULL + REFERENCES contacts(contact_id) ON DELETE CASCADE ON UPDATE CASCADE, + created timestamp with time zone DEFAULT now() NOT NULL, + PRIMARY KEY (contactgroup_id, contact_id) +); -- -- Sequence "cache_ids" @@ -124,14 +174,15 @@ CREATE SEQUENCE cache_ids CREATE TABLE "cache" ( cache_id integer DEFAULT nextval('cache_ids'::text) PRIMARY KEY, - user_id integer NOT NULL REFERENCES users (user_id), - session_id character varying(40) REFERENCES "session" (sess_id), - cache_key character varying(128) DEFAULT ''::character varying NOT NULL, + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + cache_key varchar(128) DEFAULT '' NOT NULL, created timestamp with time zone DEFAULT now() NOT NULL, data text NOT NULL ); - +CREATE INDEX cache_user_id_idx ON "cache" (user_id, cache_key); +CREATE INDEX cache_created_idx ON "cache" (created); -- -- Sequence "message_ids" @@ -149,22 +200,25 @@ CREATE SEQUENCE message_ids -- Name: messages; Type: TABLE; Schema: public; Owner: postgres -- -CREATE TABLE "messages" ( +CREATE TABLE messages ( message_id integer DEFAULT nextval('message_ids'::text) PRIMARY KEY, - user_id integer NOT NULL REFERENCES users (user_id), - del integer DEFAULT 0 NOT NULL, - cache_key character varying(128) DEFAULT ''::character varying NOT NULL, + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + del smallint DEFAULT 0 NOT NULL, + cache_key varchar(128) DEFAULT '' NOT NULL, created timestamp with time zone DEFAULT now() NOT NULL, idx integer DEFAULT 0 NOT NULL, uid integer DEFAULT 0 NOT NULL, - subject character varying(128) DEFAULT ''::character varying NOT NULL, - "from" character varying(128) DEFAULT ''::character varying NOT NULL, - "to" character varying(128) DEFAULT ''::character varying NOT NULL, - cc character varying(128) DEFAULT ''::character varying NOT NULL, + subject varchar(128) DEFAULT '' NOT NULL, + "from" varchar(128) DEFAULT '' NOT NULL, + "to" varchar(128) DEFAULT '' NOT NULL, + cc varchar(128) DEFAULT '' NOT NULL, date timestamp with time zone NOT NULL, size integer DEFAULT 0 NOT NULL, headers text NOT NULL, - structure text + structure text, + UNIQUE (user_id, cache_key, uid) ); -ALTER TABLE "messages" ADD UNIQUE (user_id, cache_key, uid); +CREATE INDEX messages_index_idx ON messages (user_id, cache_key, idx); +CREATE INDEX messages_created_idx ON messages (created);