update to MediaWiki 1.17.0

3 files changed, 125 insertions, 88 deletions

diff --git a/maintenance/ibm_db2/README b/maintenance/ibm_db2/README
deleted file mode 100644
index 3c3f381c..00000000
--- a/maintenance/ibm_db2/README
+++ /dev/null
@@ -1,3 +0,0 @@
-== See also ==
-*[http://www.mediawiki.org/wiki/Manual:IBM_DB2 Installation instructions]
-*[http://ca.php.net/manual/en/function.db2-connect.php PHP Manual for DB2 functions]
\ No newline at end of file
diff --git a/maintenance/ibm_db2/foreignkeys.sql b/maintenance/ibm_db2/foreignkeys.sql
new file mode 100644
index 00000000..81a88eb9
--- /dev/null
+++ b/maintenance/ibm_db2/foreignkeys.sql
@@ -0,0 +1,107 @@
+-- good
+ALTER TABLE user_groups ADD CONSTRAINT USER_GROUPS_FK1 FOREIGN KEY (ug_user) REFERENCES user(user_id) ON DELETE CASCADE
+;
+
+-- good
+ALTER TABLE user_newtalk ADD CONSTRAINT USER_NEWTALK_FK1 FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
+;
+
+-- referenced value not found
+ALTER TABLE revision ADD CONSTRAINT REVISION_PAGE_FK FOREIGN KEY (rev_page) REFERENCES page(page_id) ON DELETE CASCADE
+;
+-- referenced value not found
+ALTER TABLE revision ADD CONSTRAINT REVISION_USER_FK FOREIGN KEY (rev_user) REFERENCES user(user_id) ON DELETE RESTRICT
+;
+
+-- good
+ALTER TABLE page_restrictions ADD CONSTRAINT PAGE_RESTRICTIONS_PAGE_FK FOREIGN KEY (pr_page) REFERENCES page(page_id) ON DELETE CASCADE
+;
+
+-- good
+ALTER TABLE page_props ADD CONSTRAINT PAGE_PROPS_PAGE_FK FOREIGN KEY (pp_page) REFERENCES page(page_id) ON DELETE CASCADE
+;
+
+-- cannot contain null values
+-- ALTER TABLE archive ADD CONSTRAINT ARCHIVE_USER_FK FOREIGN KEY (ar_user) REFERENCES user(user_id) ON DELETE SET NULL
+--;
+
+-- referenced value not found
+ALTER TABLE redirect ADD CONSTRAINT REDIRECT_FROM_FK FOREIGN KEY (rd_from) REFERENCES page(page_id) ON DELETE CASCADE
+;
+
+-- referenced value not found
+ALTER TABLE pagelinks ADD CONSTRAINT PAGELINKS_FROM_FK FOREIGN KEY (pl_from) REFERENCES page(page_id) ON DELETE CASCADE
+;
+
+-- good
+ALTER TABLE templatelinks ADD CONSTRAINT TEMPLATELINKS_FROM_FK FOREIGN KEY (tl_from) REFERENCES page(page_id) ON DELETE CASCADE
+;
+
+-- good
+ALTER TABLE imagelinks ADD CONSTRAINT IMAGELINKS_FROM_FK FOREIGN KEY (il_from) REFERENCES page(page_id) ON DELETE CASCADE
+;
+
+-- good
+ALTER TABLE categorylinks ADD CONSTRAINT CATEGORYLINKS_FROM_FK FOREIGN KEY (cl_from) REFERENCES page(page_id) ON DELETE CASCADE
+;
+
+-- good
+ALTER TABLE externallinks ADD CONSTRAINT EXTERNALLINKS_FROM_FK FOREIGN KEY (el_from) REFERENCES page(page_id) ON DELETE CASCADE
+;
+
+-- good
+ALTER TABLE langlinks ADD CONSTRAINT LANGLINKS_FROM_FK FOREIGN KEY (ll_from) REFERENCES page(page_id) ON DELETE CASCADE
+;
+
+-- cannot contain null values
+-- ALTER TABLE ipblocks ADD CONSTRAINT IPBLOCKS_USER_FK FOREIGN KEY (ipb_user) REFERENCES user(user_id) ON DELETE SET NULL
+--;
+
+-- good
+ALTER TABLE ipblocks ADD CONSTRAINT IPBLOCKS_BY_FK FOREIGN KEY (ipb_by) REFERENCES user(user_id) ON DELETE CASCADE
+;
+
+-- cannot contain null values
+-- ALTER TABLE image ADD CONSTRAINT IMAGE_USER_FK FOREIGN KEY (img_user) REFERENCES user(user_id) ON DELETE SET NULL
+--;
+
+-- cannot contain null values
+-- ALTER TABLE oldimage ADD CONSTRAINT OLDIMAGE_USER_FK FOREIGN KEY (oi_user) REFERENCES user(user_id) ON DELETE SET NULL
+--;
+
+-- good
+ALTER TABLE oldimage ADD CONSTRAINT OLDIMAGE_NAME_FK FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE
+;
+
+-- cannot contain null values
+-- ALTER TABLE filearchive ADD CONSTRAINT FILEARCHIVE_DELETED_USER_FK FOREIGN KEY (fa_deleted_user) REFERENCES user(user_id) ON DELETE SET NULL
+--;
+
+-- cannot contain null values
+-- ALTER TABLE filearchive ADD CONSTRAINT FILEARCHIVE_USER_FK FOREIGN KEY (fa_user) REFERENCES user(user_id) ON DELETE SET NULL
+--;
+
+-- cannot contain null values
+-- ALTER TABLE recentchanges ADD CONSTRAINT RECENTCHANGES_USER_FK FOREIGN KEY (rc_user) REFERENCES user(user_id) ON DELETE SET NULL
+--;
+
+-- cannot contain null values
+-- ALTER TABLE recentchanges ADD CONSTRAINT RECENTCHANGES_CUR_ID_FK FOREIGN KEY (rc_cur_id) REFERENCES page(page_id) ON DELETE SET NULL
+--;
+
+-- good
+ALTER TABLE watchlist ADD CONSTRAINT WATCHLIST_USER_FK FOREIGN KEY (wl_user) REFERENCES user(user_id) ON DELETE CASCADE
+;
+
+-- good
+-- already in MySQL schema
+ALTER TABLE trackbacks ADD CONSTRAINT TRACKBACKS_PAGE_FK FOREIGN KEY (tb_page) REFERENCES page(page_id) ON DELETE CASCADE
+;
+
+-- cannot contain null values
+-- ALTER TABLE protected_titles ADD CONSTRAINT PROTECTED_TITLES_USER_FK FOREIGN KEY (pt_user) REFERENCES user(user_id) ON DELETE SET NULL
+--;
+
+-- cannot contain null values
+-- ALTER TABLE logging ADD CONSTRAINT LOGGING_USER_FK FOREIGN KEY (log_user) REFERENCES user(user_id) ON DELETE SET NULL
+--;
\ No newline at end of file
diff --git a/maintenance/ibm_db2/tables.sql b/maintenance/ibm_db2/tables.sql
index 71c161c6..546c871d 100644
--- a/maintenance/ibm_db2/tables.sql
+++ b/maintenance/ibm_db2/tables.sql
@@ -8,6 +8,7 @@
 
 
 CREATE TABLE user (
+  -- Needs to start with 0
   user_id					BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
   user_name                 VARCHAR(255)     NOT NULL  UNIQUE,
   user_real_name            VARCHAR(255),
@@ -47,11 +48,7 @@ CREATE TABLE user_groups (
   --    REFERENCES user(user_id) ON DELETE CASCADE,
   ug_group  VARCHAR(255)     NOT NULL
 );
-CREATE UNIQUE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
---leonsp:
-CREATE UNIQUE INDEX user_groups_include_idx
-	ON user_groups(ug_user)
-	INCLUDE (ug_group);
+CREATE INDEX user_groups_unique ON user_groups (ug_user, ug_group);
 
 
 CREATE TABLE user_newtalk (
@@ -71,7 +68,7 @@ CREATE UNIQUE INDEX user_newtalk_include_idx
 
 
 CREATE TABLE page (
-  page_id			 BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
+  page_id			 BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   page_namespace     SMALLINT       NOT NULL,
   page_title         VARCHAR(255)   NOT NULL,
   page_restrictions  VARCHAR(1024),
@@ -96,7 +93,7 @@ CREATE UNIQUE INDEX page_name_include
 
 
 CREATE TABLE revision (
-  rev_id			BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
+  rev_id			BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   rev_page        	BIGINT NOT NULL DEFAULT 0,
   --      REFERENCES page (page_id) ON DELETE CASCADE,
   rev_text_id     	BIGINT, -- FK
@@ -119,9 +116,7 @@ CREATE INDEX rev_user_text_idx      ON revision (rev_user_text);
 
 
 CREATE TABLE text ( -- replaces reserved word 'text'
-  --old_id     INTEGER  NOT NULL,
-  old_id	INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
-  --PRIMARY KEY DEFAULT nextval('text_old_id_val'),
+  old_id	INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   old_text   CLOB(16M) INLINE LENGTH 4096,
   old_flags  VARCHAR(1024)
 );
@@ -129,8 +124,8 @@ CREATE TABLE text ( -- replaces reserved word 'text'
 
 CREATE TABLE page_restrictions (
   --pr_id      INTEGER      NOT NULL  UNIQUE, --DEFAULT nextval('pr_id_val'),
-  --pr_id	INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
-  pr_id		BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 0),
+  --pr_id	INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
+  pr_id		BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   pr_page    INTEGER              NOT NULL DEFAULT 0,
   --(used to be nullable)
   --  REFERENCES page (page_id) ON DELETE CASCADE,
@@ -184,7 +179,7 @@ CREATE INDEX archive_user_text            ON archive (ar_user_text);
 
 
 CREATE TABLE redirect (
-  rd_from       BIGINT  NOT NULL  PRIMARY KEY,
+  rd_from       BIGINT  NOT NULL  PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   --REFERENCES page(page_id) ON DELETE CASCADE,
   rd_namespace  SMALLINT NOT NULL  DEFAULT 0,
   rd_title      VARCHAR(255)     NOT NULL DEFAULT '',
@@ -248,7 +243,7 @@ CREATE INDEX externallinks_index   ON externallinks (el_index);
 --
 CREATE TABLE external_user (
   -- Foreign key to user_id
-  eu_local_id			BIGINT NOT NULL PRIMARY KEY,
+  eu_local_id			BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
 
   -- Some opaque identifier provided by the external database
   eu_external_id		VARCHAR(255) NOT NULL
@@ -289,7 +284,7 @@ CREATE TABLE hitcounter (
 );
 
 CREATE TABLE ipblocks (
-  ipb_id                INTEGER      NOT NULL  PRIMARY KEY,
+  ipb_id                INTEGER      NOT NULL  PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   --DEFAULT nextval('ipblocks_ipb_id_val'),
   ipb_address           VARCHAR(1024),
   ipb_user              BIGINT NOT NULL DEFAULT 0,
@@ -358,7 +353,6 @@ CREATE TABLE oldimage (
   oi_sha1          VARCHAR(255)         NOT NULL DEFAULT ''
   --FOREIGN KEY (oi_name) REFERENCES image(img_name) ON DELETE CASCADE
 );
---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);
@@ -366,7 +360,7 @@ CREATE INDEX oi_sha1              ON oldimage (oi_sha1);
 
 
 CREATE TABLE filearchive (
-  fa_id                 INTEGER      NOT NULL PRIMARY KEY,
+  fa_id                 INTEGER      NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   --PRIMARY KEY DEFAULT nextval('filearchive_fa_id_seq'),
   fa_name               VARCHAR(255)         NOT NULL,
   fa_archive_name       VARCHAR(255),
@@ -398,7 +392,7 @@ CREATE INDEX fa_nouser    ON filearchive (fa_deleted_user);
 
 
 CREATE TABLE recentchanges (
-  rc_id              INTEGER      NOT NULL PRIMARY KEY,
+  rc_id              INTEGER      NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   --PRIMARY KEY DEFAULT nextval('rc_rc_id_seq'),
   rc_timestamp       TIMESTAMP(3)  NOT NULL,
   rc_cur_time        TIMESTAMP(3)  NOT NULL,
@@ -509,7 +503,7 @@ CREATE TABLE transcache (
 
 
 CREATE TABLE logging (
-  log_id			BIGINT      NOT NULL PRIMARY KEY,
+  log_id			BIGINT      NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   --PRIMARY KEY DEFAULT nextval('log_log_id_seq'),
   log_type			VARCHAR(32)         NOT NULL,
   log_action		VARCHAR(32)         NOT NULL,
@@ -534,10 +528,11 @@ CREATE INDEX log_page_id_time ON logging (log_page,log_timestamp);
 
 
 CREATE TABLE trackbacks (
-  tb_id     INTEGER  NOT NULL PRIMARY KEY,
+  tb_id     INTEGER  NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   --PRIMARY KEY DEFAULT nextval('trackbacks_tb_id_seq'),
   -- foreign key also in MySQL
-  tb_page   INTEGER REFERENCES page(page_id) ON DELETE CASCADE,
+  tb_page   INTEGER,
+  -- REFERENCES page(page_id) ON DELETE CASCADE,
   tb_title  VARCHAR(255)     NOT NULL,
   tb_url    CLOB(64K) INLINE LENGTH 4096	     NOT NULL,
   tb_ex     CLOB(64K) INLINE LENGTH 4096,
@@ -548,7 +543,7 @@ CREATE INDEX trackback_page ON trackbacks (tb_page);
 
 
 CREATE TABLE job (
-  job_id         BIGINT   NOT NULL PRIMARY KEY,
+  job_id         BIGINT   NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   --PRIMARY KEY DEFAULT nextval('job_job_id_seq'),
   job_cmd        VARCHAR(255)      NOT NULL,
   job_namespace  SMALLINT  NOT NULL,
@@ -558,47 +553,6 @@ CREATE TABLE job (
 CREATE INDEX job_cmd_namespace_title ON job (job_cmd, job_namespace, job_title);
 
 
-
--- Postgres' Tsearch2 dropped
---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',REPLACE(NEW.page_title,'/',' '));
---ELSIF NEW.page_title != OLD.page_title THEN
---  NEW.titlevector := to_tsvector('default',REPLACE(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();
-
-
---ALTER TABLE text ADD textvector tsvector;
---CREATE FUNCTION ts2_page_text() RETURNS TRIGGER LANGUAGE plpgsql AS
---$mw$
---BEGIN
---IF TG_OP = 'INSERT' THEN
---  NEW.textvector = to_tsvector('default',NEW.old_text);
---ELSIF NEW.old_text != OLD.old_text THEN
---  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();
-
--- These are added by the setup script due to version compatibility issues
--- If using 8.1, we switch from "gin" to "gist"
-
---CREATE INDEX ts2_page_title ON page USING gin(titlevector);
---CREATE INDEX ts2_page_text ON text USING gin(textvector);
-
 --TODO
 --CREATE FUNCTION add_interwiki (TEXT,INT,SMALLINT) RETURNS INT LANGUAGE SQL AS
 --$mw$
@@ -644,7 +598,7 @@ CREATE TABLE updatelog (
 
 
 CREATE TABLE category (
-  cat_id       INTEGER  NOT NULL PRIMARY KEY,
+  cat_id       INTEGER  NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
   --PRIMARY KEY DEFAULT nextval('category_id_seq'),
   cat_title    VARCHAR(255)     NOT NULL,
   cat_pages    INTEGER  NOT NULL  DEFAULT 0,
@@ -721,27 +675,6 @@ CREATE TABLE log_search (
 CREATE UNIQUE INDEX ls_field_val ON log_search (ls_field,ls_value,ls_log_id);
 CREATE INDEX ls_log_id ON log_search (ls_log_id);
 
-CREATE TABLE mediawiki_version (
-  type         VARCHAR(1024)         NOT NULL,
-  mw_version   VARCHAR(1024)         NOT NULL,
-  notes        VARCHAR(1024)         ,
-
-  pg_version   VARCHAR(1024)         ,
-  pg_dbname    VARCHAR(1024)         ,
-  pg_user      VARCHAR(1024)         ,
-  pg_port      VARCHAR(1024)         ,
-  mw_schema    VARCHAR(1024)         ,
-  ts2_schema   VARCHAR(1024)         ,
-  ctype        VARCHAR(1024)         ,
-
-  sql_version  VARCHAR(1024)         ,
-  sql_date     VARCHAR(1024)         ,
-  cdate        TIMESTAMP(3)  NOT NULL DEFAULT CURRENT TIMESTAMP
-);
-
-INSERT INTO mediawiki_version (type,mw_version,sql_version,sql_date)
-  VALUES ('Creation','??','$LastChangedRevision: 34049 $','$LastChangedDate: 2008-04-30 10:20:36 -0400 (Wed, 30 Apr 2008) $');
-
 -- Table for storing localisation data
 CREATE TABLE l10n_cache (
   -- Language code