Import Metadata with SQL or JDBC driver

Crawling Setup

Creation and scheduling of a crawling job

The crawling setup is accessible in the “CRAWLING” panel within the detail page of a system.

Crawling Setup

By pressing the ADD CRAWLING button, it is possible to define the details of the Job and test its execution. The details required by the form are as follows:

  • Schedule name: (automatically set).
  • Cron Expression: specify the schedule.
  • Timezone: in which the cron expression is to be evaluated.
  • Flag Job Scheduled: if enabled, it activates the scheduling and automatic execution of the crawling job according to the scheduling set by the cron expression.
  • Strategy: specifies metadata reading strategy (SQL, JDBC DRIVER).
  • Target connection: identifies the connection to the target system configured on the agent.
  • Blindata Output Connection: identifies the credentials and the Blindata endpoint into which the results must be uploaded.
  • Default Hidden: if enabled, all new physical entities are initially imported in hidden mode (this behavior can also be customized with the Tables Query).
  • Automatic Cleanup: if enabled, the crawling job, after uploading all metadata to Blindata, will execute a cleanup on the system. This will eliminate all system’s resources that have not been updated (physical entities, physical fields, routines and data flows). The automatic cleanup has two additional options:
    • Days from last update: (mandatory) this option specifies the number of days since the last update, beyond which elements will be deleted if they have not been updated.
    • Dataflows scope: (optional) the scope of data flows that will be deleted if they have not been updated within the specified number of days.

Crawling Modal

JDBC Strategy

The JDBC strategy uses directly JDBC drivers to extract metadata, which is temporary stored in the following tables.

  • Tables:
    • catalog
    • table_schema
    • table_name
    • table_type
    • comment
  • Columns:
    • catalog
    • table_schema
    • table_name
    • column_name
    • data_type
    • ordinal_position
  • Physical Constraints:
    • catalog
    • name
    • schema
    • type
    • clause
    • update_rule
    • delete_rule
    • access_method
    • list_order
    • target_physical_entity_schema
    • target_physical_entity_name
    • referenced_physical_entity_schema
    • referenced_physical_entity_name
    • target_physical_field_name
    • referenced_physical_field_name

Then using SQL queries (the following are the default ones), the metadata can be imported in Blindata as is, or additional information/transformations can be applied using the SQL syntax.

  • Tables Query:
select table_schema, table_name, comment as description, null as data_set, table_type, false as is_hidden
from jdbc_metadata_tables
where table_type in ('TABLE','VIEW')
  • Columns Query:
select table_schema, table_name, column_name, data_type, ordinal_position
from jdbc_metadata_columns
  • Physical Constraints Query:
select
catalog ,
name ,
schema ,
type ,
clause ,
update_rule ,
delete_rule ,
access_method ,
list_order ,
target_physical_entity_schema ,
target_physical_entity_name ,
referenced_physical_entity_schema ,
referenced_physical_entity_name ,
target_physical_field_name ,
referenced_physical_field_name
from jdbc_metadata_constraints;

SQL Strategy

The SQL strategy directly executes an SQL query using the specified connection, to extract metadata and upload it on Blindata.

  • Tables Query: the extraction query for metadata relating to physical entities (tables in the relational case) 
    select table_schema, table_name, table_type
from information_schema.tables
where table_schema not in ('INFORMATION_SCHEMA')
  • Tables Additional Properties Query: the extraction query for additional metadata relating to physical entities
  • Columns Query: the extraction query for the metadata relating to the physical fields (columns in the relational case) 
    select table_schema, table_name, column_name, data_type, ordinal_position
from information_schema.columns
where table_schema not in ('INFORMATION_SCHEMA')
  • Columns Additional Properties Query: the extraction query for additional metadata relating to physical fields
  • Physical Constraints Query: the extraction query for physical constraints metadata. 
    select  distinct
  KCU.constraint_name as name,
  KCU.constraint_schema as schema,
  'PRIMARY_KEY' as type,
  KCU.table_schema as target_physical_entity_schema,
  KCU.table_name as target_physical_entity_name,
  KCU.column_name as target_physical_field_name,
  KCU.ordinal_position as list_order,
  NULL as referenced_physical_entity_schema,
  NULL as referenced_physical_entity_name,
  NULL as referenced_physical_field_name,
  NULL as clause,
  null as delete_rule,
  null as update_rule,
  null as access_method
