Anchor Model

---------------------------------- [Anchor Table] ------------------------------------
-- AN_Anchor table (with 2 attributes)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'AN_Anchor' AND TYPE LIKE '%U%')
CREATE TABLE [AN_Anchor] (
   AN_ID INT IDENTITY(1, 1) NOT NULL,
   Metadata_AN INT NOT NULL,
   PRIMARY KEY (
      AN_ID ASC
   )
);
GO

----------------------- [Key Generation Stored Procedure] ----------------------------
-- AN_Anchor surrogate key generation stored procedure
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'kAN_Anchor' AND TYPE IN ('P','PC'))
DROP PROCEDURE kAN_Anchor
GO
CREATE PROCEDURE kAN_Anchor (
   @requestedNumberOfIdentities BIGINT,
   @Metadata_AN INT
) AS
BEGIN
   IF @requestedNumberOfIdentities > 0
   BEGIN
      WITH rowGenerator (rowNumber) AS (
         SELECT
            1
         UNION ALL
         SELECT
            rowNumber + 1
         FROM
            rowGenerator
         WHERE
            rowNumber < @requestedNumberOfIdentities
      )
      INSERT INTO AN_Anchor(Metadata_AN)
      OUTPUT
         inserted.AN_ID
      SELECT
         @Metadata_AN
      FROM
         rowGenerator
      OPTION (maxrecursion 0);
   END
END
GO

--------------------------------- [Attribute Table] ----------------------------------
-- AN_ONE_Anchor_AttributeOne table (on AN_Anchor)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'AN_ONE_Anchor_AttributeOne' AND TYPE LIKE '%U%')
CREATE TABLE [AN_ONE_Anchor_AttributeOne] (
   AN_ID INT NOT NULL FOREIGN KEY REFERENCES AN_Anchor(AN_ID),
   AN_ONE_Anchor_AttributeOne CHAR(42) NOT NULL,
   Metadata_AN_ONE INT NOT NULL,
   PRIMARY KEY (
      AN_ID ASC
   )
);
GO

--------------------------------- [Attribute Table] ----------------------------------
-- AN_TWO_Anchor_AttributeTwo table (on AN_Anchor)
--------------------------------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'AN_TWO_Anchor_AttributeTwo' AND TYPE LIKE '%U%')
CREATE TABLE [AN_TWO_Anchor_AttributeTwo] (
   AN_ID INT NOT NULL FOREIGN KEY REFERENCES AN_Anchor(AN_ID),
   AN_TWO_Anchor_AttributeTwo CHAR(42) NOT NULL,
   AN_TWO_ValidFrom DATE NOT NULL,
   Metadata_AN_TWO INT NOT NULL,
   PRIMARY KEY (
      AN_ID ASC,
      AN_TWO_ValidFrom DESC
   )
);
GO

------------------------------- [Latest Perspective] ---------------------------------
-- AN_Anchor viewed as is (given by the latest available information)
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'lAN_Anchor' AND TYPE LIKE '%V%')
DROP VIEW [lAN_Anchor];
GO
CREATE VIEW [lAN_Anchor] AS
SELECT
   [AN].AN_ID,
   [AN].Metadata_AN,
   [AN_ONE].AN_ONE_Anchor_AttributeOne,
   [AN_ONE].Metadata_AN_ONE,
   [AN_TWO].AN_TWO_Anchor_AttributeTwo,
   [AN_TWO].AN_TWO_ValidFrom,
   [AN_TWO].Metadata_AN_TWO
FROM
   AN_Anchor [AN]
LEFT JOIN
   AN_ONE_Anchor_AttributeOne [AN_ONE]
ON
   [AN_ONE].AN_ID = [AN].AN_ID
LEFT JOIN
   AN_TWO_Anchor_AttributeTwo [AN_TWO]
ON
   [AN_TWO].AN_ID = [AN].AN_ID
AND
   [AN_TWO].AN_TWO_ValidFrom = (
      SELECT
         MAX(sub.AN_TWO_ValidFrom)
      FROM
         AN_TWO_Anchor_AttributeTwo sub
      WHERE
         sub.AN_ID = [AN].AN_ID
   );
GO

