Database not on the correct thread ...

[mtissington]

Sorry this is a little complicated ... I need to do a select from a couple of tables in one database and insert them into another. I'd like to do this in a transaction. At the moment my code is not doing that.
I have nested dbQueue (different) and this is causing a problem ...
Any sugestions please?

dbQueueMerge = source databaase
dbQueue = destination database

let dbQueueMerge = try! DatabaseQueue(path: url.path!, configuration: config)
        dbQueue.inDatabase { db in
            try! dbQueueMerge.inDatabase {dbMerge in
                for groupRow in Row.fetch(dbMerge, "SELECT * FROM groups") {
                    var newid: Int64 = 0
                    let groupid: Int64 = groupRow.value(named: "groupid")
                    let name: String = groupRow.value(named: "name")
                    let description: String = groupRow.value(named: "description")
                    try db.execute("INSERT INTO groups (name, description) VALUES (?, ?)",                       arguments: [name, description])
                    newid = db.lastInsertedRowID
                    for wordRow in Row.fetch(dbMerge, "SELECT * FROM words WHERE groupid = \(groupid)") {
                        let word: String = wordRow.value(named: "word")
                        let action: String = wordRow.value(named: "action")
                        try db.execute("INSERT INTO words (groupid, word, action) VALUES (?, ?, ?)",
                            arguments: [newid, word, action])
                    }
                }
            }
        }

[groue]

Would you please reformat your code as below:

```swift
// yeah, Swift
do {
    // correctly indented
}
```

It will help a lot reading your question :-)

// yeah, Swift
do {
    // correctly indented
}

[mtissington]

yes, having a great difficult with code formatting ... seems to want to strip out tabs and cr/lf

[mtissington]

ok, that should do ..

[groue]

Thanks ;-)

OK now it's crystal clear. The inDatabase method executes its closure argument in a protected dispatch queue. And a database complains if it is used outside of this queue:

dbQueue.inDatabase { db in
    // In the queue of `db`
    try! dbQueueMerge.inDatabase {dbMerge in
        // In the queue of `dbMerge`
        // And there `db` complains it is not in its queue:
        try db.execute(...)
    }
}

OK. That's a pattern that is not supported today. I have to think about it.

Meanwhile, you have two possible workarounds:

1- First fetch all data in the source database queue, and then write in the destination database queue. It's much less memory efficient than your tight and sharp loop, I agree. But if you don't have too much data to copy, this solution can be an acceptable workaround.

2- If you actually copy the whole content of a database in another, use the backup method.

[mtissington]

Hmm, I was afraid you would tell me something like that. I would have thought that merging a second database is a (fairly) common need. In the merge process PK values change so I'll have to go with option one.

Hope you can figure something out ... and thanks for the speedy replies!

[groue]

I would have thought that merging a second database is a (fairly) common need.

There are my needs, the ones of my coworkers, and the ones of users like you who are kind enough to ask for a feature. Now the merge is on the list of feature requests :-)

Hope you can figure something out

So do I. Until this time, use a workaround, and stay tuned on GRDB :-)

[groue]

@mtissington OK I eventually managed to find a solution:

dbQueue1.inDatabase { db1 in 
    dbQueue2.inDatabase { db2 in 
        // Both db1 and db2 can be used here.
    }
}

[mtissington]

cool, will give it a try ....

[groue]

The fix has shipped in v0.66.0.

[mtissington]

confirmed that this now works - thanks

            try dbQueue.inTransaction { db in
                try dbQueueMerge.inDatabase {dbMerge in