Add support for CURRENT_TIMESTAMP(n) in default/on update column of DATETIME/TIMESTAMP fields (part of #2315)

10 files changed, 56 insertions, 26 deletions

diff --git a/Source/SPConstants.h b/Source/SPConstants.h
index e53d09b4..4246906f 100644
--- a/Source/SPConstants.h
+++ b/Source/SPConstants.h
@@ -620,6 +620,8 @@ extern NSString *SPBundleShellVariableAllFunctions;
 extern NSString *SPBundleShellVariableAllViews;
 extern NSString *SPBundleShellVariableAllTables;
 
+extern NSString *SPCurrentTimestampPattern;
+
 typedef NS_ENUM(NSInteger, SPBundleRedirectAction) {
 	SPBundleRedirectActionNone                 = 200,
 	SPBundleRedirectActionReplaceSection       = 201,
diff --git a/Source/SPConstants.m b/Source/SPConstants.m
index 893c725f..e5a407f4 100644
--- a/Source/SPConstants.m
+++ b/Source/SPConstants.m
@@ -431,6 +431,11 @@ NSString *SPBundleShellVariableSelectedText                 = @"SP_SELECTED_TEXT
 NSString *SPBundleShellVariableSelectedTextRange            = @"SP_SELECTED_TEXT_RANGE";
 NSString *SPBundleShellVariableUsedQueryForTable            = @"SP_USED_QUERY_FOR_TABLE";
 
+#define OWS @"\\s*" /* optional whitespace */
+//                                                CURRENT_TIMESTAMP    [            (           [n]          )    ]
+NSString *SPCurrentTimestampPattern = (@"^" OWS @"CURRENT_TIMESTAMP" @"(?:" OWS @"\\(" OWS @"(\\d*)" OWS @"\\)" @")?" OWS @"$");
+#undef OWS
+
 // URL scheme
 NSString *SPURLSchemeQueryInputPathHeader          = @"/tmp/SP_QUERY_";
 NSString *SPURLSchemeQueryResultPathHeader         = @"/tmp/SP_QUERY_RESULT_";
diff --git a/Source/SPCustomQuery.m b/Source/SPCustomQuery.m
index 4737bea8..3a04d1d5 100644
--- a/Source/SPCustomQuery.m
+++ b/Source/SPCustomQuery.m
@@ -1996,22 +1996,23 @@
 		} else if ( [anObject isKindOfClass:[NSData class]] ) {
 			newObject = [mySQLConnection escapeAndQuoteData:anObject];
 		} else {
-			if ( [[anObject description] isEqualToString:@"CURRENT_TIMESTAMP"] ) {
-				newObject = @"CURRENT_TIMESTAMP";
+			NSString *desc = [anObject description];
+			if ( [desc isMatchedByRegex:SPCurrentTimestampPattern] ) {
+				newObject = desc;
 			} else if ([anObject isEqualToString:[prefs stringForKey:SPNullValue]]
 						|| (([columnTypeGroup isEqualToString:@"float"] || [columnTypeGroup isEqualToString:@"integer"] || [columnTypeGroup isEqualToString:@"date"])
-							&& [[anObject description] isEqualToString:@""]))
+							&& [desc isEqualToString:@""]))
 			{
 				newObject = @"NULL";
 			} else if ([columnTypeGroup isEqualToString:@"geometry"]) {
 				newObject = [(NSString*)anObject getGeomFromTextString];
 			} else if ([columnTypeGroup isEqualToString:@"bit"]) {
-				newObject = [NSString stringWithFormat:@"b'%@'", ((![[anObject description] length] || [[anObject description] isEqualToString:@"0"]) ? @"0" : [anObject description])];
+				newObject = [NSString stringWithFormat:@"b'%@'", ((![desc length] || [desc isEqualToString:@"0"]) ? @"0" : desc)];
 			} else if ([columnTypeGroup isEqualToString:@"date"]
-						&& [[anObject description] isEqualToString:@"NOW()"]) {
+						&& [desc isEqualToString:@"NOW()"]) {
 				newObject = @"NOW()";
 			} else {
-				newObject = [mySQLConnection escapeAndQuoteString:[anObject description]];
+				newObject = [mySQLConnection escapeAndQuoteString:desc];
 			}
 		}
 
diff --git a/Source/SPObjectAdditions.h b/Source/SPObjectAdditions.h
index 03c04d85..bfe620d9 100644
--- a/Source/SPObjectAdditions.h
+++ b/Source/SPObjectAdditions.h
@@ -35,4 +35,9 @@
  */
 - (BOOL)isNSNull;
 
+/**
+ * easier to read version of [array containsObject:x]
+ */
+- (BOOL)isInArray:(NSArray *)list;
+
 @end
diff --git a/Source/SPObjectAdditions.m b/Source/SPObjectAdditions.m
index 5f7c73ab..ba53fc2d 100644
--- a/Source/SPObjectAdditions.m
+++ b/Source/SPObjectAdditions.m
@@ -46,6 +46,11 @@ static NSMutableDictionary *gScrollViewDealloc;
 	return (self == null);
 }
 
