iphone - iPhone sqlite 应用程序可以附加到其他数据库吗？

Question

ATTACH DATABASE 命令对于在 sqlite 数据库文件之间传输行很有用，并允许您跨数据库连接表中的行例如

$ sqlite3 BookLoansDB.sqlite 
sqlite> ATTACH DATABASE '/Users/.../Documents/BooksDB.sqlite' AS books_db;
sqlite> select B.BookName, B.Pages, BL.LentTo from main.tblBookLoan BL inner join books_db.tblBook B on B.BookID = BL.BookID;
The Client|512|Jenny
The Pelican Brief|432|Mike

我怎样才能在 iPhone 上从 objective-c 做同样的事情。我用这种代码没有成功:

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentDirectory = [paths objectAtIndex:0];
const char *booksDBPath = [[documentDirectory stringByAppendingPathComponent:@"BooksDB.sqlite"] UTF8String];
const char *bookLoansDBPath = [[documentDirectory stringByAppendingPathComponent:@"BookLoansDB.sqlite"] UTF8String];
sqlite3 *bookLoansDB;
int result = sqlite3_open(bookLoansDBPath, &bookLoansDB);
sqlite3_stmt *attachStmt;
NSString *attachSQL = [NSString stringWithFormat: @"ATTACH DATABASE \'%s\' AS books_db", bookLoansDBPath];
result = sqlite3_prepare_v2(bookLoansDB, [attachSQL UTF8String] , -1, &attachStmt, nil);
char *errorMessage;
result = sqlite3_exec(bookLoansDB, [attachSQL UTF8String], NULL, NULL, &errorMessage);
sqlite3_stmt *selectStmt;
NSString *selectSQL = @"select * from main.tblBookLoan BL inner join books_db.tblBook B on B.BookID = BL.BookID";
result = sqlite3_prepare_v2(bookLoansDB, [selectSQL UTF8String] , -1, &selectStmt, nil);
// result == 1
result = sqlite3_step(selectStmt) ; 
// result == 21
if (result == SQLITE_ROW)
{
    //do something
}

可以吗？

Answer 1

我已经让示例正常工作(我的数据库名称在“附加数据库”SQL 中混淆了)。所以是的，它可以做到。感谢您为我指明正确的方向 Deepmist

因为这样的例子很难找到，所以我在下面粘贴了工作版本。

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentDirectory = [paths objectAtIndex:0];
const char *booksDBPath = [[documentDirectory stringByAppendingPathComponent:@"BooksDB.sqlite"] UTF8String];
const char *bookLoansDBPath = [[documentDirectory stringByAppendingPathComponent:@"BookLoansDB.sqlite"] UTF8String];
sqlite3 *bookLoansDB;
if (sqlite3_open(bookLoansDBPath, &bookLoansDB) == SQLITE_OK) {
    NSString *attachSQL = [NSString stringWithFormat: @"ATTACH DATABASE \'%s\' AS books_db", booksDBPath];
    char *errorMessage;
    if (sqlite3_exec(bookLoansDB, [attachSQL UTF8String], NULL, NULL, &errorMessage) == SQLITE_OK) {
        sqlite3_stmt *selectStmt;
        NSString *selectSQL = @"select * from main.tblBookLoan BL inner join books_db.tblBook B on B.BookID = BL.BookID";
        if (sqlite3_prepare_v2(bookLoansDB, [selectSQL UTF8String] , -1, &selectStmt, nil) == SQLITE_OK) {
            int n=0;
            while (sqlite3_step(selectStmt) == SQLITE_ROW) {
                //do something
            }
        }
        else {
            NSLog(@"Error while creating select statement: '%s'", sqlite3_errmsg(bookLoansDB));
        }
    }
    else {
        NSLog(@"Error while attaching databases: '%s'", errorMessage);
    }
}
else {
    NSLog(@"Failed to open database at %@ with error %s", booksDBPath, sqlite3_errmsg(bookLoansDB));
    sqlite3_close(bookLoansDB);
}