From b7e7c8f9501850a38705e0a1f18a8ae6e25f1be1 Mon Sep 17 00:00:00 2001 From: Aleksander Machniak Date: Wed, 9 Jan 2013 15:57:29 +0100 Subject: Added new database upgrade script, converted DDL scripts to new format --- SQL/postgres/2011092800.sql | 64 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 64 insertions(+) create mode 100644 SQL/postgres/2011092800.sql (limited to 'SQL/postgres/2011092800.sql') diff --git a/SQL/postgres/2011092800.sql b/SQL/postgres/2011092800.sql new file mode 100644 index 000000000..fac3cd371 --- /dev/null +++ b/SQL/postgres/2011092800.sql @@ -0,0 +1,64 @@ +-- Updates from version 0.6 + +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") +); + +CREATE SEQUENCE search_ids + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + +CREATE TABLE searches ( + search_id integer DEFAULT nextval('search_ids'::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) +); + +DROP SEQUENCE message_ids; +DROP TABLE messages; + +CREATE TABLE cache_index ( + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + mailbox varchar(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT 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 TABLE cache_thread ( + user_id integer NOT NULL + REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE, + mailbox varchar(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL, + PRIMARY KEY (user_id, mailbox) +); + +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, + mailbox varchar(255) NOT NULL, + uid integer NOT NULL, + changed timestamp with time zone DEFAULT now() NOT 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); -- cgit v1.2.3