MariaDB 10.2.1+: Work around an exception when trying to view tables that use row-level CHECK constraints

1 files changed, 107 insertions, 98 deletions

diff --git a/Source/SPTableData.m b/Source/SPTableData.m
index d095c33b..a0bfa7ff 100644
--- a/Source/SPTableData.m
+++ b/Source/SPTableData.m
@@ -582,11 +582,12 @@
 			if(fieldName == nil || [fieldName length] == 0) {
 				NSBeep();
 				SPOnewayAlertSheetWithStyle(
-				   NSLocalizedString(@"Error while parsing CREATE TABLE syntax",@"error while parsing CREATE TABLE syntax"),
-				   nil,
-				   nil,
-				   [NSString stringWithFormat:NSLocalizedString(@"“%@” couldn't be parsed. You can edit the column setup but the column will not be shown in the Content view; please report this issue to the Sequel Pro team using the Help menu item.", @"“%@” couldn't be parsed. You can edit the column setup but the column will not be shown in the Content view; please report this issue to the Sequel Pro team using the Help menu item."), fieldsParser],
-				   NSCriticalAlertStyle);
+					NSLocalizedString(@"Error while parsing CREATE TABLE syntax",@"error while parsing CREATE TABLE syntax"),
+					nil,
+					nil,
+					[NSString stringWithFormat:NSLocalizedString(@"“%@” couldn't be parsed. You can edit the column setup but the column will not be shown in the Content view; please report this issue to the Sequel Pro team using the Help menu item.", @"“%@” couldn't be parsed. You can edit the column setup but the column will not be shown in the Content view; please report this issue to the Sequel Pro team using the Help menu item."), fieldsParser],
+					NSCriticalAlertStyle
+				);
 				continue;
 			}
 			//if the next character is again a backtick, we stumbled across an escaped backtick. we have to continue parsing.
@@ -625,108 +626,116 @@
 			// Constraints
 			if ([[parts objectAtIndex:0] hasPrefix:@"CONSTRAINT"]) {
 				NSMutableDictionary *constraintDetails = [[NSMutableDictionary alloc] init];
-
-				// Extract the relevant details from the constraint string
-				[fieldsParser setString:[[parts objectAtIndex:1] stringByTrimmingCharactersInSet:bracketSet]];
-				[constraintDetails setObject:[fieldsParser unquotedString] forKey:@"name"];
-
-				NSMutableArray *keyColumns = [NSMutableArray array];
-				NSArray *keyColumnStrings = [[[parts objectAtIndex:4] stringByTrimmingCharactersInSet:bracketSet] componentsSeparatedByString:@","];
-
-				for (NSString *keyColumn in keyColumnStrings)
-				{
-					[fieldsParser setString:[[keyColumn stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]] stringByTrimmingCharactersInSet:bracketSet]];
-					[keyColumns addObject:[fieldsParser unquotedString]];
-				}
-
-				[constraintDetails setObject:keyColumns forKey:@"columns"];
-
-				NSString *part = [[parts objectAtIndex:6] stringByTrimmingCharactersInSet:bracketSet];
-												
-				NSArray *reference = [part captureComponentsMatchedByRegex:@"^`([\\w_.]+)`\\.`([\\w_.]+)`$" options:RKLCaseless range:NSMakeRange(0, [part length]) error:nil]; 
-				
-				if ([reference count]) {					
-					[constraintDetails setObject:[reference objectAtIndex:1] forKey:@"ref_database"];
-					[constraintDetails setObject:[reference objectAtIndex:2] forKey:@"ref_table"];
-				}
-				else {
-					[fieldsParser setString:part];
-					[constraintDetails setObject:[fieldsParser unquotedString] forKey:@"ref_table"];
-				}
-
-				NSMutableArray *refKeyColumns = [NSMutableArray array];
-				NSArray *refKeyColumnStrings = [[[parts objectAtIndex:7] stringByTrimmingCharactersInSet:bracketSet] componentsSeparatedByString:@","];
-				
-				for (NSString *keyColumn in refKeyColumnStrings)
-				{
-					[fieldsParser setString:[[keyColumn stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]] stringByTrimmingCharactersInSet:bracketSet]];
-					[refKeyColumns addObject:[fieldsParser unquotedString]];
-				}
-				
-				[constraintDetails setObject:refKeyColumns forKey:@"ref_columns"];
-
-				NSUInteger nextOffs = 12;
 				
