diff options
author | Bibiko <bibiko@eva.mpg.de> | 2010-10-31 17:41:19 +0000 |
---|---|---|
committer | Bibiko <bibiko@eva.mpg.de> | 2010-10-31 17:41:19 +0000 |
commit | 7fbc246fbcbfbe819c27d561650aacd312f2d6b0 (patch) | |
tree | 5238ce87303e0951e3aaf8fefc6816b9896d78d4 /Source | |
parent | e3f9028ab739d9e973dd97345605c2abc2a79739 (diff) | |
download | sequelpro-7fbc246fbcbfbe819c27d561650aacd312f2d6b0.tar.gz sequelpro-7fbc246fbcbfbe819c27d561650aacd312f2d6b0.tar.bz2 sequelpro-7fbc246fbcbfbe819c27d561650aacd312f2d6b0.zip |
• CSV import now supports to import sequences of 0 and 1 into BIT fields
• fixed several issues while importing a CSV file if this file contains empty lines or if a line has less columns as header
- fixed issue for matching header names
- fixed issue for user-defined SQL functions while importing; in such a case set the value to @"" to get the defaults
Diffstat (limited to 'Source')
-rw-r--r-- | Source/SPDataImport.h | 2 | ||||
-rw-r--r-- | Source/SPDataImport.m | 49 | ||||
-rw-r--r-- | 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; |