diff options
author | rowanbeentje <rowan@beent.je> | 2012-03-17 15:32:00 +0000 |
---|---|---|
committer | rowanbeentje <rowan@beent.je> | 2012-03-17 15:32:00 +0000 |
commit | ac8ebfe4bba6d7da6edad87c75b174156d919621 (patch) | |
tree | 08a3c2843d3c520090f40e6341b26043a7ccf014 /Source/SPCustomQuery.m | |
parent | 8886d935e933c6239aa9b35e900d96f7d07527c7 (diff) | |
parent | eab6df4de773259f90dd5a1d25e44ca4d2765bbf (diff) | |
download | sequelpro-ac8ebfe4bba6d7da6edad87c75b174156d919621.tar.gz sequelpro-ac8ebfe4bba6d7da6edad87c75b174156d919621.tar.bz2 sequelpro-ac8ebfe4bba6d7da6edad87c75b174156d919621.zip |
Merge in the SPMySQL Framework. This new framework should provide much of the functionality required from MCPKit and is based around its interface for relatively easy integration.
Externally visible changes as a result of this merge:
- Speed improvements, particularly when loading large data sets
- Stability improvements, particularly related to connection state after the connection is dropped (eg Issue #1256)
- Improved support for new MySQL data types, which should address Issue #1052.
- Database structure retrieval and query cancellation now use a single persistent helper connection instead of lots of connections on-demand. This should help Issue #1097.
- More internal commands now use queries instead of MySQL functions; for example USE queries are now used to trigger database selection, improving transcripts. This addresses Issue #1247.
- Improved internal encoding work; while this needs support within the UI, it lays the foundation for issues like Issue #1280.
Code improvements:
- Much improved class layouts including extensive category usage
- Improved documentation across framework methods
- Support for fast enumeration across result objects
- Rewrite fixes use of a number of deprecate functions
- Much less code duplication across result set types
- Improved encapsultation within the framework, limiting the number of methods exposed, and also not exposing all the MySQL headers
From the Readme file:
The SPMySQL Framework is intended to provide a stable MySQL connection framework, with the ability to run text-based queries and rapidly retrieve result sets with conversion from MySQL data types to Cocoa objects.
SPMySQL.framework has an interface loosely based around that provided by MCPKit by Serge Cohen and Bertrand Mansion (http://mysql-cocoa.sourceforge.net/), and in particular the heavily modified Sequel Pro version (http://www.sequelpro.com/). It is a full rewrite of the original framework, although it includes code from patches implementing the following Sequel Pro functionality, largely contributed by Hans-Jörg Bibiko, Stuart Connolly, Jakob Egger, and Rowan Beentje:
- Connection locking (Jakob et al)
- Ping & keepalive (Rowan et al)
- Query cancellation (Rowan et al)
- Delegate setup (Stuart et al)
- SSL support (Rowan et al)
- Connection checking (Rowan et al)
- Version state (Stuart et al)
- Maximum packet size control (Hans et al)
- Result multithreading and streaming (Rowan et al)
- Improved encoding support & switching (Rowan et al)
- Database structure; moved to inside the app (Hans et al)
- Query reattempts and error-handling approach (Rowan et al)
- Geometry result class (Hans et al)
- Connection proxy (Stuart et al)
Diffstat (limited to 'Source/SPCustomQuery.m')
-rw-r--r-- | Source/SPCustomQuery.m | 129 |
1 files changed, 64 insertions, 65 deletions
diff --git a/Source/SPCustomQuery.m b/Source/SPCustomQuery.m index 61cac5f6..da15da11 100644 --- a/Source/SPCustomQuery.m +++ b/Source/SPCustomQuery.m @@ -25,6 +25,7 @@ #import "SPCustomQuery.h" #import "SPSQLParser.h" +#import "SPMySQL.h" #ifndef SP_REFACTOR /* headers */ #import "SPGrowlController.h" #endif @@ -47,6 +48,7 @@ #import "SPGeometryDataView.h" #import "SPAppController.h" #import "SPBundleHTMLOutputController.h" +#include <pthread.h> #ifndef SP_REFACTOR /* headers */ #import <BWToolkitFramework/BWToolkitFramework.h> @@ -540,7 +542,7 @@ /** * Toggle whether the query info pane is visible. */ -- (IBAction)toggleQueryInfoPaneCollapse:(id)sender +- (IBAction)toggleQueryInfoPaneCollapse:(NSButton *)sender { [queryInfoPaneSplitView toggleCollapse:sender]; @@ -585,12 +587,12 @@ - (void)performQueriesTask:(NSDictionary *)taskArguments { - NSAutoreleasePool *queryRunningPool = [[NSAutoreleasePool alloc] init]; - NSArray *queries = [taskArguments objectForKey:@"queries"]; - MCPStreamingResult *streamingResult = nil; - NSMutableString *errors = [NSMutableString string]; - SEL callbackMethod = NULL; - NSString *taskButtonString; + NSAutoreleasePool *queryRunningPool = [[NSAutoreleasePool alloc] init]; + NSArray *queries = [taskArguments objectForKey:@"queries"]; + SPMySQLFastStreamingResult *streamingResult = nil; + NSMutableString *errors = [NSMutableString string]; + SEL callbackMethod = NULL; + NSString *taskButtonString; NSUInteger i, totalQueriesRun = 0, totalAffectedRows = 0; double executionTime = 0; @@ -627,7 +629,7 @@ } // Disable automatic query retries on failure for the custom queries - [mySQLConnection setAllowQueryRetries:NO]; + [mySQLConnection setRetryQueriesOnConnectionFailure:NO]; NSUInteger queryCount = [queries count]; NSMutableArray *tempQueries = [NSMutableArray arrayWithCapacity:queryCount]; @@ -659,16 +661,16 @@ // Run the query, timing execution (note this also includes network and overhead) streamingResult = [[mySQLConnection streamingQueryString:query] retain]; - executionTime += [mySQLConnection lastQueryExecutionTime]; + executionTime += [streamingResult queryExecutionTime]; totalQueriesRun++; // If this is the last query, retrieve and store the result; otherwise, // discard the result without fully loading. - if (totalQueriesRun == queryCount || [mySQLConnection queryCancelled]) { + if (totalQueriesRun == queryCount || [mySQLConnection lastQueryWasCancelled]) { // Retrieve and cache the column definitions for the result array if (cqColumnDefinition) [cqColumnDefinition release]; - cqColumnDefinition = [[streamingResult fetchResultFieldsStructure] retain]; + cqColumnDefinition = [[streamingResult fieldDefinitions] retain]; if(!reloadingExistingResult) { [[self onMainThread] updateTableView]; @@ -695,24 +697,24 @@ } // Record any affected rows - if ( [mySQLConnection affectedRows] != (my_ulonglong)~0 ) - totalAffectedRows += (NSUInteger)[mySQLConnection affectedRows]; - else if ( [streamingResult numOfRows] ) - totalAffectedRows += (NSUInteger)[streamingResult numOfRows]; + if ( [mySQLConnection rowsAffectedByLastQuery] != (unsigned long long)~0 ) + totalAffectedRows += (NSUInteger)[mySQLConnection rowsAffectedByLastQuery]; + else if ( [streamingResult numberOfRows] ) + totalAffectedRows += (NSUInteger)[streamingResult numberOfRows]; [streamingResult release]; // Store any error messages - if ([mySQLConnection queryErrored] || [mySQLConnection queryCancelled]) { + if ([mySQLConnection queryErrored] || [mySQLConnection lastQueryWasCancelled]) { NSString *errorString; - if ([mySQLConnection queryCancelled]) { - if ([mySQLConnection queryCancellationUsedReconnect]) + if ([mySQLConnection lastQueryWasCancelled]) { + if ([mySQLConnection lastQueryWasCancelledUsingReconnect]) errorString = NSLocalizedString(@"Query cancelled. Please note that to cancel the query the connection had to be reset; transactions and connection variables were reset.", @"Query cancel by resetting connection error"); else errorString = NSLocalizedString(@"Query cancelled.", @"Query cancelled error"); } else { - errorString = [mySQLConnection getLastErrorMessage]; + errorString = [mySQLConnection lastErrorMessage]; } // If the query errored, append error to the error log for display at the end @@ -729,7 +731,7 @@ [[errorText onMainThread] setString:errors]; // ask the user to continue after detecting an error - if (![mySQLConnection queryCancelled]) { + if (![mySQLConnection lastQueryWasCancelled]) { [tableDocumentInstance setTaskIndicatorShouldAnimate:NO]; SPBeginWaitingAlertSheet(@"title", @@ -738,7 +740,7 @@ @selector(sheetDidEnd:returnCode:contextInfo:), @"runAllContinueStopSheet", NSLocalizedString(@"MySQL Error", @"mysql error message"), - [mySQLConnection getLastErrorMessage], + [mySQLConnection lastErrorMessage], &runAllContinueStopSheetReturnCode ); [tableDocumentInstance setTaskIndicatorShouldAnimate:YES]; @@ -771,7 +773,7 @@ databaseWasChanged = YES; } // If the query was cancelled, end all queries. - if ([mySQLConnection queryCancelled]) break; + if ([mySQLConnection lastQueryWasCancelled]) break; } // Reload table list if at least one query began with drop, alter, rename, or create @@ -800,7 +802,7 @@ if ( !queryCount ) { streamingResult = [mySQLConnection streamingQueryString:@""]; [streamingResult cancelResultLoad]; - [errors setString:[mySQLConnection getLastErrorMessage]]; + [errors setString:[mySQLConnection lastErrorMessage]]; } // add query to history @@ -817,7 +819,7 @@ // Set up the status string NSString *statusString = nil; NSString *statusErrorString = [errors length]?NSLocalizedString(@"Errors", @"Errors title"):NSLocalizedString(@"No errors", @"No errors title"); - if ( [mySQLConnection queryCancelled] ) { + if ( [mySQLConnection lastQueryWasCancelled] ) { if (totalQueriesRun > 1) { statusString = [NSString stringWithFormat:NSLocalizedString(@"%@; Cancelled in query %ld, after %@", @"text showing multiple queries were cancelled"), statusErrorString, @@ -862,7 +864,7 @@ [[affectedRowsText onMainThread] setStringValue:statusString]; // Restore automatic query retries - [mySQLConnection setAllowQueryRetries:YES]; + [mySQLConnection setRetryQueriesOnConnectionFailure:YES]; #ifndef SP_REFACTOR /* [tableDocumentInstance setQueryMode:] */ [tableDocumentInstance setQueryMode:SPInterfaceQueryMode]; @@ -937,9 +939,8 @@ /** * Processes a supplied streaming result set, loading it into the data array. */ -- (void)processResultIntoDataStorage:(MCPStreamingResult *)theResult +- (void)processResultIntoDataStorage:(SPMySQLFastStreamingResult *)theResult { - NSArray *tempRow; NSAutoreleasePool *dataLoadingPool; // Remove all items from the table @@ -951,7 +952,7 @@ // Set the column count on the data store before setting up anything else - // ensures that SPDataStorage is set up for timer-driven data loads - [resultData setColumnCount:[theResult numOfFields]]; + [resultData setColumnCount:[theResult numberOfFields]]; // Set up the table updates timer [[self onMainThread] initQueryLoadTimer]; @@ -960,10 +961,10 @@ dataLoadingPool = [[NSAutoreleasePool alloc] init]; // Loop through the result rows as they become available - while ((tempRow = [theResult fetchNextRowAsArray])) { + for (NSArray *eachRow in theResult) { pthread_mutex_lock(&resultDataLock); - SPDataStorageAddRow(resultData, tempRow); + SPDataStorageAddRow(resultData, eachRow); resultDataCount++; pthread_mutex_unlock(&resultDataLock); @@ -1289,7 +1290,7 @@ NSInteger firstErrorOccuredInQuery = [[errorDetails objectForKey:@"firstErrorQueryNumber"] integerValue]; // If errors occur, display them - if ( [mySQLConnection queryCancelled] || ([errorsString length] && !queryIsTableSorter)) { + if ( [mySQLConnection lastQueryWasCancelled] || ([errorsString length] && !queryIsTableSorter)) { // set the error text [errorText setString:[errorsString stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]]]; @@ -1300,7 +1301,7 @@ NSRange errorLineNumberRange = [errorsString rangeOfRegex:@"([0-9]+)[^0-9]*$" options:RKLNoOptions inRange:NSMakeRange(0, [errorsString length]) capture:1L error:nil]; // if error ID 1064 and a line number was found - if([mySQLConnection getLastErrorID] == 1064 && errorLineNumberRange.length) + if([mySQLConnection lastErrorID] == 1064 && errorLineNumberRange.length) { // Get the line number NSUInteger errorAtLine = [[errorsString substringWithRange:errorLineNumberRange] integerValue]; @@ -1508,7 +1509,7 @@ /** * Sets the connection (received from SPDatabaseDocument) and makes things that have to be done only once */ -- (void)setConnection:(MCPConnection *)theConnection +- (void)setConnection:(SPMySQLConnection *)theConnection { mySQLConnection = theConnection; currentQueryRanges = nil; @@ -1764,7 +1765,7 @@ [tableDocumentInstance startTaskWithDescription:NSLocalizedString(@"Checking field data for editing...", @"checking field data for editing task description")]; // Actual check whether field can be identified bijectively - MCPResult *tempResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SELECT COUNT(1) FROM %@.%@ %@", + SPMySQLResult *tempResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SELECT COUNT(1) FROM %@.%@ %@", [[columnDefinition objectForKey:@"db"] backtickQuotedString], [tableForColumn backtickQuotedString], fieldIDQueryStr]]; @@ -1774,7 +1775,7 @@ return [NSArray arrayWithObjects:[NSNumber numberWithInteger:-1], @"", nil]; } - NSArray *tempRow = [tempResult fetchRowAsArray]; + NSArray *tempRow = [tempResult getRowAsArray]; if([tempRow count] && [[tempRow objectAtIndex:0] integerValue] > 1) { // try to identify the cell by using blob data @@ -1794,7 +1795,7 @@ return [NSArray arrayWithObjects:[NSNumber numberWithInteger:-1], @"", nil]; } - tempRow = [tempResult fetchRowAsArray]; + tempRow = [tempResult getRowAsArray]; if([tempRow count] && [[tempRow objectAtIndex:0] integerValue] < 1) { [tableDocumentInstance endTask]; @@ -1817,7 +1818,6 @@ - (NSString *)argumentForRow:(NSUInteger)rowIndex ofTable:(NSString *)tableForColumn andDatabase:(NSString *)database includeBlobs:(BOOL)includeBlobs { NSArray *dataRow; - NSDictionary *theRow; id field; NSMutableArray *argumentParts = [NSMutableArray array]; @@ -1832,17 +1832,14 @@ dataRow = [resultData rowContentsAtIndex:rowIndex]; // Get the primary key if there is one, using any columns present within it - MCPResult *theResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM %@.%@", + SPMySQLResult *theResult = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM %@.%@", [database backtickQuotedString], [tableForColumn backtickQuotedString]]]; [theResult setReturnDataAsStrings:YES]; - if ([theResult numOfRows]) [theResult dataSeek:0]; NSMutableArray *primaryColumnsInSpecifiedTable = [NSMutableArray array]; - NSUInteger i; - for ( i = 0 ; i < [theResult numOfRows] ; i++ ) { - theRow = [theResult fetchRowAsDictionary]; - if ( [[theRow objectForKey:@"Key"] isEqualToString:@"PRI"] ) { + for (NSDictionary *eachRow in theResult) { + if ( [[eachRow objectForKey:@"Key"] isEqualToString:@"PRI"] ) { for (field in columnsInSpecifiedTable) { - if([[field objectForKey:@"org_name"] isEqualToString:[theRow objectForKey:@"Field"]]) { + if([[field objectForKey:@"org_name"] isEqualToString:[eachRow objectForKey:@"Field"]]) { [primaryColumnsInSpecifiedTable addObject:field]; } } @@ -1855,7 +1852,7 @@ // Build up the argument for (field in columnsToQuery) { id aValue = [dataRow objectAtIndex:[[field objectForKey:@"datacolumnindex"] integerValue]]; - if ([aValue isKindOfClass:[NSNull class]] || [aValue isNSNull]) { + if ([aValue isNSNull]) { [argumentParts addObject:[NSString stringWithFormat:@"%@ IS NULL", [[field objectForKey:@"org_name"] backtickQuotedString]]]; } else { NSString *fieldTypeGrouping = [field objectForKey:@"typegrouping"]; @@ -1875,14 +1872,14 @@ [argumentParts addObject:[NSString stringWithFormat:@"%@=b'%@'", [[field objectForKey:@"org_name"] backtickQuotedString], [aValue description]]]; } else if ([fieldTypeGrouping isEqualToString:@"geometry"]) { - [argumentParts addObject:[NSString stringWithFormat:@"%@=X'%@'", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareBinaryData:[aValue data]]]]; + [argumentParts addObject:[NSString stringWithFormat:@"%@=%@", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection escapeAndQuoteData:[aValue data]]]]; } // BLOB/TEXT data else if ([aValue isKindOfClass:[NSData class]]) { - [argumentParts addObject:[NSString stringWithFormat:@"%@=X'%@'", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareBinaryData:aValue]]]; + [argumentParts addObject:[NSString stringWithFormat:@"%@=%@", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection escapeAndQuoteData:aValue]]]; } else { - [argumentParts addObject:[NSString stringWithFormat:@"%@='%@'", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection prepareString:aValue]]]; + [argumentParts addObject:[NSString stringWithFormat:@"%@=%@", [[field objectForKey:@"org_name"] backtickQuotedString], [mySQLConnection escapeAndQuoteString:aValue]]]; } } } @@ -1919,11 +1916,11 @@ NSString *newObject = nil; if ( [anObject isKindOfClass:[NSCalendarDate class]] ) { - newObject = [NSString stringWithFormat:@"'%@'", [mySQLConnection prepareString:[anObject description]]]; + newObject = [mySQLConnection escapeAndQuoteString:[anObject description]]; } else if ( [anObject isKindOfClass:[NSNumber class]] ) { newObject = [anObject stringValue]; } else if ( [anObject isKindOfClass:[NSData class]] ) { - newObject = [NSString stringWithFormat:@"X'%@'", [mySQLConnection prepareBinaryData:anObject]]; + newObject = [mySQLConnection escapeAndQuoteData:anObject]; } else { if ( [[anObject description] isEqualToString:@"CURRENT_TIMESTAMP"] ) { newObject = @"CURRENT_TIMESTAMP"; @@ -1940,7 +1937,7 @@ && [[anObject description] isEqualToString:@"NOW()"]) { newObject = @"NOW()"; } else { - newObject = [NSString stringWithFormat:@"'%@'", [mySQLConnection prepareString:[anObject description]]]; + newObject = [mySQLConnection escapeAndQuoteString:[anObject description]]; } } @@ -1952,13 +1949,13 @@ // Check for errors while UPDATE if ([mySQLConnection queryErrored]) { SPBeginAlertSheet(NSLocalizedString(@"Error", @"error"), NSLocalizedString(@"OK", @"OK button"), NSLocalizedString(@"Cancel", @"cancel button"), nil, [tableDocumentInstance parentWindow], self, nil, nil, - [NSString stringWithFormat:NSLocalizedString(@"Couldn't write field.\nMySQL said: %@", @"message of panel when error while updating field to db"), [mySQLConnection getLastErrorMessage]]); + [NSString stringWithFormat:NSLocalizedString(@"Couldn't write field.\nMySQL said: %@", @"message of panel when error while updating field to db"), [mySQLConnection lastErrorMessage]]); return; } // This shouldn't happen – for safety reasons - if ( ![mySQLConnection affectedRows] ) { + if ( ![mySQLConnection rowsAffectedByLastQuery] ) { #ifndef SP_REFACTOR if ( [prefs boolForKey:SPShowNoAffectedRowsError] ) { SPBeginAlertSheet(NSLocalizedString(@"Warning", @"warning"), NSLocalizedString(@"OK", @"OK button"), nil, nil, [tableDocumentInstance parentWindow], self, nil, nil, @@ -2335,7 +2332,7 @@ return nil; } } - else if ([theValue isKindOfClass:[MCPGeometryData class]]) { + else if ([theValue isKindOfClass:[SPMySQLGeometryData class]]) { SPGeometryDataView *v = [[SPGeometryDataView alloc] initWithCoordinates:[theValue coordinates]]; image = [v thumbnailImage]; if(image) { @@ -3070,7 +3067,7 @@ if(![searchString length]) return @""; NSRange aRange; - MCPResult *theResult = nil; + SPMySQLResult *theResult = nil; NSDictionary *tableDetails; NSMutableString *theHelp = [NSMutableString string]; @@ -3089,14 +3086,16 @@ return SP_HELP_NOT_AVAILABLE; } // nothing found? - if(![theResult numOfRows]) { + if(![theResult numberOfRows]) { + // try to search via: HELP 'searchString%' theResult = [mySQLConnection queryString:[NSString stringWithFormat:@"HELP '%@%%'", [searchString stringByReplacingOccurrencesOfString:@"'" withString:@"\\'"]]]; + // really nothing found? - if(![theResult numOfRows]) + if(![theResult numberOfRows]) return @""; } - tableDetails = [[NSDictionary alloc] initWithDictionary:[theResult fetchRowAsDictionary]]; + tableDetails = [[NSDictionary alloc] initWithDictionary:[theResult getRowAsDictionary]]; if ([tableDetails objectForKey:@"description"]) { // one single help topic found if ([tableDetails objectForKey:@"name"]) { @@ -3171,9 +3170,9 @@ } } else { // list all found topics - NSUInteger i; - NSUInteger r = (NSUInteger)[theResult numOfRows]; - if (r) [theResult dataSeek:0]; + NSUInteger r = (NSUInteger)[theResult numberOfRows]; + if (r) [theResult seekToRow:0]; + // check if HELP 'contents' is called if(![searchString isEqualToString:SP_HELP_TOC_SEARCH_STRING]) [theHelp appendFormat:@"<br><i>%@ “%@”</i><br>", NSLocalizedString(@"Help topics for", @"help topics for"), searchString]; @@ -3182,9 +3181,9 @@ // iterate through all found rows and print them as HTML ul/li list [theHelp appendString:@"<ul>"]; - for ( i = 0 ; i < r ; i++ ) { - NSArray *anArray = [theResult fetchRowAsArray]; - NSString *topic = [anArray objectAtIndex:[anArray count]-2]; + [theResult setDefaultRowReturnType:SPMySQLResultRowAsArray]; + for (NSArray *eachRow in theResult) { + NSString *topic = [eachRow objectAtIndex:[eachRow count]-2]; [theHelp appendFormat:@"<li><a title='%@ “%@”' href='%@' class='internallink'>%@</a></li>", NSLocalizedString(@"Show MySQL help for", @"show mysql help for"), topic, topic, topic]; } @@ -4008,7 +4007,7 @@ value = [prefs objectForKey:SPNullValue]; } - if ([value isKindOfClass:[MCPGeometryData class]]) { + if ([value isKindOfClass:[SPMySQLGeometryData class]]) { value = [value wktString]; } |