| | |
| | | -- 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 |
| | |
| | | -- |
| | | |
| | | 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, |
| | | 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, |
| | | 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, |
| | | failed_login timestamp with time zone DEFAULT NULL, |
| | | failed_login_counter integer DEFAULT NULL, |
| | | "language" varchar(5), |
| | | preferences text DEFAULT ''::text NOT NULL, |
| | | CONSTRAINT users_username_key UNIQUE (username, mail_host) |
| | | ); |
| | | |
| | | CREATE INDEX users_username_id_idx ON users (username); |
| | | CREATE INDEX users_alias_id_idx ON users (alias); |
| | | |
| | | |
| | | -- |
| | | -- Table "session" |
| | | -- Name: session; Type: TABLE; Schema: public; Owner: postgres |
| | | -- |
| | | |
| | | CREATE TABLE "session" ( |
| | | sess_id character varying(40) DEFAULT ''::character varying PRIMARY KEY, |
| | | sess_id varchar(128) DEFAULT '' PRIMARY KEY, |
| | | created timestamp with time zone DEFAULT now() NOT NULL, |
| | | changed timestamp with time zone DEFAULT now() NOT NULL, |
| | | ip character varying(41) NOT NULL, |
| | | ip varchar(41) NOT NULL, |
| | | vars text NOT NULL |
| | | ); |
| | | |
| | | 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 |
| | |
| | | -- |
| | | |
| | | CREATE TABLE identities ( |
| | | identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY, |
| | | user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| | | 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), |
| | | 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, |
| | | 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); |
| | | CREATE INDEX identities_user_id_idx ON identities (user_id, del); |
| | | 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 |
| | |
| | | -- |
| | | |
| | | CREATE TABLE contacts ( |
| | | contact_id integer DEFAULT nextval('contact_ids'::text) PRIMARY KEY, |
| | | user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| | | 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, |
| | | 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, |
| | | vcard text |
| | | del smallint DEFAULT 0 NOT NULL, |
| | | name varchar(128) DEFAULT '' NOT NULL, |
| | | email text DEFAULT '' NOT NULL, |
| | | firstname varchar(128) DEFAULT '' NOT NULL, |
| | | surname varchar(128) DEFAULT '' NOT NULL, |
| | | vcard text, |
| | | words text |
| | | ); |
| | | |
| | | CREATE INDEX contacts_user_id_idx ON contacts (user_id); |
| | | CREATE INDEX contacts_user_id_idx ON contacts (user_id, del); |
| | | |
| | | -- |
| | | -- Sequence "cache_ids" |
| | | -- Name: cache_ids; Type: SEQUENCE; Schema: public; Owner: postgres |
| | | -- Sequence "contactgroups_seq" |
| | | -- Name: contactgroups_seq; Type: SEQUENCE; Schema: public; Owner: postgres |
| | | -- |
| | | |
| | | CREATE SEQUENCE cache_ids |
| | | CREATE SEQUENCE contactgroups_seq |
| | | 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_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, |
| | | 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) |
| | | ); |
| | | |
| | | CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers (contact_id); |
| | | |
| | | -- |
| | | -- Table "cache" |
| | |
| | | -- |
| | | |
| | | CREATE TABLE "cache" ( |
| | | cache_id integer DEFAULT nextval('cache_ids'::text) PRIMARY KEY, |
| | | user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| | | 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, |
| | | 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_expires_idx ON "cache" (expires); |
| | | |
| | | -- |
| | | -- Sequence "message_ids" |
| | | -- Name: message_ids; Type: SEQUENCE; Schema: public; Owner: postgres |
| | | -- Table "cache_shared" |
| | | -- Name: cache_shared; Type: TABLE; Schema: public; Owner: postgres |
| | | -- |
| | | |
| | | CREATE SEQUENCE message_ids |
| | | 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" |
| | | -- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres |
| | | -- |
| | | |
| | | CREATE TABLE cache_index ( |
| | | user_id integer NOT NULL |
| | | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| | | mailbox varchar(255) 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_expires_idx ON cache_index (expires); |
| | | |
| | | -- |
| | | -- Table "cache_thread" |
| | | -- Name: cache_thread; Type: TABLE; Schema: public; Owner: postgres |
| | | -- |
| | | |
| | | CREATE TABLE cache_thread ( |
| | | user_id integer NOT NULL |
| | | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| | | mailbox varchar(255) NOT NULL, |
| | | expires timestamp with time zone DEFAULT NULL, |
| | | data text NOT NULL, |
| | | PRIMARY KEY (user_id, mailbox) |
| | | ); |
| | | |
| | | CREATE INDEX cache_thread_expires_idx ON cache_thread (expires); |
| | | |
| | | -- |
| | | -- Table "cache_messages" |
| | | -- Name: cache_messages; Type: TABLE; Schema: public; Owner: postgres |
| | | -- |
| | | |
| | | CREATE TABLE cache_messages ( |
| | | user_id integer NOT NULL |
| | | REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| | | mailbox varchar(255) NOT NULL, |
| | | uid integer 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_expires_idx ON cache_messages (expires); |
| | | |
| | | -- |
| | | -- Table "dictionary" |
| | | -- Name: dictionary; Type: TABLE; Schema: public; Owner: postgres |
| | | -- |
| | | |
| | | CREATE TABLE dictionary ( |
| | | user_id integer DEFAULT NULL |
| | | 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_seq" |
| | | -- Name: searches_seq; Type: SEQUENCE; Schema: public; Owner: postgres |
| | | -- |
| | | |
| | | CREATE SEQUENCE searches_seq |
| | | INCREMENT BY 1 |
| | | NO MAXVALUE |
| | | NO MINVALUE |
| | | CACHE 1; |
| | | |
| | | -- |
| | | -- Table "messages" |
| | | -- Name: messages; Type: TABLE; Schema: public; Owner: postgres |
| | | -- Table "searches" |
| | | -- Name: searches; Type: TABLE; Schema: public; Owner: postgres |
| | | -- |
| | | |
| | | CREATE TABLE messages ( |
| | | message_id integer DEFAULT nextval('message_ids'::text) PRIMARY KEY, |
| | | user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, |
| | | del integer DEFAULT 0 NOT NULL, |
| | | cache_key character varying(128) DEFAULT ''::character varying 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, |
| | | date timestamp with time zone NOT NULL, |
| | | size integer DEFAULT 0 NOT NULL, |
| | | headers text NOT NULL, |
| | | structure text |
| | | CREATE TABLE searches ( |
| | | 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, |
| | | name varchar(128) NOT NULL, |
| | | data text NOT NULL, |
| | | CONSTRAINT searches_user_id_key UNIQUE (user_id, "type", name) |
| | | ); |
| | | |
| | | ALTER TABLE messages ADD UNIQUE (user_id, cache_key, uid); |
| | | CREATE INDEX messages_created_idx ON messages (created); |
| | | |
| | | -- |
| | | -- Table "system" |
| | | -- Name: system; Type: TABLE; Schema: public; Owner: postgres |
| | | -- |
| | | |
| | | CREATE TABLE "system" ( |
| | | name varchar(64) NOT NULL PRIMARY KEY, |
| | | value text |
| | | ); |
| | | |
| | | INSERT INTO system (name, value) VALUES ('roundcube-version', '2015111100'); |