---------------------------- SETUP DATABASE AND MODEL --------------------------------
USE master;
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'ComplexTest')
BEGIN
ALTER DATABASE ComplexTest SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE ComplexTest SET ONLINE;
DROP DATABASE ComplexTest
END
CREATE DATABASE ComplexTest;
USE ComplexTest;
-------------------------------- [Drop Perspectives] ---------------------------------
-- perspectives are recreated every time the script is run
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dCO_Company' and type LIKE '%F%')
DROP FUNCTION [dbo].[dCO_Company];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'pCO_Company' AND type LIKE '%F%')
DROP FUNCTION [dbo].[pCO_Company];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lCO_Company' AND type LIKE '%V%')
DROP VIEW [dbo].[lCO_Company];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'llCO_Company')
DROP SYNONYM [dbo].[llCO_Company];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lpCO_Company' and type LIKE '%F%')
DROP FUNCTION [dbo].[lpCO_Company];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'plCO_Company')
DROP SYNONYM [dbo].[plCO_Company];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ppCO_Company' and type LIKE '%F%')
DROP FUNCTION [dbo].[ppCO_Company];
GO
-------------------------------- [Drop Perspectives] ---------------------------------
-- perspectives are recreated every time the script is run
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dCU_Customer' and type LIKE '%F%')
DROP FUNCTION [dbo].[dCU_Customer];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'pCU_Customer' AND type LIKE '%F%')
DROP FUNCTION [dbo].[pCU_Customer];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lCU_Customer' AND type LIKE '%V%')
DROP VIEW [dbo].[lCU_Customer];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'llCU_Customer')
DROP SYNONYM [dbo].[llCU_Customer];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lpCU_Customer' and type LIKE '%F%')
DROP FUNCTION [dbo].[lpCU_Customer];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'plCU_Customer')
DROP SYNONYM [dbo].[plCU_Customer];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ppCU_Customer' and type LIKE '%F%')
DROP FUNCTION [dbo].[ppCU_Customer];
GO
-------------------------------- [Drop Perspectives] ---------------------------------
-- perspectives are recreated every time the script is run
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dPO_Position' and type LIKE '%F%')
DROP FUNCTION [dbo].[dPO_Position];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'pPO_Position' AND type LIKE '%F%')
DROP FUNCTION [dbo].[pPO_Position];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lPO_Position' AND type LIKE '%V%')
DROP VIEW [dbo].[lPO_Position];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'llPO_Position')
DROP SYNONYM [dbo].[llPO_Position];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lpPO_Position' and type LIKE '%F%')
DROP FUNCTION [dbo].[lpPO_Position];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'plPO_Position')
DROP SYNONYM [dbo].[plPO_Position];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ppPO_Position' and type LIKE '%F%')
DROP FUNCTION [dbo].[ppPO_Position];
GO
-------------------------------- [Drop Perspectives] ---------------------------------
-- perspectives are recreated every time the script is run
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dCU_is_PO_holding' and type LIKE '%F%')
DROP FUNCTION [dbo].[dCU_is_PO_holding];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'pCU_is_PO_holding' AND type LIKE '%F%')
DROP FUNCTION [dbo].[pCU_is_PO_holding];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lCU_is_PO_holding' AND type LIKE '%V%')
DROP VIEW [dbo].[lCU_is_PO_holding];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'llCU_is_PO_holding')
DROP SYNONYM [dbo].[llCU_is_PO_holding];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lpCU_is_PO_holding' and type LIKE '%F%')
DROP FUNCTION [dbo].[lpCU_is_PO_holding];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'plCU_is_PO_holding')
DROP SYNONYM [dbo].[plCU_is_PO_holding];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ppCU_is_PO_holding' and type LIKE '%F%')
DROP FUNCTION [dbo].[ppCU_is_PO_holding];
GO
-------------------------------- [Drop Perspectives] ---------------------------------
-- perspectives are recreated every time the script is run
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dPO_for_CO_the' and type LIKE '%F%')
DROP FUNCTION [dbo].[dPO_for_CO_the];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'pPO_for_CO_the' AND type LIKE '%F%')
DROP FUNCTION [dbo].[pPO_for_CO_the];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lPO_for_CO_the' AND type LIKE '%V%')
DROP VIEW [dbo].[lPO_for_CO_the];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'llPO_for_CO_the')
DROP SYNONYM [dbo].[llPO_for_CO_the];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lpPO_for_CO_the' and type LIKE '%F%')
DROP FUNCTION [dbo].[lpPO_for_CO_the];
GO
IF EXISTS (SELECT * FROM sys.synonyms WHERE name = 'plPO_for_CO_the')
DROP SYNONYM [dbo].[plPO_for_CO_the];
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ppPO_for_CO_the' and type LIKE '%F%')
DROP FUNCTION [dbo].[ppPO_for_CO_the];
GO
----------------------------------- [Knot Table] -------------------------------------
-- TIC_Ticker table
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'TIC_Ticker' and type LIKE '%U%')
CREATE TABLE [dbo].[TIC_Ticker] (
TIC_ID smallint not null,
TIC_Ticker varchar(6) not null,
constraint pkTIC_Ticker primary key (
TIC_ID asc
),
constraint uqTIC_Ticker unique (
TIC_Ticker
)
);
GO
----------------------------------- [Knot Table] -------------------------------------
-- RAT_Rating table
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'RAT_Rating' and type LIKE '%U%')
CREATE TABLE [dbo].[RAT_Rating] (
RAT_ID tinyint not null,
RAT_Rating char(1) not null,
constraint pkRAT_Rating primary key (
RAT_ID asc
),
constraint uqRAT_Rating unique (
RAT_Rating
)
);
GO
---------------------------------- [Anchor Table] ------------------------------------
-- CO_Company table (with 1 attributes)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'CO_Company' AND type LIKE '%U%')
CREATE TABLE [dbo].[CO_Company] (
CO_ID int identity(1, 1) not null,
CO_Dummy bit null,
constraint pkCO_Company primary key (
CO_ID asc
)
);
GO
----------------------- [Key Generation Stored Procedure] ----------------------------
-- CO_Company surrogate key generation stored procedure
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'kCO_Company' AND type in ('P','PC'))
DROP PROCEDURE [dbo].[kCO_Company]
GO
CREATE PROCEDURE [dbo].[kCO_Company] (
@requestedNumberOfIdentities bigint
) AS
BEGIN
SET NOCOUNT ON;
IF @requestedNumberOfIdentities > 0
BEGIN
WITH rowGenerator (rowNumber) AS (
SELECT
1
UNION ALL
SELECT
rowNumber + 1
FROM
rowGenerator
WHERE
rowNumber < @requestedNumberOfIdentities
)
INSERT INTO [dbo].[CO_Company] (CO_Dummy)
OUTPUT
inserted.CO_ID
SELECT
null
FROM
rowGenerator
OPTION (maxrecursion 0);
END
END
GO
--------------------------------- [Attribute Table] ----------------------------------
-- CO_TIC_Company_Ticker table (on CO_Company)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'CO_TIC_Company_Ticker' AND type LIKE '%U%')
CREATE TABLE [dbo].[CO_TIC_Company_Ticker] (
CO_ID int not null foreign key references [dbo].[CO_Company](CO_ID),
TIC_ID smallint not null foreign key references [dbo].[TIC_Ticker](TIC_ID),
CO_TIC_RecordedAt datetime not null,
CO_TIC_ErasedAt datetime not null,
constraint uqCO_TIC_Company_Ticker unique (
CO_TIC_ErasedAt,
CO_ID
),
constraint pkCO_TIC_Company_Ticker primary key (
CO_ID asc,
CO_TIC_RecordedAt desc,
CO_TIC_ErasedAt desc
)
) ON [PRIMARY];
GO
-------------------- [All changing currently recorded perspective] -------------------
-- acCO_TIC_Company_Ticker view
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'acCO_TIC_Company_Ticker' AND type LIKE '%V%')
DROP VIEW [dbo].[acCO_TIC_Company_Ticker];
GO
CREATE VIEW [dbo].[acCO_TIC_Company_Ticker] WITH SCHEMABINDING AS
SELECT
CO_ID,
TIC_ID,
CO_TIC_RecordedAt,
CO_TIC_ErasedAt
FROM
[dbo].[CO_TIC_Company_Ticker]
WHERE
CO_TIC_ErasedAt >= '9999-12-31';
GO
--------------------- [All changing rewind recording perspective] --------------------
-- arCO_TIC_Company_Ticker function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'arCO_TIC_Company_Ticker' AND type LIKE '%F%')
DROP FUNCTION [dbo].[arCO_TIC_Company_Ticker];
GO
CREATE FUNCTION [dbo].[arCO_TIC_Company_Ticker] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
CO_ID,
TIC_ID,
CO_TIC_RecordedAt,
CO_TIC_ErasedAt
FROM
[dbo].[CO_TIC_Company_Ticker]
WHERE
CO_TIC_ErasedAt > @recordingTimepoint
AND
CO_TIC_RecordedAt <= @recordingTimepoint;
GO
------------------------------- [Latest Perspective] ---------------------------------
-- CO_Company viewed as is (given by the latest available information)
--------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lCO_Company] WITH SCHEMABINDING AS
SELECT
[CO].CO_ID,
[TIC].TIC_ID,
[TIC].TIC_Ticker,
[CO_TIC].CO_TIC_RecordedAt,
[CO_TIC].CO_TIC_ErasedAt
FROM
[dbo].[CO_Company] [CO]
LEFT JOIN
[dbo].[acCO_TIC_Company_Ticker] [CO_TIC]
ON
[CO_TIC].CO_ID = [CO].CO_ID
LEFT JOIN
[dbo].[TIC_Ticker] [TIC]
ON
[TIC].TIC_ID = [CO_TIC].TIC_ID;
GO
CREATE SYNONYM [dbo].[llCO_Company] FOR [dbo].[lCO_Company];
GO
----------------- [Latest changing point-in-recording Perspective] -------------------
-- CO_Company viewed as is at the given recording time
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[lpCO_Company] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
[CO].CO_ID,
[TIC].TIC_ID,
[TIC].TIC_Ticker,
[CO_TIC].CO_TIC_RecordedAt,
[CO_TIC].CO_TIC_ErasedAt
FROM
[dbo].[CO_Company] [CO]
LEFT JOIN
[dbo].[arCO_TIC_Company_Ticker] (@recordingTimepoint) [CO_TIC]
ON
[CO_TIC].CO_ID = [CO].CO_ID
LEFT JOIN
[dbo].[TIC_Ticker] [TIC]
ON
[TIC].TIC_ID = [CO_TIC].TIC_ID;
GO
--------------------------------- [Insert Trigger] -----------------------------------
-- CO_Company insert trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'itCO_Company')
DROP TRIGGER [dbo].[itCO_Company]
GO
CREATE TRIGGER [dbo].[itCO_Company] ON lCO_Company
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @now DATETIME2(7) = SYSDATETIME();
DECLARE @CO TABLE (
Row int identity(1,1) not null primary key,
CO_ID int not null
);
INSERT INTO [dbo].[CO_Company](
CO_Dummy
)
OUTPUT
inserted.CO_ID
INTO
@CO
SELECT
null
FROM
inserted
WHERE
inserted.CO_ID is null;
INSERT INTO [dbo].[CO_TIC_Company_Ticker](
CO_ID,
TIC_ID,
CO_TIC_RecordedAt,
CO_TIC_ErasedAt
)
SELECT
ISNULL(i.CO_ID, a.CO_ID),
k.TIC_ID,
ISNULL(i.CO_TIC_RecordedAt, @now),
ISNULL(i.CO_TIC_ErasedAt, '9999-12-31')
FROM (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY CO_ID ORDER BY CO_ID) AS Row
FROM
inserted
) i
LEFT JOIN
@CO a
ON
a.Row = i.Row
JOIN
[dbo].[TIC_Ticker] k
ON
k.TIC_Ticker = i.TIC_Ticker
WHERE
i.TIC_Ticker is not null;
END
GO
--------------------------------- [Update Trigger] -----------------------------------
-- CO_Company update trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'utCO_Company')
DROP TRIGGER [dbo].[utCO_Company]
GO
CREATE TRIGGER [dbo].[utCO_Company] ON lCO_Company
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @now DATETIME2(7) = SYSDATETIME();
IF(UPDATE(CO_ID) OR UPDATE(CO_TIC_RecordedAt))
RAISERROR('Primary key columns are not updatable.', 16, 1);
IF(UPDATE(CO_TIC_ErasedAt))
UPDATE CO_TIC
SET
CO_TIC.CO_TIC_ErasedAt = ISNULL(i.CO_TIC_ErasedAt, @now)
FROM
[dbo].[CO_TIC_Company_Ticker] CO_TIC
JOIN
inserted i
ON
i.CO_ID = CO_TIC.CO_ID
AND
i.CO_TIC_RecordedAt = CO_TIC.CO_TIC_RecordedAt;
IF(UPDATE(TIC_Ticker))
INSERT INTO [dbo].[CO_TIC_Company_Ticker](
CO_ID,
TIC_ID,
CO_TIC_RecordedAt,
CO_TIC_ErasedAt
)
SELECT
i.CO_ID,
k.TIC_ID,
CASE WHEN UPDATE(CO_TIC_ErasedAt) THEN ISNULL(i.CO_TIC_ErasedAt, @now) ELSE @now END,
'9999-12-31'
FROM
inserted i
JOIN
[dbo].[TIC_Ticker] k
ON
k.TIC_Ticker = i.TIC_Ticker;
END
GO
--------------------------------- [Delete Trigger] -----------------------------------
-- CO_Company delete trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'dtCO_Company')
DROP TRIGGER [dbo].[dtCO_Company]
GO
CREATE TRIGGER [dbo].[dtCO_Company] ON lCO_Company
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @now DATETIME2(7) = SYSDATETIME();
UPDATE CO_TIC
SET
CO_TIC.CO_TIC_ErasedAt = @now
FROM
[dbo].[CO_TIC_Company_Ticker] CO_TIC
JOIN
deleted d
ON
d.CO_ID = CO_TIC.CO_ID
AND
d.CO_TIC_RecordedAt = CO_TIC.CO_TIC_RecordedAt;
END
GO
---------------------------- [Point-in-Time Perspective] -----------------------------
-- CO_Company viewed as was (at the given timepoint)
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pCO_Company] (@changingTimepoint DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
[CO].CO_ID,
[TIC].TIC_ID,
[TIC].TIC_Ticker,
[CO_TIC].CO_TIC_RecordedAt,
[CO_TIC].CO_TIC_ErasedAt
FROM
[dbo].[CO_Company] [CO]
LEFT JOIN
[dbo].[acCO_TIC_Company_Ticker] [CO_TIC]
ON
[CO_TIC].CO_ID = [CO].CO_ID
LEFT JOIN
[dbo].[TIC_Ticker] [TIC]
ON
[TIC].TIC_ID = [CO_TIC].TIC_ID;
GO
CREATE SYNONYM [dbo].[plCO_Company] FOR [dbo].[pCO_Company];
GO
----------------- [Point-in-changing point-in-recording Perspective] -------------------
-- CO_Company viewed as was (timepoint) at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[ppCO_Company] (@changingTimepoint DATETIME2(7), @recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
[CO].CO_ID,
[TIC].TIC_ID,
[TIC].TIC_Ticker,
[CO_TIC].CO_TIC_RecordedAt,
[CO_TIC].CO_TIC_ErasedAt
FROM
[dbo].[CO_Company] [CO]
LEFT JOIN
[dbo].[arCO_TIC_Company_Ticker] (@recordingTimepoint) [CO_TIC]
ON
[CO_TIC].CO_ID = [CO].CO_ID
LEFT JOIN
[dbo].[TIC_Ticker] [TIC]
ON
[TIC].TIC_ID = [CO_TIC].TIC_ID;
GO
---------------------------------- [Anchor Table] ------------------------------------
-- CU_Customer table (with 2 attributes)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'CU_Customer' AND type LIKE '%U%')
CREATE TABLE [dbo].[CU_Customer] (
CU_ID int identity(1, 1) not null,
CU_Dummy bit null,
constraint pkCU_Customer primary key (
CU_ID asc
)
);
GO
----------------------- [Key Generation Stored Procedure] ----------------------------
-- CU_Customer surrogate key generation stored procedure
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'kCU_Customer' AND type in ('P','PC'))
DROP PROCEDURE [dbo].[kCU_Customer]
GO
CREATE PROCEDURE [dbo].[kCU_Customer] (
@requestedNumberOfIdentities bigint
) AS
BEGIN
SET NOCOUNT ON;
IF @requestedNumberOfIdentities > 0
BEGIN
WITH rowGenerator (rowNumber) AS (
SELECT
1
UNION ALL
SELECT
rowNumber + 1
FROM
rowGenerator
WHERE
rowNumber < @requestedNumberOfIdentities
)
INSERT INTO [dbo].[CU_Customer] (CU_Dummy)
OUTPUT
inserted.CU_ID
SELECT
null
FROM
rowGenerator
OPTION (maxrecursion 0);
END
END
GO
--------------------------------- [Attribute Table] ----------------------------------
-- CU_RAT_Customer_Rating table (on CU_Customer)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'CU_RAT_Customer_Rating' AND type LIKE '%U%')
CREATE TABLE [dbo].[CU_RAT_Customer_Rating] (
CU_ID int not null foreign key references [dbo].[CU_Customer](CU_ID),
RAT_ID tinyint not null foreign key references [dbo].[RAT_Rating](RAT_ID),
CU_RAT_ChangedAt datetime not null,
CU_RAT_RecordedAt datetime not null,
CU_RAT_ErasedAt datetime not null,
constraint uqCU_RAT_Customer_Rating unique (
CU_RAT_ErasedAt,
CU_ID,
CU_RAT_ChangedAt desc
),
constraint pkCU_RAT_Customer_Rating primary key (
CU_ID asc,
CU_RAT_ChangedAt desc,
CU_RAT_RecordedAt desc,
CU_RAT_ErasedAt desc
)
) ON [PRIMARY];
GO
-------------------- [All changing currently recorded perspective] -------------------
-- acCU_RAT_Customer_Rating view
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'acCU_RAT_Customer_Rating' AND type LIKE '%V%')
DROP VIEW [dbo].[acCU_RAT_Customer_Rating];
GO
CREATE VIEW [dbo].[acCU_RAT_Customer_Rating] WITH SCHEMABINDING AS
SELECT
CU_ID,
RAT_ID,
CU_RAT_ChangedAt,
CU_RAT_RecordedAt,
CU_RAT_ErasedAt
FROM
[dbo].[CU_RAT_Customer_Rating]
WHERE
CU_RAT_ErasedAt >= '9999-12-31';
GO
--------------------- [All changing rewind recording perspective] --------------------
-- arCU_RAT_Customer_Rating function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'arCU_RAT_Customer_Rating' AND type LIKE '%F%')
DROP FUNCTION [dbo].[arCU_RAT_Customer_Rating];
GO
CREATE FUNCTION [dbo].[arCU_RAT_Customer_Rating] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
CU_ID,
RAT_ID,
CU_RAT_ChangedAt,
CU_RAT_RecordedAt,
CU_RAT_ErasedAt
FROM
[dbo].[CU_RAT_Customer_Rating]
WHERE
CU_RAT_ErasedAt > @recordingTimepoint
AND
CU_RAT_RecordedAt <= @recordingTimepoint;
GO
------------------ [Rewind changing currently recorded perspective] ------------------
-- rcCU_RAT_Customer_Rating function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'rcCU_RAT_Customer_Rating' AND type LIKE '%F%')
DROP FUNCTION [dbo].[rcCU_RAT_Customer_Rating];
GO
CREATE FUNCTION [dbo].[rcCU_RAT_Customer_Rating] (@changingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
CU_ID,
RAT_ID,
CU_RAT_ChangedAt,
CU_RAT_RecordedAt,
CU_RAT_ErasedAt
FROM
[dbo].[CU_RAT_Customer_Rating]
WHERE
CU_RAT_ErasedAt >= '9999-12-31'
AND
CU_RAT_ChangedAt <= @changingTimepoint;
GO
------------------- [Rewind changing rewind recording perspective] -------------------
-- rrCU_RAT_Customer_Rating function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'rrCU_RAT_Customer_Rating' AND type LIKE '%F%')
DROP FUNCTION [dbo].[rrCU_RAT_Customer_Rating];
GO
CREATE FUNCTION [dbo].[rrCU_RAT_Customer_Rating] (
@changingTimepoint datetime,
@recordingTimepoint datetime
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
CU_ID,
RAT_ID,
CU_RAT_ChangedAt,
CU_RAT_RecordedAt,
CU_RAT_ErasedAt
FROM
[dbo].[CU_RAT_Customer_Rating]
WHERE
CU_RAT_ErasedAt > @recordingTimepoint
AND
CU_RAT_ChangedAt <= @changingTimepoint
AND
CU_RAT_RecordedAt <= @recordingTimepoint;
GO
--------------------------------- [Attribute Table] ----------------------------------
-- CU_NAM_Customer_Name table (on CU_Customer)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'CU_NAM_Customer_Name' AND type LIKE '%U%')
CREATE TABLE [dbo].[CU_NAM_Customer_Name] (
CU_ID int not null foreign key references [dbo].[CU_Customer](CU_ID),
CU_NAM_Customer_Name varchar(42) not null,
CU_NAM_RecordedAt datetime not null,
CU_NAM_ErasedAt datetime not null,
constraint uqCU_NAM_Customer_Name unique (
CU_NAM_ErasedAt,
CU_ID
),
constraint pkCU_NAM_Customer_Name primary key (
CU_ID asc,
CU_NAM_RecordedAt desc,
CU_NAM_ErasedAt desc
)
) ON [PRIMARY];
GO
-------------------- [All changing currently recorded perspective] -------------------
-- acCU_NAM_Customer_Name view
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'acCU_NAM_Customer_Name' AND type LIKE '%V%')
DROP VIEW [dbo].[acCU_NAM_Customer_Name];
GO
CREATE VIEW [dbo].[acCU_NAM_Customer_Name] WITH SCHEMABINDING AS
SELECT
CU_ID,
CU_NAM_Customer_Name,
CU_NAM_RecordedAt,
CU_NAM_ErasedAt
FROM
[dbo].[CU_NAM_Customer_Name]
WHERE
CU_NAM_ErasedAt >= '9999-12-31';
GO
--------------------- [All changing rewind recording perspective] --------------------
-- arCU_NAM_Customer_Name function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'arCU_NAM_Customer_Name' AND type LIKE '%F%')
DROP FUNCTION [dbo].[arCU_NAM_Customer_Name];
GO
CREATE FUNCTION [dbo].[arCU_NAM_Customer_Name] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
CU_ID,
CU_NAM_Customer_Name,
CU_NAM_RecordedAt,
CU_NAM_ErasedAt
FROM
[dbo].[CU_NAM_Customer_Name]
WHERE
CU_NAM_ErasedAt > @recordingTimepoint
AND
CU_NAM_RecordedAt <= @recordingTimepoint;
GO
------------------------------- [Latest Perspective] ---------------------------------
-- CU_Customer viewed as is (given by the latest available information)
--------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lCU_Customer] WITH SCHEMABINDING AS
SELECT
[CU].CU_ID,
[RAT].RAT_ID,
[RAT].RAT_Rating,
[CU_RAT].CU_RAT_ChangedAt,
[CU_RAT].CU_RAT_RecordedAt,
[CU_RAT].CU_RAT_ErasedAt,
[CU_NAM].CU_NAM_Customer_Name,
[CU_NAM].CU_NAM_RecordedAt,
[CU_NAM].CU_NAM_ErasedAt
FROM
[dbo].[CU_Customer] [CU]
LEFT JOIN
[dbo].[acCU_RAT_Customer_Rating] [CU_RAT]
ON
[CU_RAT].CU_ID = [CU].CU_ID
AND
[CU_RAT].CU_RAT_ChangedAt = (
SELECT
max(sub.CU_RAT_ChangedAt)
FROM
[dbo].[acCU_RAT_Customer_Rating] sub
WHERE
sub.CU_ID = [CU].CU_ID
)
LEFT JOIN
[dbo].[RAT_Rating] [RAT]
ON
[RAT].RAT_ID = [CU_RAT].RAT_ID
LEFT JOIN
[dbo].[acCU_NAM_Customer_Name] [CU_NAM]
ON
[CU_NAM].CU_ID = [CU].CU_ID;
GO
CREATE SYNONYM [dbo].[llCU_Customer] FOR [dbo].[lCU_Customer];
GO
----------------- [Latest changing point-in-recording Perspective] -------------------
-- CU_Customer viewed as is at the given recording time
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[lpCU_Customer] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
[CU].CU_ID,
[RAT].RAT_ID,
[RAT].RAT_Rating,
[CU_RAT].CU_RAT_ChangedAt,
[CU_RAT].CU_RAT_RecordedAt,
[CU_RAT].CU_RAT_ErasedAt,
[CU_NAM].CU_NAM_Customer_Name,
[CU_NAM].CU_NAM_RecordedAt,
[CU_NAM].CU_NAM_ErasedAt
FROM
[dbo].[CU_Customer] [CU]
LEFT JOIN
[dbo].[arCU_RAT_Customer_Rating] (@recordingTimepoint) [CU_RAT]
ON
[CU_RAT].CU_ID = [CU].CU_ID
AND
[CU_RAT].CU_RAT_ChangedAt = (
SELECT
max(sub.CU_RAT_ChangedAt)
FROM
[dbo].[arCU_RAT_Customer_Rating] (@recordingTimepoint) sub
WHERE
sub.CU_ID = [CU].CU_ID
)
LEFT JOIN
[dbo].[RAT_Rating] [RAT]
ON
[RAT].RAT_ID = [CU_RAT].RAT_ID
LEFT JOIN
[dbo].[arCU_NAM_Customer_Name] (@recordingTimepoint) [CU_NAM]
ON
[CU_NAM].CU_ID = [CU].CU_ID;
GO
--------------------------------- [Insert Trigger] -----------------------------------
-- CU_Customer insert trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'itCU_Customer')
DROP TRIGGER [dbo].[itCU_Customer]
GO
CREATE TRIGGER [dbo].[itCU_Customer] ON lCU_Customer
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @now DATETIME2(7) = SYSDATETIME();
DECLARE @CU TABLE (
Row int identity(1,1) not null primary key,
CU_ID int not null
);
INSERT INTO [dbo].[CU_Customer](
CU_Dummy
)
OUTPUT
inserted.CU_ID
INTO
@CU
SELECT
null
FROM
inserted
WHERE
inserted.CU_ID is null;
INSERT INTO [dbo].[CU_RAT_Customer_Rating](
CU_ID,
RAT_ID,
CU_RAT_ChangedAt,
CU_RAT_RecordedAt,
CU_RAT_ErasedAt
)
SELECT
ISNULL(i.CU_ID, a.CU_ID),
k.RAT_ID,
ISNULL(i.CU_RAT_ChangedAt, @now),
ISNULL(i.CU_RAT_RecordedAt, @now),
ISNULL(i.CU_RAT_ErasedAt, '9999-12-31')
FROM (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY CU_ID ORDER BY CU_ID) AS Row
FROM
inserted
) i
LEFT JOIN
@CU a
ON
a.Row = i.Row
JOIN
[dbo].[RAT_Rating] k
ON
k.RAT_Rating = i.RAT_Rating
WHERE
i.RAT_Rating is not null;
INSERT INTO [dbo].[CU_NAM_Customer_Name](
CU_ID,
CU_NAM_Customer_Name,
CU_NAM_RecordedAt,
CU_NAM_ErasedAt
)
SELECT
ISNULL(i.CU_ID, a.CU_ID),
i.CU_NAM_Customer_Name,
ISNULL(i.CU_NAM_RecordedAt, @now),
ISNULL(i.CU_NAM_ErasedAt, '9999-12-31')
FROM (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY CU_ID ORDER BY CU_ID) AS Row
FROM
inserted
) i
LEFT JOIN
@CU a
ON
a.Row = i.Row
WHERE
i.CU_NAM_Customer_Name is not null;
END
GO
--------------------------------- [Update Trigger] -----------------------------------
-- CU_Customer update trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'utCU_Customer')
DROP TRIGGER [dbo].[utCU_Customer]
GO
CREATE TRIGGER [dbo].[utCU_Customer] ON lCU_Customer
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @now DATETIME2(7) = SYSDATETIME();
IF(UPDATE(CU_ID) OR UPDATE(CU_RAT_ChangedAt) OR UPDATE(CU_RAT_RecordedAt))
RAISERROR('Primary key columns are not updatable.', 16, 1);
IF(UPDATE(CU_RAT_ErasedAt))
UPDATE CU_RAT
SET
CU_RAT.CU_RAT_ErasedAt = ISNULL(i.CU_RAT_ErasedAt, @now)
FROM
[dbo].[CU_RAT_Customer_Rating] CU_RAT
JOIN
inserted i
ON
i.CU_ID = CU_RAT.CU_ID
AND
i.CU_RAT_ChangedAt = CU_RAT.CU_RAT_ChangedAt
AND
i.CU_RAT_RecordedAt = CU_RAT.CU_RAT_RecordedAt;
IF(UPDATE(RAT_Rating))
INSERT INTO [dbo].[CU_RAT_Customer_Rating](
CU_ID,
RAT_ID,
CU_RAT_ChangedAt,
CU_RAT_RecordedAt,
CU_RAT_ErasedAt
)
SELECT
i.CU_ID,
k.RAT_ID,
CASE WHEN UPDATE(CU_RAT_ErasedAt) THEN i.CU_RAT_ChangedAt ELSE @now END,
CASE WHEN UPDATE(CU_RAT_ErasedAt) THEN ISNULL(i.CU_RAT_ErasedAt, @now) ELSE @now END,
'9999-12-31'
FROM
inserted i
JOIN
[dbo].[RAT_Rating] k
ON
k.RAT_Rating = i.RAT_Rating;
IF(UPDATE(CU_ID) OR UPDATE(CU_NAM_RecordedAt))
RAISERROR('Primary key columns are not updatable.', 16, 1);
IF(UPDATE(CU_NAM_ErasedAt))
UPDATE CU_NAM
SET
CU_NAM.CU_NAM_ErasedAt = ISNULL(i.CU_NAM_ErasedAt, @now)
FROM
[dbo].[CU_NAM_Customer_Name] CU_NAM
JOIN
inserted i
ON
i.CU_ID = CU_NAM.CU_ID
AND
i.CU_NAM_RecordedAt = CU_NAM.CU_NAM_RecordedAt;
IF(UPDATE(CU_NAM_Customer_Name))
INSERT INTO [dbo].[CU_NAM_Customer_Name](
CU_ID,
CU_NAM_Customer_Name,
CU_NAM_RecordedAt,
CU_NAM_ErasedAt
)
SELECT
i.CU_ID,
i.CU_NAM_Customer_Name,
CASE WHEN UPDATE(CU_NAM_ErasedAt) THEN ISNULL(i.CU_NAM_ErasedAt, @now) ELSE @now END,
'9999-12-31'
FROM
inserted i;
END
GO
--------------------------------- [Delete Trigger] -----------------------------------
-- CU_Customer delete trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'dtCU_Customer')
DROP TRIGGER [dbo].[dtCU_Customer]
GO
CREATE TRIGGER [dbo].[dtCU_Customer] ON lCU_Customer
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @now DATETIME2(7) = SYSDATETIME();
UPDATE CU_RAT
SET
CU_RAT.CU_RAT_ErasedAt = @now
FROM
[dbo].[CU_RAT_Customer_Rating] CU_RAT
JOIN
deleted d
ON
d.CU_ID = CU_RAT.CU_ID
AND
d.CU_RAT_ChangedAt = CU_RAT.CU_RAT_ChangedAt
AND
d.CU_RAT_RecordedAt = CU_RAT.CU_RAT_RecordedAt;
UPDATE CU_NAM
SET
CU_NAM.CU_NAM_ErasedAt = @now
FROM
[dbo].[CU_NAM_Customer_Name] CU_NAM
JOIN
deleted d
ON
d.CU_ID = CU_NAM.CU_ID
AND
d.CU_NAM_RecordedAt = CU_NAM.CU_NAM_RecordedAt;
END
GO
---------------------------- [Point-in-Time Perspective] -----------------------------
-- CU_Customer viewed as was (at the given timepoint)
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pCU_Customer] (@changingTimepoint DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
[CU].CU_ID,
[RAT].RAT_ID,
[RAT].RAT_Rating,
[CU_RAT].CU_RAT_ChangedAt,
[CU_RAT].CU_RAT_RecordedAt,
[CU_RAT].CU_RAT_ErasedAt,
[CU_NAM].CU_NAM_Customer_Name,
[CU_NAM].CU_NAM_RecordedAt,
[CU_NAM].CU_NAM_ErasedAt
FROM
[dbo].[CU_Customer] [CU]
LEFT JOIN
[dbo].[rcCU_RAT_Customer_Rating] (@changingTimepoint) [CU_RAT]
ON
[CU_RAT].CU_ID = [CU].CU_ID
AND
[CU_RAT].CU_RAT_ChangedAt = (
SELECT
max(sub.CU_RAT_ChangedAt)
FROM
[dbo].[rcCU_RAT_Customer_Rating] (@changingTimepoint) sub
WHERE
sub.CU_ID = [CU].CU_ID
)
LEFT JOIN
[dbo].[RAT_Rating] [RAT]
ON
[RAT].RAT_ID = [CU_RAT].RAT_ID
LEFT JOIN
[dbo].[acCU_NAM_Customer_Name] [CU_NAM]
ON
[CU_NAM].CU_ID = [CU].CU_ID;
GO
CREATE SYNONYM [dbo].[plCU_Customer] FOR [dbo].[pCU_Customer];
GO
----------------- [Point-in-changing point-in-recording Perspective] -------------------
-- CU_Customer viewed as was (timepoint) at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[ppCU_Customer] (@changingTimepoint DATETIME2(7), @recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
[CU].CU_ID,
[RAT].RAT_ID,
[RAT].RAT_Rating,
[CU_RAT].CU_RAT_ChangedAt,
[CU_RAT].CU_RAT_RecordedAt,
[CU_RAT].CU_RAT_ErasedAt,
[CU_NAM].CU_NAM_Customer_Name,
[CU_NAM].CU_NAM_RecordedAt,
[CU_NAM].CU_NAM_ErasedAt
FROM
[dbo].[CU_Customer] [CU]
LEFT JOIN
[dbo].[rrCU_RAT_Customer_Rating] (@changingTimepoint, @recordingTimepoint) [CU_RAT]
ON
[CU_RAT].CU_ID = [CU].CU_ID
AND
[CU_RAT].CU_RAT_ChangedAt = (
SELECT
max(sub.CU_RAT_ChangedAt)
FROM
[dbo].[rrCU_RAT_Customer_Rating] (@changingTimepoint, @recordingTimepoint) sub
WHERE
sub.CU_ID = [CU].CU_ID
)
LEFT JOIN
[dbo].[RAT_Rating] [RAT]
ON
[RAT].RAT_ID = [CU_RAT].RAT_ID
LEFT JOIN
[dbo].[arCU_NAM_Customer_Name] (@recordingTimepoint) [CU_NAM]
ON
[CU_NAM].CU_ID = [CU].CU_ID;
GO
------------------------------ [Difference Perspective] ------------------------------
-- CU_Customer viewed by differences in CU_RAT_Customer_Rating
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dCU_RAT_Customer_Rating' AND type LIKE '%F%')
DROP FUNCTION [dbo].[dCU_RAT_Customer_Rating];
GO
CREATE FUNCTION [dbo].[dCU_RAT_Customer_Rating] (@intervalStart DATETIME2(7), @intervalEnd DATETIME2(7))
RETURNS TABLE AS RETURN
SELECT
timepoints.inspectedTimepoint,
[CU].*
FROM (
SELECT DISTINCT
CU_RAT_ChangedAt AS inspectedTimepoint
FROM
[dbo].[CU_RAT_Customer_Rating]
WHERE
CU_RAT_ChangedAt BETWEEN @intervalStart AND @intervalEnd
) timepoints
CROSS APPLY
pCU_Customer(timepoints.inspectedTimepoint) [CU];
GO
------------------------------ [Difference Perspective] ------------------------------
-- CU_Customer viewed by differences in every historized attribute
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[dCU_Customer] (@intervalStart DATETIME2(7), @intervalEnd DATETIME2(7))
RETURNS TABLE AS RETURN
SELECT
timepoints.inspectedTimepoint,
[CU].*
FROM (
SELECT DISTINCT
CU_RAT_ChangedAt AS inspectedTimepoint
FROM
[dbo].[CU_RAT_Customer_Rating]
WHERE
CU_RAT_ChangedAt BETWEEN @intervalStart AND @intervalEnd
) timepoints
CROSS APPLY
[dbo].[pCU_Customer](timepoints.inspectedTimepoint) [CU];
GO
---------------------------------- [Anchor Table] ------------------------------------
-- PO_Position table (with 1 attributes)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'PO_Position' AND type LIKE '%U%')
CREATE TABLE [dbo].[PO_Position] (
PO_ID int identity(1, 1) not null,
PO_Dummy bit null,
constraint pkPO_Position primary key (
PO_ID asc
)
);
GO
----------------------- [Key Generation Stored Procedure] ----------------------------
-- PO_Position surrogate key generation stored procedure
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'kPO_Position' AND type in ('P','PC'))
DROP PROCEDURE [dbo].[kPO_Position]
GO
CREATE PROCEDURE [dbo].[kPO_Position] (
@requestedNumberOfIdentities bigint
) AS
BEGIN
SET NOCOUNT ON;
IF @requestedNumberOfIdentities > 0
BEGIN
WITH rowGenerator (rowNumber) AS (
SELECT
1
UNION ALL
SELECT
rowNumber + 1
FROM
rowGenerator
WHERE
rowNumber < @requestedNumberOfIdentities
)
INSERT INTO [dbo].[PO_Position] (PO_Dummy)
OUTPUT
inserted.PO_ID
SELECT
null
FROM
rowGenerator
OPTION (maxrecursion 0);
END
END
GO
--------------------------------- [Attribute Table] ----------------------------------
-- PO_QUA_Position_Quantity table (on PO_Position)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'PO_QUA_Position_Quantity' AND type LIKE '%U%')
CREATE TABLE [dbo].[PO_QUA_Position_Quantity] (
PO_ID int not null foreign key references [dbo].[PO_Position](PO_ID),
PO_QUA_Position_Quantity int not null,
PO_QUA_ChangedAt datetime not null,
PO_QUA_RecordedAt datetime not null,
PO_QUA_ErasedAt datetime not null,
constraint uqPO_QUA_Position_Quantity unique (
PO_QUA_ErasedAt,
PO_ID,
PO_QUA_ChangedAt desc
),
constraint pkPO_QUA_Position_Quantity primary key (
PO_ID asc,
PO_QUA_ChangedAt desc,
PO_QUA_RecordedAt desc,
PO_QUA_ErasedAt desc
)
) ON [PRIMARY];
GO
-------------------- [All changing currently recorded perspective] -------------------
-- acPO_QUA_Position_Quantity view
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'acPO_QUA_Position_Quantity' AND type LIKE '%V%')
DROP VIEW [dbo].[acPO_QUA_Position_Quantity];
GO
CREATE VIEW [dbo].[acPO_QUA_Position_Quantity] WITH SCHEMABINDING AS
SELECT
PO_ID,
PO_QUA_Position_Quantity,
PO_QUA_ChangedAt,
PO_QUA_RecordedAt,
PO_QUA_ErasedAt
FROM
[dbo].[PO_QUA_Position_Quantity]
WHERE
PO_QUA_ErasedAt >= '9999-12-31';
GO
--------------------- [All changing rewind recording perspective] --------------------
-- arPO_QUA_Position_Quantity function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'arPO_QUA_Position_Quantity' AND type LIKE '%F%')
DROP FUNCTION [dbo].[arPO_QUA_Position_Quantity];
GO
CREATE FUNCTION [dbo].[arPO_QUA_Position_Quantity] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
PO_ID,
PO_QUA_Position_Quantity,
PO_QUA_ChangedAt,
PO_QUA_RecordedAt,
PO_QUA_ErasedAt
FROM
[dbo].[PO_QUA_Position_Quantity]
WHERE
PO_QUA_ErasedAt > @recordingTimepoint
AND
PO_QUA_RecordedAt <= @recordingTimepoint;
GO
------------------ [Rewind changing currently recorded perspective] ------------------
-- rcPO_QUA_Position_Quantity function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'rcPO_QUA_Position_Quantity' AND type LIKE '%F%')
DROP FUNCTION [dbo].[rcPO_QUA_Position_Quantity];
GO
CREATE FUNCTION [dbo].[rcPO_QUA_Position_Quantity] (@changingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
PO_ID,
PO_QUA_Position_Quantity,
PO_QUA_ChangedAt,
PO_QUA_RecordedAt,
PO_QUA_ErasedAt
FROM
[dbo].[PO_QUA_Position_Quantity]
WHERE
PO_QUA_ErasedAt >= '9999-12-31'
AND
PO_QUA_ChangedAt <= @changingTimepoint;
GO
------------------- [Rewind changing rewind recording perspective] -------------------
-- rrPO_QUA_Position_Quantity function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'rrPO_QUA_Position_Quantity' AND type LIKE '%F%')
DROP FUNCTION [dbo].[rrPO_QUA_Position_Quantity];
GO
CREATE FUNCTION [dbo].[rrPO_QUA_Position_Quantity] (
@changingTimepoint datetime,
@recordingTimepoint datetime
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
PO_ID,
PO_QUA_Position_Quantity,
PO_QUA_ChangedAt,
PO_QUA_RecordedAt,
PO_QUA_ErasedAt
FROM
[dbo].[PO_QUA_Position_Quantity]
WHERE
PO_QUA_ErasedAt > @recordingTimepoint
AND
PO_QUA_ChangedAt <= @changingTimepoint
AND
PO_QUA_RecordedAt <= @recordingTimepoint;
GO
------------------------------- [Latest Perspective] ---------------------------------
-- PO_Position viewed as is (given by the latest available information)
--------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lPO_Position] WITH SCHEMABINDING AS
SELECT
[PO].PO_ID,
[PO_QUA].PO_QUA_Position_Quantity,
[PO_QUA].PO_QUA_ChangedAt,
[PO_QUA].PO_QUA_RecordedAt,
[PO_QUA].PO_QUA_ErasedAt
FROM
[dbo].[PO_Position] [PO]
LEFT JOIN
[dbo].[acPO_QUA_Position_Quantity] [PO_QUA]
ON
[PO_QUA].PO_ID = [PO].PO_ID
AND
[PO_QUA].PO_QUA_ChangedAt = (
SELECT
max(sub.PO_QUA_ChangedAt)
FROM
[dbo].[acPO_QUA_Position_Quantity] sub
WHERE
sub.PO_ID = [PO].PO_ID
);
GO
CREATE SYNONYM [dbo].[llPO_Position] FOR [dbo].[lPO_Position];
GO
----------------- [Latest changing point-in-recording Perspective] -------------------
-- PO_Position viewed as is at the given recording time
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[lpPO_Position] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
[PO].PO_ID,
[PO_QUA].PO_QUA_Position_Quantity,
[PO_QUA].PO_QUA_ChangedAt,
[PO_QUA].PO_QUA_RecordedAt,
[PO_QUA].PO_QUA_ErasedAt
FROM
[dbo].[PO_Position] [PO]
LEFT JOIN
[dbo].[arPO_QUA_Position_Quantity] (@recordingTimepoint) [PO_QUA]
ON
[PO_QUA].PO_ID = [PO].PO_ID
AND
[PO_QUA].PO_QUA_ChangedAt = (
SELECT
max(sub.PO_QUA_ChangedAt)
FROM
[dbo].[arPO_QUA_Position_Quantity] (@recordingTimepoint) sub
WHERE
sub.PO_ID = [PO].PO_ID
);
GO
--------------------------------- [Insert Trigger] -----------------------------------
-- PO_Position insert trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'itPO_Position')
DROP TRIGGER [dbo].[itPO_Position]
GO
CREATE TRIGGER [dbo].[itPO_Position] ON lPO_Position
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @now DATETIME2(7) = SYSDATETIME();
DECLARE @PO TABLE (
Row int identity(1,1) not null primary key,
PO_ID int not null
);
INSERT INTO [dbo].[PO_Position](
PO_Dummy
)
OUTPUT
inserted.PO_ID
INTO
@PO
SELECT
null
FROM
inserted
WHERE
inserted.PO_ID is null;
INSERT INTO [dbo].[PO_QUA_Position_Quantity](
PO_ID,
PO_QUA_Position_Quantity,
PO_QUA_ChangedAt,
PO_QUA_RecordedAt,
PO_QUA_ErasedAt
)
SELECT
ISNULL(i.PO_ID, a.PO_ID),
i.PO_QUA_Position_Quantity,
ISNULL(i.PO_QUA_ChangedAt, @now),
ISNULL(i.PO_QUA_RecordedAt, @now),
ISNULL(i.PO_QUA_ErasedAt, '9999-12-31')
FROM (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY PO_ID ORDER BY PO_ID) AS Row
FROM
inserted
) i
LEFT JOIN
@PO a
ON
a.Row = i.Row
WHERE
i.PO_QUA_Position_Quantity is not null;
END
GO
--------------------------------- [Update Trigger] -----------------------------------
-- PO_Position update trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'utPO_Position')
DROP TRIGGER [dbo].[utPO_Position]
GO
CREATE TRIGGER [dbo].[utPO_Position] ON lPO_Position
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @now DATETIME2(7) = SYSDATETIME();
IF(UPDATE(PO_ID) OR UPDATE(PO_QUA_ChangedAt) OR UPDATE(PO_QUA_RecordedAt))
RAISERROR('Primary key columns are not updatable.', 16, 1);
IF(UPDATE(PO_QUA_ErasedAt))
UPDATE PO_QUA
SET
PO_QUA.PO_QUA_ErasedAt = ISNULL(i.PO_QUA_ErasedAt, @now)
FROM
[dbo].[PO_QUA_Position_Quantity] PO_QUA
JOIN
inserted i
ON
i.PO_ID = PO_QUA.PO_ID
AND
i.PO_QUA_ChangedAt = PO_QUA.PO_QUA_ChangedAt
AND
i.PO_QUA_RecordedAt = PO_QUA.PO_QUA_RecordedAt;
IF(UPDATE(PO_QUA_Position_Quantity))
INSERT INTO [dbo].[PO_QUA_Position_Quantity](
PO_ID,
PO_QUA_Position_Quantity,
PO_QUA_ChangedAt,
PO_QUA_RecordedAt,
PO_QUA_ErasedAt
)
SELECT
i.PO_ID,
i.PO_QUA_Position_Quantity,
CASE WHEN UPDATE(PO_QUA_ErasedAt) THEN i.PO_QUA_ChangedAt ELSE @now END,
CASE WHEN UPDATE(PO_QUA_ErasedAt) THEN ISNULL(i.PO_QUA_ErasedAt, @now) ELSE @now END,
'9999-12-31'
FROM
inserted i;
END
GO
--------------------------------- [Delete Trigger] -----------------------------------
-- PO_Position delete trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'dtPO_Position')
DROP TRIGGER [dbo].[dtPO_Position]
GO
CREATE TRIGGER [dbo].[dtPO_Position] ON lPO_Position
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @now DATETIME2(7) = SYSDATETIME();
UPDATE PO_QUA
SET
PO_QUA.PO_QUA_ErasedAt = @now
FROM
[dbo].[PO_QUA_Position_Quantity] PO_QUA
JOIN
deleted d
ON
d.PO_ID = PO_QUA.PO_ID
AND
d.PO_QUA_ChangedAt = PO_QUA.PO_QUA_ChangedAt
AND
d.PO_QUA_RecordedAt = PO_QUA.PO_QUA_RecordedAt;
END
GO
---------------------------- [Point-in-Time Perspective] -----------------------------
-- PO_Position viewed as was (at the given timepoint)
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pPO_Position] (@changingTimepoint DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
[PO].PO_ID,
[PO_QUA].PO_QUA_Position_Quantity,
[PO_QUA].PO_QUA_ChangedAt,
[PO_QUA].PO_QUA_RecordedAt,
[PO_QUA].PO_QUA_ErasedAt
FROM
[dbo].[PO_Position] [PO]
LEFT JOIN
[dbo].[rcPO_QUA_Position_Quantity] (@changingTimepoint) [PO_QUA]
ON
[PO_QUA].PO_ID = [PO].PO_ID
AND
[PO_QUA].PO_QUA_ChangedAt = (
SELECT
max(sub.PO_QUA_ChangedAt)
FROM
[dbo].[rcPO_QUA_Position_Quantity] (@changingTimepoint) sub
WHERE
sub.PO_ID = [PO].PO_ID
);
GO
CREATE SYNONYM [dbo].[plPO_Position] FOR [dbo].[pPO_Position];
GO
----------------- [Point-in-changing point-in-recording Perspective] -------------------
-- PO_Position viewed as was (timepoint) at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[ppPO_Position] (@changingTimepoint DATETIME2(7), @recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
[PO].PO_ID,
[PO_QUA].PO_QUA_Position_Quantity,
[PO_QUA].PO_QUA_ChangedAt,
[PO_QUA].PO_QUA_RecordedAt,
[PO_QUA].PO_QUA_ErasedAt
FROM
[dbo].[PO_Position] [PO]
LEFT JOIN
[dbo].[rrPO_QUA_Position_Quantity] (@changingTimepoint, @recordingTimepoint) [PO_QUA]
ON
[PO_QUA].PO_ID = [PO].PO_ID
AND
[PO_QUA].PO_QUA_ChangedAt = (
SELECT
max(sub.PO_QUA_ChangedAt)
FROM
[dbo].[rrPO_QUA_Position_Quantity] (@changingTimepoint, @recordingTimepoint) sub
WHERE
sub.PO_ID = [PO].PO_ID
);
GO
------------------------------ [Difference Perspective] ------------------------------
-- PO_Position viewed by differences in PO_QUA_Position_Quantity
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dPO_QUA_Position_Quantity' AND type LIKE '%F%')
DROP FUNCTION [dbo].[dPO_QUA_Position_Quantity];
GO
CREATE FUNCTION [dbo].[dPO_QUA_Position_Quantity] (@intervalStart DATETIME2(7), @intervalEnd DATETIME2(7))
RETURNS TABLE AS RETURN
SELECT
timepoints.inspectedTimepoint,
[PO].*
FROM (
SELECT DISTINCT
PO_QUA_ChangedAt AS inspectedTimepoint
FROM
[dbo].[PO_QUA_Position_Quantity]
WHERE
PO_QUA_ChangedAt BETWEEN @intervalStart AND @intervalEnd
) timepoints
CROSS APPLY
pPO_Position(timepoints.inspectedTimepoint) [PO];
GO
------------------------------ [Difference Perspective] ------------------------------
-- PO_Position viewed by differences in every historized attribute
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[dPO_Position] (@intervalStart DATETIME2(7), @intervalEnd DATETIME2(7))
RETURNS TABLE AS RETURN
SELECT
timepoints.inspectedTimepoint,
[PO].*
FROM (
SELECT DISTINCT
PO_QUA_ChangedAt AS inspectedTimepoint
FROM
[dbo].[PO_QUA_Position_Quantity]
WHERE
PO_QUA_ChangedAt BETWEEN @intervalStart AND @intervalEnd
) timepoints
CROSS APPLY
[dbo].[pPO_Position](timepoints.inspectedTimepoint) [PO];
GO
------------------------------------- [Tie Table] ------------------------------------
-- CU_is_PO_holding table (2-ary)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'CU_is_PO_holding' and type LIKE '%U%')
CREATE TABLE [dbo].[CU_is_PO_holding] (
CU_ID_is int not null foreign key references [dbo].[CU_Customer](CU_ID),
PO_ID_holding int not null foreign key references [dbo].[PO_Position](PO_ID),
CU_is_PO_holding_RecordedAt datetime not null,
CU_is_PO_holding_ErasedAt datetime not null,
constraint uqCU_is_PO_holding unique (
CU_is_PO_holding_ErasedAt,
PO_ID_holding asc
),
constraint pkCU_is_PO_holding primary key (
PO_ID_holding asc,
CU_is_PO_holding_RecordedAt desc,
CU_is_PO_holding_ErasedAt desc
)
) ON [PRIMARY];
GO
-------------------------- [Currently recorded perspective] --------------------------
-- cCU_is_PO_holding view
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'cCU_is_PO_holding' AND type LIKE '%V%')
DROP VIEW [dbo].[cCU_is_PO_holding];
GO
CREATE VIEW [dbo].[cCU_is_PO_holding] WITH SCHEMABINDING AS
SELECT
tie.CU_ID_is,
tie.PO_ID_holding,
tie.CU_is_PO_holding_RecordedAt,
tie.CU_is_PO_holding_ErasedAt
FROM
[dbo].[CU_is_PO_holding] tie
WHERE
CU_is_PO_holding_ErasedAt >= '9999-12-31';
GO
--------------------------- [Rewind recording perspective] ---------------------------
-- rCU_is_PO_holding function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'rCU_is_PO_holding' AND type LIKE '%F%')
DROP FUNCTION [dbo].[rCU_is_PO_holding];
GO
CREATE FUNCTION [dbo].[rCU_is_PO_holding] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
tie.CU_ID_is,
tie.PO_ID_holding,
tie.CU_is_PO_holding_RecordedAt,
tie.CU_is_PO_holding_ErasedAt
FROM
[dbo].[CU_is_PO_holding] tie
WHERE
CU_is_PO_holding_ErasedAt > @recordingTimepoint
AND
CU_is_PO_holding_RecordedAt <= @recordingTimepoint;
GO
--------------------------------- [Latest Perspective] -------------------------------
-- CU_is_PO_holding viewed as is (given by the latest available information)
--------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lCU_is_PO_holding] WITH SCHEMABINDING AS
SELECT
tie.CU_ID_is,
tie.PO_ID_holding,
tie.CU_is_PO_holding_RecordedAt,
tie.CU_is_PO_holding_ErasedAt
FROM
[dbo].[cCU_is_PO_holding] tie;
GO
CREATE SYNONYM [dbo].[llCU_is_PO_holding] FOR [dbo].[lCU_is_PO_holding];
GO
----------------- [Latest changing point-in-recording Perspective] -------------------
-- CU_is_PO_holding viewed as is at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[lpCU_is_PO_holding] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
tie.CU_ID_is,
tie.PO_ID_holding,
tie.CU_is_PO_holding_RecordedAt,
tie.CU_is_PO_holding_ErasedAt
FROM
[dbo].[rCU_is_PO_holding](@recordingTimepoint) tie;
GO
---------------------------- [Point-in-Time Perspective] -----------------------------
-- CU_is_PO_holding viewed as was (at the given timepoint)
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pCU_is_PO_holding] (@changingTimepoint DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
tie.CU_ID_is,
tie.PO_ID_holding,
tie.CU_is_PO_holding_RecordedAt,
tie.CU_is_PO_holding_ErasedAt
FROM
[dbo].[cCU_is_PO_holding] tie;
GO
CREATE SYNONYM [dbo].[plCU_is_PO_holding] FOR [dbo].[pCU_is_PO_holding];
GO
----------------- [Point-in-changing point-in-recording Perspective] -------------------
-- CU_is_PO_holding viewed as was (timepoint) at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[ppCU_is_PO_holding] (@changingTimepoint DATETIME2(7), @recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
tie.CU_ID_is,
tie.PO_ID_holding,
tie.CU_is_PO_holding_RecordedAt,
tie.CU_is_PO_holding_ErasedAt
FROM
[dbo].[rCU_is_PO_holding](@recordingTimepoint) tie;
GO
------------------------------ [Difference Perspective] ------------------------------
-- CU_is_PO_holding viewed by differences in the tie
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[dCU_is_PO_holding] (@intervalStart DATETIME2(7), @intervalEnd DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
tie.CU_ID_is,
tie.PO_ID_holding,
tie.CU_is_PO_holding_RecordedAt,
tie.CU_is_PO_holding_ErasedAt
FROM
[dbo].[cCU_is_PO_holding] tie;
GO
------------------------------------- [Tie Table] ------------------------------------
-- PO_for_CO_the table (2-ary)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'PO_for_CO_the' and type LIKE '%U%')
CREATE TABLE [dbo].[PO_for_CO_the] (
PO_ID_for int not null foreign key references [dbo].[PO_Position](PO_ID),
CO_ID_the int not null foreign key references [dbo].[CO_Company](CO_ID),
PO_for_CO_the_RecordedAt datetime not null,
PO_for_CO_the_ErasedAt datetime not null,
constraint uqPO_for_CO_the unique (
PO_for_CO_the_ErasedAt,
PO_ID_for asc
),
constraint pkPO_for_CO_the primary key (
PO_ID_for asc,
PO_for_CO_the_RecordedAt desc,
PO_for_CO_the_ErasedAt desc
)
) ON [PRIMARY];
GO
-------------------------- [Currently recorded perspective] --------------------------
-- cPO_for_CO_the view
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'cPO_for_CO_the' AND type LIKE '%V%')
DROP VIEW [dbo].[cPO_for_CO_the];
GO
CREATE VIEW [dbo].[cPO_for_CO_the] WITH SCHEMABINDING AS
SELECT
tie.PO_ID_for,
tie.CO_ID_the,
tie.PO_for_CO_the_RecordedAt,
tie.PO_for_CO_the_ErasedAt
FROM
[dbo].[PO_for_CO_the] tie
WHERE
PO_for_CO_the_ErasedAt >= '9999-12-31';
GO
--------------------------- [Rewind recording perspective] ---------------------------
-- rPO_for_CO_the function
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'rPO_for_CO_the' AND type LIKE '%F%')
DROP FUNCTION [dbo].[rPO_for_CO_the];
GO
CREATE FUNCTION [dbo].[rPO_for_CO_the] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
tie.PO_ID_for,
tie.CO_ID_the,
tie.PO_for_CO_the_RecordedAt,
tie.PO_for_CO_the_ErasedAt
FROM
[dbo].[PO_for_CO_the] tie
WHERE
PO_for_CO_the_ErasedAt > @recordingTimepoint
AND
PO_for_CO_the_RecordedAt <= @recordingTimepoint;
GO
--------------------------------- [Latest Perspective] -------------------------------
-- PO_for_CO_the viewed as is (given by the latest available information)
--------------------------------------------------------------------------------------
CREATE VIEW [dbo].[lPO_for_CO_the] WITH SCHEMABINDING AS
SELECT
tie.PO_ID_for,
tie.CO_ID_the,
tie.PO_for_CO_the_RecordedAt,
tie.PO_for_CO_the_ErasedAt
FROM
[dbo].[cPO_for_CO_the] tie;
GO
CREATE SYNONYM [dbo].[llPO_for_CO_the] FOR [dbo].[lPO_for_CO_the];
GO
----------------- [Latest changing point-in-recording Perspective] -------------------
-- PO_for_CO_the viewed as is at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[lpPO_for_CO_the] (@recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
tie.PO_ID_for,
tie.CO_ID_the,
tie.PO_for_CO_the_RecordedAt,
tie.PO_for_CO_the_ErasedAt
FROM
[dbo].[rPO_for_CO_the](@recordingTimepoint) tie;
GO
---------------------------- [Point-in-Time Perspective] -----------------------------
-- PO_for_CO_the viewed as was (at the given timepoint)
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[pPO_for_CO_the] (@changingTimepoint DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
tie.PO_ID_for,
tie.CO_ID_the,
tie.PO_for_CO_the_RecordedAt,
tie.PO_for_CO_the_ErasedAt
FROM
[dbo].[cPO_for_CO_the] tie;
GO
CREATE SYNONYM [dbo].[plPO_for_CO_the] FOR [dbo].[pPO_for_CO_the];
GO
----------------- [Point-in-changing point-in-recording Perspective] -------------------
-- PO_for_CO_the viewed as was (timepoint) at the given recording timepoint
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[ppPO_for_CO_the] (@changingTimepoint DATETIME2(7), @recordingTimepoint datetime)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
tie.PO_ID_for,
tie.CO_ID_the,
tie.PO_for_CO_the_RecordedAt,
tie.PO_for_CO_the_ErasedAt
FROM
[dbo].[rPO_for_CO_the](@recordingTimepoint) tie;
GO
------------------------------ [Difference Perspective] ------------------------------
-- PO_for_CO_the viewed by differences in the tie
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[dPO_for_CO_the] (@intervalStart DATETIME2(7), @intervalEnd DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
tie.PO_ID_for,
tie.CO_ID_the,
tie.PO_for_CO_the_RecordedAt,
tie.PO_for_CO_the_ErasedAt
FROM
[dbo].[cPO_for_CO_the] tie;
GO
--------------------------------- [Schema Evolution] ---------------------------------
-- Schema evolution tables, views and functions
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = '_Schema' and type LIKE '%U%')
CREATE TABLE [_Schema] (
[version] int identity(1, 1) not null primary key,
[activation] DATETIME2(7) not null,
[schema] xml not null
);
GO
INSERT INTO [_Schema] (
[activation],
[schema]
)
SELECT
current_timestamp,
N'<schema><metadata format="0.94" temporalization="bi"/><knot mnemonic="TIC" descriptor="Ticker" identity="smallint" dataRange="varchar(6)"><metadata capsule="dbo" generator="false"/><layout x="829.78" y="32.45" fixed="false"/></knot><knot mnemonic="RAT" descriptor="Rating" identity="tinyint" dataRange="char(1)"><metadata capsule="dbo" generator="false"/><layout x="464.58" y="368.24" fixed="false"/></knot><anchor mnemonic="CO" descriptor="Company" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="TIC" descriptor="Ticker" knotRange="TIC"><metadata capsule="dbo"/><layout x="800.42" y="78.62" fixed="false"/></attribute><layout x="777.21" y="152.28" fixed="false"/></anchor><anchor mnemonic="CU" descriptor="Customer" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="RAT" descriptor="Rating" timeRange="datetime" knotRange="RAT"><metadata capsule="dbo"/><layout x="520.76" y="403.15" fixed="false"/></attribute><attribute mnemonic="NAM" descriptor="Name" dataRange="varchar(42)"><metadata capsule="dbo"/><layout x="623.23" y="476.92" fixed="false"/></attribute><layout x="601.62" y="413.27" fixed="false"/></anchor><anchor mnemonic="PO" descriptor="Position" identity="int"><metadata capsule="dbo" generator="true"/><attribute mnemonic="QUA" descriptor="Quantity" timeRange="datetime" dataRange="int"><metadata capsule="dbo"/><layout x="609.43" y="229.66" fixed="false"/></attribute><layout x="672.26" y="267.43" fixed="false"/></anchor><tie><anchorRole role="is" type="CU" identifier="false"/><anchorRole role="holding" type="PO" identifier="true"/><metadata capsule="dbo"/><layout x="647.30" y="349.69" fixed="false"/></tie><tie><anchorRole role="for" type="PO" identifier="true"/><anchorRole role="the" type="CO" identifier="false"/><metadata capsule="dbo"/><layout x="731.35" y="220.87" fixed="false"/></tie></schema>';
GO
IF EXISTS (SELECT * FROM sys.views WHERE name = '_Anchor')
DROP VIEW [_Anchor]
GO
CREATE VIEW [_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
[_Schema] S
CROSS APPLY
S.[schema].nodes('/schema/anchor') as Nodeset(anchor);
GO
IF EXISTS (SELECT * FROM sys.views WHERE name = '_Knot')
DROP VIEW [_Knot]
GO
CREATE VIEW [_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
[_Schema] S
CROSS APPLY
S.[schema].nodes('/schema/knot') as Nodeset(knot);
GO
IF EXISTS (SELECT * FROM sys.views WHERE name = '_Attribute')
DROP VIEW [_Attribute]
GO
CREATE VIEW [_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
[_Schema] S
CROSS APPLY
S.[schema].nodes('/schema/anchor') as ParentNodeset(anchor)
OUTER APPLY
ParentNodeset.anchor.nodes('attribute') as Nodeset(attribute);
GO
IF EXISTS (SELECT * FROM sys.views WHERE name = '_Tie')
DROP VIEW [_Tie]
GO
CREATE VIEW [_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
[_Schema] S
CROSS APPLY
S.[schema].nodes('/schema/tie') as Nodeset(tie);
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = '_Evolution' and type LIKE '%F%')
DROP FUNCTION [_Evolution];
GO
CREATE FUNCTION _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
[_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
[_Schema]
WHERE
[activation] <= @timepoint
) V
JOIN (
SELECT
[name],
[version]
FROM
[_Anchor] a
UNION ALL
SELECT
[name],
[version]
FROM
[_Knot] k
UNION ALL
SELECT
[name],
[version]
FROM
[_Attribute] b
UNION ALL
SELECT
[name],
[version]
FROM
[_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
------------------------------------ LOAD DATA ---------------------------------------
insert into TIC_Ticker (
TIC_ID,
TIC_Ticker
) values
(1, 'XOM');
insert into lCO_Company (
TIC_Ticker,
CO_TIC_RecordedAt,
CO_TIC_ErasedAt
) values
('XOM', '2011-02-01', '9999-12-31');
insert into RAT_Rating (
RAT_ID,
RAT_Rating
) values
(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E');
insert into lCU_Customer (
CU_NAM_Customer_Name,
CU_NAM_RecordedAt,
CU_NAM_ErasedAt
) values
('Bridgewater Associates', '2011-01-01', '9999-12-31');
insert into lCU_Customer (
CU_ID,
RAT_Rating,
CU_RAT_ChangedAt,
CU_RAT_RecordedAt,
CU_RAT_ErasedAt
) values
(1, 'D', '2011-03-20', '2011-03-21', '9999-12-31'),
(1, 'E', '2011-03-18', '2011-03-21', '9999-12-31'),
(1, 'A', '2011-03-18', '2011-03-18', '2011-03-19'),
(1, 'D', '2011-03-17', '2011-03-21', '9999-12-31'),
(1, 'D', '2011-03-17', '2011-03-20', '2011-03-21'),
(1, 'A', '2011-03-17', '2011-03-17', '2011-03-18'),
(1, 'B', '2011-03-16', '2011-03-18', '2011-03-19'),
(1, 'C', '2011-03-15', '2011-03-20', '9999-12-31'),
(1, 'C', '2011-03-15', '2011-03-19', '2011-03-20');
-- This SP creates the given number of identities.
exec kPO_Position 1;
insert into lPO_Position (
PO_ID,
PO_QUA_Position_Quantity,
PO_QUA_ChangedAt,
PO_QUA_RecordedAt,
PO_QUA_ErasedAt
) values
(1, 1, '2011-03-17', '2011-03-17', '2011-03-18'),
(1, 20, '2011-03-16', '2011-03-18', '2011-03-19'),
(1, 300, '2011-03-15', '2011-03-19', '2011-03-20'),
(1, 4000, '2011-03-17', '2011-03-20', '2011-03-21'),
(1, 50000, '2011-03-18', '2011-03-21', '9999-12-31'),
(1, 300, '2011-03-15', '2011-03-20', '9999-12-31'),
(1, 4000, '2011-03-17', '2011-03-21', '9999-12-31');
insert into CU_is_PO_holding (
CU_ID_is,
PO_ID_holding,
CU_is_PO_holding_RecordedAt,
CU_is_PO_holding_ErasedAt
) values
(1, 1, '2011-03-15', '9999-12-31');
insert into PO_for_CO_the (
PO_ID_for,
CO_ID_the,
PO_for_CO_the_RecordedAt,
PO_for_CO_the_ErasedAt
) values
(1, 1, '2011-03-15', '9999-12-31');
----------------------------------- QUERY DATA ---------------------------------------
-- using latest changing latest recording
--------------------------------------------------------------------------------------
select
cu.CU_NAM_Customer_Name,
cu.RAT_Rating,
po.PO_QUA_Position_Quantity,
co.TIC_Ticker
from
llCU_Customer cu
join
llCU_is_PO_holding cupo
on
cupo.CU_ID_is = cu.CU_ID
join
llPO_Position po
on
po.PO_ID = cupo.PO_ID_holding
join
llPO_for_CO_the poco
on
poco.PO_ID_for = po.PO_ID
join
llCO_Company co
on
co.CO_ID = poco.CO_ID_the;
--------------------------------------------------------------------------------------
-- using point in changing point in recording
--------------------------------------------------------------------------------------
select
cu.CU_NAM_Customer_Name,
cu.RAT_Rating,
po.PO_QUA_Position_Quantity,
co.TIC_Ticker
from
ppCU_Customer('2011-03-16', '2011-03-19') cu
join
llCU_is_PO_holding cupo
on
cupo.CU_ID_is = cu.CU_ID
join
ppPO_Position('2011-03-16', '2011-03-19') po
on
po.PO_ID = cupo.PO_ID_holding
join
llPO_for_CO_the poco
on
poco.PO_ID_for = po.PO_ID
join
llCO_Company co
on
co.CO_ID = poco.CO_ID_the;
--------------------------------------------------------------------------------------
-- a pattern for a temporally dependent join (the above are independent)
--------------------------------------------------------------------------------------
select
cu.CU_NAM_Customer_Name,
cu.RAT_Rating,
po.PO_QUA_Position_Quantity,
po.PO_QUA_ChangedAt,
co.TIC_Ticker
from
ppCU_Customer('2011-03-16', '2011-03-19') cu
join
llCU_is_PO_holding cupo
on
cupo.CU_ID_is = cu.CU_ID
cross apply
ppPO_Position((
select
min(PO_QUA_ChangedAt)
from
PO_QUA_Position_Quantity PO_QUA
where
PO_QUA.PO_ID = cupo.PO_ID_holding
),
'9999-12-30'
) po
join
llPO_for_CO_the poco
on
poco.PO_ID_for = po.PO_ID
join
llCO_Company co
on
co.CO_ID = poco.CO_ID_the
where
po.PO_ID = cupo.PO_ID_holding;
--------------------------------------------------------------------------------------
-- This model will also handle a situation where for
-- example a position was wrongfully associated with
-- a company or customer, in which situation you can
-- use the pp-views on the ties to "time-travel".
--------------------------------------------------------------------------------------