Getting started with the SQLRules REST API

SQLRules exposes a REST service at https://in2test.lsi.uniovi.es/sqlrules/api/v3 to generate the SQLFpc coverage rules or SQLMutation mutants from third party applications (alternatively, they can be generated interactively from a browser using the web client).

The API for this service is specified with OpenApi V3.0. The following links describe the api:

Next sections describe the V3 version of the API in more in detail and how to use it with Java

Generate the API client

The client can be generated from the commandline with the OpenApi generator. This is an example of the API client generation with the OpenApi generator maven plugin and the Apache Httpclient library

    
      <plugin>
        <groupId>org.openapitools</groupId>
        <artifactId>openapi-generator-maven-plugin</artifactId>
        <version>6.0.0</version>
        <executions>
          <execution>
            <goals>
              <goal>generate</goal>
            </goals>
            <configuration>
              <inputSpec>https://in2test.lsi.uniovi.es/sqlrules/api/v3/api-docs</inputSpec>
              <generatorName>java</generatorName>
              <apiPackage>giis.sqlrules.openapi.api</apiPackage>
              <modelPackage>giis.sqlrules.openapi.model</modelPackage>
              <invokerPackage>giis.sqlrules.openapi.invoker</invokerPackage>
              <instantiationTypes>map=java.util.LinkedHashMap</instantiationTypes>
              <configOptions>
                <sourceFolder>src/main/java</sourceFolder>
                <hideGenerationTimestamp>true</hideGenerationTimestamp>
                <useRuntimeException>true</useRuntimeException>
                <library>apache-httpclient</library>
              </configOptions>
            </configuration>
          </execution>
        </executions>
      </plugin>
  

Then the API client can be instantiated as:

  SqlrulesApi rulesClient=new SqlrulesApi(new ApiClient().setBasePath("https://in2test.lsi.uniovi.es/sqlrules/api/v3"));
  

To generate the rules, invoke the rulePost or mutantsPost methods on the API client.

Using the generated API, the SQLFpc coverage rules can be generated as follows:

  SqlRules rulesModel=rulesClient.rulesPost(new SqlRulesBody().sql(sql).schema(schemaModel).options("lang=en"));
  

Next sections provide more details on the schema model, options and rules

Generate the database schema

The generation of rules requires to know some information about the database schema: the names of tables and columns, the datatype of each column, its nullability and the columns that are the primary key. You can generate the schema from your database (all tables or a subset of tables) using a jdbc connection using the XDBSchema tool, selecting JSON output and saving it to a file. This is an example of a schema in json:

{"tables":
    [{
      "name":"STAFF",
      "columns":[
        {"name":"EMPNUM","datatype":"char","notnull":"true","key":"true"},
        {"name":"EMPNAME","datatype":"char"},
        {"name":"GRADE","datatype":"decimal"},
        {"name":"CITY","datatype":"char"}
      ]
    },{
      "name":"WORKS",
      "columns":[
        {"name":"EMPNUM","datatype":"char","notnull":"true","key":"true"},
        {"name":"PNUM","datatype":"char","notnull":"true","key":"true"},
        {"name":"HOURS","datatype":"decimal","notnull":"true"}
      ]
    }]
  }

Generate the rules

Assume that the json string representing the schema is stored in the schemaString variable. To generate the SQLFpc rules you frist deserialize the json string string (e.g. with jackson) to a DbSchema object and then, call the API:

  DbSchema schemaModel=new ObjectMapper().readValue(schemaString, DbSchema.class);
  SqlRules rulesModel=rulesClient.rulesPost(new SqlRulesBody().sql(sql).schema(schemaModel).options("lang=en"));

The SqlRules object can be inspected to get the sql of each rule to be executed against the database. Typically, you first check the error field. If not empty, contains the error message (e.g. because the sql is not syntactically correct). This example fragment of a test checks the error and some values of the rules:

  if (!"".equals(rulesModel.getError()))
    throw new RuntimeException(rulesModel.getError());

  assertEquals("sqlfpc", rulesModel.getRulesClass());
  assertEquals(6, rulesModel.getRules().size());

  assertEquals("SELECT S.empnum , SUM(W.hours) FROM staff S INNER JOIN works W ON S.empnum = W.empnum GROUP BY S.empnum", rulesModel.getRules().get(0).getSql());
  assertEquals("1", rulesModel.getRules().get(0).getId());
  assertEquals("J", rulesModel.getRules().get(0).getCategory());
  assertEquals("I", rulesModel.getRules().get(0).getMaintype());
  assertEquals("O", rulesModel.getRules().get(0).getSubtype());
  assertEquals("SELECT S.empnum , SUM(W.hours) FROM staff S INNER JOIN works W ON S.empnum = W.empnum GROUP BY S.empnum", 
      rulesModel.getRules().get(0).getSql());

  assertEquals("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", 
      rulesModel.getRules().get(1).getSql());

Specify options

The value of the options key is a string that contains a set of keyword separted by spaces. It is not required, but used to parametrize the behaviour of the rule generator.

SQLFpc rules allow these options

SQLMutation mutants allow these options