Universidad Complutense de Madrid

 

SQL test case generator  SQLTCG

Tool for generating SQL test cases included in Datalog Educational System DES.

 

Download Install How SQLTCG works Examples

Download

Source distribution (including documentation) SQLTCG -Beta release-

Install

This source distribution only works with SICStus Prolog.

For using this software, download it and perform the following steps:

1. Run SICStus Prolog.

2. Change your working directory to the installation directory for DES (menu file -> working directory and select the corresponding path for DES).

3. Consult the file des.pl

4. The system prompt DES-datalog> should be ready for user input.

How SQLTCG works

Switch to the SQL query language via the command  /sql.

The following system session can be tried, which firstly consists of creating SQL tables and  views:

DES-SQL> CREATE OR REPLACE TABLE t(a int PRIMARY KEY, b int);
DES-SQL> CREATE OR REPLACE VIEW u(a1, a2) AS SELECT a, b FROM t where a >= 10;
DES-SQL> CREATE OR REPLACE VIEW v(a) AS SELECT a2 FROM u WHERE a2 = 88  GROUP BY a2 HAVING SUM(a1) > 0;

If you wish to automatically reproduce the previous interactive session of queries, you can type the command /process examples/extc01.sql.

In order to generate the test case for the SQL view v, type the command  /test_case v. Then, the output is a test case for the view  v:

DES-SQL> /test_case v
Info: Test Case over integers and strings:
[t([[1000,88],[999,1000]])]

The database instance generated consists of a table instance of a table t containing  two tuples.

Other examples

extc02.sql . You can generate a test case for the SQLview v_name_mainauthor typing the command /test_case v_name_mainauthor.

Figure above shows the dependency tree of the view v_name_mainauthor.

extc03.sql . By mean this exampl, you can generate a test case for SQL queries with subqueries. For instance:

CREATE OR REPLACE VIEW v_aut_withp(author, name) AS
              select a.id AS author, a.name AS name
              from t_authors AS a
where exists (select *
from t_pub2auth where a.id = t_pub2auth.id_author);
: