aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorBibiko <bibiko@eva.mpg.de>2010-10-31 17:41:19 +0000
committerBibiko <bibiko@eva.mpg.de>2010-10-31 17:41:19 +0000
commit7fbc246fbcbfbe819c27d561650aacd312f2d6b0 (patch)
tree5238ce87303e0951e3aaf8fefc6816b9896d78d4
parente3f9028ab739d9e973dd97345605c2abc2a79739 (diff)
downloadsequelpro-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
-rw-r--r--Source/SPDataImport.h2
-rw-r--r--Source/SPDataImport.m49
-rw-r--r--Source/SPFieldMapperController.m4
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;