+- (BOOL)isInArray:(NSArray *)list
+{
+	return [list containsObject:self];
+}
+
 - (void)_scrollViewDidChangeBounds:(id)obj
 {
 	NSMutableString *msg = [NSMutableString string];
diff --git a/Source/SPSQLExporter.m b/Source/SPSQLExporter.m
index 94506728..9dffe7c1 100644
--- a/Source/SPSQLExporter.m
+++ b/Source/SPSQLExporter.m
@@ -884,8 +884,8 @@
 					[fieldString appendString:@" DEFAULT NULL"];
 				}
 			} 
-			else if (([[column objectForKey:@"type"] isEqualToString:@"TIMESTAMP"] || [[column objectForKey:@"type"] isEqualToString:@"DATETIME"]) && [column objectForKey:@"default"] != [NSNull null] && [[[column objectForKey:@"default"] uppercaseString] isEqualToString:@"CURRENT_TIMESTAMP"]) {
-				[fieldString appendString:@" DEFAULT CURRENT_TIMESTAMP"];
+			else if (([[column objectForKey:@"type"] isInArray:@[@"TIMESTAMP",@"DATETIME"]]) && [[column objectForKey:@"default"] isMatchedByRegex:SPCurrentTimestampPattern]) {
+				[fieldString appendFormat:@" DEFAULT %@",[column objectForKey:@"default"]];
 			} 
 			else {
 				[fieldString appendFormat:@" DEFAULT %@", [connection escapeAndQuoteString:[column objectForKey:@"default"]]];
diff --git a/Source/SPTableContent.m b/Source/SPTableContent.m
index 095f89ee..ed01703f 100644
--- a/Source/SPTableContent.m
+++ b/Source/SPTableContent.m
@@ -2801,14 +2801,15 @@ static NSString *SPTableFilterSetDefaultOperator = @"SPTableFilterSetDefaultOper
 				fieldValue = [mySQLConnection escapeAndQuoteData:rowObject];
 
 			} else {
-				if ([[rowObject description] isEqualToString:@"CURRENT_TIMESTAMP"]) {
-					fieldValue = @"CURRENT_TIMESTAMP";
+				NSString *desc = [rowObject description];
+				if ([desc isMatchedByRegex:SPCurrentTimestampPattern]) {
+					fieldValue = desc;
 				} else if ([fieldTypeGroup isEqualToString:@"bit"]) {
-					fieldValue = [NSString stringWithFormat:@"b'%@'", ((![[rowObject description] length] || [[rowObject description] isEqualToString:@"0"]) ? @"0" : [rowObject description])];
-				} else if ([fieldTypeGroup isEqualToString:@"date"] && [[rowObject description] isEqualToString:@"NOW()"]) {
+					fieldValue = [NSString stringWithFormat:@"b'%@'", ((![desc length] || [desc isEqualToString:@"0"]) ? @"0" : desc)];
+				} else if ([fieldTypeGroup isEqualToString:@"date"] && [desc isEqualToString:@"NOW()"]) {
 					fieldValue = @"NOW()";
 				} else {
-					fieldValue = [mySQLConnection escapeAndQuoteString:[rowObject description]];
+					fieldValue = [mySQLConnection escapeAndQuoteString:desc];
 				}
 			}
 		}
@@ -3426,19 +3427,19 @@ static NSString *SPTableFilterSetDefaultOperator = @"SPTableFilterSetDefaultOper
 		} else if ( [anObject isKindOfClass:[NSData class]] ) {
 			newObject = [mySQLConnection escapeAndQuoteData:anObject];
 		} else {
-			if ( [[anObject description] isEqualToString:@"CURRENT_TIMESTAMP"] ) {
-				newObject = @"CURRENT_TIMESTAMP";
+			NSString *desc = [anObject description];
+			if ( [desc isMatchedByRegex:SPCurrentTimestampPattern] ) {
+				newObject = desc;
 			} else if([anObject isEqualToString:[prefs stringForKey:SPNullValue]]) {
 				newObject = @"NULL";
 			} else if ([[columnDefinition objectForKey:@"typegrouping"] isEqualToString:@"geometry"]) {
 				newObject = [(NSString*)anObject getGeomFromTextString];
 			} else if ([[columnDefinition objectForKey:@"typegrouping"] isEqualToString:@"bit"]) {
-				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 = [NSString stringWithFormat:@"b'%@'", ((![desc length] || [desc isEqualToString:@"0"]) ? @"0" : desc)];
+			} else if ([[columnDefinition objectForKey:@"typegrouping"] isEqualToString:@"date"] && [desc isEqualToString:@"NOW()"]) {
 				newObject = @"NOW()";
 			} else {
-				newObject = [mySQLConnection escapeAndQuoteString:[anObject description]];
+				newObject = [mySQLConnection escapeAndQuoteString:desc];
 			}
 		}
 
diff --git a/Source/SPTableData.m b/Source/SPTableData.m
index 51c6a274..62732b51 100644
--- a/Source/SPTableData.m
+++ b/Source/SPTableData.m
@@ -1359,7 +1359,8 @@
 		// Special timestamp/datetime case - Whether fields are set to update the current timestamp
 		} else if ([detailString isEqualToString:@"ON"] && (definitionPartsIndex + 2 < partsArrayLength)
 					&& [[NSArrayObjectAtIndex(definitionParts, definitionPartsIndex+1) uppercaseString] isEqualToString:@"UPDATE"]
-					&& [[NSArrayObjectAtIndex(definitionParts, definitionPartsIndex+2) uppercaseString] isEqualToString:@"CURRENT_TIMESTAMP"]) {
+					&& [NSArrayObjectAtIndex(definitionParts, definitionPartsIndex+2) isMatchedByRegex:SPCurrentTimestampPattern]) {
+			// mysql requires the CURRENT_TIMESTAMP(n) to be exactly the same as the column types length, so we don't need to keep it, we can just restore it later
 			[fieldDetails setValue:@YES forKey:@"onupdatetimestamp"];
 			definitionPartsIndex += 2;
 
diff --git a/Source/SPTableStructure.m b/Source/SPTableStructure.m
index 2fd185e5..d242ae88 100644
--- a/Source/SPTableStructure.m
+++ b/Source/SPTableStructure.m
@@ -974,12 +974,12 @@ static NSString *SPRemoveFieldAndForeignKey = @"SPRemoveFieldAndForeignKey";
 				}
 			}
 			// Otherwise, if CURRENT_TIMESTAMP was specified for timestamps/datetimes, use that
