--===========================================================================--
-- TABLES temporelles  /   MS Cloud Summit 2017   /  2017-01-09  version 1.0 --
--===========================================================================--
-- Fred Brouard, SQLpro -  http://sqlpro.developpez.com - SQLpro@SQLspot.com --
-- Arian papillon -        http://www.datafly.fr/ -   a.papillon@datafly.com --
--===========================================================================--

--▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄--
-- Exemples 6 : naviguer dans le temps                                       --
--▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀--

/*
USE master;
DROP DATABASE DB_TEMPORAL;
GO
CREATE DATABASE DB_TEMPORAL
GO
*/

USE DB_TEMPORAL
GO

CREATE TABLE T_EQUIPE_EQP
(EQP_ID                     INT NOT NULL,
 EQP_COULEUR                VARCHAR(16),
 EQP_POINTS                 SMALLINT,
 _EQP_DH_DEBUT              DATETIME2 NOT NULL,
 _EQP_DH_FIN                DATETIME2 NOT NULL);

CREATE TABLE T_EQUIPE_HISTO_EQP
(EQP_ID                     INT NOT NULL,
 EQP_COULEUR                VARCHAR(16),
 EQP_POINTS                 SMALLINT,
 _EQP_DH_DEBUT              DATETIME2 NOT NULL,
 _EQP_DH_FIN                DATETIME2 NOT NULL);

 -- equipe bleu :
INSERT INTO T_EQUIPE_HISTO_EQP
VALUES (1, 'BLEU', 10, '2001-01-01 08:00', '2001-01-01 10:00');
INSERT INTO T_EQUIPE_EQP
VALUES (1, 'BLEU', 20, '2001-01-01 10:00', '9999-12-31 23:59:59.9999999');

-- équipe rouge :
INSERT INTO T_EQUIPE_HISTO_EQP
VALUES (2, 'ROUGE', 5, '2001-01-01 08:00', '2001-01-01 09:00');
INSERT INTO T_EQUIPE_HISTO_EQP
VALUES (2, 'ROUGE', 15, '2001-01-01 09:00', '2001-01-01 10:00');
INSERT INTO T_EQUIPE_EQP
VALUES (2, 'ROUGE', 25, '2001-01-01 10:00', '9999-12-31 23:59:59.9999999');

-- équipe verte :
INSERT INTO T_EQUIPE_HISTO_EQP
VALUES (3, 'VERTE', 7, '2001-01-01 00:00', '2001-01-01 08:30');
INSERT INTO T_EQUIPE_HISTO_EQP
VALUES (3, 'VERTE', 11, '2001-01-01 08:30', '2001-01-01 08:30'); --> sympa non ????
INSERT INTO T_EQUIPE_HISTO_EQP
VALUES (3, 'VERTE', 17, '2001-01-01 08:30', '2001-01-01 09:30');
INSERT INTO T_EQUIPE_EQP
VALUES (3, 'VERTE', 23, '2001-01-01 09:30', '9999-12-31 23:59:59.9999999');

--> ajout d'une clef primaire sur la table historisée
ALTER TABLE dbo.T_EQUIPE_EQP ADD CONSTRAINT PK_EQP PRIMARY KEY (EQP_ID);

--> ajout d'un index clustered sur la table d'historique
CREATE CLUSTERED INDEX X_EQP_DH_DEB_FIN ON dbo.T_EQUIPE_HISTO_EQP (_EQP_DH_FIN,_EQP_DH_DEBUT)

-- mise en place de la période
ALTER TABLE T_EQUIPE_EQP
   ADD PERIOD FOR SYSTEM_TIME (_EQP_DH_DEBUT, _EQP_DH_FIN);
GO

-- et du versionnement
ALTER TABLE dbo.T_EQUIPE_EQP
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.T_EQUIPE_HISTO_EQP, 
                                DATA_CONSISTENCY_CHECK = ON));
GO

-- vérification
SELECT * FROM dbo.T_EQUIPE_EQP
UNION ALL
SELECT * FROM dbo.T_EQUIPE_HISTO_EQP
ORDER BY 1, 4

--> notez la période de durée 0 acceptée par le système !
SELECT *, DATEDIFF(s, _EQP_DH_DEBUT, _EQP_DH_FIN) AS DUREE
FROM dbo.T_EQUIPE_HISTO_EQP
ORDER BY EQP_COULEUR, _EQP_DH_DEBUT;

--> notez la synthèse faite par SYSTEM_TIME ALL !
SELECT * 
FROM  dbo.T_EQUIPE_EQP FOR SYSTEM_TIME ALL
ORDER BY EQP_COULEUR, _EQP_DH_DEBUT;

SELECT * 
FROM  dbo.T_EQUIPE_EQP FOR SYSTEM_TIME AS OF '2001-01-01 09:00'
ORDER BY EQP_COULEUR, _EQP_DH_DEBUT;

SELECT * 
FROM  dbo.T_EQUIPE_EQP FOR SYSTEM_TIME FROM '2001-01-01 08:30' TO '2001-01-01 09:30'
ORDER BY EQP_COULEUR, _EQP_DH_DEBUT;

SELECT * 
FROM  dbo.T_EQUIPE_EQP FOR SYSTEM_TIME BETWEEN '2001-01-01 08:30' AND '2001-01-01 09:30'
ORDER BY EQP_COULEUR, _EQP_DH_DEBUT;

SELECT * 
FROM  dbo.T_EQUIPE_EQP FOR SYSTEM_TIME CONTAINED IN ('2001-01-01 08:30', '2001-01-01 09:30')
ORDER BY EQP_COULEUR, _EQP_DH_DEBUT;


/*
DROP TABLE dbo.T_EQUIPE_EQP
DROP TABLE dbo.T_EQUIPE_HISTO_EQP
*/