aboutsummaryrefslogtreecommitdiffstats
path: root/Source
diff options
context:
space:
mode:
authorMax <post@wickenrode.com>2015-11-04 02:01:06 +0100
committerMax <post@wickenrode.com>2015-11-04 02:01:06 +0100
commit3e69d23923c0e3fe7b1312ce2c0a90acfaadbb1d (patch)
treeb1a1bc99ef147e7d795fb6f66015592ddc0ca208 /Source
parent47d9bf6d6c6dcb0810a03b268d73f3f10c513460 (diff)
downloadsequelpro-3e69d23923c0e3fe7b1312ce2c0a90acfaadbb1d.tar.gz
sequelpro-3e69d23923c0e3fe7b1312ce2c0a90acfaadbb1d.tar.bz2
sequelpro-3e69d23923c0e3fe7b1312ce2c0a90acfaadbb1d.zip
Add support for CURRENT_TIMESTAMP(n) in default/on update column of DATETIME/TIMESTAMP fields (part of #2315)
Diffstat (limited to 'Source')
-rw-r--r--Source/SPConstants.h2
-rw-r--r--Source/SPConstants.m5
-rw-r--r--Source/SPCustomQuery.m13
-rw-r--r--Source/SPObjectAdditions.h5
-rw-r--r--Source/SPObjectAdditions.m5
-rw-r--r--Source/SPSQLExporter.m4
-rw-r--r--Source/SPTableContent.m23
-rw-r--r--Source/SPTableData.m3
-rw-r--r--Source/SPTableStructure.m10
-rw-r--r--Source/SPTableStructureLoading.m12
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"];
}
}