附加两个 SQLite-DB 文件以在 Swift 中查询这两个文件 [英] Attach two SQLite-DB files to query over both files in Swift

查看:16
本文介绍了附加两个 SQLite-DB 文件以在 Swift 中查询这两个文件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在 iOS11 的 Swift4 中,我想附加两个 SQLite 数据库文件(file1.db 和 file2.db),以便对所有数据(即两个数据库)进行查询.

In Swift4 for iOS11 I would like to attach two SQLite database-files (file1.db and file2.db) in order to make a query over all data (i.e. over both databases).

我已经使用以下代码在一个 SQLite 数据库中成功实现了一个 SQL 查询(..这里有一个名称查询的例子......):

I have successfully implemented a SQL-query in one SQLite database using the following code (..here with an example of a name-query...):

func readOneDB() -> String? {

    // create SQL-DB FilePath
    do {
        // create a documents-URL from filename
        self.file_1_URL = try createSQLFilePath(fileName: "file1", fileExtension: "db")
    }
    catch {
        return nil
    }
    let query =
    """
    SELECT DISTINCT n.locations
    FROM names n
    WHERE  n.name = "\(self.myName)"
    """

    // Open SQLite database
    var db: OpaquePointer? = nil
    if sqlite3_open(self.file_1_URL?.absoluteString, &db) == SQLITE_OK {

        var statement: OpaquePointer? = nil

        // Run SELECT query from db
        if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {

            // Loop through all results from query
            while sqlite3_step(statement) == SQLITE_ROW {

                let name = sqlite3_column_text(statement, 0)
                if name != nil {
                    let versionString = String(cString:name!)
                    return nameString
                } else {
                    print("name not found", terminator: "")
                    return nil
                }
            }
        } else {
            let errmsg = String(cString: sqlite3_errmsg(db))
            print("error running query: \(errmsg)")
            return nil
        }
    } else {
        print("error opening database")
        return nil
    }
    return nil
}

现在,我读到使用以下 sql-cmd 可以将两个 SQL 数据库附加在一起(以便对它们进行查询):ATTACH

Now, I read that with the following sql-cmd it is possible to attach two SQL-databases together (in order to make a query over both of them): ATTACH

事实上,我有以下代码不会引发异常 - 但仍然无法 100% 工作

In fact, I have the following code that does not throw an exception - but still not working 100%

let attachStr = "ATTACH DATABASE \'\(self.file_2_URL!)\' AS SECOND"
if (sqlite3_exec(db, attachStr, nil, &statement, nil) == SQLITE_OK) {
    print("Success!")
}

印有成功!"的字样运行代码时.

There is a print of "Success!" when running the code.

当然,通过attach-code获取成功消息,我认为就是这样(并且db-object将两个数据库统一起来)

Of course, getting the success-message with the attach-code, I thought this is it (and the db-object has both databases united)

但实际上没有!:不幸的是,查询仍然只带来第一个数据库(即文件 1)的结果,而没有附加的第二个文件 2 的查询结果.

But actually NO ! : The query still only brings results from the first database (i.e. file1) and no query-results of the attached second file2, unfortunately.

怎么了?

这是带有附件代码和查询的完整代码(见下文).

Here is the complete code with the attachment-code and the query (see below).

为什么查询仍然只提供第一个数据库(file1)的结果,而不是两个数据库(即file1和file2)的结果????

Why is the query still only delivering results from the first database (file1) and not from both databases (i.e. file1 and file2) ????

我需要在代码中进行哪些更改才能使其正常工作????

What changes do I need to make in the code for this to work ????

func readTwoDBs() -> String? {

    // create SQL-DB FilePath
    do {
        // create documents-URLs for two filenames
        self.file_1_URL = try createSQLFilePath(fileName: "file1", fileExtension: "db")
        self.file_2_URL = try createSQLFilePath(fileName: "file2", fileExtension: "db")
    }
    catch {
        return nil
    }
    let query =
    """
    SELECT DISTINCT n.locations
    FROM names n
    WHERE  n.name = "\(self.myName)"
    """

    // Open SQLite database
    var db: OpaquePointer? = nil
    if sqlite3_open(self.file_1_URL?.absoluteString, &db) == SQLITE_OK {

        var statement: OpaquePointer? = nil

        let attachStr = "ATTACH DATABASE \'\(self.file_2_URL!)\' AS SECOND"
        if (sqlite3_exec(db, attachStr, nil, &statement, nil) == SQLITE_OK) {
            print("Success!")
        }

        //// !!!!!!! Success is printed but the query still only works with file1 WHY ????????????????????

        // Run SELECT query from db
        statement = nil
        if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {

            // Loop through all results from query
            while sqlite3_step(statement) == SQLITE_ROW {

                let name = sqlite3_column_text(statement, 0)
                if name != nil {
                    let versionString = String(cString:name!)
                    return nameString
                } else {
                    print("name not found", terminator: "")
                    return nil
                }
            }
        } else {
            let errmsg = String(cString: sqlite3_errmsg(db))
            print("error running query: \(errmsg)")
            return nil
        }
    } else {
        print("error opening database")
        return nil
    }
    return nil
}

