How to properly share in-memory SQLite database among threads?

akoch

Hi!

I'm developing an application that shall visualize log files of a specific binary format. In order to do so it dumps parsed entries into an in-memory SQLite database and then uses a QTableView to display a QSqlTableModel of the database.

As parsing of the entries can be quite complex (and the logs can get very large) I have distributed that task to multiple threads.

This worked fine but starting with Qt 5.11 I got this warning:

QSqlDatabasePrivate::database: requested database does not belong to the calling thread.

I eventually found out that sharing a QSqlDatabase connection object between multiple threads is not supported anymore and that I would have to create a separate connection for each thread (e.g. like here).

My naive approach to this ended up in having distinct in-memory databases for each thread, but after reading this topic I got that fixed (using QSQLITE_OPEN_URI, QSQLITE_ENABLE_SHARED_CACHE and file::memory: as database name).

Unfortunately I am now back where I started, getting the aforementioned warning. So I am wondering: how do you properly share an in-memory database? Is this even supported?

For reference, here's how I create the database within the constructor of my QSqlTableModel subclass (simplified):

QSqlDatabase db = database();
db.setConnectOptions("QSQLITE_OPEN_URI;QSQLITE_ENABLE_SHARED_CACHE;QSQLITE_ENABLE_REGEXP");
db.setDatabaseName("file::memory:");
if (!db.open())
{
    qFatal("Failed to create database");
}

QSqlQuery query("CREATE TABLE entries (id INTEGER PRIMARY KEY, message TEXT);", db);
if (!query.isActive())
{
    qCritical() << "Failed to create entry table:"
                << query.lastError().text();
    exit(EXIT_FAILURE);
}

And this is how each thread creates its own connection:

const QString connName = QString::number((quintptr)QThread::currentThreadId());
QSqlDatabase db = QSqlDatabase::database(connName);
if (!db.isOpen() || !db.isValid()) {
   db = QSqlDatabase::cloneDatabase(database(), connName);
   if (!db.open()) {
      qCritical() << "Failed to open db connection" + connName;
   }
}

Thanks in advance for any help!

SGaist

Hi and welcome to devnet,

Interesting use case.

What if you do not clone the database connection but create a new one ?

Christian Ehrlicher

@akoch said in How to properly share in-memory SQLite database among threads?:

getting the aforementioned warning

As always when you want to find out where a qt warning comes from - install a custom qt message handler, put a breakpoint there and see where it comes from

akoch

@SGaist said in How to properly share in-memory SQLite database among threads?:

What if you do not clone the database connection but create a new one ?

I've tried that actually before I found out about ::cloneDatabase(), same result :(

akoch

I got it fixed, thanks to @Christian-Ehrlicher!

I almost did not try your suggestion because I thought I knew very well where the warning comes from.

But it turned out that I had missed a setQuery() call that was lacking its second parameter (the database connection to use).