• added: "Copy as SQL INSERT" ^⌥⌘C

- up to now only available for the table content pane
- supports blobs
- if a primary key is given it also support (not yet loaded) blobs and long text data fields

1 files changed, 166 insertions, 83 deletions

diff --git a/Source/CMCopyTable.m b/Source/CMCopyTable.m
index 7bb66da7..2f7bb929 100644
--- a/Source/CMCopyTable.m
+++ b/Source/CMCopyTable.m
@@ -24,6 +24,8 @@
 
 #import "CMCopyTable.h"
 #import "SPArrayAdditions.h"
+#import "CMMCPConnection.h"
+#import "TableContent.h"
 
 int MENU_EDIT_COPY_WITH_COLUMN = 2001;
 int MENU_EDIT_COPY_AS_SQL      = 2002;
@@ -32,8 +34,10 @@ int MENU_EDIT_COPY_AS_SQL      = 2002;
 
 - (void)copy:(id)sender
 {
+	prefs = [[NSUserDefaults standardUserDefaults] retain];
+	
 	NSString *tmp = nil;
-
+	
 	if([sender tag] == MENU_EDIT_COPY_AS_SQL) {
 		tmp = [self selectedRowsAsSqlInserts];
 		if ( nil != tmp )
@@ -150,107 +154,180 @@ int MENU_EDIT_COPY_AS_SQL      = 2002;
 }
 
 /* 
- * Return selected rows as SQL INSERT INTO foo VALUES baz
- * string.
+ * Return selected rows as SQL INSERT INTO `foo` VALUES (baz) string.
+ * If no selected table name is given `<table>` will be used instead.
  */
 - (NSString *)selectedRowsAsSqlInserts
 {
-	if ( [self numberOfSelectedRows] > 0 )
-	{
-		NSArray *columns = [self tableColumns];
-		int numColumns = [columns count];
-		id dataSource = [self dataSource];
-		id enumObj;
 
-		NSMutableString *result = [NSMutableString stringWithCapacity:numColumns];
+	if ( [self numberOfSelectedRows] < 1 ) return nil;
 
-		// Create an array of table column names
-		NSMutableArray *tbHeader = [NSMutableArray arrayWithCapacity:numColumns];
-		enumerate(columns, enumObj) [tbHeader addObject:[[enumObj headerCell] stringValue]];
+	NSArray *columns = [self tableColumns];
+	int numColumns   = [columns count];
 
-		// Create an hash of header name and typegrouping
-		NSMutableDictionary *headerType = [NSMutableDictionary dictionaryWithCapacity:numColumns];
-		enumerate(columnDefinitions, enumObj)
-			[headerType setObject:[enumObj objectForKey:@"typegrouping"] forKey:[enumObj objectForKey:@"name"]];
+	NSTableColumn *col     = nil;
+	// NSIndexSet *rowIndexes = [self selectedRowIndexes];
+	NSString *spNULL       = [prefs objectForKey:@"NullValue"];
+	NSMutableString *value = [NSMutableString stringWithCapacity:10];
+	NSDictionary *dbDataRow;
+	id enumObj;
+	id rowData = nil;
+	id rowEnumObject = nil;
+	
+	long row;
+	long rowCounter = 0;
+	long penultimateRowIndex = [[self selectedRowIndexes] count];
+	int c;
+	int valueLength = 0;
 
-		// Create array of types according to the column order
-		NSMutableArray *types = [NSMutableArray arrayWithCapacity:numColumns];
-		enumerate(tbHeader, enumObj)
-		{
-			NSString *t = [headerType objectForKey:enumObj];
+	NSMutableString *result = [NSMutableString stringWithCapacity:numColumns];
 
-			if([t isEqualToString:@"bit"] || [t isEqualToString:@"integer"] || [t isEqualToString:@"float"])
-				[types addObject:[NSNumber numberWithInt:0]];
-			else if([t isEqualToString:@"blobdata"])
-				[types addObject:[NSNumber numberWithInt:2]];
-			else
-				[types addObject:[NSNumber numberWithInt:1]];
+	// Create an array of table column names
+	NSMutableArray *tbHeader = [NSMutableArray arrayWithCapacity:numColumns];
+	enumerate(columns, enumObj)
+		[tbHeader addObject:[[enumObj headerCell] stringValue]];
 
-		}
-		
-		[result appendString:[NSString stringWithFormat:@"INSERT INTO `%@` (%@)\nVALUES\n", 
-			(selectedTable == nil)?@"<table>":selectedTable, [tbHeader componentsJoinedAndBacktickQuoted]]];
+	// Create an hash of header name and typegrouping
+	NSMutableDictionary *headerType = [NSMutableDictionary dictionaryWithCapacity:numColumns];
+	enumerate(columnDefinitions, enumObj)
+		[headerType setObject:[enumObj objectForKey:@"typegrouping"] forKey:[enumObj objectForKey:@"name"]];
 
-		int c;
-		id rowData = nil;
-		NSTableColumn *col = nil;
-		NSIndexSet *rowIndexes = [self selectedRowIndexes];
-		unsigned row = [rowIndexes firstIndex];
+	// Create array of types according to the column order
+	NSMutableArray *types = [NSMutableArray arrayWithCapacity:numColumns];
+	enumerate(tbHeader, enumObj)
+	{
+		NSString *t = [headerType objectForKey:enumObj];
+		if([t isEqualToString:@"bit"] || [t isEqualToString:@"integer"] || [t isEqualToString:@"float"])
+			[types addObject:[NSNumber numberWithInt:0]]; // numeric
+		else if([t isEqualToString:@"blobdata"])
+			[types addObject:[NSNumber numberWithInt:2]]; // blob data
+		else if([t isEqualToString:@"textdata"])
+			[types addObject:[NSNumber numberWithInt:3]]; // long text data
+		else
+			[types addObject:[NSNumber numberWithInt:1]]; // string (fallback coevally)
+	}
 
-		while ( row != NSNotFound )
-		{ 
-			[result appendString:@"\t("];
-			rowData = nil;
-			for ( c = 0; c < numColumns; c++)
-			{
-				col = [columns objectAtIndex:c];
-				rowData = [dataSource tableView:self 
-					  objectValueForTableColumn:col 
-											row:row ];
+	[result appendString:[NSString stringWithFormat:@"INSERT INTO `%@` (%@)\nVALUES\n", 
+		(selectedTable == nil)?@"<table>":selectedTable, [tbHeader componentsJoinedAndBacktickQuoted]]];
 
-				if ( nil != rowData ) {
+	//this is really deprecated in 10.3, but the new method is really weird
+	NSEnumerator *enumerator = [self selectedRowEnumerator]; 
 
-					switch([[types objectAtIndex:c] intValue]) {
-						case 0: // numeric
-						[result appendString:[NSString stringWithFormat:@"%@,", [rowData description]]];
+	while ( rowEnumObject = [enumerator nextObject] )
+	{ 
+		[value appendString:@"\t("];
+		rowData = nil;
+		row = [rowEnumObject intValue];
+		rowCounter++;
+		for ( c = 0; c < numColumns; c++ )
+		{
+			col = [columns objectAtIndex:c];
+			rowData = [[tableData objectAtIndex:row] objectForKey:[tbHeader objectAtIndex:c]];
+
+			// Check for NULL value - TODO this is not safe!!
+			if([[rowData description] isEqualToString:spNULL]){
+				[value appendString:@"NULL, "];
+				continue;
+			}
+			else if ( rowData != nil ) {
+				// check column type and insert the data accordingly
+				switch([[types objectAtIndex:c] intValue]) {
+					case 0: // numeric
+						[value appendString:[NSString stringWithFormat:@"%@, ", [rowData description]]];
 						break;
-						case 1: // string
-						[result appendString:[NSString stringWithFormat:@"'%@',", [[rowData description] stringByReplacingOccurrencesOfString:@"'" withString:@"\'"] ] ];
+					case 1: // string
+						[value appendString:[NSString stringWithFormat:@"'%@', ", 
+							[mySQLConnection prepareString:[rowData description]]]];
 						break;
-						case 2: //blob
-						[result appendString:[NSString stringWithFormat:@"X'%@',", @"<BLOB>"]];
-					}
+					case 2: // blob
+						if ([prefs boolForKey:@"LoadBlobsAsNeeded"]) {
+
+							// Abort if there are no indices on this table or if there's no table name given.
+							if (![[tableInstance argumentForRow:row] length] || selectedTable == nil)
+								return nil;
+
+							//if we have indexes, use argumentForRow
+							dbDataRow = [[mySQLConnection queryString:
+								[NSString stringWithFormat:@"SELECT * FROM `%@` WHERE %@", 
+									selectedTable, [tableInstance argumentForRow:row]]] fetchRowAsDictionary];
+							if([[dbDataRow objectForKey:[tbHeader objectAtIndex:c]] isKindOfClass:[NSNull class]])
+								[value appendString:@"NULL, "];
+							else
+								[value appendString:[NSString stringWithFormat:@"X'%@', ", 
+									[mySQLConnection prepareBinaryData:[dbDataRow objectForKey:[tbHeader objectAtIndex:c]]]]];
+						} else {
+							[value appendString:[NSString stringWithFormat:@"X'%@', ", [mySQLConnection prepareBinaryData:rowData]]];
+						}
+						break;
+					case 3: // long text data
+						if ([prefs boolForKey:@"LoadBlobsAsNeeded"]) {
+
+							// Abort if there are no indices on this table or if there's no table name given.
+							if (![[tableInstance argumentForRow:row] length] || selectedTable == nil)
+								return nil;
+
+							//if we have indexes, use argumentForRow
+							dbDataRow = [[mySQLConnection queryString:
+								[NSString stringWithFormat:@"SELECT * FROM `%@` WHERE %@", 
+									selectedTable, [tableInstance argumentForRow:row]]] fetchRowAsDictionary];
+							if([[dbDataRow objectForKey:[tbHeader objectAtIndex:c]] isKindOfClass:[NSNull class]])
+								[value appendString:@"NULL, "];
+							else
+								[value appendString:[NSString stringWithFormat:@"'%@', ", 
+									[mySQLConnection prepareString:[[dbDataRow objectForKey:[tbHeader objectAtIndex:c]] description]]]];
+						} else {
+							[value appendString:[NSString stringWithFormat:@"'%@', ", 
+								[[rowData description] stringByReplacingOccurrencesOfString:@"'" withString:@"\\'"] ] ];
+						}
+						break;
+					default:
+						return nil;
 				}
-				else
-					[result appendString:@"'',"];
+			}
+			else
+				// TODO is this necessary? or better to return nil?
+				[value appendString:@"'', "];
 
-			} //end for each column
-			
-			if ( [result length] )
-			{
-				[result deleteCharactersInRange:NSMakeRange([result length]-1, 1)];
+		} //end for each column
+
+		// delete last ', '
+		if ( [value length] > 2 )
+			[value deleteCharactersInRange:NSMakeRange([value length]-2, 2)];
+
+		valueLength += [value length];
+
+		// Close this VALUES group and set up the next one if appropriate
+		if ( rowCounter != penultimateRowIndex ) {
+			// Add a new INSERT starter command every ~250k of data.
+			if ( valueLength > 250000 ) {
+				[result appendString:value];
+				[result appendString:[NSString stringWithFormat:@");\n\nINSERT INTO `%@` (%@)\nVALUES\n", 
+					(selectedTable == nil)?@"<table>":selectedTable, [tbHeader componentsJoinedAndBacktickQuoted]]];
+				[value setString:@""];
+				valueLength = 0;
+			} else {
+				[value appendString:@"),\n"];
 			}
-			[result appendString: [ NSString stringWithFormat:@"),\n"]];
-			
-			row = [rowIndexes indexGreaterThanIndex: row];
-			
-		} //end for each row
-		
-		if ( [result length] > 3 )
-		{
-			[result deleteCharactersInRange:NSMakeRange([result length]-2, 2)];
+		} else {
+			[value appendString:@"),\n"];
+			[result appendString:value];
 		}
-		
-		[result appendString:@";\n"];
-		
-		return result;
-	}
-	else
-	{
-		return nil;
-	}
+
+		// next selected row
+		// row = [rowIndexes indexGreaterThanIndex: row];
+
+	} //end for each row
+	
+	// delete last ",/n"
+	if ( [result length] > 3 )
+		[result deleteCharactersInRange:NSMakeRange([result length]-2, 2)];
+
+	[result appendString:@";\n"];
+	
+	return result;
 }
 
+
 //get dragged rows a string of newline separated lines of tab separated fields
 //the value in each field is from the objects description method
 - (NSString *)draggedRowsAsTabString:(NSArray *)rows
@@ -310,10 +387,16 @@ int MENU_EDIT_COPY_AS_SQL      = 2002;
 	}
 }
 
-- (void)setTableInfoWithColumns:(NSArray *)columnDefs withTable:(NSString *)aTable
+/*
+ * Init self with data coming from the table content view. Mainly used for copying data properly.
+ */
+- (void)setTableInstance:(id)anInstance withTableData:(id)theTableData withColumns:(NSArray *)columnDefs withTableName:(NSString *)aTableName withConnection:(id)aMySqlConnection
 {
 	columnDefinitions = [[NSArray arrayWithArray:columnDefs] retain];
-	selectedTable = aTable;
+	selectedTable     = aTableName;
+	tableData         = theTableData;
+	mySQLConnection   = aMySqlConnection;
+	tableInstance     = anInstance;
 }
 
 @end