from information_schema.table_constraints AS TC
inner join information_schema.key_column_usage AS KCU
on KCU.constraint_catalog = TC.constraint_catalog
and KCU.constraint_schema = TC.constraint_schema
and KCU.table_name = TC.table_name
and KCU.constraint_name = TC.constraint_name
where TC.constraint_type = 'PRIMARY KEY'
union
select  distinct
  tc.constraint_name as name,
  tc.constraint_schema as schema,
  'FOREIGN_KEY' as type,
  tc.table_schema as target_physical_entity_schema,
  tc.table_name as target_physical_entity_name,
  kcu.column_name as target_physical_field_name,
  kcu.ordinal_position as list_order,
  ccu.table_schema as referenced_physical_entity_schema,
  ccu.table_name as referenced_physical_entity_name,
  ccu.column_name as referenced_physical_field_name,
  NULL as clause,
  rc.delete_rule as delete_rule,
  rc.update_rule as update_rule,
  null as access_method
from information_schema.table_constraints as tc
join information_schema.key_column_usage as kcu
on tc.constraint_name = kcu.constraint_name
and tc.table_schema = kcu.table_schema
join information_schema.constraint_column_usage as ccu
on ccu.constraint_name = tc.constraint_name
and ccu.table_schema = tc.table_schema
join information_schema.referential_constraints rc
on rc.constraint_name = kcu.constraint_name
and kcu.column_name = ccu.column_name
where tc.constraint_type = 'FOREIGN KEY'
union
select  distinct
  KCU.constraint_name as name,
  KCU.constraint_schema as schema,
  'UNIQUE' as type,
  KCU.table_schema as target_physical_entity_schema,
  KCU.table_name as target_physical_entity_name,
  KCU.column_name as target_physical_field_name,
  KCU.ordinal_position as list_order,
  NULL as referenced_physical_entity_schema,
  NULL as referenced_physical_entity_name,
  NULL as referenced_physical_field_name,
  NULL as clause,
  null as delete_rule,
  null as update_rule,
  null as access_method
from information_schema.table_constraints AS TC
inner join information_schema.key_column_usage AS KCU
on KCU.constraint_catalog = TC.constraint_catalog
and KCU.constraint_schema = TC.constraint_schema
and KCU.table_name = TC.table_name
and KCU.constraint_name = TC.constraint_name
where TC.constraint_type = 'UNIQUE';
  • Routines Query: the extraction query for routines metadata (procedure it’s the function) present in the system 
    select * from information_schema.routines;
  • Data Flows Query: the extraction query for data flows

Fields for Extraction Queries

Each extraction query has its own list of aliases (mandatory/optional) that should be respected in order to correctly upload metadata inside Blindata.

Physical Entities Schema

The extraction aliases of the physical entities are:

Property Name Description Mandatory/Optional
SYSTEM_NAME The name of the Blindata system to which the table ( physicalEntity ) belongs. If this field is not specified, all the physicalEntities will be uploaded on the system associated to the Crawling Job. Optional
TABLE_SCHEMA The name of the schema (db) to which the table belongs Mandatory
TABLE_NAME The name of the table Mandatory
TABLE_TYPE The type of table (e.g. BASE_TABLE) Mandatory
DESCRIPTION The description of the table Optional
IS_HIDDEN Specifies if the physical entity should be loaded in hidden mode (default to false) Optional

Physical Entities Additional Properties Schema

Property Name Description Mandatory/Optional
SYSTEM_NAME The name of Blindata’s system where the physical entities is loaded. The default system is the one associated to the Crawling Job Optional
SYSTEM_UUID The uuid of Blindata’s system where the physical entities is loaded Optional
PHYSICAL_ENTITY_NAME The name of the physical entity (e.g. database table) Mandatory
PHYSICAL_ENTITY_UUID The Blindata identifier for the physical entity Optional
SCHEMA The schema of the physical entity Mandatory
PROPERTY_NAME The property key Mandatory
PROPERTY_VALUE The property value Mandatory

