From d81f562b712f2387fa02290bf2ca86392ab356f2 Mon Sep 17 00:00:00 2001 From: Pierre Schmitz Date: Wed, 11 Oct 2006 20:21:25 +0000 Subject: Aktualisierung auf Version 1.8.1 --- maintenance/postgres/tables.sql | 156 +++++++++++++++++++++++++++------------- 1 file changed, 106 insertions(+), 50 deletions(-) (limited to 'maintenance/postgres/tables.sql') diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 5481a394..9ac329d8 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -11,7 +11,7 @@ BEGIN; SET client_min_messages = 'ERROR'; CREATE SEQUENCE user_user_id_seq MINVALUE 0 START WITH 0; -CREATE TABLE "user" ( +CREATE TABLE mwuser ( -- replace reserved word 'user' user_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('user_user_id_seq'), user_name TEXT NOT NULL UNIQUE, user_real_name TEXT, @@ -26,20 +26,20 @@ CREATE TABLE "user" ( user_touched TIMESTAMPTZ, user_registration TIMESTAMPTZ ); -CREATE INDEX user_email_token_idx ON "user" (user_email_token); +CREATE INDEX user_email_token_idx ON mwuser (user_email_token); -- Create a dummy user to satisfy fk contraints especially with revisions -INSERT INTO "user" VALUES - (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now()); +INSERT INTO mwuser + VALUES (DEFAULT,'Anonymous','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,now(),now()); CREATE TABLE user_groups ( - ug_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE CASCADE, + ug_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, ug_group TEXT NOT NULL ); CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group); CREATE TABLE user_newtalk ( - user_id INTEGER NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE, + user_id INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, user_ip CIDR NULL ); CREATE INDEX user_newtalk_id_idx ON user_newtalk (user_id); @@ -69,18 +69,24 @@ CREATE INDEX page_project_title ON page (page_title) WHERE page_namespace = CREATE INDEX page_random_idx ON page (page_random); CREATE INDEX page_len_idx ON page (page_len); --- Create a dummy page to satisfy fk contraints where a page_id of "0" is added -INSERT INTO page (page_id,page_namespace,page_title,page_random,page_latest,page_len) -VALUES (0,0,'',0.0,0,0); +CREATE FUNCTION page_deleted() RETURNS TRIGGER LANGUAGE plpgsql AS +$mw$ +BEGIN +DELETE FROM recentchanges WHERE rc_namespace = OLD.page_namespace AND rc_title = OLD.page_title; +RETURN NULL; +END; +$mw$; +CREATE TRIGGER page_deleted AFTER DELETE ON page + FOR EACH ROW EXECUTE PROCEDURE page_deleted(); CREATE SEQUENCE rev_rev_id_val; CREATE TABLE revision ( rev_id INTEGER NOT NULL UNIQUE DEFAULT nextval('rev_rev_id_val'), - rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE SET NULL, + rev_page INTEGER NULL REFERENCES page (page_id) ON DELETE CASCADE, rev_text_id INTEGER NULL, -- FK rev_comment TEXT, - rev_user INTEGER NOT NULL REFERENCES "user"(user_id), + rev_user INTEGER NOT NULL REFERENCES mwuser(user_id), rev_user_text TEXT NOT NULL, rev_timestamp TIMESTAMPTZ NOT NULL, rev_minor_edit CHAR NOT NULL DEFAULT '0', @@ -93,19 +99,19 @@ CREATE INDEX rev_user_text_idx ON revision (rev_user_text); CREATE SEQUENCE text_old_id_val; -CREATE TABLE "text" ( +CREATE TABLE pagecontent ( -- replaces reserved word 'text' old_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('text_old_id_val'), old_text TEXT, old_flags TEXT ); -CREATE TABLE archive ( +CREATE TABLE archive2 ( ar_namespace SMALLINT NOT NULL, ar_title TEXT NOT NULL, ar_text TEXT, ar_comment TEXT, - ar_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, + ar_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, ar_user_text TEXT NOT NULL, ar_timestamp TIMESTAMPTZ NOT NULL, ar_minor_edit CHAR NOT NULL DEFAULT '0', @@ -113,7 +119,22 @@ CREATE TABLE archive ( ar_rev_id INTEGER, ar_text_id INTEGER ); -CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp); +CREATE INDEX archive_name_title_timestamp ON archive2 (ar_namespace,ar_title,ar_timestamp); + +-- This is the easiest way to work around the char(15) timestamp hack without modifying PHP code +CREATE VIEW archive AS +SELECT + ar_namespace, ar_title, ar_text, ar_comment, ar_user, ar_user_text, + ar_minor_edit, ar_flags, ar_rev_id, ar_text_id, + TO_CHAR(ar_timestamp, 'YYYYMMDDHH24MISS') AS ar_timestamp +FROM archive2; + +CREATE RULE archive_insert AS ON INSERT TO archive +DO INSTEAD INSERT INTO archive2 VALUES ( + NEW.ar_namespace, NEW.ar_title, NEW.ar_text, NEW.ar_comment, NEW.ar_user, NEW.ar_user_text, + TO_DATE(NEW.ar_timestamp, 'YYYYMMDDHH24MISS'), + NEW.ar_minor_edit, NEW.ar_flags, NEW.ar_rev_id, NEW.ar_text_id +); CREATE TABLE pagelinks ( @@ -121,7 +142,7 @@ CREATE TABLE pagelinks ( pl_namespace SMALLINT NOT NULL, pl_title TEXT NOT NULL ); -CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_namespace,pl_title,pl_from); +CREATE UNIQUE INDEX pagelink_unique ON pagelinks (pl_from,pl_namespace,pl_title); CREATE TABLE templatelinks ( tl_from INTEGER NOT NULL REFERENCES page(page_id) ON DELETE CASCADE, @@ -180,16 +201,18 @@ CREATE TABLE hitcounter ( CREATE SEQUENCE ipblocks_ipb_id_val; CREATE TABLE ipblocks ( - ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_val'), - ipb_address CIDR NULL, - ipb_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, - ipb_by INTEGER NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE, - ipb_reason TEXT NOT NULL, - ipb_timestamp TIMESTAMPTZ NOT NULL, - ipb_auto CHAR NOT NULL DEFAULT '0', - ipb_expiry TIMESTAMPTZ NOT NULL, - ipb_range_start TEXT, - ipb_range_end TEXT + ipb_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('ipblocks_ipb_id_val'), + ipb_address CIDR NULL, + ipb_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + ipb_by INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, + ipb_reason TEXT NOT NULL, + ipb_timestamp TIMESTAMPTZ NOT NULL, + ipb_auto CHAR NOT NULL DEFAULT '0', + ipb_anon_only CHAR NOT NULL DEFAULT '0', + ipb_create_account CHAR NOT NULL DEFAULT '1', + ipb_expiry TIMESTAMPTZ NOT NULL, + ipb_range_start TEXT, + ipb_range_end TEXT ); CREATE INDEX ipb_address ON ipblocks (ipb_address); CREATE INDEX ipb_user ON ipblocks (ipb_user); @@ -198,16 +221,16 @@ CREATE INDEX ipb_range ON ipblocks (ipb_range_start,ipb_range_end); CREATE TABLE image ( img_name TEXT NOT NULL PRIMARY KEY, - img_size SMALLINT NOT NULL, - img_width SMALLINT NOT NULL, - img_height SMALLINT NOT NULL, + img_size INTEGER NOT NULL, + img_width INTEGER NOT NULL, + img_height INTEGER NOT NULL, img_metadata TEXT, img_bits SMALLINT, img_media_type TEXT, img_major_mime TEXT DEFAULT 'unknown', img_minor_mime TEXT DEFAULT 'unknown', img_description TEXT NOT NULL, - img_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, + img_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, img_user_text TEXT NOT NULL, img_timestamp TIMESTAMPTZ ); @@ -217,12 +240,12 @@ CREATE INDEX img_timestamp_idx ON image (img_timestamp); CREATE TABLE oldimage ( oi_name TEXT NOT NULL REFERENCES image(img_name), oi_archive_name TEXT NOT NULL, - oi_size SMALLINT NOT NULL, - oi_width SMALLINT NOT NULL, - oi_height SMALLINT NOT NULL, + oi_size INTEGER NOT NULL, + oi_width INTEGER NOT NULL, + oi_height INTEGER NOT NULL, oi_bits SMALLINT NOT NULL, oi_description TEXT, - oi_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, + oi_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, oi_user_text TEXT NOT NULL, oi_timestamp TIMESTAMPTZ NOT NULL ); @@ -235,7 +258,7 @@ CREATE TABLE filearchive ( fa_archive_name TEXT, fa_storage_group VARCHAR(16), fa_storage_key CHAR(64), - fa_deleted_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, + fa_deleted_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, fa_deleted_timestamp TIMESTAMPTZ NOT NULL, fa_deleted_reason TEXT, fa_size SMALLINT NOT NULL, @@ -247,7 +270,7 @@ CREATE TABLE filearchive ( fa_major_mime TEXT DEFAULT 'unknown', fa_minor_mime TEXT DEFAULT 'unknown', fa_description TEXT NOT NULL, - fa_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, + fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, fa_user_text TEXT NOT NULL, fa_timestamp TIMESTAMPTZ ); @@ -262,7 +285,7 @@ CREATE TABLE recentchanges ( rc_id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'), rc_timestamp TIMESTAMPTZ NOT NULL, rc_cur_time TIMESTAMPTZ NOT NULL, - rc_user INTEGER NULL REFERENCES "user"(user_id) ON DELETE SET NULL, + rc_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, rc_user_text TEXT NOT NULL, rc_namespace SMALLINT NOT NULL, rc_title TEXT NOT NULL, @@ -270,7 +293,7 @@ CREATE TABLE recentchanges ( rc_minor CHAR NOT NULL DEFAULT '0', rc_bot CHAR NOT NULL DEFAULT '0', rc_new CHAR NOT NULL DEFAULT '0', - rc_cur_id INTEGER NOT NULL REFERENCES page(page_id), + rc_cur_id INTEGER NULL REFERENCES page(page_id) ON DELETE SET NULL, rc_this_oldid INTEGER NOT NULL, rc_last_oldid INTEGER NOT NULL, rc_type CHAR NOT NULL DEFAULT '0', @@ -287,7 +310,7 @@ CREATE INDEX rc_ip ON recentchanges (rc_ip); CREATE TABLE watchlist ( - wl_user INTEGER NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE, + wl_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE CASCADE, wl_namespace SMALLINT NOT NULL DEFAULT 0, wl_title TEXT NOT NULL, wl_notificationtimestamp TIMESTAMPTZ @@ -343,7 +366,7 @@ CREATE TABLE logging ( log_type TEXT NOT NULL, log_action TEXT NOT NULL, log_timestamp TIMESTAMPTZ NOT NULL, - log_user INTEGER REFERENCES "user"(user_id) ON DELETE SET NULL, + log_user INTEGER REFERENCES mwuser(user_id) ON DELETE SET NULL, log_namespace SMALLINT NOT NULL, log_title TEXT NOT NULL, log_comment TEXT, @@ -383,38 +406,71 @@ CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS $mw$ BEGIN IF TG_OP = 'INSERT' THEN - NEW.titlevector = to_tsvector(NEW.page_title); + NEW.titlevector = to_tsvector('default',NEW.page_title); ELSIF NEW.page_title != OLD.page_title THEN - NEW.titlevector := to_tsvector(NEW.page_title); + NEW.titlevector := to_tsvector('default',NEW.page_title); END IF; RETURN NEW; END; $mw$; CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATE ON page -FOR EACH ROW EXECUTE PROCEDURE ts2_page_title(); + FOR EACH ROW EXECUTE PROCEDURE ts2_page_title(); -ALTER TABLE text ADD textvector tsvector; -CREATE INDEX ts2_page_text ON text USING gist(textvector); +ALTER TABLE pagecontent ADD textvector tsvector; +CREATE INDEX ts2_page_text ON pagecontent USING gist(textvector); CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS $mw$ BEGIN IF TG_OP = 'INSERT' THEN - NEW.textvector = to_tsvector(NEW.old_text); + NEW.textvector = to_tsvector('default',NEW.old_text); ELSIF NEW.old_text != OLD.old_text THEN - NEW.textvector := to_tsvector(NEW.old_text); + NEW.textvector := to_tsvector('default',NEW.old_text); END IF; RETURN NEW; END; $mw$; -CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON text -FOR EACH ROW EXECUTE PROCEDURE ts2_page_text(); +CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent + FOR EACH ROW EXECUTE PROCEDURE ts2_page_text(); -CREATE OR REPLACE FUNCTION add_interwiki (TEXT,INT,CHAR) RETURNS INT LANGUAGE SQL AS +CREATE FUNCTION add_interwiki (TEXT,INT,CHAR) RETURNS INT LANGUAGE SQL AS $mw$ INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3); SELECT 1; $mw$; + +-- This table is not used unless profiling is turned on +CREATE TABLE profiling ( + pf_count INTEGER NOT NULL DEFAULT 0, + pf_time NUMERIC(18,10) NOT NULL DEFAULT 0, + pf_name TEXT NOT NULL, + pf_server TEXT NULL +); +CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server); + + +CREATE TABLE mediawiki_version ( + type TEXT NOT NULL, + mw_version TEXT NOT NULL, + notes TEXT NULL, + + pg_version TEXT NULL, + pg_dbname TEXT NULL, + pg_user TEXT NULL, + pg_port TEXT NULL, + mw_schema TEXT NULL, + ts2_schema TEXT NULL, + ctype TEXT NULL, + + sql_version TEXT NULL, + sql_date TEXT NULL, + cdate TIMESTAMPTZ NOT NULL DEFAULT now() +); + +INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date) + VALUES ('Creation','??','$LastChangedRevision: 16747 $','$LastChangedDate: 2006-10-02 17:55:26 -0700 (Mon, 02 Oct 2006) $'); + + COMMIT; -- cgit v1.2.3-54-g00ecf