将NSPredicate转换为SQL

Question

我需要一个类，可以将其直接放入我的项目中，将谓词作为参数发送，并返回表示SQL查询的NSString。类似下面的原型可能是这样的？

@interface PSPredicateToSQL : NSObject

+ (NSString *)SQLClauseForPredictate:(NSPredicate*) predicate;

@end

有没有人知道一个可以实现这个功能的类，如果没有，应该如何实现？

我需要实现一个搜索窗口，其中搜索数据存储在SQLite数据库中。

- Joseph Astrahan

---

我在我的程序中有一个SQLlite数据库，我正在制作一个使用NSPredicateEditor的搜索窗口，它有一个返回谓词的函数。如果我返回谓词格式，我会得到(Last Opened Date == CAST(394908328.515259, "NSDate") OR Medical Record Number CONTAINS "" OR Medical Record Number ENDSWITH "" OR Last Opened Date < CAST(394908328.515259, "NSDate") OR Paitient Name BEGINSWITH[c] "" )，这对于搜索数据库来说不是SQL友好的。我必须手动浏览返回的字符串并将其转换为SQL语法。 - Joseph Astrahan

这个项目：https://gist.github.com/iluvcapra/5118789 声称有一个转换器。有趣的是，主函数 NSString *SQLWhereClauseForPredictate(NSPredicate *predicate) 与您建议的原型一样拼写错误 "Predictate" :-) - Martin R

其实这不是巧合，我确实是从那里复制的。在来这里问问题之前，我总是会做好功课。虽然那是Python（我想是Python，或者其他语言）的转换，但我无法弄清如何将该代码转换为Objective-C格式（或者说），也无法测试它是否有效。我没有注意到他打错了一个字:P。我该如何测试他的代码？ - Joseph Astrahan

我已经完成了这个类，现在它运行得非常好。如果有人想看看我做了什么，请告诉我。我不得不完全重新设计那个示例中的伪代码。 - Joseph Astrahan

我添加了我的答案，其中包括我创建的可进行转换的工作代码。 - Joseph Astrahan

Answer 1

我终于完成了自己的类，希望能对你们有所帮助。

.H文件：

@interface PredicateToSQL : NSObject

+ (PredicateToSQL *) sharedObject;
- (NSString *) SQLFilterForPredicate:(NSPredicate *)predicate;
@end

.M File

#import "PredicateToSQL.h"

@implementation PredicateToSQL

static NSString *SQLNullValueString = @"NULL";

/* Implementation */
static PredicateToSQL *sharedObject;

+ (PredicateToSQL *) sharedObject
{
    return sharedObject;
}

+ (void) initialize
{
    sharedObject = [PredicateToSQL new];
}

- (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 stringByReplacingOccurrencesOfString:setOpt withString:@""] stringByReplacingOccurrencesOfString:@".." withString:@"."];
      retStr = [NSString stringWithFormat:@"%@(%@)",convertibleSetOperations[setOpt], clean];
     };
  };
if (retStr != nil) return(retStr);
return(keyPath);
}

- (NSString *) SQLSelectClauseForSubqueryExpression:(NSExpression *)expression
{
PSLog(@"SQLSelectClauseForSubqueryExpression not implemented");
return(nil);
}

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

for (NSExpression *obj in array) { [retArray addObject:[self SQLExpressionForNSExpression:obj]]; };
return([NSString stringWithFormat:@"(%@)",[retArray componentsJoinedByString:@","]]);
}



- (NSString *) SQLFunctionLiteralForFunctionExpression:(NSExpression *)exp
{  
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 allKeys] containsObject:[exp function]])
      { return(convertibleNullaryFunctions[[exp function]]);
       }
 else { if ([[convertibleUnaryFunctions allKeys] containsObject:[exp function]])
         { return([NSString stringWithFormat:@"%@(%@)",convertibleUnaryFunctions[[exp function]],[self SQLExpressionForNSExpression:[exp arguments][0]]]);
          }
    else { if ([[convertibleBinaryFunctions allKeys] containsObject:[exp function]])
            { return([NSString stringWithFormat:@"(%@ %@ %@)",[self SQLExpressionForNSExpression:[exp arguments][0]],convertibleBinaryFunctions[[exp function]],[self SQLExpressionForNSExpression:[exp arguments][1]]]);
             }
       else { PSLog(@"SQLFunctionLiteralForFunctionExpression could not be converted because it uses an unconvertible function");
             };
          };
       };
return(nil);
}

- (NSString *) SQLNamedReplacementVariableForVariable:(NSString *)var
{
//PSLog(@"SQLNamedReplacementVariableForVariable not implemented");
return(var);
}

