diff options
Diffstat (limited to 'Source/SPTableStructure.m')
| -rw-r--r-- | Source/SPTableStructure.m | 274 |
1 files changed, 189 insertions, 85 deletions
diff --git a/Source/SPTableStructure.m b/Source/SPTableStructure.m index 48b3cf22..1427eea1 100644 --- a/Source/SPTableStructure.m +++ b/Source/SPTableStructure.m @@ -35,10 +35,13 @@ #import "SPAlertSheets.h" #import "SPMainThreadTrampoline.h" #import "SPIndexesController.h" +#import "RegexKitLite.h" @interface SPTableStructure (PrivateAPI) - (void)_removeFieldAndForeignKey:(NSNumber *)removeForeignKey; +- (BOOL)_isFieldTypeNumeric:(NSString*)aType; +- (BOOL)_isFieldTypeDate:(NSString*)aType; @end @@ -90,6 +93,9 @@ @"REAL", @"DECIMAL", @"BIT", + @"SERIAL", + @"BOOL", + @"BOOLEAN", @"--------", @"CHAR", @"VARCHAR", @@ -111,7 +117,7 @@ @"TIME", @"YEAR", nil] retain]; - // Hint: in [self addRowToDB] > [typeSuggestions indexOfObject:theRowType] < 11 etc. has to be changed if typeSuggestions was changed! + // Hint: _isFieldTypeDate and _isFieldTypeNumeric must be changed if typeSuggestions was changed! databaseDataInstance = [tableDocumentInstance valueForKeyPath:@"databaseDataInstance"]; @@ -713,7 +719,10 @@ closes the keySheet NSInteger code; NSDictionary *theRow; NSMutableString *queryString; - NSString *theRowType; + NSString *theRowType = @""; + NSString *theRowExtra = @""; + + BOOL specialFieldTypes = NO; if (!isEditingRow || currentlyEditingRow == -1) return YES; @@ -723,11 +732,52 @@ closes the keySheet theRow = [tableFields objectAtIndex:currentlyEditingRow]; - theRowType = [[theRow objectForKey:@"type"] uppercaseString]; + if([theRow objectForKey:@"type"]) + theRowType = [[[theRow objectForKey:@"type"] stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]] uppercaseString]; + + if([theRow objectForKey:@"Extra"]) + theRowExtra = [[[theRow objectForKey:@"Extra"] stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]] uppercaseString]; if (isEditingNewRow) { + // ADD syntax - if ([[theRow objectForKey:@"length"] isEqualToString:@""] || ![theRow objectForKey:@"length"]) { + + // Check for pre-defined field types + if([theRowType isEqualToString:@"SERIAL"]) { + specialFieldTypes = YES; + queryString = [NSMutableString stringWithFormat:@"ALTER TABLE %@ ADD %@ %@", + [selectedTable backtickQuotedString], + [[theRow objectForKey:@"name"] backtickQuotedString], + theRowType]; + } + else if([theRowType rangeOfRegex:@"(?i)bool(ean)?"].length) { + specialFieldTypes = YES; + queryString = [NSMutableString stringWithFormat:@"ALTER TABLE %@ ADD %@ %@", + [selectedTable backtickQuotedString], + [[theRow objectForKey:@"name"] backtickQuotedString], + theRowType]; + + if ([[theRow objectForKey:@"null"] integerValue] == 0) { + [queryString appendString:@"\n NOT NULL"]; + } else { + [queryString appendString:@"\n NULL"]; + } + // If a NULL value has been specified, and NULL is allowed, specify DEFAULT NULL + if ([[theRow objectForKey:@"default"] isEqualToString:[prefs objectForKey:SPNullValue]]) { + if ([[theRow objectForKey:@"null"] integerValue] == 1) { + [queryString appendString:@"\n DEFAULT NULL "]; + } + } + else if (![[theRow objectForKey:@"default"] length]) { + ; + } + // Otherwise, use the provided default + else { + [queryString appendFormat:@"\n DEFAULT '%@' ", [mySQLConnection prepareString:[theRow objectForKey:@"default"]]]; + } + } + + else if ([[theRow objectForKey:@"length"] isEqualToString:@""] || ![theRow objectForKey:@"length"]) { queryString = [NSMutableString stringWithFormat:@"ALTER TABLE %@ ADD %@ %@", [selectedTable backtickQuotedString], @@ -744,9 +794,47 @@ closes the keySheet } else { // CHANGE syntax - if (([[theRow objectForKey:@"length"] isEqualToString:@""]) || + + // Check for pre-defined field types + if([theRowType isEqualToString:@"SERIAL"]) { + specialFieldTypes = YES; + queryString = [NSMutableString stringWithFormat:@"ALTER TABLE %@ CHANGE %@ %@ %@", + [selectedTable backtickQuotedString], + [[theRow objectForKey:@"name"] backtickQuotedString], + [[theRow objectForKey:@"name"] backtickQuotedString], + theRowType]; + } + else if([theRowType rangeOfRegex:@"(?i)bool(ean)?"].length) { + specialFieldTypes = YES; + queryString = [NSMutableString stringWithFormat:@"ALTER TABLE %@ CHANGE %@ %@ %@", + [selectedTable backtickQuotedString], + [[theRow objectForKey:@"name"] backtickQuotedString], + [[theRow objectForKey:@"name"] backtickQuotedString], + theRowType]; + + if ([[theRow objectForKey:@"null"] integerValue] == 0) { + [queryString appendString:@"\n NOT NULL"]; + } else { + [queryString appendString:@"\n NULL"]; + } + // If a NULL value has been specified, and NULL is allowed, specify DEFAULT NULL + if ([[theRow objectForKey:@"default"] isEqualToString:[prefs objectForKey:SPNullValue]]) { + if ([[theRow objectForKey:@"null"] integerValue] == 1) { + [queryString appendString:@"\n DEFAULT NULL "]; + } + } + else if (![[theRow objectForKey:@"default"] length]) { + ; + } + // Otherwise, use the provided default + else { + [queryString appendFormat:@"\n DEFAULT '%@' ", [mySQLConnection prepareString:[theRow objectForKey:@"default"]]]; + } + } + + else if (([[theRow objectForKey:@"length"] isEqualToString:@""]) || (![theRow objectForKey:@"length"]) || - ([[[theRow objectForKey:@"type"] lowercaseString] isEqualToString:@"datetime"])) + ([theRowType isEqualToString:@"DATETIME"])) { // If the old row and new row dictionaries are equal then the user didn't actually change anything so don't continue if ([oldRow isEqualToDictionary:theRow]) { @@ -778,101 +866,99 @@ closes the keySheet } } - NSString *fieldEncoding = @""; - if([[theRow objectForKey:@"encoding"] integerValue] > 0) { - NSString *enc = [[encodingPopupCell itemAtIndex:[[theRow objectForKey:@"encoding"] integerValue]] title]; - NSInteger start = [enc rangeOfString:@"("].location+1; - NSInteger end = [enc length] - start - 1; - fieldEncoding = [enc substringWithRange:NSMakeRange(start, end)]; - [queryString appendFormat:@" CHARACTER SET %@", fieldEncoding]; - } - if(![fieldEncoding length] && [tableDataInstance tableEncoding]) { - fieldEncoding = [tableDataInstance tableEncoding]; - } - if([fieldEncoding length] && [[theRow objectForKey:@"collation"] integerValue] > 0) { - NSArray *theCollations = [databaseDataInstance getDatabaseCollationsForEncoding:fieldEncoding]; - NSString *col = [[theCollations objectAtIndex:[[theRow objectForKey:@"collation"] integerValue]-1] objectForKey:@"COLLATION_NAME"]; - [queryString appendFormat:@" COLLATE %@", col]; - } - - // Field specification - if ([[theRow objectForKey:@"unsigned"] integerValue] == 1) { - [queryString appendString:@" UNSIGNED"]; - } - - if ( [[theRow objectForKey:@"zerofill"] integerValue] == 1) { - [queryString appendString:@" ZEROFILL"]; - } + if(!specialFieldTypes) { - if ( [[theRow objectForKey:@"binary"] integerValue] == 1) { - [queryString appendString:@" BINARY"]; - } + NSString *fieldEncoding = @""; + if([[theRow objectForKey:@"encoding"] integerValue] > 0) { + NSString *enc = [[encodingPopupCell itemAtIndex:[[theRow objectForKey:@"encoding"] integerValue]] title]; + NSInteger start = [enc rangeOfString:@"("].location+1; + NSInteger end = [enc length] - start - 1; + fieldEncoding = [enc substringWithRange:NSMakeRange(start, end)]; + [queryString appendFormat:@"\n CHARACTER SET %@", fieldEncoding]; + } + if(![fieldEncoding length] && [tableDataInstance tableEncoding]) { + fieldEncoding = [tableDataInstance tableEncoding]; + } + if([fieldEncoding length] && [[theRow objectForKey:@"collation"] integerValue] > 0) { + NSArray *theCollations = [databaseDataInstance getDatabaseCollationsForEncoding:fieldEncoding]; + NSString *col = [[theCollations objectAtIndex:[[theRow objectForKey:@"collation"] integerValue]-1] objectForKey:@"COLLATION_NAME"]; + [queryString appendFormat:@"\n COLLATE %@", col]; + } - if ([[theRow objectForKey:@"null"] integerValue] == 0) { - [queryString appendString:@" NOT NULL"]; - } else { - [queryString appendString:@" NULL"]; - } + // Field specification + if ([[theRow objectForKey:@"unsigned"] integerValue] == 1) { + [queryString appendString:@"\n UNSIGNED"]; + } - // Don't provide any defaults for auto-increment fields - if ([[theRow objectForKey:@"Extra"] isEqualToString:@"auto_increment"]) { - [queryString appendString:@" "]; - } - else { - // If a NULL value has been specified, and NULL is allowed, specify DEFAULT NULL - if ([[theRow objectForKey:@"default"] isEqualToString:[prefs objectForKey:SPNullValue]]) { - if ([[theRow objectForKey:@"null"] integerValue] == 1) { - [queryString appendString:@" DEFAULT NULL "]; - } + if ( [[theRow objectForKey:@"zerofill"] integerValue] == 1) { + [queryString appendString:@"\n ZEROFILL"]; } - // Otherwise, if CURRENT_TIMESTAMP was specified for timestamps, use that - else if ([theRowType isEqualToString:@"TIMESTAMP"] && - [[[theRow objectForKey:@"default"] uppercaseString] isEqualToString:@"CURRENT_TIMESTAMP"]) - { - [queryString appendString:@" DEFAULT CURRENT_TIMESTAMP "]; + if ( [[theRow objectForKey:@"binary"] integerValue] == 1) { + [queryString appendString:@"\n BINARY"]; } - // If the field is of type BIT, permit the use of single qoutes and also don't quote the default value. - // For example, use DEFAULT b'1' as opposed to DEFAULT 'b\'1\'' which results in an error. - else if ([[theRow objectForKey:@"default"] length] && [theRowType isEqualToString:@"BIT"]) { - [queryString appendFormat:@" DEFAULT %@ ", [theRow objectForKey:@"default"]]; + + if ([[theRow objectForKey:@"null"] integerValue] == 0 || [theRowExtra isEqualToString:@"SERIAL DEFAULT VALUE"]) { + [queryString appendString:@"\n NOT NULL"]; + } else { + [queryString appendString:@"\n NULL"]; } - // Suppress appending DEFAULT clause for any numerics, date, time fields if default is empty to avoid error messages - // Hint: [typeSuggestions indexOfObject:theRowType] < 11 has to be changed if typeSuggestions was changed! - else if (![[theRow objectForKey:@"default"] length] && ([typeSuggestions indexOfObject:theRowType] < 11 || [typeSuggestions indexOfObject:theRowType] > 25)) { - ; + + // Don't provide any defaults for auto-increment fields + if ([theRowExtra isEqualToString:@"AUTO_INCREMENT"]) { + [queryString appendString:@" "]; } - // Otherwise, use the provided default else { - [queryString appendFormat:@" DEFAULT '%@' ", [mySQLConnection prepareString:[theRow objectForKey:@"default"]]]; + // If a NULL value has been specified, and NULL is allowed, specify DEFAULT NULL + if ([[theRow objectForKey:@"default"] isEqualToString:[prefs objectForKey:SPNullValue]]) { + if ([[theRow objectForKey:@"null"] integerValue] == 1) { + [queryString appendString:@"\n DEFAULT NULL "]; + } + } + // Otherwise, if CURRENT_TIMESTAMP was specified for timestamps, use that + else if ([theRowType isEqualToString:@"TIMESTAMP"] && + [[[theRow objectForKey:@"default"] uppercaseString] isEqualToString:@"CURRENT_TIMESTAMP"]) + { + [queryString appendString:@"\n DEFAULT CURRENT_TIMESTAMP "]; + + } + // If the field is of type BIT, permit the use of single qoutes and also don't quote the default value. + // For example, use DEFAULT b'1' as opposed to DEFAULT 'b\'1\'' which results in an error. + else if ([[theRow objectForKey:@"default"] length] && [theRowType isEqualToString:@"BIT"]) { + [queryString appendFormat:@"\n DEFAULT %@ ", [theRow objectForKey:@"default"]]; + } + // Suppress appending DEFAULT clause for any numerics, date, time fields if default is empty to avoid error messages + else if (![[theRow objectForKey:@"default"] length] && ([self _isFieldTypeNumeric:theRowType] || [self _isFieldTypeDate:theRowType])) { + ; + } + // Otherwise, use the provided default + else { + [queryString appendFormat:@"\n DEFAULT '%@' ", [mySQLConnection prepareString:[theRow objectForKey:@"default"]]]; + } + } + + if (![theRowExtra isEqualToString:@""] && ![theRowExtra isEqualToString:@"NONE"]) + { + [queryString appendFormat:@"\n %@", theRowExtra]; } - } - if (!( - [[theRow objectForKey:@"Extra"] isEqualToString:@""] || - [[theRow objectForKey:@"Extra"] isEqualToString:@"None"] - ) && - [theRow objectForKey:@"Extra"] ) - { - [queryString appendString:@" "]; - [queryString appendString:[theRow objectForKey:@"Extra"]]; } // Any column comments if ([[theRow objectForKey:@"comment"] length]) { - [queryString appendFormat:@" COMMENT '%@'", [mySQLConnection prepareString:[theRow objectForKey:@"comment"]]]; + [queryString appendFormat:@"\n COMMENT '%@'", [mySQLConnection prepareString:[theRow objectForKey:@"comment"]]]; } if (!isEditingNewRow) { // Unparsed details - column formats, storage, reference definitions if ([[theRow objectForKey:@"unparsed"] length]) { - [queryString appendString:[theRow objectForKey:@"unparsed"]]; + [queryString appendFormat:@"\n %@", [theRow objectForKey:@"unparsed"]]; } } // Asks the user to add an index to query if auto_increment is set and field isn't indexed - if ([[theRow objectForKey:@"Extra"] isEqualToString:@"auto_increment"] && + if ([theRowExtra isEqualToString:@"AUTO_INCREMENT"] && ([[theRow objectForKey:@"Key"] isEqualToString:@""] || ![theRow objectForKey:@"Key"])) { @@ -891,26 +977,26 @@ closes the keySheet if (code) { // User wants to add PRIMARY KEY if ([chooseKeyButton indexOfSelectedItem] == 0 ) { - [queryString appendString:@" PRIMARY KEY"]; + [queryString appendString:@"\n PRIMARY KEY"]; // Add AFTER ... only if the user added a new field if (isEditingNewRow) { - [queryString appendFormat:@" AFTER %@", [[[tableFields objectAtIndex:(currentlyEditingRow -1)] objectForKey:@"name"] backtickQuotedString]]; + [queryString appendFormat:@"\n AFTER %@", [[[tableFields objectAtIndex:(currentlyEditingRow -1)] objectForKey:@"name"] backtickQuotedString]]; } } else { // Add AFTER ... only if the user added a new field if (isEditingNewRow) { - [queryString appendFormat:@" AFTER %@", [[[tableFields objectAtIndex:(currentlyEditingRow -1)] objectForKey:@"name"] backtickQuotedString]]; + [queryString appendFormat:@"\n AFTER %@", [[[tableFields objectAtIndex:(currentlyEditingRow -1)] objectForKey:@"name"] backtickQuotedString]]; } - [queryString appendFormat:@", ADD %@ (%@)", [chooseKeyButton titleOfSelectedItem], [[theRow objectForKey:@"name"] backtickQuotedString]]; + [queryString appendFormat:@"\n, ADD %@ (%@)", [chooseKeyButton titleOfSelectedItem], [[theRow objectForKey:@"name"] backtickQuotedString]]; } } } // Add AFTER ... only if the user added a new field else if (isEditingNewRow) { - [queryString appendFormat:@" AFTER %@", [[[tableFields objectAtIndex:(currentlyEditingRow -1)] objectForKey:@"name"] backtickQuotedString]]; + [queryString appendFormat:@"\n AFTER %@", [[[tableFields objectAtIndex:(currentlyEditingRow -1)] objectForKey:@"name"] backtickQuotedString]]; } // Execute query @@ -963,15 +1049,15 @@ closes the keySheet SPBeginAlertSheet(NSLocalizedString(@"Error adding field", @"error adding field message"), NSLocalizedString(@"Edit row", @"Edit row button"), NSLocalizedString(@"Discard changes", @"discard changes button"), nil, [tableDocumentInstance parentWindow], self, @selector(addRowErrorSheetDidEnd:returnCode:contextInfo:), nil, - [NSString stringWithFormat:NSLocalizedString(@"An error occurred when trying to add the field '%@'.\n\nMySQL said: %@", @"error adding field informative message"), - [theRow objectForKey:@"name"], [mySQLConnection getLastErrorMessage]]); + [NSString stringWithFormat:NSLocalizedString(@"An error occurred when trying to add the field '%@' via\n\n%@\n\nMySQL said: %@", @"error adding field informative message"), + [theRow objectForKey:@"name"], queryString, [mySQLConnection getLastErrorMessage]]); } else { SPBeginAlertSheet(NSLocalizedString(@"Error changing field", @"error changing field message"), NSLocalizedString(@"Edit row", @"Edit row button"), NSLocalizedString(@"Discard changes", @"discard changes button"), nil, [tableDocumentInstance parentWindow], self, @selector(addRowErrorSheetDidEnd:returnCode:contextInfo:), nil, - [NSString stringWithFormat:NSLocalizedString(@"An error occurred when trying to change the field '%@'.\n\nMySQL said: %@", @"error changing field informative message"), - [theRow objectForKey:@"name"], [mySQLConnection getLastErrorMessage]]); + [NSString stringWithFormat:NSLocalizedString(@"An error occurred when trying to change the field '%@' via\n\n%@\n\nMySQL said: %@", @"error changing field informative message"), + [theRow objectForKey:@"name"], queryString, [mySQLConnection getLastErrorMessage]]); } return NO; @@ -1748,6 +1834,24 @@ would result in a position change. #pragma mark Private API methods /** + * Return is aType is numeric according to typeSuggestions's position + * Hint: This must be changed if typeSuggestions was changed! + */ +- (BOOL)_isFieldTypeNumeric:(NSString*)aType +{ + return ([typeSuggestions indexOfObject:aType] < 14); +} + +/** + * Return is aType is a date or time according to typeSuggestions's position + * Hint: This must be changed if typeSuggestions was changed! + */ +- (BOOL)_isFieldTypeDate:(NSString*)aType +{ + return ([typeSuggestions indexOfObject:aType] > 28); +} + +/** * Removes a field from the current table and the dependent foreign key if specified. */ - (void)_removeFieldAndForeignKey:(NSNumber *)removeForeignKey |