sqlite - How to join two tables from two different databases in FMDB in a iOS app -

my scenario this. security reason, decide put app data bundle , user data document directory. problem how join tables different databases? if not possible have create table database in document directory?

sqlite3 allows attach foreign database current connection. imagine, have 2 databases, each 1 holds own connection via fmdatabase (for multithreading purposes should use fmdatabasequeue instead). now, attach other database connection of first database. can join database 1 table in database 2. important: happens via sqlite per sql statement , has nothing fmdb.

i'll provide example-code @ github: https://github.com/itinance/multidatabasejoin

for simplicity placed database-files under /tmp-directory. with ios-simulator working well, haven't tested code in device, place database in document folder or similar (but thats not point yet).

the important thing here following query in sqlite3:

[db1 executestatements:@"attach database '/tmp/tmp2.db' second_db"]; 

at database connection db1 attach database-file second database.

after attaching can join in database-connection 1 table in database connection 2 in query one:

select a.id, a.name aname, b.name bname inner join second_db.b on b.a_id = a.id 

here whole code tried:

fmdatabase *db1 = [fmdatabase databasewithpath:@"/tmp/tmp1.db"]; fmdatabase *db2 = [fmdatabase databasewithpath:@"/tmp/tmp2.db"];  [db1 open]; [db2 open];  [db1 executestatements:@"create table (id integer, name text)"]; [db1 executestatements:@"insert (id, name) values (1, 'foo'), (2, 'bar')"];  [db2 executestatements:@"create table b (id integer, a_id integer, name text)"]; [db2 executestatements:@"insert b (id, a_id, name) values (1, 1, 'b_foo'), (2, 2, 'b_bar')"];  bool success = [db1 executestatements:@"attach database '/tmp/tmp2.db' second_db"]; if(!success) {     nslog(@"%@", db1.lasterrormessage);     return yes; }  fmresultset* rs = [db1 executequery:@"select a.id, a.name aname, b.name bname inner join second_db.b on b.a_id = a.id"]; while( [rs next]) {     nslog(@"%@, %@", [rs stringforcolumn:@"aname"], [rs stringforcolumn:@"bname"]); } [rs close];