From 086ae52d12011746a75f5588e877347bc0457352 Mon Sep 17 00:00:00 2001
From: Pierre Schmitz <pierre@archlinux.de>
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

