Is sqldiff meant to detect "NOT NULL" differences?
(1) By mshamma on 2021-09-07 03:37:07 [link]
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]
Because there is a <u>[doc for sqldiff](https://sqlite.org/sqldiff.html)</u>, 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:<code> ** This is a utility program that computes the differences in content ** between two SQLite databases. </code>, 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]
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
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:<code> --table sqlite_schema </code>. These changes are available now in the SQLite code and doc repositories. They should be conveniently available at the next release.