Migrate archive table to abstract schema

One of the last ones left.

For MySQL/Sqlite:
 - Dropping default of ar_timestamp, empty string is not a valid
   timestamp.
 - Changing ar_title from "varchar() binary" to varbinary

for Postgres:
 - Set default for ar_namespace and ar_title
 - Change datatype of ar_comment_id, ar_actor, ar_namespace
The indexes were fixed separately.

Bug: T230428
Bug: T164898
Bug: T42626
Depends-On: I83cf1cd51ac9cf933c9175cefd6e38a6914f3494
Change-Id: Ic1d13a82b27f7fa39a0f0ea9c5b7b193b007e4ab

14 files changed, 282 insertions, 86 deletions

diff --git a/includes/installer/DatabaseInstaller.php b/includes/installer/DatabaseInstaller.php
index acd00d06072b..422db7e2b6c7 100644
--- a/includes/installer/DatabaseInstaller.php
+++ b/includes/installer/DatabaseInstaller.php
@@ -264,8 +264,7 @@ abstract class DatabaseInstaller {
 	 * @return Status
 	 */
 	public function createManualTables() {
-		// TODO: Set "archiveTableMustNotExist" to "false" when archive table is migrated to tables.json
-		return $this->stepApplySourceFile( 'getSchemaPath', 'install-manual', true );
+		return $this->stepApplySourceFile( 'getSchemaPath', 'install-manual', false );
 	}
 
 	/**
diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php
index ac4bc30bfc3c..4fde1723f695 100644
--- a/includes/installer/MysqlUpdater.php
+++ b/includes/installer/MysqlUpdater.php
@@ -248,6 +248,8 @@ class MysqlUpdater extends DatabaseUpdater {
 			[ 'modifyField', 'recentchanges', 'rc_id', 'patch-recentchanges-rc_id.sql' ],
 			[ 'renameIndex', 'recentchanges', 'new_name_timestamp', 'rc_new_name_timestamp', false,
 				'patch-recentchanges-rc_new_name_timestamp.sql' ],
+			[ 'dropDefault', 'archive', 'ar_timestamp' ],
+			[ 'modifyField', 'archive', 'ar_title', 'patch-archive-ar_title-varbinary.sql' ],
 		];
 	}
 
diff --git a/includes/installer/PostgresInstaller.php b/includes/installer/PostgresInstaller.php
index 9c9d8ec922bb..c5d90254497e 100644
--- a/includes/installer/PostgresInstaller.php
+++ b/includes/installer/PostgresInstaller.php
@@ -645,6 +645,11 @@ class PostgresInstaller extends DatabaseInstaller {
 		return $status;
 	}
 
+	public function createManualTables() {
+		// Already handled above. Do nothing.
+		return Status::newGood();
+	}
+
 	public function getGlobalDefaults() {
 		// The default $wgDBmwschema is null, which breaks Postgres and other DBMSes that require
 		// the use of a schema, so we need to set it here
diff --git a/includes/installer/PostgresUpdater.php b/includes/installer/PostgresUpdater.php
index 2db236fa7add..4fe6e1e7b235 100644
--- a/includes/installer/PostgresUpdater.php
+++ b/includes/installer/PostgresUpdater.php
@@ -616,6 +616,11 @@ class PostgresUpdater extends DatabaseUpdater {
 			[ 'changeNullableField', 'recentchanges', 'rc_ip', 'NOT NULL', true ],
 			[ 'renameIndex', 'recentchanges', 'new_name_timestamp', 'rc_new_name_timestamp', false,
 				'patch-recentchanges-rc_new_name_timestamp.sql' ],
+			[ 'changeField', 'archive', 'ar_namespace', 'INTEGER', '' ],
+			[ 'setDefault', 'archive', 'ar_namespace', 0 ],
+			[ 'setDefault', 'archive', 'ar_title', '' ],
+			[ 'changeField', 'archive', 'ar_comment_id', 'BIGINT', '' ],
+			[ 'changeField', 'archive', 'ar_actor', 'BIGINT', '' ],
 		];
 	}
 
diff --git a/includes/installer/SqliteInstaller.php b/includes/installer/SqliteInstaller.php
index f35373ad82ae..fd9dd9b47eca 100644
--- a/includes/installer/SqliteInstaller.php
+++ b/includes/installer/SqliteInstaller.php
@@ -342,6 +342,11 @@ EOT;
 		return $this->setupSearchIndex( $status );
 	}
 
+	public function createManualTables() {
+		// Already handled above. Do nothing.
+		return Status::newGood();
+	}
+
 	/**
 	 * @param Status &$status
 	 * @return Status
diff --git a/includes/installer/SqliteUpdater.php b/includes/installer/SqliteUpdater.php
index a37f12ec8939..a92ffbded941 100644
--- a/includes/installer/SqliteUpdater.php
+++ b/includes/installer/SqliteUpdater.php
@@ -213,6 +213,7 @@ class SqliteUpdater extends DatabaseUpdater {
 			[ 'modifyField', 'recentchanges', 'rc_title', 'patch-recentchanges-rc_title-varbinary.sql' ],
 			[ 'renameIndex', 'recentchanges', 'new_name_timestamp', 'rc_new_name_timestamp', false,
 				'patch-recentchanges-rc_new_name_timestamp.sql' ],
+			[ 'modifyField', 'archive', 'ar_title', 'patch-archive-ar_title-varbinary.sql' ],
 		];
 	}
 
diff --git a/maintenance/archives/patch-archive-ar_title-varbinary.sql b/maintenance/archives/patch-archive-ar_title-varbinary.sql
new file mode 100644
index 000000000000..531c0d1c970d
--- /dev/null
+++ b/maintenance/archives/patch-archive-ar_title-varbinary.sql
@@ -0,0 +1,2 @@
+ALTER TABLE /*_*/archive
+    MODIFY ar_title VARBINARY(255) DEFAULT '' NOT NULL;
diff --git a/maintenance/postgres/tables-generated.sql b/maintenance/postgres/tables-generated.sql
index 2ab9579cd7a9..7e50668f2940 100644
--- a/maintenance/postgres/tables-generated.sql
+++ b/maintenance/postgres/tables-generated.sql
@@ -844,3 +844,29 @@ CREATE INDEX rc_name_type_patrolled_timestamp ON recentchanges (
 );
 
 CREATE INDEX rc_this_oldid ON recentchanges (rc_this_oldid);
+
+
+CREATE TABLE archive (
+  ar_id SERIAL NOT NULL,
+  ar_namespace INT DEFAULT 0 NOT NULL,
+  ar_title TEXT DEFAULT '' NOT NULL,
+  ar_comment_id BIGINT NOT NULL,
+  ar_actor BIGINT NOT NULL,
+  ar_timestamp TIMESTAMPTZ NOT NULL,
+  ar_minor_edit SMALLINT DEFAULT 0 NOT NULL,
+  ar_rev_id INT NOT NULL,
+  ar_deleted SMALLINT DEFAULT 0 NOT NULL,
+  ar_len INT DEFAULT NULL,
+  ar_page_id INT DEFAULT NULL,
+  ar_parent_id INT DEFAULT NULL,
+  ar_sha1 TEXT DEFAULT '' NOT NULL,
+  PRIMARY KEY(ar_id)
+);
+
+CREATE INDEX ar_name_title_timestamp ON archive (
+  ar_namespace, ar_title, ar_timestamp
+);
+
+CREATE INDEX ar_actor_timestamp ON archive (ar_actor, ar_timestamp);
+
+CREATE UNIQUE INDEX ar_revid_uniq ON archive (ar_rev_id);
diff --git a/maintenance/postgres/tables.sql b/maintenance/postgres/tables.sql
index f2128ba61bed..9bfa026a35aa 100644
--- a/maintenance/postgres/tables.sql
+++ b/maintenance/postgres/tables.sql
@@ -94,28 +94,6 @@ CREATE INDEX rev_actor_timestamp    ON revision (rev_actor,rev_timestamp,rev_id)
 CREATE INDEX rev_page_actor_timestamp ON revision (rev_page,rev_actor,rev_timestamp);
 
 
-CREATE SEQUENCE archive_ar_id_seq;
-CREATE TABLE archive (
-  ar_id             INTEGER      NOT NULL  PRIMARY KEY DEFAULT nextval('archive_ar_id_seq'),
-  ar_namespace      SMALLINT     NOT NULL,
-  ar_title          TEXT         NOT NULL,
-  ar_page_id        INTEGER          NULL,
-  ar_parent_id      INTEGER          NULL,
-  ar_sha1           TEXT         NOT NULL DEFAULT '',
-  ar_comment_id     INTEGER      NOT NULL,
-  ar_actor          INTEGER      NOT NULL,
-  ar_timestamp      TIMESTAMPTZ  NOT NULL,
-  ar_minor_edit     SMALLINT     NOT NULL  DEFAULT 0,
-  ar_rev_id         INTEGER      NOT NULL,
-  ar_deleted        SMALLINT     NOT NULL  DEFAULT 0,
-  ar_len            INTEGER          NULL
-);
-ALTER SEQUENCE archive_ar_id_seq OWNED BY archive.ar_id;
-CREATE INDEX ar_name_title_timestamp ON archive (ar_namespace,ar_title,ar_timestamp);
-CREATE INDEX ar_actor_timestamp                ON archive (ar_actor,ar_timestamp);
-CREATE UNIQUE INDEX ar_revid_uniq ON archive (ar_rev_id);
-
-
 -- Tsearch2 2 stuff. Will fail if we don't have proper access to the tsearch2 tables
 -- Make sure you also change patch-tsearch2funcs.sql if the funcs below change.
 ALTER TABLE page ADD titlevector tsvector;
diff --git a/maintenance/sqlite/archives/patch-archive-ar_title-varbinary.sql b/maintenance/sqlite/archives/patch-archive-ar_title-varbinary.sql
new file mode 100644
index 000000000000..a0e4208066dc
--- /dev/null
+++ b/maintenance/sqlite/archives/patch-archive-ar_title-varbinary.sql
@@ -0,0 +1,29 @@
+CREATE TABLE archive_tmp (
+  ar_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  ar_namespace INTEGER DEFAULT 0 NOT NULL,
+  ar_title BLOB DEFAULT '' NOT NULL, ar_comment_id BIGINT UNSIGNED NOT NULL,
+  ar_actor BIGINT UNSIGNED NOT NULL,
+  ar_timestamp BLOB NOT NULL, ar_minor_edit SMALLINT DEFAULT 0 NOT NULL,
+  ar_rev_id INTEGER UNSIGNED NOT NULL,
+  ar_deleted SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
+  ar_len INTEGER UNSIGNED DEFAULT NULL,
+  ar_page_id INTEGER UNSIGNED DEFAULT NULL,
+  ar_parent_id INTEGER UNSIGNED DEFAULT NULL,
+  ar_sha1 BLOB DEFAULT '' NOT NULL
+);
+INSERT INTO /*_*/archive_tmp (
+  ar_id, ar_namespace, ar_title, ar_comment_id, ar_actor, ar_timestamp, ar_minor_edit, ar_rev_id,
+  ar_deleted, ar_len, ar_page_id, ar_parent_id, ar_sha1)
+SELECT ar_id, ar_namespace, ar_title, ar_comment_id, ar_actor, ar_timestamp, ar_minor_edit, ar_rev_id, ar_deleted,
+       ar_len, ar_page_id, ar_parent_id, ar_sha1
+FROM /*_*/archive;
+DROP TABLE /*_*/archive;
+ALTER TABLE /*_*/archive_tmp RENAME TO /*_*/archive;
+
+CREATE INDEX ar_name_title_timestamp ON /*_*/archive (
+  ar_namespace, ar_title, ar_timestamp
+);
+
+CREATE INDEX ar_actor_timestamp ON /*_*/archive (ar_actor, ar_timestamp);
+
+CREATE UNIQUE INDEX ar_revid_uniq ON /*_*/archive (ar_rev_id);
diff --git a/maintenance/sqlite/tables-generated.sql b/maintenance/sqlite/tables-generated.sql
index 0ac8e6245098..7b0edfd23f93 100644
--- a/maintenance/sqlite/tables-generated.sql
+++ b/maintenance/sqlite/tables-generated.sql
@@ -789,3 +789,26 @@ CREATE INDEX rc_name_type_patrolled_timestamp ON /*_*/recentchanges (
 );
 
 CREATE INDEX rc_this_oldid ON /*_*/recentchanges (rc_this_oldid);
+
+
+CREATE TABLE /*_*/archive (
+  ar_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  ar_namespace INTEGER DEFAULT 0 NOT NULL,
+  ar_title BLOB DEFAULT '' NOT NULL, ar_comment_id BIGINT UNSIGNED NOT NULL,
+  ar_actor BIGINT UNSIGNED NOT NULL,
+  ar_timestamp BLOB NOT NULL, ar_minor_edit SMALLINT DEFAULT 0 NOT NULL,
+  ar_rev_id INTEGER UNSIGNED NOT NULL,
+  ar_deleted SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
+  ar_len INTEGER UNSIGNED DEFAULT NULL,
+  ar_page_id INTEGER UNSIGNED DEFAULT NULL,
+  ar_parent_id INTEGER UNSIGNED DEFAULT NULL,
+  ar_sha1 BLOB DEFAULT '' NOT NULL
+);
+
+CREATE INDEX ar_name_title_timestamp ON /*_*/archive (
+  ar_namespace, ar_title, ar_timestamp
+);
+
+CREATE INDEX ar_actor_timestamp ON /*_*/archive (ar_actor, ar_timestamp);
+
+CREATE UNIQUE INDEX ar_revid_uniq ON /*_*/archive (ar_rev_id);
diff --git a/maintenance/tables-generated.sql b/maintenance/tables-generated.sql
index c715f5b22476..b84897550af4 100644
--- a/maintenance/tables-generated.sql
+++ b/maintenance/tables-generated.sql
@@ -786,3 +786,26 @@ CREATE TABLE /*_*/recentchanges (
   INDEX rc_this_oldid (rc_this_oldid),
   PRIMARY KEY(rc_id)
 ) /*$wgDBTableOptions*/;
+
+
+CREATE TABLE /*_*/archive (
+  ar_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
+  ar_namespace INT DEFAULT 0 NOT NULL,
+  ar_title VARBINARY(255) DEFAULT '' NOT NULL,
+  ar_comment_id BIGINT UNSIGNED NOT NULL,
+  ar_actor BIGINT UNSIGNED NOT NULL,
+  ar_timestamp BINARY(14) NOT NULL,
+  ar_minor_edit TINYINT DEFAULT 0 NOT NULL,
+  ar_rev_id INT UNSIGNED NOT NULL,
+  ar_deleted TINYINT UNSIGNED DEFAULT 0 NOT NULL,
+  ar_len INT UNSIGNED DEFAULT NULL,
+  ar_page_id INT UNSIGNED DEFAULT NULL,
+  ar_parent_id INT UNSIGNED DEFAULT NULL,
+  ar_sha1 VARBINARY(32) DEFAULT '' NOT NULL,
+  INDEX ar_name_title_timestamp (
+    ar_namespace, ar_title, ar_timestamp
+  ),
+  INDEX ar_actor_timestamp (ar_actor, ar_timestamp),
+  UNIQUE INDEX ar_revid_uniq (ar_rev_id),
+  PRIMARY KEY(ar_id)
+) /*$wgDBTableOptions*/;
diff --git a/maintenance/tables.json b/maintenance/tables.json
index 802376655ad8..e0697c5fef0a 100644
--- a/maintenance/tables.json
+++ b/maintenance/tables.json
@@ -3152,5 +3152,165 @@
 		"pk": [
 			"rc_id"
 		]
+	},
+	{
+		"name": "archive",
+		"comment": "Archive area for deleted pages and their revisions. These may be viewed (and restored) by admins through the Special:Undelete interface.",
+		"columns": [
+			{
+				"name": "ar_id",
+				"comment": "Primary key",
+				"type": "integer",
+				"options": {
+					"autoincrement": true,
+					"unsigned": true,
+					"notnull": true
+				}
+			},
+			{
+				"name": "ar_namespace",
+				"comment": "Copied from page_namespace",
+				"type": "integer",
+				"options": {
+					"notnull": true,
+					"default": 0
+				}
+			},
+			{
+				"name": "ar_title",
+				"comment": "Copied from page_title",
+				"type": "binary",
+				"options": {
+					"notnull": true,
+					"default": "",
+					"length": 255
+				}
+			},
+			{
+				"name": "ar_comment_id",
+				"comment": "Basic revision stuff.",
+				"type": "bigint",
+				"options": {
+					"unsigned": true,
+					"notnull": true
+				}
+			},
+			{
+				"name": "ar_actor",
+				"comment": "Basic revision stuff.",
+				"type": "bigint",
+				"options": {
+					"unsigned": true,
+					"notnull": true
+				}
+			},
+			{
+				"name": "ar_timestamp",
+				"comment": "Basic revision stuff.",
+				"type": "mwtimestamp",
+				"options": {
+					"notnull": true,
+					"length": 14
+				}
+			},
+			{
+				"name": "ar_minor_edit",
+				"comment": "Basic revision stuff.",
+				"type": "mwtinyint",
+				"options": {
+					"notnull": true,
+					"default": 0
+				}
+			},
+			{
+				"name": "ar_rev_id",
+				"comment": "Copied from rev_id. @since 1.5 Entries from 1.4 will be NULL here. When restoring archive rows from before 1.5, a new rev_id is created.",
+				"type": "integer",
+				"options": {
+					"unsigned": true,
+					"notnull": true
+				}
+			},
+			{
+				"name": "ar_deleted",
+				"comment": "Copied from rev_deleted. Although this may be raised during deletion. Users with the \"suppressrevision\" right may \"archive\" and \"suppress\" content in a single action. @since 1.10",
+				"type": "mwtinyint",
+				"options": {
+					"unsigned": true,
+					"notnull": true,
+					"default": 0
+				}
+			},
+			{
+				"name": "ar_len",
+				"comment": "Copied from rev_len, length of this revision in bytes. @since 1.10",
+				"type": "integer",
+				"options": {
+					"unsigned": true,
+					"notnull": false
+				}
+			},
+			{
+				"name": "ar_page_id",
+				"comment": "Copied from page_id. Restoration will attempt to use this as page ID if no current page with the same name exists. Otherwise, the revisions will be restored under the current page. Can be used for manual undeletion by developers if multiple pages by the same name were archived. @since 1.11 Older entries will have NULL.",
+				"type": "integer",
+				"options": {
+					"unsigned": true,
+					"notnull": false
+				}
+			},
+			{
+				"name": "ar_parent_id",
+				"comment": "Copied from rev_parent_id. @since 1.13",
+				"type": "integer",
+				"options": {
+					"unsigned": true,
+					"notnull": false,
+					"default": null
+				}
+			},
+			{
+				"name": "ar_sha1",
+				"comment": "Copied from rev_sha1, SHA-1 text content hash in base-36 @since 1.19",
+				"type": "binary",
+				"options": {
+					"notnull": true,
+					"default": "",
+					"length": 32
+				}
+			}
+		],
+		"indexes": [
+			{
+				"name": "ar_name_title_timestamp",
+				"comment": "Index for Special:Undelete to page through deleted revisions",
+				"columns": [
+					"ar_namespace",
+					"ar_title",
+					"ar_timestamp"
+				],
+				"unique": false
+			},
+			{
+				"name": "ar_actor_timestamp",
+				"comment": "Index for Special:DeletedContributions",
+				"columns": [
+					"ar_actor",
+					"ar_timestamp"
+				],
+				"unique": false
+			},
+			{
+				"name": "ar_revid_uniq",
+				"comment": "Index for linking archive rows with tables that normally link with revision rows, such as change_tag.",
+				"columns": [
+					"ar_rev_id"
+				],
+				"unique": true
+			}
+		],
+		"pk": [
+			"ar_id"
+		]
 	}
 ]