If SYSTEM_UUID is specified, SYSTEM_NAME could be not specified and vice versa. If PHYSICAL_ENTITY_UUID is specified, PHYSICAL_ENTITY_NAME and SCHEMA could be not specified and vice versa.

Physical Fields Schema

The physical fields extraction aliases are:

Property Name Description Mandatory/Optional
TABLE_SCHEMA The name of the schema (db) of the column’s table Mandatory
TABLE_NAME The name of the table to which the column belongs Mandatory
COLUMN_NAME The name of the column Mandatory
DATA_TYPE The type of the column Optional
ORDINAL_POSITION The number that defines the ordering of the column within the table. It becomes mandatory when you want to use the SQL Lineage module Optional

Physical Fields Additional Properties Schema

Property Name Description Mandatory/Optional
SYSTEM_NAME The name of Blindata’s system where the physical entities is loaded. The default system is the one associated to the Crawling Job Optional
SYSTEM_UUID The uuid of Blindata’s system where the physical entities is loaded Optional
PHYSICAL_ENTITY_NAME The name of the physical entity (e.g. database table) Mandatory
PHYSICAL_ENTITY_UUID The Blindata identifier for the physical entity Optional
SCHEMA The schema of the physical entity Mandatory
PHYSICAL_FIELD_NAME The name of the physical field Mandatory
PHYSICAL_FIELD_UUID The uuid of the physical field Optional
PROPERTY_NAME The property key Mandatory
PROPERTY_VALUE The property value Mandatory

If SYSTEM_UUID is specified, SYSTEM_NAME could be not specified and vice versa. If PHYSICAL_ENTITY_UUID is specified, PHYSICAL_ENTITY_NAME and SCHEMA could be not specified and vice versa. If PHYSICAL_FIELD_UUID is specified, PHYSICAL_FIELD_NAME and the physical entity’s fields could be not specified and vice versa.

System Routines Schema

The system routine aliases mirror those of the ANSI standard. The complete list comprehends:

Property Name Description Mandatory/Optional
ROUTINE_CATALOG The name of the catalog referenced by the routine Mandatory
ROUTINE_SCHEMA The name of the schema (db) referenced by the routine Mandatory
ROUTINE_NAME The name of the routine Mandatory
SPECIFIC_NAME The specific name of the routine often coincides with the name itself Mandatory
ROUTINE_TYPE Allowed values are usually PROCEDURE or FUNCTION Mandator
DATA_TYPE The return value of the function, valued only if the routine is a FUNCTION Mandatory
CHARACTER_MAXIMUM_LENGTH The maximum number of characters returned by the function type routine, in case it returns a string. NULL if the routine is a procedure Mandatory
CHARACTER_OCTET_LENGTH The maximum number of bytes returned by the function type routine, in case it returns a string. NULL if the routine is a procedure Mandatory
NUMERIC_PRECISION The numeric precision (the floating point number) in case the return value is numeric. NULL if the routine is a procedure Mandatory
NUMERIC_SCALE The numeric scale in case the return value of the function is numeric. NULL if the routine is a procedure Optional
DATETIME_PRECISION If the return value is a time value, it represents the time precision to the second. NULL if the routine is a procedure Mandatory
CHARACTER_SET_NAME The set of names if the return value is a string. NULL if the routine is a procedure Mandatory
COLLATION_NAME The comparison name in case the return value is a string. NULL if the routine is a procedure Mandatory
DTD_IDENTIFIER The type of the return value (String, numeric, etc.). NULL if the routine is a procedure Mandatory
ROUTINE_BODY The language used in defining the routine. Its value is always SQL Mandatory
ROUTINE_DEFINITION The SQL text of the routine Mandatory
EXTERNAL_NAME Value is always null Mandatory
EXTERNAL_LANGUAGE The language of the routine Mandatory
PARAMETER_STYLE The value is always SQL Mandatory
IS_DETERMINISTIC If the routine is deterministic, its value will be YES, otherwise NO Mandatory
SQL_DATA_ACCESS The characteristics of the access data of the routine. Its values can be: CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL DATA Mandatory
SECURITY_TYPE Values can be either DEFINER or INVOKER Mandatory
CREATED Routine creation date. Its value is a TIMESTAMP Mandatory
LAST_ALTERED The routine was last modified. Its value is a TIMESTAMP Mandatory
SQL_MODE The SQL mode in effect when the routine was created or altered, and under which the routine executes Mandatory
ROUTINE_COMMENT The text of the comment, if the routine has one. If not, this value is empty Mandatory
DEFINER The account named in the DEFINER clause (often the user who created the routine), usually in ‘user_name’@‘host_name’ format Mandatory
CHARACTER_SET_CLIENT The character set that comes from the client Mandatory
COLLATION_COLLECTION The connection session value when the procedure was created Mandatory
DATABASE_COLLATION The collation of the database with which the routine is associated Mandatory
SQL_PATH The schema names used to resolve unqualified names within the routine Mandatory

