6 files changed, 462 insertions, 256 deletions

diff --git a/Source/SPCharsetCollationHelper.h b/Source/SPCharsetCollationHelper.h
new file mode 100644
index 00000000..5739fdb5
--- /dev/null
+++ b/Source/SPCharsetCollationHelper.h
@@ -0,0 +1,109 @@
+//
+//  $Id$
+//
+//  SPCharsetCollationHelper.h
+//  sequel-pro
+//
+//  Created by Max Lohrmann on March 20, 2013.
+//  Copyright (c) 2013 Max Lohrmann. All rights reserved.
+//
+//  Permission is hereby granted, free of charge, to any person
+//  obtaining a copy of this software and associated documentation
+//  files (the "Software"), to deal in the Software without
+//  restriction, including without limitation the rights to use,
+//  copy, modify, merge, publish, distribute, sublicense, and/or sell
+//  copies of the Software, and to permit persons to whom the
+//  Software is furnished to do so, subject to the following
+//  conditions:
+//
+//  The above copyright notice and this permission notice shall be
+//  included in all copies or substantial portions of the Software.
+//
+//  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
+//  EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
+//  OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
+//  NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
+//  HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
+//  WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
+//  FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
+//  OTHER DEALINGS IN THE SOFTWARE.
+//
+//  More info at <http://code.google.com/p/sequel-pro/>
+
+#import <Cocoa/Cocoa.h>
+
+@class SPDatabaseData;
+@class SPServerSupport;
+
+/**
+ * This class serves as a Proxy between a charset+collation button
+ * and the class that actually wants to use those.
+ *
+ *
+ */
+@interface SPCharsetCollationHelper : NSObject {
+	NSPopUpButton *charsetButton;
+	NSPopUpButton *collationButton;
+	
+	BOOL _enabled;
+}
+
+- (id)initWithCharsetButton:(NSPopUpButton *)aCharsetButton CollationButton:(NSPopUpButton *)aCollationButton;
+
+/** Set this to the instance of SPDatabaseData for the current connection */
+@property(readwrite,retain) SPDatabaseData *databaseData;
+
+/** Set this to the instance of SPServerSupport for the current connection */
+@property(readwrite,retain) SPServerSupport *serverSupport;
+
+/**
+ * Set wether the UTF8 menu item should be put at the top of the charset list
+ * or appear along the other items.
+ */
+@property(readwrite,assign) BOOL promoteUTF8;
+
+/**
+ * This item will be put at the top of the list as "Default (x)". Set
+ * it to nil if there is no default.
+ */
+@property(readwrite,retain) NSString *defaultCharset;
+
+/**
+ * This item will be put at the top of the collation list as "Default (x)".
+ * Set it to nil if there is no default.
+ * Note that:
+ *   a) This property is only used when selectedCharset == defaultCharset
+ *   b) If you don't set it the default collation will be queried from the server
+ */
+@property(readwrite,retain) NSString *defaultCollation;
+
+/**
+ * The currently selected charset. Is nil when the Default item is selected or the
+ * server does not support charsets.
+ * You can set it to make a preselection.
+ */
+@property(readwrite,retain) NSString *selectedCharset;
+
+/**
+ * The currently selected collation. Is nil when the Default item is selected or the
+ * server does not support collations.
+ * You can set it to make a preselection.
+ */
+@property(readwrite,retain) NSString *selectedCollation;
+
+/**
+ * This is the format string that will be used for formatting the Default item.
+ * It must contain one %@ variable (the charset name).
+ */
+@property(readwrite,retain) NSString *defaultCharsetFormatString;
+
+/**
+ * Set this to YES before showing the UI and NO after dismissing it.
+ * This will cause the charsets to be re-read and the selection to be reset.
+ */
+@property(readwrite,assign) BOOL enabled;
+
+//used to detected "real" changes of the charset button
+@property(readwrite,retain) NSString *_oldCharset;
+
+@end
diff --git a/Source/SPCharsetCollationHelper.m b/Source/SPCharsetCollationHelper.m
new file mode 100644
index 00000000..c642c835
--- /dev/null
+++ b/Source/SPCharsetCollationHelper.m
@@ -0,0 +1,275 @@
+//
+//  $Id$
+//
+//  SPCharsetCollationHelper.h
+//  sequel-pro
+//
+//  Created by Max Lohrmann on March 20, 2013.
+//  Copyright (c) 2013 Max Lohrmann. All rights reserved.
+//
+//  Permission is hereby granted, free of charge, to any person
+//  obtaining a copy of this software and associated documentation
+//  files (the "Software"), to deal in the Software without
+//  restriction, including without limitation the rights to use,
+//  copy, modify, merge, publish, distribute, sublicense, and/or sell
+//  copies of the Software, and to permit persons to whom the
+//  Software is furnished to do so, subject to the following
+//  conditions:
+//
+//  The above copyright notice and this permission notice shall be
+//  included in all copies or substantial portions of the Software.
+//
+//  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
+//  EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
+//  OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
+//  NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
+//  HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
+//  WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
+//  FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
+//  OTHER DEALINGS IN THE SOFTWARE.
+//
+//  More info at <http://code.google.com/p/sequel-pro/>
+
+#import "SPCharsetCollationHelper.h"
+
+#import "SPServerSupport.h"
+#import "SPDatabaseData.h"
+
+@interface SPCharsetCollationHelper (Hidden)
+
+- (void)charsetButtonClicked:(id)sender;
+- (void)collationButtonClicked:(id)sender;
+- (void)refreshCharsets;
+- (void)refreshCollations;
+
+@end
+
+
+@implementation SPCharsetCollationHelper
+
+@synthesize databaseData;
+@synthesize serverSupport;
+@synthesize promoteUTF8;
+@synthesize defaultCharset;
+@synthesize defaultCollation;
+@synthesize selectedCharset;
+@synthesize selectedCollation;
+@synthesize defaultCharsetFormatString;
+@synthesize _oldCharset;
+
+- (id)initWithCharsetButton:(NSPopUpButton *)aCharsetButton CollationButton:(NSPopUpButton *)aCollationButton
+{
+	NSAssert((aCharsetButton != nil),@"aCharsetButton != nil");
+	NSAssert((aCollationButton != nil),@"aCollationButton != nil");
+	
+	self = [super init];
+	if (self != nil) {
+		[self setPromoteUTF8:YES];
+		[self setDefaultCharsetFormatString:NSLocalizedString(@"Default (%@)",@"Charset Dropdown : Default item ($1 = name)")];
+		charsetButton = aCharsetButton;
+		collationButton = aCollationButton;
+		//connect the charset button with ourselves
+		[charsetButton setTarget:self];
+		[charsetButton setAction:@selector(charsetButtonClicked:)];
+		//connect the collation button with ourselves
+		[collationButton setTarget:self];
+		[collationButton setAction:@selector(collationButtonClicked:)];
+	}
+	return self;
+}
+
+- (void)charsetButtonClicked:(id)sender {
+	if(!_enabled)
+		return;
+	
+	//update selectedCharset
+	if(defaultCharset && [charsetButton indexOfSelectedItem] == 0) {
+		//this is the default item, which means nil for selectedCollation
+		[self setSelectedCharset:nil];
+	}
+	else {
+		//this is an actual item
+		NSString *charsetId = [[charsetButton selectedItem] representedObject];
+		[self setSelectedCharset:charsetId];
+	}
+	
+	//update collations if there actually was a change in charset
+	if((selectedCharset == nil && _oldCharset == nil) || (selectedCharset && [selectedCharset isEqualToString:_oldCharset])) {
+		//same charset. (NOP - just for readability of the if statement)
+	}
+	else {
+		//reset the selected collation. that was only valid as long as the same charset was selected
+		[self setSelectedCollation:nil];
+		[self refreshCollations];
+		[self set_oldCharset:selectedCharset];
+	}
+
+}
+
+- (void)collationButtonClicked:(id)sender {
+	if(!_enabled)
+		return;
+	
+	//update selectedCollation
+	if([collationButton indexOfSelectedItem] == 0) {
+		//this is the default item, which means nil for selectedCollation
+		[self setSelectedCollation:nil];
+	}
+	else {
+		//this is an actual item
+		[self setSelectedCollation:[collationButton titleOfSelectedItem]];
+	}
+	
+}
+
+- (void)refreshCharsets {
+	//reset
+	[charsetButton removeAllItems];
+
+	// Retrieve the server-supported encodings and add them to the menu
+	NSArray *encodings  = [databaseData getDatabaseCharacterSetEncodings];
+	
+	[charsetButton setEnabled:NO];
+	
+	NSMenuItem *selectedRef = nil;
+	
+	if (([encodings count] > 0) && [serverSupport supportsPost41CharacterSetHandling]) {
+		
+		for (NSDictionary *encoding in encodings) 
+		{
+			NSString *charsetId     = [encoding objectForKey:@"CHARACTER_SET_NAME"];
+			NSString *description   = [encoding objectForKey:@"DESCRIPTION"];
+			NSString *menuItemTitle = (![description length]) ? charsetId : [NSString stringWithFormat:@"%@ (%@)", description, charsetId];
+			
+			NSMenuItem *menuItem = [[NSMenuItem alloc] initWithTitle:menuItemTitle action:NULL keyEquivalent:@""];
+			[menuItem setRepresentedObject:charsetId];
+			
+			//store the menu item that we want to select (we'll do that when the list is stable)
+			if(selectedCharset && [charsetId isEqualToString:selectedCharset])
+				selectedRef = menuItem;
+			
+			// If the UTF8 entry has been encountered, promote it to the top of the list
+			if (promoteUTF8 && [charsetId isEqualToString:@"utf8"]) {
+				[[charsetButton menu] insertItem:[menuItem autorelease] atIndex:0];
+				//only add a separator if there actually are more items other than utf8 (might not be true for mysql forks)
+				if([encodings count] > 1)
+					[[charsetButton menu] insertItem:[NSMenuItem separatorItem] atIndex:1];
+			}
+			else {
+				[[charsetButton menu] addItem:[menuItem autorelease]];
+			}
+				
+		}
+		
+		[charsetButton setEnabled:YES];
+	}
+	
+	// Populate the table encoding popup button with a default menu item (if set)
+	// This is down here so it is put even before the utf8 item.
+	if(defaultCharset) {
+		NSMenuItem *defaultItem = [[NSMenuItem alloc] initWithTitle:[NSString stringWithFormat:defaultCharsetFormatString,defaultCharset] action:NULL keyEquivalent:@""];
+		[defaultItem setRepresentedObject:defaultCharset];
+		[[charsetButton menu] insertItem:[defaultItem autorelease] atIndex:0];
+		if([encodings count] > 0)
+			[[charsetButton menu] insertItem:[NSMenuItem separatorItem] atIndex: 1];
+	}
+
+	//inserting items can have strange effects on the lists selectedItem, so we reset that.
+	[charsetButton selectItemAtIndex:0];
+	
+	//honor selectedCharset
+	if(selectedRef)
+		[charsetButton selectItem:selectedRef];
+	
+	//reload the collations for the selected charset
+	[self refreshCollations];
+}
+
+- (void)refreshCollations {
+	NSString *fmtStrDefaultId      = NSLocalizedString(@"Default (%@)",@"Collation Dropdown : Default ($1 = collation name)");
+	NSString *fmtStrDefaultUnknown = NSLocalizedString(@"Default",@"Collation Dropdown : Default (unknown)"); // MySQL < 4.1.0
+	
+	//throw out all items
+	[collationButton removeAllItems];
+	//we'll enable that later if the user can actually change the selection.
+	[collationButton setEnabled:NO];
+	
+	//add the unknown default item - we will remove that once we have a real default,
+	//which can come from two sources:
+	//  a) it was explicitly set in defaultCollation (only applies when defaultCharset == selectedCharset)
+	//  b) the server told us which was the default
+	//if neither works (old mysql / forks ?) we at least have the dummy default.
+	[collationButton addItemWithTitle:fmtStrDefaultUnknown];
+	
+	//if the server actually has support for charsets & collations we will now get a list of all collations
+	//for the current charset. Even if the default charset is kept by the user he can change the default collation
+	//so we search in that case, too.
+	if(![serverSupport supportsPost41CharacterSetHandling])
+		return;
+	
+	//get the charset id
+	NSString *charsetId = [[charsetButton selectedItem] representedObject];
+
+	//now let's get the list of collations for the selected charset id
+	NSArray *applicableCollations = [databaseData getDatabaseCollationsForEncoding:charsetId];
+	
+	//got something?
+	if (![applicableCollations count])
+		return;
+	
+	//add a separator
+	[[collationButton menu] addItem:[NSMenuItem separatorItem]];
+	
+	//if this is the defaultCharset and we have a defaultCollation use that instead
+	BOOL useGivenDefaultCollation = (defaultCharset && defaultCollation && [charsetId isEqualToString:defaultCharset]);
+	
+	if(useGivenDefaultCollation) {
+		NSString *userDefaultCollateTitle = [NSString stringWithFormat:fmtStrDefaultId,defaultCollation];
+		//remove the dummy default item.
+		[collationButton removeItemAtIndex:0];
+		//add it to the top of the list
+		[collationButton insertItemWithTitle:userDefaultCollateTitle atIndex:0];
+	}
+	
+	//add the real items
+	for (NSDictionary *collation in applicableCollations) 
+	{
+		NSString *collationName = [collation objectForKey:@"COLLATION_NAME"];
+		[collationButton addItemWithTitle:collationName];
+		
+		//is this the default collation for this charset (and we didn't override it)?
+		if(!useGivenDefaultCollation && [[collation objectForKey:@"IS_DEFAULT"] isEqualToString:@"Yes"]) {
+			NSString *defaultCollateTitle = [NSString stringWithFormat:fmtStrDefaultId,collationName];
+			//remove the dummy default item.
+			[collationButton removeItemAtIndex:0];
+			//add it to the top of the list
+			[collationButton insertItemWithTitle:defaultCollateTitle atIndex:0];
+		}
+	}
+	//reset selection to first item (it may moved when adding the default item)
+	[collationButton selectItemAtIndex:0];
+	
+	//honor selectedCollation
+	if(selectedCollation)
+		[collationButton selectItemWithTitle:selectedCollation];
+	
+	//yay, now there is actually something not the Default item, so we can enable the button
+	[collationButton setEnabled:YES];
+}
+
+- (BOOL)enabled {
+	return _enabled;
+}
+
+- (void)setEnabled:(BOOL)value {
+	
+	if(value == YES) {
+		NSAssert((databaseData != nil),@"No valid SPDatabaseData object given!");
+		NSAssert((serverSupport != nil),@"No valid SPServerSupport object given!");
+		[self set_oldCharset:selectedCharset]; //initialise to the initial selected charset
+		[self refreshCharsets];
+	}
+	_enabled = value;
+}
+
+@end
diff --git a/Source/SPDatabaseDocument.h b/Source/SPDatabaseDocument.h
index 5cee9759..d0a58dbb 100644
--- a/Source/SPDatabaseDocument.h
+++ b/Source/SPDatabaseDocument.h
@@ -56,6 +56,7 @@
 @class SPCustomQuery;
 @class SPDatabaseStructure;
 @class SPMySQLConnection;
