• [SPDatabase getDatabaseCollationsForEncoding:] now uses a cache to reduce the querying of info_schema

- this fixes the querying of each reloadData in [SPTableStructure objectValueFor:]
• autoreleased mutableCopy in [SPTableStructure loadTable]
• fixed drag&drop query in SPTableStructure to copy charset and collation as well

3 files changed, 34 insertions, 7 deletions

diff --git a/Source/SPDatabaseData.h b/Source/SPDatabaseData.h
index 29c80aeb..f0b79b45 100644
--- a/Source/SPDatabaseData.h
+++ b/Source/SPDatabaseData.h
@@ -46,6 +46,7 @@ typedef struct
 	NSMutableArray *characterSetCollations;
 	NSMutableArray *storageEngines;
 	NSMutableArray *characterSetEncodings;
+	NSMutableDictionary *cachedCollationsByEncoding;
 	
 	MCPConnection *connection;
 }
diff --git a/Source/SPDatabaseData.m b/Source/SPDatabaseData.m
index 27b542c4..bec1264c 100644
--- a/Source/SPDatabaseData.m
+++ b/Source/SPDatabaseData.m
@@ -207,6 +207,7 @@ const SPDatabaseCharSets charsets[] =
 		characterSetCollations = [[NSMutableArray alloc] init];
 		storageEngines         = [[NSMutableArray alloc] init];
 		characterSetEncodings  = [[NSMutableArray alloc] init];
+		cachedCollationsByEncoding = [[NSMutableDictionary alloc] init];
 	}
 	
 	return self;
@@ -278,10 +279,13 @@ const SPDatabaseCharSets charsets[] =
 		[characterSetCollations removeAllObjects];
 		
 		characterSetEncoding = [[NSString alloc] initWithString:encoding];
-		
+
+		if([cachedCollationsByEncoding objectForKey:characterSetEncoding] && [[cachedCollationsByEncoding objectForKey:characterSetEncoding] count])
+			return [cachedCollationsByEncoding objectForKey:characterSetEncoding];
+
 		// Try to retrieve the available collations for the supplied encoding from the database
 		if (serverMajorVersion >= 5)
-			[characterSetCollations addObjectsFromArray:[self _getDatabaseDataForQuery:[NSString stringWithFormat:@"SELECT * FROM `information_schema`.`collations` WHERE character_set_name = '%@' ORDER BY `collation_name` ASC", characterSetEncoding]]];	
+			[characterSetCollations addObjectsFromArray:[self _getDatabaseDataForQuery:[NSString stringWithFormat:@"SELECT * FROM `information_schema`.`collations` WHERE character_set_name = '%@' ORDER BY `collation_name` ASC", characterSetEncoding]]];
 
 		// If that failed, get the list of collations matching the supplied encoding from the hard-coded list
 		if (![characterSetCollations count]) {
@@ -289,15 +293,19 @@ const SPDatabaseCharSets charsets[] =
 			
 			do {
 				NSString *charSet = [NSString stringWithCString:c->name encoding:NSUTF8StringEncoding];
-				
+
 				if ([charSet isEqualToString:characterSetEncoding]) {
 					[characterSetCollations addObject:[NSDictionary dictionaryWithObject:[NSString stringWithCString:c->collation encoding:NSUTF8StringEncoding] forKey:@"COLLATION_NAME"]];
 				}
-				
+
 				++c;
 			} 
 			while (c[0].nr != 0);
-		}		
+		}
+
+		if(characterSetCollations && [characterSetCollations count])
+			[cachedCollationsByEncoding setObject:[NSArray arrayWithArray:characterSetCollations] forKey:characterSetEncoding];
+
 	}
 	
 	return characterSetCollations;
@@ -441,6 +449,7 @@ const SPDatabaseCharSets charsets[] =
 	[characterSetCollations release], characterSetCollations = nil;
 	[storageEngines release], storageEngines = nil;
 	[characterSetEncodings release], characterSetEncodings = nil;
+	[cachedCollationsByEncoding release], cachedCollationsByEncoding = nil;
 	
 	[super dealloc];
 }
diff --git a/Source/SPTableStructure.m b/Source/SPTableStructure.m
index 2ec324a7..16c01b6f 100644
--- a/Source/SPTableStructure.m
+++ b/Source/SPTableStructure.m
@@ -118,7 +118,7 @@
 
 	// Make a mutable copy out of the cached [tableDataInstance columns] since we're adding infos
 	for(id col in [tableDataInstance columns])
-		[theTableFields addObject:[col mutableCopy]];
+		[theTableFields addObject:[[col mutableCopy] autorelease]];
 
 	// Retrieve the indexes for the table
 	indexResult = [[mySQLConnection queryString:[NSString stringWithFormat:@"SHOW INDEX FROM %@", [aTable backtickQuotedString]]] retain];
@@ -760,7 +760,6 @@ closes the keySheet
 		[queryString appendFormat:@" COLLATE %@", col];
 	}
 
-
 	// Field specification
 	if ([[theRow objectForKey:@"unsigned"] integerValue] == 1) {
 		[queryString appendString:@" UNSIGNED"];
@@ -1385,6 +1384,24 @@ would result in a position change.
 		[queryString appendFormat:@"(%@)", [originalRow objectForKey:@"length"]];
 	}
 
+	NSString *fieldEncoding = @"";
+	if([[originalRow objectForKey:@"encoding"] integerValue] > 0) {
+		NSString *enc = [[[[[tableSourceView tableColumns] objectAtIndex:10] dataCell] itemAtIndex:[[originalRow objectForKey:@"encoding"] integerValue]] title];
+		NSInteger start = [enc rangeOfString:@"("].location+1;
+		NSInteger end = [enc length] - start - 1;
+		fieldEncoding = [enc substringWithRange:NSMakeRange(start, end)];
+		[queryString appendFormat:@" CHARACTER SET %@", fieldEncoding];
+	}
+	if(![fieldEncoding length] && [tableDataInstance tableEncoding]) {
+		fieldEncoding = [tableDataInstance tableEncoding];
+	}
+	if([fieldEncoding length] && [[originalRow objectForKey:@"collation"] integerValue] > 0) {
+		NSArray *theCollations = [databaseDataInstance getDatabaseCollationsForEncoding:fieldEncoding];
+		NSString *col = [[theCollations objectAtIndex:[[originalRow objectForKey:@"collation"] integerValue]-1] objectForKey:@"COLLATION_NAME"];
+		[queryString appendFormat:@" COLLATE %@", col];
+	}
+
+
 	// Add unsigned, zerofill, binary, not null if necessary
 	if ([[originalRow objectForKey:@"unsigned"] integerValue]) {
 		[queryString appendString:@" UNSIGNED"];