Difference between revisions of "Modeler:11/RepositorySql"
(Copied from Modeler:10/RepositorySql, revision 1185) |
|||
(2 intermediate revisions by the same user not shown) | |||
Line 57: | Line 57: | ||
In case the Enterprise Edition is used, a "Server" database will be listed as well. This is the central repository as used for the CaseTalk Manager and the Teams menu. It enables users with a "System" Account to query the central repository. Handle with caution! | In case the Enterprise Edition is used, a "Server" database will be listed as well. This is the central repository as used for the CaseTalk Manager and the Teams menu. It enables users with a "System" Account to query the central repository. Handle with caution! | ||
= Server Database = | |||
Once logged in a set of usr* views are available to read data to which permissions are granted. It allows queries on the central repository to retrieve results over models and versions. | |||
[[file:ServerViews11.png]] |
Latest revision as of 11:25, 23 January 2021
SQL Repository
This window enables you to write advanced queries on the repository using sql. It uses the SQLite[1] syntax. On the left the panel shows various databases which may be queried, with their tables, views and the columns in them.
Functions
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;
External Database
Dragging sqlite databases from the project panel into the database panel will mount them to be queried. Additionally any ODBC connection in the system is listed here. This allows any ODBC source to be integrated in CaseTalk through various queries. To add a new ODBC connection, use the "Shared" button in the toolbar.
In case the Enterprise Edition is used, a "Server" database will be listed as well. This is the central repository as used for the CaseTalk Manager and the Teams menu. It enables users with a "System" Account to query the central repository. Handle with caution!
Server Database
Once logged in a set of usr* views are available to read data to which permissions are granted. It allows queries on the central repository to retrieve results over models and versions.