reference.RepoObject_reference_persistence_target_as_source_explicit - V

type: V ( view ), modify_date: 2022-01-22 00:35:32

RepoObject_guid: C71DCA55-AE7B-EC11-8541-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

        some logic is possible for most cases

        • column has_match_left_and_suffix_tgt = 1 in case of aaa.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

      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
      )

Examples

Entity Diagram

entity-reference.repoobject_reference_persistence_target_as_source_explicit

Columns

Table 1. Columns of reference.RepoObject_reference_persistence_target_as_source_explicit - V
PK Column Name Data Type NULL? ID

varchar(37)

NOT NULL

nvarchar(128)

NULL

nvarchar(517)

NULL

int

NOT NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

char(2)

NULL

nvarchar(128)

NULL

nvarchar(517)

NULL

int

NULL

uniqueidentifier

NULL

nvarchar(128)

NULL

char(2)

NULL

Foreign Key Diagram

entity_1_1_fk-reference.repoobject_reference_persistence_target_as_source_explicit

References

Object Reference Diagram - 1 1

entity_1_1_objectref-reference.repoobject_reference_persistence_target_as_source_explicit

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-reference.repoobject_reference_persistence_target_as_source_explicit

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-reference.repoobject_reference_persistence_target_as_source_explicit

Column Reference Diagram

entity_1_1_colref-reference.repoobject_reference_persistence_target_as_source_explicit

Column Details

_

InformationSource

InformationSource

varchar(37)

NOT NULL

referenced_entity_name

referenced_entity_name

nvarchar(128)

NULL

referenced_fullname

referenced_fullname

nvarchar(517)

NULL

referenced_id

referenced_id

int

NOT NULL

referenced_RepoObject_guid

referenced_RepoObject_guid

uniqueidentifier

NULL

referenced_schema_name

referenced_schema_name

nvarchar(128)

NULL

referenced_type

referenced_type

char(2)

NULL

referencing_entity_name

referencing_entity_name

nvarchar(128)

NULL

referencing_fullname

referencing_fullname

nvarchar(517)

NULL

referencing_id

referencing_id

int

NULL

referencing_RepoObject_guid

referencing_RepoObject_guid

uniqueidentifier

NULL

referencing_schema_name

referencing_schema_name

nvarchar(128)

NULL

referencing_type

referencing_type

char(2)

NULL

sql_modules_definition

reference.RepoObject_reference_persistence_target_as_source_explicit - V script
/*
<<property_start>>Description
include::partial$docsnippet/reference-from-persistence.adoc[]
<<property_end>>
*/
Create View reference.RepoObject_reference_persistence_target_as_source_explicit
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_explicit'
From
    reference.RepoObject_reference_SqlExpressionDependencies As ref
    Left Join
        repo.RepoObject                                      As ro
            On
            ro.RepoObject_guid             = ref.referencing_RepoObject_guid

    Left Outer Join
        repo.RepoObject_persistence                          As ro_p
            On
            ro_p.target_RepoObject_guid    = ro.RepoObject_guid

    Left Outer Join
        repo.RepoObject                                      As ro2
            On
            ro2.RepoObject_name            = ro_p.final_target_RepoObject_name
            And ro2.RepoObject_schema_name = ro.RepoObject_schema_name
            And ro2.RepoObject_guid        = ref.referenced_RepoObject_guid
Where
    Not ro2.RepoObject_guid Is Null