+@class SPCharsetCollationHelper;
 
 #import "SPDatabaseContentViewDelegate.h"
 #import "SPConnectionControllerDelegateProtocol.h"
@@ -115,6 +116,8 @@
 	IBOutlet id databaseAlterSheet;
 	IBOutlet NSPopUpButton *databaseAlterEncodingButton;
 	IBOutlet NSPopUpButton *databaseAlterCollationButton;
+	
+	SPCharsetCollationHelper *alterDatabaseCharsetHelper;
 
 	IBOutlet NSProgressIndicator* queryProgressBar;
 #ifndef SP_CODA
@@ -130,6 +133,8 @@
 	IBOutlet id databaseEncodingButton;
 	IBOutlet id databaseCollationButton;
 	IBOutlet id addDatabaseButton;
+	
+	SPCharsetCollationHelper *addDatabaseCharsetHelper;
 
 #ifndef SP_CODA
 	IBOutlet id databaseCopyNameField;
@@ -332,8 +337,6 @@
 - (void)selectDatabase:(NSString *)aDatabase item:(NSString *)anItem;
 - (IBAction)addDatabase:(id)sender;
 - (IBAction)alterDatabase:(id)sender;
-- (IBAction)alterDatabaseEncodingButtonChanged:(id)sender;
-- (IBAction)databaseEncodingButtonChanged:(id)sender;
 - (IBAction)removeDatabase:(id)sender;
 - (IBAction)refreshTables:(id)sender;
 #ifndef SP_CODA /* method decls */
