Getting started with SQLRules Web Client

To get started with the SQLRules web client read the following information.

1. Specifying the database schema
2. Specifying the database query
3. Specifying query parameters

1. Specifying the database schema

The first step is to specify the tables and columns that are to be used by the query. There are two different ways to perform this task: either using this Web Client or uploading a JSON or XML file.

1.1. Declaring the schema using the web client

1. Indicate the number of tables and the maximum number of columns and click Update.
2. Specify the following information about tables and columns in the Database Schema:
   1. Name of the tables (entities) and their columns (attributes) in the text fields.
   2. Specify the type of each column using the combo-box.
   3. Indicate the columns that are primary keys by clicking the Key check-box.
   4. Indicate the columns that have a NOT NULL constraint by clicking the Not Null check-box.

1.2. Declaring the schema in JSON or XML

This JSON or XML string with the database schema can be be generated from your database using the XDBSchema tool.

Click the Upload Schema String, copy the schema in the text box and then click Upload this schema. This is an example of a schema file in JSON and XML:

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

<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>
        

2. Specifying the database query

Once you have declared the schema, you fill in the SQL Query text area, select the kind of rules to tenerate (FPC rules or SQL mutants). A set of addtional options is available in order to fine tune how the rules are generated.

Next, click the Generate button and the rules are displayed: for each rule its classification (Id, Category, Type...), and the SQL query that is to be executed for evaluating the coverage. For FPC an additional textual description of the test point requirement that is satisfied if the rule is covered is shown.

An example of a query to be used with the above schema is:

 select S.empnum, sum(W.hours) from staff S left join works W on S.empnum=W.empnum group by S.empnum

3. Specifying query parameters

Parameters, when present in a query, participate in the generation process in a similar way than columns or constants, and so, their data type must be known by the system. There are several ways to specify each parameter:

• JDBC style parameters: JDBC has not named parameters, but uses the placeholder ? to denote the query parameters. However the generated rules can alter the order and number of parameters, preventing a safe parameter substitution when executing the query. To avoid this, you must select the Set Numbered JDBC parameters: The parameters will appear as ?n? to represent the parameter that matches the n in the query.
• Numbered parameters: Parameters can be numbered in the original query as in seen above?n?
• Oracle named parameters: Specify each parameter as :name where name is the parameter identifier (it may be a number).