Rules

Rules allow to set SQL generation rules, which can be used for Test generation (see chapter Test generation). Users can define their custom SQL generation rules based on their needs. Some default rules are defined on initial setup.

Editing a rule – edit existing rule for test generation (click on rule to edit)
Add a new rule – add a new dynamic rule for automatic test generation (click on “Add new rule” button)
Parameters – used in rule generation. Users can add new parameters or use existing default parameters (see chapter Parameters)

Parameters

Parameters are part of rules that are replaced by database metadata or user input to dynamically generate test cases. Default parameters are replaced by database metadata:

<db_name> - replaced with database name
<schema> - replaced with database schema name
<object> - replaced with table or view name
<columns> - replaced with all (or selected) column names separated by commas. Column names are wrapped based on database syntax:
- postgres, oracle, sap iq – “column”
- MS SQL server – [column]
- MySQL, MariaDB – \`column\`
<pk_columns> - replaced by primary key columns (comma separated). If primary keys are not found then the test is not generated
<forEachColumn></forEachColumn> - content between these two parameters are repeated for all (or selected) columns.
It is also possible to use parameters <column_name> and <column_data_type> in the content.

Custom parameters

Insert new parameter name and click on "+"
New parameter will be displayed in the list of parameters
This parameter can be used in rule creation and test generation
Parameters can be deleted from the list by clicking on "x"

Adding/editing a rule

Click on “Add new rule” button
Fill in rule properties
- Test name – description which is added to all tests generated by this rule. <parameter> will be replaced with custom parameters or metadata from the database. For example: “myschema.mytable – Full duplicates”
- Rule description – explanation for the rule
- SQL – SQL syntax with <parameter>.
- Expected result – expected numeric or string value from the query (can be empty).
  - greater than – >0
  - lesser than – <1000000
  - equal – =10
  - previous – >previous
  - conjunctions (&&, ||):
    - AND – >0&&>previous
    - OR >0||=previous
- Drivers/connectors – JDBC drivers which this rule applies to
Press "Add new rule" button

Example rules

These are some dynamic rules that use database metadata as parameters.

Test name:<schema>.<object> - Count of rows
Rule description: Check if object has any rows inserted
Expected result: >0
SQL:

SELECT COUNT(*) FROM <schema>.<object>

Test name:<schema>.<object> - Duplicates
Rule description: Check if object has any duplicate rows
Expected result: =0
SQL:

            SELECT COUNT(*) FROM 
            (SELECT <columns> FROM <schema>.<object> GROUP BY <columns> HAVING COUNT(*)>1) a
        

Test name:<schema>.<object> - NULL columns
Rule description: Check if object has any columns that are NULL in all rows
Expected result: empty string – leave empty
SQL:

            SELECT 
            '' <forEachColumn> || CASE WHEN COUNT(<column_name>) = 0 THEN '<column_name>;' 
            ELSE '' END </forEachColumn> FROM <schema>.<object> 
        

Deleting existing rules

Find the rule you want to delete and click on it
Click “Delete rule” button

Last modified: 21 October 2024