iOS extend SQLite by adding functions

gistfile1.m

/*
 Target: iOS, iOS5

 Let's say you have a SQLite DB to index a number of files, resources, or items in general.
 You want to perform a full-text search on this item. Example:


 TABLE: Item
 ----------------------------------------------------------------------
 ID | Name    | Description
 ----------------------------------------------------------------------
 1  | Foo Bar | Lorem ipsum dolor sit amet, consectetur adipiscing elit 

 I want item 1 to be a result for the query: "elit dolor".

 Possible approaches:
 1) Recompile SQLite yourself, enabling full text search extension (http://www.sqlite.org/fts3.html). Good luck with that.
 2) Smart trick:
    a) Sort alphanumerically the string to match: "elit dolor" -> "dolor elit"
    b) Sort alphanumerically the content of the columns where to perform the match, and remove punctuation:
           "Lorem ipsum dolor sit amet, consectetur adipiscing elit" -> "adipiscing amet consectetur dolor elit ipsum Lorem sit"
    c) In the string obtained in a), replace spaces with % and add % at the beginning and end of the string: "dolor elit" -> "%dolor%elit%"
    d) Perform the query using LIKE

 Now, 2.a and 2.c are pretty straightforward.
 2.b is a little trickier. The sorting has to be done while the query is executed, as the strings where to search are stored in a SQLite database.

 Basically we want to do this:
 SELECT * FROM Item WHERE sortString(Description) LIKE "%dolor%elit%";
 But what is that sortString() function? It doesn't exist in SQLite. That's right, but we could implement it and add it to SQLite on the fly
 when we need to use it, by simply passing a C function pointer on a per connection basis:

 sqlite3_create_function(searchIndexDB, "sortString", 1, SQLITE_UTF8, NULL, &sortString, NULL, NULL);

 So what we need to implement is that sortString C function. The cool thing is that in Objective-C you can add inline C code, and more importantly
 you can use Objective-C code within C functions, which makes life much easier, so you don't have to manually implement char* splitting and sorting.

 I came up with this solution, that works well and is pretty fast.
 Of course the searchTerm (elit dolor in the example above) is being sorted with the same steps.

*/

//C function to extend sqlite3 and sort a string alphanumerically
void sortString (sqlite3_context *context, int argc, sqlite3_value **argv) {
    assert(argc == 1);
    switch (sqlite3_value_type(argv[0])){
        case SQLITE_TEXT: {
            unsigned const char *string = sqlite3_value_text (argv[0]);
            
            //Get the Objective C string (much easier to manage)
            NSString *ocString = [[[NSString alloc] initWithUTF8String:(char *) string] autorelease];
            //Split it on punctuation and spaces
            NSMutableCharacterSet *cset = [NSCharacterSet punctuationCharacterSet];
            [cset addCharactersInString:@" "];
            NSArray *tokens = [ocString componentsSeparatedByCharactersInSet:cset];
            //Sort it
            NSArray *sortedArray = [tokens sortedArrayUsingSelector:@selector(localizedCaseInsensitiveCompare:)];
            ocString = [sortedArray componentsJoinedByString:@" "];
            ocString = [ocString stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]];
            
            const char *rString = [ocString UTF8String];
            
            //NSLog (@"%@", [[[NSString alloc] initWithUTF8String:(char *) rString] autorelease]);
            
            sqlite3_result_text(context, rString, strlen(rString) * sizeof(char), NULL);
            break;
        }
        default: {
            sqlite3_result_null(context);
            break;
        }
    }
}
//End C function