Modeler: 9/RepositorySql

SQL Repository

This window enables you to write advanced queries on the repository using sql. It uses the SQLite syntax. You may read about it at [1].

The SQLite function set is extended with a few more functions, especially used inside CaseTalk:

md5

The md5 function generates a hash from the value passed as text.

 select 
 md5(otftname) as OTFT_HASH, OTFTName
 from otft;

macro

The macro function supports keywords also defined in the custom attribute keywords. An example query could be:

 select 
 macro('%PROJECTNAME%\%IGNAME%\v%IGVERSION%\' || otftname) as FULL_OTFT 
 from otft;

soundex

The soundex function returns a code for the text which allows text comparisons on the sound of them.

 select otftname, soundex(otftname) from otft

annotation

Extract the named annotation from a sectioned comment field. CaseTalk supports Custom Annotations, yet stores them in a single textblob. This function enables sql to extract the named annotation.

 select 
 otftname, 
 annotation('Comment', comment) as comment,
 annotation('Remarks', comment) as remarks
 from otft;

Registry

Fetch the registry entry value by the name as a simple function instead of a subselect in sql.

 select
   otftname as tablename,
   registry('Profile') as IG_Transformation
 from otft;

CustomAttribute

Fetch custom attribute value using a simple function instead of a long subquery.

 select
   otftname,
   customattributes('otft', otftname, 'source', 'modeler') as author
 from otft;