diff --git a/Source/SPDatabaseDocument.m b/Source/SPDatabaseDocument.m
index 4558e70f..ab321caa 100644
--- a/Source/SPDatabaseDocument.m
+++ b/Source/SPDatabaseDocument.m
@@ -107,6 +107,8 @@ enum {
 #import "SPDatabaseRename.h"
 #endif
 
+#import "SPCharsetCollationHelper.h"
+
 #import <SPMySQL/SPMySQL.h>
 
 // Constants
@@ -117,7 +119,6 @@ static NSString *SPRenameDatabaseAction = @"SPRenameDatabase";
 static NSString *SPAlterDatabaseAction = @"SPAlterDatabase";
 
 @interface SPDatabaseDocument ()
-- (void)_evaluateCollationsForSelectedCharset;
 - (void)_addDatabase;
 - (void)_alterDatabase;
 #ifndef SP_CODA /* method decls */
@@ -232,6 +233,8 @@ static NSString *SPAlterDatabaseAction = @"SPAlterDatabase";
 		taskCancellationCallbackObject = nil;
 		taskCancellationCallbackSelector = NULL;
 #endif
+		alterDatabaseCharsetHelper = nil; //init in awakeFromNib
+		addDatabaseCharsetHelper = nil;
 		
 		keyChainID = nil;
 #ifndef SP_CODA /* init ivars */
@@ -379,6 +382,9 @@ static NSString *SPAlterDatabaseAction = @"SPAlterDatabase";
 
 	[self updateTitlebarStatusVisibilityForcingHide:NO];
 #endif
+	
+	alterDatabaseCharsetHelper = [[SPCharsetCollationHelper alloc] initWithCharsetButton:databaseAlterEncodingButton CollationButton:databaseAlterCollationButton];
+	addDatabaseCharsetHelper   = [[SPCharsetCollationHelper alloc] initWithCharsetButton:databaseEncodingButton CollationButton:databaseCollationButton]; 
 }
 
 #ifndef SP_CODA /* password sheet and history navigation */
