uspgenerator.GeneratorUspStep_Persistence_src - V

type: V ( view ), modify_date: 2022-03-03 22:59:01

RepoObject_guid: 8190291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-uspgenerator.generatoruspstep_persistence_src

Columns

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

1

int

NOT NULL

int

NOT NULL

int

NOT NULL

int

NOT NULL

int

NOT NULL

varchar(1)

NULL

nvarchar(261)

NULL

nvarchar(261)

NULL

varchar(45)

NOT NULL

int

NOT NULL

int

NULL

uniqueidentifier

NOT NULL

nvarchar(max)

NULL

nvarchar(261)

NOT NULL

Foreign Key Diagram

entity_1_1_fk-uspgenerator.generatoruspstep_persistence_src

References

Object Reference Diagram - 1 1

entity_1_1_objectref-uspgenerator.generatoruspstep_persistence_src

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-uspgenerator.generatoruspstep_persistence_src

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-uspgenerator.generatoruspstep_persistence_src

Column Reference Diagram

entity_1_1_colref-uspgenerator.generatoruspstep_persistence_src

Indexes

PK_GeneratorUspStep_Persistence_src

UK_GeneratorUspStep_Persistence_src__2

Column Details

_

usp_id

1

usp_id

int

NOT NULL

has_logging

has_logging

int

NOT NULL

is_condition

is_condition

int

NOT NULL

is_inactive

is_inactive

int

NOT NULL

is_SubProcedure

is_SubProcedure

int

NOT NULL

log_flag_InsertUpdateDelete

log_flag_InsertUpdateDelete

varchar(1)

NULL

log_source_object

log_source_object

nvarchar(261)

NULL

log_target_object

log_target_object

nvarchar(261)

NULL

Name

Name

varchar(45)

NOT NULL

Number

Number

int

NOT NULL

Parent_Number

Parent_Number

int

NULL

RepoObject_guid

RepoObject_guid

uniqueidentifier

NOT NULL

Statement

Statement

nvarchar(max)

NULL

usp_fullname

usp_fullname

nvarchar(261)

NOT NULL

sql_modules_definition

uspgenerator.GeneratorUspStep_Persistence_src - V script
/*
<<property_start>>Description
* xref:sqldb:uspgenerator.generatoruspstep_persistence_src.adoc[] creates all possible steps for GeneratorUspStep
* xref:sqldb:uspgenerator.generatoruspstep_persistence_isinactive_setpoint.adoc[] determines which steps should be activated based on settings in xref:sqldb:repo.repoobject_persistence.adoc[]
* xref:sqldb:uspgenerator.usp_generatorusp_insert_update_persistence.adoc[] uses these views
<<property_end>>
*/

CREATE View [uspgenerator].[GeneratorUspStep_Persistence_src]
As
Select
    --
    usp_id                      = gu.id
  , Number                      = 90
  , Parent_Number               = Null
  , Name                        = 'prescript'
  , has_logging                 = 1
  , is_condition                = 0
  , is_inactive                 = 0
  , is_SubProcedure             = 0
  , Statement                   = ro.prescript
  , log_source_object           = ro.persistence_source_SysObject_fullname
  , log_target_object           = Cast(Null As NVarchar(261))
  , log_flag_InsertUpdateDelete = 'U'
  --
  , gu.usp_fullname
  , ro.RepoObject_guid
From
    repo.RepoObject_gross         As ro
    Inner Join
        uspgenerator.GeneratorUsp As gu
            On
            ro.RepoObject_schema_name   = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
Where
    ro.prescript <> ''
Union All
Select
    --
    usp_id                      = gu.id
  , Number                      = 100
  , Parent_Number               = Null
  , Name                        = 'check for empty source'
  , has_logging                 = 0
  , is_condition                = 1
  , is_inactive                 = 0
  , is_SubProcedure             = 0
  , Statement                   = '(SELECT count(*) FROM ' + ro.persistence_source_SysObject_fullname + ') = 0'
  , log_source_object           = ro.persistence_source_SysObject_fullname
  , log_target_object           = Cast(Null As NVarchar(261))
  , log_flag_InsertUpdateDelete = Cast(Null As Char(1))
  --
  , gu.usp_fullname
  , ro.RepoObject_guid
From
    repo.RepoObject_gross         As ro
    Inner Join
        uspgenerator.GeneratorUsp As gu
            On
            ro.RepoObject_schema_name   = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
