QSqlDatabase - SQLite: How to load into :memory: and save :memory: to disk

[mrjj]

@steveq said in QSqlDatabase - SQLite: How to load into :memory: and save :memory: to disk:

It's obviously quicker to read/write the database in memory rather than on the HDD.

Hi
Just as a note.
I would check/measure that assumption before copying the database around as if its small enough to fit in memory,
the file system/OS might already cache it and reading speed gains would be nonexisting and also the
write cache on the hard drive might also reduce any speed effect seen on actual write.
Im only saying to check it as at work i did move DB from disk to in mem for the logging system
and did not see much improvements. just a few microseconds for the logging.
However, the disk is a solid state type so if you are using a spin disk, it might give more.

[steveq]

@Kent-Dorfman thanks for your reply! I have a ramdisk, and you are correct, that would be great, but I can't guarantee that anyone else that may use my software will have a ramdisk installed.

[steveq]

@JonB thanks for your help. In previous software I have written, I have done what you have suggested quite successfully, however as I am using the QSqlDatabse class for the first time in QT, I was hoping to find that functionality within that class. I don't think I can use your suggestion using QSqlDatabase?

[steveq]

@mrjj Thanks for your advice. The database is only small, so you may very well be correct. It just bugs me to be constantly hitting the HDD with db operations. It makes more sense to do it all in memory and then dumping it to disk at strategic points within your software. Still from what I can tell, I may have no choice as it looks like this functionality may not be available to me!

[Christian Ehrlicher]

@steveq said in QSqlDatabase - SQLite: How to load into :memory: and save :memory: to disk:

it looks like this functionality may not be available to me!

Why not? Get access to the raw database instance and call the functions by yourself: https://doc.qt.io/qt-5/qsqldriver.html#handle

[steveq]

@Christian-Ehrlicher why did I not see this?? Thanks Christian, I'll give it a go today and report back! :-)

[steveq]

@Christian-Ehrlicher I tried your idea of getting access to the raw database but came across a few issues.

1. Making calls directly to SQLite rather than via the QSqlDatabase class would of course mean I either need a linked library, or have the code compiled into my software. I compiled the SQLite code into my software using the version I had and was able to make calls directly on the database. But which version of SQLite3 would I use? Surely there would be an issue if the version QT uses and the version I used was different.
2. I was getting a mutex error, I assume because QT had the database open and my new code was trying to do stuff too.

Something is not sitting right with me doing it like this. It seems like a hack, or have I not grasped your concept correctly?
Either way, I'm back to the drawing board and at this stage I'm just running from disk.
Cheers,
SQ :-)

[fcarney]

I wanted to know how to do this. I found this thread which showed how to get to the version Qt is shipped with. The path is wrong so I corrected it to this:

INCLUDEPATH += $$[QT_INSTALL_PREFIX]/../Src/qtbase/src/3rdparty/sqlite
SOURCES += $$[QT_INSTALL_PREFIX]/../Src/qtbase/src/3rdparty/sqlite/sqlite3.c

I am not yet sure how to use the handle, but at least you can use the same version Qt uses internally.

Edit:
I should mention that I am using Qt 5.12.2 so paths may be different if you are using a different version. There may also be a more appropriate value to use besides QT_INSTALL_PREFIX to get the right path.

[fcarney]

Hmmm, I am getting errors from the linker:

:-1: error: sqlite3.o: undefined reference to symbol 'dlclose@@GLIBC_2.2.5'
/lib/x86_64-linux-gnu/libdl.so.2:-1: error: error adding symbols: DSO missing from command line
:-1: error: collect2: error: ld returned 1 exit status

[fcarney]

It needs to lib "dl" added. So all changes to pro file look like this:

INCLUDEPATH += $$[QT_INSTALL_PREFIX]/../Src/qtbase/src/3rdparty/sqlite
SOURCES += $$[QT_INSTALL_PREFIX]/../Src/qtbase/src/3rdparty/sqlite/sqlite3.c
LIBS += -ldl

I was able to test this code so far:

#include <QGuiApplication>
#include <QQmlApplicationEngine>

#include <QDebug>
#include <QSqlDatabase>
#include <QSqlDriver>
#include <sqlite3.h>

int main(int argc, char *argv[])
{
    QCoreApplication::setAttribute(Qt::AA_EnableHighDpiScaling);

    QGuiApplication app(argc, argv);

    QQmlApplicationEngine engine;
    engine.load(QUrl(QStringLiteral("qrc:/main.qml")));
    if (engine.rootObjects().isEmpty())
        return -1;

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open())
    {
        qInfo() << "db not open";
    }

    QVariant v = db.driver()->handle();
    if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*") == 0) {
        // v.data() returns a pointer to the handle
        sqlite3 *handle = *static_cast<sqlite3 **>(v.data());
        if(handle){
            qInfo() << "Yay! Handle!";

            qInfo() << "SQLite Version:" << sqlite3_libversion();
        }
    }

    return app.exec();
}

