Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
Description
Optiq doesn’t have a way to write SQL test scripts. Right now the only way to test a SQL query is to write Java code. So, I’m thinking of adding a mechanism.
Every database I have worked on has SQL test scripts and reference logs. For example, Hive has `.q` files and stores reference output in `.q.out` files. There are several things I don’t like about this system.
- It’s difficult to disable tests (e.g. to write a script with queries initially disabled);
- It's difficult to merge tests between branches, and even more difficult to merge reference logs between branches;
- It's difficult to program by assertion (e.g. this query should return 10 rows, or this query should return the following rows but in any order, or this query should return an error that matches a given regexp).
So I’m thinking of writing a utility that can run scripts and produce output in the same format. If a run of a script is successful, the script will generate output identical to itself! If unsuccessful, you will need to diff the script with its output. Thus, you don't need reference logs anymore.
Example:
```
!use sales
!set headers on
!set outputformat csv
select *
from emp;
empno,name
10,Fred
20,Bill
!ok
select xxx from emp;
!failed
!errorMatches "unknown column"
!if (false)
```
The syntax shown is pretty basic. I have used '!' to indicate lines that aren't queries our output. I don't mind using another syntax, but I don't want to get fancy. We can add comments, control structures, variables, as needed.
It's a similar problem to designing a markup language. The main content is the queries and the results. The control flow needs to be extensible but not get in the way. You shouldn't need to double-quote your SQL strings or escape commas in your output.
It is not a goal for this to be a scripting language that can be use interactively. But it needs to be easy to create a script by pasting from sqlline.
It is a goal to be able to extend the command set. <a href="http://www.hydromatic.net/optiq/testapidocs/net/hydromatic/optiq/test/OptiqAssert.AssertQuery.html">OptiqAssert</a> is a java class that has evolved quite nicely as we have written more tests. I would like to be able to add commands to this interpreter just as easily. We should be able to add a command at runtime, by supplying a callback, without re-compiling the parser/interpreter.
---------------- Imported from GitHub ----------------
Url: https://github.com/julianhyde/optiq/issues/290
Created by: julianhyde
Labels:
Created at: Sat May 24 00:52:14 CEST 2014
State: closed