schema: Add cl_target_id and cl_collation_id to categorylinks

To normalize cl_to and cl_collation

Bug: T299951
Change-Id: If58190f3cc912ffa99c623dc9f51d530d5c87186

11 files changed, 343 insertions, 0 deletions

diff --git a/sql/abstractSchemaChanges/patch-categorylinks-target_id.json b/sql/abstractSchemaChanges/patch-categorylinks-target_id.json
new file mode 100644
index 000000000000..967f137d0546
--- /dev/null
+++ b/sql/abstractSchemaChanges/patch-categorylinks-target_id.json
@@ -0,0 +1,155 @@
+{
+	"comment": "Add cl_target_id on categorylinks (T299951)",
+	"before": {
+		"name": "categorylinks",
+		"comment": "Track category inclusions *used inline* This tracks a single level of category membership",
+		"columns": [
+			{
+				"name": "cl_from",
+				"comment": "Key to page_id of the page defined as a category member.",
+				"type": "integer",
+				"options": { "unsigned": true, "notnull": true, "default": 0 }
+			},
+			{
+				"name": "cl_to",
+				"comment": "Name of the category. This is also the page_title of the category's description page; all such pages are in namespace 14 (NS_CATEGORY).",
+				"type": "binary",
+				"options": { "notnull": true, "default": "", "length": 255 }
+			},
+			{
+				"name": "cl_sortkey",
+				"comment": "A binary string obtained by applying a sortkey generation algorithm (Collation::getSortKey()) to page_title, or cl_sortkey_prefix . \"\\n\" page_title if cl_sortkey_prefix is nonempty.",
+				"type": "binary",
+				"options": { "notnull": true, "default": "", "length": 230 }
+			},
+			{
+				"name": "cl_sortkey_prefix",
+				"comment": "A prefix for the raw sortkey manually specified by the user, either via [[Category:Foo|prefix]] or {{defaultsort:prefix}}.  If nonempty, it's concatenated with a line break followed by the page title before the sortkey conversion algorithm is run.  We store this so that we can update collations without reparsing all pages. Note: If you change the length of this field, you also need to change code in LinksUpdate.php. See T27254.",
+				"type": "binary",
+				"options": { "notnull": true, "default": "", "length": 255 }
+			},
+			{
+				"name": "cl_timestamp",
+				"comment": "This isn't really used at present. Provided for an optional sorting method by approximate addition time.",
+				"type": "datetimetz",
+				"options": { "notnull": true, "PlatformOptions": { "version": true } }
+			},
+			{
+				"name": "cl_collation",
+				"comment": "Stores $wgCategoryCollation at the time cl_sortkey was generated.  This can be used to install new collation versions, tracking which rows are not yet updated.  '' means no collation, this is a legacy row that needs to be updated by updateCollation.php.  In the future, it might be possible to specify different collations per category.",
+				"type": "binary",
+				"options": { "notnull": true, "default": "", "length": 32 }
+			},
+			{
+				"name": "cl_type",
+				"comment": "Stores whether cl_from is a category, file, or other page, so we can paginate the three categories separately.  This only has to be updated when moving pages into or out of the category namespace, since file pages cannot be moved to other namespaces, nor can non-files be moved into the file namespace.",
+				"type": "mwenum",
+				"options": { "notnull": true, "default": "page",
+					"CustomSchemaOptions": {
+						"enum_values": [ "page", "subcat", "file" ]
+					}
+				}
+			}
+		],
+		"indexes": [
+			{
+				"name": "cl_sortkey",
+				"comment": "We always sort within a given category, and within a given type.  FIXME: Formerly this index didn't cover cl_type (since that didn't exist), so old callers won't be using an index: fix this?",
+				"columns": [ "cl_to", "cl_type", "cl_sortkey", "cl_from" ],
+				"unique": false
+			},
+			{
+				"name": "cl_timestamp",
+				"comment": "Used by the API (and some extensions)",
+				"columns": [ "cl_to", "cl_timestamp" ],
+				"unique": false
+			}
+		],
+		"pk": [ "cl_from", "cl_to" ]
+	},
+	"after": {
+		"name": "categorylinks",
+		"comment": "Track category inclusions *used inline* This tracks a single level of category membership",
+		"columns": [
+			{
+				"name": "cl_from",
+				"comment": "Key to page_id of the page defined as a category member.",
+				"type": "integer",
+				"options": { "unsigned": true, "notnull": true, "default": 0 }
+			},
+			{
+				"name": "cl_to",
+				"comment": "Name of the category. This is also the page_title of the category's description page; all such pages are in namespace 14 (NS_CATEGORY).",
+				"type": "binary",
+				"options": { "notnull": true, "default": "", "length": 255 }
+			},
+			{
+				"name": "cl_sortkey",
+				"comment": "A binary string obtained by applying a sortkey generation algorithm (Collation::getSortKey()) to page_title, or cl_sortkey_prefix . \"\\n\" page_title if cl_sortkey_prefix is nonempty.",
+				"type": "binary",
+				"options": { "notnull": true, "default": "", "length": 230 }
+			},
+			{
+				"name": "cl_sortkey_prefix",
+				"comment": "A prefix for the raw sortkey manually specified by the user, either via [[Category:Foo|prefix]] or {{defaultsort:prefix}}.  If nonempty, it's concatenated with a line break followed by the page title before the sortkey conversion algorithm is run.  We store this so that we can update collations without reparsing all pages. Note: If you change the length of this field, you also need to change code in LinksUpdate.php. See T27254.",
+				"type": "binary",
+				"options": { "notnull": true, "default": "", "length": 255 }
+			},
+			{
+				"name": "cl_timestamp",
+				"comment": "This isn't really used at present. Provided for an optional sorting method by approximate addition time.",
+				"type": "datetimetz",
+				"options": { "notnull": true, "PlatformOptions": { "version": true } }
+			},
+			{
+				"name": "cl_collation",
+				"comment": "Stores $wgCategoryCollation at the time cl_sortkey was generated.  This can be used to install new collation versions, tracking which rows are not yet updated.  '' means no collation, this is a legacy row that needs to be updated by updateCollation.php.  In the future, it might be possible to specify different collations per category.",
+				"type": "binary",
+				"options": { "notnull": true, "default": "", "length": 32 }
+			},
+			{
+				"name": "cl_type",
+				"comment": "Stores whether cl_from is a category, file, or other page, so we can paginate the three categories separately.  This only has to be updated when moving pages into or out of the category namespace, since file pages cannot be moved to other namespaces, nor can non-files be moved into the file namespace.",
+				"type": "mwenum",
+				"options": { "notnull": true, "default": "page",
+					"CustomSchemaOptions": {
+						"enum_values": [ "page", "subcat", "file" ]
+					}
+				}
+			},
+			{
+				"name": "cl_collation_id",
+				"comment": "FK to collation_id",
+				"type": "smallint",
+				"options": { "notnull": true, "unsigned": true, "default": 0 }
+			},
+			{
+				"name": "cl_target_id",
+				"type": "bigint",
+				"comment": "Foreign key to linktarget.lt_id",
+				"options": { "notnull": false, "unsigned": true }
+			}
+		],
+		"indexes": [
+			{
+				"name": "cl_sortkey",
+				"comment": "We always sort within a given category, and within a given type.  FIXME: Formerly this index didn't cover cl_type (since that didn't exist), so old callers won't be using an index: fix this?",
+				"columns": [ "cl_to", "cl_type", "cl_sortkey", "cl_from" ],
+				"unique": false
+			},
+			{
+				"name": "cl_timestamp",
+				"comment": "Used by the API (and some extensions)",
+				"columns": [ "cl_to", "cl_timestamp" ],
+				"unique": false
+			},
+			{
+				"name": "cl_sortkey_id",
+				"comment": "We always sort within a given category, and within a given type.  FIXME: Formerly this index didn't cover cl_type (since that didn't exist), so old callers won't be using an index: fix this?",
+				"columns": [ "cl_target_id", "cl_type", "cl_sortkey", "cl_from" ],
+				"unique": false
+			}
+		],
+		"pk": [ "cl_from", "cl_to" ]
+	}
+}
diff --git a/sql/mysql/patch-categorylinks-target_id.sql b/sql/mysql/patch-categorylinks-target_id.sql
new file mode 100644
index 000000000000..81dcd1a9149b
--- /dev/null
+++ b/sql/mysql/patch-categorylinks-target_id.sql
@@ -0,0 +1,12 @@
+-- This file is automatically generated using maintenance/generateSchemaChangeSql.php.
+-- Source: sql/abstractSchemaChanges/patch-categorylinks-target_id.json
+-- Do not modify this file directly.
+-- See https://www.mediawiki.org/wiki/Manual:Schema_changes
+ALTER TABLE /*_*/categorylinks
+  ADD cl_collation_id SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
+  ADD cl_target_id BIGINT UNSIGNED DEFAULT NULL;
+
+CREATE INDEX cl_sortkey_id ON /*_*/categorylinks (
+    cl_target_id, cl_type, cl_sortkey,
+    cl_from
+  );
diff --git a/sql/mysql/patch-collation.sql b/sql/mysql/patch-collation.sql
new file mode 100644
index 000000000000..a9d3e7bdb64a
--- /dev/null
+++ b/sql/mysql/patch-collation.sql
@@ -0,0 +1,6 @@
+CREATE TABLE /*_*/collation (
+  collation_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
+  collation_name VARBINARY(64) NOT NULL,
+  UNIQUE INDEX collation_name (collation_name),
+  PRIMARY KEY(collation_id)
+) /*$wgDBTableOptions*/;
diff --git a/sql/mysql/tables-generated.sql b/sql/mysql/tables-generated.sql
index 7696eb81f384..0bf782500aa6 100644
--- a/sql/mysql/tables-generated.sql
+++ b/sql/mysql/tables-generated.sql
@@ -458,10 +458,16 @@ CREATE TABLE /*_*/categorylinks (
   cl_timestamp TIMESTAMP NOT NULL,
   cl_collation VARBINARY(32) DEFAULT '' NOT NULL,
   cl_type ENUM('page', 'subcat', 'file') DEFAULT 'page' NOT NULL,
+  cl_collation_id SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
+  cl_target_id BIGINT UNSIGNED DEFAULT NULL,
   INDEX cl_sortkey (
     cl_to, cl_type, cl_sortkey, cl_from
   ),
   INDEX cl_timestamp (cl_to, cl_timestamp),
+  INDEX cl_sortkey_id (
+    cl_target_id, cl_type, cl_sortkey,
+    cl_from
+  ),
   PRIMARY KEY(cl_from, cl_to)
 ) /*$wgDBTableOptions*/;
 
@@ -923,3 +929,11 @@ CREATE TABLE /*_*/filetypes (
   ),
   PRIMARY KEY(ft_id)
 ) /*$wgDBTableOptions*/;
+
+
+CREATE TABLE /*_*/collation (
+  collation_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
+  collation_name VARBINARY(64) NOT NULL,
+  UNIQUE INDEX collation_name (collation_name),
+  PRIMARY KEY(collation_id)
+) /*$wgDBTableOptions*/;
diff --git a/sql/postgres/patch-categorylinks-target_id.sql b/sql/postgres/patch-categorylinks-target_id.sql
new file mode 100644
index 000000000000..53cfb4a92290
--- /dev/null
+++ b/sql/postgres/patch-categorylinks-target_id.sql
@@ -0,0 +1,13 @@
+-- This file is automatically generated using maintenance/generateSchemaChangeSql.php.
+-- Source: sql/abstractSchemaChanges/patch-categorylinks-target_id.json
+-- Do not modify this file directly.
+-- See https://www.mediawiki.org/wiki/Manual:Schema_changes
+ALTER TABLE categorylinks
+  ADD cl_collation_id SMALLINT DEFAULT 0 NOT NULL;
+ALTER TABLE categorylinks
+  ADD cl_target_id BIGINT DEFAULT NULL;
+
+CREATE INDEX cl_sortkey_id ON categorylinks (
+  cl_target_id, cl_type, cl_sortkey,
+  cl_from
+);
diff --git a/sql/postgres/patch-collation.sql b/sql/postgres/patch-collation.sql
new file mode 100644
index 000000000000..a1bde56fe25c
--- /dev/null
+++ b/sql/postgres/patch-collation.sql
@@ -0,0 +1,7 @@
+CREATE TABLE "collation" (
+  collation_id SMALLSERIAL NOT NULL,
+  collation_name TEXT NOT NULL,
+  PRIMARY KEY(collation_id)
+);
+
+CREATE UNIQUE INDEX collation_name ON "collation" (collation_name);
diff --git a/sql/postgres/tables-generated.sql b/sql/postgres/tables-generated.sql
index 45c563cd6d8c..47d2fafab16a 100644
--- a/sql/postgres/tables-generated.sql
+++ b/sql/postgres/tables-generated.sql
@@ -509,6 +509,8 @@ CREATE TABLE categorylinks (
   cl_timestamp TIMESTAMPTZ NOT NULL,
   cl_collation TEXT DEFAULT '' NOT NULL,
   cl_type TEXT DEFAULT 'page' NOT NULL,
+  cl_collation_id SMALLINT DEFAULT 0 NOT NULL,
+  cl_target_id BIGINT DEFAULT NULL,
   PRIMARY KEY(cl_from, cl_to)
 );
 
