The COOPY toolbox can report the difference between tables in a number of formats, one of which is called the "hilite" format.
If someone sends you such a report, what can you do with it?
A good clue that you are looking at a highlighter diff is if you see annotations like "+++", "---", or "->" in the first column of one or more tables in a spreadsheet. Here is such a table:
|Brooklyn||J. A. Roebling||1595|
|->||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|
A highlighter diff shows the differences between two versions of a spreadsheet. It says what should be done to the first version of the spreadsheet to produce the second - by adding rows, removing rows, changing cell values, etc. The diff does not generally show all of the original spreadsheet, just parts that have changed and their immediate surroundings. See: Specification of the highlighter diff format.
Highlighter diffs can be applied to a variety of database and spreadsheet formats using the sspatch program available in the COOPY toolbox. Suppose we have received the highlighter diff as a file "diff.xls". We can do any of:
sspatch --inplace my_table.xlsx diff.xls sspatch --inplace my_table.ods diff.xls sspatch --inplace my_table.gnumeric diff.xls sspatch --inplace my_table.csv diff.xls sspatch --inplace my_table.sqlite diff.xls sspatch --inplace dbi:mysql:my_database:username=root diff.xls ...
See sspatch documentation for options.
Delete any rows from the diff that you don't want (or set the first column to empty). Then apply as normal.
INSERT INTO sheet (bridge, designer, length) VALUES ('Manhattan', 'G. Lindenthal', '1470'); UPDATE sheet SET designer='L. L. Buck' WHERE bridge='Williamsburg' AND designer='D. Duck' AND length='1600'; DELETE FROM sheet WHERE bridge='Spamspan' AND designer='S. Spamington' AND length='10000';
ssrediff --format sql diff.xls).
Here's a translation to TDIFF, the preferred format for the COOPY toolbox:
@@@ sheet @ |bridge=|designer=|length=| * |Brooklyn|J. A. Roebling|1595| + |Manhattan|G. Lindenthal|1470| = |Williamsburg|D. Duck->L. L. Buck|1600| - |Spamspan|S. Spamington|10000|
ssrediff --format tdiff diff.xls).
Here's a translation to "ROWOPS" format, a simple tabular representation of changes this can be useful if you don't have to worry about NULLs.
|sheet||context||Brooklyn||J. A. Roebling||1595|
|sheet||update||Williamsburg||D. Duck||1600||L. L. Buck|
ssrediff --format ops diff.xls).
There is a more elaborate tabular representation (
--format csv) that deals with NULLs, schema changes, etc. but is less easy to read.
The programs in the COOPY toolbox are a thin wrapper around a library written in C++ that can be accessed from other languages. See the top level BUILD.txt file of the source code. Currently Python and Ruby are supported. For example, this code in python:
import coopy class TableDiff(coopy.Patcher): def changeRow(self,change): print("Row change of type '%s'"%change.modeString()) for c in change.cond.keys(): print("* condition: %s = %s"%(c,change.cond[c].toString())) for v in change.val.keys(): print("* value: %s -> %s"%(v,change.val[v].toString())) print("") return True flags = coopy.CompareFlags() differ = TableDiff() parser = coopy.PatchParser(differ,"diff.xls",flags) parser.applyPatch()
Row change of type 'context' * condition: bridge = Brooklyn * condition: designer = J. A. Roebling * condition: length = 1595 Row change of type 'insert' * value: bridge -> Manhattan * value: designer -> G. Lindenthal * value: length -> 1470 Row change of type 'update' * condition: bridge = Williamsburg * condition: designer = D. Duck * condition: length = 1600 * value: designer -> L. L. Buck Row change of type 'delete' * condition: bridge = Spamspan * condition: designer = S. Spamington * condition: length = 10000
The equivalent code in Ruby is:
require 'coopy' class TableDiff < Coopy::Patcher def changeRow(change) puts "Row change of type '%s'"%change.modeString() for c in change.cond.keys() puts "* condition: %s = %s"%[c,change.cond[c].toString()] end for c in change.val.keys() puts "* value: %s -> %s"%[c,change.val[c].toString()] end puts "" true end end flags = Coopy::CompareFlags.new() differ = TableDiff.new() parser = Coopy::PatchParser.new(differ,"diff.xls",flags) parser.applyPatch()
The same code can read TDIFF format diff files.