tDiff Format Specification

This document defines the tDiff format; a text encoding for describing the differences between two tables of data.

tDiff output is intended for two audiences: end-users who are looking for an easily readable report that describes the differences between two tables; and tools that will interpret tDiff output in order to perform further processing or analysis (e.g. "patch" tools).

tDiff should be able to describe the differences between any types of data tables. That includes: tables that have an identifiable unique key (e.g. RDBMS tables with primary key) and tables that don’t (RDBMS tables without primary key, or heaps); tables where row ordering is meaningful, whether explicit or implied, such as spreadsheets or CSV files; and tables where row ordering has no inherent meaning (RDBMS tables). Further, tDiff should be able to represent the differences between two different types of table, e.g. an RDBMS table versus a spreadsheet table.

tDiff is designed to described the differences in content between two tables-- it does not have any facilities for describing the differences in structure between two tables. A future revision will expand the specification to include structure or schema information.

tDiff is designed to described text (character) content. It has no facilities to describe differences in content that does not have a natural default character representation. A future revision may expand the specification to include arbitrary binary data.

tDiff can represent the differences between exactly two tables. It cannot represent an n-way compare where n > 2. It also cannot represent the differences between collections of tables, such as multiple-sheet spreadsheets.

tDiff documents use the UTF-8 character encoding.

tDiff documents comprise any number of comment blocks, control blocks, and diff hunks, interleaved in any order. Each diff hunk describes a related set of differences between two tables. Each hunk could stand on its own as an independent tDiff document. When there is a choice in how to decompose differences between two tables as a sequence of hunks, generators are encouraged to choose a decomposition that minimizes ordering effects between hunks.

Comment blocks are delimited using: /* */ (C style). Any content can occur within a comment block.

Control blocks begin with "# ", and are delimited by a newline or linefeed. Control blocks may hold meta information about diffs, or environmental information that might be useful to an interpreter. Apart from the special header control block, they lie outside of the scope of this specification.

A tdiff document should begin with a special control block called the header. The header begins with the characters "# tdiff". It is there to aid in rapid identification of tdiff documents. Documents without this block should be accepted by interpreters, but this block should always be included by generators.

A hunk is a series of one or more adjacent diff lines, optionally preceeded by a column line, where each diff line represents the differences between the source tables for a single row. The lines within a hunk should be separated by only the newline characters that terminate each diff line, so that they all appear as adjacent lines within a text editor. Within a tDiff document, hunks are delimited from each other via intermediate filler or comment blocks.

A diff line describes differences in a single row of the two tables that were compared. One table is designated the left or local table (called L) and the other table is designated the right or remote table (called R).

There are three types of diff lines:

Each diff line occupies its own line in the document, and begins with one of three characters. These three characters are called "line type" characters:

The line type character can be left or right padded with any amount of whitespace, for readability. The line type character is followed by any number of name-value pairs, where the names represent column names, and the values are the values for the corresponding column name in that particular row. The name is separated from the value by an equals (=) sign for identifying columns (usually part of the primary key, but see Keys versus identity) or a colon (:) sign for all other columns. The name-value pairs, as well as the line type character, are delimited with whitespace and/or a pipe '|' character.

Optionally, key names can be removed from diff lines. To do this, a special column line may be given within a hunk. This lists column names, followed by "=" for identifying columns.

This establishes bridge as an identifying column that appears first, followed by designer and length columns. We can now rewrite this:

as this:

The effect of column lines is limited to within a single hunk.

Some sources of table data have a meaningful notion of row or line number (e.g. spreadsheets, CSV files) but others do not (RDBMS tables). The representation of the row number may be external to the table data itself. In order to accommodate these sources, document generators can make use of the ROW pseudo column.

Note that a diff that uses ROW numbers may be less useful for applying to RDBMS tables, and alternate row identity choices may be considered by the generator (or specified to the generator by the user). See Keys versus identity.

Some sources of table data do not use named columns-- for instance, CSV files that have not designated a column header row. In those cases, column names are replaced with column number designators. The designators follow this pattern:

1, 2, …

N.B. The ordinal numbers embedded within column number designators are 1’s based, not 2’s based. In other words, counting starts at 1, not at zero.

If column names are omitted, and no column line is given, then ordinal numbers are assumed. All columns are assumed to be identifying, unless they are modified. The previous example can therefore be equivalently expressed as:

Though not always strictly required to fully and accurately described differences between two tables, generators are allowed to include extra rows and extra column values as contextual information. These can help human readers oriented themselves within the data, and might help machine interpreters to resolve otherwise ambiguities in applying a tDiff document.

In the case of missing or extra row lines, extra column data can be included within the row as name-value pairs, just like any other name-value pairs. In the case of a change line, extra name-value pairs can be included within the line. These contextual name-value pairs should also include only the old value. In other words, contextual column values should not include "→new-value". In addition to contextual column information, a hunk may contain any number of contextual rows. These rows appear with the line type character '*'. Streaks of contextual rows can prefix and suffix the block of actual (+, -, =) real diff lines, but cannot be interspersed amongst them. In other words, a hunk is:

Some tables are ordered, some unordered. The context features of tDiff make sense for ordered tables. When a diff between ordered tables is applied as a patch to an unordered table, context features may be ignored. When a diff between unordered tables is applied as a patch to an ordered table, the resulting order may be underspecified (but presumably does not matter).

Determining whether a row is present in L and R requires a judgment about row identity. This judgment may be simple. For example, the identity of a row may simply be the value of its primary key. However, it is possible that the identity of a row is distinct from its primary key. Consider for example a table with an auto-incrementing integer primary key, rather than something derived from the row data. Comparison of that key between separately maintained copies of that table will be meaningless. For meaningful comparison, an alternate row identity would need to be constructed.

This issue lies outside the tDiff specification, but it is important that implementors be aware that columns used for identification may in some cases not be part of the primary key.

Names or values may be quoted in a tDiff document. Quoting is done as follows:

It is always safe to single-quote a name or value. Names or values must by quoted in any of the following conditions:

In the case of column diffs, for each cell that was different between L and R, both the old and new values are displayed. The old value must come first, followed by → (dash greater than), followed by the new value. For all three diff line types, the generator may include L name-value pairs that are not strictly needed, but may help with row identification.

The linebreak non-terminal needs to be parsed greedily to work with all common end-of-line formats, since the number of linebreaks is significant in the grammar. The comment_body non-terminal is as for C.

In example one, both tables are in an RDBMS, both tables have the same column names, and the rows are identified using column1.