Problems attaching two sqlite databases with https://cocoapods.org/pods/SQLite.swift

Question

I am using stephencelis/SQLite.swift and try to attach 2 databases at runtime:

ATTACH DATABASE \'(databasePath.Local)\' AS LOCAL_USER_DB;

//
//  DatabaseWrapper.swift
// ...
//

import UIKit
import SQLite

class DatabaseWrapper {

    // MARK: - Constants

    struct databaseName {
        static let Interface: String = "app_interface_sqlite3_db.db3"
        static let Local: String = "app_local_sqlite3_db_local.db3"
    }
    struct databasePath {
        static let path = NSSearchPathForDirectoriesInDomains(
            .documentDirectory, .userDomainMask, true
        ).first!
        static let Interface = "\(path)/\(databaseName.Interface)"
        static let Local = "\(path)//\(databaseName.Local)"
        static let Interface_Bundle: String = Bundle.main.path(forResource: "app_interface_sqlite3_db", ofType: "db3") ?? ""
        static let Local_Bundle: String = Bundle.main.path(forResource: "app_local_sqlite3_db_local", ofType: "db3") ?? ""
    }

    // MARK: Copy Databases

    public static func copyDatabasesIfNeeded() {
        copyDatabaseIfNeeded(databaseName: databaseName.Interface)
        copyDatabaseIfNeeded(databaseName: databaseName.Local)
    }

    public static func copyDatabaseIfNeeded(databaseName: String) {

        print("PATH: \(databasePath.Interface)")

        // Move database file from bundle to documents folder
        let fileManager = FileManager.default
        let documentsUrl = fileManager.urls(for: .documentDirectory,
                                            in: .userDomainMask)

        guard documentsUrl.count != 0 else {
            return // Could not find documents URL
        }

        let finalDatabaseURL = documentsUrl.first!.appendingPathComponent(databaseName)

        if !( (try? finalDatabaseURL.checkResourceIsReachable()) ?? false) {
            print("DB does not exist in documents folder")

            let documentsURL = Bundle.main.resourceURL?.appendingPathComponent(databaseName)

            do {
                try fileManager.copyItem(atPath: (documentsURL?.path)!, toPath: finalDatabaseURL.path)
            } catch let error as NSError {
                print("Couldn't copy file to final location! Error:\(error.description)")
            }

        } else {
            print("Database file found at path: \(finalDatabaseURL.path)")
        }
    }

    // MARK: - Attach Local User Database TESTS
    public static func attachDatabase() {

        do {
            // if the database connection works

            let db = try Connection(databasePath.Interface);

            let attachStr = "ATTACH DATABASE \'\(databasePath.Local)\' AS LOCAL_USER_DB;"

            try db.execute(attachStr)

        } catch {
            // handle error
            print("Unexpected error: \(error).")
        }

    }

    public static func checkIfDatabase2IsAttached() -> Bool {

        var isAllreadyAttached = false

        do {
            // if the database connection works

            let db = try Connection(databasePath.Interface);

            // seq, name, file
            var row_counter = 0
            for row in try db.prepare("PRAGMA database_list;") {
                print("\n\n----------\row: \(row)\n----------\n\n")
                row_counter += 1
            }
            isAllreadyAttached = (row_counter == 2) ? true : false

        } catch {
            // handle error
            print("Unexpected error: \(error).")
        }

        return isAllreadyAttached
    }

    public static func testIfDatabaseWasAttached() {

        do {
            // if the database connection works

            let db = try Connection(databasePath.Interface);

            for row in try db.prepare("SELECT * FROM LOCAL_USER_DB.users;") {
                print("row: \(row)")
            }

        } catch {
            // handle error
            print("Unexpected error: \(error).")
        }

    }

    // ...

}

When I call

// Copy both databases if needed
        DatabaseWrapper.copyDatabasesIfNeeded()
        DatabaseWrapper.attachDatabase()

        if (DatabaseWrapper.checkIfDatabase2IsAttached()) {
            print("ATTACHED")
        } else {
            print("NOT ATTACHED YET")
        }

        DatabaseWrapper.testIfDatabaseWasAttached()

Unfortunately I doesn't work and I do not get any error messages.

In the consule I get the Log:

---------- ow: [Optional(0), Optional("main"), Optional("..../Developer/CoreSimulator/Devices/F93B5F0C-87D0-439C-ACB9-B1C6B5B7BE60/data/Containers/Data/Application/BB9587A1-2512-4003-ABC0-8AB1A7F6B4AF/Documents/app_interface_sqlite3_db.db3")]

NOT ATTACHED YET Unexpected error: no such table: LOCAL_USER_DB.users (code: 1).

Has anyone a clue, has done this before or anyone help me?

Thanks in advance.

Answer 1

As I see, the issue related to testIfDatabaseWasAttached(). Are you sure that your table has name LOCAL_USER_DB.users? You can use this code - I've checked and get users from table LOCAL_USER_DB.users (the table has a strange name but ok).

public static func testIfDatabaseWasAttached() {
    do {

        let db = try Connection(databasePath.Interface);
        for element in try db.prepare(Table("LOCAL_USER_DB.users")) {
            print(element)
        }
    } catch {
        // handle error
        print("Unexpected error: \(error).")
    }
}

you should be sure that you have table with name LOCAL_USER_DB.users and error says about your issue. This struct of db:

PS when I run your code - 2 databases are created in [.documents], but they are completely empty (no data at all, there are two db only). I recommend check logic of flow

Answer 2

Maybe this helps for those having this same issue. It's a temporary "solution" (next step):

Found out: It's on the planing list but not implemented yet. It's good to know but it's a pitty. Since Stephen Celis project since to be the be the best sqlite one for Swift.

Solutions are:

1. Contribute to the project. See discussion: https://github.com/stephencelis/SQLite.swift/issues/30
2. Write your own wrapper. For here Attach two SQLite-DB files to query over both files in Swift discribed solution worked. For further inspiration see also: Accessing an SQLite Database in Swift

For getting my project to run: first I go for 2. If it works I plan to post it for the inspiration of 1.