SQLite Forum

Is sqldiff meant to detect "NOT NULL" differences?

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

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 [link]

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

These changes are available now in the SQLite code and doc repositories. They should be conveniently available at the next release.