@@ -760,44 +766,18 @@ static NSString *SPAlterDatabaseAction = @"SPAlterDatabase";
 	
 	[databaseNameField setStringValue:@""];
 
-	// Populate the database encoding popup button with a default menu item
-	[databaseEncodingButton removeAllItems];
-	NSString *defaultCharset = [databaseDataInstance getServerDefaultCharacterSet];
-	NSString *defaultItemTitle = (defaultCharset)? [NSString stringWithFormat:NSLocalizedString(@"Default (%@)",@"Add Database Sheet : Charset : Use Server Default ($1 = Charset Name)"),defaultCharset] : NSLocalizedString(@"Default",@"Add Database Sheet : Charset : Use Server Default (unknown)");
-	[databaseEncodingButton addItemWithTitle:defaultItemTitle];
-
-	// Retrieve the server-supported encodings and add them to the menu
-	NSArray *encodings  = [databaseDataInstance getDatabaseCharacterSetEncodings];
-	NSString *utf8MenuItemTitle = nil;
+	NSString *defaultCharset   = [databaseDataInstance getServerDefaultCharacterSet];
+	NSString *defaultCollation = [databaseDataInstance getServerDefaultCollation];
 	
-	[databaseEncodingButton setEnabled:YES];
-	
-	if (([encodings count] > 0) && [serverSupport supportsPost41CharacterSetHandling]) {
-		[[databaseEncodingButton menu] addItem:[NSMenuItem separatorItem]];
-		
-		for (NSDictionary *encoding in encodings) 
-		{
-			NSString *menuItemTitle = (![encoding objectForKey:@"DESCRIPTION"]) ? [encoding objectForKey:@"CHARACTER_SET_NAME"] : [NSString stringWithFormat:@"%@ (%@)", [encoding objectForKey:@"DESCRIPTION"], [encoding objectForKey:@"CHARACTER_SET_NAME"]];
-			[databaseEncodingButton addItemWithTitle:menuItemTitle];
-
-			// If the UTF8 entry has been encountered, store the title
-			if ([[encoding objectForKey:@"CHARACTER_SET_NAME"] isEqualToString:@"utf8"]) {
-				utf8MenuItemTitle = [NSString stringWithString:menuItemTitle];
-			}
-		}
-
-		// If a UTF8 entry was found, promote it to the top of the list
-		if (utf8MenuItemTitle) {
-			[[databaseEncodingButton menu] insertItem:[NSMenuItem separatorItem] atIndex:2];
-			[databaseEncodingButton insertItemWithTitle:utf8MenuItemTitle atIndex:2];
-		}
-	}
-	else {
-		[databaseEncodingButton setEnabled:NO];
-	}
-	
-	//reevaluate the collations for the selected charset
-	[self _evaluateCollationsForSelectedCharset];
+	// Setup the charset and collation dropdowns
+	[addDatabaseCharsetHelper setDatabaseData:databaseDataInstance];
+	[addDatabaseCharsetHelper setServerSupport:serverSupport];
+	[addDatabaseCharsetHelper setPromoteUTF8:YES];
+	[addDatabaseCharsetHelper setSelectedCharset:nil];
+	[addDatabaseCharsetHelper setSelectedCollation:nil];
+	[addDatabaseCharsetHelper setDefaultCharset:defaultCharset];
+	[addDatabaseCharsetHelper setDefaultCollation:defaultCollation];
+	[addDatabaseCharsetHelper setEnabled:YES];
 	
 	[NSApp beginSheet:databaseSheet
 	   modalForWindow:parentWindow
@@ -814,48 +794,19 @@ static NSString *SPAlterDatabaseAction = @"SPAlterDatabase";
 - (IBAction)alterDatabase:(id)sender
 {
 	//once the database is created the charset and collation are written
-	//to the db.opt regardless if they were explicity given or not.
+	//to the db.opt file regardless if they were explicity given or not.
 	//So there is no longer a "Default" option.
 	
-	// Populate the database encoding popup button with a default menu item
-	[databaseAlterEncodingButton removeAllItems];
-	NSString *currentCharset = [databaseDataInstance getDatabaseDefaultCharacterSet];
-
-	// Retrieve the server-supported encodings and add them to the menu
-	NSArray *encodings = [databaseDataInstance getDatabaseCharacterSetEncodings];
-	
-	if(![encodings count]) {
-		NSBeep();
-		NSLog(@"%s: Trying to show ALTER UI but getting CHARACTER SET list failed!",__func__);
-		return;
-	}
-		
-	for (NSDictionary *encoding in encodings) 
-	{
-		NSString *charsetId = [encoding objectForKey:@"CHARACTER_SET_NAME"];
-		NSString *description = [encoding objectForKey:@"DESCRIPTION"];
-		NSString *menuItemTitle = (![description length]) ? charsetId : [NSString stringWithFormat:@"%@ (%@)", description, charsetId];
-		
-		NSMenuItem *menuItem = [[[NSMenuItem alloc] initWithTitle:menuItemTitle action:NULL keyEquivalent:@""] autorelease];
-		[menuItem setRepresentedObject:charsetId];
-
-		// If the UTF8 entry has been encountered, promote it to the top of the list (this time we don't want duplicates)
-		if ([charsetId isEqualToString:@"utf8"]) {
-			[[databaseAlterEncodingButton menu] insertItem:menuItem atIndex:0];
-			[[databaseAlterEncodingButton menu] insertItem:[NSMenuItem separatorItem] atIndex:1];
-		}
-		else {
-			[[databaseAlterEncodingButton menu] addItem:menuItem];
-		}
-					 
-		// select the current charset
-		if([charsetId isEqualToString:currentCharset])
-			[databaseAlterEncodingButton selectItem:menuItem];
-		
-	}
+	NSString *currentCharset   = [databaseDataInstance getDatabaseDefaultCharacterSet];
+	NSString *currentCollation = [databaseDataInstance getDatabaseDefaultCollation];
 	
-	//refresh collations for charset
-	[self alterDatabaseEncodingButtonChanged:nil];
+	// Setup the charset and collation dropdowns
+	[alterDatabaseCharsetHelper setDatabaseData:databaseDataInstance];
+	[alterDatabaseCharsetHelper setServerSupport:serverSupport];
+	[alterDatabaseCharsetHelper setPromoteUTF8:YES];
+	[alterDatabaseCharsetHelper setSelectedCharset:currentCharset];
+	[alterDatabaseCharsetHelper setSelectedCollation:currentCollation];
+	[alterDatabaseCharsetHelper setEnabled:YES];
 	
 	[NSApp beginSheet:databaseAlterSheet
 	   modalForWindow:parentWindow
@@ -864,50 +815,6 @@ static NSString *SPAlterDatabaseAction = @"SPAlterDatabase";
 		  contextInfo:SPAlterDatabaseAction];
 }
 
-/**
- * Updates the list of collations for the currently selected encoding in the ALTER DATABASE sheet
- * @warning Make sure this method is only called on mysql 4.1+ servers!
- */
-- (IBAction)alterDatabaseEncodingButtonChanged:(id)sender
-{
-	NSString *currentCharset   = [databaseDataInstance getDatabaseDefaultCharacterSet];
-	NSString *currentCollation = [databaseDataInstance getDatabaseDefaultCollation];
-
-	//throw out all items
-	[databaseAlterCollationButton removeAllItems];
-	
-	//get the selected charset id
-	NSString *charsetId = [[databaseAlterEncodingButton selectedItem] representedObject];
-	//this should not fail so far down the line
-	if(![charsetId length]) {
-		NSLog(@"%s: Encoding menu item <%@> does not represent any charset!",__func__,[databaseAlterEncodingButton title]);
-		return;
-	}
-
-	//now let's get the list of collations for the selected charset id
-	NSArray *applicableCollations = [databaseDataInstance getDatabaseCollationsForEncoding:charsetId];
-	//and add the real items
-	for (NSDictionary *collation in applicableCollations) 
-	{
-		NSString *collationName = [collation objectForKey:@"COLLATION_NAME"];
-						
-		NSMenuItem *menuItem = [[[NSMenuItem alloc] initWithTitle:collationName action:NULL keyEquivalent:@""] autorelease];
-		[[databaseAlterCollationButton menu] addItem:menuItem];
-		
-		//if the charset is the current charset and this is the current collation
-		//else if this collation is the default for the selected charset 
-		//  => select it
-		if(([charsetId isEqualToString:currentCharset] && [collationName isEqualToString:currentCollation]) || 
-		   (![charsetId isEqualToString:currentCharset] && [[collation objectForKey:@"IS_DEFAULT"] isEqualToString:@"Yes"]))
-			[databaseAlterCollationButton selectItem:menuItem];
-	}
-}
-
-- (IBAction)databaseEncodingButtonChanged:(id)sender
-{
-	[self _evaluateCollationsForSelectedCharset];
-}
-
 #ifndef SP_CODA /* operations on whole databases */
 /**
  * opens the copy database sheet and copies the databsae
@@ -1081,6 +988,7 @@ static NSString *SPAlterDatabaseAction = @"SPAlterDatabase";
 	}
 	// Add a new database
 	else if ([contextInfo isEqualToString:@"addDatabase"]) {
+		[addDatabaseCharsetHelper setEnabled:NO];
 		if (returnCode == NSOKButton) {
 			[self _addDatabase];
 
@@ -1121,6 +1029,7 @@ static NSString *SPAlterDatabaseAction = @"SPAlterDatabase";
 #endif
 	}
 	else if([contextInfo isEqualToString:SPAlterDatabaseAction]) {
+		[alterDatabaseCharsetHelper setEnabled:NO];
 		if(returnCode == NSOKButton) {
 			[self _alterDatabase];
 		}
@@ -6005,76 +5914,6 @@ static NSString *SPAlterDatabaseAction = @"SPAlterDatabase";
 }			 
 
 /**
- * Checks the new database sheet for the currently selected charset and updates the list of collations accordingly.
- */
-- (void)_evaluateCollationsForSelectedCharset
-{
-	NSString *fmtStrDefaultId      = NSLocalizedString(@"Default (%@)",@"Add Database : Collation : Default ($1 = collation name)");
-	NSString *fmtStrDefaultUnknown = NSLocalizedString(@"Default",@"Add Database Sheet : Collation : Default (unknown)"); 
-	
-	//throw out all items
-	[databaseCollationButton removeAllItems];
-	//we'll enable that later if the user can actually change the selection.
-	[databaseCollationButton setEnabled:NO];
-	
-	/* logic below is as follows:
-	 *   if the server default charset is selected also use the server default collation
-	 *   regardless of default charset or not get the list of all collations that apply
-	 *   if a non-default charset is selected look out for it's default collation and promote that to the top as default
-	 *
-	 * Selecting a default charset (or collation) means that we don't want to specify one in the CREATE DATABASE statement.
-	 */
-		
-	//is the default charset currently selected?
-	BOOL isDefaultCharset = ([databaseEncodingButton indexOfSelectedItem] == 0);
-	
-	if(isDefaultCharset) {
-		NSString *defaultCollation = [databaseDataInstance getServerDefaultCollation];
-		NSString *defaultItemTitle = (defaultCollation)? [NSString stringWithFormat:fmtStrDefaultId,defaultCollation] : fmtStrDefaultUnknown;
-		[databaseCollationButton addItemWithTitle:defaultItemTitle];
-		//add the separator for the real items
-		[[databaseCollationButton menu] addItem:[NSMenuItem separatorItem]];
-	}
-	
-	//if the server actually has support for charsets & collations we will now get a list of all collations
-	//for the current charset. Even if the default charset is kept by the user he can change the default collation
-	//so we search in that case, too.
-	if([serverSupport supportsPost41CharacterSetHandling]) {
-		//get the charset id the lazy way
-		NSString *charsetName = [[databaseEncodingButton title] stringByMatching:@"\\((.*)\\)\\Z" capture:1L];
-		//this should not fail as even default is "Default (charset)" - if it does there's nothing we can do
-		if(!charsetName) {
-			NSLog(@"Can't find charset id in encoding name <%@>. Format should be <Description (id)>.",[databaseEncodingButton title]);
-			return;
-		}
-		//now let's get the list of collations for the selected charset id
-		NSArray *applicableCollations = [databaseDataInstance getDatabaseCollationsForEncoding:charsetName];
-		if ([applicableCollations count] > 0) {
-			//and add the real items
-			for (NSDictionary *collation in applicableCollations) 
-			{
-				NSString *collationName = [collation objectForKey:@"COLLATION_NAME"];
-				[databaseCollationButton addItemWithTitle:collationName];
-				
-				//if this is not the server default charset let's find it's default collation too
-				if(!isDefaultCharset && [[collation objectForKey:@"IS_DEFAULT"] isEqualToString:@"Yes"]) {
-					NSString *defaultCollateTitle = [NSString stringWithFormat:fmtStrDefaultId,collationName];
-					//add it to the top of the list
-					[databaseCollationButton insertItemWithTitle:defaultCollateTitle atIndex:0];
-					//add a separator underneath
-					[[databaseCollationButton menu] insertItem:[NSMenuItem separatorItem] atIndex:1];
-				}
-			}
-			//reset selection to first item (it may moved when adding the default item)
-			[databaseCollationButton selectItemAtIndex:0];
-			//yay, now there is actually something not the Default item, so we can enable the button
-			[databaseCollationButton setEnabled:YES];
-		}
-	}
-	
-}
-
-/**
  * Adds a new database.
  */
 - (void)_addDatabase
@@ -6092,20 +5931,14 @@ static NSString *SPAlterDatabaseAction = @"SPAlterDatabase";
 	NSString *createStatement = [NSString stringWithFormat:@"CREATE DATABASE %@", [[databaseNameField stringValue] backtickQuotedString]];
 	
 	// If there is an encoding selected other than the default we must specify it in CREATE DATABASE statement
-	if ([databaseEncodingButton indexOfSelectedItem] > 0) {
-		NSString *encodingName = [[databaseEncodingButton title] stringByMatching:@"\\((.*)\\)\\Z" capture:1L];
-		if (!encodingName) encodingName = [databaseEncodingButton title];
-		if (!encodingName) encodingName = @"utf8";
-
+	NSString *encodingName = [addDatabaseCharsetHelper selectedCharset];
+	if (encodingName)		
 		createStatement = [NSString stringWithFormat:@"%@ DEFAULT CHARACTER SET %@", createStatement, [encodingName backtickQuotedString]];
-	}
 	
 	// If there is a collation selected other than the default we must specify it in the CREATE DATABASE statement
-	if ([databaseCollationButton indexOfSelectedItem] > 0) {
-		//collations have no description except for the default item (which is already excluded) so we can directly use the value
-		NSString *collationName = [databaseCollationButton title];
+	NSString *collationName = [addDatabaseCharsetHelper selectedCollation];
+	if (collationName)		
 		createStatement = [NSString stringWithFormat:@"%@ DEFAULT COLLATE %@", createStatement, [collationName backtickQuotedString]];
-	}
 	
 	// Create the database
 	[mySQLConnection queryString:createStatement];
@@ -6135,11 +5968,17 @@ static NSString *SPAlterDatabaseAction = @"SPAlterDatabase";
 {
 	//we'll always run the alter statement, even if old == new because after all that is what the user requested
 	
-	NSString *newCharset   = [[databaseAlterEncodingButton selectedItem] representedObject];
-	NSString *newCollation = [databaseAlterCollationButton titleOfSelectedItem];
+	NSString *newCharset   = [alterDatabaseCharsetHelper selectedCharset];
+	NSString *newCollation = [alterDatabaseCharsetHelper selectedCollation];
 	
-	NSString *alterStatement = [NSString stringWithFormat:@"ALTER DATABASE %@ DEFAULT CHARACTER SET %@ DEFAULT COLLATE %@", [[self database] backtickQuotedString],[newCharset backtickQuotedString],[newCollation backtickQuotedString]];
+	NSString *alterStatement = [NSString stringWithFormat:@"ALTER DATABASE %@ DEFAULT CHARACTER SET %@", [[self database] backtickQuotedString],[newCharset backtickQuotedString]];
 
+	//technically there is an issue here: If a user had a non-default collation and now wants to switch to the default collation this cannot be specidifed (default == nil).
+	//However if you just do an ALTER with CHARACTER SET == oldCharset MySQL will still reset the collation therefore doing exactly what we want.
+	if(newCollation) {
+		alterStatement = [NSString stringWithFormat:@"%@ DEFAULT COLLATE %@",alterStatement,[newCollation backtickQuotedString]];
+	}
+	
 	//run alter
 	[mySQLConnection queryString:alterStatement];
 	
@@ -6456,6 +6295,9 @@ static NSString *SPAlterDatabaseAction = @"SPAlterDatabase";
 	if (customQueryInstance) [customQueryInstance release];
 #endif
 	
+	if (alterDatabaseCharsetHelper) [alterDatabaseCharsetHelper release];
+	if (addDatabaseCharsetHelper) [addDatabaseCharsetHelper release];
+	
 	[super dealloc];
 }
 
diff --git a/Source/SPTablesList.h b/Source/SPTablesList.h
index 857263b9..c46763fb 100644
--- a/Source/SPTablesList.h
+++ b/Source/SPTablesList.h
@@ -39,6 +39,7 @@
 @class SPTableStructure;
 @class SPTableContent;
 @class SPSplitView;
+@class SPCharsetCollationHelper;
 
 #ifdef SP_CODA
 @class SQLSidebarViewController;
@@ -137,6 +138,8 @@
 #ifdef SP_CODA
 	SQLSidebarViewController* sidebarViewController;
 #endif
+	
+	SPCharsetCollationHelper *addTableCharsetHelper;
 }
 
 // IBAction methods
