From 15a9d1ce671fcbc44ea3e4858d7aa6f5b22300c9 Mon Sep 17 00:00:00 2001 From: thomascube Date: Thu, 5 Jan 2006 00:37:10 +0000 Subject: Optimized loading time; added periodic mail check; added EXPUNGE command --- SQL/mysql.initial.sql | 2 +- SQL/mysql.update.sql | 4 +-- SQL/postgres.initial.sql | 74 +++++++----------------------------------------- 3 files changed, 14 insertions(+), 66 deletions(-) (limited to 'SQL') diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index 21444edda..eabc75e1f 100644 --- a/SQL/mysql.initial.sql +++ b/SQL/mysql.initial.sql @@ -91,7 +91,7 @@ CREATE TABLE `users` ( `created` datetime NOT NULL default '0000-00-00 00:00:00', `last_login` datetime NOT NULL default '0000-00-00 00:00:00', `language` varchar(5) NOT NULL default 'en', - `preferences` text NOT NULL, + `preferences` text NOT NULL default '', PRIMARY KEY (`user_id`) ) TYPE=MyISAM; diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql index 778919acf..e93fc980f 100644 --- a/SQL/mysql.update.sql +++ b/SQL/mysql.update.sql @@ -2,8 +2,8 @@ -- Version 0.1-20051007 -ALTER TABLE session ADD ip VARCHAR(15) NOT NULL AFTER changed; -ALTER TABLE users ADD alias VARCHAR(128) NOT NULL AFTER mail_host; +ALTER TABLE `session` ADD `ip` VARCHAR(15) NOT NULL AFTER changed; +ALTER TABLE `users` ADD `alias` VARCHAR(128) NOT NULL AFTER mail_host; diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index 554614ed2..b251755ad 100755 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -68,7 +68,7 @@ CREATE SEQUENCE message_ids -- CREATE TABLE users ( - user_id integer DEFAULT nextval('user_ids'::text) NOT NULL, + 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, @@ -86,7 +86,7 @@ CREATE TABLE users ( -- CREATE TABLE "session" ( - sess_id character varying(40) DEFAULT ''::character varying NOT NULL, + sess_id character varying(40) DEFAULT ''::character varying PRIMARY KEY, created timestamp with time zone DEFAULT now() NOT NULL, changed timestamp with time zone DEFAULT now() NOT NULL, ip character varying(16) NOT NULL, @@ -101,8 +101,8 @@ CREATE TABLE "session" ( -- CREATE TABLE identities ( - identity_id integer DEFAULT nextval('identity_ids'::text) NOT NULL, - user_id integer DEFAULT 0 NOT NULL, + identity_id integer DEFAULT nextval('identity_ids'::text) PRIMARY KEY, + user_id integer NOT NULL REFERENCES users (user_id), del integer DEFAULT 0 NOT NULL, standard integer DEFAULT 0 NOT NULL, name character varying(128) NOT NULL, @@ -120,8 +120,8 @@ CREATE TABLE identities ( -- CREATE TABLE contacts ( - contact_id integer DEFAULT nextval('contact_ids'::text) NOT NULL, - user_id integer DEFAULT 0 NOT NULL, + contact_id integer DEFAULT nextval('contact_ids'::text) PRIMARY KEY, + user_id integer NOT NULL REFERENCES users (user_id), changed timestamp with time zone DEFAULT now() NOT NULL, del integer DEFAULT 0 NOT NULL, name character varying(128) DEFAULT ''::character varying NOT NULL, @@ -139,9 +139,9 @@ CREATE TABLE contacts ( -- 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_id integer DEFAULT nextval('cache_ids'::text) PRIMARY KEY, + user_id integer NOT NULL REFERENCES users (user_id), + session_id character varying(40) REFERENCES "session" (session_id), cache_key character varying(128) DEFAULT ''::character varying NOT NULL, created timestamp with time zone DEFAULT now() NOT NULL, data text NOT NULL @@ -155,8 +155,8 @@ CREATE TABLE "cache" ( -- CREATE TABLE "messages" ( - message_id integer DEFAULT nextval('message_ids'::text) NOT NULL, - user_id integer DEFAULT 0 NOT NULL, + message_id integer DEFAULT nextval('message_ids'::text) PRIMARY KEY, + user_id integer NOT NULL REFERENCES users (user_id), del integer DEFAULT 0 NOT NULL, cache_key character varying(128) DEFAULT ''::character varying NOT NULL, idx integer DEFAULT 0 NOT NULL, @@ -171,55 +171,3 @@ CREATE TABLE "messages" ( body text ); - - --- --- Add primary keys --- - -ALTER TABLE ONLY "cache" - ADD CONSTRAINT cache_pkey PRIMARY KEY (cache_id); - - -ALTER TABLE ONLY "contacts" - ADD CONSTRAINT contacts_pkey PRIMARY KEY (contact_id); - - -ALTER TABLE ONLY identities - ADD CONSTRAINT identities_pkey PRIMARY KEY (identity_id); - - -ALTER TABLE ONLY "session" - ADD CONSTRAINT session_pkey PRIMARY KEY (sess_id); - - -ALTER TABLE ONLY "users" - ADD CONSTRAINT users_pkey PRIMARY KEY (user_id); - - -ALTER TABLE ONLY "messages" - ADD CONSTRAINT messages_pkey PRIMARY KEY (message_id); - - --- --- 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); - -- cgit v1.2.3