Edit:
I think "dl" lib is a *nix lib so the pro file might need some check for unix around it to be cross compatible on Windows. I am testing this under Linux.

[fcarney]

Well, I seem to have been able to interact with the :memory: db created by the Qt libs. Here is a working example:

#include <QGuiApplication>
#include <QQmlApplicationEngine>

#include <QDebug>
#include <QSqlDatabase>
#include <QSqlDriver>
#include <sqlite3.h>

int sqlexec_callback(void* unknown,int rows,char** values,char** headers)
{
    qInfo() << "sqlexec_callback";

    if(rows == 0)
        return 0;

    for(int count=0; count<rows; count++)
    {
        qInfo() << count << headers[count] << ":" << values[count];
    }

    return 0;
}

int main(int argc, char *argv[])
{
    QCoreApplication::setAttribute(Qt::AA_EnableHighDpiScaling);

    QGuiApplication app(argc, argv);

    QQmlApplicationEngine engine;
    engine.load(QUrl(QStringLiteral("qrc:/main.qml")));
    if (engine.rootObjects().isEmpty())
        return -1;

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    if (!db.open())
    {
        qInfo() << "db not open";
        return -1;
    }

    QVariant v = db.driver()->handle();
    if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*") == 0) {
        // v.data() returns a pointer to the handle
        sqlite3 *handle = *static_cast<sqlite3 **>(v.data());
        if(handle){
            qInfo() << "Yay! Handle!";

            qInfo() << "SQLite Version:" << sqlite3_libversion();

            // init things
            sqlite3_initialize();

            // do something
            sqlite3_exec(handle, "CREATE TABLE if not exists ABC(foo,bar)", 0, 0, 0);
            sqlite3_exec(handle, "INSERT INTO ABC(foo,bar) VALUES(1,1)", 0, 0, 0);
            sqlite3_exec(handle, "INSERT INTO ABC(foo,bar) VALUES(2,2)", 0, 0, 0);
            sqlite3_exec(handle, "INSERT INTO ABC(foo,bar) VALUES(3,3)", 0, 0, 0);
            sqlite3_exec(handle, "SELECT foo,bar FROM ABC", sqlexec_callback, 0, 0);
        }
    }

    return app.exec();
}

It does not do anything with copying the db to disk, but the link I provided does cover that.

[fcarney]

Well I learned a lot:

#include <QGuiApplication>
#include <QQmlApplicationEngine>

#include <QDebug>
#include <QSqlDatabase>
#include <QSqlDriver>
#include <sqlite3.h>

#include <QStandardPaths>
#include <QDir>

// first arg (customobj) comes from fourth arg of call to sqlite3_exec
int sqlexec_callback(void* customobj,int rows,char** values,char** headers)
{
    qInfo() << "sqlexec_callback";

    if(rows == 0)
        return 0;

    for(int count=0; count<rows; count++)
    {
        qInfo() << count << headers[count] << ":" << values[count];
    }

    return 0;
}

sqlite3* getSqliteHandle(QSqlDatabase *db)
{
    sqlite3 *handle = nullptr;

    QVariant v = db->driver()->handle();
    if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*") == 0) {
        // v.data() returns a pointer to the handle
        handle = *static_cast<sqlite3 **>(v.data());
    }

    return handle;
}

