SQLite Forum

sqlite3_db_filename after sqlite3_deserialize returns "x"

sqlite3_db_filename after sqlite3_deserialize returns "x"

(1) By Gonzalo Diethelm (gonzus) on 2021-04-14 14:27:04 [link] [source]

I have two different SQLite databases. I open both of them and can query for some data on both databases to verify they are indeed different. I call sqlite3_db_filename on each of them and I get back the fully qualified file name in both cases; the returned values are two different pointers with two different strings, as expected. All good.

I then call sqlite3_serialize on the first database and use the resulting buffer to call sqlite3_deserialize on the second database. I can again query for some data on both databases to verify they are now equal. Still all good.

If I now call sqlite3_db_filename on the databases, I still get the same pointer value (and string) for the first database; however, for the second database (where I deserialized the first one), I get a new pointer that points to a string with the value "x".

If I understood correctly, after calling sqlite3_deserialize on the second database it should have been converted into an in-memory database, so I was expecting sqlite3_db_filename to return an empty string (or even a null pointer) in this case. Maybe I misunderstood the docs?


(2) By Gonzalo Diethelm (gonzus) on 2021-04-15 07:58:04 in reply to 1 [source]

I added an additional test where the second database is ":memory:", so it starts life as an empty in-memory database.

If I call sqlite3_db_filename on this handle I get back a non-null empty string, as expected.

When I call sqlite3_deserialize on this second database, the database becomes a clone of the first one, as expected.

If I now call sqlite3_db_filename on this handle I get back a non-null string with the value "x", just as in the first case I reported. This is not expected (by me).

(3) By Larry Brasfield (larrybr) on 2021-04-15 09:21:02 in reply to 1 [link] [source]

I think your expectation, that the sqlite3_db_filename() return for an in-memory database reflect its not-file-backed nature, is reasonable and supported by the 1st paragraph of the sqlite3_db_filename() doc. Said doc also seems to promise that the return will be an absolute pathname if not null or the empty string (and the stock VFS is used.)

I can see that the code clearly sets the filename kept internally to "x", which is not an absolute pathname on most platforms.

For both of these reasons, I consider your post(s) to be a bug report and will create a ticket for it.

Until this behavior is altered in the code, I suggest that you treat "x" as if it had been what you expected. That name is highly unlikely to collide with anything that could be correctly returned by sqlite3_db_filename(). Alternatively, assuming you would be ultimately persisting the DB known as "x" (rather than just fooling around with it), you could use the VACUUM INTO filename statement to achieve that, and even then abandon "x" and open the newly saved DB which would then have the filename you gave it.

(4) By Gonzalo Diethelm (gonzus) on 2021-04-15 09:35:41 in reply to 3 [link] [source]

Oh, great, much appreciated. I was going to add a link to my code but you have already confirmed that this is not as expected.

Could I also request that SQLITE_ENABLE_DESERIALIZE is changed into SQLITE_OMIT_DESERIALIZE, so that this functionality is compiled in by default? It is very useful (to me at least) and seems to be pretty isolated in the code. This is in line with that is written here:


This option enables the sqlite3_serialize() and sqlite3_deserialize() interfaces.

Future releases of SQLite might enable those interfaces by default and instead offer an SQLITE_OMIT_DESERIALIZE option to leave them out.

(5) By Richard Hipp (drh) on 2021-04-15 12:09:06 in reply to 3 [link] [source]

For technical reasons, that will be a difficult change to make, and will probably have performance impact on users who do not use serialized databases.

Are you really sure you need this?

(6) By Gonzalo Diethelm (gonzus) on 2021-04-16 17:03:08 in reply to 5 [link] [source]

I think I do: my use case is downloading a binary copy of an existing SQLite DB from a data storage such as S3, where the copy naturally comes down as a blob of bytes.

I could create a file, write the data there and open that as my DB, but I would rather deserialize the data directly into an in-memory DB. This not only avoids creating the file, it allows this use case to work even when there are no permissions / resources to create such a file.

If I understood you correctly, simply enabling this flag causes potential performance impacts? Can you provide more details?

(7) By Larry Brasfield (larrybr) on 2021-04-16 17:19:16 in reply to 6 [link] [source]

I think what Richard is saying is that a fix making the returned filename meet your (per documentation) expectation will have a performance impact for users not even using the serialization API. (I suspect you have misread the thread structure to read otherwise.)

As I understand your report, you get a functioning in-memory database after the deserialize call. The problem you report is only that sqlite3_db_filename() does not return something indicating it is an in-memory database. Richard's question, and now mine, is: What impact does this odd returned filename have on what you can achieve, relative to what you could achieve if it had conformed to the doc?

It would be useful to know the answer when considering changes that would address your use case.

(8) By Gonzalo Diethelm (gonzus) on 2021-04-16 19:33:21 in reply to 7 [link] [source]

Ah OK, I misunderstood. No, I don't really depend on sqlite3_db_filename returning an empty string after calling sqlite3_deserialize. It is just odd that it returns "X", and it certainly doesn't match the docs or the "reasonable" expectations.