/*
NSArray *temp = [gPatientDatabaseDictionary allKeysForObject:var];
NSString *key = [temp objectAtIndex:0];
return(key);
*/


- (NSString *)DatabaseKeyfor:(NSString *)obj
{
NSArray *keysForObj = [DatabaseDictionary allKeysForObject:obj];
if ([keysForObj count] > 0) return([keysForObj objectAtIndex:0]);
return(obj);
}



- (NSString *)SQLExpressionForLeftKeyPath:(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 stringByReplacingOccurrencesOfString:setOpt withString:@""] stringByReplacingOccurrencesOfString:@".." withString:@"."];
      retStr = [NSString stringWithFormat:@"%@(%@)",convertibleSetOperations[setOpt],clean];
     };
  };

if (retStr != nil) return([self DatabaseKeyfor:retStr]);
return([self DatabaseKeyfor:keyPath]);
}


- (NSString *) SQLConstantForLeftValue:(id) val
{
if (val == nil) return(SQLNullValueString);
if ([val isEqual:[NSNull null]]) return(SQLNullValueString);

if ([val isKindOfClass:[NSString class]])
      { //PSLog(@"SQLConstantForLeftValue val %@",val);
        return([self DatabaseKeyfor:val]);
       }
 else { if ([val respondsToSelector:@selector(intValue)])
         { return([self DatabaseKeyfor:[val stringValue]]);
          }
    else { return([self SQLConstantForLeftValue:[val description]]);
          };
      };
return(nil);
}



-(NSString *)SQLExpressionForLeftNSExpression:(NSExpression *)expression
{
NSString *retStr = nil;

switch ([expression expressionType])
 { case NSConstantValueExpressionType: { retStr = [self SQLConstantForLeftValue:[expression constantValue]];
                                         //NSLog(@"LEFT  NSConstantValueExpressionType %@",retStr); // contains 'Patient Name' etc..
                                         break; }
        case NSVariableExpressionType: { retStr = [self SQLNamedReplacementVariableForVariable:[expression variable]];
                                         //NSLog(@"LEFT NSVariableExpressionType %@",retStr);
                                         break; }
         case NSKeyPathExpressionType: { retStr = [self SQLExpressionForLeftKeyPath:[expression keyPath]];
                                         //NSLog(@"LEFT NSKeyPathExpressionType %@",retStr); // first "Patient Name'
                                         break; }
        case NSFunctionExpressionType: { retStr = [self SQLFunctionLiteralForFunctionExpression:expression];
                                         //NSLog(@"LEFT NSFunctionExpressionType %@",retStr);
                                         break; }
        case NSSubqueryExpressionType: { retStr = [self SQLSelectClauseForSubqueryExpression:expression];
                                         //NSLog(@"LEFT NSSubqueryExpressionType %@",retStr);
                                         break; }
       case NSAggregateExpressionType: { retStr = [self SQLLiteralListForArray:[expression collection]];
                                         //NSLog(@"LEFT NSAggregateExpressionType %@",retStr);
                                         break; }
        case NSUnionSetExpressionType: { break; }
    case NSIntersectSetExpressionType: { break; }
        case NSMinusSetExpressionType: { break; }

 case NSEvaluatedObjectExpressionType: { break; } // these can't be converted 
           case NSBlockExpressionType: { break; }
        //case NSAnyKeyExpressionType: { break; }
  };
return retStr;
}



-(NSString *)SQLConstantForValue:(id) val
{
if (val == nil) return(SQLNullValueString);
if ([val isEqual:[NSNull null]]) return(SQLNullValueString);

if ([val isKindOfClass:[NSString class]])
      { //NSLog(@"SQLConstantForValue val %@",val);
        return(val);
       }
 else { if ([val respondsToSelector:@selector(intValue)])
         { return([val stringValue]);
          }
    else { return([self SQLConstantForValue:[val description]]);
          };
      };
return(nil);
}



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

