Difference between revisions of "Modeler:12/RepositorySql"
m |
m (→macro) |
||
(15 intermediate revisions by the same user not shown) | |||
Line 5: | Line 5: | ||
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. | 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: | [[File:DataExplorer.png]] | ||
The SQLite | == Toolbar == | ||
The buttons from left to right represent the following functions: | |||
* Query functions for save/load and reopening | |||
* Adding and removing IG(G) databases | |||
* Refresh metadata | |||
* Commit changes back to the CaseTalk model | |||
* Run a SQL query | |||
* Navigate back and forth between previous queries | |||
* Configure ODBC connection | |||
* Configure JDBC connection | |||
== Metadata == | |||
The various connections to local databases, ODBC and JDBC, are listed on the left panel. Expanding the tree shows tables, views, columns and can be dragged and dropped onto other panels and windows. | |||
#Dragging table and column names into the query panels will complement the SQL with the names dragged. | |||
# Dragging columns onto the Repository with OTFTs will create Object Types with roles. Once the initial object type is created, additional columns will become fact types. This is an easy way to manually 'reverse engineer' an existing database schema into a conceptual model. | |||
# Dragging columns into the mapping tab, and onto the lexicalized tree items, will document the source columns as a source to the roles in the conceptual information model for lineage purposes. | |||
=== Offline metadata === | |||
Thanks to the JDBC functionality which outputs catalog information as a text, CaseTalk is not able to parse such output. Hence the catalog content can also be manually crafted or generated using external tools. These files ([[jCatalog|*.jcatalog]]) can be stored in your roaming profile folder under the AppData folder. CaseTalk will pick those up the next time. | |||
== Query == | |||
Queries can be typed by hand or completed using drag and drop of tables, views and columns. Also the parameters are supported. Especially when queries are executed more than once, and only the search parameters are different, thie may help in quickly executing them. CaseTalk will prompt you for parameter values when the query is started. | |||
[[File:DataExplorerQueryParameter.png]] | |||
The internal databases use a SQLite engine to query the data. These functions are extended with a few more functions: | |||
=== md5 === | === md5 === | ||
Line 17: | Line 45: | ||
=== macro === | === macro === | ||
The macro function supports keywords also defined in the [ | The macro function supports keywords also defined in the [[Modeler:12/CustomAttr#Keywords|custom attribute keywords]]. An example query could be: | ||
select | select | ||
Line 53: | Line 81: | ||
from otft; | from otft; | ||
= External | === isValidType === | ||
To test a value for its datatype definition, you may use this function. The parameters are: | |||
* Value | |||
* Datatype | |||
* Length | |||
* Scale | |||
select | |||
PassengerId, | |||
isValidType(PassengerId, 'integer', 0, 0) as validInteger | |||
from Titanic_Ref; | |||
= External Metadata = | |||
== ODBC == | == ODBC == | ||
Line 73: | Line 113: | ||
# Added for external java loading | # Added for external java loading | ||
db.driver.jar=Z:/Projects/jdbcExplorer/jdbcMetaData/lib/postgresql-42.5.1.jar | db.driver.jar=Z:/Projects/jdbcExplorer/jdbcMetaData/lib/postgresql-42.5.1.jar | ||
== Offline Catalogs == | |||
The jdbcExplorer outputs catalog information as text for CaseTalk. These texts can be stored elsewhere and imported into your machine to provide the catalog information. These files can also be handcrafted to provide support in using this external metadata in your model. For more information read [[jCatalog]]. | |||
== Reference Data == | == Reference Data == | ||
Line 83: | Line 126: | ||
[[file:ServerViews11.png]] | [[file:ServerViews11.png]] | ||
== Mapping == | |||
[[File:DataExplorerMapping.png|600px]] | |||
For lineage purposes the various source schemas can be dragged and dropped on the roles / lexicalized paths of the conceptual model (to the right). |
Latest revision as of 10:32, 29 March 2024
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[1] database. These can be queried using sql or viewed by clicking the relevant tables or views.
Toolbar
The buttons from left to right represent the following functions:
- Query functions for save/load and reopening
- Adding and removing IG(G) databases
- Refresh metadata
- Commit changes back to the CaseTalk model
- Run a SQL query
- Navigate back and forth between previous queries
- Configure ODBC connection
- Configure JDBC connection
Metadata
The various connections to local databases, ODBC and JDBC, are listed on the left panel. Expanding the tree shows tables, views, columns and can be dragged and dropped onto other panels and windows.
- Dragging table and column names into the query panels will complement the SQL with the names dragged.
- Dragging columns onto the Repository with OTFTs will create Object Types with roles. Once the initial object type is created, additional columns will become fact types. This is an easy way to manually 'reverse engineer' an existing database schema into a conceptual model.
- Dragging columns into the mapping tab, and onto the lexicalized tree items, will document the source columns as a source to the roles in the conceptual information model for lineage purposes.
Offline metadata
Thanks to the JDBC functionality which outputs catalog information as a text, CaseTalk is not able to parse such output. Hence the catalog content can also be manually crafted or generated using external tools. These files (*.jcatalog) can be stored in your roaming profile folder under the AppData folder. CaseTalk will pick those up the next time.
Query
Queries can be typed by hand or completed using drag and drop of tables, views and columns. Also the parameters are supported. Especially when queries are executed more than once, and only the search parameters are different, thie may help in quickly executing them. CaseTalk will prompt you for parameter values when the query is started.
The internal databases use a SQLite engine to query the data. These functions are extended with a few more functions:
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;
isValidType
To test a value for its datatype definition, you may use this function. The parameters are:
- Value
- Datatype
- Length
- Scale
select PassengerId, isValidType(PassengerId, 'integer', 0, 0) as validInteger from Titanic_Ref;
External Metadata
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.
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.
The connection configuration uses *.jdbc files stored in your roaming profile folder and may look like this:
# Below are Postgresql jdbc connection configurations. db.driver.class=org.postgresql.Driver db.conn.url=jdbc:postgresql://192.168.1.106:5432/database db.username=admin db.password= # Added for external java loading db.driver.jar=Z:/Projects/jdbcExplorer/jdbcMetaData/lib/postgresql-42.5.1.jar
Offline Catalogs
The jdbcExplorer outputs catalog information as text for CaseTalk. These texts can be stored elsewhere and imported into your machine to provide the catalog information. These files can also be handcrafted to provide support in using this external metadata in your model. For more information read jCatalog.
Reference Data
If you have downloaded database data in Excel Worksheets, you may import those XLS files to show up as external reference data. (File\Import\Reference Data)
Server Database
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!
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.
Mapping
For lineage purposes the various source schemas can be dragged and dropped on the roles / lexicalized paths of the conceptual model (to the right).