diff options
| -rw-r--r-- | Source/SPFieldMapperController.m | 15 | ||||
| -rw-r--r-- | Source/TableDump.h | 2 | ||||
| -rw-r--r-- | Source/TableDump.m | 168 |
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; |