View source for Modeler:12/RepositorySql

= Data Explorer =
This window (formerly named ''SQL Repository'') enables you to access various databases and execute SQL queries to view the data. The metadata tree on the left contains a list of databases, schemas, tables and views and their columns.

== Repository ==
The IG and IGG files are mirrored in a SQLite[http://www.sqlite.org] database. These can be queried using sql or viewed by clicking the relevant tables or views.

[[File:SqlRepository.png]]

= 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 [http://wiki.casetalk.com/Modeler:9/CustomAttr#Keywords 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 =

== ODBC ==
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.

[[File:ODBC_Add.png|600px]]

== JDBC ==
If JAVA is installed in your machine (or available in a subfolder of CaseTalk), JDBC connections can be used to connect to external databases as well.

== Reference Data ==
If you have downloaded database data in Excel Worksheets, you may import those XLS files to show up as external reference data.

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]]