Improve handling of BIT fields, including:

- Exporting BIT fields properly in SQL dumps using b'x' notation.
- Properly handling editing of BIT fields in both the content and custom query results views.
- Correctly display BIT fields in the content view, where binary values are zero-padded to the specified length of the field.

(Note, that the new BIT handling logic has only been added to MCPKit's MCPStreamingResult and MCPResult as the latter does not keep a record of the field's length which the new functionality depends on. Needs to be discussed).

3 files changed, 29 insertions, 19 deletions

diff --git a/Source/SPCustomQuery.m b/Source/SPCustomQuery.m
index ebc4f726..02cd25ff 100644
--- a/Source/SPCustomQuery.m
+++ b/Source/SPCustomQuery.m
@@ -1671,7 +1671,7 @@
 				} else if([anObject isEqualToString:[prefs stringForKey:SPNullValue]]) {
 					newObject = @"NULL";
 				} else if ([[columnDefinition objectForKey:@"typegrouping"] isEqualToString:@"bit"]) {
-					newObject = ((![[anObject description] length] || [[anObject description] isEqualToString:@"0"])?@"0":@"1");
+					newObject = [NSString stringWithFormat:@"b'%@'", ((![[anObject description] length] || [[anObject description] isEqualToString:@"0"]) ? @"0" : [anObject description])];
 				} else if ([[columnDefinition objectForKey:@"typegrouping"] isEqualToString:@"date"]
 							&& [[anObject description] isEqualToString:@"NOW()"]) {
 					newObject = @"NOW()";
@@ -1681,7 +1681,7 @@
 			}
 
 			[mySQLConnection queryString:
-				[NSString stringWithFormat:@"UPDATE %@.%@ SET %@.%@.%@=%@ %@ LIMIT 1", 
+				[NSString stringWithFormat:@"UPDATE %@.%@ SET %@.%@.%@ = %@ %@ LIMIT 1", 
 					[[columnDefinition objectForKey:@"db"] backtickQuotedString], [tableForColumn backtickQuotedString],
 					[[columnDefinition objectForKey:@"db"] backtickQuotedString], [tableForColumn backtickQuotedString], [columnName backtickQuotedString], newObject, fieldIDQueryString]];
 			
diff --git a/Source/SPSQLExporter.m b/Source/SPSQLExporter.m
index ad16a119..683e3a48 100644
--- a/Source/SPSQLExporter.m
+++ b/Source/SPSQLExporter.m
@@ -365,11 +365,17 @@
 						}
 						
 						id object = NSArrayObjectAtIndex(row, t);
-						
+												
 						// Add NULL values directly to the output row
 						if ([object isMemberOfClass:[NSNull class]]) {
 							[sqlString appendString:@"NULL"];
 						} 
