How to set up an inner join with GRDB

Question

The SQLite.Swift version works. With the GRDB version the app crashes and I get the following error. Thread 1: Fatal error: Database methods are not reentrant.

What have I done wrong on the GRDB version.

This is the SQLite.Swift version.

static func getCommon_CategoryXX(trick_Category_XRef_Table: String, category_Table: String, trickID: Int64) -> String
{
    let trick_Type_XRef_Table = Table(trick_Category_XRef_Table)
    let type_ID_Trick_XRef = Expression<Int64>("Type_ID")
    let trick_ID_Type_XRef = Expression<Int64>("Common_ID")
    
    let itemTypse_Table = Table(category_Table)
    let itemID = Expression<Int64>("ItemID")
    let itemName = Expression<String>("Item_Name")
            
    var categoty_Name: String = ""
    
    let theQuery = trick_Type_XRef_Table.join(.inner, itemTypse_Table, on: itemID == type_ID_Trick_XRef).filter(trick_ID_Type_XRef == trickID).select(itemName)
    
    do {
        for theName in try Database.shared.databaseConnection!.prepare(theQuery)
        {
            categoty_Name = theName[itemName]
        }
    } catch {
        print("Couldn't get the category type name! \(category_Table) \(error)")
    }
    // print(categoty_Name)
    // print("Cat XRef Table \(trick_Category_XRef_Table)")
    // print("Cat Table \(category_Table)")
    
    return categoty_Name
}

This is what I have for the GRDB version.

static func getCommon_Category(trick_Category_XRef_Table: String, category_Table: String, trickID: Int64) -> String {

    var categoty_Name: String = ""
    
    do {
        try Database_GRDB.shared.databaseConnection!.read { db in
            categoty_Name = try (String.fetchOne(db, sql: "SELECT Item_Name FROM " + category_Table + " INNER JOIN " + trick_Category_XRef_Table + " ON ItemID = Type_ID WHERE Common_ID = ?", arguments: [trickID]) ?? "")
        }
                    
    } catch {
        print("Couldn't get the category type name! \(category_Table) \(error)")
    }
    // print(categoty_Name)
    // print("Cat XRef Table \(trick_Category_XRef_Table)")
    // print("Cat Table \(category_Table)")
    return categoty_Name
}

Answer 1

So I ended up making a second Db connection and set it as a global. Seems to work ok.

This was the area that was causing the problem. I'm using the original Db connection at the top and the global for the 3 ModelData.getCommon_Category.

    func getitem_List() -> [item_List]
    {
        var theArray = [item_List]()
        
        do {
            try Database_GRDB.shared.databaseConnection!.read { db in
                for theData in try item_List.fetchAll(db, sql: "SELECT * FROM My_items ORDER BY item_Name")
                {
                    let theType = ModelData.getCommon_Category(item_Category_XRef_Table: "item_Type_XRef", category_Table: "item_Types", itemID: theData.itemID)
                    let theStyle = ModelData.getCommon_Category(item_Category_XRef_Table: "item_Style_XRef", category_Table: "item_Styles", itemID: theData.itemID)
                    let theSupplier = ModelData.getCommon_Category(item_Category_XRef_Table: "item_Supplier_XRef", category_Table: "Manufacturers_List", itemID: theData.itemID)
                    
                    theArray.append(item_List(itemID: theData.itemID, item_Name: theData.item_Name, itemType: theType, manufacturer: theSupplier, itemStyle: theStyle, item_ForSale: theData.item_ForSale, item_Sold: theData.item_Sold, Practice_item: theData.Practice_item))
                }
            }
        } catch {
            print("Getting item list failed: \(error)")
        }
        return theArray
    }


    static func getCommon_Category(item_Category_XRef_Table: String, category_Table: String, itemID: Int64) -> String
    {
        var categoty_Name: String = ""
        
        do {
            try dbQueue_GRDB.read { db in
                categoty_Name = try (String.fetchOne(db, sql: "SELECT Item_Name FROM " + category_Table + " INNER JOIN " + item_Category_XRef_Table + " ON ItemID = Type_ID WHERE Common_ID = ?", arguments: [itemID]) ?? "")
            }
            
        } catch {
            print("Couldn't get the category type name! \(category_Table) \(error)")
        }
//         print(categoty_Name)
//         print("Cat XRef Table \(item_Category_XRef_Table)")
//         print("Cat Table \(category_Table)")
        return categoty_Name
    }

I created a second Db connection as a global.

var dbQueue_GRDB = DatabaseQueue()

static func openTheDB()
{
    do {
        let fileUrl = try FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false).appendingPathComponent("SmartWare_MagicWare.sqlite")
        dbQueue_GRDB = try DatabaseQueue(path: fileUrl.path)
        
        // print("Database is open")
    } catch {
        print("Opening the Db failed! \(error)")
    }
}

This is my original Db connection.

class Database_GRDB
{
    static let shared = Database_GRDB()
    
    // GRDB
    public let databaseConnection: DatabaseQueue?
    
    private init()
    {
        do
        {
            let fileUrl = try FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false).appendingPathComponent("MyApp.sqlite")
            
            // GRDB
            let dbConnection = try DatabaseQueue(path: fileUrl.path)
            self.databaseConnection = dbConnection
            
        } catch {
            databaseConnection = nil
            print("Cannot connect to Database. \(error)")
        }
    }
}