diff options
| -rw-r--r-- | SQL/postgres.initial.sql | 95 | ||||
| -rw-r--r-- | SQL/postgres.update.sql | 26 | ||||
| -rw-r--r-- | config/db.inc.php.dist | 2 |
3 files changed, 98 insertions, 25 deletions
diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index c634b4b3b..eeeca32ce 100644 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -18,12 +18,12 @@ 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), + "language" varchar(5), preferences text DEFAULT ''::text NOT NULL ); @@ -37,10 +37,10 @@ CREATE INDEX users_alias_id_idx ON users (alias); -- 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(41) NOT NULL, + ip varchar(41) NOT NULL, vars text NOT NULL ); @@ -66,14 +66,15 @@ 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) ON DELETE CASCADE ON UPDATE CASCADE, + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, del smallint DEFAULT 0 NOT NULL, standard smallint 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), + 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 ); @@ -100,19 +101,61 @@ 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) ON DELETE CASCADE ON UPDATE CASCADE, + 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, - 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, + name varchar(128) DEFAULT '' NOT NULL, + email varchar(128) 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" -- Name: cache_ids; Type: SEQUENCE; Schema: public; Owner: postgres -- @@ -130,8 +173,9 @@ 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) ON DELETE CASCADE ON UPDATE CASCADE, - 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 ); @@ -157,16 +201,17 @@ CREATE SEQUENCE message_ids 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, + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, del smallint DEFAULT 0 NOT NULL, - cache_key character varying(128) DEFAULT ''::character varying 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, diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql index 0b926d5e1..ec7ce9b62 100644 --- a/SQL/postgres.update.sql +++ b/SQL/postgres.update.sql @@ -48,3 +48,29 @@ CREATE INDEX contacts_user_id_idx ON contacts (user_id, email); DROP INDEX identities_user_id_idx; CREATE INDEX identities_user_id_idx ON identities (user_id, del); + +CREATE SEQUENCE contactgroups_ids + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + +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); + +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) +); diff --git a/config/db.inc.php.dist b/config/db.inc.php.dist index a7cfe2120..30502cc2e 100644 --- a/config/db.inc.php.dist +++ b/config/db.inc.php.dist @@ -62,6 +62,8 @@ $rcmail_config['db_sequence_identities'] = 'identity_ids'; $rcmail_config['db_sequence_contacts'] = 'contact_ids'; +$rcmail_config['db_sequence_contactgroups'] = 'contactgroups_ids'; + $rcmail_config['db_sequence_cache'] = 'cache_ids'; $rcmail_config['db_sequence_messages'] = 'message_ids'; |