@@ -144,7 +147,6 @@
 - (IBAction)addTable:(id)sender;
 - (IBAction)closeSheet:(id)sender;
 - (IBAction)removeTable:(id)sender;
-- (IBAction)tableEncodingButtonChanged:(id)sender;
 
 #ifndef SP_CODA /* method decls */
 - (IBAction)copyTable:(id)sender;
diff --git a/Source/SPTablesList.m b/Source/SPTablesList.m
index 70bab371..acc12aa7 100644
--- a/Source/SPTablesList.m
+++ b/Source/SPTablesList.m
@@ -65,6 +65,8 @@
 #import "SQLSidebarViewController.h"
 #endif
 
+#import "SPCharsetCollationHelper.h"
+
 #import <SPMySQL/SPMySQL.h>
 
 // Constants
@@ -128,6 +130,7 @@ static NSString *SPDuplicateTable = @"SPDuplicateTable";
 		
 		smallSystemFont = [NSFont systemFontOfSize:[NSFont smallSystemFontSize]];
 #endif
+		addTableCharsetHelper = nil; //initialized in awakeFromNib
 	}
 	
 	return self;
@@ -166,6 +169,9 @@ static NSString *SPDuplicateTable = @"SPDuplicateTable";
 #ifndef SP_CODA
 	[tablesListView registerForDraggedTypes:[NSArray arrayWithObjects:SPNavigatorTableDataPasteboardDragType, nil]];
 #endif
