uspgenerator.GeneratorUspStep_Persistence_IsInactive_setpoint - V

type: V ( view ), modify_date: 2021-12-16 11:41:19

RepoObject_guid: 7F90291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-uspgenerator.generatoruspstep_persistence_isinactive_setpoint

Columns

Table 1. Columns of uspgenerator.GeneratorUspStep_Persistence_IsInactive_setpoint - V
PK Column Name Data Type NULL? ID

1

int

NOT NULL

int

NOT NULL

int

NOT NULL

uniqueidentifier

NULL

Foreign Key Diagram

entity_1_1_fk-uspgenerator.generatoruspstep_persistence_isinactive_setpoint

References

Object Reference Diagram - 1 1

entity_1_1_objectref-uspgenerator.generatoruspstep_persistence_isinactive_setpoint

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-uspgenerator.generatoruspstep_persistence_isinactive_setpoint

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-uspgenerator.generatoruspstep_persistence_isinactive_setpoint

Column Reference Diagram

entity_1_1_colref-uspgenerator.generatoruspstep_persistence_isinactive_setpoint

Indexes

PK_GeneratorUspStep_Persistence_IsInactive_setpoint

UK_GeneratorUspStep_Persistence_IsInactive_setpoint__2

Column Details

_

usp_id

1

usp_id

int

NOT NULL

is_inactive

is_inactive

int

NOT NULL

Number

Number

int

NOT NULL

source_pk_index_guid

source_pk_index_guid

uniqueidentifier

NULL

sql_modules_definition

uspgenerator.GeneratorUspStep_Persistence_IsInactive_setpoint - V script
CREATE View uspgenerator.GeneratorUspStep_Persistence_IsInactive_setpoint
As
With
ro_u
As
    (
    Select
        usp_id               = u.id
      , u.usp_schema
      , u.usp_name
      , ro.is_persistence_check_duplicate_per_pk
      , ro.is_persistence_check_for_empty_source
      , ro.is_persistence_delete_missing
      , ro.is_persistence_delete_changed
      , ro.is_persistence_insert
      , ro.is_persistence_truncate
      , ro.is_persistence_update_changed
      , ro.is_persistence_persist_source
      , source_pk_index_guid = ro_s.pk_index_guid
    From
        repo.RepoObject_gross         As ro
        Inner Join
            uspgenerator.GeneratorUsp As u
                On
                ro.RepoObject_schema_name   = u.usp_schema
                And ro.usp_persistence_name = u.usp_name

        Left Join
            repo.RepoObject_gross     As ro_s
                On
                ro_s.RepoObject_guid        = ro.persistence_source_RepoObject_guid
    )
Select
    --check for empty source
    ro_u.usp_id
  , Number      = 100
  , is_inactive = Case ro_u.is_persistence_check_for_empty_source
                      When 1
                          Then
                          0
                      Else
                          1
                  End
  , ro_u.source_pk_index_guid -- required only for debugging
From
    ro_u
Union All
Select
    --persist source into #source
    ro_u.usp_id
  , Number      = 200
  , is_inactive = Case ro_u.is_persistence_persist_source
                      When 1
                          Then
                          0
                      Else
                          1
                  End
  , ro_u.source_pk_index_guid -- required only for debugging
From
    ro_u
Union All
Select
    --check duplicate per PK
    --also check existing PK
    ro_u.usp_id
  , Number      = 300
  , is_inactive = Case
                      When ro_u.is_persistence_check_duplicate_per_pk = 1
                           And Not ( ro_u.source_pk_index_guid Is Null )
                          Then
                          0
                      Else
                          1
                  End
  , ro_u.source_pk_index_guid
From
    ro_u
Union All
Select
    --truncate persistence target
    ro_u.usp_id
  , Number      = 400
  , is_inactive = Case ro_u.is_persistence_truncate
                      When 1
                          Then
                          0
                      Else
                          1
                  End
  , ro_u.source_pk_index_guid
From
    ro_u
Union All
Select
    --delete persistence target missing in source
    --also do not delete if truncate, because there is nothing to delete after truncate
    --also check existing PK
    ro_u.usp_id
  , Number      = 500
  , is_inactive = Case
                      When ro_u.is_persistence_truncate = 1
                           Or ro_u.is_persistence_delete_missing = 0
                           Or ro_u.source_pk_index_guid Is Null
                          Then
                          1
                      Else
                          0
                  End
  , ro_u.source_pk_index_guid
From
    ro_u
Union All
Select
    --delete persistence target changed
    --also do not delete if truncate, because there is nothing to delete after truncate
    --also check existing PK
    ro_u.usp_id
  , Number      = 550
  , is_inactive = Case
                      When ro_u.is_persistence_truncate = 1
                           Or ro_u.is_persistence_delete_changed = 0
                           Or ro_u.source_pk_index_guid Is Null
                          Then
                          1
                      Else
                          0
                  End
  , ro_u.source_pk_index_guid
From
    ro_u
Union All
Select
    --update changed
    --also du not update after deleting changed or after truncate, because there is nothing to update
    --also check existing PK
    ro_u.usp_id
  , Number      = 600
  , is_inactive = Case
                      When ro_u.is_persistence_truncate = 1
                           Or ro_u.is_persistence_delete_changed = 1
                           Or ro_u.is_persistence_update_changed = 0
                           Or ro_u.source_pk_index_guid Is Null
                          Then
                          1
                      Else
                          0
                  End
  , ro_u.source_pk_index_guid
From
    ro_u
Union All
Select
    --insert missing
    --don't do this, if 'insert all' (in case of truncate)
    --also check existing PK
    ro_u.usp_id
  , Number      = 700
  , is_inactive = Case
                      When ro_u.is_persistence_insert = 1
                           And Not ro_u.is_persistence_truncate = 1
                           And Not ( ro_u.source_pk_index_guid Is Null )
                          Then
                          0
                      Else
                          1
                  End
  , ro_u.source_pk_index_guid
From
    ro_u
Union All
Select
    --insert all
    --only in combination with truncate
    --possible enhancement: maybe some delete all is required, if truncate is not possible?
    ro_u.usp_id
  , Number      = 800
  , is_inactive = Case
                      When ro_u.is_persistence_truncate = 1
                           And ro_u.is_persistence_insert = 1
                          Then
                          0
                      Else
                          1
                  End
  , ro_u.source_pk_index_guid
From
    ro_u
Union All
Select
    --todo:
    --merge
    ro_u.usp_id
  , Number      = 900
  , is_inactive = 1
  , ro_u.source_pk_index_guid
From
    ro_u