Physical Constraints Schema

The extraction aliases of the physical constraints are:

Property Name Description Mandatory/Optional
NAME The name of the physical constraint. Mandatory
SCHEMA The schema of the physical constraint. Mandatory
TYPE The type of the physical constraint. Eg. PRIMARY_KEY, FOREIGN_KEY, INDEX. Mandatory
CLAUSE The definition of the physical constraint. Optional
UPDATE_RULE The physical constraint update rule. Optional
DELETE_RULE The delete rule of the physical constraint. Optional
ACCESS_METHOD The access method to the physical constraint. Optional
LIST_ORDER The position of the physical field/physical entity within the physical constraint. Mandatory
TARGET_PHYSICAL_ENTITY_SCHEMA The schema of the physical entity on which the physical constraint was defined. Mandatory
TARGET_PHYSICAL_ENTITY_NAME The name of the physical entity on which the physical constraint was defined. Mandatory
TARGET_PHYSICAL_FIELD_NAME The name of the physical field on which the physical constraint was defined. Mandatory
REFERENCED_PHYSICAL_ENTITY_SCHEMA The schema of the physical entity that is referenced by the physical constraint. Optional
REFERENCED_PHYSICAL_ENTITY_NAME The name of the physical entity that is referenced by the physical constraint. Optional
SYSTEM_NAME The name of Blindata’s system where the constraint should be loaded. Optional

Crawling Test and Execution

Once the crawling configuration has been compiled, it is possible to:

  • TEST: execute the crawling job without uploading the results on Blindata. This shows only the number of information retrieved.
  • RUN: manually execute the crawling job and the upload the result on Blindata. Alteratively the job can be run periodically without user intervention using the schedule option.

Crawling History

Configurations for common databases

This section lists the main crawling configurations for some well-known systems, which can then be customized on a case-by-case basis. The defaults set by the modal work in most cases, barring the presence of the INFORMATION_SCHEMA (ANSI-standard) or performance reasons related to extracting metadata from JDBC drivers.

Oracle

Strategy: SQL

Entities Query:

SELECT owner AS table_schema, table_name, 'TABLE' AS table_type
FROM all_tables where owner in ('SCHEMA_NAME')
UNION
SELECT owner AS table_schema, view_name AS table_name, 'VIEW' AS table_type
FROM all_views where owner in ('SCHEMA_NAME')

Fields Query:

SELECT owner AS table_schema, table_name, column_name, data_type, column_id AS ordinal_position
FROM all_tab_cols where owner in ('SCHEMA_NAME')

Postgresql

Strategy: SQL, default queries on information_schema.

Physical Constraints: (This query allows also to extract indices metadata)