+	
+	//create the charset helper
+	addTableCharsetHelper = [[SPCharsetCollationHelper alloc] initWithCharsetButton:tableEncodingButton CollationButton:tableCollationButton];
 }
 
 #pragma mark -
@@ -379,45 +385,17 @@ static NSString *SPDuplicateTable = @"SPDuplicateTable";
 		[tableTypeButton addItemWithTitle:[engine objectForKey:@"Engine"]];
 	}
 
-	// Populate the table encoding popup button with a default menu item
-	[tableEncodingButton removeAllItems];
-	[tableEncodingButton addItemWithTitle:[NSString stringWithFormat:NSLocalizedString(@"Inherit from database (%@)", @"New Table Sheet : Table Encoding Dropdown : Default inherited from database"), [tableDocumentInstance databaseEncoding]]];
-
-	// Retrieve the server-supported encodings and add them to the menu
-	NSArray *encodings  = [databaseDataInstance getDatabaseCharacterSetEncodings];
+	// Setup the charset and collation dropdowns
+	[addTableCharsetHelper setDatabaseData:databaseDataInstance];
+	[addTableCharsetHelper setServerSupport:[tableDocumentInstance serverSupport]];
+	[addTableCharsetHelper setPromoteUTF8:YES];
+	[addTableCharsetHelper setDefaultCharsetFormatString:NSLocalizedString(@"Inherit from database (%@)", @"New Table Sheet : Table Encoding Dropdown : Default inherited from database")];
+	[addTableCharsetHelper setDefaultCharset:[databaseDataInstance getDatabaseDefaultCharacterSet]];
+	[addTableCharsetHelper setDefaultCollation:[databaseDataInstance getDatabaseDefaultCollation]];
+	[addTableCharsetHelper setSelectedCharset:nil]; //reset to not carry over state from last time sheet was shown
+	[addTableCharsetHelper setSelectedCollation:nil];
+	[addTableCharsetHelper setEnabled:YES];
 	
