Is sqldiff meant to detect "NOT NULL" differences?
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?
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.
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.
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.