From 152574c6f5fc53662c8fb7ae9ad7ea6872c609b3 Mon Sep 17 00:00:00 2001 From: Max Date: Sun, 18 Jun 2017 20:11:17 +0200 Subject: MariaDB 10.2.1+: Work around an exception when trying to view tables that use row-level CHECK constraints --- Source/SPTableData.m | 205 +++++++++++++++++++++++++++------------------------ 1 file 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]; } -- cgit v1.2.3