Sqlite3 'dumps database schema.sql' test failing

[PWx21]

Sqlite3 'dumps database schema.sql' test failing

[glaszig]

same here and i investigated. it must have something to do with the sqlite version.

$ sqlite3 --version
3.19.3 2017-06-27 16:48:08 2b0954060fe10d6de6d479287dd88890f1bef6cc1beca11bc6cdb79f72e2377b

the test case expects the dump to contain the following:

spec/unit/hanami/model/migrator/sqlite.rb:285

expect(actual).to include %(INSERT INTO "schema_migrations" VALUES('20160831073534_create_reviews.rb');)

but the .dump command with the above sqlite version actually produces an insert statement without quoted table name:

$ sqlite3 tmp/create-259db92730853c0725162b4845c9a57c.sqlite3 .dump
[...]
INSERT INTO schema_migrations VALUES('20160831073534_create_reviews.rb');
[...]

the test suite does not fail on the ci. so, it would be interesting what sqlite version is running there.

unrelated but interesting:
i also found that if you rename a table, .dump suddenly produces a create table statement with an if not exists condition. i get the feeling that sqlite is a mess.

[glaszig]

sqlite versions seem to be the following:

• travis: 3.8 (by way of the ubuntu trusty package)
• circle: 3.16 (by way of the debian stretch package)

[glaszig]

ok. i stumbled upon the sqlite commit that changed the quoting behavior in drumroll version 3.18.
mackyle/sqlite@31866aa (original source: that commit) adds a quoting algorithm that dynamically decides whether to quote an identifier or not. it also contains test case where the quotes around table names got removed.

Attempt to determine if identifier zName needs to be quoted, either
because it contains non-alphanumeric characters, or because it is an
SQLite keyword. Be conservative in this estimate: When in doubt assume
that quoting is required.

summary

• the test suite is successful on ci because ci runs sqlite < 3.18
• sqlite 3.18 introduced different quoting behavior
• @PWx21 and I have versions of sqlite > 3.18, thus the test suite does not run through

suggestion

update the test case to to regexp matching when looking for specific queries in the dump to stay back-/forward-compatible.