From 967579ef1225a134c50a686ee7caa9a3b5e293f3 Mon Sep 17 00:00:00 2001 From: Bibiko Date: Fri, 16 Oct 2009 16:46:41 +0000 Subject: =?UTF-8?q?=E2=80=A2=20fixed=20SPArrayAdditions=20method=20'compon?= =?UTF-8?q?entsJoinedByCommas'=20to=20use=20a=20mutable=20string=20to=20av?= =?UTF-8?q?oid=20crashes=20if=20array=20has=20a=20very=20large=20number=20?= =?UTF-8?q?of=20items=20(malloc=20error=20due=20to=20reassigning=20a=20NSS?= =?UTF-8?q?tring=20pointer)=20=E2=80=A2=20added=20to=20SPTableData=20metho?= =?UTF-8?q?d=20-=20(NSArray=20*)=20primaryKeyColumnNames=20-=20returns=20a?= =?UTF-8?q?ll=20column=20names=20which=20are=20set=20as=20PRIMARY=20KEYs?= =?UTF-8?q?=20-=20return=20nil=20if=20no=20PRIMARY=20KEY=20is=20set=20?= =?UTF-8?q?=E2=80=A2=20improved=20the=20deletion=20of=20rows=20-=20if=20cu?= =?UTF-8?q?rrent=20table=20has=20only=20one=20PRIMARY=20KEY=20field=20dele?= =?UTF-8?q?te=20all=20rows=20via=20DELETE=20FROM=20table=20WHERE=20pri=5Fk?= =?UTF-8?q?ey=20IN=20(=E2=80=A6)=20whereby=20the=20deletion=20query=20will?= =?UTF-8?q?=20be=20splitted=20into=20256k=20chunks?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Note: line 1790ff It has to be implemented a workaround for tables with more than one PRIMARY KEY – maybe via DELETE FROM table WHERE ( (pri_key1='…' AND pri_key2='…') OR (… AND …) OR … ) splitted in 256k chunks as well --- Source/SPArrayAdditions.m | 10 +++--- Source/SPTableData.h | 2 ++ Source/SPTableData.m | 38 ++++++++++++++++++++++ Source/TableContent.m | 83 +++++++++++++++++++++++++++++++++++++---------- 4 files changed, 111 insertions(+), 22 deletions(-) (limited to 'Source') diff --git a/Source/SPArrayAdditions.m b/Source/SPArrayAdditions.m index e19869fa..bbc732af 100644 --- a/Source/SPArrayAdditions.m +++ b/Source/SPArrayAdditions.m @@ -44,15 +44,15 @@ - (NSString *)componentsJoinedByCommas { - NSString *result = [NSString string]; + NSMutableString *result = [NSMutableString string]; + [result setString:@""]; + for (NSString *component in self) { if ([result length]) - { - result = [result stringByAppendingString:@","]; - } + [result appendString:@","]; - result = [result stringByAppendingString:component]; + [result appendString:component]; } return result; } diff --git a/Source/SPTableData.h b/Source/SPTableData.h index 4df13719..928d2b54 100644 --- a/Source/SPTableData.h +++ b/Source/SPTableData.h @@ -62,5 +62,7 @@ - (NSDictionary *) informationForView:(NSString *)viewName; - (BOOL) updateStatusInformationForCurrentTable; - (NSDictionary *) parseFieldDefinitionStringParts:(NSArray *)definitionParts; +- (NSArray *) primaryKeyColumnNames; + @end diff --git a/Source/SPTableData.m b/Source/SPTableData.m index ec02c301..52911330 100644 --- a/Source/SPTableData.m +++ b/Source/SPTableData.m @@ -968,6 +968,44 @@ return [fieldDetails autorelease]; } +/* + * Return the column names which are set to PRIMIARY KEY; returns nil if no PRIMARY KEY is set. + */ +- (NSArray *)primaryKeyColumnNames +{ + + NSString *selectedTable = [tableListInstance tableName]; + if(![selectedTable length]) return nil; + + MCPResult *r; + NSArray *resultRow; + NSInteger i; + NSMutableArray *keyColumns = [NSMutableArray array]; + + r = [mySQLConnection queryString:[NSString stringWithFormat:@"SHOW COLUMNS FROM %@ WHERE `key` = 'PRI'", [selectedTable backtickQuotedString]]]; + + if([r numOfRows] < 1) return nil; + + if (![[mySQLConnection getLastErrorMessage] isEqualToString:@""]) { + if ([mySQLConnection isConnected]) + NSRunAlertPanel(@"Error", [NSString stringWithFormat:@"An error occured while retrieving the PRIAMRY KEY data:\n\n%@", [mySQLConnection getLastErrorMessage]], @"OK", nil, nil); + return nil; + } + + + for( i = 0; i < [r numOfRows]; i++ ) { + resultRow = [r fetchRowAsArray]; + [keyColumns addObject:[NSArrayObjectAtIndex(resultRow, 0) description]]; + } + + if([keyColumns count]) + return keyColumns; + + return nil; +} + +#pragma mark - + - (void) dealloc { [columns release]; diff --git a/Source/TableContent.m b/Source/TableContent.m index 12658d30..49f8e27c 100644 --- a/Source/TableContent.m +++ b/Source/TableContent.m @@ -1655,7 +1655,8 @@ if ( [tempValue isNSNull] ) { [argument appendString:[NSString stringWithFormat:@"%@ IS NULL", [NSArrayObjectAtIndex(keys, i) backtickQuotedString]]]; } else if ( [tempValue isSPNotLoaded] ) { - // TODO + NSLog(@"Exceptional case: SPNotLoaded object found for method “argumentForRow:”!"); + return @""; } else { if ( [tempValue isKindOfClass:[NSData class]] ) @@ -1784,30 +1785,78 @@ NSUInteger index = [selectedRows firstIndex]; - while (index != NSNotFound) { + NSArray *primaryKeyFieldNames = [tableDataInstance primaryKeyColumnNames]; + + // TODO tentative → || [primaryKeyFields count] != 1 + if(primaryKeyFieldNames == nil || [primaryKeyFieldNames count] != 1) { + // delete row by row + while (index != NSNotFound) { - wherePart = [NSString stringWithString:[self argumentForRow:index]]; + wherePart = [NSString stringWithString:[self argumentForRow:index]]; - //argumentForRow might return empty query, in which case we shouldn't execute the partial query - if([wherePart length]) { - [mySQLConnection queryString:[NSString stringWithFormat:@"DELETE FROM %@ WHERE %@", [selectedTable backtickQuotedString], wherePart]]; + //argumentForRow might return empty query, in which case we shouldn't execute the partial query + if([wherePart length]) { + [mySQLConnection queryString:[NSString stringWithFormat:@"DELETE FROM %@ WHERE %@", [selectedTable backtickQuotedString], wherePart]]; - // Check for errors - if ( ![mySQLConnection affectedRows] || ![[mySQLConnection getLastErrorMessage] isEqualToString:@""]) { - // If error delete that index from selectedRows for reloading table if - // "ReloadAfterRemovingRow" is disbaled + // Check for errors + if ( ![mySQLConnection affectedRows] || ![[mySQLConnection getLastErrorMessage] isEqualToString:@""]) { + // If error delete that index from selectedRows for reloading table if + // "ReloadAfterRemovingRow" is disbaled + if(!reloadAfterRemovingRow) + [selectedRows removeIndex:index]; + errors++; + } + } else { if(!reloadAfterRemovingRow) [selectedRows removeIndex:index]; errors++; } - } else { - if(!reloadAfterRemovingRow) - [selectedRows removeIndex:index]; - errors++; + index = [selectedRows indexGreaterThanIndex:index]; } - index = [selectedRows indexGreaterThanIndex:index]; - } + } else { + // if table has only one PRIMARY KEY + // delete the fast way by using the PRIMARY KEY in an IN clause + NSMutableString *deleteQuery = [NSMutableString string]; + NSInteger affectedRows = 0; + + [deleteQuery setString:[NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ IN (", [selectedTable backtickQuotedString], [NSArrayObjectAtIndex(primaryKeyFieldNames,0) backtickQuotedString]]]; + + while (index != NSNotFound) { + + id keyValue = [NSArrayObjectAtIndex(tableValues, index) objectAtIndex:[[[tableDataInstance columnWithName:NSArrayObjectAtIndex(primaryKeyFieldNames,0)] objectForKey:@"datacolumnindex"] intValue]]; + + if([keyValue isKindOfClass:[NSData class]]) + [deleteQuery appendString:[NSString stringWithFormat:@"X'%@'", [mySQLConnection prepareBinaryData:keyValue]]]; + else + [deleteQuery appendString:[NSString stringWithFormat:@"'%@'", [keyValue description]]]; + + // Split deletion query into 256k chunks + if([deleteQuery length] > 256000) { + [deleteQuery appendString:@")"]; + [mySQLConnection queryString:deleteQuery]; + // Remember affected rows for error checking + affectedRows += [mySQLConnection affectedRows]; + // Reinit a new deletion query + [deleteQuery setString:[NSString stringWithFormat:@"DELETE FROM %@ WHERE %@ IN (", [selectedTable backtickQuotedString], [NSArrayObjectAtIndex(primaryKeyFieldNames,0) backtickQuotedString]]]; + } else { + [deleteQuery appendString:@","]; + } + index = [selectedRows indexGreaterThanIndex:index]; + } + + // Check if deleteQuery's maximal length was reached for the last index + // if yes omit the empty query + if(![deleteQuery hasSuffix:@"("]) { + // Replace final , by ) and delete the remaining rows + [deleteQuery setString:[NSString stringWithFormat:@"%@)", [deleteQuery substringToIndex:([deleteQuery length]-1)]]]; + [mySQLConnection queryString:deleteQuery]; + // Remember affected rows for error checking + affectedRows += [mySQLConnection affectedRows]; + } + errors = [selectedRows count] - affectedRows; + } + // Restore Console Log window's updating bahaviour [[SPQueryController sharedQueryController] setAllowConsoleUpdate:consoleUpdateStatus]; @@ -2327,7 +2376,7 @@ } BOOL isBlob = [tableDataInstance columnIsBlobOrText:[[aTableColumn headerCell] stringValue]]; - + // Open the sheet if the multipleLineEditingButton is enabled or the column was a blob or a text. if ([multipleLineEditingButton state] == NSOnState || isBlob) { -- cgit v1.2.3