reference.RepoObject_reference_persistence_target_as_source - V
type: V ( view ), modify_date: 2022-01-05 18:06:35
RepoObject_guid: 4AB8C8A5-CEF9-EB11-850D-A81E8446D5B0
Description
repo.RepoObject_persistence - U contains metadata between persistence source and persistence target, this metadata needs to be included as reference
-
normally the persistence target can be a table
reference.RepoObject_reference_persistence - V -
the persistence target can be a view on top a table, then some extra handling is required:
-
add a reference: view (referenced) ← table (referencing)
reference.RepoObject_reference_persistence_target_as_source - V-
issue: often the view is based on several view data sources
-
the one and only real persistence target table
-
some additional tables which are used as filter of the target table
explicit target table name can be defined in sqldb:repo.repoobject_persistence.adoc#column-final_targetunderlinerepoobjectunderlinename
some logic is possible for most cases
-
column
has_match_left_and_suffix_tgt= 1 in case ofaaa.bbb_ccc_tgt ← aaa.bbb-
same schema
-
the view uses the suffix '_tgt'
-
the object name of the table is included in the view name
-
-
-
-
remove the reference between referencing view and referenced table
this is done in reference.RepoObject_reference - V
Where --avoid cyclic references --exclude references `aaa.bbb <- aaa.bbb_ccc_tgt`, we already added `aaa.bbb_ccc_tgt <- aaa.bbb` Not Exists ( Select 1 From reference.RepoObject_reference_persistence_target_as_source As T2 Where T2.has_match_left_and_suffix_tgt = 1 And T2.referenced_RepoObject_guid = T1.referencing_RepoObject_guid And T2.referencing_RepoObject_guid = T1.referenced_RepoObject_guid )sql
-
Columns
| PK | Column Name | Data Type | NULL? | ID |
|---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
sql_modules_definition
reference.RepoObject_reference_persistence_target_as_source - V script
/*
<<property_start>>Description
include::partial$docsnippet/reference-from-persistence.adoc[]
<<property_end>>
*/
CREATE View reference.RepoObject_reference_persistence_target_as_source
As
Select
referenced_id = ref.referencing_id
, referencing_id = ref.referenced_id
, referenced_entity_name = ref.referencing_entity_name
, referenced_fullname = ref.referencing_fullname
--, [referenced_node_id] = [referencing_node_id]
, referenced_RepoObject_guid = ref.referencing_RepoObject_guid
, referenced_schema_name = ref.referencing_schema_name
, referenced_type = ref.referencing_type
, referencing_entity_name = ref.referenced_entity_name
, referencing_fullname = ref.referenced_fullname
--, [referencing_node_id] = [referenced_node_id]
, referencing_RepoObject_guid = ref.referenced_RepoObject_guid
, referencing_schema_name = ref.referenced_schema_name
, referencing_type = ref.referenced_type
, InformationSource = 'persistence_target_as_source'
--example: aaa.bbb_ccc_tgt <- aaa.bbb
, has_match_left_and_suffix_tgt = Iif(
ref.referencing_schema_name = ref.referenced_schema_name
And Right(ref.referencing_entity_name, 4) = '_tgt'
And ref.referencing_entity_name Like ref.referenced_entity_name + '%'
, 1
, 0)
----example: aaa.bbb_tgt <- aaa.bbb
--, has_match_exact_and_suffix_tgt = Iif(
-- ref.referencing_schema_name = ref.referenced_schema_name
-- And ref.referencing_entity_name = ref.referenced_entity_name + '_tgt'
-- , 1
-- , 0)
From
reference.RepoObject_reference_SqlExpressionDependencies As ref
Left Join
repo.RepoObject_gross_persistence As ro
On
ro.RepoObject_guid = ref.referencing_RepoObject_guid
Where
ro.is_persistence = 1