diff options
| -rw-r--r-- | Source/SPCustomQuery.m | 13 | ||||
| -rw-r--r-- | Source/SPDataImport.h | 2 | ||||
| -rw-r--r-- | Source/SPDataImport.m | 25 |
3 files changed, 34 insertions, 6 deletions
diff --git a/Source/SPCustomQuery.m b/Source/SPCustomQuery.m index bed5506e..26810323 100644 --- a/Source/SPCustomQuery.m +++ b/Source/SPCustomQuery.m @@ -1980,11 +1980,13 @@ if (aTableView == customQueryView) { NSDictionary *columnDefinition; + NSString *columnTypeGroup; // Retrieve the column defintion for(id c in cqColumnDefinition) { if([[c objectForKey:@"datacolumnindex"] isEqualToNumber:[aTableColumn identifier]]) { columnDefinition = [NSDictionary dictionaryWithDictionary:c]; + columnTypeGroup = [columnDefinition objectForKey:@"typegrouping"]; break; } } @@ -2018,13 +2020,16 @@ } else { if ( [[anObject description] isEqualToString:@"CURRENT_TIMESTAMP"] ) { newObject = @"CURRENT_TIMESTAMP"; - } else if([anObject isEqualToString:[prefs stringForKey:SPNullValue]]) { + } else if ([anObject isEqualToString:[prefs stringForKey:SPNullValue]] + || (([columnTypeGroup isEqualToString:@"float"] || [columnTypeGroup isEqualToString:@"integer"]) + && [[anObject description] isEqualToString:@""])) + { newObject = @"NULL"; - } else if ([[columnDefinition objectForKey:@"typegrouping"] isEqualToString:@"geometry"]) { + } else if ([columnTypeGroup isEqualToString:@"geometry"]) { newObject = [(NSString*)anObject getGeomFromTextString]; - } else if ([[columnDefinition objectForKey:@"typegrouping"] isEqualToString:@"bit"]) { + } else if ([columnTypeGroup isEqualToString:@"bit"]) { newObject = [NSString stringWithFormat:@"b'%@'", ((![[anObject description] length] || [[anObject description] isEqualToString:@"0"]) ? @"0" : [anObject description])]; - } else if ([[columnDefinition objectForKey:@"typegrouping"] isEqualToString:@"date"] + } else if ([columnTypeGroup isEqualToString:@"date"] && [[anObject description] isEqualToString:@"NOW()"]) { newObject = @"NOW()"; } else { diff --git a/Source/SPDataImport.h b/Source/SPDataImport.h index f6e51299..b9810201 100644 --- a/Source/SPDataImport.h +++ b/Source/SPDataImport.h @@ -107,6 +107,8 @@ NSMutableIndexSet *geometryFieldsMapIndex; NSMutableArray *bitFields; NSMutableIndexSet *bitFieldsMapIndex; + NSMutableArray *nullableNumericFields; + NSMutableIndexSet *nullableNumericFieldsMapIndex; NSSavePanel *currentExportPanel; } diff --git a/Source/SPDataImport.m b/Source/SPDataImport.m index 8db9f9b7..d1b2b67a 100644 --- a/Source/SPDataImport.m +++ b/Source/SPDataImport.m @@ -69,6 +69,8 @@ geometryFieldsMapIndex = [[NSMutableIndexSet alloc] init]; bitFields = [[NSMutableArray alloc] init]; bitFieldsMapIndex = [[NSMutableIndexSet alloc] init]; + nullableNumericFields = [[NSMutableArray alloc] init]; + nullableNumericFieldsMapIndex = [[NSMutableIndexSet alloc] init]; fieldMappingArray = nil; fieldMappingGlobalValueArray = nil; fieldMappingTableColumnNames = nil; @@ -730,6 +732,8 @@ [geometryFieldsMapIndex removeAllIndexes]; [bitFields removeAllObjects]; [bitFieldsMapIndex removeAllIndexes]; + [nullableNumericFields removeAllObjects]; + [nullableNumericFieldsMapIndex removeAllIndexes]; // Start the notification timer to allow notifications to be shown even if frontmost for long queries [[SPGrowlController sharedGrowlController] setVisibilityForNotificationName:@"Import Finished"]; @@ -938,6 +942,16 @@ [[NSApp onMainThread] beginSheet:singleProgressSheet modalForWindow:[tableDocumentInstance parentWindow] modalDelegate:self didEndSelector:nil contextInfo:nil]; [[singleProgressSheet onMainThread] makeKeyWindow]; + // Set up index sets for use during row enumeration + for (i = 0; i < [fieldMappingArray count]; i++) { + if ([NSArrayObjectAtIndex(fieldMapperOperator, i) integerValue] == 0) { + NSString *fieldName = NSArrayObjectAtIndex(fieldMappingTableColumnNames, i); + if ([nullableNumericFields containsObject:fieldName]) { + [nullableNumericFieldsMapIndex addIndex:i]; + } + } + } + // Set up the field names import string for INSERT or REPLACE INTO [insertBaseString appendString:csvImportHeaderString]; if(!importMethodIsUpdate) { @@ -1287,12 +1301,15 @@ targetTableDetails = [selectedTableData informationForTable:selectedTableTarget]; [selectedTableData release]; - // Store all field names which are of typegrouping 'geometry' and 'bit' + // Store all field names which are of typegrouping 'geometry' and 'bit', and check if + // numeric columns can hold NULL values to map empty strings to. for(NSDictionary *field in [targetTableDetails objectForKey:@"columns"]) { if([[field objectForKey:@"typegrouping"] isEqualToString:@"geometry"]) [geometryFields addObject:[field objectForKey:@"name"]]; if([[field objectForKey:@"typegrouping"] isEqualToString:@"bit"]) [bitFields addObject:[field objectForKey:@"name"]]; + if(([[field objectForKey:@"typegrouping"] isEqualToString:@"float"] || [[field objectForKey:@"typegrouping"] isEqualToString:@"integer"]) && [[field objectForKey:@"null"] boolValue]) + [nullableNumericFields addObject:[field objectForKey:@"name"]]; } [importFieldNamesSwitch setState:[fieldMapperController importFieldNamesHeader]]; @@ -1509,8 +1526,10 @@ if ([cellData isSPNotLoaded]) cellData = NSArrayObjectAtIndex(fieldMappingTableDefaultValues, i); - if (cellData == [NSNull null]) { + // Insert a NULL if the cell is an NSNull, or is a nullable numeric field and empty + if (cellData == [NSNull null] || ([nullableNumericFieldsMapIndex containsIndex:i] && [[cellData description] isEqualToString:@""])) { [valueString appendString:@"NULL"]; + } else { // Apply GeomFromText() for each geometry field if([geometryFields count] && [geometryFieldsMapIndex containsIndex:i]) { @@ -1674,7 +1693,9 @@ if (geometryFields) [geometryFields release]; if (geometryFieldsMapIndex) [geometryFieldsMapIndex release]; if (bitFields) [bitFields release]; + if (nullableNumericFields) [nullableNumericFields release]; if (bitFieldsMapIndex) [bitFieldsMapIndex release]; + if (nullableNumericFieldsMapIndex) [nullableNumericFieldsMapIndex release]; if (lastFilename) [lastFilename release]; if (prefs) [prefs release]; |