Add `.ost`, a file format for idempotent test scripts

[julianhyde]

Add OST (Ossie SQL Test, file suffix.ost), a file format for idempotent test scripts.

Each test script contains the expected output of each command, and "idempotent" means that if the test script succeeds it generates itself. If a test script fails, it generates a script that would succeed if it were copied over the original. (The user must of course check that the output is correct.)

Earlier idempotent formats are Sqlite's Sql Logic Test .slt, Quidem (.iq) used by Apache Calcite and Morel's .smli.

Example:

# File header with copyright
# 
# An example .ost (Ossie SQL Test) file.

# Connect to a particular database.
connect("postgres");

# A test case that is a SQL query.
# Because there is no ORDER BY clause, any output order is acceptable.
# Changes in whitespace are also acceptable.
select *
from emps
where job = 'CLERK';
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |         |     10 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |     20 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |         |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |         |     30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
(4 rows)

# A test case that tests whether a query is valid. The output is a boolean.
# Output of any type besides row-set is printed as lines with '> ' prefix.
isValid(
  select *
  from emps
  group by deptno);
> false : boolean

# In the test harness, you can easily define new functions.
plus(1, 2);
> 3 : int

# A test case that prints the expansion of an Ossie query into underlying Postgres.
# The output is a raw string that contains newlines.
expand(
  with enhancedEmps as (
    select *,
        measure(avg(sal)) as avg_sal
    from scott.emps)
  select deptno, measure(avg_sal)
  from enhancedEmps);
> {_|
select deptno,
  avg(sal) as avg_sal
from scott.emps
|_} : string

# End example.ost

The syntax of the raw string literal in the last example is syntax based on .smli and OCaml.

Benefits

The format has a number of benefits over golden scripts or tests written in the implementation language (Python).

• Idempotent scripts are more convenient than golden scripts because there is only one file to manage. (Handling merge conflicts in golden files is error-prone because neither file contains the full picture, but both files must be kept in sync.)
• With a few comments, an idempotent script is a readable example that helps a user understand a new feature.
• Compared to code (.py) or markup (.xml, .json, .yaml) formats, .ost files are easy to diff and merge. Each added section is typically a number of whole lines, and regions are often surrounded by blank lines.
• A test case and its result are concise, and therefore you can cover several cases in one page and many cases in one file.
• The format is not tied to a particular implementation language (say Python or Java). To create an implementation in a new language, you just need to write a runner that can parse and execute scripts in the OST format.
• It is easy to add new functions if there are new things you want to test. Therefore you can put most of your test cases in scripts, rather than the implementation language.
• Agents quickly learn how to generate scripts for ad hoc testing.

Implementation

The implementation will be a Python harness that can parse and execute scripts. If the test fails (even after accommodating changes in whitespace and line order) the harness generates a corrected script.

$ ost --help
Usage: ost [option...] [file...]

Options:
 --help Print usage

Ossie SQL Test)