int main(int argc, char *argv[])
{
    QCoreApplication::setAttribute(Qt::AA_EnableHighDpiScaling);

    QGuiApplication app(argc, argv);

    QQmlApplicationEngine engine;
    engine.load(QUrl(QStringLiteral("qrc:/main.qml")));
    if (engine.rootObjects().isEmpty())
        return -1;

    QSqlDatabase memdb = QSqlDatabase::addDatabase("QSQLITE","memdb");
    memdb.setDatabaseName(":memory:");
    if (!memdb.open())
    {
        qInfo() << "memdb not open";
        return -1;
    }

    qInfo() << "SQLite Version:" << sqlite3_libversion();

    // init things
    sqlite3_initialize();

    sqlite3* memhandle = getSqliteHandle(&memdb);

    if(!memhandle)
    {
        qInfo() << "no memhandle";
        return -1;
    }

    qInfo() << "Yay! Handle!";

    // do something
    sqlite3_exec(memhandle, "CREATE TABLE if not exists ABC(foo,bar)", nullptr, nullptr, nullptr);
    sqlite3_exec(memhandle, "INSERT INTO ABC(foo,bar) VALUES(1,1)", nullptr, nullptr, nullptr);
    sqlite3_exec(memhandle, "INSERT INTO ABC(foo,bar) VALUES(2,2)", nullptr, nullptr, nullptr);
    sqlite3_exec(memhandle, "INSERT INTO ABC(foo,bar) VALUES(3,3)", nullptr, nullptr, nullptr);
    sqlite3_exec(memhandle, "SELECT foo,bar FROM ABC", sqlexec_callback, nullptr, nullptr);

    QSqlDatabase diskdb = QSqlDatabase::addDatabase("QSQLITE","diskdb");
    const QString docsFolder = QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation);
    diskdb.setDatabaseName(docsFolder + QDir::separator() + "testsqlite.db");
    if (!diskdb.open())
    {
        qInfo() << "diskdb not open";
        return -1;
    }

    sqlite3* diskhandle = getSqliteHandle(&diskdb);

    if(!diskhandle)
    {
        qInfo() << "no diskhandle";
        return -1;
    }

    // copy from memdb to diskdb
    // https://www.sqlite.org/backup.html
    sqlite3_backup *pBackup;
    pBackup = sqlite3_backup_init(diskhandle, "main", memhandle, "main");
    if( pBackup ){
        sqlite3_backup_step(pBackup, -1);
        sqlite3_backup_finish(pBackup);
    }

    return app.exec();
}

This code copies from a db created in mem to one created on disk.

[fcarney]

I am having too much fun with this:

#include <QGuiApplication>
#include <QQmlApplicationEngine>

#include <QDebug>
#include <QSqlDatabase>
#include <QSqlDriver>
#include <QSqlQuery>
#include <sqlite3.h>

#include <QStandardPaths>
#include <QDir>

// first arg (customobj) comes from fourth arg of call to sqlite3_exec
int sqlexec_callback(void* customobj,int rows,char** values,char** headers)
{
    qInfo() << "sqlexec_callback";

    if(rows == 0)
        return 0;

    for(int count=0; count<rows; count++)
    {
        qInfo() << count << headers[count] << ":" << values[count];
    }

    return 0;
}

sqlite3* getSqliteHandle(QSqlDatabase *db)
{
    sqlite3 *handle = nullptr;

    QVariant v = db->driver()->handle();
    if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*") == 0) {
        // v.data() returns a pointer to the handle
        handle = *static_cast<sqlite3 **>(v.data());
    }

    return handle;
}

int main(int argc, char *argv[])
{
    QCoreApplication::setAttribute(Qt::AA_EnableHighDpiScaling);

    QGuiApplication app(argc, argv);

    QQmlApplicationEngine engine;
    engine.load(QUrl(QStringLiteral("qrc:/main.qml")));
    if (engine.rootObjects().isEmpty())
        return -1;

    QSqlDatabase memdb = QSqlDatabase::addDatabase("QSQLITE","memdb");
    memdb.setDatabaseName(":memory:");
    if (!memdb.open())
    {
        qInfo() << "memdb not open";
        return -1;
    }

    // do qt style queries
    QSqlQuery query(memdb);
    query.exec("CREATE TABLE if not exists XYZ(fiz,baz)");
    query.exec("INSERT INTO XYZ(fiz,baz) VALUES('a',1)");
    query.exec("INSERT INTO XYZ(fiz,baz) VALUES('b',2)");
    query.exec("INSERT INTO XYZ(fiz,baz) VALUES('c',3)");

    qInfo() << "SQLite Version:" << sqlite3_libversion();

    // init things
    sqlite3_initialize();

    sqlite3* memhandle = getSqliteHandle(&memdb);

    if(!memhandle)
    {
        qInfo() << "no memhandle";
        return -1;
    }

    qInfo() << "Yay! Handle!";

    // do something
    //sqlite3_exec(memhandle, "CREATE TABLE if not exists ABC(foo,bar)", nullptr, nullptr, nullptr);
    //sqlite3_exec(memhandle, "INSERT INTO ABC(foo,bar) VALUES(1,1)", nullptr, nullptr, nullptr);
    //sqlite3_exec(memhandle, "INSERT INTO ABC(foo,bar) VALUES(2,2)", nullptr, nullptr, nullptr);
    //sqlite3_exec(memhandle, "INSERT INTO ABC(foo,bar) VALUES(3,3)", nullptr, nullptr, nullptr);
    //sqlite3_exec(memhandle, "SELECT foo,bar FROM ABC", sqlexec_callback, nullptr, nullptr);

    QSqlDatabase diskdb = QSqlDatabase::addDatabase("QSQLITE","diskdb");
    const QString docsFolder = QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation);
    diskdb.setDatabaseName(docsFolder + QDir::separator() + "testsqlite.db");
    if (!diskdb.open())
    {
        qInfo() << "diskdb not open";
        return -1;
    }

    sqlite3* diskhandle = getSqliteHandle(&diskdb);

    if(!diskhandle)
    {
        qInfo() << "no diskhandle";
        return -1;
    }

    // copy from memdb to diskdb
    // https://www.sqlite.org/backup.html
    sqlite3_backup *pBackup;
    pBackup = sqlite3_backup_init(diskhandle, "main", memhandle, "main");
    if( pBackup ){
        sqlite3_backup_step(pBackup, -1);
        sqlite3_backup_finish(pBackup);
    }

    // qt style query
    QSqlQuery dquery(diskdb);
    dquery.exec("SELECT fiz,baz FROM XYZ");
    while (dquery.next()) {
        QString name = dquery.value(0).toString();
        int value = dquery.value(1).toInt();
        qDebug() << name << value;
    }

    return app.exec();
}

