• CSV Import Field Mapper

- added Advanced sheet for setting IGNORE, DELAYED, ON DUPLICATE KEY UPDATE '<string>' for INSERT/REPLACE INTO
• if during import CSV Import an error occurred show SP Console

5 files changed, 138 insertions, 10 deletions

diff --git a/Source/SPFieldMapperController.h b/Source/SPFieldMapperController.h
index 4a6441ca..5bca61d0 100644
--- a/Source/SPFieldMapperController.h
+++ b/Source/SPFieldMapperController.h
@@ -24,6 +24,7 @@
 
 #import <Cocoa/Cocoa.h>
 #import <MCPKit/MCPKit.h>
+#import "CMTextView.h"
 
 @interface SPFieldMapperController : NSWindowController {
 
@@ -46,6 +47,14 @@
 	IBOutlet NSButton *insertNULLValueButton;
 	IBOutlet id replaceAfterSavingCheckBox;
 
+	IBOutlet id advancedSheet;
+	IBOutlet id ignoreCheckBox;
+	IBOutlet id delayedCheckBox;
+	IBOutlet id onupdateCheckBox;
+	IBOutlet CMTextView *onupdateTextView;
+	IBOutlet id advancedButton;
+
+
 	id theDelegate;
 	id fieldMappingImportArray;
 
@@ -93,6 +102,8 @@
 - (NSArray*)fieldMappingTableColumnNames;
 - (NSArray*)fieldMappingGlobalValueArray;
 - (BOOL)importFieldNamesHeader;
+- (NSString*)onupdateString;
+- (NSString*)importHeaderString;
 
 // IBAction methods
 - (IBAction)changeTableTarget:(id)sender;
@@ -101,6 +112,7 @@
 - (IBAction)changeHasHeaderCheckbox:(id)sender;
 - (IBAction)stepRow:(id)sender;
 - (IBAction)addGlobalSourceVariable:(id)sender;
+- (IBAction)openAdvancedSheet:(id)sender;
 - (IBAction)closeSheet:(id)sender;
 - (IBAction)goBackToFileChooser:(id)sender;
 
@@ -108,6 +120,9 @@
 - (IBAction)removeGlobalValue:(id)sender;
 - (IBAction)insertNULLValue:(id)sender;
 - (IBAction)closeGlobalValuesSheet:(id)sender;
+- (IBAction)closeAdvancedSheet:(id)sender;
+- (IBAction)advancedCheckboxValidation:(id)sender;
+
 
 // Others
 - (void)matchHeaderNames;
diff --git a/Source/SPFieldMapperController.m b/Source/SPFieldMapperController.m
index 616497b4..ccf94f99 100644
--- a/Source/SPFieldMapperController.m
+++ b/Source/SPFieldMapperController.m
@@ -192,6 +192,30 @@
 	return ([importFieldNamesHeaderSwitch state] == NSOnState)?YES:NO;
 }
 
+- (NSString*)importHeaderString
+{
+	if([[importMethodPopup titleOfSelectedItem] isEqualToString:@"INSERT"]) {
+		return [NSString stringWithFormat:@"INSERT %@%@INTO ", 
+			([delayedCheckBox state] == NSOnState) ? @"DELAYED " : @"",
+			([ignoreCheckBox state] == NSOnState) ? @"IGNORE " : @""
+			];
+	}
+	else if([[importMethodPopup titleOfSelectedItem] isEqualToString:@"REPLACE"]) {
+		return [NSString stringWithFormat:@"REPLACE %@INTO ", 
+			([delayedCheckBox state] == NSOnState) ? @"DELAYED " : @""
+			];
+	}
+	return @"";
+}
+
+- (NSString*)onupdateString
+{
+	if([onupdateCheckBox state] == NSOnState && [[onupdateTextView string] length])
+		return [NSString stringWithFormat:@"ON DUPLICATE KEY UPDATE %@", [onupdateTextView string]];
+	else
+		return @"";
+}
+
 #pragma mark -
 #pragma mark IBAction methods
 
