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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
|
Soit une base de données de contacts, très mal modélisée :
Une seule table
Des colonnes nullables
Une sémantique redondante entre certaines colonnes
create table contact
(
id int primary key not null identity,
nom varchar(50) not null,
prenom varchar(50) not null,
telephone_maison varchar(10) null check (telephone_maison like replicate('[0-9]', 10)),
telephone_mobile varchar(10) null check (telephone_mobile like replicate('[0-9]', 10)),
telephone_boulot varchar(10) null check (telephone_boulot like replicate('[0-9]', 10)),
unique (nom, prenom)
);
On souhaite effectuer des recherches simple en saisissant un numéro de téléphone. La recherche doit alors retourner toutes les lignes de contact où l’un des trois numéros de téléphone est égal au numéro rechercher.
La table étant très mal modélisée, on a au départ tenté :
select *
from contact
where telephone_maison = '0123456789' or telephone_mobile = '0123456789' or telephone_boulot = '0123456789';
Mais malgré la création d’index, cette requête est très lente (le OR est non sargable) et franchement pas très jolie. Surtout qu’on a pour objectif d’ajouter un jour “telephone_conjoint”, donc il faudra modifier la requête.
On a donc créé une vue permettant d’effectuer cette recherche plus facilement, et de façon plus performante :
create view contact_telephone (id, nom, prenom, type_telephone, numero_telephone)
as
select id, nom, prenom, 'MAISON', telephone_maison
from contact
union
select id, nom, prenom, 'MOBILE', telephone_mobile
from contact
union
select id, nom, prenom, 'BOULOT', telephone_boulot
from contact;
Après mure réflexion, cette modélisation est parfaitement pourrie, et on souhaite l’améliorer.
Seulement, on a un programme qui permet de faire du CRUD sur la table “contact”, et de faire des recherches à l’aide de la vue “contact_telephone”.
Ce programme étant écrit en COBOL, et son auteur étant parti à la retraite à l’époque où vous ne saviez même pas encore lire, vous préférez éviter d’avoir à le toucher, même si, à terme, vous serez certainement amenés à le remplacer.
Voici donc comment procéder.
On va, dans un premier temps, créer les tables “propres”.
create table personne
(
id int primary key not null identity,
nom varchar(50) not null,
prenom varchar(50) not null,
unique (nom, prenom)
);
create table telephone
(
id int primary key not null identity,
personne_id int not null references personne(id),
type_telephone varchar(6) not null check (type_telephone in ('MAISON', 'MOBILE', 'BOULOT')),
numero_telephone varchar(10) not null check (numero_telephone like replicate('[0-9]', 10))
);
Puis recopier notre table poubelle dedans :
set identity_insert personne on;
insert into personne (id, nom, prenom)
select id, nom, prenom
from contact;
set identity_insert personne off;
insert into telephone (personne_id, type_telephone, numero_telephone)
select id, type_telephone, numero_telephone
from contact_telephone
where numero_telephone is not null;
Et on va maintenant transformer la table poubelle en vue toute propre :
drop table contact;
go
create view contact (id, nom, prenom, telephone_maison, telephone_mobile, telephone_boulot)
as
select p.id, p.nom, p.prenom, t1.numero_telephone, t2.numero_telephone, t3.numero_telephone
from personne p
left outer join telephone t1 on t1.personne_id = p.id and t1.type_telephone = 'MAISON'
left outer join telephone t2 on t2.personne_id = p.id and t2.type_telephone = 'MOBILE'
left outer join telephone t3 on t3.personne_id = p.id and t3.type_telephone = 'BUREAU';
go
Seul hic, si on tente de créer/modifier/supprimer des lignes, ça ne fait pas trop ce qu’on veut…
INSERT :
Msg 4405, Level 16, State 1, Line 75
View or function 'contact' is not updatable because the modification affects multiple base tables.
UPDATE :
(0 row(s) affected)
Lorsqu’on tente de mettre à jour un numéro de téléphone.
DELETE :
Msg 4405, Level 16, State 1, Line 79
View or function 'contact' is not updatable because the modification affects multiple base tables.
On va donc devoir créer quelques triggers pour gérer correctement les différents cas :
create trigger trg_contact_ins
on contact
instead of insert
as
begin
insert into personne (nom, prenom)
select nom, prenom
from inserted;
insert into telephone (personne_id, type_telephone, numero_telephone)
select personne.id, 'MAISON', inserted.telephone_maison
from inserted
inner join personne on personne.nom = inserted.nom and personne.prenom = inserted.prenom
where telephone_maison is not null
union
select personne.id, 'MOBILE', inserted.telephone_mobile
from inserted
inner join personne on personne.nom = inserted.nom and personne.prenom = inserted.prenom
where telephone_mobile is not null
union
select personne.id, 'BOULOT', inserted.telephone_boulot
from inserted
inner join personne on personne.nom = inserted.nom and personne.prenom = inserted.prenom
where telephone_boulot is not null;
end;
go
create trigger trg_contact_del
on contact
instead of delete
as
begin
delete telephone where personne_id in (select id from deleted);
delete personne where id in (select id from deleted);
end;
go
create trigger trg_contact_upd
on contact
instead of update
as
begin
update personne
set nom = i.nom, prenom = i.prenom
from inserted i
where personne.id = i.id and (personne.nom <> i.nom or personne.prenom <> i.prenom);
insert into telephone (personne_id, type_telephone, numero_telephone)
select i.id, 'MAISON', i.telephone_maison from inserted i left outer join deleted d on d.id = i.id where d.telephone_maison is null and i.telephone_maison is not null
union
select i.id, 'MOBILE', i.telephone_mobile from inserted i left outer join deleted d on d.id = i.id where d.telephone_mobile is null and i.telephone_mobile is not null
union
select i.id, 'BOULOT', i.telephone_boulot from inserted i left outer join deleted d on d.id = i.id where d.telephone_boulot is null and i.telephone_boulot is not null;
update telephone
set numero_telephone = tmp.numero_telephone
from (
select i.id personne_id, 'MAISON' type_telephone, i.telephone_maison numero_telephone from inserted i inner join deleted d on d.id = i.id where d.telephone_maison <> i.telephone_maison
union
select i.id, 'MOBILE', i.telephone_mobile from inserted i inner join deleted d on d.id = i.id where d.telephone_mobile <> i.telephone_mobile
union
select i.id, 'BOULOT', i.telephone_boulot from inserted i inner join deleted d on d.id = i.id where d.telephone_boulot <> i.telephone_boulot
) tmp
where telephone.personne_id = tmp.personne_id and telephone.type_telephone = tmp.type_telephone;
delete telephone
from inserted
inner join deleted on deleted.id = inserted.id
where (deleted.telephone_maison is not null and inserted.telephone_maison is null and telephone.type_telephone = 'MAISON')
or (deleted.telephone_mobile is not null and inserted.telephone_mobile is null and telephone.type_telephone = 'MOBILE')
or (deleted.telephone_boulot is not null and inserted.telephone_boulot is null and telephone.type_telephone = 'BOULOT')
end;
go
drop view contact_telephone;
go
create view contact_telephone (id, nom, prenom, type_telephone, numero_telephone)
as
with type_telephone (libelle)
as
(
select 'MAISON'
union all
select 'MOBILE'
union all
select 'BOULOT'
)
select p.id, p.nom, p.prenom, type_telephone.libelle, t.numero_telephone
from personne p
cross join type_telephone
left outer join telephone t on t.personne_id = p.id and t.type_telephone = type_telephone.libelle;
Et voilà ! Maintenant, le vieux programme travaille dans une jolie base bien modélisée sans même s'en rendre compte ! |
Changement de la base de données
Répondre avec citation
Envoyé par manu49
Partager