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.
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%';
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.
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
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.
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 = ;