1

I created a new sqlite3 database using the latest sqlite3.exe v3.25 tools downloaded from https://www.sqlite.org/download.html

I want to verify that the sqlite database created is v3.25 to take advantage of the latest features.

How do I use sqlite3.exe tool to verify that the version is v3.25? I welcome other means to do so.

I tried the most voted answer here; How to find SQLITE database file version

However, the answer doesn't help. It returns the library version, not the database file version.

I am using windows 10.

guagay_wk
  • 26,337
  • 54
  • 186
  • 295
  • The sqlite3 program prints out a banner message on startup that includes the version. – Shawn Oct 17 '18 at 00:07
  • 1
    The database files themselves have a version but the format hasn't changed in a few years. The current version is used by default unless you change it with a pragma before creating any tables in a new db. – Shawn Oct 17 '18 at 00:09
  • Does the banner message refer to the version of the tool or the database file? – guagay_wk Oct 17 '18 at 00:09
  • 1
    The tool. You can find the file version with the `legacy_file_format` pragma, which is probably going to return 4 unless you're dealing with a DB that was created with a sqlite version before 3.7.10, in which case it'll probably be 1 (or maybe 2 or 3). [Details](https://www.sqlite.org/compile.html#default_file_format) – Shawn Oct 17 '18 at 00:15
  • 1
    It just dawned on me that you might think the version of sqlite used to create a DB affects what versions can later use the file. As long as the file version (1-4) can be read by that sqlite version, it doesn't (with the caveat that 3.8.2 or later is needed for a DB with a WITHOUT ROWID table; that feature was added without bumping the file format for whatever reason). – Shawn Oct 17 '18 at 00:45
  • @Shawn, if I am using python to run sqlite query, does the python library need to be updated to the latest to support windows functions in the query? – guagay_wk Oct 17 '18 at 00:52
  • 1
    Yes it would have to be using 3.25 or newer. I'd be surprised if the version bundled with python has been updated yet for a release. – Shawn Oct 17 '18 at 00:56
  • @Shawn, too bad that the wonderful windows functions in 3.25 cannot be supported by python for the time being. Windows functions make code much more readable. – guagay_wk Oct 17 '18 at 00:57

1 Answers1

4

As per the official specification of the database format, there is a header at the beginning of the database file spanning the first 100 bytes. There are a few items in the header that contain version numbers:

Offset  Size    Description 
18      1       File format write version. 1 for legacy; 2 for WAL. 
19      1       File format read version. 1 for legacy; 2 for WAL. 
60      4       The "user version" as read and set by the user_version pragma.
92      4       The version-valid-for number.
96      4       SQLITE_VERSION_NUMBER

As per your question it looks like you want to get SQLITE_VERSION_NUMBER. That is actually not a "database version" (and there is not such a thing), but rather the value for the SQLite library that most recently modified the database file.

To get that value you simply have to read the 4-byte big-endian integer at offset 96. For that sake, you can use python code here ("I welcome other means to do so.). I tried creating a database with python code containing the lines

import sqlite3
print('sqlite3 sqlite_version: ', sqlite3.sqlite_version)
(... create database ...)

which printed

sqlite3 sqlite_version:  3.31.1

and then reading the database header with the linked code. In particular, lines

sqlite_version_number = unpack('>i', header[96:])[0]
print('SQLITE_VERSION_NUMBER: ' + str(sqlite_version_number))

printed

SQLITE_VERSION_NUMBER: 3031001

confirming the correct reading of "the database version number" (between quotes for reasons explained above).

Notes:

  1. This would work in both Windows and Linux, as long as you have python available.

  2. You can find other ways of reading the header. The point is identifying where to look for the information you need.

    2.1. Sqlite. I couldn't find a command that would show the required info.

    2.2. Linux. dd can do the job for you.

    2.3. Powershell. [System.IO.File] or Get-Content can possibly help. I did not explore this further.

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185