roger-mahler
commented
4 years ago SQL query:
with public_foreign_keys as (
select table_name as source_name,
foreign_table_name as target_name,
foreign_column_name as column_name
from sead_utility.foreign_key_columns
where table_schema = 'public'
), public_relations as (
select source_name, target_name, column_name
from public_foreign_keys
union all
select target_name, source_name, column_name
from public_foreign_keys
), facet_undirected_relations as (
select t1.table_or_udf_name as source_name,
t2.table_or_udf_name as target_name,
r.target_column_name as column_name
from facet.table_relation r
join facet.table t1 on t1.table_id = r.source_table_id
join facet.table t2 on t2.table_id = r.target_table_id
where r.source_column_name = r.target_column_name
), facet_relations as (
select source_name, target_name, column_name
from facet_undirected_relations
union all
select target_name, source_name, column_name
from facet_undirected_relations
), consolidated_relations as (
select coalesce(p.source_name, f.source_name) as source_name,
coalesce(p.target_name, f.target_name) as target_name,
coalesce(p.column_name, f.column_name) as column_name,
case when p.source_name is null then '' else 'FK' end as is_public,
case when f.source_name is null then '' else 'EDGE' end as is_facet
from public_relations p
full outer join facet_relations f
on f.source_name = p.source_name
and f.target_name = p.target_name
) select distinct
'(''' || case when source_name <= target_name then source_name else target_name end || ''', ''' ||
'''' || case when source_name > target_name then source_name else target_name end || ''', ''' ||
'''' || column_name || '''), -- ' || is_public || ' ' || is_facet
from consolidated_relations
where is_public || is_facet <> 'FKEDGE'
and source_name not like 'facet.%'
and target_name not like 'facet.%'
order by 1
Find all discrepancies between the facet graph and the public SEAD database foreign key relations. Purpose is to identify:
The check assume that all relations are bi-directional.