diff options
Diffstat (limited to 'SQL/postgres.initial.sql')
| -rw-r--r-- | SQL/postgres.initial.sql | 82 |
1 files changed, 49 insertions, 33 deletions
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index d6acb5d69..f18cb6ad0 100644 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -1,11 +1,11 @@ -- Roundcube Webmail initial database structure -- --- Sequence "user_ids" --- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- Sequence "users_seq" +-- Name: users_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- -CREATE SEQUENCE user_ids +CREATE SEQUENCE users_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE @@ -17,7 +17,7 @@ CREATE SEQUENCE user_ids -- CREATE TABLE users ( - user_id integer DEFAULT nextval('user_ids'::text) PRIMARY KEY, + user_id integer DEFAULT nextval('users_seq'::text) PRIMARY KEY, username varchar(128) DEFAULT '' NOT NULL, mail_host varchar(128) DEFAULT '' NOT NULL, created timestamp with time zone DEFAULT now() NOT NULL, @@ -45,11 +45,11 @@ CREATE INDEX session_changed_idx ON session (changed); -- --- Sequence "identity_ids" --- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- Sequence "identities_seq" +-- Name: identities_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- -CREATE SEQUENCE identity_ids +CREATE SEQUENCE identities_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE @@ -62,7 +62,7 @@ CREATE SEQUENCE identity_ids -- CREATE TABLE identities ( - identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY, + identity_id integer DEFAULT nextval('identities_seq'::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, @@ -82,11 +82,11 @@ CREATE INDEX identities_email_idx ON identities (email, del); -- --- Sequence "contact_ids" --- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- Sequence "contacts_seq" +-- Name: contacts_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- -CREATE SEQUENCE contact_ids +CREATE SEQUENCE contacts_seq START WITH 1 INCREMENT BY 1 NO MAXVALUE @@ -99,7 +99,7 @@ CREATE SEQUENCE contact_ids -- CREATE TABLE contacts ( - contact_id integer DEFAULT nextval('contact_ids'::text) PRIMARY KEY, + contact_id integer DEFAULT nextval('contacts_seq'::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, @@ -115,11 +115,11 @@ CREATE TABLE contacts ( CREATE INDEX contacts_user_id_idx ON contacts (user_id, del); -- --- Sequence "contactgroups_ids" --- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- Sequence "contactgroups_seq" +-- Name: contactgroups_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- -CREATE SEQUENCE contactgroups_ids +CREATE SEQUENCE contactgroups_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE @@ -131,7 +131,7 @@ CREATE SEQUENCE contactgroups_ids -- CREATE TABLE contactgroups ( - contactgroup_id integer DEFAULT nextval('contactgroups_ids'::text) PRIMARY KEY, + contactgroup_id integer DEFAULT nextval('contactgroups_seq'::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, @@ -164,14 +164,30 @@ CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_ CREATE TABLE "cache" ( user_id integer NOT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + 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, + expires timestamp with time zone DEFAULT 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); +CREATE INDEX cache_expires_idx ON "cache" (expires); + +-- +-- Table "cache_shared" +-- Name: cache_shared; Type: TABLE; Schema: public; Owner: postgres +-- + +CREATE TABLE "cache_shared" ( + cache_key varchar(255) NOT NULL, + created timestamp with time zone DEFAULT now() NOT NULL, + expires timestamp with time zone DEFAULT NULL, + data text NOT NULL +); + +CREATE INDEX cache_shared_cache_key_idx ON "cache_shared" (cache_key); +CREATE INDEX cache_shared_expires_idx ON "cache_shared" (expires); -- -- Table "cache_index" @@ -180,15 +196,15 @@ CREATE INDEX cache_created_idx ON "cache" (created); CREATE TABLE cache_index ( user_id integer NOT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, mailbox varchar(255) NOT NULL, - changed timestamp with time zone DEFAULT now() NOT NULL, + expires timestamp with time zone DEFAULT NULL, valid smallint NOT NULL DEFAULT 0, data text NOT NULL, PRIMARY KEY (user_id, mailbox) ); -CREATE INDEX cache_index_changed_idx ON cache_index (changed); +CREATE INDEX cache_index_expires_idx ON cache_index (expires); -- -- Table "cache_thread" @@ -197,14 +213,14 @@ CREATE INDEX cache_index_changed_idx ON cache_index (changed); CREATE TABLE cache_thread ( user_id integer NOT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, mailbox varchar(255) NOT NULL, - changed timestamp with time zone DEFAULT now() NOT NULL, + expires timestamp with time zone DEFAULT NULL, data text NOT NULL, PRIMARY KEY (user_id, mailbox) ); -CREATE INDEX cache_thread_changed_idx ON cache_thread (changed); +CREATE INDEX cache_thread_expires_idx ON cache_thread (expires); -- -- Table "cache_messages" @@ -213,16 +229,16 @@ CREATE INDEX cache_thread_changed_idx ON cache_thread (changed); CREATE TABLE cache_messages ( user_id integer NOT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, mailbox varchar(255) NOT NULL, uid integer NOT NULL, - changed timestamp with time zone DEFAULT now() NOT NULL, + expires timestamp with time zone DEFAULT NULL, data text NOT NULL, flags integer NOT NULL DEFAULT 0, PRIMARY KEY (user_id, mailbox, uid) ); -CREATE INDEX cache_messages_changed_idx ON cache_messages (changed); +CREATE INDEX cache_messages_expires_idx ON cache_messages (expires); -- -- Table "dictionary" @@ -231,18 +247,18 @@ CREATE INDEX cache_messages_changed_idx ON cache_messages (changed); CREATE TABLE dictionary ( user_id integer DEFAULT NULL - REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, "language" varchar(5) NOT NULL, data text NOT NULL, CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, "language") ); -- --- Sequence "searches_ids" --- Name: searches_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- Sequence "searches_seq" +-- Name: searches_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- -CREATE SEQUENCE search_ids +CREATE SEQUENCE searches_seq INCREMENT BY 1 NO MAXVALUE NO MINVALUE @@ -254,7 +270,7 @@ CREATE SEQUENCE search_ids -- CREATE TABLE searches ( - search_id integer DEFAULT nextval('search_ids'::text) PRIMARY KEY, + search_id integer DEFAULT nextval('searches_seq'::text) PRIMARY KEY, user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, "type" smallint DEFAULT 0 NOT NULL, @@ -274,4 +290,4 @@ CREATE TABLE "system" ( value text ); -INSERT INTO system (name, value) VALUES ('roundcube-version', '2013011700.sql'); +INSERT INTO system (name, value) VALUES ('roundcube-version', '2013061000'); |