Use SQLite3 Temporary database with QT4 QSQLDatabase

Question

http://www.sqlite.org/inmemorydb.html

Mentions the ability to create a temporary database that will be stored in memory until a file is necessary (if at all). It will also remove that file when finished automatically. This is achieved by providing a blank database name "".

rc = sqlite3_open("", &db);

I'm trying to do this within a QT4 based application using the QSQLDatabase

QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("");
bool ok = db.open();

Results in a failure to open the database.

I'm aware of the :memory: option and using it is much faster in our application for small datasets. I would prefer something that will drop back to a file when necessary since we may have some large datasets.

I am making the assumption that allowing the database engine to cache to file when necessary will be more efficient that just letting the OS page the memory in and out.

I'm open to alternatives with the following requirements:

Fast insert and lookup
No file once application is closed

Update After going through some of the suggested SQLite performance suggestions I now have acceptable performance when using a file (TRANSACTIONS!).

I have not been able to figure out how to use sqlite3's built-in temporary file functionality.

I'm trying to use a QTemporaryFile, but for some reason they won't auto-delete the way the documentation implies they should. I have some more experimenting to do.

Have you considered the backup option of SQLite on a `:memory:` database? https://www.sqlite.org/backup.html — nonsensickle, Jan 13 '15 at 21:29
Alternatively http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite tells you about other ways of speeding up a database that is tied to the disk. There are pros/cons for each of the options you turn on/off... Special emphasis on the **synchronous = OFF**... — nonsensickle, Jan 13 '15 at 21:30

score 1 · Accepted Answer · edited May 23 '17 at 11:59

TL;DR - NO, you cannot give an empty string as a database name to sqlite3 using Qt. (see Edit 3).

Original answer

~~One possibility is to use the backup option in SQLite for an in-memory database.~~

But since it sounds like an optimization issue, you should read this SO answer which goes into a fair amount of detail about how you can speed up your database and disconnect it from the disk temporarily using PRAGMA synchronous = OFF and/or PRAGMA journal_mode = MEMORY. You might also want to limit the size of your in-memory pages by using the PRAGMA cache_size.

Edit 1: After reading your question again I realize now that you are asking to store overflow data to disk and want SQLite to just manage that so my first paragraph is not useful.

Edit 2: Added the PRAGMA cache_size suggestion.

Edit 3: Answering the meat of the question:

Your link to the other SO answer about SQLite optimization was very helpful, but still does not answer the meat of the question. ie how to use the sqlite3 built-in temp file functionality through the QT interface. Gavin S.

Ok, but the answer to that is no, and here's why.

If you take a look at the SQLite driver in Qt source and in particular the QSQliteDriver::open function it looks like this:

/*
   SQLite dbs have no user name, passwords, hosts or ports.
   just file names.
*/
bool QSQLiteDriver::open(const QString & db, const QString &, const QString &, const QString &, int, const QString &conOpts)
{
    if (isOpen())
        close();

->  if (db.isEmpty())
->      return false;
    bool sharedCache = false;
    int openMode = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, timeOut=5000;
    QStringList opts=QString(conOpts).remove(QLatin1Char(' ')).split(QLatin1Char(';'));
    foreach(const QString &option, opts) {
        if (option.startsWith(QLatin1String("QSQLITE_BUSY_TIMEOUT="))) {
            bool ok;
            int nt = option.mid(21).toInt(&ok);
            if (ok)
                timeOut = nt;
        }
        if (option == QLatin1String("QSQLITE_OPEN_READONLY"))
            openMode = SQLITE_OPEN_READONLY;
        if (option == QLatin1String("QSQLITE_ENABLE_SHARED_CACHE"))
            sharedCache = true;
    }

    sqlite3_enable_shared_cache(sharedCache);

->  if (sqlite3_open_v2(db.toUtf8().constData(), &d->access, openMode, NULL) == SQLITE_OK) {
        sqlite3_busy_timeout(d->access, timeOut);
        setOpen(true);
        setOpenError(false);
        return true;
    } else {
        setLastError(qMakeError(d->access, tr("Error opening database"),
                     QSqlError::ConnectionError));
        setOpenError(true);
        return false;
    }
}

The function sqlite3_open you are trying to call will never be called with an empty string as an argument because of the early return condition on lines 530 and 531 unless you patch the Qt driver on that particular line of code.

Your link to the other SO answer about SQLite optimization was very helpful, but still does not answer the meat of the question. ie how to use the sqlite3 built-in temp file functionality through the QT interface. — Gavin S, Jan 15 '15 at 04:38
Ok, I have edited the answer and in short it's a **no**. Basically the Qt driver does not allow you to use this feature. For more info, refer to my answer. — nonsensickle, Jan 21 '15 at 22:30

Use SQLite3 Temporary database with QT4 QSQLDatabase

1 Answers1