- When importing CSVs, or editing custom query results, set numeric fields to NULL instead of 0 if an empty string is entered. This matches TableContent behaviour and addresses Issue #1034.

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];