推荐答案

在 Joakim 的帮助下,我想我找到了解决方案:

With the help of Joakim, I think I found a solution:

ATTACH-cmd 实际上意味着多个数据库共享同一个数据库连接.正如 Joakim 指出的那样,如果想要从两个数据库文件中获取结果,仍然需要执行两次查询!

The ATTACH-cmd actually means that more than one database is sharing the same database-connection. As Joakim pointed out, one still needs to do two queries if one wants to get results from both database-files!

代码如下:(为了便于说明,该方法的返回值是一个元组,包含两个查询的两个结果(一个来自第一个数据库,第二个来自第二个数据库)

Here is the code : (for illustration purposes, the method's return-value is a tuple containing the two results of the two queries (one from the first database, second one from the second database)

func readTwoDBs() -> (String, String)? {

    var returnValue_1: String = ""
    var returnValue_2: String = ""

    // create SQL-DB FilePath
    do {
        // create documents-URLs for two filenames
        self.file_1_URL = try createSQLFilePath(fileName: "file1", fileExtension: "db")
        self.file_2_URL = try createSQLFilePath(fileName: "file2", fileExtension: "db")
    }
    catch {
        return nil
    }
    let query_1 =
    """
    SELECT DISTINCT n.locations
    FROM names n
    WHERE  n.name = "\(self.myName)"
    """

    // !! FOR QUERY_2 YOU NEED THE "MY_DB_TWO" NAME AND ITS TABLE IN THE FROM STATEMENT !!!!!!!
    // !! THIS "MY_DB_TWO" IS GIVEN BY THE ATTACH-cmd FURTHER DOWN... !!!!!!!! 
    let query_2 =
    """
    SELECT DISTINCT n.locations
    FROM MY_DB_TWO.names n
    WHERE  n.name = "\(self.myName)"
    """

    // Open SQLite database
    var db: OpaquePointer? = nil
    if sqlite3_open(self.file_1_URL?.absoluteString, &db) == SQLITE_OK {

        var statement: OpaquePointer? = nil

        let attachStr = "ATTACH DATABASE \'\(self.file_2_URL!)\' AS MY_DB_TWO"
        if (sqlite3_exec(db, attachStr, nil, &statement, nil) == SQLITE_OK) {
            print("Success!")
        }

        // Run SELECT query Nr1 from main.db
        statement = nil
        if sqlite3_prepare_v2(db, query_1, -1, &statement, nil) == SQLITE_OK {

            // Loop through all results from query
            while sqlite3_step(statement) == SQLITE_ROW {

                let name = sqlite3_column_text(statement, 0)
                if name != nil {
                    let nameString = String(cString:name!)
                    returnValue_1 = nameString
                } else {
                    print("name not found", terminator: "")
                    returnValue_1 = ""
                }
            }
        } else {
            let errmsg = String(cString: sqlite3_errmsg(db))
            print("error running query: \(errmsg)")
            returnValue_1 = ""
        }

        // Run SELECT query Nr2 from MY_DB_TWO
        statement = nil
        if sqlite3_prepare_v2(db, query_2, -1, &statement, nil) == SQLITE_OK {

            // Loop through all results from query
            while sqlite3_step(statement) == SQLITE_ROW {

                let name = sqlite3_column_text(statement, 0)
                if name != nil {
                    let nameString = String(cString:name!)
                    returnValue_2 = nameString
                } else {
                    print("name not found", terminator: "")
                    returnValue_2 = ""
                }
            }
        } else {
            let errmsg = String(cString: sqlite3_errmsg(db))
            print("error running query: \(errmsg)")
            returnValue_2 = ""
        }
    } else {
        print("error opening database")
    }

    return (returnValue_1, returnValue_2)
}

这篇关于附加两个 SQLite-DB 文件以在 Swift 中查询这两个文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