Hacker News Reader

Comment by 6gvONxR4sf7o | Hacker News Reader

I used to advocate for this, then tried opening a sqlite file I'd been using on another computer and it said it was corrupted. I took it back to the original computer and it opened just fine. So there was something going on that made it more complex and less portable than something simple like json, which is super frustrating because otherwise it's a really useful format for datasets, and now I can't really trust it.

View on HN

If you use certain new features of SQLite, the database file gets "upgraded". At that time, you must use a version of SQLite that understands these new features.

So, the other computer you used was probably running an older version of SQLite. Just update it to make it work.

I think the computer it worked on was one with an older version of SQLite rather than a newer one, but I’m not certain. It was a while ago. I thought I’d checked for the upgrade issue, but my memory is shit.

That’s a good catch. I’d there any way to tell which version of SQLite created the file? Aside from trial and error, I mean?

The version is stored in byte 96 of the header of the sqlite file[1].

I haven't looked but there will be some sqlite command to query it and I'm sure some viewer tools will display it as well.

[1] https://www.sqlite.org/fileformat.html

You can print the version with this magical incantation:

    $ python -c 'import sys, struct; print(struct.unpack(">I", open(sys.argv[1], "rb").read()[96:100])[0])' foo.db

Recentish versions of file can show you the version:

    $ file example.db
    example.db: SQLite 3.x database, last written using SQLite version 3007017

Reply on news.ycombinator.com