SQLite Forum

Suggestion for sqldiff

Suggestion for sqldiff

(1) By tom (younique) on 2021-01-31 23:06:41 [link] [source]

I'm often using columns like "created DEFAULT CURRENT_TIMESTAMP". Now, when importing the same data into two different databases, the result will be identical except for the "created" column. This is a great annoyance when comparing these databases.

It would be a valuable enhancement to sqldiff if it could ignore all "DEFAULT CURRENT_TIMESTAMP" columns in the comparison. One would get a much better impression on whether the data contained are identical or different.

(2) By Larry Brasfield (LarryBrasfield) on 2021-02-01 00:34:27 in reply to 1 [link] [source]

It would be fairly simple to run the output of sqldiff through a filter that sifted out lines which only updated a column of your choosing in tables of your choosing. This would be simpler than using your eyes and brain, and less annoying too.

With slightly more effort, sqldiff.c could be made to accept an option telling it to ignore a table,column combination. That would be a better ask than specializing the tool to "ignore all 'DEFAULT CURRENT_TIMESTAMP' columns".

I would be remiss to neglect mention of what could be done with the sqlite3 shell, attaching the databases of interest, and specifying joins on tables that might be similar but not identical. You could ignore any set of columns that way.

(3) By Larry Brasfield (LarryBrasfield) on 2021-02-01 03:22:31 in reply to 2 [source]

For grins, I made sqldiff accept another option whose usage tip reads: --skipcolumn COL Ignore column COL differences

Here is an example of its action: C:Usersbrasf>sqlite3m -batch db1.sdb "select * from Pets" name species lastMeal Fido dog 17:54 Fluffy cat 17:49 Slither snake 15 days ago

C:Usersbrasf>sqlite3m -batch db2.sdb "select * from Pets" name species lastMeal Fido dog 17:54 Fluffy cat 17:48 Slither serpent 16 days ago

C:Usersbrasf>sqldiff db1.sdb db2.sdb UPDATE Pets SET lastMeal='17:48' WHERE rowid=2; UPDATE Pets SET species='serpent', lastMeal='16 days ago' WHERE rowid=3;

C:Usersbrasf>sqldiff -skipcolumn lastMeal db1.sdb db2.sdb UPDATE Pets SET species='serpent' WHERE rowid=3;

Here are Diffs of Changes to tool/sqldiff.c.

The option is not paired with table selection. The to-be-ignored column is ignored in any table which has the named column. It is only ignored of not part of a primary key. To restrict the ignoring to a single table, the (preexisting) --table option must be used.

(4) By tom (younique) on 2021-02-01 09:53:14 in reply to 3 [link] [source]

Great 👍

Would be nice if this patch made it into the official code 😉

(5) By Larry Brasfield (LarryBrasfield) on 2021-02-01 15:57:44 in reply to 4 [link] [source]

It needs a little more work to cause generated INSERT statements to copy all of the columns. As it is with that patch, the ignored columns need to have a default value because they do not appear in the generated INSERT statements.

I will follow up later in this thread with that revision.

(6.1) By Larry Brasfield (LarryBrasfield) on 2021-02-02 11:58:05 edited from 6.0 in reply to 5 [link] [source]

I made additional changes so that options such as --summarize do the sensible thing and various corner cases are handled correctly. (For example, specifying that all non-PK columns be ignored.) Generated inserts copy all columns from the base DB. Relevant files are:

  1. Patches for tool/sqldiff.c

  2. The sqldiff.c file from SQLite release 3.34.1 with above patches applied

  3. An NMAKE makefile to build 64-bit sqldiff.exe

(7) By Larry Brasfield (LarryBrasfield) on 2021-02-02 01:48:16 in reply to 1 [link] [source]

Yet another option for dealing with your particular annoyance:

If sqldiff is invoked as usual, but with this additional invocation argument: --debug 2 , then it blats the differencing SQL instead of the differences. That can be collected (as edited_sqldiff.sql), edited then used thusly in the sqlite3 CLI shell: .open MY_BaseDB attach MY_NewerDB as aux .output migrate_BaseNewer.sql .read edited_sqldiff.sql .output stdout .quit . The edited_sqldiff.sql is almost what sqldiff --debug 2 emits, except that it will have some lines reading: SQL for TableWhatever: which must be removed before it deserves the .sql extension.

Once that CLI shell session is run, the migrate_BaseNewer.sql file will contain SQL which, if applied to MY_BaseDB, will alter it to be equivalent to MY_NewerDB . It would be relatively easy to edit edited_sqldiff.sql so that any columns you wish to be ignored are not mentioned in value comparisons. Specifically (and most easily), all expressions resembling "A.noisyCol IS NOT B.noisyCol" can just become "0" (sans quotes.)

This may look like a lot of fuss, but the collection of and edits upon edited_sqldiff.sql need be done only once with repeats for schema changes.