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



  • Hi Gurus,
    I'm writing a small app that uses a SQLite database, I'd like to load the database into :memory: when the program runs, and also save the database in :memory: to disk when I need to. It's obviously quicker to read/write the database in memory rather than on the HDD.
    I can't seem to find functionality to do this, perhaps I am just looking too hard.
    Could someone point me in the right direction if this is possible please?

    Many thanks,
    Steve Q.



  • copy the database to a ramdisk, do your I/O, and then copy it back to persistent storage. Of course you'll lose your changes if anything weird happens during your I/O phase, or if you cannot write it back to disk.



  • @steveq
    You could go with @Kent-Dorfman's suggestion. But to answer what you asked, read https://www.sqlite.org/backup.html, Example 1: Loading and Saving In-Memory Databases.


  • Lifetime Qt Champion

    @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.



  • @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.



  • @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?



  • @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!


  • Qt Champions 2018

    @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



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



  • @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 :-)



  • 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.



  • 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
    


  • 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.



  • 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.



  • 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.



  • 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.



  • @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! :-)


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.