QSLite in memory shared cache, create file on disk

[achard.ju]

Hi everyone !

I'm trying to find answers on a strange behavior that I don't understand.

I am using QSLite driver, and I want to create an in-memory database, using the shared-cache system. I know that it is discouraged according to the documentation of SQLite, but I am not actually allowed to change this.

I have two target platform, that don't behave in the same way :

• Debian 8, Qt 4.8.6, SQLite 3.7.8.1
• Debian 11, Qt 5.15.2, SQLite 3.34.1

Everything taken from the official Debian repositories.

I am creating my in memory database this way :

QSQLDatabase db;
db.setDatabaseName("file::memory:");
db.setConnectOptions("QSQLITE_OPEN_URI;QSQLITE_ENABLE_SHARED_CACHE");
db.open();

On the Debian 8 platform, a file is created on disk in the working directory of my application named 'file::memory:'.
This does not happen on the Debian 11 platform.

I searched for differences in the QSQLite driver implementation, between my two Qt versions, but with no luck.
Is this a known bug that I did not found online ? Bug in the Qt implementation, bug in SQLite3 maybe ?
Is there something I am missing ?

Thanks everyone !

[JonB]

@achard-ju
If you do not get a specific answer. I would think that Qt drivers pass your strings through as-are with little interpretation? Then it comes down to e.g. https://www.sqlite.org/c3ref/open.html. There you see a lot about what might be accepted/not accepted, including statements about depending how SQLite was compiled. Could that differ across the different SQLite versions and/or host OSes?

[achard.ju]

@JonB Hey, thanks for your answer ! I think you helped me a lot ... I was not searching in the source of the good Qt version ...

If the strings you refer to are the string passed to setConnectOptions, yes they are parsed by Qt and converted to accepted parameters by SQLite.

This is the SQL driver plugin code Under Debian 8 (Qt 4.8.6) :

...
        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) {
...

And this is the SQL driver plugin code Under Debian 11 (Qt 5.15.2) :

...
        } else if (option == QLatin1String("QSQLITE_OPEN_URI")) {
            openUriOption = true;
        } else if (option == QLatin1String("QSQLITE_ENABLE_SHARED_CACHE")) {
            sharedCache = true;
        }
...
    int openMode = (openReadOnlyOption ? SQLITE_OPEN_READONLY : (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE));
    openMode |= (sharedCache ? SQLITE_OPEN_SHAREDCACHE : SQLITE_OPEN_PRIVATECACHE);
    if (openUriOption)
        openMode |= SQLITE_OPEN_URI;
...
    const int res = sqlite3_open_v2(db.toUtf8().constData(), &d->access, openMode, NULL);

So yes, there is a major difference, as in Qt 4.8.6 the SQLILE_OPEN_URI does not seems to be handled !
And I thinks this is exactly what can explain the unwanted file creation, as the passed name is an URI.

Thanks again !

[achard.ju]

In order to leave a trace of the resolution:

In Qt 4.8.6 QSQLite driver, the URI parameter is not handled, so the "file::memory:" special filename is not processed as an URI but as a classic file name. So a file is created using this name.

My workaround has been to use a direct call to the sqlite API with sqlite3_config, to globally enable the URI processing each time a filename is passed to sqlite3_open or sqlite3_open_v2 is called.
According to the documentation, and I agree with it, it is safe to globally enable the URI processing, as user have to pass a filename beginning with the specific "file:" prefix to be specifically processed as an URI. When called with a real filename, it should not begin with this pattern.

Si this is what I did:

      //Only if the library has not been initialized yet
      if (sqlite3_test_control(SQLITE_TESTCTRL_ISINIT) != SQLITE_OK)
      {
          int result = sqlite3_config(SQLITE_CONFIG_URI, 1);
          if (result != 0)
          {
              qWarning() << "Failed to call sqlite3_config, resultCode : " << result;
          }
      }
      sqlite3_initialize();

The sqlite3_config call should be done before any sqlite3_initialize call (even if the initialize should not be called according to the sqlite documentation, it is internally called automatically).
Finally has my code can be called several time, I check if the library has been initialized before calling sqlite3_config.

As I have to maintain several target platform, I surrounded this with preprocessor instruction to build this only when using Qt 4.8.6.

Thanks for your time !

[SGaist]

Hi,

I was wondering if you are building that old version of Qt yourself ? If so, you could backport the code to your Qt version and have it directly integrated.

Just in case, the latest version of the Qt 4 series is 4.8.7, it might be worth checking.

[ChrisW67]

@achard-ju said in QSLite in memory shared cache, create file on disk:

a file is created on disk in the working directory of my application

First. Be very sure that you know where the process' current working directory is. It may not be where you think it is.

Second. Try opening the in-memory database as described here. This appears to be introduced in Sqlite 3.7.13.

A lot has changed between Qt 4 and Qt 5. A lot has changed in Sqlite over the decade between 2011-09-19 and 2021-01-20, which are the release dates of the two versions concerned. At least versions 3.14, 3.8.8, 3.8.7.3, 3.7.17, 3.7.15, and 3.7.13 have touched shared cache mode (and in some cases fix corruption bugs).