Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- KNOTS --------------------------------------------------------------------------------------------------------------
- --
- -- Knots are used to store finite sets of values, normally used to describe states
- -- of entities (through knotted attributes) or relationships (through knotted ties).
- -- Knots have their own surrogate identities and are therefore immutable.
- -- Values can be added to the set over time though.
- -- Knots should have values that are mutually exclusive and exhaustive.
- --
- -- Knot table ---------------------------------------------------------------------------------------------------------
- -- PAT_ParentalType table
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('PAT_ParentalType', 'U') IS NULL
- CREATE TABLE [dbo].[PAT_ParentalType] (
- PAT_ID tinyint not null,
- PAT_ParentalType varchar(42) not null,
- Metadata_PAT int not null,
- constraint pkPAT_ParentalType primary key (
- PAT_ID asc
- ),
- constraint uqPAT_ParentalType unique (
- PAT_ParentalType
- )
- );
- GO
- -- Knot table ---------------------------------------------------------------------------------------------------------
- -- PLV_ProfessionalLevel table
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('PLV_ProfessionalLevel', 'U') IS NULL
- CREATE TABLE [dbo].[PLV_ProfessionalLevel] (
- PLV_ID tinyint not null,
- PLV_ProfessionalLevel varchar(42) not null,
- Metadata_PLV int not null,
- constraint pkPLV_ProfessionalLevel primary key (
- PLV_ID asc
- ),
- constraint uqPLV_ProfessionalLevel unique (
- PLV_ProfessionalLevel
- )
- );
- GO
- -- Knot table ---------------------------------------------------------------------------------------------------------
- -- RAT_Rating table
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('RAT_Rating', 'U') IS NULL
- CREATE TABLE [dbo].[RAT_Rating] (
- RAT_ID tinyint not null,
- RAT_Rating varchar(42) not null,
- Metadata_RAT int not null,
- constraint pkRAT_Rating primary key (
- RAT_ID asc
- ),
- constraint uqRAT_Rating unique (
- RAT_Rating
- )
- );
- GO
- -- Knot table ---------------------------------------------------------------------------------------------------------
- -- GEN_Gender table
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('GEN_Gender', 'U') IS NULL
- CREATE TABLE [dbo].[GEN_Gender] (
- GEN_ID bit not null,
- GEN_Gender varchar(42) not null,
- Metadata_GEN int not null,
- constraint pkGEN_Gender primary key (
- GEN_ID asc
- ),
- constraint uqGEN_Gender unique (
- GEN_Gender
- )
- );
- GO
- -- ANCHORS AND ATTRIBUTES ---------------------------------------------------------------------------------------------
- --
- -- Anchors are used to store the identities of entities.
- -- Anchors are immutable.
- -- Attributes are used to store values for properties of entities.
- -- Attributes are mutable, their values may change over one or more types of time.
- -- Attributes have four flavors: static, historized, knotted static, and knotted historized.
- -- Anchors may have zero or more adjoined attributes.
- --
- -- Anchor table -------------------------------------------------------------------------------------------------------
- -- EV_Event table (with 0 attributes)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('EV_Event', 'U') IS NULL
- CREATE TABLE [dbo].[EV_Event] (
- EV_ID int IDENTITY(1,1) not null,
- Metadata_EV int not null,
- constraint pkEV_Event primary key (
- EV_ID asc
- )
- );
- GO
- -- Anchor table -------------------------------------------------------------------------------------------------------
- -- PR_Program table (with 1 attributes)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('PR_Program', 'U') IS NULL
- CREATE TABLE [dbo].[PR_Program] (
- PR_ID int IDENTITY(1,1) not null,
- Metadata_PR int not null,
- constraint pkPR_Program primary key (
- PR_ID asc
- )
- );
- GO
- -- Static attribute table ---------------------------------------------------------------------------------------------
- -- PR_NAM_Program_Name table (on PR_Program)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('PR_NAM_Program_Name', 'U') IS NULL
- CREATE TABLE [dbo].[PR_NAM_Program_Name] (
- PR_NAM_PR_ID int not null,
- PR_NAM_Program_Name varchar(42) not null,
- Metadata_PR_NAM int not null,
- constraint fkPR_NAM_Program_Name foreign key (
- PR_NAM_PR_ID
- ) references [dbo].[PR_Program](PR_ID),
- constraint pkPR_NAM_Program_Name primary key (
- PR_NAM_PR_ID asc
- )
- );
- GO
- -- Anchor table -------------------------------------------------------------------------------------------------------
- -- ST_Stage table (with 2 attributes)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('ST_Stage', 'U') IS NULL
- CREATE TABLE [dbo].[ST_Stage] (
- ST_ID int IDENTITY(1,1) not null,
- Metadata_ST int not null,
- constraint pkST_Stage primary key (
- ST_ID asc
- )
- );
- GO
- -- Historized attribute table -----------------------------------------------------------------------------------------
- -- ST_NAM_Stage_Name table (on ST_Stage)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('ST_NAM_Stage_Name', 'U') IS NULL
- CREATE TABLE [dbo].[ST_NAM_Stage_Name] (
- ST_NAM_ST_ID int not null,
- ST_NAM_Stage_Name varchar(42) not null,
- ST_NAM_ChangedAt datetime not null,
- Metadata_ST_NAM int not null,
- constraint fkST_NAM_Stage_Name foreign key (
- ST_NAM_ST_ID
- ) references [dbo].[ST_Stage](ST_ID),
- constraint pkST_NAM_Stage_Name primary key (
- ST_NAM_ST_ID asc,
- ST_NAM_ChangedAt desc
- )
- );
- GO
- -- Static attribute table ---------------------------------------------------------------------------------------------
- -- ST_LOC_Stage_Location table (on ST_Stage)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('ST_LOC_Stage_Location', 'U') IS NULL
- CREATE TABLE [dbo].[ST_LOC_Stage_Location] (
- ST_LOC_ST_ID int not null,
- ST_LOC_Stage_Location varchar(42) not null,
- Metadata_ST_LOC int not null,
- constraint fkST_LOC_Stage_Location foreign key (
- ST_LOC_ST_ID
- ) references [dbo].[ST_Stage](ST_ID),
- constraint pkST_LOC_Stage_Location primary key (
- ST_LOC_ST_ID asc
- )
- );
- GO
- -- Anchor table -------------------------------------------------------------------------------------------------------
- -- PE_Performance table (with 3 attributes)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('PE_Performance', 'U') IS NULL
- CREATE TABLE [dbo].[PE_Performance] (
- PE_ID int IDENTITY(1,1) not null,
- Metadata_PE int not null,
- constraint pkPE_Performance primary key (
- PE_ID asc
- )
- );
- GO
- -- Static attribute table ---------------------------------------------------------------------------------------------
- -- PE_DAT_Performance_Date table (on PE_Performance)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('PE_DAT_Performance_Date', 'U') IS NULL
- CREATE TABLE [dbo].[PE_DAT_Performance_Date] (
- PE_DAT_PE_ID int not null,
- PE_DAT_Performance_Date datetime not null,
- Metadata_PE_DAT int not null,
- constraint fkPE_DAT_Performance_Date foreign key (
- PE_DAT_PE_ID
- ) references [dbo].[PE_Performance](PE_ID),
- constraint pkPE_DAT_Performance_Date primary key (
- PE_DAT_PE_ID asc
- )
- );
- GO
- -- Static attribute table ---------------------------------------------------------------------------------------------
- -- PE_AUD_Performance_Audience table (on PE_Performance)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('PE_AUD_Performance_Audience', 'U') IS NULL
- CREATE TABLE [dbo].[PE_AUD_Performance_Audience] (
- PE_AUD_PE_ID int not null,
- PE_AUD_Performance_Audience int not null,
- Metadata_PE_AUD int not null,
- constraint fkPE_AUD_Performance_Audience foreign key (
- PE_AUD_PE_ID
- ) references [dbo].[PE_Performance](PE_ID),
- constraint pkPE_AUD_Performance_Audience primary key (
- PE_AUD_PE_ID asc
- )
- );
- GO
- -- Static attribute table ---------------------------------------------------------------------------------------------
- -- PE_REV_Performance_Revenue table (on PE_Performance)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('PE_REV_Performance_Revenue', 'U') IS NULL
- CREATE TABLE [dbo].[PE_REV_Performance_Revenue] (
- PE_REV_PE_ID int not null,
- PE_REV_Performance_Revenue money not null,
- Metadata_PE_REV int not null,
- constraint fkPE_REV_Performance_Revenue foreign key (
- PE_REV_PE_ID
- ) references [dbo].[PE_Performance](PE_ID),
- constraint pkPE_REV_Performance_Revenue primary key (
- PE_REV_PE_ID asc
- )
- );
- GO
- -- Anchor table -------------------------------------------------------------------------------------------------------
- -- AC_Actor table (with 3 attributes)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('AC_Actor', 'U') IS NULL
- CREATE TABLE [dbo].[AC_Actor] (
- AC_ID int IDENTITY(1,1) not null,
- Metadata_AC int not null,
- constraint pkAC_Actor primary key (
- AC_ID asc
- )
- );
- GO
- -- Historized attribute table -----------------------------------------------------------------------------------------
- -- AC_NAM_Actor_Name table (on AC_Actor)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('AC_NAM_Actor_Name', 'U') IS NULL
- CREATE TABLE [dbo].[AC_NAM_Actor_Name] (
- AC_NAM_AC_ID int not null,
- AC_NAM_Actor_Name varchar(42) not null,
- AC_NAM_ChangedAt datetime not null,
- Metadata_AC_NAM int not null,
- constraint fkAC_NAM_Actor_Name foreign key (
- AC_NAM_AC_ID
- ) references [dbo].[AC_Actor](AC_ID),
- constraint pkAC_NAM_Actor_Name primary key (
- AC_NAM_AC_ID asc,
- AC_NAM_ChangedAt desc
- )
- );
- GO
- -- Knotted static attribute table -------------------------------------------------------------------------------------
- -- AC_GEN_Actor_Gender table (on AC_Actor)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('AC_GEN_Actor_Gender', 'U') IS NULL
- CREATE TABLE [dbo].[AC_GEN_Actor_Gender] (
- AC_GEN_AC_ID int not null,
- AC_GEN_GEN_ID bit not null,
- Metadata_AC_GEN int not null,
- constraint fk_A_AC_GEN_Actor_Gender foreign key (
- AC_GEN_AC_ID
- ) references [dbo].[AC_Actor](AC_ID),
- constraint fk_K_AC_GEN_Actor_Gender foreign key (
- AC_GEN_GEN_ID
- ) references [dbo].[GEN_Gender](GEN_ID),
- constraint pkAC_GEN_Actor_Gender primary key (
- AC_GEN_AC_ID asc
- )
- );
- GO
- -- Knotted historized attribute table ---------------------------------------------------------------------------------
- -- AC_PLV_Actor_ProfessionalLevel table (on AC_Actor)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('AC_PLV_Actor_ProfessionalLevel', 'U') IS NULL
- CREATE TABLE [dbo].[AC_PLV_Actor_ProfessionalLevel] (
- AC_PLV_AC_ID int not null,
- AC_PLV_PLV_ID tinyint not null,
- AC_PLV_ChangedAt datetime not null,
- Metadata_AC_PLV int not null,
- constraint fk_A_AC_PLV_Actor_ProfessionalLevel foreign key (
- AC_PLV_AC_ID
- ) references [dbo].[AC_Actor](AC_ID),
- constraint fk_K_AC_PLV_Actor_ProfessionalLevel foreign key (
- AC_PLV_PLV_ID
- ) references [dbo].[PLV_ProfessionalLevel](PLV_ID),
- constraint pkAC_PLV_Actor_ProfessionalLevel primary key (
- AC_PLV_AC_ID asc,
- AC_PLV_ChangedAt desc
- )
- );
- GO
- -- TIES ---------------------------------------------------------------------------------------------------------------
- --
- -- Ties are used to represent relationships between entities.
- -- They come in four flavors: static, historized, knotted static, and knotted historized.
- -- Ties have cardinality, constraining how members may participate in the relationship.
- -- Every entity that is a member in a tie has a specified role in the relationship.
- -- Ties must have at least two anchor roles and zero or more knot roles.
- --
- -- Knotted static tie table -------------------------------------------------------------------------------------------
- -- AC_parent_AC_child_PAT_having table (having 3 roles)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('AC_parent_AC_child_PAT_having', 'U') IS NULL
- CREATE TABLE [dbo].[AC_parent_AC_child_PAT_having] (
- AC_ID_parent int not null,
- AC_ID_child int not null,
- PAT_ID_having tinyint not null,
- Metadata_AC_parent_AC_child_PAT_having int not null,
- constraint AC_parent_AC_child_PAT_having_fkAC_parent foreign key (
- AC_ID_parent
- ) references AC_Actor(AC_ID),
- constraint AC_parent_AC_child_PAT_having_fkAC_child foreign key (
- AC_ID_child
- ) references AC_Actor(AC_ID),
- constraint AC_parent_AC_child_PAT_having_fkPAT_having foreign key (
- PAT_ID_having
- ) references PAT_ParentalType(PAT_ID),
- constraint pkAC_parent_AC_child_PAT_having primary key (
- AC_ID_parent asc,
- AC_ID_child asc,
- PAT_ID_having asc
- )
- );
- GO
- -- Historized tie table -----------------------------------------------------------------------------------------------
- -- ST_atLocation_PR_isPlaying table (having 2 roles)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('ST_atLocation_PR_isPlaying', 'U') IS NULL
- CREATE TABLE [dbo].[ST_atLocation_PR_isPlaying] (
- ST_ID_atLocation int not null,
- PR_ID_isPlaying int not null,
- ST_atLocation_PR_isPlaying_ChangedAt datetime not null,
- Metadata_ST_atLocation_PR_isPlaying int not null,
- constraint ST_atLocation_PR_isPlaying_fkST_atLocation foreign key (
- ST_ID_atLocation
- ) references ST_Stage(ST_ID),
- constraint ST_atLocation_PR_isPlaying_fkPR_isPlaying foreign key (
- PR_ID_isPlaying
- ) references PR_Program(PR_ID),
- constraint pkST_atLocation_PR_isPlaying primary key (
- ST_ID_atLocation asc,
- PR_ID_isPlaying asc,
- ST_atLocation_PR_isPlaying_ChangedAt desc
- )
- );
- GO
- -- Static tie table ---------------------------------------------------------------------------------------------------
- -- PE_at_PR_wasPlayed table (having 2 roles)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('PE_at_PR_wasPlayed', 'U') IS NULL
- CREATE TABLE [dbo].[PE_at_PR_wasPlayed] (
- PE_ID_at int not null,
- PR_ID_wasPlayed int not null,
- Metadata_PE_at_PR_wasPlayed int not null,
- constraint PE_at_PR_wasPlayed_fkPE_at foreign key (
- PE_ID_at
- ) references PE_Performance(PE_ID),
- constraint PE_at_PR_wasPlayed_fkPR_wasPlayed foreign key (
- PR_ID_wasPlayed
- ) references PR_Program(PR_ID),
- constraint pkPE_at_PR_wasPlayed primary key (
- PE_ID_at asc
- )
- );
- GO
- -- Static tie table ---------------------------------------------------------------------------------------------------
- -- PE_wasHeld_ST_atLocation table (having 2 roles)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('PE_wasHeld_ST_atLocation', 'U') IS NULL
- CREATE TABLE [dbo].[PE_wasHeld_ST_atLocation] (
- PE_ID_wasHeld int not null,
- ST_ID_atLocation int not null,
- Metadata_PE_wasHeld_ST_atLocation int not null,
- constraint PE_wasHeld_ST_atLocation_fkPE_wasHeld foreign key (
- PE_ID_wasHeld
- ) references PE_Performance(PE_ID),
- constraint PE_wasHeld_ST_atLocation_fkST_atLocation foreign key (
- ST_ID_atLocation
- ) references ST_Stage(ST_ID),
- constraint pkPE_wasHeld_ST_atLocation primary key (
- PE_ID_wasHeld asc
- )
- );
- GO
- -- Historized tie table -----------------------------------------------------------------------------------------------
- -- AC_exclusive_AC_with table (having 2 roles)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('AC_exclusive_AC_with', 'U') IS NULL
- CREATE TABLE [dbo].[AC_exclusive_AC_with] (
- AC_ID_exclusive int not null,
- AC_ID_with int not null,
- AC_exclusive_AC_with_ChangedAt datetime not null,
- Metadata_AC_exclusive_AC_with int not null,
- constraint AC_exclusive_AC_with_fkAC_exclusive foreign key (
- AC_ID_exclusive
- ) references AC_Actor(AC_ID),
- constraint AC_exclusive_AC_with_fkAC_with foreign key (
- AC_ID_with
- ) references AC_Actor(AC_ID),
- constraint AC_exclusive_AC_with_uqAC_exclusive unique (
- AC_ID_exclusive,
- AC_exclusive_AC_with_ChangedAt
- ),
- constraint AC_exclusive_AC_with_uqAC_with unique (
- AC_ID_with,
- AC_exclusive_AC_with_ChangedAt
- ),
- constraint pkAC_exclusive_AC_with primary key (
- AC_ID_exclusive asc,
- AC_ID_with asc,
- AC_exclusive_AC_with_ChangedAt desc
- )
- );
- GO
- -- Static tie table ---------------------------------------------------------------------------------------------------
- -- PE_subset_EV_of table (having 2 roles)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('PE_subset_EV_of', 'U') IS NULL
- CREATE TABLE [dbo].[PE_subset_EV_of] (
- PE_ID_subset int not null,
- EV_ID_of int not null,
- Metadata_PE_subset_EV_of int not null,
- constraint PE_subset_EV_of_fkPE_subset foreign key (
- PE_ID_subset
- ) references PE_Performance(PE_ID),
- constraint PE_subset_EV_of_fkEV_of foreign key (
- EV_ID_of
- ) references EV_Event(EV_ID),
- constraint PE_subset_EV_of_uqPE_subset unique (
- PE_ID_subset
- ),
- constraint PE_subset_EV_of_uqEV_of unique (
- EV_ID_of
- ),
- constraint pkPE_subset_EV_of primary key (
- PE_ID_subset asc,
- EV_ID_of asc
- )
- );
- GO
- -- Static tie table ---------------------------------------------------------------------------------------------------
- -- PE_in_AC_wasCast table (having 2 roles)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('PE_in_AC_wasCast', 'U') IS NULL
- CREATE TABLE [dbo].[PE_in_AC_wasCast] (
- PE_ID_in int not null,
- AC_ID_wasCast int not null,
- Metadata_PE_in_AC_wasCast int not null,
- constraint PE_in_AC_wasCast_fkPE_in foreign key (
- PE_ID_in
- ) references PE_Performance(PE_ID),
- constraint PE_in_AC_wasCast_fkAC_wasCast foreign key (
- AC_ID_wasCast
- ) references AC_Actor(AC_ID),
- constraint pkPE_in_AC_wasCast primary key (
- PE_ID_in asc,
- AC_ID_wasCast asc
- )
- );
- GO
- -- Knotted historized tie table ---------------------------------------------------------------------------------------
- -- AC_part_PR_in_RAT_got table (having 3 roles)
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('AC_part_PR_in_RAT_got', 'U') IS NULL
- CREATE TABLE [dbo].[AC_part_PR_in_RAT_got] (
- AC_ID_part int not null,
- PR_ID_in int not null,
- RAT_ID_got tinyint not null,
- AC_part_PR_in_RAT_got_ChangedAt datetime not null,
- Metadata_AC_part_PR_in_RAT_got int not null,
- constraint AC_part_PR_in_RAT_got_fkAC_part foreign key (
- AC_ID_part
- ) references AC_Actor(AC_ID),
- constraint AC_part_PR_in_RAT_got_fkPR_in foreign key (
- PR_ID_in
- ) references PR_Program(PR_ID),
- constraint AC_part_PR_in_RAT_got_fkRAT_got foreign key (
- RAT_ID_got
- ) references RAT_Rating(RAT_ID),
- constraint pkAC_part_PR_in_RAT_got primary key (
- AC_ID_part asc,
- PR_ID_in asc,
- AC_part_PR_in_RAT_got_ChangedAt desc
- )
- );
- GO
- -- ATTRIBUTE RESTATEMENT CONSTRAINTS ----------------------------------------------------------------------------------
- --
- -- Attributes may be prevented from storing restatements.
- -- A restatement is when the same value occurs for two adjacent points
- -- in changing time.
- --
- -- returns 1 for at least one equal surrounding value, 0 for different surrounding values
- --
- -- @id the identity of the anchored entity
- -- @value the value of the attribute
- -- @changed the point in time from which this value shall represent a change
- --
- -- Restatement Finder Function and Constraint -------------------------------------------------------------------------
- -- rfST_NAM_Stage_Name restatement finder, also used by the insert and update triggers for idempotent attributes
- -- rcST_NAM_Stage_Name restatement constraint, with checking made by the finder function
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('rfST_NAM_Stage_Name', 'FN') IS NULL
- BEGIN
- EXEC('
- CREATE FUNCTION [dbo].[rfST_NAM_Stage_Name] (
- @id int,
- @value varchar(42),
- @changed datetime
- )
- RETURNS tinyint AS
- BEGIN RETURN (
- CASE WHEN @value IN ((
- SELECT TOP 1
- pre.ST_NAM_Stage_Name
- FROM
- [dbo].[ST_NAM_Stage_Name] pre
- WHERE
- pre.ST_NAM_ST_ID = @id
- AND
- pre.ST_NAM_ChangedAt < @changed
- ORDER BY
- pre.ST_NAM_ChangedAt DESC
- ),(
- SELECT TOP 1
- fol.ST_NAM_Stage_Name
- FROM
- [dbo].[ST_NAM_Stage_Name] fol
- WHERE
- fol.ST_NAM_ST_ID = @id
- AND
- fol.ST_NAM_ChangedAt > @changed
- ORDER BY
- fol.ST_NAM_ChangedAt ASC
- ))
- THEN 1
- ELSE 0
- END
- );
- END
- ');
- ALTER TABLE [dbo].[ST_NAM_Stage_Name]
- ADD CONSTRAINT [rcST_NAM_Stage_Name] CHECK (
- [dbo].[rfST_NAM_Stage_Name] (
- ST_NAM_ST_ID,
- ST_NAM_Stage_Name,
- ST_NAM_ChangedAt
- ) = 0
- );
- END
- GO
- -- Restatement Finder Function and Constraint -------------------------------------------------------------------------
- -- rfAC_NAM_Actor_Name restatement finder, also used by the insert and update triggers for idempotent attributes
- -- rcAC_NAM_Actor_Name restatement constraint, with checking made by the finder function
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('rfAC_NAM_Actor_Name', 'FN') IS NULL
- BEGIN
- EXEC('
- CREATE FUNCTION [dbo].[rfAC_NAM_Actor_Name] (
- @id int,
- @value varchar(42),
- @changed datetime
- )
- RETURNS tinyint AS
- BEGIN RETURN (
- CASE WHEN @value IN ((
- SELECT TOP 1
- pre.AC_NAM_Actor_Name
- FROM
- [dbo].[AC_NAM_Actor_Name] pre
- WHERE
- pre.AC_NAM_AC_ID = @id
- AND
- pre.AC_NAM_ChangedAt < @changed
- ORDER BY
- pre.AC_NAM_ChangedAt DESC
- ),(
- SELECT TOP 1
- fol.AC_NAM_Actor_Name
- FROM
- [dbo].[AC_NAM_Actor_Name] fol
- WHERE
- fol.AC_NAM_AC_ID = @id
- AND
- fol.AC_NAM_ChangedAt > @changed
- ORDER BY
- fol.AC_NAM_ChangedAt ASC
- ))
- THEN 1
- ELSE 0
- END
- );
- END
- ');
- ALTER TABLE [dbo].[AC_NAM_Actor_Name]
- ADD CONSTRAINT [rcAC_NAM_Actor_Name] CHECK (
- [dbo].[rfAC_NAM_Actor_Name] (
- AC_NAM_AC_ID,
- AC_NAM_Actor_Name,
- AC_NAM_ChangedAt
- ) = 0
- );
- END
- GO
- -- Restatement Finder Function and Constraint -------------------------------------------------------------------------
- -- rfAC_PLV_Actor_ProfessionalLevel restatement finder, also used by the insert and update triggers for idempotent attributes
- -- rcAC_PLV_Actor_ProfessionalLevel restatement constraint, with checking made by the finder function
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('rfAC_PLV_Actor_ProfessionalLevel', 'FN') IS NULL
- BEGIN
- EXEC('
- CREATE FUNCTION [dbo].[rfAC_PLV_Actor_ProfessionalLevel] (
- @id int,
- @value tinyint,
- @changed datetime
- )
- RETURNS tinyint AS
- BEGIN RETURN (
- CASE WHEN @value IN ((
- SELECT TOP 1
- pre.AC_PLV_PLV_ID
- FROM
- [dbo].[AC_PLV_Actor_ProfessionalLevel] pre
- WHERE
- pre.AC_PLV_AC_ID = @id
- AND
- pre.AC_PLV_ChangedAt < @changed
- ORDER BY
- pre.AC_PLV_ChangedAt DESC
- ),(
- SELECT TOP 1
- fol.AC_PLV_PLV_ID
- FROM
- [dbo].[AC_PLV_Actor_ProfessionalLevel] fol
- WHERE
- fol.AC_PLV_AC_ID = @id
- AND
- fol.AC_PLV_ChangedAt > @changed
- ORDER BY
- fol.AC_PLV_ChangedAt ASC
- ))
- THEN 1
- ELSE 0
- END
- );
- END
- ');
- ALTER TABLE [dbo].[AC_PLV_Actor_ProfessionalLevel]
- ADD CONSTRAINT [rcAC_PLV_Actor_ProfessionalLevel] CHECK (
- [dbo].[rfAC_PLV_Actor_ProfessionalLevel] (
- AC_PLV_AC_ID,
- AC_PLV_PLV_ID,
- AC_PLV_ChangedAt
- ) = 0
- );
- END
- GO
- -- KEY GENERATORS -----------------------------------------------------------------------------------------------------
- --
- -- These stored procedures can be used to generate identities of entities.
- -- Corresponding anchors must have an incrementing identity column.
- --
- -- Key Generation Stored Procedure ------------------------------------------------------------------------------------
- -- kEV_Event identity by surrogate key generation stored procedure
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('kEV_Event', 'P') IS NULL
- BEGIN
- EXEC('
- CREATE PROCEDURE [dbo].[kEV_Event] (
- @requestedNumberOfIdentities bigint,
- @metadata int
- ) AS
- BEGIN
- SET NOCOUNT ON;
- IF @requestedNumberOfIdentities > 0
- BEGIN
- WITH idGenerator (idNumber) AS (
- SELECT
- 1
- UNION ALL
- SELECT
- idNumber + 1
- FROM
- idGenerator
- WHERE
- idNumber < @requestedNumberOfIdentities
- )
- INSERT INTO [dbo].[EV_Event] (
- Metadata_EV
- )
- OUTPUT
- inserted.EV_ID
- SELECT
- @metadata
- FROM
- idGenerator
- OPTION (maxrecursion 0);
- END
- END
- ');
- END
- GO
- -- Key Generation Stored Procedure ------------------------------------------------------------------------------------
- -- kPR_Program identity by surrogate key generation stored procedure
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('kPR_Program', 'P') IS NULL
- BEGIN
- EXEC('
- CREATE PROCEDURE [dbo].[kPR_Program] (
- @requestedNumberOfIdentities bigint,
- @metadata int
- ) AS
- BEGIN
- SET NOCOUNT ON;
- IF @requestedNumberOfIdentities > 0
- BEGIN
- WITH idGenerator (idNumber) AS (
- SELECT
- 1
- UNION ALL
- SELECT
- idNumber + 1
- FROM
- idGenerator
- WHERE
- idNumber < @requestedNumberOfIdentities
- )
- INSERT INTO [dbo].[PR_Program] (
- Metadata_PR
- )
- OUTPUT
- inserted.PR_ID
- SELECT
- @metadata
- FROM
- idGenerator
- OPTION (maxrecursion 0);
- END
- END
- ');
- END
- GO
- -- Key Generation Stored Procedure ------------------------------------------------------------------------------------
- -- kST_Stage identity by surrogate key generation stored procedure
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('kST_Stage', 'P') IS NULL
- BEGIN
- EXEC('
- CREATE PROCEDURE [dbo].[kST_Stage] (
- @requestedNumberOfIdentities bigint,
- @metadata int
- ) AS
- BEGIN
- SET NOCOUNT ON;
- IF @requestedNumberOfIdentities > 0
- BEGIN
- WITH idGenerator (idNumber) AS (
- SELECT
- 1
- UNION ALL
- SELECT
- idNumber + 1
- FROM
- idGenerator
- WHERE
- idNumber < @requestedNumberOfIdentities
- )
- INSERT INTO [dbo].[ST_Stage] (
- Metadata_ST
- )
- OUTPUT
- inserted.ST_ID
- SELECT
- @metadata
- FROM
- idGenerator
- OPTION (maxrecursion 0);
- END
- END
- ');
- END
- GO
- -- Key Generation Stored Procedure ------------------------------------------------------------------------------------
- -- kPE_Performance identity by surrogate key generation stored procedure
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('kPE_Performance', 'P') IS NULL
- BEGIN
- EXEC('
- CREATE PROCEDURE [dbo].[kPE_Performance] (
- @requestedNumberOfIdentities bigint,
- @metadata int
- ) AS
- BEGIN
- SET NOCOUNT ON;
- IF @requestedNumberOfIdentities > 0
- BEGIN
- WITH idGenerator (idNumber) AS (
- SELECT
- 1
- UNION ALL
- SELECT
- idNumber + 1
- FROM
- idGenerator
- WHERE
- idNumber < @requestedNumberOfIdentities
- )
- INSERT INTO [dbo].[PE_Performance] (
- Metadata_PE
- )
- OUTPUT
- inserted.PE_ID
- SELECT
- @metadata
- FROM
- idGenerator
- OPTION (maxrecursion 0);
- END
- END
- ');
- END
- GO
- -- Key Generation Stored Procedure ------------------------------------------------------------------------------------
- -- kAC_Actor identity by surrogate key generation stored procedure
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('kAC_Actor', 'P') IS NULL
- BEGIN
- EXEC('
- CREATE PROCEDURE [dbo].[kAC_Actor] (
- @requestedNumberOfIdentities bigint,
- @metadata int
- ) AS
- BEGIN
- SET NOCOUNT ON;
- IF @requestedNumberOfIdentities > 0
- BEGIN
- WITH idGenerator (idNumber) AS (
- SELECT
- 1
- UNION ALL
- SELECT
- idNumber + 1
- FROM
- idGenerator
- WHERE
- idNumber < @requestedNumberOfIdentities
- )
- INSERT INTO [dbo].[AC_Actor] (
- Metadata_AC
- )
- OUTPUT
- inserted.AC_ID
- SELECT
- @metadata
- FROM
- idGenerator
- OPTION (maxrecursion 0);
- END
- END
- ');
- END
- GO
- -- ATTRIBUTE REWINDERS ------------------------------------------------------------------------------------------------
- --
- -- These table valued functions rewind an attribute table to the given
- -- point in changing time. It does not pick a temporal perspective and
- -- instead shows all rows that have been in effect before that point
- -- in time.
- --
- -- @changingTimepoint the point in changing time to rewind to
- --
- -- Attribute rewinder -------------------------------------------------------------------------------------------------
- -- rST_NAM_Stage_Name rewinding over changing time function
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('rST_NAM_Stage_Name','IF') IS NULL
- BEGIN
- EXEC('
- CREATE FUNCTION [dbo].[rST_NAM_Stage_Name] (
- @changingTimepoint datetime
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- Metadata_ST_NAM,
- ST_NAM_ST_ID,
- ST_NAM_Stage_Name,
- ST_NAM_ChangedAt
- FROM
- [dbo].[ST_NAM_Stage_Name]
- WHERE
- ST_NAM_ChangedAt <= @changingTimepoint;
- ');
- END
- GO
- -- Attribute rewinder -------------------------------------------------------------------------------------------------
- -- rAC_NAM_Actor_Name rewinding over changing time function
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('rAC_NAM_Actor_Name','IF') IS NULL
- BEGIN
- EXEC('
- CREATE FUNCTION [dbo].[rAC_NAM_Actor_Name] (
- @changingTimepoint datetime
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- Metadata_AC_NAM,
- AC_NAM_AC_ID,
- AC_NAM_Actor_Name,
- AC_NAM_ChangedAt
- FROM
- [dbo].[AC_NAM_Actor_Name]
- WHERE
- AC_NAM_ChangedAt <= @changingTimepoint;
- ');
- END
- GO
- -- Attribute rewinder -------------------------------------------------------------------------------------------------
- -- rAC_PLV_Actor_ProfessionalLevel rewinding over changing time function
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('rAC_PLV_Actor_ProfessionalLevel','IF') IS NULL
- BEGIN
- EXEC('
- CREATE FUNCTION [dbo].[rAC_PLV_Actor_ProfessionalLevel] (
- @changingTimepoint datetime
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- Metadata_AC_PLV,
- AC_PLV_AC_ID,
- AC_PLV_PLV_ID,
- AC_PLV_ChangedAt
- FROM
- [dbo].[AC_PLV_Actor_ProfessionalLevel]
- WHERE
- AC_PLV_ChangedAt <= @changingTimepoint;
- ');
- END
- GO
- -- ANCHOR TEMPORAL PERSPECTIVES ---------------------------------------------------------------------------------------
- --
- -- These table valued functions simplify temporal querying by providing a temporal
- -- perspective of each anchor. There are four types of perspectives: latest,
- -- point-in-time, difference, and now. They also denormalize the anchor, its attributes,
- -- and referenced knots from sixth to third normal form.
- --
- -- The latest perspective shows the latest available information for each anchor.
- -- The now perspective shows the information as it is right now.
- -- The point-in-time perspective lets you travel through the information to the given timepoint.
- --
- -- @changingTimepoint the point in changing time to travel to
- --
- -- The difference perspective shows changes between the two given timepoints, and for
- -- changes in all or a selection of attributes.
- --
- -- @intervalStart the start of the interval for finding changes
- -- @intervalEnd the end of the interval for finding changes
- -- @selection a list of mnemonics for tracked attributes, ie 'MNE MON ICS', or null for all
- --
- -- Drop perspectives --------------------------------------------------------------------------------------------------
- IF Object_ID('dEV_Event', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[dEV_Event];
- IF Object_ID('nEV_Event', 'V') IS NOT NULL
- DROP VIEW [dbo].[nEV_Event];
- IF Object_ID('pEV_Event', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[pEV_Event];
- IF Object_ID('lEV_Event', 'V') IS NOT NULL
- DROP VIEW [dbo].[lEV_Event];
- GO
- -- Drop perspectives --------------------------------------------------------------------------------------------------
- IF Object_ID('dPR_Program', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[dPR_Program];
- IF Object_ID('nPR_Program', 'V') IS NOT NULL
- DROP VIEW [dbo].[nPR_Program];
- IF Object_ID('pPR_Program', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[pPR_Program];
- IF Object_ID('lPR_Program', 'V') IS NOT NULL
- DROP VIEW [dbo].[lPR_Program];
- GO
- -- Latest perspective -------------------------------------------------------------------------------------------------
- -- lPR_Program viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[lPR_Program] WITH SCHEMABINDING AS
- SELECT
- [PR].PR_ID,
- [PR].Metadata_PR,
- [NAM].PR_NAM_PR_ID,
- [NAM].Metadata_PR_NAM,
- [NAM].PR_NAM_Program_Name
- FROM
- [dbo].[PR_Program] [PR]
- LEFT JOIN
- [dbo].[PR_NAM_Program_Name] [NAM]
- ON
- [NAM].PR_NAM_PR_ID = [PR].PR_ID;
- GO
- -- Point-in-time perspective ------------------------------------------------------------------------------------------
- -- pPR_Program viewed as it was on the given timepoint
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[pPR_Program] (
- @changingTimepoint datetime2(7)
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- [PR].PR_ID,
- [PR].Metadata_PR,
- [NAM].PR_NAM_PR_ID,
- [NAM].Metadata_PR_NAM,
- [NAM].PR_NAM_Program_Name
- FROM
- [dbo].[PR_Program] [PR]
- LEFT JOIN
- [dbo].[PR_NAM_Program_Name] [NAM]
- ON
- [NAM].PR_NAM_PR_ID = [PR].PR_ID;
- GO
- -- Now perspective ----------------------------------------------------------------------------------------------------
- -- nPR_Program viewed as it currently is (cannot include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[nPR_Program]
- AS
- SELECT
- *
- FROM
- [dbo].[pPR_Program](sysdatetime());
- GO
- -- Drop perspectives --------------------------------------------------------------------------------------------------
- IF Object_ID('dST_Stage', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[dST_Stage];
- IF Object_ID('nST_Stage', 'V') IS NOT NULL
- DROP VIEW [dbo].[nST_Stage];
- IF Object_ID('pST_Stage', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[pST_Stage];
- IF Object_ID('lST_Stage', 'V') IS NOT NULL
- DROP VIEW [dbo].[lST_Stage];
- GO
- -- Latest perspective -------------------------------------------------------------------------------------------------
- -- lST_Stage viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[lST_Stage] WITH SCHEMABINDING AS
- SELECT
- [ST].ST_ID,
- [ST].Metadata_ST,
- [NAM].ST_NAM_ST_ID,
- [NAM].Metadata_ST_NAM,
- [NAM].ST_NAM_ChangedAt,
- [NAM].ST_NAM_Stage_Name,
- [LOC].ST_LOC_ST_ID,
- [LOC].Metadata_ST_LOC,
- [LOC].ST_LOC_Stage_Location
- FROM
- [dbo].[ST_Stage] [ST]
- LEFT JOIN
- [dbo].[ST_NAM_Stage_Name] [NAM]
- ON
- [NAM].ST_NAM_ST_ID = [ST].ST_ID
- AND
- [NAM].ST_NAM_ChangedAt = (
- SELECT
- max(sub.ST_NAM_ChangedAt)
- FROM
- [dbo].[ST_NAM_Stage_Name] sub
- WHERE
- sub.ST_NAM_ST_ID = [ST].ST_ID
- )
- LEFT JOIN
- [dbo].[ST_LOC_Stage_Location] [LOC]
- ON
- [LOC].ST_LOC_ST_ID = [ST].ST_ID;
- GO
- -- Point-in-time perspective ------------------------------------------------------------------------------------------
- -- pST_Stage viewed as it was on the given timepoint
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[pST_Stage] (
- @changingTimepoint datetime2(7)
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- [ST].ST_ID,
- [ST].Metadata_ST,
- [NAM].ST_NAM_ST_ID,
- [NAM].Metadata_ST_NAM,
- [NAM].ST_NAM_ChangedAt,
- [NAM].ST_NAM_Stage_Name,
- [LOC].ST_LOC_ST_ID,
- [LOC].Metadata_ST_LOC,
- [LOC].ST_LOC_Stage_Location
- FROM
- [dbo].[ST_Stage] [ST]
- LEFT JOIN
- [dbo].[rST_NAM_Stage_Name](@changingTimepoint) [NAM]
- ON
- [NAM].ST_NAM_ST_ID = [ST].ST_ID
- AND
- [NAM].ST_NAM_ChangedAt = (
- SELECT
- max(sub.ST_NAM_ChangedAt)
- FROM
- [dbo].[rST_NAM_Stage_Name](@changingTimepoint) sub
- WHERE
- sub.ST_NAM_ST_ID = [ST].ST_ID
- )
- LEFT JOIN
- [dbo].[ST_LOC_Stage_Location] [LOC]
- ON
- [LOC].ST_LOC_ST_ID = [ST].ST_ID;
- GO
- -- Now perspective ----------------------------------------------------------------------------------------------------
- -- nST_Stage viewed as it currently is (cannot include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[nST_Stage]
- AS
- SELECT
- *
- FROM
- [dbo].[pST_Stage](sysdatetime());
- GO
- -- Difference perspective ---------------------------------------------------------------------------------------------
- -- dST_Stage showing all differences between the given timepoints and optionally for a subset of attributes
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[dST_Stage] (
- @intervalStart datetime2(7),
- @intervalEnd datetime2(7),
- @selection varchar(max) = null
- )
- RETURNS TABLE AS RETURN
- SELECT
- timepoints.inspectedTimepoint,
- [pST].*
- FROM (
- SELECT DISTINCT
- ST_NAM_ChangedAt AS inspectedTimepoint
- FROM
- [dbo].[ST_NAM_Stage_Name]
- WHERE
- (@selection is null OR @selection like '%NAM%')
- AND
- ST_NAM_ChangedAt BETWEEN @intervalStart AND @intervalEnd
- ) timepoints
- CROSS APPLY
- [dbo].[pST_Stage](timepoints.inspectedTimepoint) [pST];
- GO
- -- Drop perspectives --------------------------------------------------------------------------------------------------
- IF Object_ID('dPE_Performance', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[dPE_Performance];
- IF Object_ID('nPE_Performance', 'V') IS NOT NULL
- DROP VIEW [dbo].[nPE_Performance];
- IF Object_ID('pPE_Performance', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[pPE_Performance];
- IF Object_ID('lPE_Performance', 'V') IS NOT NULL
- DROP VIEW [dbo].[lPE_Performance];
- GO
- -- Latest perspective -------------------------------------------------------------------------------------------------
- -- lPE_Performance viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[lPE_Performance] WITH SCHEMABINDING AS
- SELECT
- [PE].PE_ID,
- [PE].Metadata_PE,
- [DAT].PE_DAT_PE_ID,
- [DAT].Metadata_PE_DAT,
- [DAT].PE_DAT_Performance_Date,
- [AUD].PE_AUD_PE_ID,
- [AUD].Metadata_PE_AUD,
- [AUD].PE_AUD_Performance_Audience,
- [REV].PE_REV_PE_ID,
- [REV].Metadata_PE_REV,
- [REV].PE_REV_Performance_Revenue
- FROM
- [dbo].[PE_Performance] [PE]
- LEFT JOIN
- [dbo].[PE_DAT_Performance_Date] [DAT]
- ON
- [DAT].PE_DAT_PE_ID = [PE].PE_ID
- LEFT JOIN
- [dbo].[PE_AUD_Performance_Audience] [AUD]
- ON
- [AUD].PE_AUD_PE_ID = [PE].PE_ID
- LEFT JOIN
- [dbo].[PE_REV_Performance_Revenue] [REV]
- ON
- [REV].PE_REV_PE_ID = [PE].PE_ID;
- GO
- -- Point-in-time perspective ------------------------------------------------------------------------------------------
- -- pPE_Performance viewed as it was on the given timepoint
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[pPE_Performance] (
- @changingTimepoint datetime2(7)
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- [PE].PE_ID,
- [PE].Metadata_PE,
- [DAT].PE_DAT_PE_ID,
- [DAT].Metadata_PE_DAT,
- [DAT].PE_DAT_Performance_Date,
- [AUD].PE_AUD_PE_ID,
- [AUD].Metadata_PE_AUD,
- [AUD].PE_AUD_Performance_Audience,
- [REV].PE_REV_PE_ID,
- [REV].Metadata_PE_REV,
- [REV].PE_REV_Performance_Revenue
- FROM
- [dbo].[PE_Performance] [PE]
- LEFT JOIN
- [dbo].[PE_DAT_Performance_Date] [DAT]
- ON
- [DAT].PE_DAT_PE_ID = [PE].PE_ID
- LEFT JOIN
- [dbo].[PE_AUD_Performance_Audience] [AUD]
- ON
- [AUD].PE_AUD_PE_ID = [PE].PE_ID
- LEFT JOIN
- [dbo].[PE_REV_Performance_Revenue] [REV]
- ON
- [REV].PE_REV_PE_ID = [PE].PE_ID;
- GO
- -- Now perspective ----------------------------------------------------------------------------------------------------
- -- nPE_Performance viewed as it currently is (cannot include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[nPE_Performance]
- AS
- SELECT
- *
- FROM
- [dbo].[pPE_Performance](sysdatetime());
- GO
- -- Drop perspectives --------------------------------------------------------------------------------------------------
- IF Object_ID('dAC_Actor', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[dAC_Actor];
- IF Object_ID('nAC_Actor', 'V') IS NOT NULL
- DROP VIEW [dbo].[nAC_Actor];
- IF Object_ID('pAC_Actor', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[pAC_Actor];
- IF Object_ID('lAC_Actor', 'V') IS NOT NULL
- DROP VIEW [dbo].[lAC_Actor];
- GO
- -- Latest perspective -------------------------------------------------------------------------------------------------
- -- lAC_Actor viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[lAC_Actor] WITH SCHEMABINDING AS
- SELECT
- [AC].AC_ID,
- [AC].Metadata_AC,
- [NAM].AC_NAM_AC_ID,
- [NAM].Metadata_AC_NAM,
- [NAM].AC_NAM_ChangedAt,
- [NAM].AC_NAM_Actor_Name,
- [GEN].AC_GEN_AC_ID,
- [GEN].Metadata_AC_GEN,
- [kGEN].GEN_Gender AS AC_GEN_GEN_Gender,
- [kGEN].Metadata_GEN AS AC_GEN_Metadata_GEN,
- [GEN].AC_GEN_GEN_ID,
- [PLV].AC_PLV_AC_ID,
- [PLV].Metadata_AC_PLV,
- [PLV].AC_PLV_ChangedAt,
- [kPLV].PLV_ProfessionalLevel AS AC_PLV_PLV_ProfessionalLevel,
- [kPLV].Metadata_PLV AS AC_PLV_Metadata_PLV,
- [PLV].AC_PLV_PLV_ID
- FROM
- [dbo].[AC_Actor] [AC]
- LEFT JOIN
- [dbo].[AC_NAM_Actor_Name] [NAM]
- ON
- [NAM].AC_NAM_AC_ID = [AC].AC_ID
- AND
- [NAM].AC_NAM_ChangedAt = (
- SELECT
- max(sub.AC_NAM_ChangedAt)
- FROM
- [dbo].[AC_NAM_Actor_Name] sub
- WHERE
- sub.AC_NAM_AC_ID = [AC].AC_ID
- )
- LEFT JOIN
- [dbo].[AC_GEN_Actor_Gender] [GEN]
- ON
- [GEN].AC_GEN_AC_ID = [AC].AC_ID
- LEFT JOIN
- [dbo].[GEN_Gender] [kGEN]
- ON
- [kGEN].GEN_ID = [GEN].AC_GEN_GEN_ID
- LEFT JOIN
- [dbo].[AC_PLV_Actor_ProfessionalLevel] [PLV]
- ON
- [PLV].AC_PLV_AC_ID = [AC].AC_ID
- AND
- [PLV].AC_PLV_ChangedAt = (
- SELECT
- max(sub.AC_PLV_ChangedAt)
- FROM
- [dbo].[AC_PLV_Actor_ProfessionalLevel] sub
- WHERE
- sub.AC_PLV_AC_ID = [AC].AC_ID
- )
- LEFT JOIN
- [dbo].[PLV_ProfessionalLevel] [kPLV]
- ON
- [kPLV].PLV_ID = [PLV].AC_PLV_PLV_ID;
- GO
- -- Point-in-time perspective ------------------------------------------------------------------------------------------
- -- pAC_Actor viewed as it was on the given timepoint
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[pAC_Actor] (
- @changingTimepoint datetime2(7)
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- [AC].AC_ID,
- [AC].Metadata_AC,
- [NAM].AC_NAM_AC_ID,
- [NAM].Metadata_AC_NAM,
- [NAM].AC_NAM_ChangedAt,
- [NAM].AC_NAM_Actor_Name,
- [GEN].AC_GEN_AC_ID,
- [GEN].Metadata_AC_GEN,
- [kGEN].GEN_Gender AS AC_GEN_GEN_Gender,
- [kGEN].Metadata_GEN AS AC_GEN_Metadata_GEN,
- [GEN].AC_GEN_GEN_ID,
- [PLV].AC_PLV_AC_ID,
- [PLV].Metadata_AC_PLV,
- [PLV].AC_PLV_ChangedAt,
- [kPLV].PLV_ProfessionalLevel AS AC_PLV_PLV_ProfessionalLevel,
- [kPLV].Metadata_PLV AS AC_PLV_Metadata_PLV,
- [PLV].AC_PLV_PLV_ID
- FROM
- [dbo].[AC_Actor] [AC]
- LEFT JOIN
- [dbo].[rAC_NAM_Actor_Name](@changingTimepoint) [NAM]
- ON
- [NAM].AC_NAM_AC_ID = [AC].AC_ID
- AND
- [NAM].AC_NAM_ChangedAt = (
- SELECT
- max(sub.AC_NAM_ChangedAt)
- FROM
- [dbo].[rAC_NAM_Actor_Name](@changingTimepoint) sub
- WHERE
- sub.AC_NAM_AC_ID = [AC].AC_ID
- )
- LEFT JOIN
- [dbo].[AC_GEN_Actor_Gender] [GEN]
- ON
- [GEN].AC_GEN_AC_ID = [AC].AC_ID
- LEFT JOIN
- [dbo].[GEN_Gender] [kGEN]
- ON
- [kGEN].GEN_ID = [GEN].AC_GEN_GEN_ID
- LEFT JOIN
- [dbo].[rAC_PLV_Actor_ProfessionalLevel](@changingTimepoint) [PLV]
- ON
- [PLV].AC_PLV_AC_ID = [AC].AC_ID
- AND
- [PLV].AC_PLV_ChangedAt = (
- SELECT
- max(sub.AC_PLV_ChangedAt)
- FROM
- [dbo].[rAC_PLV_Actor_ProfessionalLevel](@changingTimepoint) sub
- WHERE
- sub.AC_PLV_AC_ID = [AC].AC_ID
- )
- LEFT JOIN
- [dbo].[PLV_ProfessionalLevel] [kPLV]
- ON
- [kPLV].PLV_ID = [PLV].AC_PLV_PLV_ID;
- GO
- -- Now perspective ----------------------------------------------------------------------------------------------------
- -- nAC_Actor viewed as it currently is (cannot include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[nAC_Actor]
- AS
- SELECT
- *
- FROM
- [dbo].[pAC_Actor](sysdatetime());
- GO
- -- Difference perspective ---------------------------------------------------------------------------------------------
- -- dAC_Actor showing all differences between the given timepoints and optionally for a subset of attributes
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[dAC_Actor] (
- @intervalStart datetime2(7),
- @intervalEnd datetime2(7),
- @selection varchar(max) = null
- )
- RETURNS TABLE AS RETURN
- SELECT
- timepoints.inspectedTimepoint,
- [pAC].*
- FROM (
- SELECT DISTINCT
- AC_NAM_ChangedAt AS inspectedTimepoint
- FROM
- [dbo].[AC_NAM_Actor_Name]
- WHERE
- (@selection is null OR @selection like '%NAM%')
- AND
- AC_NAM_ChangedAt BETWEEN @intervalStart AND @intervalEnd
- UNION
- SELECT DISTINCT
- AC_PLV_ChangedAt AS inspectedTimepoint
- FROM
- [dbo].[AC_PLV_Actor_ProfessionalLevel]
- WHERE
- (@selection is null OR @selection like '%PLV%')
- AND
- AC_PLV_ChangedAt BETWEEN @intervalStart AND @intervalEnd
- ) timepoints
- CROSS APPLY
- [dbo].[pAC_Actor](timepoints.inspectedTimepoint) [pAC];
- GO
- -- ANCHOR TRIGGERS ---------------------------------------------------------------------------------------------------
- --
- -- The following triggers on the latest view make it behave like a table.
- -- There are three different 'instead of' triggers: insert, update, and delete.
- -- They will ensure that such operations are propagated to the underlying tables
- -- in a consistent way. Default values are used for some columns if not provided
- -- by the corresponding SQL statements.
- --
- -- For idempotent attributes, only changes that represent a value different from
- -- the previous or following value are stored. Others are silently ignored in
- -- order to avoid unnecessary temporal duplicates.
- --
- -- Insert trigger -----------------------------------------------------------------------------------------------------
- -- itPR_Program instead of INSERT trigger on lPR_Program
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[itPR_Program] ON [dbo].[lPR_Program]
- INSTEAD OF INSERT
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- DECLARE @maxVersion int;
- DECLARE @currentVersion int;
- DECLARE @PR TABLE (
- Row bigint IDENTITY(1,1) not null primary key,
- PR_ID int not null
- );
- INSERT INTO [dbo].[PR_Program] (
- Metadata_PR
- )
- OUTPUT
- inserted.PR_ID
- INTO
- @PR
- SELECT
- Metadata_PR
- FROM
- inserted
- WHERE
- inserted.PR_ID is null;
- DECLARE @inserted TABLE (
- PR_ID int not null,
- Metadata_PR int not null,
- PR_NAM_PR_ID int null,
- Metadata_PR_NAM int null,
- PR_NAM_Program_Name varchar(42) null
- );
- INSERT INTO @inserted
- SELECT
- ISNULL(i.PR_ID, a.PR_ID),
- i.Metadata_PR,
- ISNULL(ISNULL(i.PR_NAM_PR_ID, i.PR_ID), a.PR_ID),
- ISNULL(i.Metadata_PR_NAM, i.Metadata_PR),
- i.PR_NAM_Program_Name
- FROM (
- SELECT
- PR_ID,
- Metadata_PR,
- PR_NAM_PR_ID,
- Metadata_PR_NAM,
- PR_NAM_Program_Name,
- ROW_NUMBER() OVER (PARTITION BY PR_ID ORDER BY PR_ID) AS Row
- FROM
- inserted
- ) i
- LEFT JOIN
- @PR a
- ON
- a.Row = i.Row;
- INSERT INTO [dbo].[PR_NAM_Program_Name] (
- PR_NAM_PR_ID,
- Metadata_PR_NAM,
- PR_NAM_Program_Name
- )
- SELECT
- i.PR_NAM_PR_ID,
- i.Metadata_PR_NAM,
- i.PR_NAM_Program_Name
- FROM
- @inserted i
- LEFT JOIN
- [dbo].[PR_NAM_Program_Name] [NAM]
- ON
- [NAM].PR_NAM_PR_ID = i.PR_NAM_PR_ID
- WHERE
- [NAM].PR_NAM_PR_ID is null
- AND
- i.PR_NAM_Program_Name is not null;
- END
- GO
- -- Insert trigger -----------------------------------------------------------------------------------------------------
- -- itST_Stage instead of INSERT trigger on lST_Stage
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[itST_Stage] ON [dbo].[lST_Stage]
- INSTEAD OF INSERT
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- DECLARE @maxVersion int;
- DECLARE @currentVersion int;
- DECLARE @ST TABLE (
- Row bigint IDENTITY(1,1) not null primary key,
- ST_ID int not null
- );
- INSERT INTO [dbo].[ST_Stage] (
- Metadata_ST
- )
- OUTPUT
- inserted.ST_ID
- INTO
- @ST
- SELECT
- Metadata_ST
- FROM
- inserted
- WHERE
- inserted.ST_ID is null;
- DECLARE @inserted TABLE (
- ST_ID int not null,
- Metadata_ST int not null,
- ST_NAM_ST_ID int null,
- Metadata_ST_NAM int null,
- ST_NAM_ChangedAt datetime null,
- ST_NAM_Stage_Name varchar(42) null,
- ST_LOC_ST_ID int null,
- Metadata_ST_LOC int null,
- ST_LOC_Stage_Location varchar(42) null
- );
- INSERT INTO @inserted
- SELECT
- ISNULL(i.ST_ID, a.ST_ID),
- i.Metadata_ST,
- ISNULL(ISNULL(i.ST_NAM_ST_ID, i.ST_ID), a.ST_ID),
- ISNULL(i.Metadata_ST_NAM, i.Metadata_ST),
- ISNULL(i.ST_NAM_ChangedAt, @now),
- i.ST_NAM_Stage_Name,
- ISNULL(ISNULL(i.ST_LOC_ST_ID, i.ST_ID), a.ST_ID),
- ISNULL(i.Metadata_ST_LOC, i.Metadata_ST),
- i.ST_LOC_Stage_Location
- FROM (
- SELECT
- ST_ID,
- Metadata_ST,
- ST_NAM_ST_ID,
- Metadata_ST_NAM,
- ST_NAM_ChangedAt,
- ST_NAM_Stage_Name,
- ST_LOC_ST_ID,
- Metadata_ST_LOC,
- ST_LOC_Stage_Location,
- ROW_NUMBER() OVER (PARTITION BY ST_ID ORDER BY ST_ID) AS Row
- FROM
- inserted
- ) i
- LEFT JOIN
- @ST a
- ON
- a.Row = i.Row;
- DECLARE @ST_NAM_Stage_Name TABLE (
- ST_NAM_ST_ID int not null,
- Metadata_ST_NAM int not null,
- ST_NAM_ChangedAt datetime not null,
- ST_NAM_Stage_Name varchar(42) not null,
- ST_NAM_Version bigint not null,
- ST_NAM_StatementType char(1) not null,
- primary key(
- ST_NAM_Version,
- ST_NAM_ST_ID
- )
- );
- INSERT INTO @ST_NAM_Stage_Name
- SELECT
- i.ST_NAM_ST_ID,
- i.Metadata_ST_NAM,
- i.ST_NAM_ChangedAt,
- i.ST_NAM_Stage_Name,
- DENSE_RANK() OVER (PARTITION BY i.ST_NAM_ST_ID ORDER BY i.ST_NAM_ChangedAt),
- 'X'
- FROM
- @inserted i
- WHERE
- i.ST_NAM_Stage_Name is not null;
- SELECT
- @maxVersion = max(ST_NAM_Version),
- @currentVersion = 0
- FROM
- @ST_NAM_Stage_Name;
- WHILE (@currentVersion < @maxVersion)
- BEGIN
- SET @currentVersion = @currentVersion + 1;
- UPDATE v
- SET
- v.ST_NAM_StatementType =
- CASE
- WHEN [NAM].ST_NAM_ST_ID is not null
- THEN 'D' -- duplicate
- WHEN [dbo].[rfST_NAM_Stage_Name](
- v.ST_NAM_ST_ID,
- v.ST_NAM_Stage_Name,
- v.ST_NAM_ChangedAt
- ) = 1
- THEN 'R' -- restatement
- ELSE 'N' -- new statement
- END
- FROM
- @ST_NAM_Stage_Name v
- LEFT JOIN
- [dbo].[ST_NAM_Stage_Name] [NAM]
- ON
- [NAM].ST_NAM_ST_ID = v.ST_NAM_ST_ID
- AND
- [NAM].ST_NAM_ChangedAt = v.ST_NAM_ChangedAt
- AND
- [NAM].ST_NAM_Stage_Name = v.ST_NAM_Stage_Name
- WHERE
- v.ST_NAM_Version = @currentVersion;
- INSERT INTO [dbo].[ST_NAM_Stage_Name] (
- ST_NAM_ST_ID,
- Metadata_ST_NAM,
- ST_NAM_ChangedAt,
- ST_NAM_Stage_Name
- )
- SELECT
- ST_NAM_ST_ID,
- Metadata_ST_NAM,
- ST_NAM_ChangedAt,
- ST_NAM_Stage_Name
- FROM
- @ST_NAM_Stage_Name
- WHERE
- ST_NAM_Version = @currentVersion
- AND
- ST_NAM_StatementType in ('N');
- END
- INSERT INTO [dbo].[ST_LOC_Stage_Location] (
- ST_LOC_ST_ID,
- Metadata_ST_LOC,
- ST_LOC_Stage_Location
- )
- SELECT
- i.ST_LOC_ST_ID,
- i.Metadata_ST_LOC,
- i.ST_LOC_Stage_Location
- FROM
- @inserted i
- LEFT JOIN
- [dbo].[ST_LOC_Stage_Location] [LOC]
- ON
- [LOC].ST_LOC_ST_ID = i.ST_LOC_ST_ID
- WHERE
- [LOC].ST_LOC_ST_ID is null
- AND
- i.ST_LOC_Stage_Location is not null;
- END
- GO
- -- UPDATE trigger -----------------------------------------------------------------------------------------------------
- -- utST_Stage instead of UPDATE trigger on lST_Stage
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[utST_Stage] ON [dbo].[lST_Stage]
- INSTEAD OF UPDATE
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- IF(UPDATE(ST_ID))
- RAISERROR('The identity column ST_ID is not updatable.', 16, 1);
- IF(UPDATE(ST_NAM_Stage_Name))
- INSERT INTO [dbo].[ST_NAM_Stage_Name] (
- ST_NAM_ST_ID,
- Metadata_ST_NAM,
- ST_NAM_ChangedAt,
- ST_NAM_Stage_Name
- )
- SELECT
- i.ST_NAM_ST_ID,
- CASE WHEN UPDATE(Metadata_ST_NAM) THEN i.Metadata_ST_NAM ELSE 0 END,
- CASE WHEN UPDATE(ST_NAM_ChangedAt) THEN i.ST_NAM_ChangedAt ELSE @now END,
- i.ST_NAM_Stage_Name
- FROM
- inserted i
- LEFT JOIN
- [dbo].[ST_NAM_Stage_Name] b
- ON
- b.ST_NAM_ST_ID = i.ST_NAM_ST_ID
- AND
- b.ST_NAM_Stage_Name = i.ST_NAM_Stage_Name
- AND
- b.ST_NAM_ChangedAt = i.ST_NAM_ChangedAt
- WHERE
- b.ST_NAM_ST_ID is null
- AND
- [dbo].[rfST_NAM_Stage_Name](
- i.ST_NAM_ST_ID,
- i.ST_NAM_Stage_Name,
- i.ST_NAM_ChangedAt
- ) = 0;
- END
- GO
- -- DELETE trigger -----------------------------------------------------------------------------------------------------
- -- dtST_Stage instead of DELETE trigger on lST_Stage
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[dtST_Stage] ON [dbo].[lST_Stage]
- INSTEAD OF DELETE
- AS
- BEGIN
- SET NOCOUNT ON;
- DELETE [NAM]
- FROM
- [dbo].[ST_NAM_Stage_Name] [NAM]
- JOIN
- deleted d
- ON
- d.ST_NAM_ST_ID = [NAM].ST_NAM_ST_ID
- AND
- d.ST_NAM_ChangedAt = [NAM].ST_NAM_ChangedAt;
- DELETE [LOC]
- FROM
- [dbo].[ST_LOC_Stage_Location] [LOC]
- JOIN
- deleted d
- ON
- d.ST_LOC_ST_ID = [LOC].ST_LOC_ST_ID
- DELETE [ST]
- FROM
- [dbo].[ST_Stage] [ST]
- LEFT JOIN
- [dbo].[ST_NAM_Stage_Name] [NAM]
- ON
- [NAM].ST_NAM_ST_ID = [ST].ST_ID
- LEFT JOIN
- [dbo].[ST_LOC_Stage_Location] [LOC]
- ON
- [LOC].ST_LOC_ST_ID = [ST].ST_ID
- WHERE
- [NAM].ST_NAM_ST_ID is null
- AND
- [LOC].ST_LOC_ST_ID is null;
- END
- GO
- -- Insert trigger -----------------------------------------------------------------------------------------------------
- -- itPE_Performance instead of INSERT trigger on lPE_Performance
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[itPE_Performance] ON [dbo].[lPE_Performance]
- INSTEAD OF INSERT
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- DECLARE @maxVersion int;
- DECLARE @currentVersion int;
- DECLARE @PE TABLE (
- Row bigint IDENTITY(1,1) not null primary key,
- PE_ID int not null
- );
- INSERT INTO [dbo].[PE_Performance] (
- Metadata_PE
- )
- OUTPUT
- inserted.PE_ID
- INTO
- @PE
- SELECT
- Metadata_PE
- FROM
- inserted
- WHERE
- inserted.PE_ID is null;
- DECLARE @inserted TABLE (
- PE_ID int not null,
- Metadata_PE int not null,
- PE_DAT_PE_ID int null,
- Metadata_PE_DAT int null,
- PE_DAT_Performance_Date datetime null,
- PE_AUD_PE_ID int null,
- Metadata_PE_AUD int null,
- PE_AUD_Performance_Audience int null,
- PE_REV_PE_ID int null,
- Metadata_PE_REV int null,
- PE_REV_Performance_Revenue money null
- );
- INSERT INTO @inserted
- SELECT
- ISNULL(i.PE_ID, a.PE_ID),
- i.Metadata_PE,
- ISNULL(ISNULL(i.PE_DAT_PE_ID, i.PE_ID), a.PE_ID),
- ISNULL(i.Metadata_PE_DAT, i.Metadata_PE),
- i.PE_DAT_Performance_Date,
- ISNULL(ISNULL(i.PE_AUD_PE_ID, i.PE_ID), a.PE_ID),
- ISNULL(i.Metadata_PE_AUD, i.Metadata_PE),
- i.PE_AUD_Performance_Audience,
- ISNULL(ISNULL(i.PE_REV_PE_ID, i.PE_ID), a.PE_ID),
- ISNULL(i.Metadata_PE_REV, i.Metadata_PE),
- i.PE_REV_Performance_Revenue
- FROM (
- SELECT
- PE_ID,
- Metadata_PE,
- PE_DAT_PE_ID,
- Metadata_PE_DAT,
- PE_DAT_Performance_Date,
- PE_AUD_PE_ID,
- Metadata_PE_AUD,
- PE_AUD_Performance_Audience,
- PE_REV_PE_ID,
- Metadata_PE_REV,
- PE_REV_Performance_Revenue,
- ROW_NUMBER() OVER (PARTITION BY PE_ID ORDER BY PE_ID) AS Row
- FROM
- inserted
- ) i
- LEFT JOIN
- @PE a
- ON
- a.Row = i.Row;
- INSERT INTO [dbo].[PE_DAT_Performance_Date] (
- PE_DAT_PE_ID,
- Metadata_PE_DAT,
- PE_DAT_Performance_Date
- )
- SELECT
- i.PE_DAT_PE_ID,
- i.Metadata_PE_DAT,
- i.PE_DAT_Performance_Date
- FROM
- @inserted i
- LEFT JOIN
- [dbo].[PE_DAT_Performance_Date] [DAT]
- ON
- [DAT].PE_DAT_PE_ID = i.PE_DAT_PE_ID
- WHERE
- [DAT].PE_DAT_PE_ID is null
- AND
- i.PE_DAT_Performance_Date is not null;
- INSERT INTO [dbo].[PE_AUD_Performance_Audience] (
- PE_AUD_PE_ID,
- Metadata_PE_AUD,
- PE_AUD_Performance_Audience
- )
- SELECT
- i.PE_AUD_PE_ID,
- i.Metadata_PE_AUD,
- i.PE_AUD_Performance_Audience
- FROM
- @inserted i
- LEFT JOIN
- [dbo].[PE_AUD_Performance_Audience] [AUD]
- ON
- [AUD].PE_AUD_PE_ID = i.PE_AUD_PE_ID
- WHERE
- [AUD].PE_AUD_PE_ID is null
- AND
- i.PE_AUD_Performance_Audience is not null;
- INSERT INTO [dbo].[PE_REV_Performance_Revenue] (
- PE_REV_PE_ID,
- Metadata_PE_REV,
- PE_REV_Performance_Revenue
- )
- SELECT
- i.PE_REV_PE_ID,
- i.Metadata_PE_REV,
- i.PE_REV_Performance_Revenue
- FROM
- @inserted i
- LEFT JOIN
- [dbo].[PE_REV_Performance_Revenue] [REV]
- ON
- [REV].PE_REV_PE_ID = i.PE_REV_PE_ID
- WHERE
- [REV].PE_REV_PE_ID is null
- AND
- i.PE_REV_Performance_Revenue is not null;
- END
- GO
- -- Insert trigger -----------------------------------------------------------------------------------------------------
- -- itAC_Actor instead of INSERT trigger on lAC_Actor
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[itAC_Actor] ON [dbo].[lAC_Actor]
- INSTEAD OF INSERT
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- DECLARE @maxVersion int;
- DECLARE @currentVersion int;
- DECLARE @AC TABLE (
- Row bigint IDENTITY(1,1) not null primary key,
- AC_ID int not null
- );
- INSERT INTO [dbo].[AC_Actor] (
- Metadata_AC
- )
- OUTPUT
- inserted.AC_ID
- INTO
- @AC
- SELECT
- Metadata_AC
- FROM
- inserted
- WHERE
- inserted.AC_ID is null;
- DECLARE @inserted TABLE (
- AC_ID int not null,
- Metadata_AC int not null,
- AC_NAM_AC_ID int null,
- Metadata_AC_NAM int null,
- AC_NAM_ChangedAt datetime null,
- AC_NAM_Actor_Name varchar(42) null,
- AC_GEN_AC_ID int null,
- Metadata_AC_GEN int null,
- AC_GEN_GEN_Gender varchar(42) null,
- AC_GEN_Metadata_GEN int null,
- AC_GEN_GEN_ID bit null,
- AC_PLV_AC_ID int null,
- Metadata_AC_PLV int null,
- AC_PLV_ChangedAt datetime null,
- AC_PLV_PLV_ProfessionalLevel varchar(42) null,
- AC_PLV_Metadata_PLV int null,
- AC_PLV_PLV_ID tinyint null
- );
- INSERT INTO @inserted
- SELECT
- ISNULL(i.AC_ID, a.AC_ID),
- i.Metadata_AC,
- ISNULL(ISNULL(i.AC_NAM_AC_ID, i.AC_ID), a.AC_ID),
- ISNULL(i.Metadata_AC_NAM, i.Metadata_AC),
- ISNULL(i.AC_NAM_ChangedAt, @now),
- i.AC_NAM_Actor_Name,
- ISNULL(ISNULL(i.AC_GEN_AC_ID, i.AC_ID), a.AC_ID),
- ISNULL(i.Metadata_AC_GEN, i.Metadata_AC),
- i.AC_GEN_GEN_Gender,
- ISNULL(i.AC_GEN_Metadata_GEN, i.Metadata_AC),
- i.AC_GEN_GEN_ID,
- ISNULL(ISNULL(i.AC_PLV_AC_ID, i.AC_ID), a.AC_ID),
- ISNULL(i.Metadata_AC_PLV, i.Metadata_AC),
- ISNULL(i.AC_PLV_ChangedAt, @now),
- i.AC_PLV_PLV_ProfessionalLevel,
- ISNULL(i.AC_PLV_Metadata_PLV, i.Metadata_AC),
- i.AC_PLV_PLV_ID
- FROM (
- SELECT
- AC_ID,
- Metadata_AC,
- AC_NAM_AC_ID,
- Metadata_AC_NAM,
- AC_NAM_ChangedAt,
- AC_NAM_Actor_Name,
- AC_GEN_AC_ID,
- Metadata_AC_GEN,
- AC_GEN_GEN_Gender,
- AC_GEN_Metadata_GEN,
- AC_GEN_GEN_ID,
- AC_PLV_AC_ID,
- Metadata_AC_PLV,
- AC_PLV_ChangedAt,
- AC_PLV_PLV_ProfessionalLevel,
- AC_PLV_Metadata_PLV,
- AC_PLV_PLV_ID,
- ROW_NUMBER() OVER (PARTITION BY AC_ID ORDER BY AC_ID) AS Row
- FROM
- inserted
- ) i
- LEFT JOIN
- @AC a
- ON
- a.Row = i.Row;
- DECLARE @AC_NAM_Actor_Name TABLE (
- AC_NAM_AC_ID int not null,
- Metadata_AC_NAM int not null,
- AC_NAM_ChangedAt datetime not null,
- AC_NAM_Actor_Name varchar(42) not null,
- AC_NAM_Version bigint not null,
- AC_NAM_StatementType char(1) not null,
- primary key(
- AC_NAM_Version,
- AC_NAM_AC_ID
- )
- );
- INSERT INTO @AC_NAM_Actor_Name
- SELECT
- i.AC_NAM_AC_ID,
- i.Metadata_AC_NAM,
- i.AC_NAM_ChangedAt,
- i.AC_NAM_Actor_Name,
- DENSE_RANK() OVER (PARTITION BY i.AC_NAM_AC_ID ORDER BY i.AC_NAM_ChangedAt),
- 'X'
- FROM
- @inserted i
- WHERE
- i.AC_NAM_Actor_Name is not null;
- SELECT
- @maxVersion = max(AC_NAM_Version),
- @currentVersion = 0
- FROM
- @AC_NAM_Actor_Name;
- WHILE (@currentVersion < @maxVersion)
- BEGIN
- SET @currentVersion = @currentVersion + 1;
- UPDATE v
- SET
- v.AC_NAM_StatementType =
- CASE
- WHEN [NAM].AC_NAM_AC_ID is not null
- THEN 'D' -- duplicate
- WHEN [dbo].[rfAC_NAM_Actor_Name](
- v.AC_NAM_AC_ID,
- v.AC_NAM_Actor_Name,
- v.AC_NAM_ChangedAt
- ) = 1
- THEN 'R' -- restatement
- ELSE 'N' -- new statement
- END
- FROM
- @AC_NAM_Actor_Name v
- LEFT JOIN
- [dbo].[AC_NAM_Actor_Name] [NAM]
- ON
- [NAM].AC_NAM_AC_ID = v.AC_NAM_AC_ID
- AND
- [NAM].AC_NAM_ChangedAt = v.AC_NAM_ChangedAt
- AND
- [NAM].AC_NAM_Actor_Name = v.AC_NAM_Actor_Name
- WHERE
- v.AC_NAM_Version = @currentVersion;
- INSERT INTO [dbo].[AC_NAM_Actor_Name] (
- AC_NAM_AC_ID,
- Metadata_AC_NAM,
- AC_NAM_ChangedAt,
- AC_NAM_Actor_Name
- )
- SELECT
- AC_NAM_AC_ID,
- Metadata_AC_NAM,
- AC_NAM_ChangedAt,
- AC_NAM_Actor_Name
- FROM
- @AC_NAM_Actor_Name
- WHERE
- AC_NAM_Version = @currentVersion
- AND
- AC_NAM_StatementType in ('N');
- END
- INSERT INTO [dbo].[AC_GEN_Actor_Gender] (
- AC_GEN_AC_ID,
- Metadata_AC_GEN,
- AC_GEN_GEN_ID
- )
- SELECT
- i.AC_GEN_AC_ID,
- i.Metadata_AC_GEN,
- ISNULL(i.AC_GEN_GEN_ID, [kGEN].GEN_ID)
- FROM
- @inserted i
- LEFT JOIN
- [dbo].[AC_GEN_Actor_Gender] [GEN]
- ON
- [GEN].AC_GEN_AC_ID = i.AC_GEN_AC_ID
- LEFT JOIN
- [dbo].[GEN_Gender] [kGEN]
- ON
- [kGEN].GEN_Gender = i.AC_GEN_GEN_Gender
- WHERE
- ISNULL(i.AC_GEN_GEN_ID, [kGEN].GEN_ID) is not null
- AND
- [GEN].AC_GEN_AC_ID is null
- AND
- ISNULL(i.AC_GEN_GEN_ID, [kGEN].GEN_ID) is not null;
- DECLARE @AC_PLV_Actor_ProfessionalLevel TABLE (
- AC_PLV_AC_ID int not null,
- Metadata_AC_PLV int not null,
- AC_PLV_ChangedAt datetime not null,
- AC_PLV_PLV_ID tinyint not null,
- AC_PLV_Version bigint not null,
- AC_PLV_StatementType char(1) not null,
- primary key(
- AC_PLV_Version,
- AC_PLV_AC_ID
- )
- );
- INSERT INTO @AC_PLV_Actor_ProfessionalLevel
- SELECT
- i.AC_PLV_AC_ID,
- i.Metadata_AC_PLV,
- i.AC_PLV_ChangedAt,
- ISNULL(i.AC_PLV_PLV_ID, [kPLV].PLV_ID),
- DENSE_RANK() OVER (PARTITION BY i.AC_PLV_AC_ID ORDER BY i.AC_PLV_ChangedAt),
- 'X'
- FROM
- @inserted i
- LEFT JOIN
- [dbo].[PLV_ProfessionalLevel] [kPLV]
- ON
- [kPLV].PLV_ProfessionalLevel = i.AC_PLV_PLV_ProfessionalLevel
- WHERE
- ISNULL(i.AC_PLV_PLV_ID, [kPLV].PLV_ID) is not null;
- SELECT
- @maxVersion = max(AC_PLV_Version),
- @currentVersion = 0
- FROM
- @AC_PLV_Actor_ProfessionalLevel;
- WHILE (@currentVersion < @maxVersion)
- BEGIN
- SET @currentVersion = @currentVersion + 1;
- UPDATE v
- SET
- v.AC_PLV_StatementType =
- CASE
- WHEN [PLV].AC_PLV_AC_ID is not null
- THEN 'D' -- duplicate
- WHEN [dbo].[rfAC_PLV_Actor_ProfessionalLevel](
- v.AC_PLV_AC_ID,
- v.AC_PLV_PLV_ID,
- v.AC_PLV_ChangedAt
- ) = 1
- THEN 'R' -- restatement
- ELSE 'N' -- new statement
- END
- FROM
- @AC_PLV_Actor_ProfessionalLevel v
- LEFT JOIN
- [dbo].[AC_PLV_Actor_ProfessionalLevel] [PLV]
- ON
- [PLV].AC_PLV_AC_ID = v.AC_PLV_AC_ID
- AND
- [PLV].AC_PLV_ChangedAt = v.AC_PLV_ChangedAt
- AND
- [PLV].AC_PLV_PLV_ID = v.AC_PLV_PLV_ID
- WHERE
- v.AC_PLV_Version = @currentVersion;
- INSERT INTO [dbo].[AC_PLV_Actor_ProfessionalLevel] (
- AC_PLV_AC_ID,
- Metadata_AC_PLV,
- AC_PLV_ChangedAt,
- AC_PLV_PLV_ID
- )
- SELECT
- AC_PLV_AC_ID,
- Metadata_AC_PLV,
- AC_PLV_ChangedAt,
- AC_PLV_PLV_ID
- FROM
- @AC_PLV_Actor_ProfessionalLevel
- WHERE
- AC_PLV_Version = @currentVersion
- AND
- AC_PLV_StatementType in ('N');
- END
- END
- GO
- -- UPDATE trigger -----------------------------------------------------------------------------------------------------
- -- utAC_Actor instead of UPDATE trigger on lAC_Actor
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[utAC_Actor] ON [dbo].[lAC_Actor]
- INSTEAD OF UPDATE
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- IF(UPDATE(AC_ID))
- RAISERROR('The identity column AC_ID is not updatable.', 16, 1);
- IF(UPDATE(AC_NAM_Actor_Name))
- INSERT INTO [dbo].[AC_NAM_Actor_Name] (
- AC_NAM_AC_ID,
- Metadata_AC_NAM,
- AC_NAM_ChangedAt,
- AC_NAM_Actor_Name
- )
- SELECT
- i.AC_NAM_AC_ID,
- CASE WHEN UPDATE(Metadata_AC_NAM) THEN i.Metadata_AC_NAM ELSE 0 END,
- CASE WHEN UPDATE(AC_NAM_ChangedAt) THEN i.AC_NAM_ChangedAt ELSE @now END,
- i.AC_NAM_Actor_Name
- FROM
- inserted i
- LEFT JOIN
- [dbo].[AC_NAM_Actor_Name] b
- ON
- b.AC_NAM_AC_ID = i.AC_NAM_AC_ID
- AND
- b.AC_NAM_Actor_Name = i.AC_NAM_Actor_Name
- AND
- b.AC_NAM_ChangedAt = i.AC_NAM_ChangedAt
- WHERE
- b.AC_NAM_AC_ID is null
- AND
- [dbo].[rfAC_NAM_Actor_Name](
- i.AC_NAM_AC_ID,
- i.AC_NAM_Actor_Name,
- i.AC_NAM_ChangedAt
- ) = 0;
- IF(UPDATE(AC_PLV_PLV_ID) OR UPDATE(AC_PLV_PLV_ProfessionalLevel))
- INSERT INTO [dbo].[AC_PLV_Actor_ProfessionalLevel] (
- AC_PLV_AC_ID,
- Metadata_AC_PLV,
- AC_PLV_ChangedAt,
- AC_PLV_PLV_ID
- )
- SELECT
- i.AC_PLV_AC_ID,
- CASE WHEN UPDATE(Metadata_AC_PLV) THEN i.Metadata_AC_PLV ELSE 0 END,
- CASE WHEN UPDATE(AC_PLV_ChangedAt) THEN i.AC_PLV_ChangedAt ELSE @now END,
- CASE WHEN UPDATE(AC_PLV_PLV_ID) THEN i.AC_PLV_PLV_ID ELSE [kPLV].PLV_ID END
- FROM
- inserted i
- LEFT JOIN
- [dbo].[PLV_ProfessionalLevel] [kPLV]
- ON
- [kPLV].PLV_ProfessionalLevel = i.AC_PLV_PLV_ProfessionalLevel
- LEFT JOIN
- [dbo].[AC_PLV_Actor_ProfessionalLevel] b
- ON
- b.AC_PLV_AC_ID = i.AC_PLV_AC_ID
- AND
- b.AC_PLV_PLV_ID = CASE WHEN UPDATE(AC_PLV_PLV_ID) THEN i.AC_PLV_PLV_ID ELSE [kPLV].PLV_ID END
- AND
- b.AC_PLV_ChangedAt = i.AC_PLV_ChangedAt
- WHERE
- b.AC_PLV_AC_ID is null
- AND
- [dbo].[rfAC_PLV_Actor_ProfessionalLevel](
- i.AC_PLV_AC_ID,
- i.AC_PLV_PLV_ID,
- i.AC_PLV_ChangedAt
- ) = 0;
- END
- GO
- -- DELETE trigger -----------------------------------------------------------------------------------------------------
- -- dtAC_Actor instead of DELETE trigger on lAC_Actor
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[dtAC_Actor] ON [dbo].[lAC_Actor]
- INSTEAD OF DELETE
- AS
- BEGIN
- SET NOCOUNT ON;
- DELETE [NAM]
- FROM
- [dbo].[AC_NAM_Actor_Name] [NAM]
- JOIN
- deleted d
- ON
- d.AC_NAM_AC_ID = [NAM].AC_NAM_AC_ID
- AND
- d.AC_NAM_ChangedAt = [NAM].AC_NAM_ChangedAt;
- DELETE [GEN]
- FROM
- [dbo].[AC_GEN_Actor_Gender] [GEN]
- JOIN
- deleted d
- ON
- d.AC_GEN_AC_ID = [GEN].AC_GEN_AC_ID
- DELETE [PLV]
- FROM
- [dbo].[AC_PLV_Actor_ProfessionalLevel] [PLV]
- JOIN
- deleted d
- ON
- d.AC_PLV_AC_ID = [PLV].AC_PLV_AC_ID
- AND
- d.AC_PLV_ChangedAt = [PLV].AC_PLV_ChangedAt;
- DELETE [AC]
- FROM
- [dbo].[AC_Actor] [AC]
- LEFT JOIN
- [dbo].[AC_NAM_Actor_Name] [NAM]
- ON
- [NAM].AC_NAM_AC_ID = [AC].AC_ID
- LEFT JOIN
- [dbo].[AC_GEN_Actor_Gender] [GEN]
- ON
- [GEN].AC_GEN_AC_ID = [AC].AC_ID
- LEFT JOIN
- [dbo].[AC_PLV_Actor_ProfessionalLevel] [PLV]
- ON
- [PLV].AC_PLV_AC_ID = [AC].AC_ID
- WHERE
- [NAM].AC_NAM_AC_ID is null
- AND
- [GEN].AC_GEN_AC_ID is null
- AND
- [PLV].AC_PLV_AC_ID is null;
- END
- GO
- -- TIE RESTATEMENT CONSTRAINTS ----------------------------------------------------------------------------------------
- --
- -- Ties may be prevented from storing restatements.
- -- A restatement is when the same (non-key) values occurs for two adjacent points
- -- in changing time.
- --
- -- returns 1 for one or two equal surrounding values, 0 for different surrounding values
- --
- -- Restatement Finder Function and Constraint -------------------------------------------------------------------------
- -- rfAC_exclusive_AC_with restatement finder
- --
- -- @AC_ID_exclusive non-key value
- -- @AC_ID_with non-key value
- -- @changed the point in time from which this value shall represent a change
- --
- -- rcAC_exclusive_AC_with restatement constraint
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('rfAC_exclusive_AC_with', 'FN') IS NULL
- BEGIN
- EXEC('
- CREATE FUNCTION [dbo].[rfAC_exclusive_AC_with] (
- @AC_ID_exclusive int,
- @AC_ID_with int,
- @changed datetime
- )
- RETURNS tinyint AS
- BEGIN RETURN (
- SELECT
- COUNT(*)
- FROM (
- SELECT TOP 1
- pre.AC_ID_exclusive,
- pre.AC_ID_with
- FROM
- [dbo].[AC_exclusive_AC_with] pre
- WHERE
- (
- pre.AC_ID_exclusive = @AC_ID_exclusive
- OR
- pre.AC_ID_with = @AC_ID_with
- )
- AND
- pre.AC_exclusive_AC_with_ChangedAt < @changed
- ORDER BY
- pre.AC_exclusive_AC_with_ChangedAt DESC
- UNION
- SELECT TOP 1
- fol.AC_ID_exclusive,
- fol.AC_ID_with
- FROM
- [dbo].[AC_exclusive_AC_with] fol
- WHERE
- (
- fol.AC_ID_exclusive = @AC_ID_exclusive
- OR
- fol.AC_ID_with = @AC_ID_with
- )
- AND
- fol.AC_exclusive_AC_with_ChangedAt > @changed
- ORDER BY
- fol.AC_exclusive_AC_with_ChangedAt ASC
- ) s
- WHERE
- s.AC_ID_exclusive = @AC_ID_exclusive
- AND
- s.AC_ID_with = @AC_ID_with
- );
- END
- ');
- ALTER TABLE [dbo].[AC_exclusive_AC_with]
- ADD CONSTRAINT [rcAC_exclusive_AC_with] CHECK (
- [dbo].[rfAC_exclusive_AC_with] (
- AC_ID_exclusive,
- AC_ID_with,
- AC_exclusive_AC_with_ChangedAt
- ) = 0
- );
- END
- GO
- -- Restatement Finder Function and Constraint -------------------------------------------------------------------------
- -- rfAC_part_PR_in_RAT_got restatement finder
- --
- -- @AC_ID_part primary key component
- -- @PR_ID_in primary key component
- -- @RAT_ID_got non-key value
- -- @changed the point in time from which this value shall represent a change
- --
- -- rcAC_part_PR_in_RAT_got restatement constraint
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('rfAC_part_PR_in_RAT_got', 'FN') IS NULL
- BEGIN
- EXEC('
- CREATE FUNCTION [dbo].[rfAC_part_PR_in_RAT_got] (
- @AC_ID_part int,
- @PR_ID_in int,
- @RAT_ID_got tinyint,
- @changed datetime
- )
- RETURNS tinyint AS
- BEGIN RETURN (
- SELECT
- COUNT(*)
- FROM (
- SELECT TOP 1
- pre.RAT_ID_got
- FROM
- [dbo].[AC_part_PR_in_RAT_got] pre
- WHERE
- pre.AC_ID_part = @AC_ID_part
- AND
- pre.PR_ID_in = @PR_ID_in
- AND
- pre.AC_part_PR_in_RAT_got_ChangedAt < @changed
- ORDER BY
- pre.AC_part_PR_in_RAT_got_ChangedAt DESC
- UNION
- SELECT TOP 1
- fol.RAT_ID_got
- FROM
- [dbo].[AC_part_PR_in_RAT_got] fol
- WHERE
- fol.AC_ID_part = @AC_ID_part
- AND
- fol.PR_ID_in = @PR_ID_in
- AND
- fol.AC_part_PR_in_RAT_got_ChangedAt > @changed
- ORDER BY
- fol.AC_part_PR_in_RAT_got_ChangedAt ASC
- ) s
- WHERE
- s.RAT_ID_got = @RAT_ID_got
- );
- END
- ');
- ALTER TABLE [dbo].[AC_part_PR_in_RAT_got]
- ADD CONSTRAINT [rcAC_part_PR_in_RAT_got] CHECK (
- [dbo].[rfAC_part_PR_in_RAT_got] (
- AC_ID_part,
- PR_ID_in,
- RAT_ID_got,
- AC_part_PR_in_RAT_got_ChangedAt
- ) = 0
- );
- END
- GO
- -- TIE TEMPORAL PERSPECTIVES ------------------------------------------------------------------------------------------
- --
- -- These table valued functions simplify temporal querying by providing a temporal
- -- perspective of each tie. There are four types of perspectives: latest,
- -- point-in-time, difference, and now.
- --
- -- The latest perspective shows the latest available information for each tie.
- -- The now perspective shows the information as it is right now.
- -- The point-in-time perspective lets you travel through the information to the given timepoint.
- --
- -- @changingTimepoint the point in changing time to travel to
- --
- -- The difference perspective shows changes between the two given timepoints.
- --
- -- @intervalStart the start of the interval for finding changes
- -- @intervalEnd the end of the interval for finding changes
- --
- -- Drop perspectives --------------------------------------------------------------------------------------------------
- IF Object_ID('dAC_parent_AC_child_PAT_having', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[dAC_parent_AC_child_PAT_having];
- IF Object_ID('nAC_parent_AC_child_PAT_having', 'V') IS NOT NULL
- DROP VIEW [dbo].[nAC_parent_AC_child_PAT_having];
- IF Object_ID('pAC_parent_AC_child_PAT_having', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[pAC_parent_AC_child_PAT_having];
- IF Object_ID('lAC_parent_AC_child_PAT_having', 'V') IS NOT NULL
- DROP VIEW [dbo].[lAC_parent_AC_child_PAT_having];
- GO
- -- Latest perspective -------------------------------------------------------------------------------------------------
- -- lAC_parent_AC_child_PAT_having viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[lAC_parent_AC_child_PAT_having] WITH SCHEMABINDING AS
- SELECT
- tie.Metadata_AC_parent_AC_child_PAT_having,
- tie.AC_ID_parent,
- tie.AC_ID_child,
- [PAT_having].PAT_ParentalType AS having_PAT_ParentalType,
- [PAT_having].Metadata_PAT AS having_Metadata_PAT,
- tie.PAT_ID_having
- FROM
- [dbo].[AC_parent_AC_child_PAT_having] tie
- LEFT JOIN
- [dbo].[PAT_ParentalType] [PAT_having]
- ON
- [PAT_having].PAT_ID = tie.PAT_ID_having;
- GO
- -- Point-in-time perspective ------------------------------------------------------------------------------------------
- -- pAC_parent_AC_child_PAT_having viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[pAC_parent_AC_child_PAT_having] (
- @changingTimepoint datetime2(7)
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- tie.Metadata_AC_parent_AC_child_PAT_having,
- tie.AC_ID_parent,
- tie.AC_ID_child,
- [PAT_having].PAT_ParentalType AS having_PAT_ParentalType,
- [PAT_having].Metadata_PAT AS having_Metadata_PAT,
- tie.PAT_ID_having
- FROM
- [dbo].[AC_parent_AC_child_PAT_having] tie
- LEFT JOIN
- [dbo].[PAT_ParentalType] [PAT_having]
- ON
- [PAT_having].PAT_ID = tie.PAT_ID_having;
- GO
- -- Now perspective ----------------------------------------------------------------------------------------------------
- -- nAC_parent_AC_child_PAT_having viewed as it currently is (cannot include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[nAC_parent_AC_child_PAT_having]
- AS
- SELECT
- *
- FROM
- [dbo].[pAC_parent_AC_child_PAT_having](sysdatetime());
- GO
- -- Drop perspectives --------------------------------------------------------------------------------------------------
- IF Object_ID('dST_atLocation_PR_isPlaying', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[dST_atLocation_PR_isPlaying];
- IF Object_ID('nST_atLocation_PR_isPlaying', 'V') IS NOT NULL
- DROP VIEW [dbo].[nST_atLocation_PR_isPlaying];
- IF Object_ID('pST_atLocation_PR_isPlaying', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[pST_atLocation_PR_isPlaying];
- IF Object_ID('lST_atLocation_PR_isPlaying', 'V') IS NOT NULL
- DROP VIEW [dbo].[lST_atLocation_PR_isPlaying];
- GO
- -- Latest perspective -------------------------------------------------------------------------------------------------
- -- lST_atLocation_PR_isPlaying viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[lST_atLocation_PR_isPlaying] WITH SCHEMABINDING AS
- SELECT
- tie.Metadata_ST_atLocation_PR_isPlaying,
- tie.ST_atLocation_PR_isPlaying_ChangedAt,
- tie.ST_ID_atLocation,
- tie.PR_ID_isPlaying
- FROM
- [dbo].[ST_atLocation_PR_isPlaying] tie
- WHERE
- tie.ST_atLocation_PR_isPlaying_ChangedAt = (
- SELECT
- max(sub.ST_atLocation_PR_isPlaying_ChangedAt)
- FROM
- [dbo].[ST_atLocation_PR_isPlaying] sub
- WHERE
- sub.ST_ID_atLocation = tie.ST_ID_atLocation
- AND
- sub.PR_ID_isPlaying = tie.PR_ID_isPlaying
- );
- GO
- -- Point-in-time perspective ------------------------------------------------------------------------------------------
- -- pST_atLocation_PR_isPlaying viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[pST_atLocation_PR_isPlaying] (
- @changingTimepoint datetime2(7)
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- tie.Metadata_ST_atLocation_PR_isPlaying,
- tie.ST_atLocation_PR_isPlaying_ChangedAt,
- tie.ST_ID_atLocation,
- tie.PR_ID_isPlaying
- FROM
- [dbo].[ST_atLocation_PR_isPlaying] tie
- WHERE
- tie.ST_atLocation_PR_isPlaying_ChangedAt = (
- SELECT
- max(sub.ST_atLocation_PR_isPlaying_ChangedAt)
- FROM
- [dbo].[ST_atLocation_PR_isPlaying] sub
- WHERE
- sub.ST_ID_atLocation = tie.ST_ID_atLocation
- AND
- sub.PR_ID_isPlaying = tie.PR_ID_isPlaying
- AND
- tie.ST_atLocation_PR_isPlaying_ChangedAt <= @changingTimepoint
- );
- GO
- -- Now perspective ----------------------------------------------------------------------------------------------------
- -- nST_atLocation_PR_isPlaying viewed as it currently is (cannot include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[nST_atLocation_PR_isPlaying]
- AS
- SELECT
- *
- FROM
- [dbo].[pST_atLocation_PR_isPlaying](sysdatetime());
- GO
- -- Difference perspective ---------------------------------------------------------------------------------------------
- -- dST_atLocation_PR_isPlaying showing all differences between the given timepoints
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[dST_atLocation_PR_isPlaying] (
- @intervalStart datetime2(7),
- @intervalEnd datetime2(7)
- )
- RETURNS TABLE AS RETURN
- SELECT
- tie.Metadata_ST_atLocation_PR_isPlaying,
- tie.ST_atLocation_PR_isPlaying_ChangedAt,
- tie.ST_ID_atLocation,
- tie.PR_ID_isPlaying
- FROM
- [dbo].[ST_atLocation_PR_isPlaying] tie
- WHERE
- tie.ST_atLocation_PR_isPlaying_ChangedAt BETWEEN @intervalStart AND @intervalEnd;
- GO
- -- Drop perspectives --------------------------------------------------------------------------------------------------
- IF Object_ID('dPE_at_PR_wasPlayed', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[dPE_at_PR_wasPlayed];
- IF Object_ID('nPE_at_PR_wasPlayed', 'V') IS NOT NULL
- DROP VIEW [dbo].[nPE_at_PR_wasPlayed];
- IF Object_ID('pPE_at_PR_wasPlayed', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[pPE_at_PR_wasPlayed];
- IF Object_ID('lPE_at_PR_wasPlayed', 'V') IS NOT NULL
- DROP VIEW [dbo].[lPE_at_PR_wasPlayed];
- GO
- -- Latest perspective -------------------------------------------------------------------------------------------------
- -- lPE_at_PR_wasPlayed viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[lPE_at_PR_wasPlayed] WITH SCHEMABINDING AS
- SELECT
- tie.Metadata_PE_at_PR_wasPlayed,
- tie.PE_ID_at,
- tie.PR_ID_wasPlayed
- FROM
- [dbo].[PE_at_PR_wasPlayed] tie;
- GO
- -- Point-in-time perspective ------------------------------------------------------------------------------------------
- -- pPE_at_PR_wasPlayed viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[pPE_at_PR_wasPlayed] (
- @changingTimepoint datetime2(7)
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- tie.Metadata_PE_at_PR_wasPlayed,
- tie.PE_ID_at,
- tie.PR_ID_wasPlayed
- FROM
- [dbo].[PE_at_PR_wasPlayed] tie;
- GO
- -- Now perspective ----------------------------------------------------------------------------------------------------
- -- nPE_at_PR_wasPlayed viewed as it currently is (cannot include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[nPE_at_PR_wasPlayed]
- AS
- SELECT
- *
- FROM
- [dbo].[pPE_at_PR_wasPlayed](sysdatetime());
- GO
- -- Drop perspectives --------------------------------------------------------------------------------------------------
- IF Object_ID('dPE_wasHeld_ST_atLocation', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[dPE_wasHeld_ST_atLocation];
- IF Object_ID('nPE_wasHeld_ST_atLocation', 'V') IS NOT NULL
- DROP VIEW [dbo].[nPE_wasHeld_ST_atLocation];
- IF Object_ID('pPE_wasHeld_ST_atLocation', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[pPE_wasHeld_ST_atLocation];
- IF Object_ID('lPE_wasHeld_ST_atLocation', 'V') IS NOT NULL
- DROP VIEW [dbo].[lPE_wasHeld_ST_atLocation];
- GO
- -- Latest perspective -------------------------------------------------------------------------------------------------
- -- lPE_wasHeld_ST_atLocation viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[lPE_wasHeld_ST_atLocation] WITH SCHEMABINDING AS
- SELECT
- tie.Metadata_PE_wasHeld_ST_atLocation,
- tie.PE_ID_wasHeld,
- tie.ST_ID_atLocation
- FROM
- [dbo].[PE_wasHeld_ST_atLocation] tie;
- GO
- -- Point-in-time perspective ------------------------------------------------------------------------------------------
- -- pPE_wasHeld_ST_atLocation viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[pPE_wasHeld_ST_atLocation] (
- @changingTimepoint datetime2(7)
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- tie.Metadata_PE_wasHeld_ST_atLocation,
- tie.PE_ID_wasHeld,
- tie.ST_ID_atLocation
- FROM
- [dbo].[PE_wasHeld_ST_atLocation] tie;
- GO
- -- Now perspective ----------------------------------------------------------------------------------------------------
- -- nPE_wasHeld_ST_atLocation viewed as it currently is (cannot include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[nPE_wasHeld_ST_atLocation]
- AS
- SELECT
- *
- FROM
- [dbo].[pPE_wasHeld_ST_atLocation](sysdatetime());
- GO
- -- Drop perspectives --------------------------------------------------------------------------------------------------
- IF Object_ID('dAC_exclusive_AC_with', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[dAC_exclusive_AC_with];
- IF Object_ID('nAC_exclusive_AC_with', 'V') IS NOT NULL
- DROP VIEW [dbo].[nAC_exclusive_AC_with];
- IF Object_ID('pAC_exclusive_AC_with', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[pAC_exclusive_AC_with];
- IF Object_ID('lAC_exclusive_AC_with', 'V') IS NOT NULL
- DROP VIEW [dbo].[lAC_exclusive_AC_with];
- GO
- -- Latest perspective -------------------------------------------------------------------------------------------------
- -- lAC_exclusive_AC_with viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[lAC_exclusive_AC_with] WITH SCHEMABINDING AS
- SELECT
- tie.Metadata_AC_exclusive_AC_with,
- tie.AC_exclusive_AC_with_ChangedAt,
- tie.AC_ID_exclusive,
- tie.AC_ID_with
- FROM
- [dbo].[AC_exclusive_AC_with] tie
- WHERE
- tie.AC_exclusive_AC_with_ChangedAt = (
- SELECT
- max(sub.AC_exclusive_AC_with_ChangedAt)
- FROM
- [dbo].[AC_exclusive_AC_with] sub
- WHERE
- sub.AC_ID_exclusive = tie.AC_ID_exclusive
- OR
- sub.AC_ID_with = tie.AC_ID_with
- );
- GO
- -- Point-in-time perspective ------------------------------------------------------------------------------------------
- -- pAC_exclusive_AC_with viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[pAC_exclusive_AC_with] (
- @changingTimepoint datetime2(7)
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- tie.Metadata_AC_exclusive_AC_with,
- tie.AC_exclusive_AC_with_ChangedAt,
- tie.AC_ID_exclusive,
- tie.AC_ID_with
- FROM
- [dbo].[AC_exclusive_AC_with] tie
- WHERE
- tie.AC_exclusive_AC_with_ChangedAt = (
- SELECT
- max(sub.AC_exclusive_AC_with_ChangedAt)
- FROM
- [dbo].[AC_exclusive_AC_with] sub
- WHERE
- (
- sub.AC_ID_exclusive = tie.AC_ID_exclusive
- OR
- sub.AC_ID_with = tie.AC_ID_with
- )
- AND
- tie.AC_exclusive_AC_with_ChangedAt <= @changingTimepoint
- );
- GO
- -- Now perspective ----------------------------------------------------------------------------------------------------
- -- nAC_exclusive_AC_with viewed as it currently is (cannot include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[nAC_exclusive_AC_with]
- AS
- SELECT
- *
- FROM
- [dbo].[pAC_exclusive_AC_with](sysdatetime());
- GO
- -- Difference perspective ---------------------------------------------------------------------------------------------
- -- dAC_exclusive_AC_with showing all differences between the given timepoints
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[dAC_exclusive_AC_with] (
- @intervalStart datetime2(7),
- @intervalEnd datetime2(7)
- )
- RETURNS TABLE AS RETURN
- SELECT
- tie.Metadata_AC_exclusive_AC_with,
- tie.AC_exclusive_AC_with_ChangedAt,
- tie.AC_ID_exclusive,
- tie.AC_ID_with
- FROM
- [dbo].[AC_exclusive_AC_with] tie
- WHERE
- tie.AC_exclusive_AC_with_ChangedAt BETWEEN @intervalStart AND @intervalEnd;
- GO
- -- Drop perspectives --------------------------------------------------------------------------------------------------
- IF Object_ID('dPE_subset_EV_of', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[dPE_subset_EV_of];
- IF Object_ID('nPE_subset_EV_of', 'V') IS NOT NULL
- DROP VIEW [dbo].[nPE_subset_EV_of];
- IF Object_ID('pPE_subset_EV_of', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[pPE_subset_EV_of];
- IF Object_ID('lPE_subset_EV_of', 'V') IS NOT NULL
- DROP VIEW [dbo].[lPE_subset_EV_of];
- GO
- -- Latest perspective -------------------------------------------------------------------------------------------------
- -- lPE_subset_EV_of viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[lPE_subset_EV_of] WITH SCHEMABINDING AS
- SELECT
- tie.Metadata_PE_subset_EV_of,
- tie.PE_ID_subset,
- tie.EV_ID_of
- FROM
- [dbo].[PE_subset_EV_of] tie;
- GO
- -- Point-in-time perspective ------------------------------------------------------------------------------------------
- -- pPE_subset_EV_of viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[pPE_subset_EV_of] (
- @changingTimepoint datetime2(7)
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- tie.Metadata_PE_subset_EV_of,
- tie.PE_ID_subset,
- tie.EV_ID_of
- FROM
- [dbo].[PE_subset_EV_of] tie;
- GO
- -- Now perspective ----------------------------------------------------------------------------------------------------
- -- nPE_subset_EV_of viewed as it currently is (cannot include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[nPE_subset_EV_of]
- AS
- SELECT
- *
- FROM
- [dbo].[pPE_subset_EV_of](sysdatetime());
- GO
- -- Drop perspectives --------------------------------------------------------------------------------------------------
- IF Object_ID('dPE_in_AC_wasCast', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[dPE_in_AC_wasCast];
- IF Object_ID('nPE_in_AC_wasCast', 'V') IS NOT NULL
- DROP VIEW [dbo].[nPE_in_AC_wasCast];
- IF Object_ID('pPE_in_AC_wasCast', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[pPE_in_AC_wasCast];
- IF Object_ID('lPE_in_AC_wasCast', 'V') IS NOT NULL
- DROP VIEW [dbo].[lPE_in_AC_wasCast];
- GO
- -- Latest perspective -------------------------------------------------------------------------------------------------
- -- lPE_in_AC_wasCast viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[lPE_in_AC_wasCast] WITH SCHEMABINDING AS
- SELECT
- tie.Metadata_PE_in_AC_wasCast,
- tie.PE_ID_in,
- tie.AC_ID_wasCast
- FROM
- [dbo].[PE_in_AC_wasCast] tie;
- GO
- -- Point-in-time perspective ------------------------------------------------------------------------------------------
- -- pPE_in_AC_wasCast viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[pPE_in_AC_wasCast] (
- @changingTimepoint datetime2(7)
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- tie.Metadata_PE_in_AC_wasCast,
- tie.PE_ID_in,
- tie.AC_ID_wasCast
- FROM
- [dbo].[PE_in_AC_wasCast] tie;
- GO
- -- Now perspective ----------------------------------------------------------------------------------------------------
- -- nPE_in_AC_wasCast viewed as it currently is (cannot include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[nPE_in_AC_wasCast]
- AS
- SELECT
- *
- FROM
- [dbo].[pPE_in_AC_wasCast](sysdatetime());
- GO
- -- Drop perspectives --------------------------------------------------------------------------------------------------
- IF Object_ID('dAC_part_PR_in_RAT_got', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[dAC_part_PR_in_RAT_got];
- IF Object_ID('nAC_part_PR_in_RAT_got', 'V') IS NOT NULL
- DROP VIEW [dbo].[nAC_part_PR_in_RAT_got];
- IF Object_ID('pAC_part_PR_in_RAT_got', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[pAC_part_PR_in_RAT_got];
- IF Object_ID('lAC_part_PR_in_RAT_got', 'V') IS NOT NULL
- DROP VIEW [dbo].[lAC_part_PR_in_RAT_got];
- GO
- -- Latest perspective -------------------------------------------------------------------------------------------------
- -- lAC_part_PR_in_RAT_got viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[lAC_part_PR_in_RAT_got] WITH SCHEMABINDING AS
- SELECT
- tie.Metadata_AC_part_PR_in_RAT_got,
- tie.AC_part_PR_in_RAT_got_ChangedAt,
- tie.AC_ID_part,
- tie.PR_ID_in,
- [RAT_got].RAT_Rating AS got_RAT_Rating,
- [RAT_got].Metadata_RAT AS got_Metadata_RAT,
- tie.RAT_ID_got
- FROM
- [dbo].[AC_part_PR_in_RAT_got] tie
- LEFT JOIN
- [dbo].[RAT_Rating] [RAT_got]
- ON
- [RAT_got].RAT_ID = tie.RAT_ID_got
- WHERE
- tie.AC_part_PR_in_RAT_got_ChangedAt = (
- SELECT
- max(sub.AC_part_PR_in_RAT_got_ChangedAt)
- FROM
- [dbo].[AC_part_PR_in_RAT_got] sub
- WHERE
- sub.AC_ID_part = tie.AC_ID_part
- AND
- sub.PR_ID_in = tie.PR_ID_in
- );
- GO
- -- Point-in-time perspective ------------------------------------------------------------------------------------------
- -- pAC_part_PR_in_RAT_got viewed by the latest available information (may include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[pAC_part_PR_in_RAT_got] (
- @changingTimepoint datetime2(7)
- )
- RETURNS TABLE WITH SCHEMABINDING AS RETURN
- SELECT
- tie.Metadata_AC_part_PR_in_RAT_got,
- tie.AC_part_PR_in_RAT_got_ChangedAt,
- tie.AC_ID_part,
- tie.PR_ID_in,
- [RAT_got].RAT_Rating AS got_RAT_Rating,
- [RAT_got].Metadata_RAT AS got_Metadata_RAT,
- tie.RAT_ID_got
- FROM
- [dbo].[AC_part_PR_in_RAT_got] tie
- LEFT JOIN
- [dbo].[RAT_Rating] [RAT_got]
- ON
- [RAT_got].RAT_ID = tie.RAT_ID_got
- WHERE
- tie.AC_part_PR_in_RAT_got_ChangedAt = (
- SELECT
- max(sub.AC_part_PR_in_RAT_got_ChangedAt)
- FROM
- [dbo].[AC_part_PR_in_RAT_got] sub
- WHERE
- sub.AC_ID_part = tie.AC_ID_part
- AND
- sub.PR_ID_in = tie.PR_ID_in
- AND
- tie.AC_part_PR_in_RAT_got_ChangedAt <= @changingTimepoint
- );
- GO
- -- Now perspective ----------------------------------------------------------------------------------------------------
- -- nAC_part_PR_in_RAT_got viewed as it currently is (cannot include future versions)
- -----------------------------------------------------------------------------------------------------------------------
- CREATE VIEW [dbo].[nAC_part_PR_in_RAT_got]
- AS
- SELECT
- *
- FROM
- [dbo].[pAC_part_PR_in_RAT_got](sysdatetime());
- GO
- -- Difference perspective ---------------------------------------------------------------------------------------------
- -- dAC_part_PR_in_RAT_got showing all differences between the given timepoints
- -----------------------------------------------------------------------------------------------------------------------
- CREATE FUNCTION [dbo].[dAC_part_PR_in_RAT_got] (
- @intervalStart datetime2(7),
- @intervalEnd datetime2(7)
- )
- RETURNS TABLE AS RETURN
- SELECT
- tie.Metadata_AC_part_PR_in_RAT_got,
- tie.AC_part_PR_in_RAT_got_ChangedAt,
- tie.AC_ID_part,
- tie.PR_ID_in,
- [RAT_got].RAT_Rating AS got_RAT_Rating,
- [RAT_got].Metadata_RAT AS got_Metadata_RAT,
- tie.RAT_ID_got
- FROM
- [dbo].[AC_part_PR_in_RAT_got] tie
- LEFT JOIN
- [dbo].[RAT_Rating] [RAT_got]
- ON
- [RAT_got].RAT_ID = tie.RAT_ID_got
- WHERE
- tie.AC_part_PR_in_RAT_got_ChangedAt BETWEEN @intervalStart AND @intervalEnd;
- GO
- -- TIE TRIGGERS -------------------------------------------------------------------------------------------------------
- --
- -- The following triggers on the latest view make it behave like a table.
- -- There are three different 'instead of' triggers: insert, update, and delete.
- -- They will ensure that such operations are propagated to the underlying tables
- -- in a consistent way. Default values are used for some columns if not provided
- -- by the corresponding SQL statements.
- --
- -- For idempotent ties, only changes that represent values different from
- -- the previous or following value are stored. Others are silently ignored in
- -- order to avoid unnecessary temporal duplicates.
- --
- -- Insert trigger -----------------------------------------------------------------------------------------------------
- -- itAC_parent_AC_child_PAT_having instead of INSERT trigger on lAC_parent_AC_child_PAT_having
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[itAC_parent_AC_child_PAT_having] ON [dbo].[lAC_parent_AC_child_PAT_having]
- INSTEAD OF INSERT
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- DECLARE @maxVersion int;
- DECLARE @currentVersion int;
- DECLARE @inserted TABLE (
- Metadata_AC_parent_AC_child_PAT_having int not null,
- AC_ID_parent int not null,
- AC_ID_child int not null,
- PAT_ID_having tinyint not null,
- primary key (
- AC_ID_parent,
- AC_ID_child,
- PAT_ID_having
- )
- );
- INSERT INTO @inserted
- SELECT
- ISNULL(i.Metadata_AC_parent_AC_child_PAT_having, 0),
- i.AC_ID_parent,
- i.AC_ID_child,
- ISNULL(i.PAT_ID_having, [PAT_having].PAT_ID)
- FROM
- inserted i
- LEFT JOIN
- [dbo].[PAT_ParentalType] [PAT_having]
- ON
- [PAT_having].PAT_ParentalType = i.having_PAT_ParentalType
- WHERE
- i.AC_ID_parent is not null
- AND
- i.AC_ID_child is not null
- AND
- i.PAT_ID_having is not null;
- INSERT INTO [dbo].[AC_parent_AC_child_PAT_having] (
- Metadata_AC_parent_AC_child_PAT_having,
- AC_ID_parent,
- AC_ID_child,
- PAT_ID_having
- )
- SELECT
- i.Metadata_AC_parent_AC_child_PAT_having,
- i.AC_ID_parent,
- i.AC_ID_child,
- i.PAT_ID_having
- FROM
- @inserted i
- LEFT JOIN
- [dbo].[AC_parent_AC_child_PAT_having] tie
- ON
- tie.AC_ID_parent = i.AC_ID_parent
- AND
- tie.AC_ID_child = i.AC_ID_child
- AND
- tie.PAT_ID_having = i.PAT_ID_having
- WHERE
- tie.PAT_ID_having is null;
- END
- GO
- -- DELETE trigger -----------------------------------------------------------------------------------------------------
- -- dtAC_parent_AC_child_PAT_having instead of DELETE trigger on lAC_parent_AC_child_PAT_having
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[dtAC_parent_AC_child_PAT_having] ON [dbo].[lAC_parent_AC_child_PAT_having]
- INSTEAD OF DELETE
- AS
- BEGIN
- SET NOCOUNT ON;
- DELETE tie
- FROM
- [dbo].[AC_parent_AC_child_PAT_having] tie
- JOIN
- deleted d
- ON
- d.AC_ID_parent = tie.AC_ID_parent
- AND
- d.AC_ID_child = tie.AC_ID_child
- AND
- d.PAT_ID_having = tie.PAT_ID_having;
- END
- GO
- -- Insert trigger -----------------------------------------------------------------------------------------------------
- -- itST_atLocation_PR_isPlaying instead of INSERT trigger on lST_atLocation_PR_isPlaying
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[itST_atLocation_PR_isPlaying] ON [dbo].[lST_atLocation_PR_isPlaying]
- INSTEAD OF INSERT
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- DECLARE @maxVersion int;
- DECLARE @currentVersion int;
- DECLARE @inserted TABLE (
- Metadata_ST_atLocation_PR_isPlaying int not null,
- ST_atLocation_PR_isPlaying_ChangedAt datetime not null,
- ST_atLocation_PR_isPlaying_Version bigint not null,
- ST_atLocation_PR_isPlaying_StatementType char(1) not null,
- ST_ID_atLocation int not null,
- PR_ID_isPlaying int not null,
- primary key (
- ST_atLocation_PR_isPlaying_Version,
- ST_ID_atLocation,
- PR_ID_isPlaying
- )
- );
- INSERT INTO @inserted
- SELECT
- ISNULL(i.Metadata_ST_atLocation_PR_isPlaying, 0),
- ISNULL(i.ST_atLocation_PR_isPlaying_ChangedAt, @now),
- DENSE_RANK() OVER (
- PARTITION BY
- ST_ID_atLocation,
- PR_ID_isPlaying
- ORDER BY
- ISNULL(i.ST_atLocation_PR_isPlaying_ChangedAt, @now)
- ),
- 'X',
- i.ST_ID_atLocation,
- i.PR_ID_isPlaying
- FROM
- inserted i
- WHERE
- i.ST_ID_atLocation is not null
- AND
- i.PR_ID_isPlaying is not null;
- INSERT INTO [dbo].[ST_atLocation_PR_isPlaying] (
- Metadata_ST_atLocation_PR_isPlaying,
- ST_ID_atLocation,
- PR_ID_isPlaying
- )
- SELECT
- i.Metadata_ST_atLocation_PR_isPlaying,
- i.ST_ID_atLocation,
- i.PR_ID_isPlaying
- FROM
- @inserted i
- LEFT JOIN
- [dbo].[ST_atLocation_PR_isPlaying] tie
- ON
- tie.ST_ID_atLocation = i.ST_ID_atLocation
- AND
- tie.PR_ID_isPlaying = i.PR_ID_isPlaying
- WHERE
- tie.PR_ID_isPlaying is null;
- END
- GO
- -- DELETE trigger -----------------------------------------------------------------------------------------------------
- -- dtST_atLocation_PR_isPlaying instead of DELETE trigger on lST_atLocation_PR_isPlaying
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[dtST_atLocation_PR_isPlaying] ON [dbo].[lST_atLocation_PR_isPlaying]
- INSTEAD OF DELETE
- AS
- BEGIN
- SET NOCOUNT ON;
- DELETE tie
- FROM
- [dbo].[ST_atLocation_PR_isPlaying] tie
- JOIN
- deleted d
- ON
- d.ST_atLocation_PR_isPlaying_ChangedAt = tie.ST_atLocation_PR_isPlaying_ChangedAt
- AND
- d.ST_ID_atLocation = tie.ST_ID_atLocation
- AND
- d.PR_ID_isPlaying = tie.PR_ID_isPlaying;
- END
- GO
- -- Insert trigger -----------------------------------------------------------------------------------------------------
- -- itPE_at_PR_wasPlayed instead of INSERT trigger on lPE_at_PR_wasPlayed
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[itPE_at_PR_wasPlayed] ON [dbo].[lPE_at_PR_wasPlayed]
- INSTEAD OF INSERT
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- DECLARE @maxVersion int;
- DECLARE @currentVersion int;
- DECLARE @inserted TABLE (
- Metadata_PE_at_PR_wasPlayed int not null,
- PE_ID_at int not null,
- PR_ID_wasPlayed int not null,
- primary key (
- PE_ID_at
- )
- );
- INSERT INTO @inserted
- SELECT
- ISNULL(i.Metadata_PE_at_PR_wasPlayed, 0),
- i.PE_ID_at,
- i.PR_ID_wasPlayed
- FROM
- inserted i
- WHERE
- i.PE_ID_at is not null;
- INSERT INTO [dbo].[PE_at_PR_wasPlayed] (
- Metadata_PE_at_PR_wasPlayed,
- PE_ID_at,
- PR_ID_wasPlayed
- )
- SELECT
- i.Metadata_PE_at_PR_wasPlayed,
- i.PE_ID_at,
- i.PR_ID_wasPlayed
- FROM
- @inserted i
- LEFT JOIN
- [dbo].[PE_at_PR_wasPlayed] tie
- ON
- tie.PE_ID_at = i.PE_ID_at
- WHERE
- tie.PE_ID_at is null;
- END
- GO
- -- DELETE trigger -----------------------------------------------------------------------------------------------------
- -- dtPE_at_PR_wasPlayed instead of DELETE trigger on lPE_at_PR_wasPlayed
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[dtPE_at_PR_wasPlayed] ON [dbo].[lPE_at_PR_wasPlayed]
- INSTEAD OF DELETE
- AS
- BEGIN
- SET NOCOUNT ON;
- DELETE tie
- FROM
- [dbo].[PE_at_PR_wasPlayed] tie
- JOIN
- deleted d
- ON
- d.PE_ID_at = tie.PE_ID_at;
- END
- GO
- -- Insert trigger -----------------------------------------------------------------------------------------------------
- -- itPE_wasHeld_ST_atLocation instead of INSERT trigger on lPE_wasHeld_ST_atLocation
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[itPE_wasHeld_ST_atLocation] ON [dbo].[lPE_wasHeld_ST_atLocation]
- INSTEAD OF INSERT
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- DECLARE @maxVersion int;
- DECLARE @currentVersion int;
- DECLARE @inserted TABLE (
- Metadata_PE_wasHeld_ST_atLocation int not null,
- PE_ID_wasHeld int not null,
- ST_ID_atLocation int not null,
- primary key (
- PE_ID_wasHeld
- )
- );
- INSERT INTO @inserted
- SELECT
- ISNULL(i.Metadata_PE_wasHeld_ST_atLocation, 0),
- i.PE_ID_wasHeld,
- i.ST_ID_atLocation
- FROM
- inserted i
- WHERE
- i.PE_ID_wasHeld is not null;
- INSERT INTO [dbo].[PE_wasHeld_ST_atLocation] (
- Metadata_PE_wasHeld_ST_atLocation,
- PE_ID_wasHeld,
- ST_ID_atLocation
- )
- SELECT
- i.Metadata_PE_wasHeld_ST_atLocation,
- i.PE_ID_wasHeld,
- i.ST_ID_atLocation
- FROM
- @inserted i
- LEFT JOIN
- [dbo].[PE_wasHeld_ST_atLocation] tie
- ON
- tie.PE_ID_wasHeld = i.PE_ID_wasHeld
- WHERE
- tie.PE_ID_wasHeld is null;
- END
- GO
- -- DELETE trigger -----------------------------------------------------------------------------------------------------
- -- dtPE_wasHeld_ST_atLocation instead of DELETE trigger on lPE_wasHeld_ST_atLocation
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[dtPE_wasHeld_ST_atLocation] ON [dbo].[lPE_wasHeld_ST_atLocation]
- INSTEAD OF DELETE
- AS
- BEGIN
- SET NOCOUNT ON;
- DELETE tie
- FROM
- [dbo].[PE_wasHeld_ST_atLocation] tie
- JOIN
- deleted d
- ON
- d.PE_ID_wasHeld = tie.PE_ID_wasHeld;
- END
- GO
- -- Insert trigger -----------------------------------------------------------------------------------------------------
- -- itAC_exclusive_AC_with instead of INSERT trigger on lAC_exclusive_AC_with
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[itAC_exclusive_AC_with] ON [dbo].[lAC_exclusive_AC_with]
- INSTEAD OF INSERT
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- DECLARE @maxVersion int;
- DECLARE @currentVersion int;
- DECLARE @inserted TABLE (
- Metadata_AC_exclusive_AC_with int not null,
- AC_exclusive_AC_with_ChangedAt datetime not null,
- AC_exclusive_AC_with_Version bigint not null,
- AC_exclusive_AC_with_StatementType char(1) not null,
- AC_ID_exclusive int not null,
- AC_ID_with int not null,
- primary key (
- AC_exclusive_AC_with_Version,
- AC_ID_exclusive,
- AC_ID_with
- )
- );
- INSERT INTO @inserted
- SELECT
- ISNULL(i.Metadata_AC_exclusive_AC_with, 0),
- ISNULL(i.AC_exclusive_AC_with_ChangedAt, @now),
- DENSE_RANK() OVER (
- PARTITION BY
- AC_ID_exclusive,
- AC_ID_with
- ORDER BY
- ISNULL(i.AC_exclusive_AC_with_ChangedAt, @now)
- ),
- 'X',
- i.AC_ID_exclusive,
- i.AC_ID_with
- FROM
- inserted i
- WHERE
- i.AC_ID_exclusive is not null
- AND
- i.AC_ID_with is not null;
- SELECT
- @maxVersion = max(AC_exclusive_AC_with_Version),
- @currentVersion = 0
- FROM
- @inserted;
- WHILE (@currentVersion < @maxVersion)
- BEGIN
- SET @currentVersion = @currentVersion + 1;
- UPDATE v
- SET
- v.AC_exclusive_AC_with_StatementType =
- CASE
- WHEN tie.AC_exclusive_AC_with_ChangedAt is not null
- THEN 'D' -- duplicate
- WHEN [dbo].[rfAC_exclusive_AC_with] (
- v.AC_ID_exclusive,
- v.AC_ID_with,
- v.AC_exclusive_AC_with_ChangedAt
- ) > 0
- THEN 'R' -- restatement
- ELSE 'N' -- new statement
- END
- FROM
- @inserted v
- LEFT JOIN
- [dbo].[AC_exclusive_AC_with] tie
- ON
- tie.AC_exclusive_AC_with_ChangedAt = v.AC_exclusive_AC_with_ChangedAt
- AND
- tie.AC_ID_exclusive = v.AC_ID_exclusive
- AND
- tie.AC_ID_with = v.AC_ID_with
- WHERE
- v.AC_exclusive_AC_with_Version = @currentVersion;
- INSERT INTO [dbo].[AC_exclusive_AC_with] (
- Metadata_AC_exclusive_AC_with,
- AC_ID_exclusive,
- AC_ID_with
- )
- SELECT
- Metadata_AC_exclusive_AC_with,
- AC_ID_exclusive,
- AC_ID_with
- FROM
- @inserted
- WHERE
- AC_exclusive_AC_with_Version = @currentVersion
- AND
- AC_exclusive_AC_with_StatementType in ('N');
- END
- END
- GO
- -- UPDATE trigger -----------------------------------------------------------------------------------------------------
- -- utAC_exclusive_AC_with instead of UPDATE trigger on lAC_exclusive_AC_with
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[utAC_exclusive_AC_with] ON [dbo].[lAC_exclusive_AC_with]
- INSTEAD OF UPDATE
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- INSERT INTO [dbo].[AC_exclusive_AC_with] (
- Metadata_AC_exclusive_AC_with,
- AC_ID_exclusive,
- AC_ID_with,
- AC_exclusive_AC_with_ChangedAt
- )
- SELECT
- i.Metadata_AC_exclusive_AC_with,
- i.AC_ID_exclusive,
- i.AC_ID_with,
- CASE WHEN UPDATE(AC_exclusive_AC_with_ChangedAt) THEN i.AC_exclusive_AC_with_ChangedAt ELSE @now END
- FROM
- inserted i
- LEFT JOIN
- [dbo].[AC_exclusive_AC_with] tie
- ON
- tie.AC_ID_exclusive = i.AC_ID_exclusive
- OR
- tie.AC_ID_with = i.AC_ID_with
- WHERE
- tie.AC_ID_with is null
- AND
- [dbo].[rfAC_exclusive_AC_with](
- i.AC_ID_exclusive,
- i.AC_ID_with,
- i.AC_exclusive_AC_with_ChangedAt
- ) = 0;
- END
- GO
- -- DELETE trigger -----------------------------------------------------------------------------------------------------
- -- dtAC_exclusive_AC_with instead of DELETE trigger on lAC_exclusive_AC_with
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[dtAC_exclusive_AC_with] ON [dbo].[lAC_exclusive_AC_with]
- INSTEAD OF DELETE
- AS
- BEGIN
- SET NOCOUNT ON;
- DELETE tie
- FROM
- [dbo].[AC_exclusive_AC_with] tie
- JOIN
- deleted d
- ON
- d.AC_exclusive_AC_with_ChangedAt = tie.AC_exclusive_AC_with_ChangedAt
- AND
- (
- d.AC_ID_exclusive = tie.AC_ID_exclusive
- OR
- d.AC_ID_with = tie.AC_ID_with
- );
- END
- GO
- -- Insert trigger -----------------------------------------------------------------------------------------------------
- -- itPE_subset_EV_of instead of INSERT trigger on lPE_subset_EV_of
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[itPE_subset_EV_of] ON [dbo].[lPE_subset_EV_of]
- INSTEAD OF INSERT
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- DECLARE @maxVersion int;
- DECLARE @currentVersion int;
- DECLARE @inserted TABLE (
- Metadata_PE_subset_EV_of int not null,
- PE_ID_subset int not null,
- EV_ID_of int not null,
- primary key (
- PE_ID_subset,
- EV_ID_of
- )
- );
- INSERT INTO @inserted
- SELECT
- ISNULL(i.Metadata_PE_subset_EV_of, 0),
- i.PE_ID_subset,
- i.EV_ID_of
- FROM
- inserted i
- WHERE
- i.PE_ID_subset is not null
- AND
- i.EV_ID_of is not null;
- INSERT INTO [dbo].[PE_subset_EV_of] (
- Metadata_PE_subset_EV_of,
- PE_ID_subset,
- EV_ID_of
- )
- SELECT
- i.Metadata_PE_subset_EV_of,
- i.PE_ID_subset,
- i.EV_ID_of
- FROM
- @inserted i
- LEFT JOIN
- [dbo].[PE_subset_EV_of] tie
- ON
- tie.PE_ID_subset = i.PE_ID_subset
- OR
- tie.EV_ID_of = i.EV_ID_of
- WHERE
- tie.EV_ID_of is null;
- END
- GO
- -- DELETE trigger -----------------------------------------------------------------------------------------------------
- -- dtPE_subset_EV_of instead of DELETE trigger on lPE_subset_EV_of
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[dtPE_subset_EV_of] ON [dbo].[lPE_subset_EV_of]
- INSTEAD OF DELETE
- AS
- BEGIN
- SET NOCOUNT ON;
- DELETE tie
- FROM
- [dbo].[PE_subset_EV_of] tie
- JOIN
- deleted d
- ON
- (
- d.PE_ID_subset = tie.PE_ID_subset
- OR
- d.EV_ID_of = tie.EV_ID_of
- );
- END
- GO
- -- Insert trigger -----------------------------------------------------------------------------------------------------
- -- itPE_in_AC_wasCast instead of INSERT trigger on lPE_in_AC_wasCast
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[itPE_in_AC_wasCast] ON [dbo].[lPE_in_AC_wasCast]
- INSTEAD OF INSERT
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- DECLARE @maxVersion int;
- DECLARE @currentVersion int;
- DECLARE @inserted TABLE (
- Metadata_PE_in_AC_wasCast int not null,
- PE_ID_in int not null,
- AC_ID_wasCast int not null,
- primary key (
- PE_ID_in,
- AC_ID_wasCast
- )
- );
- INSERT INTO @inserted
- SELECT
- ISNULL(i.Metadata_PE_in_AC_wasCast, 0),
- i.PE_ID_in,
- i.AC_ID_wasCast
- FROM
- inserted i
- WHERE
- i.PE_ID_in is not null
- AND
- i.AC_ID_wasCast is not null;
- INSERT INTO [dbo].[PE_in_AC_wasCast] (
- Metadata_PE_in_AC_wasCast,
- PE_ID_in,
- AC_ID_wasCast
- )
- SELECT
- i.Metadata_PE_in_AC_wasCast,
- i.PE_ID_in,
- i.AC_ID_wasCast
- FROM
- @inserted i
- LEFT JOIN
- [dbo].[PE_in_AC_wasCast] tie
- ON
- tie.PE_ID_in = i.PE_ID_in
- AND
- tie.AC_ID_wasCast = i.AC_ID_wasCast
- WHERE
- tie.AC_ID_wasCast is null;
- END
- GO
- -- DELETE trigger -----------------------------------------------------------------------------------------------------
- -- dtPE_in_AC_wasCast instead of DELETE trigger on lPE_in_AC_wasCast
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[dtPE_in_AC_wasCast] ON [dbo].[lPE_in_AC_wasCast]
- INSTEAD OF DELETE
- AS
- BEGIN
- SET NOCOUNT ON;
- DELETE tie
- FROM
- [dbo].[PE_in_AC_wasCast] tie
- JOIN
- deleted d
- ON
- d.PE_ID_in = tie.PE_ID_in
- AND
- d.AC_ID_wasCast = tie.AC_ID_wasCast;
- END
- GO
- -- Insert trigger -----------------------------------------------------------------------------------------------------
- -- itAC_part_PR_in_RAT_got instead of INSERT trigger on lAC_part_PR_in_RAT_got
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[itAC_part_PR_in_RAT_got] ON [dbo].[lAC_part_PR_in_RAT_got]
- INSTEAD OF INSERT
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- DECLARE @maxVersion int;
- DECLARE @currentVersion int;
- DECLARE @inserted TABLE (
- Metadata_AC_part_PR_in_RAT_got int not null,
- AC_part_PR_in_RAT_got_ChangedAt datetime not null,
- AC_part_PR_in_RAT_got_Version bigint not null,
- AC_part_PR_in_RAT_got_StatementType char(1) not null,
- AC_ID_part int not null,
- PR_ID_in int not null,
- RAT_ID_got tinyint not null,
- primary key (
- AC_part_PR_in_RAT_got_Version,
- AC_ID_part,
- PR_ID_in
- )
- );
- INSERT INTO @inserted
- SELECT
- ISNULL(i.Metadata_AC_part_PR_in_RAT_got, 0),
- ISNULL(i.AC_part_PR_in_RAT_got_ChangedAt, @now),
- DENSE_RANK() OVER (
- PARTITION BY
- AC_ID_part,
- PR_ID_in
- ORDER BY
- ISNULL(i.AC_part_PR_in_RAT_got_ChangedAt, @now)
- ),
- 'X',
- i.AC_ID_part,
- i.PR_ID_in,
- ISNULL(i.RAT_ID_got, [RAT_got].RAT_ID)
- FROM
- inserted i
- LEFT JOIN
- [dbo].[RAT_Rating] [RAT_got]
- ON
- [RAT_got].RAT_Rating = i.got_RAT_Rating
- WHERE
- i.AC_ID_part is not null
- AND
- i.PR_ID_in is not null;
- SELECT
- @maxVersion = max(AC_part_PR_in_RAT_got_Version),
- @currentVersion = 0
- FROM
- @inserted;
- WHILE (@currentVersion < @maxVersion)
- BEGIN
- SET @currentVersion = @currentVersion + 1;
- UPDATE v
- SET
- v.AC_part_PR_in_RAT_got_StatementType =
- CASE
- WHEN tie.AC_part_PR_in_RAT_got_ChangedAt is not null
- THEN 'D' -- duplicate
- WHEN [dbo].[rfAC_part_PR_in_RAT_got] (
- v.AC_ID_part,
- v.PR_ID_in,
- v.RAT_ID_got,
- v.AC_part_PR_in_RAT_got_ChangedAt
- ) > 0
- THEN 'R' -- restatement
- ELSE 'N' -- new statement
- END
- FROM
- @inserted v
- LEFT JOIN
- [dbo].[AC_part_PR_in_RAT_got] tie
- ON
- tie.AC_part_PR_in_RAT_got_ChangedAt = v.AC_part_PR_in_RAT_got_ChangedAt
- AND
- tie.AC_ID_part = v.AC_ID_part
- AND
- tie.PR_ID_in = v.PR_ID_in
- AND
- tie.RAT_ID_got = v.RAT_ID_got
- WHERE
- v.AC_part_PR_in_RAT_got_Version = @currentVersion;
- INSERT INTO [dbo].[AC_part_PR_in_RAT_got] (
- Metadata_AC_part_PR_in_RAT_got,
- AC_ID_part,
- PR_ID_in,
- RAT_ID_got
- )
- SELECT
- Metadata_AC_part_PR_in_RAT_got,
- AC_ID_part,
- PR_ID_in,
- RAT_ID_got
- FROM
- @inserted
- WHERE
- AC_part_PR_in_RAT_got_Version = @currentVersion
- AND
- AC_part_PR_in_RAT_got_StatementType in ('N');
- END
- END
- GO
- -- UPDATE trigger -----------------------------------------------------------------------------------------------------
- -- utAC_part_PR_in_RAT_got instead of UPDATE trigger on lAC_part_PR_in_RAT_got
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[utAC_part_PR_in_RAT_got] ON [dbo].[lAC_part_PR_in_RAT_got]
- INSTEAD OF UPDATE
- AS
- BEGIN
- SET NOCOUNT ON;
- DECLARE @now datetime2(7) = sysdatetime();
- IF(UPDATE(AC_ID_part))
- RAISERROR('The identity column AC_ID_part is not updatable.', 16, 1);
- IF(UPDATE(PR_ID_in))
- RAISERROR('The identity column PR_ID_in is not updatable.', 16, 1);
- INSERT INTO [dbo].[AC_part_PR_in_RAT_got] (
- Metadata_AC_part_PR_in_RAT_got,
- AC_ID_part,
- PR_ID_in,
- RAT_ID_got,
- AC_part_PR_in_RAT_got_ChangedAt
- )
- SELECT
- i.Metadata_AC_part_PR_in_RAT_got,
- i.AC_ID_part,
- i.PR_ID_in,
- i.RAT_ID_got,
- CASE WHEN UPDATE(AC_part_PR_in_RAT_got_ChangedAt) THEN i.AC_part_PR_in_RAT_got_ChangedAt ELSE @now END
- FROM
- inserted i
- LEFT JOIN
- [dbo].[AC_part_PR_in_RAT_got] tie
- ON
- tie.AC_ID_part = i.AC_ID_part
- AND
- tie.PR_ID_in = i.PR_ID_in
- WHERE
- tie.PR_ID_in is null
- AND
- [dbo].[rfAC_part_PR_in_RAT_got](
- i.AC_ID_part,
- i.PR_ID_in,
- i.RAT_ID_got,
- i.AC_part_PR_in_RAT_got_ChangedAt
- ) = 0;
- END
- GO
- -- DELETE trigger -----------------------------------------------------------------------------------------------------
- -- dtAC_part_PR_in_RAT_got instead of DELETE trigger on lAC_part_PR_in_RAT_got
- -----------------------------------------------------------------------------------------------------------------------
- CREATE TRIGGER [dbo].[dtAC_part_PR_in_RAT_got] ON [dbo].[lAC_part_PR_in_RAT_got]
- INSTEAD OF DELETE
- AS
- BEGIN
- SET NOCOUNT ON;
- DELETE tie
- FROM
- [dbo].[AC_part_PR_in_RAT_got] tie
- JOIN
- deleted d
- ON
- d.AC_part_PR_in_RAT_got_ChangedAt = tie.AC_part_PR_in_RAT_got_ChangedAt
- AND
- d.AC_ID_part = tie.AC_ID_part
- AND
- d.PR_ID_in = tie.PR_ID_in;
- END
- GO
- -- SCHEMA EVOLUTION ---------------------------------------------------------------------------------------------------
- --
- -- The following tables, views, and functions are used to track schema changes
- -- over time, as well as providing every XML that has been 'executed' against
- -- the database.
- --
- -- Schema table -------------------------------------------------------------------------------------------------------
- -- The schema table holds every xml that has been executed against the database
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('_Schema', 'U') IS NULL
- CREATE TABLE [dbo].[_Schema] (
- [version] int identity(1, 1) not null primary key,
- [activation] datetime2(7) not null,
- [schema] xml not null
- );
- GO
- -- Insert the XML schema (as of now)
- INSERT INTO [dbo].[_Schema] (
- [activation],
- [schema]
- )
- SELECT
- current_timestamp,
- N'<schema><metadata format="0.97" temporalization="uni"/><knot mnemonic="PAT" descriptor="ParentalType" identity="tinyint" dataRange="varchar(42)"><metadata capsule="dbo" generator="false"/><layout x="616.06" y="383.39" fixed="false"/></knot><knot mnemonic="PLV" descriptor="ProfessionalLevel" identity="tinyint" dataRange="varchar(42)"><metadata capsule="dbo" generator="false"/><layout x="547.64" y="327.96" fixed="false"/></knot><knot mnemonic="RAT" descriptor="Rating" identity="tinyint" dataRange="varchar(42)"><metadata capsule="dbo" generator="false"/><layout x="867.37" y="247.09" fixed="false"/></knot><knot mnemonic="GEN" descriptor="Gender" identity="bit" dataRange="varchar(42)"><metadata capsule="dbo" generator="false"/><layout x="580.04" y="194.93" fixed="false"/></knot><anchor mnemonic="EV" descriptor="Event" identity="int"><metadata capsule="dbo" generator="true"/><layout x="1112.63" y="240.76" fixed="false"/></anchor><anchor mnemonic="PR" descriptor="Program" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="NAM" descriptor="Name" dataRange="varchar(42)"><metadata capsule="dbo"/><layout x="993.36" y="419.62" fixed="false"/></attribute><layout x="999.17" y="337.14" fixed="false"/></anchor><anchor mnemonic="ST" descriptor="Stage" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="NAM" descriptor="Name" timeRange="datetime" dataRange="varchar(42)"><metadata capsule="dbo" restatable="false" idempotent="true"/><layout x="1249.50" y="364.75" fixed="false"/></attribute><attribute mnemonic="LOC" descriptor="Location" dataRange="varchar(42)"><metadata capsule="dbo"/><layout x="1292.59" y="308.91" fixed="false"/></attribute><layout x="1200.71" y="316.61" fixed="false"/></anchor><anchor mnemonic="PE" descriptor="Performance" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="DAT" descriptor="Date" dataRange="datetime"><metadata capsule="dbo"/><layout x="1087.90" y="16.92" fixed="false"/></attribute><attribute mnemonic="AUD" descriptor="Audience" dataRange="int"><metadata capsule="dbo"/><layout x="1055.59" y="36.48" fixed="false"/></attribute><attribute mnemonic="REV" descriptor="Revenue" dataRange="money"><metadata capsule="dbo"/><layout x="1136.77" y="59.82" fixed="false"/></attribute><layout x="1078.79" y="111.16" fixed="false"/></anchor><anchor mnemonic="AC" descriptor="Actor" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="NAM" descriptor="Name" timeRange="datetime" dataRange="varchar(42)"><metadata capsule="dbo" restatable="false" idempotent="true"/><layout x="771.78" y="342.28" fixed="false"/></attribute><attribute mnemonic="GEN" descriptor="Gender" knotRange="GEN"><metadata capsule="dbo"/><layout x="642.32" y="227.10" fixed="false"/></attribute><attribute mnemonic="PLV" descriptor="ProfessionalLevel" timeRange="datetime" knotRange="PLV"><metadata capsule="dbo" restatable="false" idempotent="true"/><layout x="634.20" y="311.32" fixed="false"/></attribute><layout x="738.14" y="263.82" fixed="false"/></anchor><tie><anchorRole role="parent" type="AC" identifier="true"/><anchorRole role="child" type="AC" identifier="true"/><knotRole role="having" type="PAT" identifier="true"/><metadata capsule="dbo"/><layout x="679.70" y="337.17" fixed="false"/></tie><tie timeRange="datetime"><anchorRole role="atLocation" type="ST" identifier="true"/><anchorRole role="isPlaying" type="PR" identifier="true"/><metadata capsule="dbo" restatable="false" idempotent="true"/><layout x="1093.51" y="335.53" fixed="false"/></tie><tie><anchorRole role="at" type="PE" identifier="true"/><anchorRole role="wasPlayed" type="PR" identifier="false"/><metadata capsule="dbo"/><layout x="1034.95" y="219.71" fixed="false"/></tie><tie><anchorRole role="wasHeld" type="PE" identifier="true"/><anchorRole role="atLocation" type="ST" identifier="false"/><metadata capsule="dbo"/><layout x="1173.89" y="210.84" fixed="false"/></tie><tie timeRange="datetime"><anchorRole role="exclusive" type="AC" identifier="false"/><anchorRole role="with" type="AC" identifier="false"/><metadata capsule="dbo" restatable="false" idempotent="true"/><layout x="720.47" y="193.69" fixed="false"/></tie><tie><anchorRole role="subset" type="PE" identifier="false"/><anchorRole role="of" type="EV" identifier="false"/><metadata capsule="dbo"/><layout x="1073.97" y="201.16" fixed="false"/></tie><tie><anchorRole role="in" type="PE" identifier="true"/><anchorRole role="wasCast" type="AC" identifier="true"/><metadata capsule="dbo"/><layout x="896.09" y="183.39" fixed="false"/></tie><tie timeRange="datetime"><anchorRole role="part" type="AC" identifier="true"/><anchorRole role="in" type="PR" identifier="true"/><knotRole role="got" type="RAT" identifier="false"/><metadata capsule="dbo" restatable="false" idempotent="true"/><layout x="864.02" y="322.19" fixed="false"/></tie></schema>';
- GO
- -- Anchor view --------------------------------------------------------------------------------------------------------
- -- The anchor view shows information about all the anchors in a schema
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('_Anchor', 'V') IS NOT NULL
- DROP VIEW [dbo].[_Anchor]
- GO
- CREATE VIEW [dbo].[_Anchor]
- AS
- SELECT
- S.version,
- S.activation,
- Nodeset.anchor.value('concat(@mnemonic, "_", @descriptor)', 'nvarchar(max)') as [name],
- Nodeset.anchor.value('metadata[1]/@capsule', 'nvarchar(max)') as [capsule],
- Nodeset.anchor.value('@mnemonic', 'nvarchar(max)') as [mnemonic],
- Nodeset.anchor.value('@descriptor', 'nvarchar(max)') as [descriptor],
- Nodeset.anchor.value('@identity', 'nvarchar(max)') as [identity],
- Nodeset.anchor.value('metadata[1]/@generator', 'nvarchar(max)') as [generator],
- Nodeset.anchor.value('count(attribute)', 'int') as [numberOfAttributes]
- FROM
- [dbo].[_Schema] S
- CROSS APPLY
- S.[schema].nodes('/schema/anchor') as Nodeset(anchor);
- GO
- -- Knot view ----------------------------------------------------------------------------------------------------------
- -- The knot view shows information about all the knots in a schema
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('_Knot', 'V') IS NOT NULL
- DROP VIEW [dbo].[_Knot]
- GO
- CREATE VIEW [dbo].[_Knot]
- AS
- SELECT
- S.version,
- S.activation,
- Nodeset.knot.value('concat(@mnemonic, "_", @descriptor)', 'nvarchar(max)') as [name],
- Nodeset.knot.value('metadata[1]/@capsule', 'nvarchar(max)') as [capsule],
- Nodeset.knot.value('@mnemonic', 'nvarchar(max)') as [mnemonic],
- Nodeset.knot.value('@descriptor', 'nvarchar(max)') as [descriptor],
- Nodeset.knot.value('@identity', 'nvarchar(max)') as [identity],
- Nodeset.knot.value('metadata[1]/@generator', 'nvarchar(max)') as [generator],
- Nodeset.knot.value('@dataRange', 'nvarchar(max)') as [dataRange]
- FROM
- [dbo].[_Schema] S
- CROSS APPLY
- S.[schema].nodes('/schema/knot') as Nodeset(knot);
- GO
- -- Attribute view -----------------------------------------------------------------------------------------------------
- -- The attribute view shows information about all the attributes in a schema
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('_Attribute', 'V') IS NOT NULL
- DROP VIEW [dbo].[_Attribute]
- GO
- CREATE VIEW [dbo].[_Attribute]
- AS
- SELECT
- S.version,
- S.activation,
- ParentNodeset.anchor.value('concat(@mnemonic, "_")', 'nvarchar(max)') +
- Nodeset.attribute.value('concat(@mnemonic, "_")', 'nvarchar(max)') +
- ParentNodeset.anchor.value('concat(@descriptor, "_")', 'nvarchar(max)') +
- Nodeset.attribute.value('@descriptor', 'nvarchar(max)') as [name],
- Nodeset.attribute.value('metadata[1]/@capsule', 'nvarchar(max)') as [capsule],
- Nodeset.attribute.value('@mnemonic', 'nvarchar(max)') as [mnemonic],
- Nodeset.attribute.value('@descriptor', 'nvarchar(max)') as [descriptor],
- ParentNodeset.anchor.value('@mnemonic', 'nvarchar(max)') as [anchorMnemonic],
- ParentNodeset.anchor.value('@descriptor', 'nvarchar(max)') as [anchorDescriptor],
- ParentNodeset.anchor.value('@identity', 'nvarchar(max)') as [anchorIdentity],
- Nodeset.attribute.value('@dataRange', 'nvarchar(max)') as [dataRange],
- Nodeset.attribute.value('@knotRange', 'nvarchar(max)') as [knotRange],
- Nodeset.attribute.value('@timeRange', 'nvarchar(max)') as [timeRange]
- FROM
- [dbo].[_Schema] S
- CROSS APPLY
- S.[schema].nodes('/schema/anchor') as ParentNodeset(anchor)
- OUTER APPLY
- ParentNodeset.anchor.nodes('attribute') as Nodeset(attribute);
- GO
- -- Tie view -----------------------------------------------------------------------------------------------------------
- -- The tie view shows information about all the ties in a schema
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('_Tie', 'V') IS NOT NULL
- DROP VIEW [dbo].[_Tie]
- GO
- CREATE VIEW [dbo].[_Tie]
- AS
- SELECT
- S.version,
- S.activation,
- REPLACE(Nodeset.tie.query('
- for $role in *[local-name() = "anchorRole" or local-name() = "knotRole"]
- return concat($role/@type, "_", $role/@role)
- ').value('.', 'nvarchar(max)'), ' ', '_') as [name],
- Nodeset.tie.value('metadata[1]/@capsule', 'nvarchar(max)') as [capsule],
- Nodeset.tie.value('count(anchorRole) + count(knotRole)', 'int') as [numberOfRoles],
- Nodeset.tie.query('
- for $role in *[local-name() = "anchorRole" or local-name() = "knotRole"]
- return string($role/@role)
- ').value('.', 'nvarchar(max)') as [roles],
- Nodeset.tie.value('count(anchorRole)', 'int') as [numberOfAnchors],
- Nodeset.tie.query('
- for $role in anchorRole
- return string($role/@type)
- ').value('.', 'nvarchar(max)') as [anchors],
- Nodeset.tie.value('count(knotRole)', 'int') as [numberOfKnots],
- Nodeset.tie.query('
- for $role in knotRole
- return string($role/@type)
- ').value('.', 'nvarchar(max)') as [knots],
- Nodeset.tie.value('count(*[local-name() = "anchorRole" or local-name() = "knotRole"][@identifier = "true"])', 'int') as [numberOfIdentifiers],
- Nodeset.tie.query('
- for $role in *[local-name() = "anchorRole" or local-name() = "knotRole"][@identifier = "true"]
- return string($role/@type)
- ').value('.', 'nvarchar(max)') as [identifiers],
- Nodeset.tie.value('@timeRange', 'nvarchar(max)') as [timeRange]
- FROM
- [dbo].[_Schema] S
- CROSS APPLY
- S.[schema].nodes('/schema/tie') as Nodeset(tie);
- GO
- -- Evolution function -------------------------------------------------------------------------------------------------
- -- The evolution function shows what the schema looked like at the given
- -- point in time with additional information about missing or added
- -- modeling components since that time.
- --
- -- @timepoint The point in time to which you would like to travel.
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('_Evolution', 'IF') IS NOT NULL
- DROP FUNCTION [dbo].[_Evolution];
- GO
- CREATE FUNCTION [dbo].[_Evolution] (
- @timepoint AS DATETIME2(7)
- )
- RETURNS TABLE
- RETURN
- SELECT
- V.[version],
- ISNULL(S.[name], T.[name]) AS [name],
- ISNULL(V.[activation], T.[create_date]) AS [activation],
- CASE
- WHEN S.[name] is null THEN
- CASE
- WHEN T.[create_date] > (
- SELECT
- ISNULL(MAX([activation]), @timepoint)
- FROM
- [dbo].[_Schema]
- WHERE
- [activation] <= @timepoint
- ) THEN 'Future'
- ELSE 'Past'
- END
- WHEN T.[name] is null THEN 'Missing'
- ELSE 'Present'
- END AS Existence
- FROM (
- SELECT
- MAX([version]) as [version],
- MAX([activation]) as [activation]
- FROM
- [dbo].[_Schema]
- WHERE
- [activation] <= @timepoint
- ) V
- JOIN (
- SELECT
- [name],
- [version]
- FROM
- [dbo].[_Anchor] a
- UNION ALL
- SELECT
- [name],
- [version]
- FROM
- [dbo].[_Knot] k
- UNION ALL
- SELECT
- [name],
- [version]
- FROM
- [dbo].[_Attribute] b
- UNION ALL
- SELECT
- [name],
- [version]
- FROM
- [dbo].[_Tie] t
- ) S
- ON
- S.[version] = V.[version]
- FULL OUTER JOIN (
- SELECT
- [name],
- [create_date]
- FROM
- sys.tables
- WHERE
- [type] like '%U%'
- AND
- LEFT([name], 1) <> '_'
- ) T
- ON
- S.[name] = T.[name];
- GO
- -- Drop Script Generator ----------------------------------------------------------------------------------------------
- -- generates a drop script, that must be run separately, dropping everything in an Anchor Modeled database
- -----------------------------------------------------------------------------------------------------------------------
- IF Object_ID('_GenerateDropScript', 'P') IS NOT NULL
- DROP PROCEDURE [dbo].[_GenerateDropScript];
- GO
- CREATE PROCEDURE [dbo]._GenerateDropScript (
- @exclusionPattern varchar(42) = '[_]%' -- exclude Metadata by default
- )
- AS
- BEGIN
- WITH objects AS (
- SELECT
- 'DROP ' + ft.[type] + ' ' + fn.[name] + '; -- ' + fn.[description] as [statement],
- row_number() OVER (
- ORDER BY
- -- order based on type
- CASE ft.[type]
- WHEN 'PROCEDURE' THEN 1
- WHEN 'FUNCTION' THEN 2
- WHEN 'VIEW' THEN 3
- WHEN 'TABLE' THEN 4
- ELSE 5
- END ASC,
- -- order within type
- CASE dc.[description]
- WHEN 'key generator' THEN 1
- WHEN 'latest perspective' THEN 2
- WHEN 'current perspective' THEN 3
- WHEN 'difference perspective' THEN 4
- WHEN 'point-in-time perspective' THEN 5
- WHEN 'time traveler' THEN 6
- WHEN 'rewinder' THEN 7
- WHEN 'assembled view' THEN 8
- WHEN 'annex table' THEN 9
- WHEN 'posit table' THEN 10
- WHEN 'table' THEN 11
- WHEN 'restatement finder' THEN 12
- ELSE 13
- END,
- -- order within description
- CASE ft.[type]
- WHEN 'TABLE' THEN
- CASE cl.[class]
- WHEN 'Attribute' THEN 1
- WHEN 'Attribute Annex' THEN 2
- WHEN 'Attribute Posit' THEN 3
- WHEN 'Tie' THEN 4
- WHEN 'Anchor' THEN 5
- WHEN 'Knot' THEN 6
- ELSE 7
- END
- ELSE
- CASE cl.[class]
- WHEN 'Anchor' THEN 1
- WHEN 'Attribute' THEN 2
- WHEN 'Attribute Annex' THEN 3
- WHEN 'Attribute Posit' THEN 4
- WHEN 'Tie' THEN 5
- WHEN 'Knot' THEN 6
- ELSE 7
- END
- END,
- -- finally alphabetically
- o.[name] ASC
- ) AS [ordinal]
- FROM
- sys.objects o
- JOIN
- sys.schemas s
- ON
- s.[schema_id] = o.[schema_id]
- CROSS APPLY (
- SELECT
- CASE
- WHEN o.[name] LIKE '[_]%'
- COLLATE Latin1_General_BIN THEN 'Metadata'
- WHEN o.[name] LIKE '%[A-Z][A-Z][_][a-z]%[A-Z][A-Z][_][a-z]%'
- COLLATE Latin1_General_BIN THEN 'Tie'
- WHEN o.[name] LIKE '%[A-Z][A-Z][_][A-Z][A-Z][A-Z][_][A-Z]%[_]%'
- COLLATE Latin1_General_BIN THEN 'Attribute'
- WHEN o.[name] LIKE '%[A-Z][A-Z][A-Z][_][A-Z]%'
- COLLATE Latin1_General_BIN THEN 'Knot'
- WHEN o.[name] LIKE '%[A-Z][A-Z][_][A-Z]%'
- COLLATE Latin1_General_BIN THEN 'Anchor'
- ELSE 'Other'
- END
- ) cl ([class])
- CROSS APPLY (
- SELECT
- CASE o.[type]
- WHEN 'P' THEN 'PROCEDURE'
- WHEN 'IF' THEN 'FUNCTION'
- WHEN 'FN' THEN 'FUNCTION'
- WHEN 'V' THEN 'VIEW'
- WHEN 'U' THEN 'TABLE'
- END
- ) ft ([type])
- CROSS APPLY (
- SELECT
- CASE
- WHEN ft.[type] = 'PROCEDURE' AND cl.[class] = 'Anchor' AND o.[name] LIKE 'k%'
- COLLATE Latin1_General_BIN THEN 'key generator'
- WHEN ft.[type] = 'FUNCTION' AND o.[name] LIKE 't%'
- COLLATE Latin1_General_BIN THEN 'time traveler'
- WHEN ft.[type] = 'FUNCTION' AND o.[name] LIKE 'rf%'
- COLLATE Latin1_General_BIN THEN 'restatement finder'
- WHEN ft.[type] = 'FUNCTION' AND o.[name] LIKE 'r%'
- COLLATE Latin1_General_BIN THEN 'rewinder'
- WHEN ft.[type] = 'VIEW' AND o.[name] LIKE 'l%'
- COLLATE Latin1_General_BIN THEN 'latest perspective'
- WHEN ft.[type] = 'FUNCTION' AND o.[name] LIKE 'p%'
- COLLATE Latin1_General_BIN THEN 'point-in-time perspective'
- WHEN ft.[type] = 'VIEW' AND o.[name] LIKE 'n%'
- COLLATE Latin1_General_BIN THEN 'current perspective'
- WHEN ft.[type] = 'FUNCTION' AND o.[name] LIKE 'd%'
- COLLATE Latin1_General_BIN THEN 'difference perspective'
- WHEN ft.[type] = 'VIEW' AND cl.[class] = 'Attribute'
- COLLATE Latin1_General_BIN THEN 'assembled view'
- WHEN ft.[type] = 'TABLE' AND o.[name] LIKE '%Annex'
- COLLATE Latin1_General_BIN THEN 'annex table'
- WHEN ft.[type] = 'TABLE' AND o.[name] LIKE '%Posit'
- COLLATE Latin1_General_BIN THEN 'posit table'
- WHEN ft.[type] = 'TABLE'
- COLLATE Latin1_General_BIN THEN 'table'
- ELSE 'other'
- END
- ) dc ([description])
- CROSS APPLY (
- SELECT
- s.[name] + '.' + o.[name],
- cl.[class] + ' ' + dc.[description]
- ) fn ([name], [description])
- WHERE
- o.[type] IN ('P', 'IF', 'FN', 'V', 'U')
- AND
- o.[name] NOT LIKE ISNULL(@exclusionPattern, '')
- )
- SELECT
- [statement] + CHAR(13) as [text()]
- FROM
- objects
- ORDER BY
- [ordinal]
- FOR XML PATH('');
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement