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.