-	NSString *utf8MenuItemTitle = nil;
-	
-	[tableEncodingButton setEnabled:YES];
-	
-	if (([encodings count] > 0) && [[tableDocumentInstance serverSupport] supportsPost41CharacterSetHandling]) {
-		[[tableEncodingButton menu] addItem:[NSMenuItem separatorItem]];
-		
-		for (NSDictionary *encoding in encodings) 
-		{
-			NSString *menuItemTitle = (![encoding objectForKey:@"DESCRIPTION"]) ? [encoding objectForKey:@"CHARACTER_SET_NAME"] : [NSString stringWithFormat:@"%@ (%@)", [encoding objectForKey:@"DESCRIPTION"], [encoding objectForKey:@"CHARACTER_SET_NAME"]];
-			
-			[tableEncodingButton addItemWithTitle:menuItemTitle];
-
-			// If the UTF8 entry has been encountered, store the menu title
-			if ([[encoding objectForKey:@"CHARACTER_SET_NAME"] isEqualToString:@"utf8"]) {
-				utf8MenuItemTitle = [NSString stringWithString:menuItemTitle];
-			}
-		}
-
-		// If a UTF8 entry was found, promote it to the top of the list
-		if (utf8MenuItemTitle) {
-			[[tableEncodingButton menu] insertItem:[NSMenuItem separatorItem] atIndex:2];
-			[tableEncodingButton insertItemWithTitle:utf8MenuItemTitle atIndex:2];
-		}
-	}
-	else {
-		[tableEncodingButton setEnabled:NO];
-	}
-	
-	//Load the collations for the current charset
-	[self tableEncodingButtonChanged:self];
-
 	// Set the focus to the name field
 	[tableSheet makeFirstResponder:tableNameField];
 
