I really like SQLite. If I am going to have to more than one pass over any data set of decent size and complexity, I like to import it into an SQLite database first.
Recently, I realized SQLite 3.9.2 had been released, whereas the sqlite3.exe shell I had on this system dated back to the early 3.8 series, and the amalgamation bundled with the installed DBD::SQLite was at version 3.8.10.
I decided to update both.
C:\...\> curl -O https://cpan.metacpan.org/authors/id/I/IS/ISHIGAKI/DBD-SQLite-1.49_03.tar.gz
C:\...\> tar -xvf DBD-SQLite-1.49_03.tar.gz
C:\...\> cd DBD-SQLite-1.49_03
C:\...\> perl Makefile.PL
C:\...\> nmake test
...
All tests successful.
Files=99, Tests=3519, 42 wallclock secs ( 0.78 usr + 0.39 sys = 1.17 CPU)
Result: PASSWonderful!
Wait, what is that message about to scroll off my ConEmu window?!
t\59_extended_result_codes.t .......................... skipped: this test requires SQLite 3.7.12 and aboveHow can that be? I did download the right source archive.
Yup! Looking in sqlite3.h, I have the right source files.
#define SQLITE_VERSION "3.9.2"
#define SQLITE_VERSION_NUMBER 3009002Here is the relevant code from t/59_extended_result_codes:
BEGIN{
plan skip_all => 'this test is for Win32 only' unless $^O eq 'MSWin32';
plan skip_all => 'this test requires SQLite 3.7.12 and above' unless $DBD::SQLite::sqlite_version_number > 3071100;
}And, here is where $DBD::SQLite::sqlite_version_number comes from:
BOOT:
init_cxt();
sv_setpv(get_sv("DBD::SQLite::sqlite_version", TRUE|GV_ADDMULTI), SQLITE_VERSION);
sv_setiv(get_sv("DBD::SQLite::sqlite_version_number", TRUE|GV_ADDMULTI), SQLITE_VERSION_NUMBER);So, we are testing 3009002 > 3071100, which, of course, fails. But, we know that 3.9.2 is a more recent version than 3.7.11.
At this point, we need to know how SQLite constructs SQLITE_VERSION_NUMBER:
The
SQLITE_VERSIONC preprocessor macro in thesqlite3.hheader evaluates to a string literal that is the SQLite version in the format “X.Y.Z” where X is the major version number (always 3 for SQLite3) and Y is the minor version number and Z is the release number. TheSQLITE_VERSION_NUMBERC preprocessor macro resolves to an integer with the value (X*1000000 + Y*1000 + Z) where X, Y, and Z are the same numbers used inSQLITE_VERSION. TheSQLITE_VERSION_NUMBERfor any given release of SQLite will also be larger than the release from which it is derived. Either Y will be held constant and Z will be incremented or else Y will be incremented and Z will be reset to zero.
This seems to indicate that the 3071100 may be a typo, and the correct integer representing version 3.7.11 would be 3007011. But, we need to verify this.
For that, we need to travel to 2012 and look at sqlite.h:
/* ** CAPI3REF: Compile-Time Library Version Numbers ** ** ^(The [SQLITE_VERSION] C preprocessor macro in the sqlite3.h header ** evaluates to a string literal that is the SQLite version in the ** format "X.Y.Z" where X is the major version number (always 3 for ** SQLite3) and Y is the minor version number and Z is the release number.)^ ** ^(The [SQLITE_VERSION_NUMBER] C preprocessor macro resolves to an integer ** with the value (X*1000000 + Y*1000 + Z) where X, Y, and Z are the same ** numbers used in [SQLITE_VERSION].)^ ** The SQLITE_VERSION_NUMBER for any given release of SQLite will also ** be larger than the release from which it is derived. Either Y will ** be held constant and Z will be incremented or else Y will be incremented ** and Z will be reset to zero.
which seems to strongly suggest that the way SQLITE_VERSION_NUMBER is constructed hasn’t changed since 3.7.11.
As one final verification step, I went back to CPAN. Luckily, DBD::SQLite 1.36_03 had bundled version 3.7.11 of SQLite. Looking at the header file, lo and behold, here is the version number:
#define SQLITE_VERSION "3.7.11"
#define SQLITE_VERSION_NUMBER 3007011
#define SQLITE_SOURCE_ID "2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669"So, we have ruled out with 100% that, at one point or another, 3071100 might have been a valid integer representation of SQLite version 3.7.11.
That is, t/59_extended_result_codes.t has never run since the version check was added on April 16, 2015.
I wanted to establish this before fixing the bug, because the fix would have been different if the version number construction scheme had ever changed.
So, let’s go ahead and make the change and see what happens.
C:\...\> prove -vb t\59_extended_result_codes.t
t\59_extended_result_codes.t ..
1..18
ok 1
ok 2 - An object of class 'DBI::db' isa 'DBI::db'
ok 3
ok 4
ok 5
ok 6
ok 7
ok 8 - An object of class 'DBI::db' isa 'DBI::db'
ok 9
ok 10
ok 11
ok 12
ok 13
ok 14 - Shouldn't be able to open a temporary directory as a database
ok 15
ok 16 - Shouldn't be able to open a temporary directory as a database
ok 17
ok 18 - no warnings
ok
All tests successful.
Files=1, Tests=18, 2 wallclock secs ( 0.08 usr + 0.05 sys = 0.12 CPU)
Result: PASSPheeewwwwwwww!!!! It’s an easy fix ;-)
diff --git a/t/59_extended_result_codes.t b/t/59_extended_result_codes.t
index 826d0a9..21124f3 100644
--- a/t/59_extended_result_codes.t
+++ b/t/59_extended_result_codes.t
@@ -12,7 +12,7 @@ use DBD::SQLite;
BEGIN{
plan skip_all => 'this test is for Win32 only' unless $^O eq 'MSWin32';
- plan skip_all => 'this test requires SQLite 3.7.12 and above' unless $DBD::SQLite::sqlite_version_number > 3071100;
+ plan skip_all => 'this test requires SQLite 3.7.12 and above' unless $DBD::SQLite::sqlite_version_number > 3007011;
}See also, PR#14.
Moral of the story: Version numbers are hard.
PS: You can discuss this post on /r/perl.