* Add support for querying MySQL 4 for collations

3 files changed, 60 insertions, 0 deletions

diff --git a/Source/SPDatabaseData.m b/Source/SPDatabaseData.m
index 202f56ce..835c7328 100644
--- a/Source/SPDatabaseData.m
+++ b/Source/SPDatabaseData.m
@@ -39,8 +39,10 @@
 @interface SPDatabaseData ()
 
 - (NSArray *)_getDatabaseDataForQuery:(NSString *)query;
++ (NSArray *)_relabelCollationResult:(NSArray *)data;
 
 NSInteger _sortMySQL4CharsetEntry(NSDictionary *itemOne, NSDictionary *itemTwo, void *context);
+NSInteger _sortMySQL4CollationEntry(NSDictionary *itemOne, NSDictionary *itemTwo, void *context);
 NSInteger _sortStorageEngineEntry(NSDictionary *itemOne, NSDictionary *itemTwo, void *context);
 
 @end
@@ -100,6 +102,14 @@ NSInteger _sortStorageEngineEntry(NSDictionary *itemOne, NSDictionary *itemTwo,
 		if ([serverSupport supportsInformationSchema]) {
 			[collations addObjectsFromArray:[self _getDatabaseDataForQuery:@"SELECT * FROM `information_schema`.`collations` ORDER BY `collation_name` ASC"]];	
 		}
+		else if([serverSupport supportsShowCollation]) {
+			//use the 4.1-style query
+			NSArray *supportedCollations = [self _getDatabaseDataForQuery:@"SHOW COLLATION"];
+			//apply the sorting
+			supportedCollations = [supportedCollations sortedArrayUsingFunction:_sortMySQL4CollationEntry context:nil];
+			//convert the output to the information_schema style
+			[collations addObjectsFromArray:[SPDatabaseData _relabelCollationResult:supportedCollations]];
+		}
 		
 		// If that failed, get the list of collations from the hard-coded list
 		if (![collations count]) {
@@ -137,6 +147,14 @@ NSInteger _sortStorageEngineEntry(NSDictionary *itemOne, NSDictionary *itemTwo,
 		if ([serverSupport supportsInformationSchema]) {
 			[characterSetCollations addObjectsFromArray:[self _getDatabaseDataForQuery:[NSString stringWithFormat:@"SELECT * FROM `information_schema`.`collations` WHERE character_set_name = '%@' ORDER BY `collation_name` ASC", characterSetEncoding]]];
 		}
+		else if([serverSupport supportsShowCollation]) {
+			//use the 4.1-style query (as every collation name starts with the charset name we can use the prefix search)
+			NSArray *supportedCollations = [self _getDatabaseDataForQuery:[NSString stringWithFormat:@"SHOW COLLATION LIKE '%@%%'",characterSetEncoding]];
+			//apply the sorting
+			supportedCollations = [supportedCollations sortedArrayUsingFunction:_sortMySQL4CollationEntry context:nil];
+			
+			[characterSetCollations addObjectsFromArray:[SPDatabaseData _relabelCollationResult:supportedCollations]];
+		}
 
 		// If that failed, get the list of collations matching the supplied encoding from the hard-coded list
 		if (![characterSetCollations count]) {
@@ -386,6 +404,29 @@ NSInteger _sortStorageEngineEntry(NSDictionary *itemOne, NSDictionary *itemTwo,
 }
 
 /**
+ * Converts the output of a MySQL 4.1 style "SHOW COLLATION" to the format of a MySQL 5.0 style "SELECT * FROM information_schema.collations"
+ */
++ (NSArray *)_relabelCollationResult:(NSArray *)data
+{
+	NSMutableArray *outData = [[NSMutableArray alloc] initWithCapacity:[data count]];
+	
+	for (NSDictionary *aCollation in data)
+	{
+		NSDictionary *convertedCollation = [NSDictionary dictionaryWithObjectsAndKeys:
+											[aCollation objectForKey:@"Collation"], @"COLLATION_NAME",
+											[aCollation objectForKey:@"Charset"],   @"CHARACTER_SET_NAME",
+											[aCollation objectForKey:@"Id"],        @"ID",
+											[aCollation objectForKey:@"Default"],   @"IS_DEFAULT",
+											[aCollation objectForKey:@"Compiled"],  @"IS_COMPILED",
+											[aCollation objectForKey:@"Sortlen"],   @"SORTLEN",
+											nil];
+		
+		[outData addObject:convertedCollation];
+	}
+	return [outData autorelease];
+}
+
+/**
  * Sorts a 4.1-style SHOW CHARACTER SET result by the Charset key.
  */
 NSInteger _sortMySQL4CharsetEntry(NSDictionary *itemOne, NSDictionary *itemTwo, void *context)
@@ -394,6 +435,14 @@ NSInteger _sortMySQL4CharsetEntry(NSDictionary *itemOne, NSDictionary *itemTwo,
 }
 
 /**
+ * Sorts a 4.1-style SHOW COLLATION result by the Collation key.
+ */
+NSInteger _sortMySQL4CollationEntry(NSDictionary *itemOne, NSDictionary *itemTwo, void *context)
+{
+	return [[itemOne objectForKey:@"Collation"] compare:[itemTwo objectForKey:@"Collation"]];
+}
+
+/**
  * Sorts a storage engine array by the Engine key.
  */
 NSInteger _sortStorageEngineEntry(NSDictionary *itemOne, NSDictionary *itemTwo, void *context)
diff --git a/Source/SPServerSupport.h b/Source/SPServerSupport.h
index 93d0b591..2f562177 100644
--- a/Source/SPServerSupport.h
+++ b/Source/SPServerSupport.h
@@ -61,6 +61,7 @@
 	
 	// Encoding
 	BOOL supportsShowCharacterSet;
+    BOOL supportsShowCollation;
 	BOOL supportsCharacterSetDatabaseVar;
 	BOOL supportsPost41CharacterSetHandling;
 	
@@ -144,6 +145,11 @@
 @property (readonly) BOOL supportsShowCharacterSet;
 
 /**
+ * @property supportsShowCollation Indicates if the server supports the SHOW COLLATION statement
+ */
+@property (readonly) BOOL supportsShowCollation;
+
+/**
  * @property supportsCharacterSetDatabaseVar Indicates if the server supports the 'character_set_database'
  *                                           variable.
  */
diff --git a/Source/SPServerSupport.m b/Source/SPServerSupport.m
index c2a139eb..b32a9dc2 100644
--- a/Source/SPServerSupport.m
+++ b/Source/SPServerSupport.m
@@ -55,6 +55,7 @@
 @synthesize supportsInformationSchema;
 @synthesize supportsSpatialExtensions;
 @synthesize supportsShowCharacterSet;
+@synthesize supportsShowCollation;
 @synthesize supportsCharacterSetDatabaseVar;
 @synthesize supportsPost41CharacterSetHandling;
 @synthesize supportsCreateUser;
@@ -137,6 +138,9 @@
 	
 	// The SHOW CHARACTER SET statement wasn't added until MySQL 4.1.0
 	supportsShowCharacterSet = [self isEqualToOrGreaterThanMajorVersion:4 minor:1 release:0];
+
+	// The SHOW COLLATION statement wasn't added until MySQL 4.1.0
+	supportsShowCollation = [self isEqualToOrGreaterThanMajorVersion:4 minor:1 release:0];
 	
 	// The variable 'character_set_database' wasn't added until MySQL 4.1.1
 	supportsCharacterSetDatabaseVar = [self isEqualToOrGreaterThanMajorVersion:4 minor:1 release:1];
@@ -258,6 +262,7 @@
 	supportsInformationSchema               = NO;
 	supportsSpatialExtensions               = NO;
 	supportsShowCharacterSet                = NO;
+	supportsShowCollation                   = NO;
 	supportsCharacterSetDatabaseVar         = NO;
 	supportsPost41CharacterSetHandling      = NO;
 	supportsCreateUser                      = NO;