@@ -278,10 +302,13 @@
 	NSInteger i;
 	// If operator is set to = for UPDATE method replace it by doNotImport
 	if(![[importMethodPopup titleOfSelectedItem] isEqualToString:@"UPDATE"]) {
+		[advancedButton setEnabled:YES];
 		for(i=0; i<[fieldMappingTableColumnNames count]; i++) {
 			if([fieldMappingOperatorArray objectAtIndex:i] == isEqual)
 				[fieldMappingOperatorArray replaceObjectAtIndex:i withObject:doNotImport];
 		}
+	} else {
+		[advancedButton setEnabled:NO];
 	}
 
 	[self updateFieldMappingOperatorOptions];
@@ -351,9 +378,12 @@
 	[theDelegate importFile];
 }
 
+#pragma mark -
+#pragma mark Global Value Sheet
+
 - (IBAction)addGlobalSourceVariable:(id)sender
 {
-	[NSApp beginSheet:[globalValuesSheet window] 
+	[NSApp beginSheet:globalValuesSheet 
 		modalForWindow:[self window] 
 		modalDelegate:self 
 		didEndSelector:@selector(sheetDidEnd:returnCode:contextInfo:) contextInfo:nil];
@@ -388,7 +418,7 @@
 	[globalValuesTableView reloadData];
 
 	// Set focus to favorite list to avoid an unstable state
-	[[globalValuesSheet window] makeFirstResponder:globalValuesTableView];
+	[globalValuesSheet makeFirstResponder:globalValuesTableView];
 
 	[removeGlobalValueButton setEnabled:([globalValuesTableView numberOfSelectedRows] > 0)];
 	[insertNULLValueButton setEnabled:([globalValuesTableView numberOfSelectedRows] == 1)];
@@ -411,7 +441,7 @@
 	// Ensure all changes are stored before ordering out
 	[globalValuesTableView validateEditing];
 	if ([globalValuesTableView numberOfSelectedRows] == 1) 
-		[[globalValuesSheet window] makeFirstResponder:globalValuesTableView];
+		[globalValuesSheet makeFirstResponder:globalValuesTableView];
 
 	// Replace the current map pair with the last selected global value
 	if([replaceAfterSavingCheckBox state] == NSOnState && [globalValuesTableView numberOfSelectedRows] == 1) {
@@ -428,7 +458,66 @@
 
 	}
 
-	[NSApp endSheet:[globalValuesSheet window] returnCode:[sender tag]];
+	[NSApp endSheet:globalValuesSheet returnCode:[sender tag]];
+}
+
+#pragma mark -
+#pragma mark Advanced Sheet
+
+- (IBAction)openAdvancedSheet:(id)sender
+{
+
+	if([[importMethodPopup titleOfSelectedItem] isEqualToString:@"REPLACE"]) {
+		[ignoreCheckBox setEnabled:NO];
+		[onupdateCheckBox setEnabled:NO];
+		[delayedCheckBox setEnabled:YES];
+		[onupdateTextView setBackgroundColor:[NSColor lightGrayColor]];
+		[onupdateTextView setEditable:NO];
+	}
+	else if([[importMethodPopup titleOfSelectedItem] isEqualToString:@"INSERT"]) {
+		[ignoreCheckBox setEnabled:YES];
+		[onupdateCheckBox setEnabled:([delayedCheckBox state] == NSOnState) ? NO : YES];
+		[delayedCheckBox setEnabled:([onupdateCheckBox state] == NSOnState) ? NO : YES];
+		if([onupdateCheckBox state] == NSOffState) {
+			[onupdateTextView setEditable:NO];
+			[onupdateTextView setBackgroundColor:[NSColor lightGrayColor]];
+		} else {
+			[onupdateTextView setEditable:YES];
+			[onupdateTextView setBackgroundColor:[NSColor whiteColor]];
+		}
+	}
+	
+	[advancedSheet makeFirstResponder:ignoreCheckBox];
+	
+	[NSApp beginSheet:advancedSheet 
+		modalForWindow:[self window] 
+		modalDelegate:self 
+		didEndSelector:@selector(sheetDidEnd:returnCode:contextInfo:) contextInfo:nil];
+}
+
+- (IBAction)closeAdvancedSheet:(id)sender
+{
+	[NSApp endSheet:advancedSheet returnCode:[sender tag]];
+}
+
+- (IBAction)advancedCheckboxValidation:(id)sender
+{
+
+	if([delayedCheckBox state] == NSOnState)
+		[onupdateCheckBox setState:NO];
+	if([onupdateCheckBox state] == NSOnState) {
+		[delayedCheckBox setState:NO];
+		[onupdateTextView setBackgroundColor:[NSColor whiteColor]];
+		[onupdateTextView setEditable:YES];
+		[advancedSheet makeFirstResponder:onupdateTextView];
+	} else {
+		[onupdateTextView setBackgroundColor:[NSColor lightGrayColor]];
+		[onupdateTextView setEditable:NO];
+	}
+
+	[onupdateCheckBox setEnabled:([delayedCheckBox state] == NSOnState) ? NO : YES];
+	[delayedCheckBox setEnabled:([onupdateCheckBox state] == NSOnState) ? NO : YES];
+
 }
 
 #pragma mark -
@@ -436,8 +525,9 @@
 
 - (void)sheetDidEnd:(NSWindow *)sheet returnCode:(NSInteger)returnCode contextInfo:(void *)contextInfo
 {
-	[[globalValuesSheet window] orderOut:self];
-	[self updateFieldMappingButtonCell];
+	[sheet orderOut:self];
+	if (sheet == globalValuesSheet)
+		[self updateFieldMappingButtonCell];
 }
 
 - (void)matchHeaderNames
diff --git a/Source/TableDocument.h b/Source/TableDocument.h
index ca4bb812..6c03e01a 100644
--- a/Source/TableDocument.h
+++ b/Source/TableDocument.h
@@ -239,6 +239,8 @@
 - (IBAction)backForwardInHistory:(id)sender;
 - (IBAction)showUserManager:(id)sender;
 
+- (void)showConsole:(id)sender;
+
 // Accessor methods
 - (NSString *)host;
 - (NSString *)name;
diff --git a/Source/TableDump.h b/Source/TableDump.h
index 9475649b..14149aa5 100644
--- a/Source/TableDump.h
+++ b/Source/TableDump.h
@@ -110,8 +110,11 @@ typedef enum _SPExportModes {
 	NSString *selectedTableTarget;
 	NSString *selectedImportMethod;
 	NSString *lastFilename;
+	NSString *csvImportHeaderString;
+	NSString *csvImportTailString;
 	NSInteger fieldMapperSheetStatus;
 	BOOL fieldMappingArrayHasGlobalVariables;
+	BOOL csvImportMethodHasTail;
 
 	NSUInteger exportMode;
 	NSUserDefaults *prefs;
diff --git a/Source/TableDump.m b/Source/TableDump.m
index 077b7f4f..46bb10d2 100644
--- a/Source/TableDump.m
+++ b/Source/TableDump.m
@@ -953,7 +953,7 @@
 				[singleProgressSheet makeKeyWindow];
 
 				// Set up the field names import string for INSERT or REPLACE INTO
-				[insertBaseString appendFormat:@"%@ INTO ", selectedImportMethod];
+				[insertBaseString appendFormat:@"%@", csvImportHeaderString];
 				[insertBaseString appendString:[selectedTableTarget backtickQuotedString]];
 				[insertBaseString appendString:@" ("];
 				insertBaseStringHasEntries = NO;
@@ -993,16 +993,26 @@
 				}
 
 				// Perform the query
-				[mySQLConnection queryString:query];
+				if(csvImportMethodHasTail)
+					[mySQLConnection queryString:[NSString stringWithFormat:@"%@ %@", query, csvImportTailString]];
+				else
+					[mySQLConnection queryString:query];
 				[query release];
 
 				// If an error occurred, run the queries individually to get exact line errors
 				if (![[mySQLConnection getLastErrorMessage] isEqualToString:@""]) {
+					[tableDocumentInstance showConsole:nil];
 					for (i = 0; i < csvRowsThisQuery; i++) {
 						query = [[NSMutableString alloc] initWithString:insertBaseString];
 						[query appendString:[self mappedValueStringForRowArray:[parsedRows objectAtIndex:i]]];
-						[mySQLConnection queryString:query];
+
+						// Perform the query
+						if(csvImportMethodHasTail)
+							[mySQLConnection queryString:[NSString stringWithFormat:@"%@ %@", query, csvImportTailString]];
+						else
+							[mySQLConnection queryString:query];
 						[query release];
+
 						if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
 							[errors appendString:[NSString stringWithFormat:
 								NSLocalizedString(@"[ERROR in row %ld] %@\n", @"error text when reading of csv file gave errors"),
@@ -1039,6 +1049,8 @@
 	[csvDataBuffer release];
 	[parsedRows release];
 	[parsePositions release];
+	if(csvImportTailString) [csvImportTailString release]; csvImportTailString = nil;
+	if(csvImportHeaderString) [csvImportHeaderString release]; csvImportHeaderString = nil;
 	if(fieldMappingArray) [fieldMappingArray release]; fieldMappingArray = nil;
 	if(fieldMappingGlobalValueArray) [fieldMappingGlobalValueArray release]; fieldMappingGlobalValueArray = nil;
 	if(fieldMapperOperator) [fieldMapperOperator release]; fieldMapperOperator = nil;
@@ -1184,6 +1196,9 @@
 	selectedImportMethod = [NSString stringWithString:[fieldMapperController selectedImportMethod]];
 	fieldMappingTableColumnNames = [NSArray arrayWithArray:[fieldMapperController fieldMappingTableColumnNames]];
 	fieldMappingGlobalValueArray = [[NSArray arrayWithArray:[fieldMapperController fieldMappingGlobalValueArray]] retain];
+	csvImportHeaderString = [[NSString stringWithString:[fieldMapperController importHeaderString]] retain];
+	csvImportTailString = [[NSString stringWithString:[fieldMapperController onupdateString]] retain];
+	csvImportMethodHasTail = ([csvImportTailString length] == 0) ? NO : YES;
 
 	if([fieldMappingImportArray count] && [fieldMappingGlobalValueArray count] > [NSArrayObjectAtIndex(fieldMappingImportArray,0) count])
 		fieldMappingArrayHasGlobalVariables = YES;
@@ -1433,7 +1448,7 @@
 			
 			// Set up a result set in streaming mode
 			streamingResult = [mySQLConnection streamingQueryString:[NSString stringWithFormat:@"SELECT * FROM %@", [tableName backtickQuotedString]] useLowMemoryBlockingStreaming:([sqlFullStreamingSwitch state] == NSOnState)];
-			fieldNames = [streamingResult fetchFieldNames];			
+			fieldNames = [streamingResult fetchFieldNames];
 			
 			// Update the progress text and set the progress bar back to determinate
 			[singleProgressText setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Table %ld of %lu (%@): Dumping...", @"text showing that app is writing data for table dump"), (long)(i+1), (unsigned long)[selectedTables count], tableName]];
@@ -2797,6 +2812,9 @@
 	fieldMappingArray = nil;
 	fieldMappingGlobalValueArray = nil;
 	fieldMappingImportArray = nil;
+	csvImportTailString = nil;
+	csvImportHeaderString = nil;
+	csvImportMethodHasTail = NO;
 	fieldMappingImportArrayIsPreview = NO;
 	fieldMappingArrayHasGlobalVariables = NO;
 	prefs = nil;