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

        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

Columns

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

int

NOT NULL

varchar(28)

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

References

Object Reference Diagram - 1 1

entity_1_1_objectref-reference.repoobject_reference_persistence_target_as_source

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-reference.repoobject_reference_persistence_target_as_source

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-reference.repoobject_reference_persistence_target_as_source

Column Reference Diagram

entity_1_1_colref-reference.repoobject_reference_persistence_target_as_source

Column Details

_

has_match_left_and_suffix_tgt

has_match_left_and_suffix_tgt

int

NOT NULL

InformationSource

InformationSource

varchar(28)

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 - 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