From 086ae52d12011746a75f5588e877347bc0457352 Mon Sep 17 00:00:00 2001 From: Pierre Schmitz Date: Fri, 21 Mar 2008 11:49:34 +0100 Subject: Update auf MediaWiki 1.12.0 --- maintenance/postgres/tables.sql | 112 ++++++++++++++++++++++------------------ 1 file changed, 62 insertions(+), 50 deletions(-) (limited to 'maintenance/postgres/tables.sql') diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql index 8f99c84a..dc1d7e92 100644 --- a/maintenance/postgres/tables.sql +++ b/maintenance/postgres/tables.sql @@ -5,7 +5,7 @@ -- For information about each table, please see the notes in maintenance/tables.sql -- Please make sure all dollar-quoting uses $mw$ at the start of the line -- We can't use SERIAL everywhere: the sequence names are hard-coded into the PHP --- TODO: Change CHAR to BOOL (still needed as CHAR due to some PHP code) +-- TODO: Change CHAR/SMALLINT to BOOL (still needed as CHAR due to some PHP code) BEGIN; SET client_min_messages = 'ERROR'; @@ -18,9 +18,9 @@ CREATE TABLE mwuser ( -- replace reserved word 'user' user_password TEXT, user_newpassword TEXT, user_newpass_time TIMESTAMPTZ, - user_token CHAR(32), + user_token TEXT, user_email TEXT, - user_email_token CHAR(32), + user_email_token TEXT, user_email_token_expires TIMESTAMPTZ, user_email_authenticated TIMESTAMPTZ, user_options TEXT, @@ -55,8 +55,8 @@ CREATE TABLE page ( page_title TEXT NOT NULL, page_restrictions TEXT, page_counter BIGINT NOT NULL DEFAULT 0, - page_is_redirect CHAR NOT NULL DEFAULT 0, - page_is_new CHAR NOT NULL DEFAULT 0, + page_is_redirect SMALLINT NOT NULL DEFAULT 0, + page_is_new SMALLINT NOT NULL DEFAULT 0, page_random NUMERIC(15,14) NOT NULL DEFAULT RANDOM(), page_touched TIMESTAMPTZ, page_latest INTEGER NOT NULL, -- FK? @@ -91,8 +91,8 @@ CREATE TABLE revision ( rev_user INTEGER NOT NULL REFERENCES mwuser(user_id) ON DELETE RESTRICT, rev_user_text TEXT NOT NULL, rev_timestamp TIMESTAMPTZ NOT NULL, - rev_minor_edit CHAR NOT NULL DEFAULT '0', - rev_deleted CHAR NOT NULL DEFAULT '0', + rev_minor_edit SMALLINT NOT NULL DEFAULT 0, + rev_deleted SMALLINT NOT NULL DEFAULT 0, rev_len INTEGER NULL, rev_parent_id INTEGER NULL ); @@ -127,17 +127,17 @@ ALTER TABLE page_restrictions ADD CONSTRAINT page_restrictions_pk PRIMARY KEY (p CREATE TABLE archive ( ar_namespace SMALLINT NOT NULL, ar_title TEXT NOT NULL, - ar_text TEXT, + ar_text TEXT, -- technically should be bytea, but not used anymore ar_page_id INTEGER NULL, ar_comment TEXT, 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', + ar_minor_edit SMALLINT NOT NULL DEFAULT 0, ar_flags TEXT, ar_rev_id INTEGER, ar_text_id INTEGER, - ar_deleted INTEGER NOT NULL DEFAULT 0, + ar_deleted SMALLINT NOT NULL DEFAULT 0, ar_len INTEGER NULL ); CREATE INDEX archive_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp); @@ -161,7 +161,7 @@ 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, - tl_namespace TEXT NOT NULL, + tl_namespace SMALLINT NOT NULL, tl_title TEXT NOT NULL ); CREATE UNIQUE INDEX templatelinks_unique ON templatelinks (tl_namespace,tl_title,tl_from); @@ -202,7 +202,7 @@ CREATE TABLE site_stats ( ss_row_id INTEGER NOT NULL UNIQUE, ss_total_views INTEGER DEFAULT 0, ss_total_edits INTEGER DEFAULT 0, - ss_good_articles INTEGER DEFAULT 0, + ss_good_articles INTEGER DEFAULT 0, ss_total_pages INTEGER DEFAULT -1, ss_users INTEGER DEFAULT -1, ss_admins INTEGER DEFAULT -1, @@ -222,15 +222,15 @@ CREATE TABLE ipblocks ( 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_enable_autoblock CHAR NOT NULL DEFAULT '1', + ipb_auto SMALLINT NOT NULL DEFAULT 0, + ipb_anon_only SMALLINT NOT NULL DEFAULT 0, + ipb_create_account SMALLINT NOT NULL DEFAULT 1, + ipb_enable_autoblock SMALLINT NOT NULL DEFAULT 1, ipb_expiry TIMESTAMPTZ NOT NULL, ipb_range_start TEXT, ipb_range_end TEXT, - ipb_deleted INTEGER NOT NULL DEFAULT 0, - ipb_block_email CHAR NOT NULL DEFAULT '0' + ipb_deleted SMALLINT NOT NULL DEFAULT 0, + ipb_block_email SMALLINT NOT NULL DEFAULT 0 ); CREATE INDEX ipb_address ON ipblocks (ipb_address); @@ -243,7 +243,7 @@ CREATE TABLE image ( img_size INTEGER NOT NULL, img_width INTEGER NOT NULL, img_height INTEGER NOT NULL, - img_metadata TEXT, + img_metadata BYTEA NOT NULL DEFAULT '', img_bits SMALLINT, img_media_type TEXT, img_major_mime TEXT DEFAULT 'unknown', @@ -259,7 +259,7 @@ CREATE INDEX img_timestamp_idx ON image (img_timestamp); CREATE INDEX img_sha1 ON image (img_sha1); CREATE TABLE oldimage ( - oi_name TEXT NOT NULL REFERENCES image(img_name), + oi_name TEXT NOT NULL, oi_archive_name TEXT NOT NULL, oi_size INTEGER NOT NULL, oi_width INTEGER NOT NULL, @@ -273,9 +273,10 @@ CREATE TABLE oldimage ( oi_media_type TEXT NULL, oi_major_mime TEXT NOT NULL DEFAULT 'unknown', oi_minor_mime TEXT NOT NULL DEFAULT 'unknown', - oi_deleted CHAR NOT NULL DEFAULT '0', + oi_deleted SMALLINT NOT NULL DEFAULT 0, oi_sha1 TEXT NOT NULL DEFAULT '' ); +ALTER TABLE oldimage ADD CONSTRAINT oldimage_oi_name_fkey_cascade FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE; CREATE INDEX oi_name_timestamp ON oldimage (oi_name,oi_timestamp); CREATE INDEX oi_name_archive_name ON oldimage (oi_name,oi_archive_name); CREATE INDEX oi_sha1 ON oldimage (oi_sha1); @@ -285,15 +286,15 @@ CREATE TABLE filearchive ( fa_id SERIAL NOT NULL PRIMARY KEY, fa_name TEXT NOT NULL, fa_archive_name TEXT, - fa_storage_group VARCHAR(16), - fa_storage_key CHAR(64), + fa_storage_group TEXT, + fa_storage_key TEXT, 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, - fa_width SMALLINT NOT NULL, - fa_height SMALLINT NOT NULL, - fa_metadata TEXT, + fa_size INTEGER NOT NULL, + fa_width INTEGER NOT NULL, + fa_height INTEGER NOT NULL, + fa_metadata BYTEA NOT NULL DEFAULT '', fa_bits SMALLINT, fa_media_type TEXT, fa_major_mime TEXT DEFAULT 'unknown', @@ -302,7 +303,7 @@ CREATE TABLE filearchive ( fa_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, fa_user_text TEXT NOT NULL, fa_timestamp TIMESTAMPTZ, - fa_deleted INTEGER NOT NULL DEFAULT 0 + fa_deleted SMALLINT NOT NULL DEFAULT 0 ); CREATE INDEX fa_name_time ON filearchive (fa_name, fa_timestamp); CREATE INDEX fa_dupe ON filearchive (fa_storage_group, fa_storage_key); @@ -320,20 +321,20 @@ CREATE TABLE recentchanges ( rc_namespace SMALLINT NOT NULL, rc_title TEXT NOT NULL, rc_comment TEXT, - rc_minor CHAR NOT NULL DEFAULT '0', - rc_bot CHAR NOT NULL DEFAULT '0', - rc_new CHAR NOT NULL DEFAULT '0', + rc_minor SMALLINT NOT NULL DEFAULT 0, + rc_bot SMALLINT NOT NULL DEFAULT 0, + rc_new SMALLINT NOT NULL DEFAULT 0, 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', + rc_type SMALLINT NOT NULL DEFAULT 0, rc_moved_to_ns SMALLINT, rc_moved_to_title TEXT, - rc_patrolled CHAR NOT NULL DEFAULT '0', + rc_patrolled SMALLINT NOT NULL DEFAULT 0, rc_ip CIDR, rc_old_len INTEGER, rc_new_len INTEGER, - rc_deleted INTEGER NOT NULL DEFAULT 0, + rc_deleted SMALLINT NOT NULL DEFAULT 0, rc_logid INTEGER NOT NULL DEFAULT 0, rc_log_type TEXT, rc_log_action TEXT, @@ -365,16 +366,16 @@ CREATE TABLE math ( CREATE TABLE interwiki ( - iw_prefix TEXT NOT NULL UNIQUE, - iw_url TEXT NOT NULL, - iw_local CHAR NOT NULL, - iw_trans CHAR NOT NULL DEFAULT '0' + iw_prefix TEXT NOT NULL UNIQUE, + iw_url TEXT NOT NULL, + iw_local SMALLINT NOT NULL, + iw_trans SMALLINT NOT NULL DEFAULT 0 ); CREATE TABLE querycache ( qc_type TEXT NOT NULL, - qc_value SMALLINT NOT NULL, + qc_value INTEGER NOT NULL, qc_namespace SMALLINT NOT NULL, qc_title TEXT NOT NULL ); @@ -387,7 +388,7 @@ CREATE TABLE querycache_info ( CREATE TABLE querycachetwo ( qcc_type TEXT NOT NULL, - qcc_value SMALLINT NOT NULL DEFAULT 0, + qcc_value INTEGER NOT NULL DEFAULT 0, qcc_namespace INTEGER NOT NULL DEFAULT 0, qcc_title TEXT NOT NULL DEFAULT '', qcc_namespacetwo INTEGER NOT NULL DEFAULT 0, @@ -398,7 +399,7 @@ CREATE INDEX querycachetwo_title ON querycachetwo (qcc_type,qcc_namespace,q CREATE INDEX querycachetwo_titletwo ON querycachetwo (qcc_type,qcc_namespacetwo,qcc_titletwo); CREATE TABLE objectcache ( - keyname CHAR(255) UNIQUE, + keyname TEXT UNIQUE, value BYTEA NOT NULL DEFAULT '', exptime TIMESTAMPTZ NOT NULL ); @@ -422,7 +423,7 @@ CREATE TABLE logging ( log_title TEXT NOT NULL, log_comment TEXT, log_params TEXT, - log_deleted INTEGER NOT NULL DEFAULT 0 + log_deleted SMALLINT NOT NULL DEFAULT 0 ); CREATE INDEX logging_type_name ON logging (log_type, log_timestamp); CREATE INDEX logging_user_time ON logging (log_timestamp, log_user); @@ -451,15 +452,16 @@ CREATE TABLE job ( CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title); -- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables +-- Note: if version 8.3 or higher, we remove the 'default' arg ALTER TABLE page ADD titlevector tsvector; CREATE FUNCTION ts2_page_title() RETURNS TRIGGER LANGUAGE plpgsql AS $mw$ BEGIN IF TG_OP = 'INSERT' THEN - NEW.titlevector = to_tsvector('default',NEW.page_title); + NEW.titlevector = to_tsvector('default',REPLACE(NEW.page_title,'/',' ')); ELSIF NEW.page_title != OLD.page_title THEN - NEW.titlevector := to_tsvector('default',NEW.page_title); + NEW.titlevector := to_tsvector('default',REPLACE(NEW.page_title,'/',' ')); END IF; RETURN NEW; END; @@ -486,11 +488,12 @@ CREATE TRIGGER ts2_page_text BEFORE INSERT OR UPDATE ON pagecontent FOR EACH ROW EXECUTE PROCEDURE ts2_page_text(); -- These are added by the setup script due to version compatibility issues --- If using 8.1, switch from "gin" to "gist" --- CREATE INDEX ts2_page_title ON page USING gin(titlevector); --- CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector); +-- If using 8.1, we switch from "gin" to "gist" -CREATE FUNCTION add_interwiki (TEXT,INT,CHAR) RETURNS INT LANGUAGE SQL AS +CREATE INDEX ts2_page_title ON page USING gin(titlevector); +CREATE INDEX ts2_page_text ON pagecontent USING gin(textvector); + +CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS $mw$ INSERT INTO interwiki (iw_prefix, iw_url, iw_local) VALUES ($1,$2,$3); SELECT 1; @@ -505,6 +508,16 @@ CREATE TABLE profiling ( ); CREATE UNIQUE INDEX pf_name_server ON profiling (pf_name, pf_server); +CREATE TABLE protected_titles ( + pt_namespace SMALLINT NOT NULL, + pt_title TEXT NOT NULL, + pt_user INTEGER NULL REFERENCES mwuser(user_id) ON DELETE SET NULL, + pt_reason TEXT NULL, + pt_timestamp TIMESTAMPTZ NOT NULL, + pt_expiry TIMESTAMPTZ NULL, + pt_create_perm TEXT NOT NULL DEFAULT '' +); +CREATE UNIQUE INDEX protected_titles_unique ON protected_titles(pt_namespace, pt_title); CREATE TABLE mediawiki_version ( type TEXT NOT NULL, @@ -525,6 +538,5 @@ CREATE TABLE mediawiki_version ( ); INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date) - VALUES ('Creation','??','$LastChangedRevision: 25527 $','$LastChangedDate: 2007-09-05 04:14:18 -0400 (Wed, 05 Sep 2007) $'); - + VALUES ('Creation','??','$LastChangedRevision: 30800 $','$LastChangedDate: 2008-02-10 08:50:38 -0800 (Sun, 10 Feb 2008) $'); -- cgit v1.2.3-54-g00ecf