SQLite (Spatialite) query on iOS works on 64-bit simulator but nothing else

699 Views Asked by At

I have a Spatialite database generated by a Django management command, which I'm embedding in an iOS app. The db opens perfectly, but I've found that querying the database on iOS only works if I'm running on a 64-bit device. Any other devices, simulated or otherwise, produce the output SQLITE_NOTADB on calling sqlite3_prepare_v2, with the error message file is encrypted or is not a database.

Obviously, the Mac that produced this database is a 64-bit machine, but SQLite databases should be bit-agnostic, so I don't see how this should be a problem. Perhaps this doesn't apply to Spatialite databases? Are there any flags I can use with any of the SQLite functions (maybe sqlite3_open or sqlite3_prepare_v2 or maybe a pragma command) to make it read the file in the same way as the 64-bit arch iOS does? Or perhaps there is a way to generate the Spatialite db from django in a more platform-compatible format? Any suggestions here are welcome.

Here's a snippet of my code, if anyone can find any obvious problems:

@implementation DataModel

@synthesize db;

- (id) init {
    self = [super init];
    if (self != nil) {
        spatialite_init(1);

        sqlite3 *newDbConnection;
        if (sqlite3_open([[self spatialiteDbPath] UTF8String], &newDbConnection) == SQLITE_OK) {
            NSLog(@"Database opened successfully");
            db = newDbConnection;
        } else {
            NSLog(@"Error opening database");
            db = NULL;
        }

    }

    return self;
}

- (NSArray *) getLockupsForRegion: (MKCoordinateRegion) region {

    NSMutableArray *lockups = [[NSMutableArray alloc] init];
    NSString *query = [NSString stringWithFormat:@"\
            SELECT name, X(location) as lat, Y(location) as lon, \
                   covered, type, capacity \
            FROM lockups_lockup \
            WHERE WITHIN(location, GeomFromText('POLYGON((%f %f, %f %f, %f %f, %f %f))'));",
                       regionCorners[0].latitude, regionCorners[0].longitude,
                       regionCorners[1].latitude, regionCorners[1].longitude,
                       regionCorners[2].latitude, regionCorners[2].longitude,
                       regionCorners[3].latitude, regionCorners[3].longitude];

    sqlite3_stmt *statement;
    if(db &&
       sqlite3_prepare_v2(db, [query UTF8String], -1, &statement, NULL) == SQLITE_OK) {
        while(sqlite3_step(statement) == SQLITE_ROW) {
            NSLog(@"A row");
        }
    } else {
        NSLog(@"Query failed for reason: %s", sqlite3_errmsg(db));
        NSLog(@"Sqlite version: %s", sqlite3_version);
    }

    return lockups;

}

@end

Launching the app and running on a non-64bit iOS platform getLockupsForRegion: produces the log output:

SpatiaLite version ..: 4.1.1    Supported Extensions:
    - 'VirtualShape'    [direct Shapefile access]
    - 'VirtualDbf'      [direct DBF access]
    - 'VirtualText'     [direct CSV/TXT access]
    - 'VirtualNetwork'  [Dijkstra shortest path]
    - 'RTree'       [Spatial Index - R*Tree]
    - 'MbrCache'        [Spatial Index - MBR cache]
    - 'VirtualSpatialIndex' [R*Tree metahandler]
    - 'VirtualFDO'      [FDO-OGR interoperability]
    - 'SpatiaLite'      [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.8.0, 6 March 2012
GEOS version ........: 3.4.2-CAPI-1.8.2 r3921
2013-12-31 00:29:39.567 App[8320:70b] Database opened successfully
2013-12-31 00:29:48.128 App[8320:70b] Query failed for reason: file is encrypted or is not a database
2013-12-31 00:29:48.710 App[8320:70b] Sqlite version: 3.8.1

And the log output under arm64:

SpatiaLite version ..: 4.1.1    Supported Extensions:
    - 'VirtualShape'    [direct Shapefile access]
    - 'VirtualDbf'      [direct DBF access]
    - 'VirtualText'     [direct CSV/TXT access]
    - 'VirtualNetwork'  [Dijkstra shortest path]
    - 'RTree'       [Spatial Index - R*Tree]
    - 'MbrCache'        [Spatial Index - MBR cache]
    - 'VirtualSpatialIndex' [R*Tree metahandler]
    - 'VirtualFDO'      [FDO-OGR interoperability]
    - 'SpatiaLite'      [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 4.8.0, 6 March 2012
GEOS version ........: 3.4.2-CAPI-1.8.2 r3921
2013-12-31 01:10:34.491 App[8548:70b] Database opened successfully
2013-12-31 01:10:44.913 App[8548:70b] A row
2013-12-31 01:10:44.913 App[8548:70b] A row
2013-12-31 01:10:44.914 App[8548:70b] A row
2013-12-31 01:10:44.914 App[8548:70b] A row
2013-12-31 01:10:44.915 App[8548:70b] A row
2013-12-31 01:10:44.915 App[8548:70b] A row
2013-12-31 01:10:44.916 App[8548:70b] A row
2013-12-31 01:10:44.916 App[8548:70b] A row

Versions:

  • Python: 2.7.5
  • Django: 1.6
  • PySqlite: 2.6.3
  • Sqlite (on machine generating Spatialite db): 3.8.1
  • Spatialite (on machine generating Spatialite db): 4.1.1
  • Sqlite (on iOS): 3.8.1
  • Spatialite (on iOS): 4.1.1

Any suggestions on this would be much appreciated!

0

There are 0 best solutions below