@@ -765,6 +743,7 @@ static NSString *SPDuplicateTable = @"SPDuplicateTable";
 	else 
 #endif
 	if ([contextInfo isEqualToString:SPAddNewTable]) {
+		[addTableCharsetHelper setEnabled:NO];
 		if (returnCode == NSOKButton) {
 			[self _addTable];
 		}
@@ -2287,20 +2266,14 @@ static NSString *SPDuplicateTable = @"SPDuplicateTable";
 	}
 
 	// If there is an encoding selected other than the default we must specify it in CREATE TABLE statement
-	if ([tableEncodingButton indexOfSelectedItem] > 0) {
-		NSString *encodingName = [[tableEncodingButton title] stringByMatching:@"\\((.*)\\)" capture:1L];
-		
-		if (!encodingName) encodingName = @"utf8";
-		
+	NSString *encodingName = [addTableCharsetHelper selectedCharset];
+	if (encodingName)		
 		charSetStatement = [NSString stringWithFormat:@"DEFAULT CHARACTER SET %@", [encodingName backtickQuotedString]];
-	}
 	
-	// If there is a collation selected other than the default we must specify it in the CREATE DATABASE statement
-	if ([tableCollationButton indexOfSelectedItem] > 0) {
-		//collations have no description except for the default item (which is already excluded) so we can directly use the value
-		NSString *collationName = [tableCollationButton title];
+	// If there is a collation selected other than the default we must specify it in the CREATE TABLE statement
+	NSString *collationName = [addTableCharsetHelper selectedCollation];
+	if (collationName)		
 		collationStatement = [NSString stringWithFormat:@"DEFAULT COLLATE %@",[collationName backtickQuotedString]];
-	}
 
 	// If there is a type selected other than the default we must specify it in CREATE TABLE statement
 	if ([tableTypeButton indexOfSelectedItem] > 0) {
@@ -2682,6 +2655,8 @@ static NSString *SPDuplicateTable = @"SPDuplicateTable";
 #endif
 	if (selectedTableName) [selectedTableName release];
 	
+	if (addTableCharsetHelper) [addTableCharsetHelper release];
+	
 	[super dealloc];
 }