select  distinct
    KCU.constraint_name as name,
    KCU.constraint_schema as schema,
    'PRIMARY_KEY' as type,
    KCU.table_schema as target_physical_entity_schema,
    KCU.table_name as target_physical_entity_name,
    KCU.column_name as target_physical_field_name,
    KCU.ordinal_position as list_order,
    NULL as referenced_physical_entity_schema,
    NULL as referenced_physical_entity_name,
    NULL as referenced_physical_field_name,
    NULL as clause,
    null as delete_rule,
    null as update_rule,
    null as access_method
  from information_schema.table_constraints AS TC
  inner join information_schema.key_column_usage AS KCU
  on KCU.constraint_catalog = TC.constraint_catalog
  and KCU.constraint_schema = TC.constraint_schema
  and KCU.table_name = TC.table_name
  and KCU.constraint_name = TC.constraint_name
  where TC.constraint_type = 'PRIMARY KEY'
  union
  select  distinct
    tc.constraint_name as name,
    tc.constraint_schema as schema,
    'FOREIGN_KEY' as type,
    tc.table_schema as target_physical_entity_schema,
    tc.table_name as target_physical_entity_name,
    kcu.column_name as target_physical_field_name,
    kcu.ordinal_position as list_order,
    ccu.table_schema as referenced_physical_entity_schema,
    ccu.table_name as referenced_physical_entity_name,
    ccu.column_name as referenced_physical_field_name,
    NULL as clause,
    rc.delete_rule as delete_rule,
    rc.update_rule as update_rule,
    null as access_method
  from information_schema.table_constraints as tc
  join information_schema.key_column_usage as kcu
  on tc.constraint_name = kcu.constraint_name
  and tc.table_schema = kcu.table_schema
  join information_schema.constraint_column_usage as ccu
  on ccu.constraint_name = tc.constraint_name
  and ccu.table_schema = tc.table_schema
  join information_schema.referential_constraints rc
  on rc.constraint_name = kcu.constraint_name
  and kcu.column_name = ccu.column_name
  where tc.constraint_type = 'FOREIGN KEY'
  union
  select  distinct
    KCU.constraint_name as name,
    KCU.constraint_schema as schema,
    'UNIQUE' as type,
    KCU.table_schema as target_physical_entity_schema,
    KCU.table_name as target_physical_entity_name,
    KCU.column_name as target_physical_field_name,
    KCU.ordinal_position as list_order,
    NULL as referenced_physical_entity_schema,
    NULL as referenced_physical_entity_name,
    NULL as referenced_physical_field_name,
    NULL as clause,
    null as delete_rule,
    null as update_rule,
    null as access_method
  from information_schema.table_constraints AS TC
  inner join information_schema.key_column_usage AS KCU
  on KCU.constraint_catalog = TC.constraint_catalog
  and KCU.constraint_schema = TC.constraint_schema
  and KCU.table_name = TC.table_name
  and KCU.constraint_name = TC.constraint_name
  where TC.constraint_type = 'UNIQUE'
  union
  select  distinct
    i.relname as name,
    ixs.schemaname  as schema,
    'INDEX' as type,
    ixs.schemaname  as target_physical_entity_schema,
    t.relname as target_physical_entity_name,
    a.attname as target_physical_field_name,
    a.attnum  as list_order,
    NULL as referenced_physical_entity_schema,
    NULL as referenced_physical_entity_name,
    NULL as referenced_physical_field_name,
    ixs.indexdef as clause,
    null as delete_rule,
    null as update_rule,
    null as access_method
  from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_indexes ixs,
    pg_attribute a
  where	i.relname = ixs.indexname
  and t.oid = ix.indrelid
  and i.oid = ix.indexrelid
  and a.attrelid = t.oid
  and a.attnum = ANY(ix.indkey);

Routines: if routines permissions and ownership cause problems with view definitions extraction, the following query should be used instead.

