NSPredicate2SQL.mm

static NSString *SQLNullValueString = [[NSString alloc] initWithString:@"NULL"];

/* Prototypes */

NSString *SQLWhereClauseForPredictate(NSPredicate *predicate);

NSString *SQLExpressionForNSExpression(NSExpression *expression);


/* Implementation */

NSString *SQLExpressionForKeyPath(NSString *keyPath) {
    NSString *retStr = nil;

    NSDictionary *convertibleSetOperations = @{
            @"@avg" = @"avg"
            @"@max" = @"max",
            @"@min" = @"min",
            @"@sum" = @"sum",
            @"@distinctUnionOfObjects" = @"distinct";
    };

    for (NSString *setOpt in [convertibleSetOperations allKeys]) {
        if ([keyPath hasSuffix:setOpt]) {
            NSString *clean = [[keyPath stringByReplacingOccurencesOfString:setOpt
                                                                 withString:@""]
                                        stringByReplacingOccurencesOfString:@".."
                                                                 withString:@"."];

            retStr = [NSString stringWithFormat:@"%@(%@)",
                convertibleSetOperations[setOpt], clean];
        }
    }
    

    NSAssert(0,@"%s Not Implemented",__func__);
    return retStr;
}

NSString *SQLSelectClauseForSubqueryExpression(NSExpression *expression) {
    NSString *retStr = nil;
    NSAssert(0,@"%s Not Implemented",__func__);
    return retStr;
}

NSString *SQLLiteralListForArray(NSArray *array) {
    NSMutableArray *retArray = [NSMutableArray array];

    for (NSExpression *obj in array) {
        [retArray addObject:SQLExpressionForNSExpression(obj)];
    }

    return [NSString stringWithFormat:@"(%@)",[retArray componentsJoinedByString:@","]];
}

NSString *SQLNamedReplacementVariableForVariable(NSString *var) {
    NSAssert(0,@"%s Not Implemented",__func__);
    return nil;
}