switch ([expression expressionType])
 { case NSConstantValueExpressionType: { retStr = [self SQLConstantForValue:[expression constantValue]];
                                         //NSLog(@"NSConstantValueExpressionType %@",retStr); // contains 'Patient Name' etc..
                                         break; }
        case NSVariableExpressionType: { retStr = [self SQLNamedReplacementVariableForVariable:[expression variable]];
                                         //NSLog(@"NSVariableExpressionType %@",retStr);
                                         break; }
         case NSKeyPathExpressionType: { retStr = [self SQLExpressionForKeyPath:[expression keyPath]];
                                         //NSLog(@"NSKeyPathExpressionType %@",retStr);
                                         break; }
        case NSFunctionExpressionType: { retStr = [self SQLFunctionLiteralForFunctionExpression:expression];
                                         //NSLog(@"NSFunctionExpressionType %@",retStr);
                                         break; }
        case NSSubqueryExpressionType: { retStr = [self SQLSelectClauseForSubqueryExpression:expression];
                                         //NSLog(@"NSSubqueryExpressionType %@",retStr);
                                         break; }
       case NSAggregateExpressionType: { retStr = [self SQLLiteralListForArray:[expression collection]];
                                         //PSLog(@"NSAggregateExpressionType %@",retStr);
                                         break; }
        case NSUnionSetExpressionType: { break; }
    case NSIntersectSetExpressionType: { break; }
        case NSMinusSetExpressionType: { break; }

 case NSEvaluatedObjectExpressionType: { break; } // these can't be converted 
           case NSBlockExpressionType: { break; }
        //case NSAnyKeyExpressionType: { break; }
  };
return retStr;
}

/*
- (NSString *) SQLInfixOperatorForOperatorType:(NSPredicateOperatorType) type
{
switch (type)
 {           case NSLessThanPredicateOperatorType: { return(@"<");
                                                     break; }
    case NSLessThanOrEqualToPredicateOperatorType: { return(@"<=");
                                                     break; }
          case NSGreaterThanPredicateOperatorType: { return(@">");
                                                     break; }
 case NSGreaterThanOrEqualToPredicateOperatorType: { return(@">=");
                                                     break; }
              case NSEqualToPredicateOperatorType: { return(@"=");
                                                     break; }
           case NSNotEqualToPredicateOperatorType: { return(@"<>");
                                                     break; }
              case NSMatchesPredicateOperatorType: { return(@"MATCH");
                                                     break; }
                   case NSInPredicateOperatorType: { return(@"IN");
                                                     break; }
              case NSBetweenPredicateOperatorType: { return(@"BETWEEN");
                                                     break; }
                 case NSLikePredicateOperatorType: { return(@"LIKE");
                                                     break; }
             case NSContainsPredicateOperatorType: { return(@"CONTAINS");
                                                     break; }
           case NSBeginsWithPredicateOperatorType:
             case NSEndsWithPredicateOperatorType: { //NSAssert(0,@"predicate not converted 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(nil);
}
*/