Union All
Select
    --
    usp_id                      = gu.id
  , Number                      = 110
  , Parent_Number               = 100
  , Name                        = 'ERROR 50110: persistence source is empty'
  , has_logging                 = 0
  , is_condition                = 0
  , is_inactive                 = 0
  , is_SubProcedure             = 0
  , Statement                   = ' THROW 50110
  , ''persistence source is empty: ' + ro.persistence_source_SysObject_fullname + '''
  , 1;
'
  , log_source_object           = Cast(Null As NVarchar(261))
  , log_target_object           = Cast(Null As NVarchar(261))
  , log_flag_InsertUpdateDelete = Cast(Null As Char(1))
  --
  , gu.usp_fullname
  , ro.RepoObject_guid
From
    repo.RepoObject_gross         As ro
    Inner Join
        uspgenerator.GeneratorUsp As gu
            On
            ro.RepoObject_schema_name   = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
Union All
Select
    --
    usp_id                      = gu.id
  , Number                      = 200
  , Parent_Number               = Null
  , Name                        = 'persist source into #source'
  , has_logging                 = 1
  , is_condition                = 0
  , is_inactive                 = 0
  , is_SubProcedure             = 0
  , Statement                   = '
SELECT
' + ro.PersistenceTempTableColumnList + '
INTO
  #source
FROM ' + ro.persistence_source_SysObject_fullname
  , log_source_object           = ro.persistence_source_SysObject_fullname
  , log_target_object           = '#source'
  , log_flag_InsertUpdateDelete = 'I'
  --
  , gu.usp_fullname
  , ro.RepoObject_guid
From
    repo.RepoObject_gross2        As ro
    Inner Join
        uspgenerator.GeneratorUsp As gu
            On
            ro.RepoObject_schema_name   = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
Union All

-- todo
-- distinguish between real ro.persistence_source_SysObject_fullname or #source
--instead of ro.persistence_source_SysObject_fullname we use
--ro.persistence_source_SysObject_fullname_or_tempsource
--00:01:19
Select
    --will be empty if PK doesn't exist
    usp_id                      = gu.id
  , Number                      = 300
  , Parent_Number               = Null
  , Name                        = 'check duplicate per PK'
  , has_logging                 = 0
  , is_condition                = 1
  , is_inactive                 = 0
  , is_SubProcedure             = 0
  , Statement                   = 'EXISTS(SELECT TOP 1 1 FROM ' + ro.persistence_source_SysObject_fullname_or_tempsource + ' GROUP BY '
                                  + i.ColumnList + ' HAVING COUNT(*) > 1)'
  , log_source_object           = ro.persistence_source_SysObject_fullname_or_tempsource
  , log_target_object           = Cast(Null As NVarchar(261))
  , log_flag_InsertUpdateDelete = Cast(Null As Char(1))
  --
  , gu.usp_fullname
  , ro.RepoObject_guid
From
    repo.RepoObject_gross         As ro
    Inner Join
        uspgenerator.GeneratorUsp As gu
            On
            ro.RepoObject_schema_name   = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
    --INNER JOIN because step 300 should be created only when PK exists in persistence_source

    Inner Join
        repo.RepoObject_gross     As ro_s
            On
            ro_s.RepoObject_guid        = ro.persistence_source_RepoObject_guid

    Inner Join
        repo.Index_ColumList_T    As i
            On
            i.index_guid                = ro_s.pk_index_guid
