Getting started with SQLFpc REST API V2

SQLFpc exposes a service to generate the SQLFpc Coverage Rules from third party applications using a REST API (alternatively, they can be generated interactively from a browser with the web client). In order to get started on how to integrate an application using the service, read the following information.

Follow this link to get the main documentation page

NOTE: this API is deprecated but maintainted for compatibility. The new V3 API is specified with OpenApi and allows the automatic generation of clients for virtually any language.

Specification of the REST API V2 service

The service resource url is https://in2test.lsi.uniovi.es/sqlrules/api/v2/rules.xml

The service provides a POST operation that returns an XML string with the SQLFpc rules of a given SQL query

Inputs

The rules.xml method requires a body with an XML string containing three items:

<sql> (required): A string containing the SQL of the query.
<schema> (required): A string containing an XML representation of the database Schema. This xml can be generated from your database using the XDBSchema tool.
<options> (optional): Additional parameters (see Additional options).

Outputs

The service allways returns http status 200 with a string containing the rules formatted in an XML document that can be further loaded and processed by any application.

The format of the result is explained below:

The root tag named <sqlfpcws> which contains:

A header tag named <version> which contains a single text with the version number.
A tag named <sql> which contains the SQL statement.
A tag with the result of the rule generation. This can be one of the following:
1. The <error> tag is returned if an error occurred during the process. The text inside displays an explanatory message.
2. The <fpcrules> tag is returned when the rule generation is successful. It contains all rules generated and, for each one:
  - A <fpcrule> tag for each single rule. Inside this tag:
    - A set of identification tags that classify the mutant (<id>: a sequence number, <category>, <type> and <subtype>:)
    - The <sql> tag contains the SQL representation of the coverage rule
    - The <description> tag contains a textual explanation of the test situation represented by the rule

Additional options

The third item in the input (optional) is a string which may include a set of options (separated by spaces) enclosed in the tag <options></options>. Enclosed in this tag you may include one or more of the following tags:

lang=[en|es]: Specifies the output language for the textul description (lang=es or lang=en)
numberjdbcparam: Convert each jdbc parameter (?) into ?n? where n is the position in the sql. Needed if paramerers are later substituted to run the generated rules
noconditions: If present, rules for conditions (select operator) are not generated
noboolean: If present, rules for boolean conditions (select operator) are not generated
nonulls: If present, rules for nulls in conditions (select operator) are not generated
noboundaries: If present, rules for boundary values in numeric expressions are not generated
nojoins: If present, rules for joins are not generated
nosubqueries: If present, rules for subqueries are not generated
nogroupby: If present, rules for group by clauses are not generated
noaggregate: If present, rules for aggregate funcions are not generated
notautology: If present, a number of rules for WHERE and CASE conditions that are unsolvable due to coupled conditions are filtered out

Example Requests

Successful request

POST https://in2test.lsi.uniovi.es/sqlrules/api/v2/rules.xml

Body:

<body>
<sql>select S.empnum, sum(W.hours) from staff S left join works W on S.empnum=W.empnum group by S.empnum</sql>
<schema>
<table name="STAFF">
  <column name="EMPNUM" type="char" notnull="true" key="true"/>
  <column name="EMPNAME" type="char"/>
  <column name="GRADE" type="decimal"/>
  <column name="CITY" type="char"/>
</table>
<table name= "WORKS">
  <column name="EMPNUM" type="char" notnull="true" key="true"/>
  <column name="PNUM" type="char" notnull="true" key="true"/>
  <column name="HOURS" type="decimal" notnull="true"/>
</table>
</schema>
<options>nogroupby noaggregate</options>
</body>

Response (rules on GROUP BY and aggregate functions are ommited as specified in options):

<?xml version="1.0" encoding="UTF-8"?>
<sqlfpc>
<version>2.2.0.dev<development/></version>
<sql>select S.empnum, sum(W.hours) from staff S left join works W on S.empnum=W.empnum group by S.empnum</sql>
<fpcrules>
<fpcrule><id>1</id><category>J</category><type>I</type><subtype>O</subtype><location>1.1.[LEFT JOIN]</location>
<sql>SELECT S.empnum , SUM(W.hours) FROM staff S INNER JOIN works W ON S.empnum = W.empnum GROUP BY S.empnum</sql>
<description>
--The JOIN tables fulfill:
--There exist a set of rows that are fully joined (inner join)</description>
</fpcrule>
<fpcrule><id>2</id><category>J</category><type>L</type><subtype>O</subtype><location>1.1.[LEFT JOIN]</location>
<sql>SELECT S.empnum , SUM(W.hours) FROM staff S LEFT JOIN works W ON S.empnum = W.empnum WHERE (W.EMPNUM IS NULL) AND (S.EMPNUM IS NOT NULL) GROUP BY S.empnum</sql>
<description>
--The JOIN tables fulfill:
--summary: [STAFF S] left [WORKS W] ON S.empnum = W.empnum
--There exist some row in table STAFF S
--which does not join to any table in WORKS W</description>
</fpcrule>
<fpcrule><id>3</id><category>J</category><type>R</type><subtype>O</subtype><location>1.1.[LEFT JOIN]</location>
<sql>SELECT S.empnum , SUM(W.hours) FROM staff S RIGHT JOIN works W ON S.empnum = W.empnum WHERE (S.EMPNUM IS NULL) AND (W.EMPNUM IS NOT NULL) GROUP BY S.empnum</sql>
<description>
--The JOIN tables fulfill:
--summary: [STAFF S] right [WORKS W] ON S.empnum = W.empnum
--There exist some row in table WORKS W
--which does not join to any table in STAFF S</description>
</fpcrule>
</fpcrules>
</sqlfpc>

Request with errors

POST https://in2test.lsi.uniovi.es/sqlrules/api/v2/rules.xml

Body:

<body>
<sql>select S.empnum, sum(W.hours) from staff S left join works W on S.empnum=W.empnum group by S.empnum</sql>
<options>nogroupby noaggregate</options>
</body>

Response (still returns http status 200 but dispalys an error message because schema has not been specified):

<?xml version="1.0" encoding="UTF-8"?>
<sqlfpc>
<version>2.2.0.dev<development/></version>
<sql>select S.empnum, sum(W.hours) from staff S left join works W on S.empnum=W.empnum group by S.empnum</sql>
<error>Generation exception: SQLSchema.load: Table staff is not defined in the schema</error>
</sqlfpc>