@@ -518,6 +520,11 @@ CREATE INDEX cl_sortkey ON categorylinks (
 
 CREATE INDEX cl_timestamp ON categorylinks (cl_to, cl_timestamp);
 
+CREATE INDEX cl_sortkey_id ON categorylinks (
+  cl_target_id, cl_type, cl_sortkey,
+  cl_from
+);
+
 
 CREATE TABLE logging (
   log_id SERIAL NOT NULL,
@@ -993,3 +1000,12 @@ CREATE TABLE filetypes (
 CREATE UNIQUE INDEX ft_media_mime ON filetypes (
   ft_media_type, ft_major_mime, ft_minor_mime
 );
+
+
+CREATE TABLE "collation" (
+  collation_id SMALLSERIAL NOT NULL,
+  collation_name TEXT NOT NULL,
+  PRIMARY KEY(collation_id)
+);
+
+CREATE UNIQUE INDEX collation_name ON "collation" (collation_name);
diff --git a/sql/sqlite/patch-categorylinks-target_id.sql b/sql/sqlite/patch-categorylinks-target_id.sql
new file mode 100644
index 000000000000..4dce6be527f8
--- /dev/null
+++ b/sql/sqlite/patch-categorylinks-target_id.sql
@@ -0,0 +1,55 @@
+-- This file is automatically generated using maintenance/generateSchemaChangeSql.php.
+-- Source: sql/abstractSchemaChanges/patch-categorylinks-target_id.json
+-- Do not modify this file directly.
+-- See https://www.mediawiki.org/wiki/Manual:Schema_changes
+CREATE TEMPORARY TABLE /*_*/__temp__categorylinks AS
+SELECT
+  cl_from,
+  cl_to,
+  cl_sortkey,
+  cl_sortkey_prefix,
+  cl_timestamp,
+  cl_collation,
+  cl_type
+FROM /*_*/categorylinks;
+DROP TABLE /*_*/categorylinks;
+
+
+CREATE TABLE /*_*/categorylinks (
+    cl_from INTEGER UNSIGNED DEFAULT 0 NOT NULL,
+    cl_to BLOB DEFAULT '' NOT NULL,
+    cl_sortkey BLOB DEFAULT '' NOT NULL,
+    cl_sortkey_prefix BLOB DEFAULT '' NOT NULL,
+    cl_timestamp DATETIME NOT NULL,
+    cl_collation BLOB DEFAULT '' NOT NULL,
+    cl_type TEXT DEFAULT 'page' NOT NULL,
+    cl_collation_id SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
+    cl_target_id BIGINT UNSIGNED DEFAULT NULL,
+    PRIMARY KEY(cl_from, cl_to)
+  );
+INSERT INTO /*_*/categorylinks (
+    cl_from, cl_to, cl_sortkey, cl_sortkey_prefix,
+    cl_timestamp, cl_collation, cl_type
+  )
+SELECT
+  cl_from,
+  cl_to,
+  cl_sortkey,
+  cl_sortkey_prefix,
+  cl_timestamp,
+  cl_collation,
+  cl_type
+FROM
+  /*_*/__temp__categorylinks;
+DROP TABLE /*_*/__temp__categorylinks;
+
+CREATE INDEX cl_sortkey ON /*_*/categorylinks (
+    cl_to, cl_type, cl_sortkey, cl_from
+  );
+
+CREATE INDEX cl_timestamp ON /*_*/categorylinks (cl_to, cl_timestamp);
+
+CREATE INDEX cl_sortkey_id ON /*_*/categorylinks (
+    cl_target_id, cl_type, cl_sortkey,
+    cl_from
+  );
diff --git a/sql/sqlite/patch-collation.sql b/sql/sqlite/patch-collation.sql
new file mode 100644
index 000000000000..a6d5554e6e56
--- /dev/null
+++ b/sql/sqlite/patch-collation.sql
@@ -0,0 +1,7 @@
+
+CREATE TABLE /*_*/collation (
+  collation_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  collation_name BLOB NOT NULL
+);
+
+CREATE UNIQUE INDEX collation_name ON /*_*/collation (collation_name);
diff --git a/sql/sqlite/tables-generated.sql b/sql/sqlite/tables-generated.sql
index c558b59fd381..2a27dc4fd171 100644
--- a/sql/sqlite/tables-generated.sql
+++ b/sql/sqlite/tables-generated.sql
@@ -484,6 +484,8 @@ CREATE TABLE /*_*/categorylinks (
   cl_timestamp DATETIME NOT NULL,
   cl_collation BLOB DEFAULT '' NOT NULL,
   cl_type TEXT DEFAULT 'page' NOT NULL,
+  cl_collation_id SMALLINT UNSIGNED DEFAULT 0 NOT NULL,
+  cl_target_id BIGINT UNSIGNED DEFAULT NULL,
   PRIMARY KEY(cl_from, cl_to)
 );
 
@@ -493,6 +495,11 @@ CREATE INDEX cl_sortkey ON /*_*/categorylinks (
 
 CREATE INDEX cl_timestamp ON /*_*/categorylinks (cl_to, cl_timestamp);
 
+CREATE INDEX cl_sortkey_id ON /*_*/categorylinks (
+  cl_target_id, cl_type, cl_sortkey,
+  cl_from
+);
+
 
 CREATE TABLE /*_*/logging (
   log_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
@@ -916,3 +923,11 @@ CREATE TABLE /*_*/filetypes (
 CREATE UNIQUE INDEX ft_media_mime ON /*_*/filetypes (
   ft_media_type, ft_major_mime, ft_minor_mime
 );
+
+
+CREATE TABLE /*_*/collation (
+  collation_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+  collation_name BLOB NOT NULL
+);
+
+CREATE UNIQUE INDEX collation_name ON /*_*/collation (collation_name);
diff --git a/sql/tables.json b/sql/tables.json
index a557a287da17..6fffd0bccc03 100644
--- a/sql/tables.json
+++ b/sql/tables.json
@@ -1529,6 +1529,18 @@
 						"enum_values": [ "page", "subcat", "file" ]
 					}
 				}
+			},
+			{
+				"name": "cl_collation_id",
+				"comment": "FK to collation_id",
+				"type": "smallint",
+				"options": { "notnull": true, "unsigned": true, "default": 0 }
+			},
+			{
+				"name": "cl_target_id",
+				"type": "bigint",
+				"comment": "Foreign key to linktarget.lt_id",
+				"options": { "notnull": false, "unsigned": true }
 			}
 		],
 		"indexes": [
@@ -1543,6 +1555,12 @@
 				"comment": "Used by the API (and some extensions)",
 				"columns": [ "cl_to", "cl_timestamp" ],
 				"unique": false
+			},
+			{
+				"name": "cl_sortkey_id",
+				"comment": "We always sort within a given category, and within a given type.  FIXME: Formerly this index didn't cover cl_type (since that didn't exist), so old callers won't be using an index: fix this?",
+				"columns": [ "cl_target_id", "cl_type", "cl_sortkey", "cl_from" ],
+				"unique": false
 			}
 		],
 		"pk": [ "cl_from", "cl_to" ]
@@ -3977,5 +3995,30 @@
 			}
 		],
 		"pk": [ "ft_id" ]
+	},
+	{
+		"name": "collation",
+		"comment": "Normalization table for collation names",
+		"columns": [
+			{
+				"name": "collation_id",
+				"type": "smallint",
+				"options": { "notnull": true, "unsigned": true, "autoincrement": true }
+			},
+			{
+				"name": "collation_name",
+				"type": "binary",
+				"options": { "notnull": true, "length": 64 }
+			}
+		],
+		"indexes": [
+			{
+				"name": "collation_name",
+				"columns": [ "collation_name" ],
+				"comment": "Index for looking up the internal ID of a collation",
+				"unique": true
+			}
+		],
+		"pk": [ "collation_id" ]
 	}
 ]