From 2a81ccd4c2047179d572019bb7c1158481c7810d Mon Sep 17 00:00:00 2001 From: stuconnolly Date: Wed, 4 Jul 2012 09:22:37 +0000 Subject: If available display the columns default encoding and collation in the structure view. Implements issue #1355. --- Source/SPDatabaseData.h | 2 + Source/SPDatabaseData.m | 24 ++++++++++++ Source/SPTableStructure.m | 95 ++++++++++++++++++++++++++++++++++------------- 3 files changed, 95 insertions(+), 26 deletions(-) (limited to 'Source') diff --git a/Source/SPDatabaseData.h b/Source/SPDatabaseData.h index 23f1711a..6b42f1e9 100644 --- a/Source/SPDatabaseData.h +++ b/Source/SPDatabaseData.h @@ -37,6 +37,7 @@ { NSString *characterSetEncoding; NSString *defaultCharacterSetEncoding; + NSString *defaultCollation; NSString *defaultStorageEngine; NSMutableArray *collations; @@ -67,6 +68,7 @@ - (NSArray *)getDatabaseCharacterSetEncodings; - (NSString *)getDatabaseDefaultCharacterSet; +- (NSString *)getDatabaseDefaultCollation; - (NSString *)getDatabaseDefaultStorageEngine; @end diff --git a/Source/SPDatabaseData.m b/Source/SPDatabaseData.m index 88f07e52..64a95c80 100644 --- a/Source/SPDatabaseData.m +++ b/Source/SPDatabaseData.m @@ -52,6 +52,7 @@ NSInteger _sortStorageEngineEntry(NSDictionary *itemOne, NSDictionary *itemTwo, { if ((self = [super init])) { characterSetEncoding = nil; + defaultCollation = nil; defaultCharacterSetEncoding = nil; collations = [[NSMutableArray alloc] init]; @@ -305,6 +306,26 @@ NSInteger _sortStorageEngineEntry(NSDictionary *itemOne, NSDictionary *itemTwo, return defaultCharacterSetEncoding; } +/** + * Returns the database's default collation. + * + * @return The default collation as a string + */ +- (NSString *)getDatabaseDefaultCollation +{ + if (!defaultCollation) { + [defaultCollation release]; + + SPMySQLResult *result = [connection queryString:@"SHOW VARIABLES LIKE 'collation_database'"]; + + [result setReturnDataAsStrings:YES]; + + defaultCollation = [[[result getRowAsDictionary] objectForKey:@"Value"] retain]; + } + + return defaultCollation; +} + /** * Returns the database's default storage engine. * @@ -330,6 +351,7 @@ NSInteger _sortStorageEngineEntry(NSDictionary *itemOne, NSDictionary *itemTwo, // Retrieve the corresponding value for the determined key, ensuring return as a string SPMySQLResult *result = [connection queryString:[NSString stringWithFormat:@"SHOW VARIABLES LIKE %@", [storageEngineKey tickQuotedString]]];; + [result setReturnDataAsStrings:YES]; defaultStorageEngine = [[[result getRowAsDictionary] objectForKey:@"Value"] retain]; @@ -352,6 +374,7 @@ NSInteger _sortStorageEngineEntry(NSDictionary *itemOne, NSDictionary *itemTwo, if ([connection queryErrored]) return [NSArray array]; [result setReturnDataAsStrings:YES]; + return [result getAllRows]; } @@ -381,6 +404,7 @@ NSInteger _sortStorageEngineEntry(NSDictionary *itemOne, NSDictionary *itemTwo, { if (characterSetEncoding) [characterSetEncoding release], characterSetEncoding = nil; if (defaultCharacterSetEncoding) [defaultCharacterSetEncoding release], defaultCharacterSetEncoding = nil; + if (defaultCollation) [defaultCollation release], defaultCollation = nil; [collations release], collations = nil; [characterSetCollations release], characterSetCollations = nil; diff --git a/Source/SPTableStructure.m b/Source/SPTableStructure.m index e443710c..ba8c5eb9 100644 --- a/Source/SPTableStructure.m +++ b/Source/SPTableStructure.m @@ -231,12 +231,10 @@ */ - (void)loadTable:(NSString *)aTable { - NSArray *theTableIndexes; NSMutableDictionary *theTableEnumLists = [NSMutableDictionary dictionary]; - SPMySQLResult *indexResult; // Check whether a save of the current row is required. - if ( ![[self onMainThread] saveRowOnDeselect] ) return; + if (![[self onMainThread] saveRowOnDeselect]) return; // If no table is selected, reset the interface and return if (!aTable || ![aTable length]) { @@ -247,11 +245,13 @@ NSMutableArray *theTableFields = [[NSMutableArray alloc] init]; // Make a mutable copy out of the cached [tableDataInstance columns] since we're adding infos - for (id col in [tableDataInstance columns]) + for (id col in [tableDataInstance columns]) + { [theTableFields addObject:[[col mutableCopy] autorelease]]; + } // Retrieve the indexes for the table - indexResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW INDEX FROM %@", [aTable backtickQuotedString]]]; + SPMySQLResult *indexResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW INDEX FROM %@", [aTable backtickQuotedString]]]; // If an error occurred, reset the interface and abort if ([mySQLConnection queryErrored]) { @@ -269,21 +269,26 @@ } // Process the indexes into a local array of dictionaries - theTableIndexes = [self convertIndexResultToArray:indexResult]; + NSArray *theTableIndexes = [self convertIndexResultToArray:indexResult]; // Set the Key column for (NSDictionary* theIndex in theTableIndexes) { for (id field in theTableFields) { - if([[field objectForKey:@"name"] isEqualToString:[theIndex objectForKey:@"Column_name"]]) { - if([[theIndex objectForKey:@"Key_name"] isEqualToString:@"PRIMARY"]) + if ([[field objectForKey:@"name"] isEqualToString:[theIndex objectForKey:@"Column_name"]]) { + if ([[theIndex objectForKey:@"Key_name"] isEqualToString:@"PRIMARY"]) { [field setObject:@"PRI" forKey:@"Key"]; - else - if([[field objectForKey:@"typegrouping"] isEqualToString:@"geometry"]) - [field setObject:@"SPA" forKey:@"Key"]; - else + } + else { + if ([[field objectForKey:@"typegrouping"] isEqualToString:@"geometry"]) { + [field setObject:@"SPA" forKey:@"Key"]; + } + else { [field setObject:(([[theIndex objectForKey:@"Non_unique"] isEqualToString:@"1"]) ? @"MUL" : @"UNI") forKey:@"Key"]; + } + } + break; } } @@ -291,13 +296,18 @@ // Set up the encoding PopUpButtonCell NSArray *encodings = [databaseDataInstance getDatabaseCharacterSetEncodings]; + if ([encodings count]) { // Populate encoding popup button NSMutableArray *encodingTitles = [[NSMutableArray alloc] initWithCapacity:[encodings count]+1]; + [encodingTitles addObject:@""]; + for (NSDictionary *encoding in encodings) + { [encodingTitles addObject:(![encoding objectForKey:@"DESCRIPTION"]) ? [encoding objectForKey:@"CHARACTER_SET_NAME"] : [NSString stringWithFormat:@"%@ (%@)", [encoding objectForKey:@"DESCRIPTION"], [encoding objectForKey:@"CHARACTER_SET_NAME"]]]; + } [[encodingPopupCell onMainThread] removeAllItems]; [[encodingPopupCell onMainThread] addItemsWithTitles:encodingTitles]; @@ -309,40 +319,72 @@ } // Process all the fields to normalise keys and add additional information - for (id theField in theTableFields) { - + for (id theField in theTableFields) + { // Select and re-map encoding and collation since [self dataSource] stores the choice as NSNumbers NSString *fieldEncoding = @""; NSInteger selectedIndex = 0; - if([theField objectForKey:@"encoding"]) { - for(id enc in encodings) { - if([[enc objectForKey:@"CHARACTER_SET_NAME"] isEqualToString:[theField objectForKey:@"encoding"]]) { - fieldEncoding = [theField objectForKey:@"encoding"]; + + NSString *type = [[[theField objectForKey:@"type"] stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]] uppercaseString]; + + NSString *collation = nil; + NSString *encoding = nil; + + if ([fieldValidation isFieldTypeString:type] || ![type hasSuffix:@"BINARY"] || ![type hasSuffix:@"BLOB"]) { + + collation = [theField objectForKey:@"collation"] ? [theField objectForKey:@"collation"] : [[tableDataInstance statusValues] objectForKey:@"collation"]; + encoding = [theField objectForKey:@"encoding"] ? [theField objectForKey:@"encoding"] : [tableDataInstance tableEncoding]; + + // If we still don't have a collation then fallback on the database default (not available on MySQL < 4.1.1). + if (!collation) { + collation = [databaseDataInstance getDatabaseDefaultCollation]; + } + } + + if (encoding) { + for (id enc in encodings) + { + if ([[enc objectForKey:@"CHARACTER_SET_NAME"] isEqualToString:encoding]) { + fieldEncoding = encoding; break; } + selectedIndex++; } - selectedIndex++; // due to leading @"" in popup list + + // Due to leading @"" in popup list + selectedIndex++; } + [theField setObject:[NSNumber numberWithInteger:selectedIndex] forKey:@"encoding"]; + selectedIndex = 0; - if([fieldEncoding length] && [theField objectForKey:@"collation"]) { + + if (encoding && collation) { + NSArray *theCollations = [databaseDataInstance getDatabaseCollationsForEncoding:fieldEncoding]; - for(id col in theCollations) { - if([[col objectForKey:@"COLLATION_NAME"] isEqualToString:[theField objectForKey:@"collation"]]) { + + for (id col in theCollations) + { + if ([[col objectForKey:@"COLLATION_NAME"] isEqualToString:collation]) { + // Set BINARY if collation ends with _bin for convenience - if([[col objectForKey:@"COLLATION_NAME"] hasSuffix:@"_bin"]) + if ([[col objectForKey:@"COLLATION_NAME"] hasSuffix:@"_bin"]) { [theField setObject:[NSNumber numberWithInt:1] forKey:@"binary"]; + } + break; } + selectedIndex++; } - selectedIndex++; // due to leading @"" in popup list + + // Due to leading @"" in popup list + selectedIndex++; } + [theField setObject:[NSNumber numberWithInteger:selectedIndex] forKey:@"collation"]; - NSString *type = [[theField objectForKey:@"type"] uppercaseString]; - // Get possible values if the field is an enum or a set if (([type isEqualToString:@"ENUM"] || [type isEqualToString:@"SET"]) && [theField objectForKey:@"values"]) { [theTableEnumLists setObject:[NSArray arrayWithArray:[theField objectForKey:@"values"]] forKey:[theField objectForKey:@"name"]]; @@ -378,6 +420,7 @@ theTableIndexes, @"tableIndexes", theTableEnumLists, @"enumLists", nil]; + [[self onMainThread] setTableDetails:tableDetails]; isCurrentExtraAutoIncrement = [tableDataInstance tableHasAutoIncrementField]; -- cgit v1.2.3