Comparing tables using ssdiff

You can use ssdiff to summarize changes you've made between two versions of a table (or tables).

Suppose you are a bridge geek, and have been working with this table of New York bridges compiled by a friend:

bridge	designer	length
Brooklyn	J. A. Roebling	1595
Williamsburg	D. Duck	1600
Queensborough	Palmer & Hornbostel	1182
Triborough	O. H. Ammann	1380,383
Bronx Whitestone	O. H. Ammann	2300
Throgs Neck	O. H. Ammann	1800
George Washington	O. H. Ammann	3500
Spamspan	S. Spamington	10000

That table has some problems (D. Duck? Spamington?). So you fix them.

bridge	designer	length
Brooklyn	J. A. Roebling	1595
Manhattan	G. Lindenthal	1470
Williamsburg	L. L. Buck	1600
Queensborough	Palmer & Hornbostel	1182
Triborough	O. H. Ammann	1380,383
Bronx Whitestone	O. H. Ammann	2300
Throgs Neck	O. H. Ammann	1800
George Washington	O. H. Ammann	3500

As a public-spirited type, you'd like to offer your fixes to your friend. If you both happen to run the same software, chances are it has some way of tracking and highlighting your revisions, and everything is hunky-dorey. If not, then this is where sadness often begins. Some sad outcomes:

• You send your edited table to your friend, they say thanks, but since they don't have a quick way to see what you changed, or it is not in their preferred format, they never get around to actually merging in your changes.

• You try to convince your friend to use the same software as you, but they are not convinced. You argue, and pretty soon you are no longer friends.

• You try to convince your friend to use the same software as you, and they agree. The world becomes just a smidgeon more boring, and the status quo crusts over just a little bit more. Also, every problem your friend ever has with that software becomes your fault.

• You get fed up and don't send the table - too much hassle, too little reward.

COOPY can help in several ways, depending on your friend's preferences.

Presenting differences flexibly

The most basic way that COOPY can help is by letting you quickly prepare a summary of the changes you've made, in the style that is most suited to your friend. For example, if your friend is a spreadsheet user, you can use COOPY to prepare a report like this:

@@	bridge	designer	length
	Brooklyn	J. A. Roebling	1595
+++	Manhattan	G. Lindenthal	1470
->	Williamsburg	D. Duck->L. L. Buck	1600
	Queensborough	Palmer & Hornbostel	1182
	Triborough	O. H. Ammann	1380,383
...	...	...	...
	Throgs Neck	O. H. Ammann	1800
	George Washington	O. H. Ammann	3500
---	Spamspan	S. Spamington	10000

(produced with ssdiff --format hilite --output report.xls FILE1 FILE2)

This makes it very clear what you've changed, and if your friend's software understands this format, it could go ahead and apply these changes. Currently, only COOPY does, but we're working to get it more widely supported (see Specification of the highlighter diff format).

If your friend uses something that speaks SQL, COOPY can produce an equivalent report for them, along the lines of:

INSERT INTO bridges (bridge, designer, length) VALUES ('Manhattan', 'G. Lindenthal', '1470');
UPDATE bridges SET designer='L. L. Buck' WHERE bridge='Williamsburg' AND designer='D. Duck' AND length='1600';
DELETE FROM bridges WHERE bridge='Spamspan' AND designer='S. Spamington' AND length='10000';

(produced with ssdiff --format sql --default-table bridges FILE1 FILE2)

If your friend happens to already be a COOPY fan, you could also send COOPY's own preferred format for representing differences, called TDIFF. This is modeled loosely after a format used for contributing to software projects.

* |bridge=Brooklyn|
+ |bridge:->Manhattan|designer:->G. Lindenthal|length:->1470|
= |bridge=Williamsburg|designer=D. Duck->L. L. Buck|
- |bridge=Spamspan|

(produced with ssdiff FILE1 FILE2)

With any of these formats, your friend can quickly review the changes you made.

Merging differences flexibly

Visualizing differences is already useful. Being able to selectively apply those differences is even more useful.

Let's switch perspective now. Suppose we've received an email from a friend, saying they've made some fixes to our bridge list, and they've attached this spreadsheet:

@@	bridge	designer	length
	Brooklyn	J. A. Roebling	1595
+++	Manhattan	G. Lindenthal	1470
->	Williamsburg	D. Duck->L. L. Buck	1600
	Queensborough	Palmer & Hornbostel	1182
	Triborough	O. H. Ammann	1380,383
...	...	...	...
	Throgs Neck	O. H. Ammann	1800
	George Washington	O. H. Ammann	3500
---	Spamspan	S. Spamington	10000

That's handy, it is easy to see what has changed. The L.L.Buck fix is important, and how did we forget the Manhattan bridge - but the Spamspan bridge shouldn't be deleted (let's imagine we live in a parallel universe where S. Spamington is a bona fide designer, reknowned for making very long bridges).

It is simple to go ahead and make those changes manually, but we feel like doing some yak-shaving (look it up). The report is apparently in something called "hilite diff" format, supported by a vast list of programs that currently appears to be exactly one: COOPY. So we download the COOPY program, and quickly find out that we can just edit the report in anything, remove the lines we don't want applied, save the result, and then apply those changes to our table with sspatch. Our information is in a table called "bridges" in an Sqlite database, ny.sqlite. So we change the sheet name in the report to "bridges" and do:

sspatch --inplace ny.sqlite report.xls/csv/...

Done!