select
'put_catalog_name_here' as ROUTINE_CATALOG ,
n.nspname as ROUTINE_SCHEMA,
p.proname as ROUTINE_NAME,
p.proname as SPECIFIC_NAME,
null as ROUTINE_TYPE,
null as DATA_TYPE,
null as CHARACTER_MAXIMUM_LENGTH,
null as CHARACTER_OCTET_LENGTH,
null as NUMERIC_PRECISION,
null as NUMERIC_SCALE,
null as DATETIME_PRECISION,
null as CHARACTER_SET_NAME,
null as COLLATION_NAME,
null as DTD_IDENTIFIER,
null as ROUTINE_BODY,
p.prosrc as ROUTINE_DEFINITION,
null as EXTERNAL_NAME,
null as EXTERNAL_LANGUAGE,
null as PARAMETER_STYLE,
null as IS_DETERMINISTIC,
null as SPECIFIC_NAME,
null as SQL_DATA_ACCESS,
null as SECURITY_TYPE,
null as CREATED,
null as LAST_ALTERED,
null as SQL_MODE,
null as ROUTINE_COMMENT,
null as DEFINER,
null as CHARACTER_SET_CLIENT,
null as COLLATION_CONNECTION,
null as DATABASE_COLLATION,
null as SQL_PATH
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
join pg_catalog.pg_user b on b.usesysid = p.proowner
where nspname not in ('information_schema', 'pg_catalog');

MySQL

Strategy: SQL Default query on information_schema

Routines Query:

select * from information_schema.routines;

BigQuery

Strategy: SQL or JDBC using default queries.

RedShift

Strategy: SQL, default queries on information_schema.

Strategy: SQL

Routines Query:

select
'edpproddbred_repo' as ROUTINE_CATALOG ,
n.nspname as ROUTINE_SCHEMA,
p.proname as ROUTINE_NAME,
p.proname as SPECIFIC_NAME,
null as ROUTINE_TYPE,
null as DATA_TYPE,
null as CHARACTER_MAXIMUM_LENGTH,
null as CHARACTER_OCTET_LENGTH,
null as NUMERIC_PRECISION,
null as NUMERIC_SCALE,
null as DATETIME_PRECISION,
null as CHARACTER_SET_NAME,
null as COLLATION_NAME,
null as DTD_IDENTIFIER,
null as ROUTINE_BODY,
p.prosrc as ROUTINE_DEFINITION,
null as EXTERNAL_NAME,
null as EXTERNAL_LANGUAGE,
null as PARAMETER_STYLE,
null as IS_DETERMINISTIC,
null as SPECIFIC_NAME,
null as SQL_DATA_ACCESS,
null as SECURITY_TYPE,
null as CREATED,
null as LAST_ALTERED,
null as SQL_MODE,
null as ROUTINE_COMMENT,
null as DEFINER,
null as CHARACTER_SET_CLIENT,
null as COLLATION_CONNECTION,
null as DATABASE_COLLATION,
null as SQL_PATH
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
join pg_catalog.pg_user b on b.usesysid = p.proowner
where nspname not in ('information_schema', 'pg_catalog')

HIVE

Strategy: JDBC.

Spotfire

Strategy: SQL

The following queries can be used to extract Spotfire’s reports and to generate data flows from the tables and columns of a source system to the reports in the Spotfire system.

Reports Query (physical entities):

SELECT distinct
li.TITLE as table_name,
'REPORT' as table_type,
left((
  coalesce (folders5.title || '/', '') ||
  coalesce (folders4.title || '/' , '') ||
  coalesce (folders3.title || '/', '') ||
  coalesce (folders2.title || '/', '') ||
  coalesce (folders.title, '')
), 255) as table_schema,
coalesce (li.DESCRIPTION, '') as description
FROM LIB_ITEMS li
left join lib_items folders
on li.parent_id = folders.item_id
left join lib_items folders2
on folders.parent_id = folders2.item_id
left join lib_items folders3
on folders2.parent_id = folders3.item_id
left join lib_items folders4
on folders3.parent_id = folders4.item_id
left join lib_items folders5
on folders4.parent_id = folders5.item_id
WHERE li.ITEM_TYPE = (SELECT LIB_ITEM_TYPES.TYPE_ID FROM LIB_ITEM_TYPES WHERE LIB_ITEM_TYPES.LABEL = 'dxp')

Data Flows Query:

