reference.RepoObject_reference_persistence - V
type: V ( view ), modify_date: 2022-01-05 17:04:09
RepoObject_guid: F38F291C-9D61-EB11-84DC-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 )
-
Columns
| PK | Column Name | Data Type | NULL? | ID |
|---|---|---|---|---|
1 |
|
|
||
2 |
|
|
||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
PK_RepoObject_reference_persistence
-
IndexSemanticGroup: no_group
-
[column-referenced_RepoObject_guid]; uniqueidentifier
-
[column-referencing_RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 1, 1, 0
idx_RepoObject_reference_persistence__2
-
IndexSemanticGroup: no_group
-
[column-referenced_schema_name]; nvarchar(128)
-
[column-referenced_entity_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_reference_persistence__3
-
IndexSemanticGroup: no_group
-
[column-referencing_schema_name]; nvarchar(128)
-
[column-referencing_entity_name]; nvarchar(128)
-
-
PK, Unique, Real: 0, 0, 0
idx_RepoObject_reference_persistence__4
-
IndexSemanticGroup: no_group
-
[column-referencing_RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
Column Details
_
referencing_RepoObject_guid
2 |
referencing_RepoObject_guid |
|
|
referenced_fullname
referenced_fullname |
|
|
(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))
referencing_fullname
referencing_fullname |
|
|
(concat('[',[RepoObject_schema_name],'].[',[RepoObject_name],']'))
sql_modules_definition
reference.RepoObject_reference_persistence - V script
/*
<<property_start>>Description
include::partial$docsnippet/reference-from-persistence.adoc[]
<<property_end>>
*/
CREATE View [reference].[RepoObject_reference_persistence]
As
--
Select
--
referenced_RepoObject_guid = rop.source_RepoObject_guid
, referencing_RepoObject_guid = rop.target_RepoObject_guid
, referenced_entity_name = ro_s.SysObject_name
, referenced_fullname = ro_s.RepoObject_fullname
, referenced_id = ro_s.SysObject_id
--, referenced_node_id = ro_s.node_id
, referenced_schema_name = ro_s.SysObject_schema_name
, referenced_type = ro_s.SysObject_type
, referencing_entity_name = ro_t.SysObject_name
, referencing_fullname = ro_t.RepoObject_fullname
, referencing_id = ro_t.SysObject_id
--, referencing_node_id = ro_t.node_id
, referencing_schema_name = ro_t.SysObject_schema_name
, referencing_type = Coalesce ( ro_t.SysObject_type, ro_t.RepoObject_type, 'U' )
, InformationSource = 'repo.RepoObject_persistence'
From
repo.RepoObject_persistence As rop
Inner Join
repo.RepoObject As ro_t
On
ro_t.RepoObject_guid = rop.target_RepoObject_guid
Inner Join
repo.RepoObject As ro_s
On
ro_s.RepoObject_guid = rop.source_RepoObject_guid