View source for 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.

[[File:Customrulesresult.png]]

== 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 [[Modeler:8/RepositorySql| sql repository window]].