undefined | Better HN

0 pointsOskarS2y ago0 comments

Is there no capability in SQLite to dump these things as well? Seems like a pretty big thing missing if that's the case, a thing I would expect SQLite to support pretty well.

0 comments

3 comments · 1 top-level

JNRowe2y ago· 2 in thread

Not as far as I'm aware. You can query much of that stuff using the CLI with the .dbinfo command, but support for that appears to be dependent on build options so may not be available. Another option might be to provide multiple commands("sqlite my.db 'PRAGMA user_version' .dump"), but you'd have to remember to update scripts if you suddenly started using other things in your app too.

I'll note that the metadata is handled correctly by the CLI's .backup command or you can "VACUUM INTO <somewhere>"¹, so if you're just attempting to use .dump for the sake of backup there are probably better options available anyway.

But yeah, I was surprised when I chased down a bug that was caused by a missing application_id following a restore from .dump output. I did a search at the time and noticed it had been reported to the sqlite folks on their forums a few times, so presumably I'm missing the reasoning behind not including it.

¹ https://www.sqlite.org/lang_vacuum.html

o11c2y ago

Not sure if this actually does more, but remember you can do:

    sqlite> select * from pragma_application_id();
    0
    sqlite> select * from pragma_user_version();
    0

JNRowe2y ago

Yeah, you could use that as in "sqlite3 t.db 'SELECT format(…) FROM pragma_…' .dump" to create a file that sets application_id/$others and then dumps the tables, which would make restoration from the dump file work in more cases.

It is just another thing to be aware of if you're trying to store your database in your git repository with a filter, and want it to roundtrip correctly.

j / k navigate · click thread line to collapse