Change the internal handling of charset/collation from offset-based to name-based in structure view

This is a major change to the charset/collation code. Please watch out for issues!
(part of #2237)

3 files changed, 161 insertions, 193 deletions

diff --git a/Source/SPTableStructure.m b/Source/SPTableStructure.m
index 9973bf4b..319e74ec 100644
--- a/Source/SPTableStructure.m
+++ b/Source/SPTableStructure.m
@@ -46,6 +46,7 @@
 #import "SPTableStructureLoading.h"
 #import "SPThreadAdditions.h"
 #import "SPServerSupport.h"
+#import "SPExtendedTableInfo.h"
 
 #import <SPMySQL/SPMySQL.h>
 
@@ -252,8 +253,8 @@ static NSString *SPRemoveFieldAndForeignKey = @"SPRemoveFieldAndForeignKey";
 	BOOL allowNull = [[[tableDataInstance statusValueForKey:@"Engine"] uppercaseString] isEqualToString:@"CSV"] ? NO : [prefs boolForKey:SPNewFieldsAllowNulls];
 	
 	[tableFields insertObject:[NSMutableDictionary
-							   dictionaryWithObjects:[NSArray arrayWithObjects:@"", @"INT", @"", @"0", @"0", @"0", allowNull ? @"1" : @"0", @"", [prefs stringForKey:SPNullValue], @"None", @"", @0, @0, nil]
-							   forKeys:@[@"name", @"type", @"length", @"unsigned", @"zerofill", @"binary", @"null", @"Key", @"default", @"Extra", @"comment", @"encoding", @"collation"]]
+							   dictionaryWithObjects:[NSArray arrayWithObjects:@"", @"INT", @"", @"0", @"0", @"0", allowNull ? @"1" : @"0", @"", [prefs stringForKey:SPNullValue], @"None", @"", nil]
+							   forKeys:@[@"name", @"type", @"length", @"unsigned", @"zerofill", @"binary", @"null", @"Key", @"default", @"Extra", @"comment"]]
 					  atIndex:insertIndex];
 #else
 	[tableFields insertObject:[NSMutableDictionary
@@ -810,30 +811,25 @@ static NSString *SPRemoveFieldAndForeignKey = @"SPRemoveFieldAndForeignKey";
 
 
 		if ([fieldValidation isFieldTypeString:theRowType]) {
+			BOOL charsetSupport = [[tableDocumentInstance serverSupport] supportsPost41CharacterSetHandling];
+
 			// Add CHARSET
-			NSString *fieldEncoding = @"";
-			if([[theRow objectForKey:@"encoding"] integerValue] > 0 && [[tableDocumentInstance serverSupport] supportsPost41CharacterSetHandling]) {
-				NSString *enc = [[encodingPopupCell itemAtIndex:[[theRow objectForKey:@"encoding"] integerValue]] title];
-				NSInteger start = [enc rangeOfString:@"("].location+1;
-				NSInteger end = [enc length] - start - 1;
-				fieldEncoding = [enc substringWithRange:NSMakeRange(start, end)];
+			NSString *fieldEncoding = [theRow objectForKey:@"encodingName"];
+			if(charsetSupport && [fieldEncoding length]) {
 				[queryString appendFormat:@"\n CHARACTER SET %@", fieldEncoding];
 			}
-			// Remember CHARSET for COLLATE
-			if(![fieldEncoding length] && [tableDataInstance tableEncoding]) {
-				fieldEncoding = [tableDataInstance tableEncoding];
-			}
-
-			// ADD COLLATE
-			if([fieldEncoding length] && [[theRow objectForKey:@"collation"] integerValue] > 0 && ![[theRow objectForKey:@"binary"] integerValue]) {
-				NSArray *theCollations = [databaseDataInstance getDatabaseCollationsForEncoding:fieldEncoding];
-				NSString *col = [[theCollations objectAtIndex:[[theRow objectForKey:@"collation"] integerValue]-1] objectForKey:@"COLLATION_NAME"];
-				[queryString appendFormat:@"\n COLLATE %@", col];
-			}
 
-			if ( [[theRow objectForKey:@"binary"] integerValue] == 1) {
+			if ([[theRow objectForKey:@"binary"] integerValue] == 1) {
 				[queryString appendString:@"\n BINARY"];
 			}
+			else {
+				// ADD COLLATE
+				// Note: a collate without charset is valid in MySQL. The charset can be determined from a collation.
+				NSString *fieldCollation = [theRow objectForKey:@"collationName"];
+				if(charsetSupport && [fieldCollation length]) {
+					[queryString appendFormat:@"\n COLLATE %@", fieldCollation];
+				}
+			}
 
 		}
 		else if ([fieldValidation isFieldTypeNumeric:theRowType] && (![theRowType isEqualToString:@"BIT"])) {
diff --git a/Source/SPTableStructureDelegate.m b/Source/SPTableStructureDelegate.m
index b8db3bff..4bfd84c8 100644
--- a/Source/SPTableStructureDelegate.m
+++ b/Source/SPTableStructureDelegate.m
@@ -37,6 +37,7 @@
 #import "SPTableFieldValidation.h"
 #import "SPTableStructureLoading.h"
 #import "SPServerSupport.h"
+#import "SPTablesList.h"
 
 #import <SPMySQL/SPMySQL.h>
 
@@ -60,72 +61,68 @@
 {
 	// Return a placeholder if the table is reloading
 	if ((NSUInteger)rowIndex >= [tableFields count]) return @"...";
+
+	NSDictionary *rowData = NSArrayObjectAtIndex(tableFields, rowIndex);
 	
 	if ([[tableColumn identifier] isEqualToString:@"collation"]) {
 		NSString *tableEncoding = [tableDataInstance tableEncoding];
-		NSString *columnEncoding = nil;
-		NSInteger idx = 0;
-		
-		if ((idx = [[NSArrayObjectAtIndex(tableFields, rowIndex) objectForKey:@"encoding"] integerValue]) > 0 && idx < [encodingPopupCell numberOfItems]) {
-			NSString *enc = [[encodingPopupCell itemAtIndex:idx] title];
+		NSString *columnEncoding = [rowData objectForKey:@"encodingName"];
+		NSString *columnCollation = [rowData objectForKey:@"collationName"]; // loadTable: has already inferred it, if not set explicit
 
-			NSUInteger start = [enc rangeOfString:@"("].location + 1;
-			
-			columnEncoding = [enc substringWithRange:NSMakeRange(start, [enc length] - start - 1)];
-			collations = [databaseDataInstance getDatabaseCollationsForEncoding:columnEncoding];
-		} 
-		else {
-			// If the structure has loaded (not still loading!) and the table encoding
-			// is set, use the appropriate collations.
-			collations = @[];
-			if([tableDocumentInstance structureLoaded]) {
-				columnEncoding = [tableDataInstance tableEncoding];
-				if(columnEncoding) collations = [databaseDataInstance getDatabaseCollationsForEncoding:columnEncoding];
-			}
-		}
-		
 		NSPopUpButtonCell *collationCell = [tableColumn dataCell];
-		
 		[collationCell removeAllItems];
-		
-		if ([collations count] > 0) {
-			NSString *tableCollation = [[tableDataInstance statusValues] objectForKey:@"Collation"];
-			
-			if (![tableCollation length]) {
-				tableCollation = [databaseDataInstance getDefaultCollationForEncoding:tableEncoding];
-			}
-			
-			NSString *columnCollation = [NSArrayObjectAtIndex(tableFields, rowIndex) objectForKey:@"collationName"];
-			
-			if (![columnCollation length]) {
-				columnCollation = [databaseDataInstance getDefaultCollationForEncoding:columnEncoding];
-			}
-			
-			[[tableColumn dataCell] addItemWithTitle:@""];
+		[collationCell addItemWithTitle:@"dummy"];
+		//copy the default style of menu items and add gray color for default item
+		NSMutableDictionary *menuAttrs = [NSMutableDictionary dictionaryWithDictionary:[[collationCell attributedTitle] attributesAtIndex:0 effectiveRange:NULL]];
+		[menuAttrs setObject:[NSColor lightGrayColor] forKey:NSForegroundColorAttributeName];
+		[[collationCell lastItem] setTitle:@""];
+
+		//if this is not set the column either has no encoding (numeric etc.) or retrieval failed. Either way we can't provide collations
+		if(columnEncoding) {
+			collations = [databaseDataInstance getDatabaseCollationsForEncoding:columnEncoding];
 
-			BOOL useMonospacedFont = [prefs boolForKey:SPUseMonospacedFonts];
-			CGFloat monospacedFontSize = [prefs floatForKey:SPMonospacedFontSize] > 0 ? [prefs floatForKey:SPMonospacedFontSize] : [NSFont smallSystemFontSize];
-			NSMutableDictionary *menuAttributes = [NSMutableDictionary dictionaryWithObject:[NSColor lightGrayColor] forKey:NSForegroundColorAttributeName];
-			[menuAttributes setObject:useMonospacedFont ? [NSFont fontWithName:SPDefaultMonospacedFontName size:monospacedFontSize] : [NSFont systemFontOfSize:[NSFont smallSystemFontSize]] forKey:NSFontAttributeName];
-			
-			BOOL columnUsesTableDefaultEncoding = ([columnEncoding isEqualToString:tableEncoding]);
-			// Populate collation popup button
-			for (NSDictionary *collation in collations)
-			{
-				NSString *collationName = [collation objectForKey:@"COLLATION_NAME"];
-				
-				[[tableColumn dataCell] addItemWithTitle:collationName];
+			if ([collations count] > 0) {
+				NSString *tableCollation = [[tableDataInstance statusValues] objectForKey:@"Collation"];
 
-				// If this matches the table's collation, draw in gray
-				if (columnUsesTableDefaultEncoding && [collationName isEqualToString:tableCollation]) {
-					NSMenuItem *collationMenuItem = [(NSPopUpButtonCell *)[tableColumn dataCell] itemAtIndex:([[tableColumn dataCell] numberOfItems] - 1)];
+				if (![tableCollation length]) {
+					tableCollation = [databaseDataInstance getDefaultCollationForEncoding:tableEncoding];
+				}
 
-					NSAttributedString *itemString = [[[NSAttributedString alloc] initWithString:collationName attributes:menuAttributes] autorelease];
-					
-					[collationMenuItem setAttributedTitle:itemString];
+				BOOL columnUsesTableDefaultEncoding = ([columnEncoding isEqualToString:tableEncoding]);
+				// Populate collation popup button
+				for (NSDictionary *collation in collations)
+				{
+					NSString *collationName = [collation objectForKey:@"COLLATION_NAME"];
+
+					[collationCell addItemWithTitle:collationName];
+					NSMenuItem *item = [collationCell lastItem];
+					[item setRepresentedObject:collationName];
+
+					// If this matches the table's collation, draw in gray
+					if (columnUsesTableDefaultEncoding && [collationName isEqualToString:tableCollation]) {
+						NSAttributedString *itemString = [[NSAttributedString alloc] initWithString:[item title] attributes:menuAttrs];
+						[item setAttributedTitle:[itemString autorelease]];
+					}
 				}
+
+				//look up the right item
+				NSInteger idx = [collationCell indexOfItemWithRepresentedObject:columnCollation];
+				if(idx > 0) return @(idx);
 			}
 		}
+
+		return @0;
+	}
+	else if ([[tableColumn identifier] isEqualToString:@"encoding"]) {
+		// the encoding menu was already configured during setTableDetails:
+		NSString *columnEncoding = [rowData objectForKey:@"encodingName"];
+
+		if(columnEncoding) {
+			NSInteger idx = [encodingPopupCell indexOfItemWithRepresentedObject:columnEncoding];
+			if(idx > 0) return @(idx);
+		}
+
+		return @0;
 	}
 	else if ([[tableColumn identifier] isEqualToString:@"Extra"]) {
 		id dataCell = [tableColumn dataCell];
@@ -141,7 +138,7 @@
 		}
 	}
 
-	return [NSArrayObjectAtIndex(tableFields, rowIndex) objectForKey:[tableColumn identifier]];
+	return [rowData objectForKey:[tableColumn identifier]];
 }
 
 - (void)tableView:(NSTableView *)aTableView setObjectValue:(id)anObject forTableColumn:(NSTableColumn *)aTableColumn row:(NSInteger)rowIndex
@@ -159,16 +156,32 @@
 
 	// Reset collation if encoding was changed
 	if ([[aTableColumn identifier] isEqualToString:@"encoding"]) {
-		if ([[currentRow objectForKey:@"encoding"] integerValue] != [anObject integerValue]) {
-			[currentRow setObject:@0 forKey:@"collation"];
+		NSString *oldEncoding = [currentRow objectForKey:@"encodingName"];
+		NSString *newEncoding = [[encodingPopupCell itemAtIndex:[anObject integerValue]] representedObject];
+		if (![oldEncoding isEqualToString:newEncoding]) {
+			[currentRow removeObjectForKey:@"collationName"];
 			[tableSourceView reloadData];
 		}
+		if(!newEncoding)
+			[currentRow removeObjectForKey:@"encodingName"];
+		else
+			[currentRow setObject:newEncoding forKey:@"encodingName"];
+		return;
+	}
+	else if ([[aTableColumn identifier] isEqualToString:@"collation"]) {
+		NSString *newCollation = [[(NSPopUpButtonCell *)[aTableColumn dataCell] itemAtIndex:[anObject integerValue]] representedObject];
+
+		if(!newCollation)
+			[currentRow removeObjectForKey:@"collationName"];
+		else
+			[currentRow setObject:newCollation forKey:@"collationName"];
+		return;
 	}
 	// Reset collation if BINARY was set changed, as enabling BINARY sets collation to *_bin
 	else if ([[aTableColumn identifier] isEqualToString:@"binary"]) {
 		if ([[currentRow objectForKey:@"binary"] integerValue] != [anObject integerValue]) {
-			[currentRow setObject:@0 forKey:@"collation"];
-			
+			[currentRow removeObjectForKey:@"collationName"];
+
 			[tableSourceView reloadData];
 		}
 	}
@@ -220,9 +233,8 @@
 			[tableSourceView reloadData];
 		}
 	}
-	
 	// Store new value but not if user choose "---" for type and reset values if required
-	if ([[aTableColumn identifier] isEqualToString:@"type"]) {
+	else if ([[aTableColumn identifier] isEqualToString:@"type"]) {
 		if (anObject && [(NSString*)anObject length] && ![(NSString*)anObject hasPrefix:@"--"]) {
 			[currentRow setObject:[(NSString*)anObject uppercaseString] forKey:@"type"];
 			
@@ -238,10 +250,10 @@
 			
 			[tableSourceView reloadData];
 		}
+		return;
 	} 
-	else {
-		[currentRow setObject:(anObject) ? anObject : @"" forKey:[aTableColumn identifier]];
-	}
+
+	[currentRow setObject:(anObject) ? anObject : @"" forKey:[aTableColumn identifier]];
 }
 
 /**
@@ -270,7 +282,7 @@
 	
 	if ([rows count] == 1) {
 		[pboard declareTypes:@[SPDefaultPasteboardDragType] owner:nil];
-		[pboard setString:[[NSNumber numberWithInteger:[rows firstIndex]] stringValue] forType:SPDefaultPasteboardDragType];
+		[pboard setString:[NSString stringWithFormat:@"%lu",[rows firstIndex]] forType:SPDefaultPasteboardDragType];
 		
 		return YES;
 	} 
@@ -332,27 +344,19 @@
 		[queryString appendFormat:@"(%@)", [originalRow objectForKey:@"length"]];
 	}
 	
-	NSString *fieldEncoding = @"";
-			
-	if ([[originalRow objectForKey:@"encoding"] integerValue] > 0 && [[tableDocumentInstance serverSupport] supportsPost41CharacterSetHandling]) {
-		NSString *enc = [[encodingPopupCell itemAtIndex:[[originalRow objectForKey:@"encoding"] integerValue]] title];
-		
-		NSInteger start = [enc rangeOfString:@"("].location + 1;
-		
-		fieldEncoding = [enc substringWithRange:NSMakeRange(start, [enc length] - start - 1)];
-		
+	NSString *fieldEncoding = [originalRow objectForKey:@"encodingName"];
+
+	if ([fieldEncoding length] && [[tableDocumentInstance serverSupport] supportsPost41CharacterSetHandling]) {
 		[queryString appendFormat:@" CHARACTER SET %@", fieldEncoding];
 	}
 	
 	if (![fieldEncoding length] && [tableDataInstance tableEncoding]) {
 		fieldEncoding = [tableDataInstance tableEncoding];
 	}
-	
-	if ([fieldEncoding length] && [[originalRow objectForKey:@"collation"] integerValue] > 0 && ![[originalRow objectForKey:@"binary"] integerValue]) {
-		NSArray *theCollations = [databaseDataInstance getDatabaseCollationsForEncoding:fieldEncoding];
-		NSString *col = [[theCollations objectAtIndex:[[originalRow objectForKey:@"collation"] integerValue] - 1] objectForKey:@"COLLATION_NAME"];
-		
-		[queryString appendFormat:@" COLLATE %@", col];
+
+	NSString *fieldCollation = [originalRow objectForKey:@"collationName"];
+	if ([fieldEncoding length] && [fieldCollation length] && ![[originalRow objectForKey:@"binary"] integerValue]) {
+		[queryString appendFormat:@" COLLATE %@", fieldCollation];
 	}
 
 	// Add unsigned, zerofill, binary, not null if necessary
diff --git a/Source/SPTableStructureLoading.m b/Source/SPTableStructureLoading.m
index 2c6d65fe..85b74a7c 100644
--- a/Source/SPTableStructureLoading.m
+++ b/Source/SPTableStructureLoading.m
@@ -36,6 +36,8 @@
 #import "SPIndexesController.h"
 #import "SPTablesList.h"
 #import "SPThreadAdditions.h"
+#import "SPTableView.h"
+#import "SPFunctions.h"
 
 #import <SPMySQL/SPMySQL.h>
 
@@ -115,65 +117,47 @@
 	
 	// Set up the encoding PopUpButtonCell
 	NSArray *encodings  = [databaseDataInstance getDatabaseCharacterSetEncodings];
-	
-	if ([encodings count]) {
-		NSString *defaultEncodingDescription = nil;
-		
-		// 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"]]];
-			if ([[encoding objectForKey:@"CHARACTER_SET_NAME"] isEqualToString:[tableDataInstance tableEncoding]]) {
-				defaultEncodingDescription = [encodingTitles lastObject];
-			}
-		}
-		
-		[[encodingPopupCell onMainThread] removeAllItems];
-		[[encodingPopupCell onMainThread] addItemsWithTitles:encodingTitles];
 
-		// Take the encoding that matches the table's encoding and gray it out
-		if (defaultEncodingDescription) {
-			NSMenuItem *tableEncodingMenuItem = [[encodingPopupCell menu] itemWithTitle:defaultEncodingDescription];
-			NSMutableParagraphStyle *menuStyle = [[[NSMutableParagraphStyle alloc] init] autorelease];
-			
-			[menuStyle setLineBreakMode:NSLineBreakByTruncatingTail];
-			
-			NSMutableDictionary *menuAttributes = [NSMutableDictionary dictionaryWithObject:[NSColor lightGrayColor] forKey:NSForegroundColorAttributeName];
+	SPMainQSync(^{
+		[encodingPopupCell removeAllItems];
 
-			CGFloat monospacedFontSize = [prefs floatForKey:SPMonospacedFontSize] > 0 ? [prefs floatForKey:SPMonospacedFontSize] : [NSFont smallSystemFontSize];
+		if ([encodings count]) {
 
-			[menuAttributes setObject:[prefs boolForKey:SPUseMonospacedFonts] ? [NSFont fontWithName:SPDefaultMonospacedFontName size:monospacedFontSize] : [NSFont systemFontOfSize:[NSFont smallSystemFontSize]] forKey:NSFontAttributeName];
-			
-			NSAttributedString *itemString = [[[NSAttributedString alloc] initWithString:defaultEncodingDescription attributes:menuAttributes] autorelease];
-			
-			[[tableEncodingMenuItem onMainThread] setAttributedTitle:itemString];
-		}
+			[encodingPopupCell addItemWithTitle:@"dummy"];
+			//copy the default attributes and add gray color
+			NSMutableDictionary *defaultAttrs = [NSMutableDictionary dictionaryWithDictionary:[[encodingPopupCell attributedTitle] attributesAtIndex:0 effectiveRange:NULL]];
+			[defaultAttrs setObject:[NSColor lightGrayColor] forKey:NSForegroundColorAttributeName];
+			[[encodingPopupCell lastItem] setTitle:@""];
 
-		[encodingTitles release];
-	}
-	else {
-		[[encodingPopupCell onMainThread] removeAllItems];
-		[[encodingPopupCell onMainThread] addItemWithTitle:NSLocalizedString(@"Not available", @"not available label")];
-	}
+			for (NSDictionary *encoding in encodings)
+			{
+				NSString *encodingName = [encoding objectForKey:@"CHARACTER_SET_NAME"];
+				NSString *title = (![encoding objectForKey:@"DESCRIPTION"]) ? encodingName : [NSString stringWithFormat:@"%@ (%@)", [encoding objectForKey:@"DESCRIPTION"], encodingName];
+
+				[encodingPopupCell addItemWithTitle:title];
+				NSMenuItem *item = [encodingPopupCell lastItem];
+
+				[item setRepresentedObject:encodingName];
+
+				if ([encodingName isEqualToString:[tableDataInstance tableEncoding]]) {
+
+					NSAttributedString *itemString = [[NSAttributedString alloc] initWithString:[item title] attributes:defaultAttrs];
+
+					[item setAttributedTitle:[itemString autorelease]];
+				}
+			}
+		}
+		else {
+			[encodingPopupCell addItemWithTitle:NSLocalizedString(@"Not available", @"not available label")];
+		}
+	});
 	
 	// Process all the fields to normalise keys and add additional information
 	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;
-		
 		NSString *type = [[[theField objectForKey:@"type"] stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]] uppercaseString];
-		
-		NSString *collation = nil; 
-		NSString *encoding = nil;
-		
+
 		if ([fieldValidation isFieldTypeString:type]) {
-			
 			// The MySQL 4.1 manual says:
 			//
 			// MySQL chooses the column character set and collation in the following manner:
@@ -181,57 +165,41 @@
 			//   2. If CHARACTER SET X was specified without COLLATE, then character set X and its default collation are used.
 			//   3. If COLLATE Y was specified without CHARACTER SET, then the character set associated with Y and collation Y.
 			//   4. Otherwise, the table character set and collation are used.
-#warning This is not correct, see the comment above. \
-         However MySQL ususally outputs the CREATE TABLE statement in a way for this to still get the result right.
-			encoding  = [theField objectForKey:@"encoding"]  ? [theField objectForKey:@"encoding"]  : [tableDataInstance tableEncoding];
-			collation = [theField objectForKey:@"collation"] ? [theField objectForKey:@"collation"] : [databaseDataInstance getDefaultCollationForEncoding:encoding];
-			
-			// MySQL < 4.1 does not support collations (they are part of the charset), it will be nil there
-		}
-
-		if (encoding) {
-			for (id enc in encodings) 
-			{
-				if ([[enc objectForKey:@"CHARACTER_SET_NAME"] isEqualToString:encoding]) {
-					fieldEncoding = encoding;
-
-					// Set the selected index as the match index +1 due to the leading @"" in the popup list
-					[theField setObject:@(selectedIndex + 1) forKey:@"encoding"];
-					[theField setObject:encoding forKey:@"encodingName"];
-					break;
+			NSString *encoding  = [theField objectForKey:@"encoding"];
+			NSString *collation = [theField objectForKey:@"collation"];
+			if(encoding) {
+				if(collation) {
+					// 1
+				}
+				else {
+					collation = [databaseDataInstance getDefaultCollationForEncoding:encoding]; // 2
 				}
-				
-				selectedIndex++;
 			}
-		}
-		
-		selectedIndex = 0;
-		
-		if (encoding && collation) {
-			
-			NSArray *theCollations = [databaseDataInstance getDatabaseCollationsForEncoding:fieldEncoding];
-			
-			for (id col in theCollations) 
-			{
-				if ([[col objectForKey:@"COLLATION_NAME"] isEqualToString:collation]) {
-					
-					// Set the selected index as the match index +1 due to the leading @"" in the popup list
-					[theField setObject:[NSNumber numberWithInteger:(selectedIndex + 1)] forKey:@"collation"];
-					[theField setObject:collation forKey:@"collationName"];
-
-					// Set BINARY if collation ends with _bin for convenience
-					if ([[col objectForKey:@"COLLATION_NAME"] hasSuffix:@"_bin"]) {
-						[theField setObject:@1 forKey:@"binary"];
+			else {
+				if(collation) {
+					encoding = [databaseDataInstance getEncodingFromCollation:collation]; // 3
+				}
+				else {
+					encoding = [tableDataInstance tableEncoding]; //4
+					collation = [tableDataInstance statusValueForKey:@"Collation"];
+					if(!collation) {
+						// should not happen, as the TABLE STATUS output always(?) includes the collation
+						collation = [databaseDataInstance getDefaultCollationForEncoding:encoding];
 					}
-					
-					break;
 				}
-				
-				selectedIndex++;
+			}
+
+			// MySQL < 4.1 does not support collations (they are part of the charset), it will be nil there
+
+			[theField setObject:encoding forKey:@"encodingName"];
+			[theField setObject:collation forKey:@"collationName"];
+
+			// Set BINARY if collation ends with _bin for convenience
+			if ([collation hasSuffix:@"_bin"]) {
+				[theField setObject:@1 forKey:@"binary"];
 			}
 		}
 		
-		
 		// 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"]];