-				if ([parts count] > 8) {
-					// NOTE: this won't get SET NULL | NO ACTION | RESTRICT
-					if ([[parts objectAtIndex:9] hasPrefix:@"UPDATE"]) {
-						if( [NSArrayObjectAtIndex(parts, 10) hasPrefix:@"SET"] ) {
-							[constraintDetails setObject:@"SET NULL"
-												  forKey:@"update"];
-							nextOffs = 13;
-						} else if( [NSArrayObjectAtIndex(parts, 10) hasPrefix:@"NO"] ) {
-							[constraintDetails setObject:@"NO ACTION"
-												  forKey:@"update"];
-							nextOffs = 13;
-						} else {
-							[constraintDetails setObject:NSArrayObjectAtIndex(parts, 10)
-												  forKey:@"update"];
-						}
+				if([[parts objectAtIndex:2] hasPrefix:@"FOREIGN"] && [[parts objectAtIndex:3] hasPrefix:@"KEY"]) {
+					// Extract the relevant details from the constraint string
+					[fieldsParser setString:[[parts objectAtIndex:1] stringByTrimmingCharactersInSet:bracketSet]];
+					[constraintDetails setObject:[fieldsParser unquotedString] forKey:@"name"];
+					
+					NSMutableArray *keyColumns = [NSMutableArray array];
+					NSArray *keyColumnStrings = [[[parts objectAtIndex:4] stringByTrimmingCharactersInSet:bracketSet] componentsSeparatedByString:@","];
+					
+					for (NSString *keyColumn in keyColumnStrings)
+					{
+						[fieldsParser setString:[[keyColumn stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]] stringByTrimmingCharactersInSet:bracketSet]];
+						[keyColumns addObject:[fieldsParser unquotedString]];
 					}
-					else if ([NSArrayObjectAtIndex(parts, 9) hasPrefix:@"DELETE"]) {
-						if ([NSArrayObjectAtIndex(parts, 10) hasPrefix:@"SET"]) {
-							[constraintDetails setObject:@"SET NULL"
-												  forKey:@"delete"];
-							nextOffs = 13;
-						} else if( [NSArrayObjectAtIndex(parts, 10) hasPrefix:@"NO"] ) {
-							[constraintDetails setObject:@"NO ACTION"
-												  forKey:@"delete"];
-							nextOffs = 13;
-						} else {
-							[constraintDetails setObject:NSArrayObjectAtIndex(parts, 10)
-												  forKey:@"delete"];
-						}
+					
+					[constraintDetails setObject:keyColumns forKey:@"columns"];
+					
+					NSString *part = [[parts objectAtIndex:6] stringByTrimmingCharactersInSet:bracketSet];
+					
+					NSArray *reference = [part captureComponentsMatchedByRegex:@"^`([\\w_.]+)`\\.`([\\w_.]+)`$" options:RKLCaseless range:NSMakeRange(0, [part length]) error:nil];
+					
+					if ([reference count]) {
+						[constraintDetails setObject:[reference objectAtIndex:1] forKey:@"ref_database"];
+						[constraintDetails setObject:[reference objectAtIndex:2] forKey:@"ref_table"];
 					}
-				}
-				
-				if ([parts count] > nextOffs - 1) {
-					if( [NSArrayObjectAtIndex(parts, nextOffs) hasPrefix:@"UPDATE"] ) {
-						if( [NSArrayObjectAtIndex(parts, nextOffs+1) hasPrefix:@"SET"] ) {
-							[constraintDetails setObject:@"SET NULL"
-												  forKey:@"update"];
-						} else if( [NSArrayObjectAtIndex(parts, nextOffs+1) hasPrefix:@"NO"] ) {
-							[constraintDetails setObject:@"NO ACTION"
-												  forKey:@"update"];
-						} else {
-							[constraintDetails setObject:NSArrayObjectAtIndex(parts, nextOffs+1)
-												  forKey:@"update"];
+					else {
+						[fieldsParser setString:part];
+						[constraintDetails setObject:[fieldsParser unquotedString] forKey:@"ref_table"];
+					}
+					
+					NSMutableArray *refKeyColumns = [NSMutableArray array];
+					NSArray *refKeyColumnStrings = [[[parts objectAtIndex:7] stringByTrimmingCharactersInSet:bracketSet] componentsSeparatedByString:@","];
+					
+					for (NSString *keyColumn in refKeyColumnStrings)
+					{
+						[fieldsParser setString:[[keyColumn stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]] stringByTrimmingCharactersInSet:bracketSet]];
+						[refKeyColumns addObject:[fieldsParser unquotedString]];
+					}
+					
+					[constraintDetails setObject:refKeyColumns forKey:@"ref_columns"];
+					
+					NSUInteger nextOffs = 12;
+					
+					if ([parts count] > 8) {
+						// NOTE: this won't get SET NULL | NO ACTION | RESTRICT
+						if ([[parts objectAtIndex:9] hasPrefix:@"UPDATE"]) {
+							if( [NSArrayObjectAtIndex(parts, 10) hasPrefix:@"SET"] ) {
+								[constraintDetails setObject:@"SET NULL"
+								                      forKey:@"update"];
+								nextOffs = 13;
+							} else if( [NSArrayObjectAtIndex(parts, 10) hasPrefix:@"NO"] ) {
+								[constraintDetails setObject:@"NO ACTION"
+								                      forKey:@"update"];
+								nextOffs = 13;
+							} else {
+								[constraintDetails setObject:NSArrayObjectAtIndex(parts, 10)
+								                      forKey:@"update"];
+							}
+						}
+						else if ([NSArrayObjectAtIndex(parts, 9) hasPrefix:@"DELETE"]) {
+							if ([NSArrayObjectAtIndex(parts, 10) hasPrefix:@"SET"]) {
+								[constraintDetails setObject:@"SET NULL"
+								                      forKey:@"delete"];
+								nextOffs = 13;
+							} else if( [NSArrayObjectAtIndex(parts, 10) hasPrefix:@"NO"] ) {
+								[constraintDetails setObject:@"NO ACTION"
+								                      forKey:@"delete"];
+								nextOffs = 13;
+							} else {
+								[constraintDetails setObject:NSArrayObjectAtIndex(parts, 10)
+								                      forKey:@"delete"];
+							}
 						}
 					}
-					else if( [NSArrayObjectAtIndex(parts, nextOffs) hasPrefix:@"DELETE"] ) {
-						if( [NSArrayObjectAtIndex(parts, nextOffs+1) hasPrefix:@"SET"] ) {
-							[constraintDetails setObject:@"SET NULL"
-												  forKey:@"delete"];
-						} else if( [NSArrayObjectAtIndex(parts, nextOffs+1) hasPrefix:@"NO"] ) {
-							[constraintDetails setObject:@"NO ACTION"
-												  forKey:@"delete"];
-						} else {
-							[constraintDetails setObject:NSArrayObjectAtIndex(parts, nextOffs+1)
-												  forKey:@"delete"];
+					
+					if ([parts count] > nextOffs - 1) {
+						if( [NSArrayObjectAtIndex(parts, nextOffs) hasPrefix:@"UPDATE"] ) {
+							if( [NSArrayObjectAtIndex(parts, nextOffs+1) hasPrefix:@"SET"] ) {
+								[constraintDetails setObject:@"SET NULL"
+								                      forKey:@"update"];
+							} else if( [NSArrayObjectAtIndex(parts, nextOffs+1) hasPrefix:@"NO"] ) {
+								[constraintDetails setObject:@"NO ACTION"
+								                      forKey:@"update"];
+							} else {
+								[constraintDetails setObject:NSArrayObjectAtIndex(parts, nextOffs+1)
+								                      forKey:@"update"];
+							}
+						}
+						else if( [NSArrayObjectAtIndex(parts, nextOffs) hasPrefix:@"DELETE"] ) {
+							if( [NSArrayObjectAtIndex(parts, nextOffs+1) hasPrefix:@"SET"] ) {
+								[constraintDetails setObject:@"SET NULL"
+								                      forKey:@"delete"];
+							} else if( [NSArrayObjectAtIndex(parts, nextOffs+1) hasPrefix:@"NO"] ) {
+								[constraintDetails setObject:@"NO ACTION"
+								                      forKey:@"delete"];
+							} else {
+								[constraintDetails setObject:NSArrayObjectAtIndex(parts, nextOffs+1)
+								                      forKey:@"delete"];
+							}
 						}
 					}
+					
+					[constraints addObject:constraintDetails];
+				}
+				else {
+					//TODO: MariaDB 10.2.1+ (not Mysql) supports syntax:
+					//  CONSTRAINT [constraint_name] CHECK (expression)
+					SPLog(@"Skipping unrecognized CONSTRAINT in CREATE stmt: %@", fieldsParser);
 				}
 				
-				[constraints addObject:constraintDetails];
 				[constraintDetails release];
 			}