NSString *SQLConstantForValue(id val) {
    NSString *retStr = nil;

            if ([val isKindOfClass:[NSString class]]) {
                retStr = [NSString stringWithFormat:@"'%@'",
                    [val stringByReplacingOccurencesOfString@"'" 
                        withString:@"\\'"];

            } else if ([arg respondsToSelector:@selector(intValue]) {
                retStr = [val stringValue];
                
            } else if ([arg isEqual:[NSNull null]] || arg == nil ){
                retStr = SQLNullValueString;
                
            } else {
                retStr = SQLConstantForValue([val description]); 
            }

    return retStr;
}

NSString *SQLFunctionLiteralForFunctionExpression(NSExpression *exp) {
    
    NSString *retStr = nil;

    NSDictionary *convertibleNullaryFunctions = @{
                                                @"now"    = @"date('now')",
                                                @"random"   = @"random()" 
                                                };

    NSDictionary *convertibleUnaryFunctions = @{@"uppercase:" = @"upper"    ,
                                                @"lowercase:" = @"lower"    ,
                                                @"abs:"       = @"abs"
                                                }; 

    NSDictionary *convertibleBinaryFunctions = @{ 
                            @"add:to:"              = @"+" , 
                            @"from:subtract:"       = @"-" , 
                            @"multiply:by:"         = @"*" , 
                            @"divide:by:"           = @"/" ,
                            @"modulus:by:"          = @"%" , 
                            @"leftshift:by"         = @"<<",
                            @"rightshift:by:"       = @">>"
                            };

    if ([convertibleNullaryFunctions containsObject:
        [expression function]]) {
        retStr = convertibleNullaryFunctions[ [expression function] ];
    } else 
    if ([convertibleUnaryFunctions containsObject:
        [expression function]]) {
    retStr = [NSString stringWithFormat:@"%@(%@)",
            convertibleUnaryFunctions[ [expression function] ],
            SQLExpressionForNSExpression( [expression arguments[0]] )];
    } else
    if ([convertibleBinaryFunctions containsObject:
        [expression function]]) {
        retStr = [NSString stringWithFormat:@"(%@ %@ %@)",
            SQLExpressionForNSExpression( [expression arguments[0]] ) ,
            convertibleBinaryFunctions[ [expression function] ],
            SQLExpressionForNSExpression( [expression arguments[1]] )
            ];

    } else {
        NSAssert(0,@"the expression %@ could not be converted because "
                    "it uses an unconvertible function %@",expression,
                    [expression function]);
    }
    return retStr;
}

NSString *SQLExpressionForNSExpression(NSExpression *expression) {
    NSString *retStr = nil;

    switch ([expression expressionType]) {
        case NSConstantValueExpressionType:
            retStr = SQLConstantForValue([expression constantValue]);
            break;

        case NSVariableExpressionType:
            retStr = SQLNamedReplacementVariableForVariable([expression variable]);
            break;

        case NSKeyPathExpressionType:
            retStr = SQLExpressionForKeyPath([expression keyPath]);
            break;

        case NSFunctionExpressionType:
            retStr = SQLFunctionLiteralForFunctionExpression(expression);
            break;

        case NSSubqueryExpressionType:
            retStr = SQLSelectClauseForSubqueryExpression(expression);
            break;

        case case NSAggregateExpressionType:
            retStr = SQLLiteralListForArray([expression collection]);
            break;
       
        case NSUnionSetExpressionType:
        case NSIntersectSetExpressionType:
        case NSMinusSetExpressionType:
            // impl
            break;
        /* these can't be converted */
        case NSEvaluatedObjectExpressionType:
        case NSBlockExpressionType:
        
            break;
    }
    return retStr;
}

NSString *SQLInfixOperatorForOperatorType(NSPredicateOperatorType type) {
    NSString *comparator = nil;
    switch (type) {
        case NSLessThanPredicateOperatorType:               comparator = @"<";      break;
        case NSLessThanOrEqualToPredicateOperatorType:      comparator = @"<=";     break;
        case NSGreaterThanPredicateOperatorType:            comparator = @">";      break;
        case NSGreaterThanOrEqualToPredicateOperatorType:   comparator = @">=";     break;
        case NSEqualToPredicateOperatorType:                comparator = @"IS";     break;
        case NSNotEqualToPredicateOperatorType:             comparator = @"IS NOT"; break;
        case NSMatchesPredicateOperatorType:                comparator = @"MATCH";  break;
        case NSInPredicateOperatorType:                     comparator = @"IN";     break;
        case NSBetweenPredicateOperatorType:                comparator = @"BETWEEN";break;

        case NSLikePredicateOperatorType:
            NSAssert(0,@"predicate cannot be converted to a where clause because 'like' "
                        "uses an pattern matching syntax which is not converted at this "
                        "time.  Use 'MATCHES' instead.");               
            break;


        case NSContainsPredicateOperatorType:
        case NSBeginsWithPredicateOperatorType:
        case NSEndsWithPredicateOperatorType:
            NSAssert(0,@"predicate cannot be converted to a where clause because 'beginswith' "
                        "and 'endswith' are not consistently supported by SQL");
            break;
        
        case NSCustomSelectorPredicateOperatorType:
            NSAssert(0,@"predicate cannot be converted to a where clause because it calls a"
                        "custom selector");
            break;
    }
    return comparator;
}

NSString *SQLWhereClauseForComparisonPredicate(NSComparisonPredicate *predicate) {
    NSString *retStr = nil;

    NSAssert([predicate leftExpression] && [predicate rightExpression],
                @"The predicate %@ could not be converted, comparison predicates "
                "must have both a left-hand and right-hand expression". predicate);

    NSAssert([predicate comparisonPreicateModifier] != NSDirectPredicateModifier,
                @"Predicate %@ could not be converted to SQL because its predicate "
                "modifier is not NSDirectPredicateModifier.", predicate);
    
    NSAssert([predicate customSelector] == NULL,
                @"Predicate %@ could not be converted to SQL because it uses a "
                "custom selector.", predicate);

    NSString *comparator = nil;
    if (!retStr) {
        comparator = SQLInfixOperatorForOperatorType([predicate predicateOperatorType);
    }

    NSAssert(comparator,@"Predicate %@ could not be converted, the predicate operator "
                        "could not be converted.");
    
    if (!retStr) {
        if ([comparator isEqual:@"BETWEEN"]) {
            retStr = [NSString stringWithFormat:@"(%@ %@ %@ AND %@)",
                        SQLExpressionForNSExpression([predicate leftExpression]),
                        comparator,
                        SQLExpressionForNSExpression(
                            [[predicate rightExpression] collection][0]),
                        SQLExpressionForNSExpression(
                            [[predicate rightExpression] collection][1]);
        } else {
            retStr = [NSString stringWithFormat:@"(%@ %@ %@)",
                        SQLExpressionForNSExpression([predicate leftExpression]),
                        comparator,
                        SQLExpressionForNSExpression([predicate rightExpression]);
        }
    }

    return retStr;
}

NSString *SQLWhereClauseForCompoundPredicate(NSCompoundPredicate *predicate) {

    NSMutableArray *subs = [NSMutableArray array];
    for (NSPredicate *sub in [predicate subpredicates]) {
        [subs addObject:WhereClauseForPredicate(sub)];
    }

    NSString *conjunction;
    switch ([(NSCompoundPredicate *)predicate compoundPredicateType]) {
        case NSAndPredicateType:
            conjunction = @" AND ";
            break;
        case NSOrPredicateType:
            conjunction = @" OR ";
            break;
        case NSNotPredicateType:
            conjunction = @" NOT ";
            break;
    }

    return [NSString stringWithFormat:@"( %@ )", [subs componentsJoinedByString:conjunction]];
}

NSString *SQLWhereClauseForPredictate(NSPredicate *predicate) {
    NSString *retVal = nil;

    if ([predicate respondsToSelector:@selector(compoundPredicateType]) {
        retVal = SQLWhereClauseForCompoundPredicate((NSCompoundPredicate *)predicate);
    } else if ([predicate respondsToSelector:@selector(predicateOperatorType]) {
        retVal = SQLWhereClauseForComparisonPredicate((NSComparisonPredicate *)predicate);
    } else {
        NSAssert(0,@"predicate %@ cannot be converted to SQL because it is not of "
                    "a convertible class",predicate);
    }

    return retVal;
}