+						// If the field is off type BIT, the values need a binary prefix of b'x'.
+						else if ([[NSArrayObjectAtIndex([tableDetails objectForKey:@"columns"], t) objectForKey:@"type"] isEqualToString:@"BIT"]) {
+							[sqlString appendString:@"b'"];
+							[sqlString appendString:[object description]];
+							[sqlString appendString:@"'"];
+						}
 						// Add data types directly as hex data
 						else if ([object isKindOfClass:[NSData class]]) {
 							
@@ -400,8 +406,7 @@
 							if ([cellValue length] == 0) {
 								[sqlString appendString:@"''"];
 							} 
-							else {								
-								// If this is a numeric column type, add the number directly.
+							else {	
 								if ([NSArrayObjectAtIndex(tableColumnNumericStatus, t) boolValue]) {
 									[sqlString appendString:cellValue];
 								} 
diff --git a/Source/SPTableContent.m b/Source/SPTableContent.m
index 4af8685e..15f4df31 100644
--- a/Source/SPTableContent.m
+++ b/Source/SPTableContent.m
@@ -2150,7 +2150,7 @@
 				if ([[rowObject description] isEqualToString:@"CURRENT_TIMESTAMP"]) {
 					[rowValue setString:@"CURRENT_TIMESTAMP"];
 				} else if ([[NSArrayObjectAtIndex(dataColumns, i) objectForKey:@"typegrouping"] isEqualToString:@"bit"]) {
-					[rowValue setString:((![[rowObject description] length] || [[rowObject description] isEqualToString:@"0"])?@"0":@"1")];
+					[rowValue setString:[NSString stringWithFormat:@"b'%@'", ((![[rowObject description] length] || [[rowObject description] isEqualToString:@"0"]) ? @"0" : [rowObject description])]];
 				} else if ([[NSArrayObjectAtIndex(dataColumns, i) objectForKey:@"typegrouping"] isEqualToString:@"date"]
 							&& [[rowObject description] isEqualToString:@"NOW()"]) {
 					[rowValue setString:@"NOW()"];
@@ -2161,12 +2161,11 @@
 		}
 		[fieldValues addObject:[NSString stringWithString:rowValue]];
 	}
-	
+		
 	// Use INSERT syntax when creating new rows - no need to do not loaded checking, as all values have been entered
 	if ( isEditingNewRow ) {
 		queryString = [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES (%@)",
-					   [selectedTable backtickQuotedString], [[tableDataInstance columnNames] componentsJoinedAndBacktickQuoted], [fieldValues componentsJoinedByString:@","]];
-
+					   [selectedTable backtickQuotedString], [[tableDataInstance columnNames] componentsJoinedAndBacktickQuoted], [fieldValues componentsJoinedByString:@","]];		
 	// Use UPDATE syntax otherwise
 	} else {
 		BOOL firstCellOutput = NO;
@@ -2179,7 +2178,7 @@
 			if (firstCellOutput) [queryString appendString:@", "];
 			else firstCellOutput = YES;
 
-			[queryString appendString:[NSString stringWithFormat:@"%@=%@",
+			[queryString appendString:[NSString stringWithFormat:@"%@ = %@",
 									   [[NSArrayObjectAtIndex(dataColumns, i) objectForKey:@"name"] backtickQuotedString], [fieldValues objectAtIndex:i]]];
 		}
 		[queryString appendString:[NSString stringWithFormat:@" WHERE %@", [self argumentForRow:-2]]];
@@ -2332,7 +2331,6 @@
 	id tempValue;
 	NSMutableString *value = [NSMutableString string];
 	NSMutableString *argument = [NSMutableString string];
-	// NSString *columnType;
 	NSArray *columnNames;
 	NSInteger i;
 	
@@ -2384,18 +2382,25 @@
 			tempValue = [tableValues cellDataAtRow:row column:[[[tableDataInstance columnWithName:NSArrayObjectAtIndex(keys, i)] objectForKey:@"datacolumnindex"] integerValue]];
 
 		// Otherwise use the oldRow
-		} else {
+		} 
+		else {
 			tempValue = [oldRow objectAtIndex:[[[tableDataInstance columnWithName:NSArrayObjectAtIndex(keys, i)] objectForKey:@"datacolumnindex"] integerValue]];
 		}
 
-		if ( [tempValue isNSNull] ) {
+		if ([tempValue isNSNull]) {
 			[argument appendString:[NSString stringWithFormat:@"%@ IS NULL", [NSArrayObjectAtIndex(keys, i) backtickQuotedString]]];
-		} else if ( [tempValue isSPNotLoaded] ) {
+		} 
+		else if ([tempValue isSPNotLoaded]) {
 			NSLog(@"Exceptional case: SPNotLoaded object found for method “argumentForRow:”!");
 			return @"";
-		} else {
-
-			if ( [tempValue isKindOfClass:[NSData class]] )
+		} 
+		else {					
+			// If the field is of type BIT then it needs a binary prefix
+			if ([[[tableDataInstance columnWithName:NSArrayObjectAtIndex(keys, i)] objectForKey:@"type"] isEqualToString:@"BIT"]) {
+				[value setString:[NSString stringWithFormat:@"b'%@'", [mySQLConnection prepareString:tempValue]]];
+			}
+			// BLOB/TEXT data
+			else if ([tempValue isKindOfClass:[NSData class]])
 				[value setString:[NSString stringWithFormat:@"X'%@'", [mySQLConnection prepareBinaryData:tempValue]]];
 			else
 				[value setString:[NSString stringWithFormat:@"'%@'", [mySQLConnection prepareString:tempValue]]];
@@ -2435,7 +2440,7 @@
 {
 	NSInteger i;
 	NSMutableArray *fields = [NSMutableArray array];
-	
+		
 	if (([prefs boolForKey:SPLoadBlobsAsNeeded]) && ([dataColumns count] > 0)) {
 		
 		NSArray *columnNames = [tableDataInstance columnNames];
@@ -2443,7 +2448,7 @@
 		for (i = 0 ; i < [columnNames count]; i++) 
 		{
 			if (![tableDataInstance columnIsBlobOrText:[NSArrayObjectAtIndex(dataColumns, i) objectForKey:@"name"]] ) {
-				[fields addObject:[NSArrayObjectAtIndex(columnNames, i) backtickQuotedString]];
+					[fields addObject:[NSArrayObjectAtIndex(columnNames, i) backtickQuotedString]];
 			} 
 			else {
 				// For blob/text fields, select a null placeholder so the column count is still correct