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