Mssql 2008 et table modulaire

**darkepsylon** · 19/05/2016, 10h47

Bonjour tout le monde,

je vous explique mon problème :

J'ai conçu une base de données sous MSSQL 2008, les tables de la bases sont alimenté en C# via la lecture d'un fichier fourni par notre client.
jusqu'ici tout va bien, le problème c'est que j'ai appris récemment que le client pouvais à sa guise changer la structure des données dans le fichier qu'il nous fourni.

exemple :

la phrase suivante est dans le fichier : " AAABBBCCCDDDEEEFFFGGGHHH"
aujourd'hui on va dire que la découpe se fait de la maniére suivante : "AAA" dans colonnes A, "BBB" dans colonnes B ...
le souci c'est que demain le client peut décider que désormais le champs A va contenir "AA", puis mettre en place un nouveau champs de 1 caractère pour le 3éme "A"...

ce qui fait que la table qui était composé de la maniére suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
create table A
(
id_machin int identity....,
A varchar(3),
B varchar(3),
C varchar(3),
D varchar(3),
E varchar(3),
F varchar(3),
G varchar(3),
H varchar(3)
)

doit maintenant être composé de cette manière

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
(
id_machin int identity....,
A varchar(3),
NOUVEAUCHAMP varchar(1),
B varchar(3),
C varchar(3),
D varchar(3),
E varchar(3),
F varchar(3),
G varchar(3),
H varchar(3)
)

comment je pourrai faire pour gérer ce genre de cas ? il faudra forcément que je change mes schéma d'importation en C#, mais ce n'est pas un problème, c'est surtout que je me demande comment gérer ça de manière dynamique si c'est possible.

vous avez une idée à me proposer ?

**SQLpro** · 19/05/2016, 16h20

1) mettre vos données dans une table dotée d'une seule colonne (en sus des clefs) représentant l'intégralité de la ligne de données. Par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
CREATE TABLE T_RECORD_RCD
(
RCD_ID             BIGINT IDENTITY PRIMARY KEY,
VRS_ID             INT NOT NULL REFERENCES T_VERSION_VRS (VRS_ID),
RCD_DATA           NVARCHAR(max) NOT NULL);

2) Notez que cette table fait référence à une table des versions que voici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CREATE TABLE T_VERSION_VRS
(
VRS_ID             INT IDENTITY PRIMARY KEY,
VRS_DATE           DATE NOT NULL);

A chaque fois que votre client change de format, vous ajoutez une ligne dans cette table avec la date.

3) enfin il vous faut une table de "découpe" :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
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);

A partir de ces trois tables, vous pourrez créer une requête dynamique qui présente les données de tel ou tel flux entré dans votre système.

A +

**darkepsylon** · 20/05/2016, 09h25

Merci pour votre réponse, je vois bien ce que vous voulez dire, mais dans ce cas ça ne sera valable que pour un affichage des données, je me trompe ?

si j'ai des requête à faire sur ma table par la suite, comment cela va se passer ?

désolé si mes question peuvent paraître "bête", j'ai encore un peu de mal avec sql, mais on va tacher de corriger ça avec vos cours

**SQLpro** · 20/05/2016, 10h50

Envoyé par darkepsylon

Merci pour votre réponse, je vois bien ce que vous voulez dire, mais dans ce cas ça ne sera valable que pour un affichage des données, je me trompe ?

OUI

si j'ai des requête à faire sur ma table par la suite, comment cela va se passer ?

Utiliser une vue créée à la volée après saisie des information de découpe

désolé si mes question peuvent paraître "bête", j'ai encore un peu de mal avec sql, mais on va tacher de corriger ça avec vos cours

À partir de la découpe figurant dans la table T_DECOUPE_DCP vous pouvez générer une requête SQL dynamique ou bien créer une vue.

A +

**SQLpro** · 20/05/2016, 12h51

Voici un exemple complet...

1 - Création des objets pour manipuler tout ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

2 - insertion de la définition de la découpe d'un fichier

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
-- insertion d'une nouvelle version d'import à la date d'ajourd'hui
INSERT INTO S_IMPORT_FILE.T_VERSION_VRS SELECT SYSDATETIME(), 0;
DECLARE @ID INT = SCOPE_IDENTITY();
 
-- decoupe de cette nouvelle version d'import :
INSERT INTO S_IMPORT_FILE.T_DECOUPE_DCP 
       (VRS_ID, DCP_POSITION, DCP_LONGUEUR, DCP_NOM_ATTRIBUT, DCP_TYPE_SQL) 
SELECT @ID    , 1           , 10          , 'DATE_NAISSANCE', 'DATE'     
UNION 
SELECT @ID    , 2           , 32          , 'NOM',            'VARCHAR' ;
 
-- finalisation de la découpe (et création automatique de la vue !
UPDATE S_IMPORT_FILE.T_VERSION_VRS SET VRS_FINAL = 1 WHERE VRS_ID = 1;

Pour simplifier nous avons considéré que ce fichier ne contient que 2 rubrique : date de naissance et nom
la date de naissance est sur 10 caractères en position 1 et le nom sur 32 caractères en position 2

L'update final indique que la définition de la découpe est terminé et permet de créer automatiquement la vue

3 - on simule l'insertion d'un fichier tel que décris dans la version 1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
INSERT INTO S_IMPORT_FILE.T_RECORD_RCD (VRS_ID,  RCD_DATA)
SELECT 1, '1970-11-25MARTIN                          '
UNION ALL
SELECT 1, '1960-12-31DUPONT                          '
UNION ALL
SELECT 1, '1980-03-16DUPOND                          ';

4 - test de la vue automatiquement créée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT *
FROM    S_IMPORT_FILE.V_1
WHERE   DATE_NAISSANCE > '1968-05-30'

A +

**darkepsylon** · 20/05/2016, 13h19

super, merci beaucoup

je regarde ça avec attention.

**darkepsylon** · 20/05/2016, 13h49

j'ai une erreur dans le code suivant :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
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

Msg*102, Niveau*15, État*1, Procédure*V_DECOUPE_DCP, Ligne*3
Incorrect syntax near 'order'.

je vois pas ce qui cloche avec le order, je suis aveugle ?

**darkepsylon** · 20/05/2016, 14h10

j'ai trouver ma réponse, ce n'est pas supporté dans sql server 2008

Mssql 2008 et table modulaire [2008R2]

Développement SQL Server

Discussions similaires

Partager

Partager