This example performs all queries using Qt api. Which are a lot easier to use.

[steveq]

@fcarney Haha, you are having way too much fun, but thanks heaps for all the information! I'll have a closer look at all of this soon and report back!
Cheers for your help mate, I really appreciate it! :-)

[HoMa]

I know this is a very old question, but I was struggeling with a similar question and found - with the current version of sqlite - a nice solution, which I wanted to share.
My use case: save a database copy, with all personal data replaced by neutral strings.
Solution:

• Create a new DB in memory
• Use SQL "VACUUM INTO .." to replicate the database into the inmemory database
• Use the same method, to save the database to the file system

This code is from a minimal Qt Program with two buttons - one to create the source database (if it does not exist) the second button will create a modified copy on disc with the method described above. You can find the complete source code at github

void MainWindow::on_pbGo_clicked()
{
    QString imCon =ui->leConnectionName->text (); // "imCon"
    QString imDbName =ui->leDbName->text();       // "file::memory:

    {
        // open in memory database
        QSqlDatabase imDb =QSqlDatabase::addDatabase ("QSQLITE", imCon);
        imDb.setConnectOptions ("QSQLITE_OPEN_URI;QSQLITE_ENABLE_SHARED_CACHE");
        imDb.setDatabaseName (imDbName);
        if( not imDb.open())
            qCritical() << "failed to open inmemory DB " << imDb.lastError ();

        // open source db
        QSqlDatabase srcDb =QSqlDatabase::addDatabase ("QSQLITE", "source");
        srcDb.setConnectOptions ("QSQLITE_OPEN_URI;QSQLITE_ENABLE_SHARED_CACHE");
        srcDb.setDatabaseName ("source.db");
        if( not srcDb.open ())
            qCritical() << "failed to open source db " << srcDb.lastError ();

        // copy data to im db
        QSqlQuery qSourceToMemory (srcDb);
        if( not qSourceToMemory.exec(QString("VACUUM INTO '%1'").arg(imDbName)))
            qCritical() << "failed to copy data to inmemory db " << imDbName << "\n" << qSourceToMemory.lastError ();

        // change data
        QSqlQuery qModifyImDb(imDb);
        if( not qModifyImDb.exec ("INSERT INTO t (f) VALUES ('BrandNewData')"))
            qCritical() << "failed to modify in memory db " << qModifyImDb.lastError ();

        // save imdb to disc
        QSqlQuery qMemoryToDisc(imDb);
        QFile::remove("dest.db");
        if( not qMemoryToDisc.exec(QString("VACUUM INTO '%1'").arg("dest.db")))
            qCritical() << "failed to vacuum into dest db " << qMemoryToDisc.lastError ();

        // cleanup
        srcDb.close ();
        srcDb.close();
    }
    QSqlDatabase::removeDatabase (imCon);
    QSqlDatabase::removeDatabase ("source");
}


void MainWindow::on_pbexit_2_clicked()
{
    {
        QSqlDatabase db =QSqlDatabase::addDatabase ("QSQLITE", "source");
        db.setDatabaseName ("source.db");
        if( not db.open ())
            qCritical() << "failed to open source db for creation";

        QSqlQuery q(db);
        if( not q.exec ("CREATE TABLE IF NOT EXISTS t (f TEXT PRIMARY KEY)"))
            qCritical() << "failed to create table" << q.lastError ();
        if( not q.exec ("REPLACE INTO t (f) VALUES ('Hallo'), ('World'), ('Welcome'), ('Aliens')"))
            qCritical() << "failed to write data"<< q.lastError ();
        db.close ();
    }   // let QSqlQuery and QSqlDatabase run out of scope to close

    QSqlDatabase::removeDatabase ("source");
}