sqllogictest

the sqllogictest program reads a full script and runs the statements and
queries contained therein against a database engine under test.  The results
received back from the database engine are compared against the results
in the full script to validate the output of the database engine.

For example, to verify that SQLite gets the same answer as MySQL on
a particular set of queries, one might execute commands as follows.
First complete the prototype script using MySQL accessed through ODBC
as the reference database engine:

<blockquote><pre>
sqllogictest -odbc DSN=mysqlslt MySQL prototype.test >full.test
</pre></blockquote>

In the command above, you would, of course, substitute whatever DSN
string is appropriate for your installation.
Afterwards, verify the results using the built-in copy of SQLite:

<blockquote><pre>
sqllogictest -verify full.test
</pre></blockquote>

The second command will display any discrepencies between the output
generated by SQLite and the reference data that was generated by MySQL
in the first command.  Notice that the default mode of operation for
sqllogictest is completion mode.  The -verify command-line option is used
to activate validation mode.

Another approach to validation is to run the competion step separately
for each database engine and save the output in separate test scripts.
Then compare the two test scripts using a file comparison utility.
For example:

<blockquote><pre>
sqllogictest -odbc DSN=mysqlslt prototype.test >full-1.test
sqllogictest prototype.test >full-2.test
diff full-1.test full-2.test
</pre></blockquote>

Note that in completion mode, the sqllogictest program will ignore any
result value contained in its input test script.  Or, in other words, it is
safe to pass a full test script into sqllogictest running in completion
mode.  So the previous test could have been run this way:

<blockquote><pre>
sqllogictest -odbc DSN=mysqlslt MySQL prototype.test >full-1.test
sqllogictest full-1.test >full-2.test
diff full-1.test full-2.test
</pre></blockquote>

The long-term vision of the sqllogictest project is to amass a huge 
collection of prototype scripts.  Many of the prototype scripts will
be very large, being automatically generated by some dynamic language
like Tcl or Perl.  Prototype scripts will try queries using all kinds

expected to succeed.  But some statements can deliberately fail.  For
example, an INSERT statement that violates a UNIQUE or CHECK or NOT NULL
constraint might fail.  

A query record begins with a line of the following form:

<blockquote>
<tt>query</tt> <i>&lt;type-string&gt; &lt;sort-mode&gt; &lt;label&gt;</i>
</blockquote>

The SQL for the query is found on second an subsequent lines of the 
record up to first line of the form "----" or until the end of the
record.  Lines following the "----" are expected results of the query,
one value per line.  If the "----" and/or the results are omitted, then
the query is expected to return an empty set.  The "----" and results

single row of result, since otherwise the order of results is undefined
and might vary from one database engine to another.  The "rowsort" mode
gathers all output from the database engine then sorts it by rows on
the client side.  Sort comparisons use strcmp() on the rendered ASCII
text representation of the values.  Hence, "9" sorts after "10", not before.
The "valuesort" mode works like rowsort except that it does not honor
row groupings.  Each individual result value is sorted on its own.

The <label> argument is also optional.  If included, sqllogictest
stores a hash of the results of this query under the given label.  If
the label is reused, then sqllogictest verifies that the results are the
same.  This can be used to verify that two or more queries in the
same test script that are logically equivalent always generate the same
output.

In the results section, integer values are rendered as if by
printf("%d").  Floating point values are rendered as if by
printf("%.3f").  NULL values are rendered as "NULL".  Empty
strings are rendered as "(empty)".  Within non-empty strings,
all control characters and unprintable characters are rendered as "@".