How to join two tables from two different database

Question

My scenario is like this. for a security reason, I decide I put app data to the bundle and user data to the document directory. the problem is how to join the tables from the different databases? if not possible do I have to create a table to the database in document directory?

Answer 1

Sqlite3 allows it to attach foreign database to the current connection. Imagine, you have two databases, each one holds an own connection via FMDatabase (for multithreading purposes you should use FMDatabaseQueue instead). Now, attach the other database to the connection of the first database. Then you can join from database 1 to a table in database 2. Important: all this happens via sqlite per sql statement and has nothing to do with FMDB.

I'll provide example-code at github: https://github.com/itinance/MultiDatabaseJoin

For simplicity i placed the database-files under /tmp-directory. With iOS-Simulator this is working well, i haven't tested this code in a really device, where you would place the database in the document folder or something similar (but thats not the point yet).

The important thing here is the following query in sqlite3:

[db1 executeStatements:@"ATTACH DATABASE '/tmp/tmp2.db' AS second_db"];

At the database connection db1 we attach the database-file from the second database.

After this attaching you can join in Database-Connection 1 to a table in database connection 2 in a query like this one:

SELECT a.id, a.name AS aname, b.name AS bname FROM a INNER JOIN second_db.b ON b.a_id = a.id

Here is the whole code i tried:

FMDatabase *db1 = [FMDatabase databaseWithPath:@"/tmp/tmp1.db"];
FMDatabase *db2 = [FMDatabase databaseWithPath:@"/tmp/tmp2.db"];

[db1 open];
[db2 open];

[db1 executeStatements:@"CREATE TABLE a (id INTEGER, name TEXT)"];
[db1 executeStatements:@"INSERT INTO a (id, name) VALUES (1, 'foo'), (2, 'bar')"];

[db2 executeStatements:@"CREATE TABLE b (id INTEGER, a_id INTEGER, name TEXT)"];
[db2 executeStatements:@"INSERT INTO b (id, a_id, name) VALUES (1, 1, 'b_foo'), (2, 2, 'b_bar')"];

bool success = [db1 executeStatements:@"ATTACH DATABASE '/tmp/tmp2.db' AS second_db"];
if(!success) {
    NSLog(@"%@", db1.lastErrorMessage);
    return YES;
}

FMResultSet* rs = [db1 executeQuery:@"SELECT a.id, a.name AS aname, b.name AS bname FROM a INNER JOIN second_db.b ON b.a_id = a.id"];
while( [rs next]) {
    NSLog(@"%@, %@", [rs stringForColumn:@"aname"], [rs stringForColumn:@"bname"]);
}
[rs close];