From 4bfddfe4bffa4b23ef290a50b9830936eb475632 Mon Sep 17 00:00:00 2001
From: Roan Kattouw <catrope@users.mediawiki.org>
Date: Sun, 13 Mar 2011 10:35:06 +0000
Subject: [PATCH] Schema change: change cl_type from ENUM('page', 'subcat',
 'file') to varchar(6). This is needed because MySQL sorts 'page' < 'subcat' <
 'file' when using ORDER BY cl_type but uses 'file' < 'page' < 'subcat' for
 the purposes of WHERE clauses, making paging impossible. Changing the ENUM()
 order to be alphabetical would fix the order discrepancy, but leave range
 scans like WHERE cl_type > 'page' unindexed. Varchars do behave correctly.
 Changing to an int was not an option because existing data would have to be
 migrated.

This commit does not include a patch for SQLite, because ALTER TABLE MODIFY is apparently not supported by SQLite as far as I could tell by Googling. Leaving resolution of this issue for SQLite to the SQLite experts; maybe SQLite's enum implementation is saner than MySQL and it doesn't even need this schema change, I don't know.
---
 includes/installer/MysqlUpdater.php    | 15 +++++++++++++++
 maintenance/archives/patch-cl_type.sql |  6 ++++++
 maintenance/tables.sql                 |  4 +++-
 3 files changed, 24 insertions(+), 1 deletion(-)
 create mode 100644 maintenance/archives/patch-cl_type.sql

diff --git a/includes/installer/MysqlUpdater.php b/includes/installer/MysqlUpdater.php
index c3186f21c8..db9f9c5944 100644
--- a/includes/installer/MysqlUpdater.php
+++ b/includes/installer/MysqlUpdater.php
@@ -175,6 +175,7 @@ class MysqlUpdater extends DatabaseUpdater {
 			array( 'dropIndex', 'archive', 'ar_page_revid',         'patch-archive_kill_ar_page_revid.sql' ),
 			array( 'addIndex', 'archive', 'ar_revid',               'patch-archive_ar_revid.sql' ),
 			array( 'doLangLinksLengthUpdate' ),
+			array( 'doClTypeVarcharUpdate' ),
 		);
 	}
 
@@ -828,4 +829,18 @@ class MysqlUpdater extends DatabaseUpdater {
 			$this->output( "...ll_lang is up-to-date.\n" );
 		}
 	}
+	
+	protected function doClTypeVarcharUpdate() {
+		$categorylinks = $this->db->tableName( 'categorylinks' );
+		$res = $this->db->query( "SHOW COLUMNS FROM $categorylinks LIKE 'cl_type'" );
+		$row = $this->db->fetchObject( $res );
+		
+		if ( $row && substr( $row->Type, 0, 4 ) == 'enum' ) {
+			$this->output( 'Changing cl_type from enum to varchar...' );
+			$this->applyPatch( 'patch-cl_type.sql' );
+			$this->output( "done.\n" );
+		} else {
+			$this->output( "...cl_type is up-to-date.\n" );
+		}
+	}
 }
diff --git a/maintenance/archives/patch-cl_type.sql b/maintenance/archives/patch-cl_type.sql
new file mode 100644
index 0000000000..0eefa025fb
--- /dev/null
+++ b/maintenance/archives/patch-cl_type.sql
@@ -0,0 +1,6 @@
+--
+-- Change cl_type to a varchar from an enum because of the weird semantics of
+-- the < and > operators when working with enums
+--
+
+ALTER TABLE categorylinks MODIFY cl_type varchar(6) NOT NULL default 'page';
diff --git a/maintenance/tables.sql b/maintenance/tables.sql
index 11392f1855..99b70e4169 100644
--- a/maintenance/tables.sql
+++ b/maintenance/tables.sql
@@ -521,7 +521,9 @@ CREATE TABLE /*_*/categorylinks (
   -- paginate the three categories separately.  This never has to be updated
   -- after the page is created, since none of these page types can be moved to
   -- any other.
-  cl_type ENUM('page', 'subcat', 'file') NOT NULL default 'page'
+  -- This used to be ENUM('page', 'subcat', 'file') but was changed to a
+  -- varchar because of the weird semantics of < and > when used on enums
+  cl_type varchar(6) NOT NULL default 'page'
 ) /*$wgDBTableOptions*/;
 
 CREATE UNIQUE INDEX /*i*/cl_from ON /*_*/categorylinks (cl_from,cl_to);
-- 
2.20.1