diff --git a/maintenance/tables.sql b/maintenance/tables.sql
index b8a4406719f4..d92745fc10db 100644
--- a/maintenance/tables.sql
+++ b/maintenance/tables.sql
@@ -274,68 +274,6 @@ CREATE INDEX /*i*/rev_actor_timestamp ON /*_*/revision (rev_actor,rev_timestamp,
 -- in ApiQueryContributors. Also for ApiQueryRevisions if rvuser is specified.
 CREATE INDEX /*i*/rev_page_actor_timestamp ON /*_*/revision (rev_page,rev_actor,rev_timestamp);
 
---
--- Archive area for deleted pages and their revisions.
--- These may be viewed (and restored) by admins through the Special:Undelete interface.
---
-CREATE TABLE /*_*/archive (
-  -- Primary key
-  ar_id int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
-
-  -- Copied from page_namespace
-  ar_namespace int NOT NULL default 0,
-  -- Copied from page_title
-  ar_title varchar(255) binary NOT NULL default '',
-
-  -- Basic revision stuff...
-  ar_comment_id bigint unsigned NOT NULL,
-  ar_actor bigint unsigned NOT NULL,
-  ar_timestamp binary(14) NOT NULL default '',
-  ar_minor_edit tinyint NOT NULL default 0,
-
-  -- Copied from rev_id.
-  --
-  -- @since 1.5 Entries from 1.4 will be NULL here. When restoring
-  -- archive rows from before 1.5, a new rev_id is created.
-  ar_rev_id int unsigned NOT NULL,
-
-  -- Copied from rev_deleted. Although this may be raised during deletion.
-  -- Users with the "suppressrevision" right may "archive" and "suppress"
-  -- content in a single action.
-  -- @since 1.10
-  ar_deleted tinyint unsigned NOT NULL default 0,
-
-  -- Copied from rev_len, length of this revision in bytes.
-  -- @since 1.10
-  ar_len int unsigned,
-
-  -- Copied from page_id. Restoration will attempt to use this as page ID if
-  -- no current page with the same name exists. Otherwise, the revisions will
-  -- be restored under the current page. Can be used for manual undeletion by
-  -- developers if multiple pages by the same name were archived.
-  --
-  -- @since 1.11 Older entries will have NULL.
-  ar_page_id int unsigned,
-
-  -- Copied from rev_parent_id.
-  -- @since 1.13
-  ar_parent_id int unsigned default NULL,
-
-  -- Copied from rev_sha1, SHA-1 text content hash in base-36
-  -- @since 1.19
-  ar_sha1 varbinary(32) NOT NULL default ''
-) /*$wgDBTableOptions*/;
-
--- Index for Special:Undelete to page through deleted revisions
-CREATE INDEX /*i*/ar_name_title_timestamp ON /*_*/archive (ar_namespace,ar_title,ar_timestamp);
-
--- Index for Special:DeletedContributions
-CREATE INDEX /*i*/ar_actor_timestamp ON /*_*/archive (ar_actor,ar_timestamp);
-
--- Index for linking archive rows with tables that normally link with revision
--- rows, such as change_tag.
-CREATE UNIQUE INDEX /*i*/ar_revid_uniq ON /*_*/archive (ar_rev_id);
-
 
 --
 -- When using the default MySQL search backend, page titles