- (NSString *) SQLWhereClauseForComparisonPredicate:(NSComparisonPredicate *)predicate
{
NSString *leftSQLExpression  = [self SQLExpressionForLeftNSExpression:[predicate leftExpression]];
NSString *rightSQLExpression = [self SQLExpressionForNSExpression:[predicate rightExpression]];

switch ([predicate predicateOperatorType])
 {           case NSLessThanPredicateOperatorType: { return([NSString stringWithFormat:@"(%@ < '%@')",leftSQLExpression,rightSQLExpression]);
                                                     break; }
    case NSLessThanOrEqualToPredicateOperatorType: { return([NSString stringWithFormat:@"(%@ <= '%@')",leftSQLExpression,rightSQLExpression]);
                                                     break; }
          case NSGreaterThanPredicateOperatorType: { return([NSString stringWithFormat:@"(%@ > '%@')",leftSQLExpression,rightSQLExpression]);
                                                     break; }
 case NSGreaterThanOrEqualToPredicateOperatorType: { return([NSString stringWithFormat:@"(%@ >= '%@')",leftSQLExpression,rightSQLExpression]);
                                                     break; }
              case NSEqualToPredicateOperatorType: { return([NSString stringWithFormat:@"(%@ = '%@')",leftSQLExpression,rightSQLExpression]);
                                                     break; }
           case NSNotEqualToPredicateOperatorType: { return([NSString stringWithFormat:@"(%@ <> '%@')",leftSQLExpression,rightSQLExpression]);
                                                     break; }
              case NSMatchesPredicateOperatorType: { return([NSString stringWithFormat:@"(%@ MATCH '%@')",leftSQLExpression,rightSQLExpression]);
                                                     break; }
                   case NSInPredicateOperatorType: { return([NSString stringWithFormat:@"(%@ IN '%@')",leftSQLExpression,rightSQLExpression]);
                                                     break; }
              case NSBetweenPredicateOperatorType: { return([NSString stringWithFormat:@"(%@ BETWEEN '%@' AND '%@')",[self SQLExpressionForLeftNSExpression:[predicate leftExpression]],
                      [self SQLExpressionForNSExpression:[[predicate rightExpression] collection][0]],
                      [self SQLExpressionForNSExpression:[[predicate rightExpression] collection][1]]]);
                                                     break; }
                 case NSLikePredicateOperatorType:
             case NSContainsPredicateOperatorType: { return([NSString stringWithFormat:@"(%@ LIKE '%%%@%%')",leftSQLExpression,rightSQLExpression]);
                                                     break; }
           case NSBeginsWithPredicateOperatorType: { return([NSString stringWithFormat:@"(%@ LIKE '%@%%')",leftSQLExpression,rightSQLExpression]);
                                                     break; }
             case NSEndsWithPredicateOperatorType: { return([NSString stringWithFormat:@"(%@ LIKE '%%%@')",leftSQLExpression,rightSQLExpression]);
                                                     break; }
       case NSCustomSelectorPredicateOperatorType: { PSLog(@"SQLWhereClauseForComparisonPredicate custom selectors are not supported");
                                                     break; }
  };

/*
//NSAssert(0,@"predicate not converted because 'beginswith' and 'endswith' are not consistently supported by SQL");
//NSAssert(0,@"predicate cannot be converted to a where clause because it calls a custom selector");

NSString *comparator = [self SQLInfixOperatorForOperatorType:[predicate predicateOperatorType]];
if (comparator != nil)
   { if ([comparator isEqual:@"BETWEEN"])
      { return([NSString stringWithFormat:@"(%@ %@ '%@' AND '%@')",[self SQLExpressionForLeftNSExpression:[predicate leftExpression]],comparator,
                      [self SQLExpressionForNSExpression:[[predicate rightExpression] collection][0]],
                      [self SQLExpressionForNSExpression:[[predicate rightExpression] collection][1]]]);

       }
 else { if ([comparator isEqual:@"CONTAINS"])
         { return([NSString stringWithFormat:@"(%@ LIKE '%%%@%%')",[self SQLExpressionForLeftNSExpression:[predicate leftExpression]],
                      [self SQLExpressionForNSExpression:[predicate rightExpression]]]);
          }
    else { return([NSString stringWithFormat:@"(%@ %@ '%@')",[self SQLExpressionForLeftNSExpression:[predicate leftExpression]],comparator,
                      [self SQLExpressionForNSExpression:[predicate rightExpression]]]);
          };
       };
   } else { PSLog(@"SQLWhereClauseForComparisonPredicate predicate could not be converted to comparator"); };
   */
return(nil);
}

- (NSString *) SQLWhereClauseForCompoundPredicate:(NSCompoundPredicate *)predicate
{
NSMutableArray *subs = [NSMutableArray array];

for (NSPredicate *sub in [predicate subpredicates]) { [subs addObject:[self SQLFilterForPredicate:sub]]; };

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

//NSLog(@"SQLWhereClauseForCompoundPredicate conjunction %@",conjunction);

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

- (NSString *)SQLFilterForPredicate:(NSPredicate *)predicate
{    
if ([predicate respondsToSelector:@selector(compoundPredicateType)])
      { return([self SQLWhereClauseForCompoundPredicate:(NSCompoundPredicate *)predicate]);
       }
 else { if ([predicate respondsToSelector:@selector(predicateOperatorType)])
         { return([self SQLWhereClauseForComparisonPredicate:(NSComparisonPredicate *)predicate]);
          }
    else { PSLog(@"SQLFilterForPredicate predicate is not of a convertible class");
          }
      };
return(nil);
}

@end

您可以在某些全局变量中创建自己的数据库信息，如下所示。

gDatabaseTitles = [NSArray arrayWithObjects:@"Name",@"Phone Number",@"Location",@"Date Of Birth",@"Miscellaneous",nil];

   gDatabaseKeys = [NSArray arrayWithObjects:@"name",@"phone_number",@"location",@"date_of_birth",@"misc",nil];

   gDatabaseTypes = [NSArray arrayWithObjects:@"name TEXT",@"phone_number TEXT",@"location TEXT",@"date_of_birth DATE",@"misc TEXT",nil];

   gDatabaseDictionary = [NSDictionary dictionaryWithObjects:gPatientDatabaseTitles forKeys:gPatientDatabaseKeys];

我不得不编辑很多代码，以去掉与我的项目相关的内容，所以如果我漏掉了什么，这段代码可能无法编译，但这是有效的概念。如果您有任何问题，请让我知道，我会进行修复。在任何您看到gVariable的地方，那是我所指的全局变量，在我的项目中帮助了我，也可能对您有帮助。

- Joseph Astrahan