From e4dc361f7cd4b8a5062f58548427a5f542917a7f Mon Sep 17 00:00:00 2001 From: stuconnolly Date: Sat, 10 Sep 2011 22:58:45 +0000 Subject: QueryKit additions. --- Frameworks/QueryKit/QKQuery.h | 32 +++++- Frameworks/QueryKit/QKQuery.m | 202 +++++++++++++++++++++++++++++++-- Frameworks/QueryKit/QKQueryParameter.h | 13 ++- Frameworks/QueryKit/QKQueryParameter.m | 19 ++++ Frameworks/QueryKit/QKQueryUtilities.h | 7 ++ Frameworks/QueryKit/QKQueryUtilities.m | 39 ++++++- UnitTests/QKSelectQueryTests.m | 6 +- 7 files changed, 292 insertions(+), 26 deletions(-) diff --git a/Frameworks/QueryKit/QKQuery.h b/Frameworks/QueryKit/QKQuery.h index 73da6cb9..b3670808 100644 --- a/Frameworks/QueryKit/QKQuery.h +++ b/Frameworks/QueryKit/QKQuery.h @@ -32,7 +32,15 @@ #import "QKQueryTypes.h" #import "QKQueryOperators.h" +#import "QKQueryParameter.h" +/** + * @class QKQuery QKQuery.h + * + * @author Stuart Connolly http://stuconnolly.com/ + * + * Main QueryKit query class. + */ @interface QKQuery : NSObject { NSString *_database; @@ -41,39 +49,42 @@ NSMutableString *_query; NSMutableArray *_parameters; NSMutableArray *_fields; + NSMutableArray *_groupByFields; + NSMutableArray *_orderByFields; QKQueryType _queryType; BOOL _quoteFields; + BOOL _orderDescending; } /** - * + * @property _database The database the query is to be run against (optional). */ @property (readwrite, retain, getter=database, setter=setDatabase:) NSString *_database; /** - * + * @property _table The table the query is to be run against. */ @property (readwrite, retain, getter=table, setter=setTable:) NSString *_table; /** - * + * @property _parameters The parameters (constraints) of the query. */ @property (readwrite, retain, getter=parameters, setter=setParameters:) NSMutableArray *_parameters; /** - * + * @property _fields The fields of the query. */ @property (readwrite, retain, getter=fields, setter=setFields:) NSMutableArray *_fields; /** - * + * @property _queryType The type of query to be built. */ @property (readwrite, assign, getter=queryType, setter=setQueryType:) QKQueryType _queryType; /** - * + * @property _quoteFields Indicates whether or not the query's fields should be quoted. */ @property (readwrite, assign, getter=quoteFields, setter=setQuoteFields:) BOOL _quoteFields; @@ -85,6 +96,15 @@ - (NSString *)query; - (void)addField:(NSString *)field; +- (void)addFields:(NSArray *)fields; + +- (void)addParameter:(QKQueryParameter *)parameter; - (void)addParameter:(NSString *)field operator:(QKQueryOperator)operator value:(id)value; +- (void)groupByField:(NSString *)field; +- (void)groupByFields:(NSArray *)fields; + +- (void)orderByField:(NSString *)field descending:(BOOL)descending; +- (void)orderByFields:(NSArray *)fields descending:(BOOL)descending; + @end diff --git a/Frameworks/QueryKit/QKQuery.m b/Frameworks/QueryKit/QKQuery.m index fcf81a06..cda3b877 100644 --- a/Frameworks/QueryKit/QKQuery.m +++ b/Frameworks/QueryKit/QKQuery.m @@ -31,8 +31,6 @@ // More info at #import "QKQuery.h" -#import "QKQueryParameter.h" -#import "QKQueryUtilities.h" static NSString *QKNoQueryTypeException = @"QKNoQueryType"; static NSString *QKNoQueryTableException = @"QKNoQueryTable"; @@ -44,6 +42,10 @@ static NSString *QKNoQueryTableException = @"QKNoQueryTable"; - (NSString *)_buildQuery; - (NSString *)_buildFieldList; - (NSString *)_buildConstraints; +- (NSString *)_buildGroupByClause; +- (NSString *)_buildOrderByClause; + +- (BOOL)_addString:(NSString *)string toArray:(NSMutableArray *)array; @end @@ -82,6 +84,11 @@ static NSString *QKNoQueryTableException = @"QKNoQueryTable"; [self setQueryType:-1]; [self setQuoteFields:NO]; + _orderDescending = NO; + + _groupByFields = [[NSMutableArray alloc] init]; + _orderByFields = [[NSMutableArray alloc] init]; + _query = [[NSMutableString alloc] init]; } @@ -96,29 +103,104 @@ static NSString *QKNoQueryTableException = @"QKNoQueryTable"; return _query ? [self _buildQuery] : @""; } +#pragma mark - +#pragma mark Fields + /** * Shortcut for adding a new field to this query. */ - (void)addField:(NSString *)field { - [_fields addObject:field]; + [self _addString:field toArray:_fields]; } /** - * Shortcut for adding a new parameter to this query. + * Convenience method for adding more than one field. + * + * @param The array (of strings) of fields to add. + */ +- (void)addFields:(NSArray *)fields +{ + for (NSString *field in fields) + { + [self addField:field]; + } +} + +#pragma mark - +#pragma mark Parameters + +/** + * Adds the supplied parameter. + * + * @param parameter The parameter to add. + */ +- (void)addParameter:(QKQueryParameter *)parameter +{ + if ([parameter field] && ([[parameter field] length] > 0) && ((NSInteger)[parameter operator] > -1) && [parameter value]) { + [_parameters addObject:parameter]; + } +} + +/** + * Convenience method for adding a new parameter. */ - (void)addParameter:(NSString *)field operator:(QKQueryOperator)operator value:(id)value +{ + [self addParameter:[QKQueryParameter queryParamWithField:field operator:operator value:value]]; +} + +#pragma mark - +#pragma mark Grouping + +/** + * Adds the supplied field to the query's GROUP BY clause. + */ +- (void)groupByField:(NSString *)field +{ + [self _addString:field toArray:_groupByFields]; +} + +/** + * Convenience method for adding more than one field to the query's GROUP BY clause. + */ +- (void)groupByFields:(NSArray *)fields { - QKQueryParameter *param = [QKQueryParameter queryParamWithField:field operator:operator value:value]; + for (NSString *field in fields) + { + [self groupByField:field]; + } +} + +#pragma mark - +#pragma mark Ordering + +/** + * Adds the supplied field to the query's ORDER BY clause. + */ +- (void)orderByField:(NSString *)field descending:(BOOL)descending +{ + _orderDescending = descending; - [_parameters addObject:param]; + [self _addString:field toArray:_orderByFields]; +} + +/** + * Convenience method for adding more than one field to the query's ORDER BY clause. + */ +- (void)orderByFields:(NSArray *)fields descending:(BOOL)descending +{ + for (NSString *field in fields) + { + [self orderByField:field descending:descending]; + } } #pragma mark - #pragma mark Private API /** - * + * Validates that everything necessary to build the query has been set. */ - (void)_validateRequiements { @@ -169,6 +251,19 @@ static NSString *QKNoQueryTableException = @"QKNoQueryTable"; [_query appendString:[self _buildConstraints]]; } + if (isSelect) { + NSString *groupBy = [self _buildGroupByClause]; + NSString *orderBy = [self _buildOrderByClause]; + + if ([groupBy length] > 0) { + [_query appendFormat:@" %@", groupBy]; + } + + if ([orderBy length] > 0) { + [_query appendFormat:@" %@", orderBy]; + } + } + return _query; } @@ -222,11 +317,7 @@ static NSString *QKNoQueryTableException = @"QKNoQueryTable"; for (QKQueryParameter *param in _parameters) { - NSString *field = [[param field] stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]]; - - [constraints appendString:field]; - [constraints appendFormat:@" %@ ", [QKQueryUtilities operatorRepresentationForType:[param operator]]]; - [constraints appendString:[[param value] description]]; + [constraints appendFormat:@"%@ ", param]; [constraints appendString:@" AND "]; } @@ -237,6 +328,87 @@ static NSString *QKNoQueryTableException = @"QKNoQueryTable"; return constraints; } + +/** + * Builds the string representation of the query's GROUP BY clause. + * + * @return The GROUP BY clause + */ +- (NSString *)_buildGroupByClause +{ + NSMutableString *groupBy = [NSMutableString string]; + + if ([_groupByFields count] == 0) return groupBy; + + [groupBy appendString:@"GROUP BY "]; + + for (NSString *field in _groupByFields) + { + [groupBy appendString:field]; + [groupBy appendString:@", "]; + } + + if ([groupBy hasSuffix:@", "]) { + [groupBy setString:[groupBy substringToIndex:([groupBy length] - 2)]]; + } + + return groupBy; +} + +/** + * Builds the string representation of the query's ORDER BY clause. + * + * @return The ORDER BY clause + */ +- (NSString *)_buildOrderByClause +{ + NSMutableString *orderBy = [NSMutableString string]; + + if ([_orderByFields count] == 0) return orderBy; + + [orderBy appendString:@"ORDER BY "]; + + for (NSString *field in _orderByFields) + { + [orderBy appendString:field]; + [orderBy appendString:@", "]; + } + + if ([orderBy hasSuffix:@", "]) { + [orderBy setString:[orderBy substringToIndex:([orderBy length] - 2)]]; + } + + if (_orderDescending) { + [orderBy appendString:@" DESC"]; + } + + return orderBy; +} + +/** + * Adds the supplied string to the supplied array, but only if the length is greater than zero. + * + * @param string The string to add to the array + * @param array The array to add the string to + * + * @return A BOOL indicating whether or not the string was added. + */ +- (BOOL)_addString:(NSString *)string toArray:(NSMutableArray *)array +{ + BOOL result = NO; + + if (!string || !array) return result; + + string = [string stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]]; + + if ([string length] > 0) { + [array addObject:string]; + + result = YES; + } + + return result; +} #pragma mark - @@ -249,7 +421,13 @@ static NSString *QKNoQueryTableException = @"QKNoQueryTable"; - (void)dealloc { + if (_table) [_table release], _table = nil; + if (_database) [_database release], _database = nil; if (_query) [_query release], _query = nil; + if (_parameters) [_parameters release], _parameters = nil; + if (_fields) [_fields release], _fields = nil; + if (_groupByFields) [_groupByFields release], _groupByFields = nil; + if (_orderByFields) [_orderByFields release], _orderByFields = nil; [super dealloc]; } diff --git a/Frameworks/QueryKit/QKQueryParameter.h b/Frameworks/QueryKit/QKQueryParameter.h index b7685f9d..3a24eb8d 100644 --- a/Frameworks/QueryKit/QKQueryParameter.h +++ b/Frameworks/QueryKit/QKQueryParameter.h @@ -32,6 +32,13 @@ #import "QKQueryOperators.h" +/** + * @class QKQueryParameter QKQueryParameter.h + * + * @author Stuart Connolly http://stuconnolly.com/ + * + * QueryKit query parameter class. + */ @interface QKQueryParameter : NSObject { NSString *_field; @@ -42,17 +49,17 @@ } /** - * + * @property _field The field component of the parameter. */ @property (readwrite, retain, getter=field, setter=setField:) NSString *_field; /** - * + * @property _operator The operator component of the parameter. */ @property (readwrite, assign, getter=operator, setter=setOperator:) QKQueryOperator _operator; /** - * + *@property _value The value component of the parameter. */ @property (readwrite, retain, getter=value, setter=setValue:) id _value; diff --git a/Frameworks/QueryKit/QKQueryParameter.m b/Frameworks/QueryKit/QKQueryParameter.m index b070e141..d131c156 100644 --- a/Frameworks/QueryKit/QKQueryParameter.m +++ b/Frameworks/QueryKit/QKQueryParameter.m @@ -31,6 +31,7 @@ // More info at #import "QKQueryParameter.h" +#import "QKQueryUtilities.h" @implementation QKQueryParameter @@ -38,6 +39,9 @@ @synthesize _operator; @synthesize _value; +#pragma mark - +#pragma mark Initialisation + + (QKQueryParameter *)queryParamWithField:(NSString *)field operator:(QKQueryOperator)op value:(id)value { return [[[QKQueryParameter alloc] initParamWithField:field operator:op value:value] autorelease]; @@ -56,6 +60,21 @@ #pragma mark - +- (NSString *)description +{ + NSMutableString *string = [NSMutableString string]; + + NSString *field = [_field stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]]; + + [string appendString:field]; + [string appendFormat:@" %@ ", [QKQueryUtilities operatorRepresentationForType:_operator]]; + [string appendString:[_value description]]; + + return string; +} + +#pragma mark - + - (void)dealloc { if (_field) [_field release], _field = nil; diff --git a/Frameworks/QueryKit/QKQueryUtilities.h b/Frameworks/QueryKit/QKQueryUtilities.h index 6dfaf110..352c5533 100644 --- a/Frameworks/QueryKit/QKQueryUtilities.h +++ b/Frameworks/QueryKit/QKQueryUtilities.h @@ -32,6 +32,13 @@ #import "QKQueryOperators.h" +/** + * @class QKQueryUtilities QKQueryUtilities.h + * + * @author Stuart Connolly http://stuconnolly.com/ + * + * QueryKit utilities class. + */ @interface QKQueryUtilities : NSObject + (NSString *)operatorRepresentationForType:(QKQueryOperator)operator; diff --git a/Frameworks/QueryKit/QKQueryUtilities.m b/Frameworks/QueryKit/QKQueryUtilities.m index cc354a90..5e4eb4ab 100644 --- a/Frameworks/QueryKit/QKQueryUtilities.m +++ b/Frameworks/QueryKit/QKQueryUtilities.m @@ -37,11 +37,11 @@ static NSString *QKUnrecognisedQueryOperatorException = @"QKUnrecognisedQueryOpe @implementation QKQueryUtilities /** - * + * Returns a string representation of the supplied operator type. * - * @param operator + * @param operator The operator * - * @return + * @return A string represenation of the operator. */ + (NSString *)operatorRepresentationForType:(QKQueryOperator)operator { @@ -52,6 +52,39 @@ static NSString *QKUnrecognisedQueryOperatorException = @"QKUnrecognisedQueryOpe case QKEqualityOperator: opString = @"="; break; + case QKNotEqualOperator: + opString = @"!="; + break; + case QKLikeOperator: + opString = @"LIKE"; + break; + case QKNotLikeOperator: + opString = @"NOT LIKE"; + break; + case QKInOperator: + opString = @"IN"; + break; + case QKNotInOperator: + opString = @"NOT IN"; + break; + case QKIsNullOperator: + opString = @"IS NULL"; + break; + case QKIsNotNullOperator: + opString = @"IS NOT NULL"; + break; + case QKGreaterThanOperator: + opString = @">"; + break; + case QKLessThanOperator: + opString = @"<"; + break; + case QKGreaterThanOrEqualOperator: + opString = @">="; + break; + case QKLessThanOrEqualOperator: + opString = @"<="; + break; default: [NSException raise:QKUnrecognisedQueryOperatorException format:@"Unrecognised query operator type: %d", operator]; break; diff --git a/UnitTests/QKSelectQueryTests.m b/UnitTests/QKSelectQueryTests.m index f1329f46..36a5d6d2 100644 --- a/UnitTests/QKSelectQueryTests.m +++ b/UnitTests/QKSelectQueryTests.m @@ -33,6 +33,8 @@ static NSString *SPTestFieldTwo = @"test_field2"; static NSString *SPTestFieldThree = @"test_field3"; static NSString *SPTestFieldFour = @"test_field4"; +static NSString *SPTestParameterOne = @"10"; + @implementation QKSelectQueryTests #pragma mark - @@ -47,7 +49,7 @@ static NSString *SPTestFieldFour = @"test_field4"; [_query addField:SPTestFieldThree]; [_query addField:SPTestFieldFour]; - [_query addParameter:SPTestFieldOne operator:QKEqualityOperator value:@"10"]; + [_query addParameter:SPTestFieldOne operator:QKEqualityOperator value:SPTestParameterOne]; } -(void)tearDown @@ -66,7 +68,7 @@ static NSString *SPTestFieldFour = @"test_field4"; - (void)testSelectQueryFieldsAreCorrect { NSString *query = [NSString stringWithFormat:@"SELECT %@, %@, %@, %@", SPTestFieldOne, SPTestFieldTwo, SPTestFieldThree, SPTestFieldFour]; - + NSLog(@"%@", _query); STAssertTrue([[_query query] hasPrefix:query], @"query fields"); -- cgit v1.2.3