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

1 files changed, 29 insertions, 0 deletions

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);