aboutsummaryrefslogtreecommitdiffstats
path: root/Source
diff options
context:
space:
mode:
Diffstat (limited to 'Source')
-rw-r--r--Source/SPFieldMapperController.m15
-rw-r--r--Source/TableDump.h2
-rw-r--r--Source/TableDump.m168
3 files changed, 152 insertions, 33 deletions
diff --git a/Source/SPFieldMapperController.m b/Source/SPFieldMapperController.m
index 8f13b6bb..e6fafe1b 100644
--- a/Source/SPFieldMapperController.m
+++ b/Source/SPFieldMapperController.m
@@ -162,7 +162,7 @@
- (NSString*)selectedTableTarget
{
- return [tableTargetPopup titleOfSelectedItem];
+ return ([tableTargetPopup titleOfSelectedItem] == nil) ? @"" : [tableTargetPopup titleOfSelectedItem];
}
- (NSArray*)fieldMapperOperator
@@ -172,7 +172,7 @@
- (NSString*)selectedImportMethod
{
- return [importMethodPopup titleOfSelectedItem];
+ return ([importMethodPopup titleOfSelectedItem] == nil) ? @"" : [importMethodPopup titleOfSelectedItem];
}
- (NSArray*)fieldMappingArray
@@ -213,6 +213,9 @@
([delayedCheckBox state] == NSOnState) ? @"DELAYED " : @""
];
}
+ else if([[importMethodPopup titleOfSelectedItem] isEqualToString:@"UPDATE"]) {
+ return [NSString stringWithFormat:@"UPDATE %@ SET ", [[self selectedTableTarget] backtickQuotedString]];
+ }
return @"";
}
@@ -330,6 +333,7 @@
} else {
[advancedButton setEnabled:NO];
[addRemainingDataSwitch setHidden:NO];
+ [addRemainingDataSwitch setEnabled:NO]; // TODO HansJB
}
[self validateImportButton];
@@ -383,7 +387,7 @@
[fieldMapperTableView reloadData];
- [recordCountLabel setStringValue:[NSString stringWithFormat:@"%ld of %@%lu records", (long)(fieldMappingCurrentRow+1), fieldMappingImportArrayIsPreview?@"first ":@"", (unsigned long)[fieldMappingImportArray count]]];
+ [recordCountLabel setStringValue:[NSString stringWithFormat:NSLocalizedString(@"%ld of %@%lu records", @"%ld of %@%lu records"), (long)(fieldMappingCurrentRow+1), fieldMappingImportArrayIsPreview?@"first ":@"", (unsigned long)[fieldMappingImportArray count]]];
// enable/disable buttons
[rowDownButton setEnabled:(fieldMappingCurrentRow != 0)];
@@ -691,7 +695,6 @@
break;
}
}
- enableImportButton = NO; // TODO HansJB WIP
}
[importButton setEnabled:enableImportButton];
}
@@ -897,8 +900,8 @@
[fieldMappingArray replaceObjectAtIndex:rowIndex withObject:anObject];
- // If user _changed_ the csv file column set the operator to doImport
- if([(NSNumber*)anObject integerValue] > -1)
+ // If user _changed_ the csv file column set the operator to doImport if not set to =
+ if([(NSNumber*)anObject integerValue] > -1 && NSArrayObjectAtIndex(fieldMappingOperatorArray, rowIndex) != isEqual)
[fieldMappingOperatorArray replaceObjectAtIndex:rowIndex withObject:doImport];
[self validateImportButton];
diff --git a/Source/TableDump.h b/Source/TableDump.h
index 3855fe57..8f785f72 100644
--- a/Source/TableDump.h
+++ b/Source/TableDump.h
@@ -116,6 +116,7 @@ typedef enum _SPExportModes {
BOOL fieldMappingArrayHasGlobalVariables;
BOOL csvImportMethodHasTail;
BOOL insertRemainingRowsAfterUpdate;
+ BOOL importMethodIsUpdate;
NSUInteger exportMode;
NSUserDefaults *prefs;
@@ -144,6 +145,7 @@ typedef enum _SPExportModes {
- (void)openPanelDidEnd:(NSOpenPanel *)sheet returnCode:(NSInteger)returnCode contextInfo:(NSString *)contextInfo;
- (BOOL) buildFieldMappingArrayWithData:(NSArray *)importData isPreview:(BOOL)dataIsPreviewData ofSoureFile:(NSString*)filename;
- (NSString *) mappedValueStringForRowArray:(NSArray *)csvRowArray;
+- (NSString *) mappedUpdateSetStatementStringForRowArray:(NSArray *)csvRowArray;
// Export methods
- (BOOL)dumpSelectedTablesAsSqlToFileHandle:(NSFileHandle *)fileHandle;
diff --git a/Source/TableDump.m b/Source/TableDump.m
index 542d52ba..4ee19c57 100644
--- a/Source/TableDump.m
+++ b/Source/TableDump.m
@@ -954,17 +954,19 @@
// Set up the field names import string for INSERT or REPLACE INTO
[insertBaseString appendString:csvImportHeaderString];
- [insertBaseString appendString:[selectedTableTarget backtickQuotedString]];
- [insertBaseString appendString:@" ("];
- insertBaseStringHasEntries = NO;
- for (i = 0; i < [fieldMappingArray count]; i++) {
- if ([NSArrayObjectAtIndex(fieldMapperOperator, i) integerValue] == 0) {
- if (insertBaseStringHasEntries) [insertBaseString appendString:@","];
- else insertBaseStringHasEntries = YES;
- [insertBaseString appendString:[NSArrayObjectAtIndex(fieldMappingTableColumnNames, i) backtickQuotedString]];
+ if(!importMethodIsUpdate) {
+ [insertBaseString appendString:[selectedTableTarget backtickQuotedString]];
+ [insertBaseString appendString:@" ("];
+ insertBaseStringHasEntries = NO;
+ for (i = 0; i < [fieldMappingArray count]; i++) {
+ if ([NSArrayObjectAtIndex(fieldMapperOperator, i) integerValue] == 0) {
+ if (insertBaseStringHasEntries) [insertBaseString appendString:@","];
+ else insertBaseStringHasEntries = YES;
+ [insertBaseString appendString:[NSArrayObjectAtIndex(fieldMappingTableColumnNames, i) backtickQuotedString]];
+ }
}
+ [insertBaseString appendString:@") VALUES\n"];
}
- [insertBaseString appendString:@") VALUES\n"];
// Remove the header row from the data set if appropriate
if ([importFieldNamesSwitch state] == NSOnState) {
@@ -983,26 +985,53 @@
|| (!csvRowArray && allDataRead && [parsedRows count]))
{
if (progressCancelled) break;
- query = [[NSMutableString alloc] initWithString:insertBaseString];
csvRowsThisQuery = 0;
- for (i = 0; i < csvRowsPerQuery && i < [parsedRows count]; i++) {
- if (i > 0) [query appendString:@",\n"];
- [query appendString:[self mappedValueStringForRowArray:[parsedRows objectAtIndex:i]]];
- csvRowsThisQuery++;
- if ([query length] > 250000) break;
- }
+ if(!importMethodIsUpdate) {
+ query = [[NSMutableString alloc] initWithString:insertBaseString];
+ for (i = 0; i < csvRowsPerQuery && i < [parsedRows count]; i++) {
+ if (i > 0) [query appendString:@",\n"];
+ [query appendString:[self mappedValueStringForRowArray:[parsedRows objectAtIndex:i]]];
+ csvRowsThisQuery++;
+ if ([query length] > 250000) break;
+ }
- // Perform the query
- if(csvImportMethodHasTail)
- [mySQLConnection queryString:[NSString stringWithFormat:@"%@ %@", query, csvImportTailString]];
- else
- [mySQLConnection queryString:query];
- [query release];
+ // Perform the query
+ if(csvImportMethodHasTail)
+ [mySQLConnection queryString:[NSString stringWithFormat:@"%@ %@", query, csvImportTailString]];
+ else
+ [mySQLConnection queryString:query];
+ [query release];
+ } else {
+ for (i = 0; i < [parsedRows count]; i++) {
+ if (progressCancelled) break;
+ query = [[NSMutableString alloc] initWithString:insertBaseString];
+ [query appendString:[self mappedUpdateSetStatementStringForRowArray:[parsedRows objectAtIndex:i]]];
+
+ // Perform the query
+ if(csvImportMethodHasTail)
+ [mySQLConnection queryString:[NSString stringWithFormat:@"%@ %@", query, csvImportTailString]];
+ else
+ [mySQLConnection queryString:query];
+ [query release];
+ if ( ![[mySQLConnection getLastErrorMessage] isEqualToString:@""] ) {
+ [tableDocumentInstance showConsole:nil];
+ [errors appendString:[NSString stringWithFormat:
+ NSLocalizedString(@"[ERROR in row %ld] %@\n", @"error text when reading of csv file gave errors"),
+ (long)(rowsImported+1),[mySQLConnection getLastErrorMessage]]];
+ }
+ rowsImported++;
+ csvRowsThisQuery++;
+ [singleProgressBar setDoubleValue:[[parsePositions objectAtIndex:i] doubleValue]];
+ [singleProgressText setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Imported %@ of %@", @"SQL import progress text"),
+ [NSString stringForByteSize:[[parsePositions objectAtIndex:i] longValue]], [NSString stringForByteSize:fileTotalLength]]];
+ }
+ }
// If an error occurred, run the queries individually to get exact line errors
- if (![[mySQLConnection getLastErrorMessage] isEqualToString:@""]) {
+ if (!importMethodIsUpdate && ![[mySQLConnection getLastErrorMessage] isEqualToString:@""]) {
[tableDocumentInstance showConsole:nil];
for (i = 0; i < csvRowsThisQuery; i++) {
+ if (progressCancelled) break;
query = [[NSMutableString alloc] initWithString:insertBaseString];
[query appendString:[self mappedValueStringForRowArray:[parsedRows objectAtIndex:i]]];
@@ -1053,6 +1082,7 @@
if(csvImportHeaderString) [csvImportHeaderString release]; csvImportHeaderString = nil;
if(fieldMappingArray) [fieldMappingArray release]; fieldMappingArray = nil;
if(fieldMappingGlobalValueArray) [fieldMappingGlobalValueArray release]; fieldMappingGlobalValueArray = nil;
+ if(fieldMappingTableColumnNames) [fieldMappingTableColumnNames release]; fieldMappingTableColumnNames = nil;
if(fieldMapperOperator) [fieldMapperOperator release]; fieldMapperOperator = nil;
[importPool drain];
[tableDocumentInstance setQueryMode:SPInterfaceQueryMode];
@@ -1189,17 +1219,30 @@
while (fieldMapperSheetStatus == 1)
usleep(100000);
- // Get mapping settings
+ // Get mapping settings and preset some global variables
fieldMapperOperator = [[NSArray arrayWithArray:[fieldMapperController fieldMapperOperator]] retain];
fieldMappingArray = [[NSArray arrayWithArray:[fieldMapperController fieldMappingArray]] retain];
selectedTableTarget = [NSString stringWithString:[fieldMapperController selectedTableTarget]];
selectedImportMethod = [NSString stringWithString:[fieldMapperController selectedImportMethod]];
- fieldMappingTableColumnNames = [NSArray arrayWithArray:[fieldMapperController fieldMappingTableColumnNames]];
+ fieldMappingTableColumnNames = [[NSArray arrayWithArray:[fieldMapperController fieldMappingTableColumnNames]] retain];
fieldMappingGlobalValueArray = [[NSArray arrayWithArray:[fieldMapperController fieldMappingGlobalValueArray]] retain];
csvImportHeaderString = [[NSString stringWithString:[fieldMapperController importHeaderString]] retain];
csvImportTailString = [[NSString stringWithString:[fieldMapperController onupdateString]] retain];
csvImportMethodHasTail = ([csvImportTailString length] == 0) ? NO : YES;
insertRemainingRowsAfterUpdate = [fieldMapperController insertRemainingRowsAfterUpdate];
+ importMethodIsUpdate = ([selectedImportMethod isEqualToString:@"UPDATE"]) ? YES : NO;
+
+ // Error checking
+ if( ![fieldMapperOperator count]
+ || ![fieldMappingArray count]
+ || ![selectedImportMethod length]
+ || ![selectedTableTarget length]
+ || ![csvImportHeaderString length])
+ {
+ if(fieldMapperController) [fieldMapperController release];
+ NSBeep();
+ return FALSE;
+ }
if([fieldMappingImportArray count] && [fieldMappingGlobalValueArray count] > [NSArrayObjectAtIndex(fieldMappingImportArray,0) count])
fieldMappingArrayHasGlobalVariables = YES;
@@ -1215,6 +1258,7 @@
return FALSE;
}
+
- (void)fieldMapperDidEndSheet:(NSWindow *)sheet returnCode:(NSInteger)returnCode contextInfo:(void *)contextInfo
{
[sheet orderOut:self];
@@ -1222,6 +1266,73 @@
}
/*
+ * Construct the SET and WHERE clause for a CSV row, based on the field mapping array
+ * for the import method "UPDATE".
+ */
+- (NSString *) mappedUpdateSetStatementStringForRowArray:(NSArray *)csvRowArray
+{
+
+ NSMutableString *setString = [NSMutableString stringWithString:@""];
+ NSMutableString *whereString = [NSMutableString stringWithString:@"WHERE "];
+
+ NSInteger i, j;
+ NSInteger mapColumn;
+ id cellData;
+ NSInteger mappingArrayCount = [fieldMappingArray count];
+
+ for (i = 0; i < mappingArrayCount; i++) {
+
+ // Skip unmapped columns
+ if ([NSArrayObjectAtIndex(fieldMapperOperator, i) integerValue] == 1 ) continue;
+
+ mapColumn = [NSArrayObjectAtIndex(fieldMappingArray, i) integerValue];
+
+ // SET clause
+ if ([NSArrayObjectAtIndex(fieldMapperOperator, i) integerValue] == 0 ) {
+ if ([setString length] > 1) [setString appendString:@","];
+ [setString appendString:[NSArrayObjectAtIndex(fieldMappingTableColumnNames, i) backtickQuotedString]];
+ [setString appendString:@"="];
+ // Append the data
+ // - check for global values
+ if(fieldMappingArrayHasGlobalVariables && mapColumn >= [csvRowArray count])
+ cellData = NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn);
+ else
+ cellData = NSArrayObjectAtIndex(csvRowArray, mapColumn);
+
+ if (cellData == [NSNull null]) {
+ [setString appendString:@"NULL"];
+ } else {
+ [setString appendString:@"'"];
+ [setString appendString:[mySQLConnection prepareString:cellData]];
+ [setString appendString:@"'"];
+ }
+ }
+ // WHERE clause
+ else if ([NSArrayObjectAtIndex(fieldMapperOperator, i) integerValue] == 2 )
+ {
+ if ([whereString length] > 7) [whereString appendString:@" AND "];
+ [whereString appendString:[NSArrayObjectAtIndex(fieldMappingTableColumnNames, i) backtickQuotedString]];
+ // Append the data
+ // - check for global values
+ if(fieldMappingArrayHasGlobalVariables && mapColumn >= [csvRowArray count])
+ cellData = NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn);
+ else
+ cellData = NSArrayObjectAtIndex(csvRowArray, mapColumn);
+
+ if (cellData == [NSNull null]) {
+ [whereString appendString:@" IS NULL"];
+ } else {
+ [whereString appendString:@"="];
+ [whereString appendString:@"'"];
+ [whereString appendString:[mySQLConnection prepareString:cellData]];
+ [whereString appendString:@"'"];
+ }
+ }
+ }
+ return [NSString stringWithFormat:@"%@ %@", setString, whereString];
+}
+
+/*
* Construct the VALUES string for a CSV row, based on the field mapping array - including
* surrounding brackets but not including the VALUES keyword.
*/
@@ -2803,8 +2914,7 @@
[self switchTab:[[exportToolbar items] objectAtIndex:0]];
[exportToolbar setSelectedItemIdentifier:[[[exportToolbar items] objectAtIndex:0] itemIdentifier]];
}
-
-//last but not least
+
- (id)init;
{
self = [super init];
@@ -2812,12 +2922,16 @@
tables = [[NSMutableArray alloc] init];
fieldMappingArray = nil;
fieldMappingGlobalValueArray = nil;
+ fieldMappingTableColumnNames = nil;
fieldMappingImportArray = nil;
csvImportTailString = nil;
csvImportHeaderString = nil;
csvImportMethodHasTail = NO;
fieldMappingImportArrayIsPreview = NO;
fieldMappingArrayHasGlobalVariables = NO;
+ importMethodIsUpdate = NO;
+ insertRemainingRowsAfterUpdate = NO;
+
prefs = nil;
lastFilename = nil;
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-19 13:33:33 +0000