--------------------------------- [Insert Trigger] -----------------------------------
-- AN_Anchor insert trigger on the latest perspective
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'tAN_Anchor')
DROP TRIGGER tAN_Anchor
GO
CREATE TRIGGER tAN_Anchor ON lAN_Anchor
INSTEAD OF INSERT
AS
BEGIN
   DECLARE @Metadata_AN INT;
   SELECT
      @Metadata_AN = Metadata_AN
   FROM
      inserted;
   DECLARE @AN_ID INT;
   SELECT
      @AN_ID = AN_ID
   FROM
      inserted;
   IF(@AN_ID IS NULL)
   BEGIN
      INSERT INTO AN_Anchor(
         Metadata_AN
      )
      VALUES (@Metadata_AN);
      SELECT @AN_ID = SCOPE_IDENTITY();
   END
   INSERT INTO AN_ONE_Anchor_AttributeOne(
      AN_ID,
      AN_ONE_Anchor_AttributeOne,
      Metadata_AN_ONE
   )
   SELECT
      @AN_ID,
      AN_ONE_Anchor_AttributeOne,
      COALESCE(Metadata_AN_ONE, @Metadata_AN)
   FROM
      inserted
   WHERE
      AN_ONE_Anchor_AttributeOne IS NOT NULL
   AND
      COALESCE(Metadata_AN_ONE, @Metadata_AN) IS NOT NULL;
   INSERT INTO AN_TWO_Anchor_AttributeTwo(
      AN_ID,
      AN_TWO_Anchor_AttributeTwo,
      Metadata_AN_TWO,
      AN_TWO_ValidFrom

   )
   SELECT
      @AN_ID,
      AN_TWO_Anchor_AttributeTwo,
      COALESCE(Metadata_AN_TWO, @Metadata_AN),
      AN_TWO_ValidFrom

   FROM
      inserted
   WHERE
      AN_TWO_Anchor_AttributeTwo IS NOT NULL
   AND
      COALESCE(Metadata_AN_TWO, @Metadata_AN) IS NOT NULL
AND      AN_TWO_ValidFrom IS NOT NULL;
END
GO

---------------------------- [Point-in-Time Perspective] -----------------------------
-- AN_Anchor viewed as was (at the given timepoint)
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'pAN_Anchor' AND TYPE LIKE '%F%')
DROP FUNCTION [pAN_Anchor];
GO
CREATE FUNCTION [pAN_Anchor] (@timepoint datetime)
RETURNS TABLE RETURN
SELECT
   [AN].AN_ID,
   [AN].Metadata_AN,
   [AN_ONE].AN_ONE_Anchor_AttributeOne,
   [AN_ONE].Metadata_AN_ONE,
   [AN_TWO].AN_TWO_Anchor_AttributeTwo,
   [AN_TWO].AN_TWO_ValidFrom,
   [AN_TWO].Metadata_AN_TWO
FROM
   AN_Anchor [AN]
LEFT JOIN
   AN_ONE_Anchor_AttributeOne [AN_ONE]
ON
   [AN_ONE].AN_ID = [AN].AN_ID
LEFT JOIN
   AN_TWO_Anchor_AttributeTwo [AN_TWO]
ON
   [AN_TWO].AN_ID = [AN].AN_ID
AND
   [AN_TWO].AN_TWO_ValidFrom = (
      SELECT
         MAX(sub.AN_TWO_ValidFrom)
      FROM
         AN_TWO_Anchor_AttributeTwo sub
      WHERE
         sub.AN_ID = [AN].AN_ID
      AND
         sub.AN_TWO_ValidFrom <= @timepoint
   );
GO

------------------------------ [Difference Perspective] ------------------------------
-- AN_Anchor viewed by differences in AN_TWO_Anchor_AttributeTwo
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dAN_TWO_Anchor_AttributeTwo' AND TYPE LIKE '%F%')
DROP FUNCTION [dAN_TWO_Anchor_AttributeTwo];
GO
CREATE FUNCTION [dAN_TWO_Anchor_AttributeTwo] (@intervalStart datetime, @intervalEnd datetime)
RETURNS TABLE RETURN
SELECT
   timepoints.inspectedTimepoint,
   [AN].*
FROM (
   SELECT DISTINCT
      AN_TWO_ValidFrom AS inspectedTimepoint
   FROM
      AN_TWO_Anchor_AttributeTwo
   WHERE
      AN_TWO_ValidFrom BETWEEN @intervalStart AND @intervalEnd
) timepoints
CROSS APPLY
   pAN_Anchor(timepoints.inspectedTimepoint) [AN];
GO

------------------------------ [Difference Perspective] ------------------------------
-- AN_Anchor viewed by differences in every historized attribute
--------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'dAN_Anchor' AND TYPE LIKE '%F%')
DROP FUNCTION [dAN_Anchor];
GO
CREATE FUNCTION [dAN_Anchor] (@intervalStart datetime, @intervalEnd datetime)
RETURNS TABLE RETURN
SELECT
   timepoints.inspectedTimepoint,
   [AN].*
FROM (
   SELECT DISTINCT
      AN_TWO_ValidFrom AS inspectedTimepoint
   FROM
      AN_TWO_Anchor_AttributeTwo
   WHERE
      AN_TWO_ValidFrom BETWEEN @intervalStart AND @intervalEnd
) timepoints
CROSS APPLY
   pAN_Anchor(timepoints.inspectedTimepoint) [AN];
GO