Custom SQL Rules

Custom SQL Rules

This feature enables the custom sql scripts to be defined by users to execute arbitrary sql when validating the model for well-formednes is started. Once this feature is activated and the user has created a sql script for it, the result will be shown in the model validation panel / window.

Create Rules

CaseTalk already contains a lot of rules to check the validity of the information model. However since opening up the custom attributes, the use may have the need for more validation rules.

For instance a custom attribute may be defined to help users in the administrative tasks of maintaining a large model. Fact Types need approval, etc. A status attribute might provide for such administration.

Secondly a need may arise to validate the model for approvals. For these kind of examples, a custom rule could be defined in custom SQL.

In the main menu Repository \ Custom Rules.. can be selected to create rules. The default example contains:

INSERT INTO $SCHEMA$.CustomRules 
 SELECT 
 'Starts with A' as name, 
 '"' || otftname || '" starts with A' as message, 
 'OTFT' as repositoryType, 
 otftname as repositoryID, 
 0 as status,
 1 as enableEdit
 FROM $SCHEMA$.OTFT 
 WHERE otftname like 'A%'
UNION 
 SELECT 
 'Starts with Y' as name,
 '"' || otftname || '" starts with Y' as message,
 'OTFT' as repositoryType, 
 otftname as repositoryID, 
 1 as status,
 1 as enableEdit
 FROM $SCHEMA$.OTFT 
 WHERE otftname like 'Y%';

Executing rules

CaseTalk, when validating, will create a temporary table where this script can insert records. After execution CaseTalk will present the result in the validation panel / window.

SQL Requirements

The temporary table which is maintained by CaseTalk during validation has the following definition:

TableName: $SCHEMA$.CustomRules 
Columns:
name: The name of the rule (text)
message: The message for the user mentioning a single violation (text)
repositoryType: The FCO-IM entity table in which the violation occurred (text)
repositoryID: The FCO-IM entity key referring to the violation instance occurred (text)
status: The status of the violation. 0=warning; 1=error. (integer)
enableEdit: CaseTalk can start the editor for a mentioned Fact Type

An example record from the above example is:

name: Starts with A
message: "amount of euro's" starts with A
repositoryType: OTFT
repositoryID: amount of euros
status: 0
enableEdit: 1

or:

name: Starts with Y
message: "Year" starts with Y
repositoryType: OTFT
repositoryID: Year
status: 1
enableEdit: 1

Remember to test the sql in the sql repository window.

Deploying individual rules

Though CaseTalk allows a single customrules.sql file to contain multiple rules. It would be better to deploy rules separately. This way rules may be distributed in your environment without manually editing the single file.

Rules may be split into single *.rule files, and stored in the casetalk folder in the roaming profile of users.

 C:\Users\<username>\AppData\Roaming\CaseTalk8\MissingSoftSemantics.rule

The filename, or the first comment line in the script is used as a rule name in the user interface. Below is the content of such a rule file:

 -- Expression requires soft semantics
 
 INSERT INTO $SCHEMA$.CustomRules
 with  b as (
 select otft, expcode,
   (select trim(group_concat(nullif(text,)))
   from $SCHEMA$.expressionpart ep
   where e.expcode = ep.exp
     and ep.role is not null
   order by exppartno) as text_only
 from $SCHEMA$.expression e
 where e.expcode like 'F%'
 )
   SELECT
   'Expression requires soft semantics' as name,
   '"' || b.otft || '"'  as message,
   'OTFT' as repositoryType,
   b.otft as repositoryID,
   0 as status,
   1 as enableEdit
 from b
 where text_only = ;