1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
| CREATE SCHEMA S_IMPORT_FILE
CREATE TABLE T_RECORD_RCD
(
RCD_ID BIGINT IDENTITY PRIMARY KEY,
RCD_DH_IMPORT DATETIME2 DEFAULT SYSDATETIME(),
VRS_ID INT NOT NULL REFERENCES T_VERSION_VRS (VRS_ID),
RCD_DATA NVARCHAR(max) NOT NULL)
CREATE TABLE T_VERSION_VRS
(
VRS_ID INT IDENTITY PRIMARY KEY,
VRS_DATE DATE NOT NULL,
VRS_FINAL BIT NOT NULL DEFAULT 0)
CREATE TABLE T_DECOUPE_DCP
(
DCP_ID BIGINT IDENTITY PRIMARY KEY,
VRS_ID INT REFERENCES T_VERSION_VRS (VRS_ID),
DCP_POSITION SMALLINT NOT NULL CHECK (DCP_POSITION > 0),
DCP_LONGUEUR TINYINT NOT NULL,
DCP_NOM_ATTRIBUT SYSNAME,
DCP_TYPE_SQL VARCHAR(16) NOT NULL,
CONSTRAINT UK_DCP_POS_VRS UNIQUE(VRS_ID,DCP_POSITION));
GO
CREATE VIEW S_IMPORT_FILE.V_DECOUPE_DCP
AS
SELECT *, 1 - DCP_LONGUEUR + SUM(DCP_LONGUEUR) OVER(PARTITION BY VRS_ID ORDER BY DCP_POSITION) AS DEBUT,
COUNT(*) OVER(PARTITION BY VRS_ID) AS NB_COLS
FROM S_IMPORT_FILE.T_DECOUPE_DCP
GO
-- controle de position => pas de "trou" dans la position demarrant à 1 et continue pour une même version.
CREATE TRIGGER E_IUD_DCP
ON S_IMPORT_FILE.T_DECOUPE_DCP
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON;
IF NOT UPDATE(DCP_POSITION)
RETURN;
IF EXISTS(SELECT 1
FROM S_IMPORT_FILE.T_DECOUPE_DCP
WHERE VRS_ID IN (SELECT DISTINCT VRS_ID FROM inserted
UNION
SELECT DISTINCT VRS_ID FROM deleted)
GROUP BY VRS_ID
HAVING MIN(DCP_POSITION) <> 1
OR MAX(DCP_POSITION) <> COUNT(*))
BEGIN
ROLLBACK;
RAISERROR('La numérotation des positions pour une mêmme version doit commencer à 1 et continuer sans trou jusqu''à la dernière position', 16, 1);
END;
GO
-- trigger pour créer automatiquement la vue adéquate pour les données à découper
CREATE TRIGGER E_U_VRS
ON S_IMPORT_FILE.T_VERSION_VRS
FOR UPDATE
AS
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max);
SET @SQL = N'';
WITH
T_HEAD AS
(SELECT VRS_ID, N'IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEW WHERE TABLE_SCHEMA = '' S_IMPORT'' AND TABLE NAME = ''V_''' + CAST(VRS_ID AS NVARCHAR(16)) + '''' + ' DROP VIEW S_IMPORT.V_' + CAST(VRS_ID AS NVARCHAR(16)) + ';' AS SQLCMD
FROM inserted
),
T_BODY AS
(SELECT H.VRS_ID, DCP_POSITION, NB_COLS,
CAST(N'CREATE VIEW S_IMPORT_FILE.V_' + CAST(H.VRS_ID AS NVARCHAR(16)) + N' AS SELECT RCD_DH_IMPORT,'
+ N' CAST(SUBSTRING(RCD_DATA, ' + CAST(DEBUT AS VARCHAR(16)) + N', ' + CAST(DCP_LONGUEUR AS VARCHAR(16)) + N') AS ' + DCP_TYPE_SQL + N') [' + DCP_NOM_ATTRIBUT + N']' AS NVARCHAR(max)) AS SQLCMD
FROM S_IMPORT_FILE.V_DECOUPE_DCP AS D
JOIN T_HEAD AS H
ON D.VRS_ID = H.VRS_ID
WHERE DCP_POSITION = 1
UNION ALL
SELECT B.VRS_ID, D.DCP_POSITION, D.NB_COLS,
SQLCMD + N', CAST(SUBSTRING(RCD_DATA, ' + CAST(DEBUT AS VARCHAR(16)) + N', ' + CAST(DCP_LONGUEUR AS VARCHAR(16)) + N') AS ' + DCP_TYPE_SQL + N') [' + DCP_NOM_ATTRIBUT + N']' AS SQLCMD
FROM T_BODY AS B
JOIN S_IMPORT_FILE.V_DECOUPE_DCP AS D
ON B.VRS_ID = D.VRS_ID
AND B.DCP_POSITION + 1 = D.DCP_POSITION
)
SELECT @SQL = @SQL + SQLCMD + N' FROM S_IMPORT_FILE.T_RECORD_RCD WHERE VRS_ID = ' + CAST(VRS_ID AS VARCHAR(16)) +';'
FROM T_BODY WHERE DCP_POSITION = NB_COLS;
EXEC (@SQL)
GO |
