From 7fbc246fbcbfbe819c27d561650aacd312f2d6b0 Mon Sep 17 00:00:00 2001 From: Bibiko Date: Sun, 31 Oct 2010 17:41:19 +0000 Subject: =?UTF-8?q?=E2=80=A2=20CSV=20import=20now=20supports=20to=20import?= =?UTF-8?q?=20sequences=20of=200=20and=201=20into=20BIT=20fields=20?= =?UTF-8?q?=E2=80=A2=20fixed=20several=20issues=20while=20importing=20a=20?= =?UTF-8?q?CSV=20file=20if=20this=20file=20contains=20empty=20lines=20or?= =?UTF-8?q?=20if=20a=20line=20has=20less=20columns=20as=20header=20-=20fix?= =?UTF-8?q?ed=20issue=20for=20matching=20header=20names=20-=20fixed=20issu?= =?UTF-8?q?e=20for=20user-defined=20SQL=20functions=20while=20importing;?= =?UTF-8?q?=20in=20such=20a=20case=20set=20the=20value=20to=20@""=20to=20g?= =?UTF-8?q?et=20the=20defaults?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- Source/SPDataImport.h | 2 ++ Source/SPDataImport.m | 49 ++++++++++++++++++++++++++++++++-------- Source/SPFieldMapperController.m | 4 +++- 3 files changed, 45 insertions(+), 10 deletions(-) diff --git a/Source/SPDataImport.h b/Source/SPDataImport.h index 28bf8b04..8ea49fa1 100644 --- a/Source/SPDataImport.h +++ b/Source/SPDataImport.h @@ -103,6 +103,8 @@ NSDictionary *targetTableDetails; NSMutableArray *geometryFields; NSMutableIndexSet *geometryFieldsMapIndex; + NSMutableArray *bitFields; + NSMutableIndexSet *bitFieldsMapIndex; NSSavePanel *currentExportPanel; } diff --git a/Source/SPDataImport.m b/Source/SPDataImport.m index dc22cb0f..f7f3eb4c 100644 --- a/Source/SPDataImport.m +++ b/Source/SPDataImport.m @@ -65,6 +65,8 @@ nibObjectsToRelease = [[NSMutableArray alloc] init]; geometryFields = [[NSMutableArray alloc] init]; geometryFieldsMapIndex = [[NSMutableIndexSet alloc] init]; + bitFields = [[NSMutableArray alloc] init]; + bitFieldsMapIndex = [[NSMutableIndexSet alloc] init]; fieldMappingArray = nil; fieldMappingGlobalValueArray = nil; fieldMappingTableColumnNames = nil; @@ -687,6 +689,8 @@ [geometryFields removeAllObjects]; [geometryFieldsMapIndex removeAllIndexes]; + [bitFields removeAllObjects]; + [bitFieldsMapIndex removeAllIndexes]; // Start the notification timer to allow notifications to be shown even if frontmost for long queries [[SPGrowlController sharedGrowlController] setVisibilityForNotificationName:@"Import Finished"]; @@ -907,6 +911,11 @@ if([geometryFields containsObject:fieldName = NSArrayObjectAtIndex(fieldMappingTableColumnNames, i) ]) [geometryFieldsMapIndex addIndex:i]; [insertBaseString appendString:[fieldName backtickQuotedString]]; + } else if([bitFields count]) { + // Store column index for each bit field to be able to wrap it into b'…' while importing + if([bitFields containsObject:fieldName = NSArrayObjectAtIndex(fieldMappingTableColumnNames, i) ]) + [bitFieldsMapIndex addIndex:i]; + [insertBaseString appendString:[fieldName backtickQuotedString]]; } else { [insertBaseString appendString:[NSArrayObjectAtIndex(fieldMappingTableColumnNames, i) backtickQuotedString]]; } @@ -1220,10 +1229,13 @@ targetTableDetails = [selectedTableData informationForTable:selectedTableTarget]; [selectedTableData release]; - // Store all field names which are of typegrouping 'geometry' - for(NSDictionary *field in [targetTableDetails objectForKey:@"columns"]) + // Store all field names which are of typegrouping 'geometry' and 'bit' + for(NSDictionary *field in [targetTableDetails objectForKey:@"columns"]) { if([[field objectForKey:@"typegrouping"] isEqualToString:@"geometry"]) - [geometryFields addObject:[field objectForKey:@"name"]]; + [geometryFields addObject:[field objectForKey:@"name"]]; + if([[field objectForKey:@"typegrouping"] isEqualToString:@"bit"]) + [bitFields addObject:[field objectForKey:@"name"]]; + } [importFieldNamesSwitch setState:[fieldMapperController importFieldNamesHeader]]; [prefs setBool:[importFieldNamesSwitch state] forKey:SPCSVImportFirstLineIsHeader]; @@ -1277,10 +1289,13 @@ // - check for global values if(fieldMappingArrayHasGlobalVariables && mapColumn >= numberOfImportDataColumns) { NSMutableString *globalVar = [NSMutableString string]; - if([NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn) isKindOfClass:[NSNull class]]) { + id insertItem = NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn); + if([insertItem isKindOfClass:[NSNull class]]) { + [globalVar setString:@"NULL"]; + } else if([insertItem isSPNotLoaded]) { [globalVar setString:@"NULL"]; } else { - [globalVar setString:NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn)]; + [globalVar setString:insertItem]; // Global variables are coming wrapped in ' ' if there're not marked as SQL. // If global variable contains column placeholders $1 etc. replace them. if([globalVar rangeOfString:@"$"].length && [globalVar isMatchedByRegex:re]) { @@ -1292,6 +1307,8 @@ id colStr = NSArrayObjectAtIndex(csvRowArray, colIndex-1); if(colStr == [NSNull null]) [globalVar replaceCharactersInRange:aRange withString:@"NULL"]; + else if([colStr isSPNotLoaded]) + [globalVar replaceCharactersInRange:aRange withString:@""]; else [globalVar replaceCharactersInRange:aRange withString:[NSString stringWithFormat:@"'%@'", [(NSString*)colStr stringByReplacingOccurrencesOfString:@"'" withString:@"\\'"]]]; } else { @@ -1324,10 +1341,13 @@ // - check for global values if(fieldMappingArrayHasGlobalVariables && mapColumn >= numberOfImportDataColumns) { NSMutableString *globalVar = [NSMutableString string]; - if([NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn) isKindOfClass:[NSNull class]]) { + id insertItem = NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn); + if([insertItem isKindOfClass:[NSNull class]]) { + [globalVar setString:@"NULL"]; + } else if([insertItem isSPNotLoaded]) { [globalVar setString:@"NULL"]; } else { - [globalVar setString:NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn)]; + [globalVar setString:insertItem]; // Global variables are coming wrapped in ' ' if there're not marked as SQL. // If global variable contains column placeholders $1 etc. replace them. if([globalVar rangeOfString:@"$"].length && [globalVar isMatchedByRegex:re]) { @@ -1339,6 +1359,8 @@ id colStr = NSArrayObjectAtIndex(csvRowArray, colIndex-1); if(colStr == [NSNull null]) [globalVar replaceCharactersInRange:aRange withString:@"NULL"]; + else if([colStr isSPNotLoaded]) + [globalVar replaceCharactersInRange:aRange withString:@""]; else [globalVar replaceCharactersInRange:aRange withString:[NSString stringWithFormat:@"'%@'", [(NSString*)colStr stringByReplacingOccurrencesOfString:@"'" withString:@"\\'"]]]; } else { @@ -1393,10 +1415,13 @@ // - check for global values if(fieldMappingArrayHasGlobalVariables && mapColumn >= numberOfImportDataColumns) { NSMutableString *globalVar = [NSMutableString string]; - if([NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn) isKindOfClass:[NSNull class]]) { + id insertItem = NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn); + if([insertItem isKindOfClass:[NSNull class]]) { + [globalVar setString:@"NULL"]; + } else if([insertItem isSPNotLoaded]) { [globalVar setString:@"NULL"]; } else { - [globalVar setString:NSArrayObjectAtIndex(fieldMappingGlobalValueArray, mapColumn)]; + [globalVar setString:insertItem]; // Global variables are coming wrapped in ' ' if there're not marked as SQL. // If global variable contains column placeholders $1 etc. replace them by escaped 'csv content' or NULL. if([globalVar rangeOfString:@"$"].length && [globalVar isMatchedByRegex:re]) { @@ -1408,6 +1433,8 @@ id colStr = NSArrayObjectAtIndex(csvRowArray, colIndex-1); if(colStr == [NSNull null]) [globalVar replaceCharactersInRange:aRange withString:@"NULL"]; + else if([colStr isSPNotLoaded]) + [globalVar replaceCharactersInRange:aRange withString:@""]; else [globalVar replaceCharactersInRange:aRange withString:[NSString stringWithFormat:@"'%@'", [(NSString*)colStr stringByReplacingOccurrencesOfString:@"'" withString:@"\\'"]]]; } else { @@ -1430,6 +1457,8 @@ // Apply GeomFromText() for each geometry field if([geometryFields count] && [geometryFieldsMapIndex containsIndex:i]) { [valueString appendString:[(NSString*)cellData getGeomFromTextString]]; + } else if([bitFields count] && [bitFieldsMapIndex containsIndex:i]) { + [valueString appendFormat:@"b'%@'", [mySQLConnection prepareString:cellData]]; } else { [valueString appendFormat:@"'%@'", [mySQLConnection prepareString:cellData]]; } @@ -1586,6 +1615,8 @@ if (fieldMappingImportArray) [fieldMappingImportArray release]; if (geometryFields) [geometryFields release]; if (geometryFieldsMapIndex) [geometryFieldsMapIndex release]; + if (bitFields) [bitFields release]; + if (bitFieldsMapIndex) [bitFieldsMapIndex release]; if (lastFilename) [lastFilename release]; if (prefs) [prefs release]; diff --git a/Source/SPFieldMapperController.m b/Source/SPFieldMapperController.m index cbaed536..0be1c4d4 100644 --- a/Source/SPFieldMapperController.m +++ b/Source/SPFieldMapperController.m @@ -1370,7 +1370,9 @@ static const NSString *SPTableViewSqlColumnID = @"sql"; CGFloat minDist = 1e6; NSInteger minIndex = 0; for(j=0; j < [fileHeaderNames count]; j++) { - NSString *headerName = [NSArrayObjectAtIndex(fileHeaderNames,j) lowercaseString]; + id fileHeaderName = NSArrayObjectAtIndex(fileHeaderNames,j); + if([fileHeaderName isKindOfClass:[NSNull class]] || [fileHeaderName isSPNotLoaded]) continue; + NSString *headerName = [(NSString*)fileHeaderName lowercaseString]; CGFloat dist = [[NSArrayObjectAtIndex(tableHeaderNames,i) lowercaseString] levenshteinDistanceWithWord:headerName]; if(dist < minDist && ![matchedHeaderNames containsObject:headerName]) { minDist = dist; -- cgit v1.2.3