lundi 30 novembre 2015

SQLite returning "no such table" using FMDB and RTree on iOS

I am building an iOS app and using SQLite using R*Tree indexing and FMDB for client-side data. When I run executeQuery against one of my virtual tables SQLite is returning DB Error: 1 "no such table: HuntEvent_index". However, I am able to insert records into the virtual table without any trouble (see code and image below) so I know it exists. Here's my code:

Table creation

CREATE VIRTUAL TABLE IF NOT EXISTS HuntEvent_index USING rtree( ID, minX, maxX, minY, maxY );
CREATE TABLE IF NOT EXISTS "HuntEvent_index_rowid"(rowid INTEGER PRIMARY KEY, nodeno INTEGER);
CREATE TABLE IF NOT EXISTS "HuntEvent_index_parent"(nodeno INTEGER PRIMARY KEY, parentnode INTEGER);
CREATE TABLE IF NOT EXISTS "HuntEvent_index_node"(nodeno INTEGER PRIMARY KEY, data BLOB);

Insert statement

INSERT INTO HuntEvent_index VALUES
 (1,SomeValue,SomeValue,SomeValue,SomeValue)

As I said, I am confident that both the CREATE statement and the INSERT statement are functioning properly because I can see the table and record inside the database I created on the device (Actual values removed): HuntEvent_index rows

Query method

-(void)getEventsWithinBoundingBox:(WSBoundingBox*)boundingBox
                   withCompletion:(SearchCompletionBlock)completion
{    
    FMDatabase* db = [[WSSQLiteHelper sharedHelper] getFMAppDB];

    if (![db openWithFlags:SQLITE_OPEN_READONLY]) {
        completion(nil, [NSError errorWithDomain:@"WSHP" 
            code:0 
            userInfo:[NSDictionary dictionaryWithObject:@"Could not open User Database." 
            forKey:@"message"]]);
    }

    //Find events IDs within the bounding box
    //
    NSMutableArray *activeEventIDs = [NSMutableArray array];
    NSMutableString* query = [NSMutableString string];
    [query appendString:@"SELECT ID FROM HuntEvent_index "];
    [query appendFormat:@"WHERE minX >= %f AND maxX <= %f ", 
        [[boundingBox.boundingBoxDictionary objectForKey:kBoundingBoxxMinFieldName] doubleValue], 
        [[boundingBox.boundingBoxDictionary objectForKey:kBoundingBoxxMaxFieldName] doubleValue]];
    [query appendFormat:@"AND minY >= %f AND maxY <= %f", 
        [[boundingBox.boundingBoxDictionary objectForKey:kBoundingBoxyMinFieldName] doubleValue], 
        [[boundingBox.boundingBoxDictionary objectForKey:kBoundingBoxyMaxFieldName] doubleValue]];

    FMResultSet* rs = [db executeQuery:query];
    while ([rs next])
    {
        [activeEventIDs addObject:[NSNumber numberWithInt:[rs intForColumnIndex:0]]];
    }
    [rs close];
    [db close]; 

    if ([activeEventIDs count] == 0)
    {
        completion(nil, [NSError errorWithDomain:@"WSHP" 
            code:1 
            userInfo: [NSDictionary dictionaryWithObject:@"No events found within region." 
            forKey:@"message"]]);
        return;
    }

    //Code continues...

So why is SQLite returning the no such table error? I am following the example from the SQLite R*Tree article as far as I can tell. Am I missing the obvious here?

Aucun commentaire:

Enregistrer un commentaire