Is sqldiff meant to detect "NOT NULL" differences?
(1) By mshamma on 2021-09-07 03:37:07 [link] [source]
I was playing around with sqldiff while developing some schema tests.
I noticed that the tool does not raise errors if a table/column in the two given databases differ in "NOT NULL".
Here is a simple example run that demonstrates the problem:
mshamma@shamma /tmp sqlite3 SQLite version 3.32.3 2020-06-18 14:16:19 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .open not_null.db sqlite> CREATE TABLE groups ( ...> group_id INTEGER PRIMARY KEY, ...> name TEXT NOT NULL ...> ); sqlite> .open null.db sqlite> CREATE TABLE groups ( ...> group_id INTEGER PRIMARY KEY, ...> name TEXT ...> ); sqlite> ^D mshamma@shamma /tmp sqldiff --schema not_null.db null.db mshamma@shamma /tmp
Is it expected that sqldiff does not complain about this difference? Alternatively, is this a known bug tracked anywhere?
(2) By Larry Brasfield (larrybr) on 2021-09-07 15:00:03 in reply to 1 [link] [source]
Because there is a doc for sqldiff, and that doc does not mention the below point about content, I think this should be considered a bug.
The first, non-boiler-plate comment in sqldiff.c states:
** This is a utility program that computes the differences in content
** between two SQLite databases.
, and if that was the utility's documentation, I would not see its limited capability as a bug. (One could argue that differing sqlite_schema table content should show up in the output, but that is normally considered to be the schema rather than content.)
Many other schema differences escape sqldiff's notice; it only insists that like-named tables have matching column names (ignoring case) in the same order to be considered content-comparable.
Whether this will be considered a doc bug or a code bug remains to be seen. I will take this up with other project members and fix one or the other.
(3) By mshamma on 2021-09-07 18:59:36 in reply to 2 [link] [source]
Thanks for the reply Larry, and thanks for planning to bring it up to the devs.
One thing to note is that the utility takes a "--schema" flag to restrict diffs to schema as opposed to schema and contents. This is what made me believe it might be a utility bug more than a documentation bug.
(4) By Larry Brasfield (larrybr) on 2021-09-07 19:36:38 in reply to 3 [source]
Your initial belief on this was entirely reasonable given what the doc page and sqldiff's --help output said.
The doc page has been clarified to emphasize that the (usual) comparison is on content and to note the limited effect of the --schema option.
The sqldiff utility has been modified to make it possible to compare schema among databases more completely, via the option:
These changes are available now in the SQLite code and doc repositories. They should be conveniently available at the next release.