diff options
Diffstat (limited to 'Source/TableDump.m')
| -rw-r--r-- | Source/TableDump.m | 168 |
1 files changed, 141 insertions, 27 deletions
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; |