diff options
Diffstat (limited to 'SQL')
| -rw-r--r-- | SQL/mysql.initial.sql | 38 | ||||
| -rw-r--r-- | SQL/mysql.update.sql | 35 | ||||
| -rwxr-xr-x | SQL/postgres.initial.sql | 286 | ||||
| -rw-r--r-- | SQL/sqlite.initial.sql | 54 |
4 files changed, 245 insertions, 168 deletions
diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index 09d9b8017..21444edda 100644 --- a/SQL/mysql.initial.sql +++ b/SQL/mysql.initial.sql @@ -11,7 +11,7 @@ CREATE TABLE `cache` ( `cache_id` int(10) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned NOT NULL default '0', - `session_id` varchar(32) default NULL, + `session_id` varchar(40) default NULL, `cache_key` varchar(128) NOT NULL default '', `created` datetime NOT NULL default '0000-00-00 00:00:00', `data` longtext NOT NULL, @@ -31,7 +31,7 @@ CREATE TABLE `contacts` ( `contact_id` int(10) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned NOT NULL default '0', `changed` datetime NOT NULL default '0000-00-00 00:00:00', - `del` enum('0','1') NOT NULL default '0', + `del` tinyint(1) NOT NULL default '0', `name` varchar(128) NOT NULL default '', `email` varchar(128) NOT NULL default '', `firstname` varchar(128) NOT NULL default '', @@ -50,8 +50,8 @@ CREATE TABLE `contacts` ( CREATE TABLE `identities` ( `identity_id` int(10) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned NOT NULL default '0', - `del` enum('0','1') NOT NULL default '0', - `default` enum('0','1') NOT NULL default '0', + `del` tinyint(1) NOT NULL default '0', + `standard` tinyint(1) NOT NULL default '0', `name` varchar(128) NOT NULL default '', `organization` varchar(128) NOT NULL default '', `email` varchar(128) NOT NULL default '', @@ -94,3 +94,33 @@ CREATE TABLE `users` ( `preferences` text NOT NULL, PRIMARY KEY (`user_id`) ) TYPE=MyISAM; + +-- -------------------------------------------------------- + +-- +-- Table structure for table `messages` +-- + +CREATE TABLE `messages` ( + `message_id` int(11) unsigned NOT NULL auto_increment, + `user_id` int(11) unsigned NOT NULL default '0', + `del` tinyint(1) NOT NULL default '0', + `cache_key` varchar(128) NOT NULL default '', + `idx` int(11) unsigned NOT NULL default '0', + `uid` int(11) unsigned NOT NULL default '0', + `subject` varchar(255) NOT NULL default '', + `from` varchar(255) NOT NULL default '', + `to` varchar(255) NOT NULL default '', + `cc` varchar(255) NOT NULL default '', + `date` datetime NOT NULL default '0000-00-00 00:00:00', + `size` int(11) unsigned NOT NULL default '0', + `headers` text NOT NULL, + `body` longtext, + PRIMARY KEY (`message_id`), + KEY `user_id` (`user_id`), + KEY `cache_key` (`cache_key`), + KEY `idx` (`idx`), + KEY `uid` (`uid`) +) TYPE=MyISAM; + + diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql index 0119225ef..778919acf 100644 --- a/SQL/mysql.update.sql +++ b/SQL/mysql.update.sql @@ -11,4 +11,39 @@ ALTER TABLE users ADD alias VARCHAR(128) NOT NULL AFTER mail_host; -- Version 0.1-20051021 ALTER TABLE `session` CHANGE `sess_id` `sess_id` VARCHAR(40) NOT NULL; + +ALTER TABLE `contacts` CHANGE `del` `del` TINYINT(1) NOT NULL; ALTER TABLE `contacts` ADD `changed` DATETIME NOT NULL AFTER `user_id`; + +UPDATE `contacts` SET `del`=0 WHERE `del`=1; +UPDATE `contacts` SET `del`=1 WHERE `del`=2; + +ALTER TABLE `identities` CHANGE `default` `standard` TINYINT(1) NOT NULL; +ALTER TABLE `identities` CHANGE `del` `del` TINYINT(1) NOT NULL; + +UPDATE `identities` SET `del`=0 WHERE `del`=1; +UPDATE `identities` SET `del`=1 WHERE `del`=2; +UPDATE `identities` SET `standard`=0 WHERE `standard`=1; +UPDATE `identities` SET `standard`=1 WHERE `standard`=2; + +CREATE TABLE `messages` ( + `message_id` int(11) unsigned NOT NULL auto_increment, + `user_id` int(11) unsigned NOT NULL default '0', + `del` tinyint(1) NOT NULL default '0', + `cache_key` varchar(128) NOT NULL default '', + `idx` int(11) unsigned NOT NULL default '0', + `uid` int(11) unsigned NOT NULL default '0', + `subject` varchar(255) NOT NULL default '', + `from` varchar(255) NOT NULL default '', + `to` varchar(255) NOT NULL default '', + `cc` varchar(255) NOT NULL default '', + `date` datetime NOT NULL default '0000-00-00 00:00:00', + `size` int(11) unsigned NOT NULL default '0', + `headers` text NOT NULL, + `body` longtext, + PRIMARY KEY (`message_id`), + KEY `user_id` (`user_id`), + KEY `cache_key` (`cache_key`), + KEY `idx` (`idx`), + KEY `uid` (`uid`) +) TYPE=MyISAM; diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index 29c134d14..4e74a222f 100755 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -1,255 +1,223 @@ -- --- PostgreSQL database dump --- - -SET client_encoding = 'UNICODE'; -SET check_function_bodies = false; -SET search_path = public, pg_catalog; - -ALTER TABLE ONLY public.identities DROP CONSTRAINT "$1"; -ALTER TABLE ONLY public.contacts DROP CONSTRAINT "$1"; -ALTER TABLE ONLY public."cache" DROP CONSTRAINT "$2"; -ALTER TABLE ONLY public."cache" DROP CONSTRAINT "$1"; -ALTER TABLE ONLY public.users DROP CONSTRAINT users_pkey; -ALTER TABLE ONLY public."session" DROP CONSTRAINT session_pkey; -ALTER TABLE ONLY public.identities DROP CONSTRAINT identities_pkey; -ALTER TABLE ONLY public.contacts DROP CONSTRAINT contacts_pkey; -ALTER TABLE ONLY public."cache" DROP CONSTRAINT cache_pkey; -DROP TABLE public.users; -DROP TABLE public."session"; -DROP TABLE public.identities; -DROP TABLE public.contacts; -DROP TABLE public."cache"; -DROP SEQUENCE public.user_ids; -DROP SEQUENCE public.identity_ids; -DROP SEQUENCE public.contact_ids; -DROP SEQUENCE public.cache_ids; --- --- TOC entry 4 (OID 15282470) --- Name: cache_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- Table "users" +-- Name: users; Type: TABLE; Schema: public; Owner: postgres -- -CREATE SEQUENCE cache_ids - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; - - --- --- TOC entry 5 (OID 15282472) --- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres --- +CREATE TABLE users ( + user_id integer DEFAULT nextval('user_ids'::text) NOT NULL, + 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, + 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 +); -CREATE SEQUENCE contact_ids - START WITH 1 - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; -- --- TOC entry 6 (OID 15282474) --- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- Table "session" +-- Name: session; Type: TABLE; Schema: public; Owner: postgres -- -CREATE SEQUENCE identity_ids - START WITH 1 - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; - - --- --- TOC entry 7 (OID 15282476) --- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres --- +CREATE TABLE "session" ( + sess_id character varying(40) DEFAULT ''::character varying NOT NULL, + created timestamp with time zone DEFAULT now() NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + ip character varying(16) NOT NULL, + vars text NOT NULL +); -CREATE SEQUENCE user_ids - INCREMENT BY 1 - NO MAXVALUE - NO MINVALUE - CACHE 1; -- --- TOC entry 8 (OID 15282478) --- Name: cache; Type: TABLE; Schema: public; Owner: postgres +-- Table "identities" +-- Name: identities; Type: TABLE; Schema: public; Owner: postgres -- -CREATE TABLE "cache" ( - cache_id integer DEFAULT nextval('cache_ids'::text) NOT NULL, +CREATE TABLE identities ( + identity_id integer DEFAULT nextval('identity_ids'::text) NOT NULL, user_id integer DEFAULT 0 NOT NULL, - session_id character varying(32), - cache_key character varying(128) DEFAULT ''::character varying NOT NULL, - created timestamp with time zone DEFAULT now() NOT NULL, - data text NOT NULL + 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), + signature text ); -- --- TOC entry 10 (OID 15282486) +-- Table "contacts" -- Name: contacts; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE contacts ( contact_id integer DEFAULT nextval('contact_ids'::text) NOT NULL, user_id integer DEFAULT 0 NOT NULL, - del boolean DEFAULT false NOT NULL, + 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 NOT NULL + vcard text ); --- --- TOC entry 11 (OID 15282494) --- Name: identities; Type: TABLE; Schema: public; Owner: postgres --- - -CREATE TABLE identities ( - identity_id integer DEFAULT nextval('identity_ids'::text) NOT NULL, - user_id integer DEFAULT 0 NOT NULL, - del boolean DEFAULT false NOT NULL, - "default" boolean DEFAULT false 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), - signature text -); - -- --- TOC entry 12 (OID 15282503) --- Name: session; Type: TABLE; Schema: public; Owner: postgres +-- Table "cache" +-- Name: cache; Type: TABLE; Schema: public; Owner: postgres -- -CREATE TABLE "session" ( - sess_id character varying(32) DEFAULT ''::character varying NOT NULL, +CREATE TABLE "cache" ( + cache_id integer DEFAULT nextval('cache_ids'::text) NOT NULL, + user_id integer DEFAULT 0 NOT NULL, + session_id character varying(40), + cache_key character varying(128) DEFAULT ''::character varying NOT NULL, created timestamp with time zone DEFAULT now() NOT NULL, - changed timestamp with time zone DEFAULT now() NOT NULL, - ip character varying(16) NOT NULL, - vars text NOT NULL + data text NOT NULL ); + -- --- TOC entry 13 (OID 15282510) --- Name: users; Type: TABLE; Schema: public; Owner: postgres +-- Table "messages" +-- Name: messages; Type: TABLE; Schema: public; Owner: postgres -- -CREATE TABLE users ( - user_id integer DEFAULT nextval('user_ids'::text) NOT NULL, - 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, - 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 +CREATE TABLE "messages" ( + message_id integer DEFAULT nextval('message_ids'::text) NOT NULL, + user_id integer DEFAULT 0 NOT NULL, + del integer DEFAULT 0 NOT NULL, + cache_key character varying(128) DEFAULT ''::character varying 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, + body text ); + -- --- TOC entry 14 (OID 15282518) --- Name: cache_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres +-- Add primary keys -- ALTER TABLE ONLY "cache" ADD CONSTRAINT cache_pkey PRIMARY KEY (cache_id); --- --- TOC entry 15 (OID 15282520) --- Name: contacts_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres --- - -ALTER TABLE ONLY contacts +ALTER TABLE ONLY "contacts" ADD CONSTRAINT contacts_pkey PRIMARY KEY (contact_id); --- --- TOC entry 16 (OID 15282522) --- Name: identities_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres --- - ALTER TABLE ONLY identities ADD CONSTRAINT identities_pkey PRIMARY KEY (identity_id); --- --- TOC entry 17 (OID 15282524) --- Name: session_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres --- - ALTER TABLE ONLY "session" ADD CONSTRAINT session_pkey PRIMARY KEY (sess_id); --- --- TOC entry 18 (OID 15282526) --- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres --- - -ALTER TABLE ONLY users +ALTER TABLE ONLY "users" ADD CONSTRAINT users_pkey PRIMARY KEY (user_id); +ALTER TABLE ONLY "messages" + ADD CONSTRAINT messages_pkey PRIMARY KEY (message_id); + + -- --- TOC entry 19 (OID 15282528) --- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- Reference keys -- ALTER TABLE ONLY "cache" ADD CONSTRAINT "$1" FOREIGN KEY (user_id) REFERENCES users(user_id); +ALTER TABLE ONLY "cache" + ADD CONSTRAINT "$2" FOREIGN KEY (session_id) REFERENCES "session"(sess_id); + + +ALTER TABLE ONLY "contacts" + ADD CONSTRAINT "$1" FOREIGN KEY (user_id) REFERENCES users(user_id); + + +ALTER TABLE ONLY "identities" + ADD CONSTRAINT "$1" FOREIGN KEY (user_id) REFERENCES users(user_id); + + +ALTER TABLE ONLY "messages" + ADD CONSTRAINT "$1" FOREIGN KEY (user_id) REFERENCES users(user_id); + -- --- TOC entry 20 (OID 15282532) --- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- Sequence "cache_ids" +-- Name: cache_ids; Type: SEQUENCE; Schema: public; Owner: postgres -- -ALTER TABLE ONLY "cache" - ADD CONSTRAINT "$2" FOREIGN KEY (session_id) REFERENCES "session"(sess_id); +CREATE SEQUENCE cache_ids + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; -- --- TOC entry 21 (OID 15282536) --- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- Sequence "contact_ids" +-- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres -- -ALTER TABLE ONLY contacts - ADD CONSTRAINT "$1" FOREIGN KEY (user_id) REFERENCES users(user_id); +CREATE SEQUENCE contact_ids + START WITH 1 + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; -- --- TOC entry 22 (OID 15282540) --- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- Sequence "identity_ids" +-- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres -- -ALTER TABLE ONLY identities - ADD CONSTRAINT "$1" FOREIGN KEY (user_id) REFERENCES users(user_id); - +CREATE SEQUENCE identity_ids + START WITH 1 + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; -SET SESSION AUTHORIZATION 'postgres'; -- --- TOC entry 3 (OID 15282469) --- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres +-- Sequence "user_ids" +-- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres -- -COMMENT ON SCHEMA public IS 'Standard public schema'; - +CREATE SEQUENCE user_ids + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; -SET SESSION AUTHORIZATION 'postgres'; -- --- TOC entry 9 (OID 15282478) --- Name: TABLE "cache"; Type: COMMENT; Schema: public; Owner: postgres ---
\ No newline at end of file +-- Sequence "message_ids" +-- Name: message_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE message_ids + INCREMENT BY 1 + NO MAXVALUE + NO MINVALUE + CACHE 1; + diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql index 01f51d6a8..19ca6a578 100644 --- a/SQL/sqlite.initial.sql +++ b/SQL/sqlite.initial.sql @@ -11,7 +11,7 @@ CREATE TABLE cache ( cache_id integer NOT NULL PRIMARY KEY, user_id integer NOT NULL default 0, - session_id varchar(32) default NULL, + session_id varchar(40) default NULL, cache_key varchar(128) NOT NULL default '', created datetime NOT NULL default '0000-00-00 00:00:00', data longtext NOT NULL @@ -21,6 +21,7 @@ CREATE INDEX ix_cache_user_id ON cache(user_id); CREATE INDEX ix_cache_cache_key ON cache(cache_key); CREATE INDEX ix_cache_session_id ON cache(session_id); + -- -------------------------------------------------------- -- @@ -30,7 +31,8 @@ CREATE INDEX ix_cache_session_id ON cache(session_id); CREATE TABLE contacts ( contact_id integer NOT NULL PRIMARY KEY, user_id integer NOT NULL default '0', - del integer NOT NULL default '0', + created datetime NOT NULL default '0000-00-00 00:00:00', + del tinyint NOT NULL default '0', name varchar(128) NOT NULL default '', email varchar(128) NOT NULL default '', firstname varchar(128) NOT NULL default '', @@ -49,10 +51,10 @@ CREATE INDEX ix_contacts_user_id ON contacts(user_id); CREATE TABLE identities ( identity_id integer NOT NULL PRIMARY KEY, user_id integer NOT NULL default '0', - del integer NOT NULL default '0', - "default" integer NOT NULL default '0', + del tinyint NOT NULL default '0', + standard tinyint NOT NULL default '0', name varchar(128) NOT NULL default '', - organization varchar(128) NOT NULL default '', + organization varchar(128) default '', email varchar(128) NOT NULL default '', "reply-to" varchar(128) NOT NULL default '', bcc varchar(128) NOT NULL default '', @@ -78,3 +80,45 @@ CREATE TABLE users ( language varchar(5) NOT NULL default 'en', preferences text NOT NULL default '' ); + + +-- -------------------------------------------------------- + +-- +-- Table structure for table session +-- + +CREATE TABLE session ( + sess_id varchar(40) NOT NULL PRIMARY KEY, + created datetime NOT NULL default '0000-00-00 00:00:00', + changed datetime NOT NULL default '0000-00-00 00:00:00', + ip varchar(15) NOT NULL default '', + vars text NOT NULL +); + + +-- -------------------------------------------------------- + +-- +-- Table structure for table messages +-- + +CREATE TABLE messages ( + message_id integer NOT NULL PRIMARY KEY, + user_id integer NOT NULL default '0', + del tinyint NOT NULL default '0', + cache_key varchar(128) NOT NULL default '', + idx integer NOT NULL default '0', + uid integer NOT NULL default '0', + subject varchar(255) NOT NULL default '', + "from" varchar(255) NOT NULL default '', + "to" varchar(255) NOT NULL default '', + cc varchar(255) NOT NULL default '', + date datetime NOT NULL default '0000-00-00 00:00:00', + size integer NOT NULL default '0', + headers text NOT NULL, + body text +); + +CREATE INDEX ix_messages_user_id ON messages(user_id); +CREATE INDEX ix_messages_cache_key ON messages(cache_key); |