To discover and add data objects, joins and metadata to the configuration automatically use:
Automatic Database Discovery is available for all Exago supported Data Sources except files (Excel & XML), .NET Assemblies and Microsoft OLAP. Google BigQuery Data Sources do not support primary and foreign key constraints, so the Automatic Database Discovery tools cannot add primary key columns or suggest joins.
To automatically add all complete data objects, joins and column metadata in a single operation, use the Add All Complete Objects, Joins and Metadata feature.
This feature adds new tables, views, stored procedures or database functions that have at least one primary key defined in the data source (called a complete object). Joins are added when there is a foreign key from one complete object to another.
Any data objects or joins that already exist in the configuration will not be updated with information discovered with this method, they are skipped instead. To add missing metadata or join information, use the Discover Database Metadata method described below.
To do so:
The summary tab shows the result of the operation:
The Discover Database Metadata option is available for granular control over the automatic discovery and import of Data Source schema information.It is also used when incomplete objects exist in a Data Source and are not detected with the Add All Complete Objects, Joins and Metadata v2021.2+ feature.
To start using Automatic Database Discovery, select a Data Source from the list and then either:
This will open a discovery tab for the Data Source.
Automatic Database Discovery tab
In the discovery tab you can:
Icon | Description |
---|---|
![]() |
This object has identified unique key values |
![]() |
This object has associated joins |
![]() |
This object is incomplete |
Note
If any selected Objects are missing unique key values they can be completed individually in a new tab which will open entitled Incomplete Objects.
Exago can retrieve primary and foreign key constraint information from the Data Sources by either:
The SQL statements used for Automatic Database Discovery can be customized if necessary, in order to accommodate non-standard key names.
To customize the discovery SQL:
dbconfigs
Information section to correctly override the default behavior depending on your version of Exago.Excerpt from dbconfigs.json
showing the database discovery SQL statements for Primary and Foreign Key discovery on a MySQL Data Source:
"mysql": {
"PrimaryKeySql": "SELECT CONSTRAINT_NAME AS indexname, TABLE_SCHEMA as schemaname, TABLE_NAME as tablename, COLUMN_NAME as columnname FROM information_schema.key_column_usage WHERE table_schema = schema() AND CONSTRAINT_NAME = 'PRIMARY'",
"ForeignKeySql": "SELECT TABLE_SCHEMA As schemaname, TABLE_NAME as tablename, COLUMN_NAME as columnname, REFERENCED_TABLE_SCHEMA as referencedschemaname, REFERENCED_TABLE_NAME as referencedtablename, REFERENCED_COLUMN_NAME as referencedcolumnname FROM information_schema.key_column_usage WHERE table_schema = schema() AND CONSTRAINT_NAME Like 'FK%'"
}
Exago will use the standard GetSchema()
methods to request schema information from a Data Source when:
dbconfigs.json
(or according dbconfigs.overrides.json
) file contains the PrimaryKeysCollection
and/or the ForeignKeysCollection
properties.Exago provides the definition of the PrimaryKeysCollection
and ForeignKeysCollection
properties on data source-by-data source basis. The values for each property vary with the Data Source type and the driver in use.
An example of implementing ADO.NET GetSchema() primary/foreign key constraint discovery for a MySQL Data Source follows:
{
"mysql": {
"PrimaryKeysCollection": {
"CollectionName": "Indexes",
"ConstraintNameColumn": "INDEX_NAME",
"SchemaColumn": "INDEX_SCHEMA",
"TableColumn": "TABLE_NAME",
"IsPrimaryColumn": "PRIMARY"
},
"PrimaryKeyColumnsCollection": {
"CollectionName": "IndexColumns",
"ConstraintNameColumn": "INDEX_NAME",
"SchemaColumn": "INDEX_SCHEMA",
"TableColumn": "TABLE_NAME",
"ColumnColumn": "COLUMN_NAME"
},
"ForeignKeysCollection": {
"CollectionName": "Foreign Key Columns",
"FromSchemaColumn": "TABLE_SCHEMA",
"FromTableColumn": "TABLE_NAME",
"FromColumnColumn": "COLUMN_NAME",
"ToSchemaColumn": "REFERENCED_TABLE_SCHEMA",
"ToTableColumn": "REFERENCED_TABLE_NAME",
"ToColumnColumn": "REFERENCED_COLUMN_NAME"
}
}
}