SELECT distinct
'insert_dataflow_scope_here' as scope,
left((
	 coalesce(SchemaName.Property_Value || '.', '') ||
	 coalesce(TableName.Property_Value || '.','') ||
	 coalesce(ColumnName.Property_Value,'') ||
	 '->' ||
	 coalesce(SchemaName.ReportFolder || '/', '') ||
	 coalesce(SchemaName.ReportName, '')
   ),
   255
) as name,
'insert_data_flow_description_here' as description,
'insert_source_system_uuid (optional)' as from_system_uuid,
'insert_source_system_name' as from_system_name,
SchemaName.Property_Value as from_physical_entity_schema,
TableName.Property_Value as from_physical_entity_name,
ColumnName.Property_Value as from_physical_field_name,

'insert_spotfire_system_uuid (optional)' as to_system_uuid,
'insert_spotfire_system_name' as to_system_name,
left(coalesce(SchemaName.ReportFolder, ''), 255) as to_physical_entity_schema,
SchemaName.ReportName as to_physical_entity_name
FROM
( SELECT A.ReportName, D.* FROM (SELECT TITLE ReportName, ITEM_ID
         ReportItemID
    FROM lib_items) A
JOIN LIB_RESOLVED_DEPEND B ON A.ReportItemID = B.DEPENDENT_ID
JOIN LIB_RESOLVED_DEPEND C ON B.REQUIRED_ID  = C.DEPENDENT_ID
JOIN LIB_PROPERTIES      D ON D.ITEM_ID      = C.REQUIRED_ID
WHERE D.PROPERTY_NAME = 'table') TableName
JOIN (SELECT A.ReportName, D.*
FROM (SELECT TITLE ReportName, ITEM_ID ReportItemID
        FROM lib_items ) A
JOIN LIB_RESOLVED_DEPEND B ON A.ReportItemID = B.DEPENDENT_ID
JOIN LIB_RESOLVED_DEPEND C ON B.REQUIRED_ID = C.DEPENDENT_ID
JOIN LIB_PROPERTIES D ON D.ITEM_ID = C.REQUIRED_ID
WHERE D.PROPERTY_NAME = 'column') ColumnName
   ON TableName.ITEM_ID    = ColumnName.ITEM_ID AND
      TableName.ReportName = ColumnName.ReportName
JOIN (SELECT A.ReportName, D.*
FROM (SELECT TITLE ReportName, ITEM_ID ReportItemID
FROM lib_items) A
JOIN LIB_RESOLVED_DEPEND B ON A.ReportItemID = B.DEPENDENT_ID
JOIN LIB_RESOLVED_DEPEND C ON B.REQUIRED_ID = C.DEPENDENT_ID
JOIN LIB_PROPERTIES D ON D.ITEM_ID = C.REQUIRED_ID
WHERE D.PROPERTY_NAME = 'catalog') DatabaseName
   ON ColumnName.ITEM_ID      = DatabaseName.ITEM_ID AND
      DatabaseName.ReportName = ColumnName.ReportName
JOIN ( SELECT A.ReportName, D.*, left(
 (
 coalesce (folders5.title || '/', '') ||
coalesce (folders4.title || '/' , '') ||
coalesce (folders3.title || '/', '') ||
coalesce (folders2.title || '/', '') ||
coalesce (folders.title, '')
), 255) as ReportFolder
               FROM ( SELECT TITLE ReportName, ITEM_ID ReportItemID, parent_id
                        FROM lib_items ) A
JOIN LIB_RESOLVED_DEPEND B ON A.ReportItemID = B.DEPENDENT_ID
JOIN LIB_RESOLVED_DEPEND C ON B.REQUIRED_ID = C.DEPENDENT_ID
JOIN LIB_PROPERTIES D ON D.ITEM_ID = C.REQUIRED_ID

left join lib_items folders
on A.parent_id = folders.item_id
left join lib_items folders2
on folders.parent_id = folders2.item_id
left join lib_items folders3
on folders2.parent_id = folders3.item_id
left join lib_items folders4
on folders3.parent_id = folders4.item_id
left join lib_items folders5
on folders4.parent_id = folders5.item_id

WHERE D.PROPERTY_NAME = 'schema' ) SchemaName
   ON DatabaseName.ITEM_ID    = SchemaName.ITEM_ID AND
      DatabaseName.ReportName = SchemaName.ReportName

Updated on 06 Jun 2024