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!