-			else if (([theRowType isEqualToString:@"TIMESTAMP"] || [theRowType isEqualToString:@"DATETIME"]) &&
-					[(matches = [[[theRow objectForKey:@"default"] uppercaseString] captureComponentsMatchedByRegex:@"^\\s*CURRENT_TIMESTAMP(?:\\s*\\(\\s*(\\d+)\\s*\\))?\\s*$"]) count])
+			else if ([theRowType isInArray:@[@"TIMESTAMP",@"DATETIME"]] &&
+					[(matches = [[[theRow objectForKey:@"default"] uppercaseString] captureComponentsMatchedByRegex:SPCurrentTimestampPattern]) count])
 			{
 				[queryString appendString:@"\n DEFAULT CURRENT_TIMESTAMP"];
 				NSString *userLen = [matches objectAtIndex:1];
-				//mysql 5.6.4+ allows DATETIME(n) for fractional seconds, which in turn requires CURRENT_TIMESTAMP(n).
+				// mysql 5.6.4+ allows DATETIME(n) for fractional seconds, which in turn requires CURRENT_TIMESTAMP(n) with the same n!
 				// Also, if the user explicitly added one we should never ignore that.
 				if([userLen length] || fieldDefIncludesLen) {
 					[queryString appendFormat:@"(%@)",([userLen length]? userLen : [theRow objectForKey:@"length"])];
@@ -1003,6 +1003,10 @@ static NSString *SPRemoveFieldAndForeignKey = @"SPRemoveFieldAndForeignKey";
 
 		if ([theRowExtra length] && ![theRowExtra isEqualToString:@"NONE"]) {
 			[queryString appendFormat:@"\n %@", theRowExtra];
+			//fix our own default item if needed
+			if([theRowExtra isEqualToString:@"ON UPDATE CURRENT_TIMESTAMP"] && fieldDefIncludesLen) {
+				[queryString appendFormat:@"(%@)",[theRow objectForKey:@"length"]];
+			}
 		}
 	}
 
diff --git a/Source/SPTableStructureLoading.m b/Source/SPTableStructureLoading.m
index 85b74a7c..57191b4b 100644
--- a/Source/SPTableStructureLoading.m
+++ b/Source/SPTableStructureLoading.m
@@ -227,9 +227,15 @@
 		}
 		
 		// For timestamps/datetime check to see whether "on update CURRENT_TIMESTAMP"  and set Extra accordingly
-		else if (([type isEqualToString:@"TIMESTAMP"] || [type isEqualToString:@"DATETIME"]) &&
-				 [[theField objectForKey:@"onupdatetimestamp"] integerValue]) {
-			[theField setObject:@"on update CURRENT_TIMESTAMP" forKey:@"Extra"];
+		else if ([type isInArray:@[@"TIMESTAMP",@"DATETIME"]] && [[theField objectForKey:@"onupdatetimestamp"] boolValue]) {
+			NSString *ouct = @"on update CURRENT_TIMESTAMP";
+			// restore a length parameter if the field has fractional seconds.
+			// the parameter of current_timestamp MUST match the field's length in that case, so we can just 'guess' it.
+			NSString *fieldLen = [theField objectForKey:@"length"];
+			if([fieldLen length] && ![fieldLen isEqualToString:@"0"]) {
+				ouct = [ouct stringByAppendingFormat:@"(%@)",fieldLen];
+			}
+			[theField setObject:ouct forKey:@"Extra"];
 		}
 	}