Union All
--00:01:25
Select
    --will be empty if PK doesn't exist
    usp_id                      = gu.id
  , Number                      = 310
  , Parent_Number               = 300
  , Name                        = 'ERROR 50310: persistence source PK not unique'
  , has_logging                 = 0
  , is_condition                = 0
  , is_inactive                 = 0
  , is_SubProcedure             = 0
  , Statement                   = ' THROW 50310
  , ''persistence source PK not unique: ' + ro.persistence_source_SysObject_fullname_or_tempsource + '; '
                                  + i.ColumnList + '''
  , 1;
'
  , log_source_object           = Cast(Null As NVarchar(261))
  , log_target_object           = Cast(Null As NVarchar(261))
  , log_flag_InsertUpdateDelete = Cast(Null As Char(1))
  --
  , gu.usp_fullname
  , ro.RepoObject_guid
From
    repo.RepoObject_gross         As ro
    Inner Join
        uspgenerator.GeneratorUsp As gu
            On
            ro.RepoObject_schema_name   = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
    --INNER JOIN because step 300 should be created only when PK exists in persistence_source

    Inner Join
        repo.RepoObject_gross     As ro_s
            On
            ro_s.RepoObject_guid        = ro.persistence_source_RepoObject_guid

    Inner Join
        repo.Index_ColumList_T    As i
            On
            i.index_guid                = ro_s.pk_index_guid
Union All
Select
    --
    usp_id                      = gu.id
  , Number                      = 400
  , Parent_Number               = Null
  , Name                        = 'truncate persistence target'
  , has_logging                 = 1
  , is_condition                = 0
  , is_inactive                 = 0
  , is_SubProcedure             = 0
  , Statement                   = 'TRUNCATE TABLE ' + ro.RepoObject_fullname
  , log_source_object           = Cast(Null As NVarchar(261))
  , log_target_object           = ro.RepoObject_fullname
  , log_flag_InsertUpdateDelete = 'D'
  --
  , gu.usp_fullname
  , ro.RepoObject_guid
From
    repo.RepoObject_gross         As ro
    Inner Join
        uspgenerator.GeneratorUsp As gu
            On
            ro.RepoObject_schema_name   = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
Union All
--00:01:19
Select
    --will be empty if PK doesn't exist
    usp_id                      = gu.id
  , Number                      = 500
  , Parent_Number               = Null
  , Name                        = 'delete persistence target missing in source'
  , has_logging                 = 1
  , is_condition                = 0
  , is_inactive                 = 0
  , is_SubProcedure             = 0
  , Statement                   = 'DELETE T
FROM ' + ro.RepoObject_fullname + ' AS T
WHERE
NOT EXISTS
(SELECT 1 FROM ' + ro.persistence_source_SysObject_fullname_or_tempsource + ' AS S
WHERE
' + i.PersistenceWhereColumnList + ')
 '
  , log_source_object           = ro.persistence_source_SysObject_fullname_or_tempsource
  , log_target_object           = ro.RepoObject_fullname
  , log_flag_InsertUpdateDelete = 'D'
  --
  , gu.usp_fullname
  , ro.RepoObject_guid
From
    repo.RepoObject_gross         As ro
    Inner Join
        uspgenerator.GeneratorUsp As gu
            On
            ro.RepoObject_schema_name   = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
    --INNER JOIN because step 500 should be created only when PK exists in persistence_source

    Inner Join
        repo.RepoObject_gross     As ro_s
            On
            ro_s.RepoObject_guid        = ro.persistence_source_RepoObject_guid

    Inner Join
        repo.Index_ColumList_T    As i
            On
            i.index_guid                = ro_s.pk_index_guid
Union All
--00:01:53
Select
    --will be empty if PK doesn't exist
    usp_id                      = gu.id
  , Number                      = 550
  , Parent_Number               = Null
  , Name                        = 'delete persistence target changed'
  , has_logging                 = 1
  , is_condition                = 0
  , is_inactive                 = 0
  , is_SubProcedure             = 0
  , Statement                   = Concat (
                                             'DELETE T
FROM '                                       + ro.RepoObject_fullname + ' AS T
INNER JOIN '
                                             + ro.persistence_source_SysObject_fullname_or_tempsource + ' AS S
ON
'
                                             + i.PersistenceWhereColumnList
                                           --ro.PersistenceCompareColumnList could be empty
                                           , '
WHERE
' + ro.PersistenceCompareColumnList
                                         )
  , log_source_object           = ro.persistence_source_SysObject_fullname_or_tempsource
  , log_target_object           = ro.RepoObject_fullname
  , log_flag_InsertUpdateDelete = 'D'
  --
  , gu.usp_fullname
  , ro.RepoObject_guid
From
    repo.RepoObject_gross2        As ro
    Inner Join
        uspgenerator.GeneratorUsp As gu
            On
            ro.RepoObject_schema_name   = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
    --INNER JOIN because step 500 should be created only when PK exists in persistence_source

    Inner Join
        repo.RepoObject_gross     As ro_s
            On
            ro_s.RepoObject_guid        = ro.persistence_source_RepoObject_guid

    Inner Join
        repo.Index_ColumList_T    As i
            On
            i.index_guid                = ro_s.pk_index_guid
Union All
--00:01:41
Select
    --will be empty if PK doesn't exist
    usp_id                      = gu.id
  , Number                      = 600
  , Parent_Number               = Null
  , Name                        = 'update changed'
  , has_logging                 = 1
  , is_condition                = 0
  , is_inactive                 = 0
  , is_SubProcedure             = 0
  , Statement                   = Concat (
                                             'UPDATE T
SET
'                                            + ro.PersistenceUpdateColumnList + '
FROM '                                                                                    + ro.RepoObject_fullname
                                             + ' AS T
INNER JOIN '
                                             + ro.persistence_source_SysObject_fullname_or_tempsource + ' AS S
ON
'
                                             + i.PersistenceWhereColumnList
                                           --ro.PersistenceCompareColumnList could be empty
                                           , '
WHERE
' + ro.PersistenceCompareColumnList
                                         )
  , log_source_object           = ro.persistence_source_SysObject_fullname_or_tempsource
  , log_target_object           = ro.RepoObject_fullname
  , log_flag_InsertUpdateDelete = 'U'
  --
  , gu.usp_fullname
  , ro.RepoObject_guid
From
    repo.RepoObject_gross2        As ro
    Inner Join
        uspgenerator.GeneratorUsp As gu
            On
            ro.RepoObject_schema_name   = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
    --INNER JOIN because step 500 should be created only when PK exists in persistence_source

    Inner Join
        repo.RepoObject_gross     As ro_s
            On
            ro_s.RepoObject_guid        = ro.persistence_source_RepoObject_guid

    Inner Join
        repo.Index_ColumList_T    As i
            On
            i.index_guid                = ro_s.pk_index_guid
Union All
--00:02:06
Select
    --will be empty if PK doesn't exist
    usp_id                      = gu.id
  , Number                      = 700
  , Parent_Number               = Null
  , Name                        = 'insert missing'
  , has_logging                 = 1
  , is_condition                = 0
  , is_inactive                 = 0
  , is_SubProcedure             = 0
  , Statement                   = 'INSERT INTO
 ' + ro.RepoObject_fullname + '
 (
' + ro.PersistenceInsertColumnList + ')
SELECT
' + ro.PersistenceInsertColumnListSource + '
FROM ' + ro.persistence_source_SysObject_fullname_or_tempsource + ' AS S
WHERE
NOT EXISTS
(SELECT 1
FROM ' + ro.RepoObject_fullname + ' AS T
WHERE
' + i.PersistenceWhereColumnList + ')'
  , log_source_object           = ro.persistence_source_SysObject_fullname_or_tempsource
  , log_target_object           = ro.RepoObject_fullname
  , log_flag_InsertUpdateDelete = 'I'
  --
  , gu.usp_fullname
  , ro.RepoObject_guid
From
    repo.RepoObject_gross2        As ro
    Inner Join
        uspgenerator.GeneratorUsp As gu
            On
            ro.RepoObject_schema_name   = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
    --INNER JOIN because step 500 should be created only when PK exists in persistence_source

    Inner Join
        repo.RepoObject_gross     As ro_s
            On
            ro_s.RepoObject_guid        = ro.persistence_source_RepoObject_guid

    Inner Join
        repo.Index_ColumList_T    As i
            On
            i.index_guid                = ro_s.pk_index_guid
Union All
--00:00:04
Select
    --should be used in combination with truncate
    usp_id                      = gu.id
  , Number                      = 800
  , Parent_Number               = Null
  , Name                        = 'insert all'
  , has_logging                 = 1
  , is_condition                = 0
  , is_inactive                 = 0
  , is_SubProcedure             = 0
  , Statement                   = 'INSERT INTO
 ' + ro.RepoObject_fullname + '
 (
' + ro.PersistenceInsertColumnList + ')
SELECT
' + ro.PersistenceInsertColumnListSource + '
FROM ' + ro.persistence_source_SysObject_fullname_or_tempsource + ' AS S'
  , log_source_object           = ro.persistence_source_SysObject_fullname_or_tempsource
  , log_target_object           = ro.RepoObject_fullname
  , log_flag_InsertUpdateDelete = 'I'
  --
  , gu.usp_fullname
  , ro.RepoObject_guid
From
    repo.RepoObject_gross2        As ro
    Inner Join
        uspgenerator.GeneratorUsp As gu
            On
            ro.RepoObject_schema_name   = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
Union All
Select
    --
    usp_id                      = gu.id
  , Number                      = 990
  , Parent_Number               = Null
  , Name                        = 'postscript'
  , has_logging                 = 1
  , is_condition                = 0
  , is_inactive                 = 0
  , is_SubProcedure             = 0
  , Statement                   = ro.postscript
  , log_source_object           = ro.persistence_source_SysObject_fullname
  , log_target_object           = Cast(Null As NVarchar(261))
  , log_flag_InsertUpdateDelete = 'U'
  --
  , gu.usp_fullname
  , ro.RepoObject_guid
From
    repo.RepoObject_gross         As ro
    Inner Join
        uspgenerator.GeneratorUsp As gu
            On
            ro.RepoObject_schema_name   = gu.usp_schema
            And ro.usp_persistence_name = gu.usp_name
Where
    ro.postscript <> ''