Discussion :

[Bill Fassinou]

La norme SQL:2023 est finalisée et livrée avec de nouvelles fonctionnalités pour le format JSON, des modifications du langage SQL existant
et les requêtes sur les graphes de propriétés

Le groupe de travail chargé de la normalisation du langage SQL a annoncé ce 1er juin 2023 que les travaux sur la norme SQL:2023 sont terminés. La nouvelle norme SQL apporte un large éventail de mises à jour dans 3 domaines principaux : une série de modifications du langage SQL existant, de nouvelles fonctionnalités pour JSON et l'introduction d'une nouvelle section pour les requêtes sur les graphes de propriétés pour définir et parcourir les graphes dans un SGBD relationnel. SQL:2023 comprend également d'autres types d'optimisation du langage et la publication de ce nouveau standard constitue un événement marquant pour la communauté de la science des données.

SQL (Structured Query Language - langage de requête structuré) est un langage standardisé permettant de gérer les bases de données relationnelles et effectuer diverses opérations sur les données qu'elles contiennent. Créé dans les années 1970, SQL est régulièrement utilisé non seulement par les administrateurs de bases de données, mais également par les développeurs qui écrivent des scripts d'intégration de données et par les analystes de données cherchant à mettre en place et à exécuter des requêtes analytiques. La norme est régulièrement mise à jour pour ajouter de nouvelles fonctionnalités et pour apporter des modifications au langage existant.

Le nom complet du standard SQL est ISO/IEC 9075 « Database Language SQL ». La mise à jour la plus récente est apparue en 2016. La version de SQL parue en 2016 a la référence ISO/IEC 9075:2016, ou plus simplement SQL:2016. Le standard SQL:2023 vient d'être finalisé et le texte final a été soumis par le groupe de travail au Secrétariat central de l'ISO. Selon les notes de version disponibles actuellement sur SQL:2023, la norme apporte un certain nombre de changements qui couvrent trois domaines principaux. En ce qui concerne le langage SQL existant, il y a des changements mineurs et d'autres majeurs, tous sous la forme de fonctionnalités optionnelles.

Modifications apportées au langage SQL existant

SQL:2023 clarifie le traitement des valeurs "null" dans les contraintes uniques (F292), en le rendant plus explicite et en réduisant l'ambiguïté précédente. Elle permet une plus grande flexibilité dans l'ordre des tables groupées (F868), ce qui n'était techniquement pas autorisé auparavant. Il introduit notamment des fonctions telles que GREATEST et LEAST (T054), LPAD et RPAD (T055), et des fonctions de découpage à plusieurs caractères (T056). Ces fonctions promettent d'améliorer l'efficacité et la convivialité du langage SQL. Les fonctions GREATEST et LEAST étaient déjà présentes dans de nombreuses implémentations. Voici quelques exemples :

• Remarque :

Dans d'autres langages de programmation, les fonctions GREATEST et LEAST auraient simplement pu s'appeler max et min. Mais en SQL, ces noms sont déjà utilisés pour les fonctions d'agrégation. Comme il n'y a pas de différence syntaxique entre les fonctions normales et les fonctions agrégées, vous devez choisir deux noms différents.

Code SQL :

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

1
2
SELECT greatest(1, 2);  --> 2
SELECT least(1, 2);     --> 1

• Le nombre d'arguments pris en charge est illimité :

Code SQL :

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

1
2
SELECT greatest(1, 2, 3);  --> 3
SELECT least(1, 2, 3);     --> 1

• Possibilité d'effectuer des calculs plus complexes :

Code SQL :

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

1
2
SELECT greatest(base_price * 0.10, fixed_fee) FROM data ...
SELECT least(standard, discount) FROM data ...

Nouvelles fonctionnalités liées au format JSON

Le deuxième domaine de changement concerne les fonctionnalités liées à JSON. SQL:2023 inclut officiellement un type de données JSON (T801), ce qui améliore sa compatibilité avec les langages de programmation modernes. La norme autorise désormais les entiers hexadécimaux dans le langage de chemin SQL/JSON (T840), une amélioration qui aligne les capacités JSON de SQL sur le JavaScript moderne. En outre, elle introduit l'utilisation d'une syntaxe d'accesseur simplifiée, qui permet un accès facile à des parties de valeurs JSON (T860-T864), et des méthodes d'éléments SQL/JSON (T865-T878) axées sur les conversions de types de données.

Vous pouvez appliquer ces fonctions ou ces méthodes aux valeurs SQL/JSON à l'intérieur du langage SQL/JSON. SQL:2016 en contenait déjà un certain nombre, comme abs(), floor(), size(). Les méthodes introduites par SQL:2023 sont :

• T865 : bigint() ;
• T866 : boolean() ;
• T867 : date() ;
• T868 : decimal() ;
• T869 : decimal() avec précision et échelle ;
• T870 : integer() ;
• T871 : number() ;
• T872 : string() ;
• T873 : time() ;
• T874 : time_tz() ;
• T875 : time precision ;
• T876 : timestamp() ;
• T877 : timestamp_tz() ;
• T878 : timestamp precision.

Requêtes sur les graphes de propriétés

SQL:2023 introduit des requêtes de graphe de propriété (SQL/PGQ) en tant que partie entièrement nouvelle de la norme SQL. SQL/PGQ est un sous-ensemble de la norme GQL émergente. Ainsi, SQL/PGQ réduit encore la différence de fonctionnalité entre les SGBD relationnels et les SGBD de graphes natifs. En gros, cette nouvelle fonctionnalité facilite l'interrogation des données dans les tables comme si elles se trouvaient dans une base de données graphique, offrant ainsi une alternative, peut-être plus intuitive, à l'écriture de requêtes de jointure complexes. Cette fonctionnalité pourrait très accueillie dans la communauté de la science des données.

Code SQL :

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
CREATE TABLE person (...);
CREATE TABLE company (...);
CREATE TABLE ownerof (...);
CREATE TABLE transaction (...);
CREATE TABLE account (...);
 
CREATE PROPERTY GRAPH financial_transactions
    VERTEX TABLES (person, company, account)
    EDGE TABLES (ownerof, transaction);
 
SELECT owner_name,
       SUM(amount) AS total_transacted
FROM financial_transactions GRAPH_TABLE (
  MATCH (p:person WHERE p.name = 'Alice')
        -[:ownerof]-> (:account)
        -[t:transaction]- (:account)
        <-[:ownerof]- (owner:person|company)
  COLUMNS (owner.name AS owner_name, t.amount AS amount)
) AS ft
GROUP BY owner_name;

Dans cet exemple, toutes les tables doivent avoir des clés étrangères entre elles afin que la définition du graphe des propriétés puisse déterminer comment elles sont connectées. Il existe également une syntaxe permettant de spécifier les connexions dans la définition du graphe des propriétés s'il n'y a pas de clés étrangères. Par ailleurs, les nouveautés introduites par la norme SQL:2023 reflètent le désir permanent de gérer davantage de données de manière de plus en plus polyvalente, en maintenant SQL, les données relationnelles et les données organisées par schéma au centre de la gestion des données.

SQL:2023 prouve que si le langage SQL relationnel de base reste solide, il est toujours possible de l'améliorer et de le moderniser. Cette nouvelle norme arrive après la deuxième plus longue période d'interruption dans l'histoire de SQL (après 1992-1999), la pandémie de la Covid-19 étant un facteur important qui a contribué à ce retard. Malgré cela, l'équipe travaille déjà sur les futures itérations de la norme SQL, avec à l'horizon de nouvelles améliorations pour PGQ, JSON et le langage de base.

Source : Annonce de SQL:2023

Et vous ?

Que pensez-vous des nouveautés introduites par SQL:2023 ?

Voir aussi

Les travaux sur la norme C++ 23 sont terminés et cette nouvelle version porte le nom de code "Pandemic Edition", C++ 23 cherche à améliorer la vitesse de compilation et l'hygiène du code

Nubank annonce que les versions Pro et Cloud de Datomic, sa base de données distribuée, sont désormais disponibles gratuitement sous licence Apache 2.0, mais les sources des binaires restent fermées

sqlite-gui, un éditeur de base de données SQLite pour les systèmes Windows, l'outil est gratuit et basé sur C++/mingw64/WinAPI

Le Big Data serait mort, d'après Jordan Tigani, ingénieur fondateur de Google BigQuery, alors que pour IDC, le marché du Big Data enregistrera une forte croissance dans les années à venir

[escartefigue]

Modifications apportées au langage SQL existant

Code SQL :

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

1
2
SELECT greatest(1, 2);  --> 2
SELECT least(1, 2);     --> 1

• Le nombre d'arguments pris en charge est illimité :

Code SQL :

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

1
2
SELECT greatest(1, 2, 3);  --> 3
SELECT least(1, 2, 3);     --> 1

• Possibilité d'effectuer des calculs plus complexes :

Code SQL :

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

1
2
SELECT greatest(base_price * 0.10, fixed_fee) FROM data ...
SELECT least(standard, discount) FROM data ...

Rien de nouveau à ce stade, ces fonctions sont déjà opérationnelles sur la plupart des SGBD

Requêtes sur les graphes de propriétés

SQL:2023 introduit des requêtes de graphe de propriété (SQL/PGQ) en tant que partie entièrement nouvelle de la norme SQL. SQL/PGQ est un sous-ensemble de la norme GQL émergente. Ainsi, SQL/PGQ réduit encore la différence de fonctionnalité entre les SGBD relationnels et les SGBD de graphes natifs. En gros, cette nouvelle fonctionnalité facilite l'interrogation des données dans les tables comme si elles se trouvaient dans une base de données graphique, offrant ainsi une alternative, peut-être plus intuitive, à l'écriture de requêtes de jointure complexes. Cette fonctionnalité pourrait très accueillie dans la communauté de la science des données.

Code SQL :

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
CREATE TABLE person (...);
CREATE TABLE company (...);
CREATE TABLE ownerof (...);
CREATE TABLE transaction (...);
CREATE TABLE account (...);
 
CREATE PROPERTY GRAPH financial_transactions
    VERTEX TABLES (person, company, account)
    EDGE TABLES (ownerof, transaction);
 
SELECT owner_name,
       SUM(amount) AS total_transacted
FROM financial_transactions GRAPH_TABLE (
  MATCH (p:person WHERE p.name = 'Alice')
        -[:ownerof]-> (:account)
        -[t:transaction]- (:account)
        <-[:ownerof]- (owner:person|company)
  COLUMNS (owner.name AS owner_name, t.amount AS amount)
) AS ft
GROUP BY owner_name;

Un exemple concret avec un petit jeu d'essai en entrée et le résultat correspondant aurait été le bienvenu.

[Séb.]

J'espère les voir en prod. de mon vivant

[SQLpro]

J'espère les voir en prod. de mon vivant

Ça existe déjà dans SQL Server depuis la version 2019... Un petite démo ? (je la donne en cours avancé...). Normal Microsoft est actuellement le plus gros contributeur de la norme SQL ! Quant aux fonctions GREATEST et LEAST elles sont présentes dans SQL Server 2022.

1 - Création de la base de données

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
USE master;
GO
 
DROP DATABASE IF EXISTS DB_GRAPH_DEMO;
GO
 
CREATE DATABASE DB_GRAPH_DEMO;
GO
 
USE DB_GRAPH_DEMO;
GO
 
-- création d'une table de noeuds pour les acteurs
CREATE TABLE T_ACTEUR_ACT
(ACT_ID        INT PRIMARY KEY,
 ACT_PRENOM    VARCHAR(32),
 ACT_NOM       VARCHAR(50)) AS NODE;
 
-- création d'une table de noeuds pour les films
CREATE TABLE T_FILM_FLM
(FLM_ID        INT PRIMARY KEY,
 FLM_TITRE     VARCHAR(250),
 FLM_ANNEE     SMALLINT) AS NODE;
 
-- création d'une table d'arrête pour lier les films et les acteurs
CREATE TABLE T_JOUE_JOU (JOU_INTERPRETE VARCHAR(250)) AS EDGE;
GO
 
-- création d'une contrainte pour spécifier de quels noeuds viennent les connections
ALTER TABLE T_JOUE_JOU 
   ADD CONSTRAINT EK_JOU_FLM_ACT CONNECTION (T_ACTEUR_ACT TO T_FILM_FLM) 
      ON DELETE NO ACTION;
GO

2 - Insertion des films et des acteurs

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
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
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
-- premier film
 
INSERT INTO T_FILM_FLM VALUES
(1, 'Rebecca', 1940);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10001, 'Laurence', 'Olivier'),
(10002, 'Joan', 'Fontaine'),
(10003, 'Georges', 'Sanders');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10001), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 1), 'George Fortescue Maximilien de Winter'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10002), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 1), 'Mrs de Winter'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10003), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 1), 'Jack Favell');
GO
 
-- second film
 
INSERT INTO T_FILM_FLM VALUES
(2, 'Correspondant 17', 1940);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10004, 'Joel', 'McCrea'),
(10005, 'Laraine', 'Day'),
(10006, 'Herbert', 'Marshall');
 
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10004), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 2), 'John Jones / Huntley Haverstock'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10005), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 2), 'Carol Fisher'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10006), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 2), 'Stephen Fisher'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10003), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 2), 'Scott Ffolliott');
GO
 
-- 3e film
 
INSERT INTO T_FILM_FLM VALUES
(3, 'Soupçons', 1941);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10007, 'Cary', 'Grant'),
(10014, 'Leo', 'G. Caroll');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10002), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 3), 'Lina McLaidlaw Aysgarth'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10007), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 3), 'Johnnie Aysgarth'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10014), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 3), 'capitaine George Melbeck')
;
 
 
-- 4e film
 
INSERT INTO T_FILM_FLM VALUES
(4, 'L''Ombre d''un doute', 1943);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10008, 'Joseph', 'Cotten'),
(10017, 'Teresa', 'Wright');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10008), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 4), 'Charlie Oakley'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10017), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 4), 'Charlotte Newton');
 
-- 5e film
 
INSERT INTO T_FILM_FLM VALUES
(5, 'La Maison du docteur Edwardes', 1945);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10009, 'Gregory', 'Peck'),
(10010, 'Ingrid', 'Bergman');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10009), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 5), 'John Ballantine / Dr Anthony Edwardes / John Brown'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10010), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 5), 'Dr Constance Petersen'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10014), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 5), 'Dr Murchinson');
 
 
-- 6e film
 
INSERT INTO T_FILM_FLM VALUES
(6, 'Les Enchaînés', 1946);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10018, 'Claude', 'Rains');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10007), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 6), 'T.R. Devlin'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10010), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 6), 'Alicia Huberman'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10018), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 6), 'Alexander Sebastian');
 
 
-- 7e film
 
INSERT INTO T_FILM_FLM VALUES
(7, 'Le Procès Paradine', 1947);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10011, 'Charles', 'Laughton'),
(10019, 'Ann', 'Todd'),
(10020, 'Louis', 'Jourdan');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10009), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 7), 'Anthony Keane'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10011), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 7), 'Lord Thomas Horfield (juge)'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10019), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 7), 'Gay Keane'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10020), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 7), 'André Latour'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10014), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 7), 'Sir Joseph');
 
-- 8e film
 
INSERT INTO T_FILM_FLM VALUES
(8, 'la Corde', 1948);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10012, 'Farley', 'Granger'),
(10013, 'James', 'Stewart');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10012), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 8), 'Philip Morgan'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10013), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 8), 'Rupert Cadell');
 
 
-- 9e film
 
INSERT INTO T_FILM_FLM VALUES
(9, 'Les Amants du Capricorne', 1949);
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10008), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 9), 'Sam Flusky'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10010), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 9), 'Henrietta Flusky');
 
-- 10e film
 
INSERT INTO T_FILM_FLM VALUES
(10, 'L''Inconnu du Nord-Express', 1951);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10021, 'Ruth', 'Roman'),
(10022, 'Robert', 'Walker'),
(10023, 'Patricia', 'Hitchcock');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10012), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 10), 'Guy Haines'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10014), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 10), 'sénateur Morton'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10021), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 10), 'Anne Morton'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10022), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 10), 'Bruno Antony'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10023), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 10), 'Barbara Morton');
 
 
-- 11e film
 
INSERT INTO T_FILM_FLM VALUES
(11, 'Le crime était presque parfait', 1954);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10015, 'Ray', 'Milland'),
(10016, 'Grace', 'Kelly'),
(10024, 'Robert', 'Cummings'),
(10025, 'John', 'Williams');
;
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10015), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 11), 'Tony Wendice'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10016), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 11), 'Margot Wendice'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10024), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 11), 'Mark Halliday'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10025), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 11), 'inspecteur Hubbard');
 
-- 12e film
 
INSERT INTO T_FILM_FLM VALUES
(12, 'Fenêtre sur cour', 1954);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10026, 'Raymond', 'Burr');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10013), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 12), 'Jeff Jefferies'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10016), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 12), 'Lisa Carol Fremont'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10026), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 12), 'Lars Thorwald');
 
-- 13e film
 
INSERT INTO T_FILM_FLM VALUES
(13, 'La Main au collet', 1955);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10027, 'Charles', 'Vanel'),
(10028, 'Brigitte', 'Auber'),
(10029, 'Jessie', 'Royce Landis');
 
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10007), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 13), 'Georges Robert dit « le Chat » (John Robie dans la version originale)'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10016), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 13), 'Frances Stevens'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10027), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 13), 'Bertani'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10028), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 13), 'Danielle Foussard'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10029), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 13), 'Jessie Stevens'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10025), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 13), 'H. H. Hughson');
 
-- 14e film
 
INSERT INTO T_FILM_FLM VALUES
(14, 'L''Homme qui en savait trop', 1956);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10030, 'Doris', 'Day'),
(10031, 'Daniel', 'Gélin');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10007), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 14), 'docteur Ben McKenna'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10030), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 14), 'Dorothée McKenna'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10031), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 14), 'Louis Bernard');
 
-- 15e film
 
INSERT INTO T_FILM_FLM VALUES
(15, 'Sueurs froides', 1959);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10032, 'Kim', 'Novak');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10013), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 15), 'John Ferguson'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10032), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 15), 'Madeleine Elster / Judy Barton');
 
-- 16e film
 
INSERT INTO T_FILM_FLM VALUES
(16, 'La Mort aux trousses', 1959);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10033, 'Eva Marie', 'Saint'),
(10034, 'James', 'Mason'),
(10035, 'Martin', 'Landau');;
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10007), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 16), 'Roger Thornhill'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10033), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 16), 'Eve Kendall'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10034), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 16), 'Philip Vandamm'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10035), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 16), 'Leonard'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10029), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 16), 'Clara Thornhill'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10014), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 16), 'le professeur');
 
 
-- 17e film
 
INSERT INTO T_FILM_FLM VALUES
(17, 'Psychose', 1960);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10036, 'Antony', 'Perkins'),
(10037, 'Janet', 'Leigh');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10036), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 17), 'Norman Bates'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10037), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 17), 'Marion Crane'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10023), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 17), 'Caroline (la secrétaire)');
 
-- 18e film
 
INSERT INTO T_FILM_FLM VALUES
(18, 'Les oiseaux', 1963);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10038, 'Rod', 'Taylor'),
(10039, 'Tippi', 'Hedren');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10038), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 18), 'Mitch Brenner'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10039), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 18), 'Melanie Daniels');
 
 
-- 19e film
 
INSERT INTO T_FILM_FLM VALUES
(19, 'Pas de printemps pour Marnie', 1964);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10040, 'Sean', 'Connery');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10038), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 19), 'Mark Rutland'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10039), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 19), 'Marnie Edgar / Margaret Edgar / Peggy Nicholson / Mary Taylor / Marion Holland');
 
-- 20e film
 
INSERT INTO T_FILM_FLM VALUES
(20, 'Le rideau déchiré', 1966);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10041, 'Paul', 'Newman'),
(10042, 'Julie', 'Andrews');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10041), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 20), 'professeur Michael Armstrong'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10042), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 20), 'docteur Sarah Louise Sherman');
 
-- 21e film
INSERT INTO T_FILM_FLM VALUES
(21, 'L''étau', 1969);
 
INSERT INTO T_ACTEUR_ACT VALUES
(10043, 'Frédérick', 'Stafford'),
(10044, 'Dany', 'Robin'),
(10045, 'Claude', 'Jade'),
(10046, 'Michel', 'Piccoli'),
(10047, 'Philippe', 'Noiret');
 
INSERT INTO T_JOUE_JOU VALUES
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10043), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 21), 'André Devereaux'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10044), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 21), 'Nicole Devereaux'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10045), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 21), 'Michèle Picard'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10046), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 21), 'Jacques Granville'),
((SELECT $node_id FROM T_ACTEUR_ACT WHERE ACT_ID = 10047), (SELECT $node_id FROM T_FILM_FLM WHERE FLM_ID = 21), 'Henri Jarré');

3 - quelques requêtes bateau

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
-- requêtes pour voir...
 
SELECT * FROM T_FILM_FLM;
 
SELECT * FROM T_ACTEUR_ACT;
 
SELECT graph_id_FC0B330ADB564C0CB71E67EA94506283, $node_id, * FROM T_ACTEUR_ACT
 
SELECT $node_id, * FROM T_ACTEUR_ACT
 
SELECT * FROM T_JOUE_JOU;
 
SELECT $edge_id, * FROM T_JOUE_JOU;
 
SELECT $edge_id, $from_id, $to_id, * FROM T_JOUE_JOU;
 
-- metadonnées
 
-- niveau table
SELECT name, type_desc, is_node, is_edge
FROM   sys.tables
WHERE  object_id IN ((SELECT OBJECT_ID('T_FILM_FLM')), 
                     (SELECT OBJECT_ID('T_ACTEUR_ACT')), 
                     (SELECT OBJECT_ID('T_JOUE_JOU')));
 
-- niveau colonnes
SELECT OBJECT_NAME(object_id) AS TABLE_NAME, column_id, name, is_hidden, graph_type_desc
FROM   sys.columns 
WHERE  object_id IN ((SELECT OBJECT_ID('T_FILM_FLM')), 
                     (SELECT OBJECT_ID('T_ACTEUR_ACT')), 
                     (SELECT OBJECT_ID('T_JOUE_JOU')));
 
-- niveau contraintes d'arrête (edge)
SELECT EC.name AS CONSTRAINT_NAME, 
       OBJECT_NAME(EC.parent_object_id) AS edge_table_name,
       OBJECT_NAME(ECC.from_object_id) AS from_node_table_name,
       OBJECT_NAME(ECC.to_object_id) AS to_node_table_name,
       is_disabled,
       is_not_trusted
FROM   sys.edge_constraints EC
       INNER JOIN sys.edge_constraint_clauses ECC
          ON EC.object_id = ECC.object_id;
 
--> fonctions spécifiques 
 
-- pour les tables de noeud (node)
SELECT OBJECT_ID_FROM_NODE_ID((SELECT TOP 1 $node_id FROM T_ACTEUR_ACT));
SELECT GRAPH_ID_FROM_NODE_ID((SELECT $node_id FROM T_ACTEUR_ACT ORDER BY 1 OFFSET 3 ROWS FETCH NEXT 1 ROW ONLY));
SELECT NODE_ID_FROM_PARTS(OBJECT_ID('T_ACTEUR_ACT'), 3);
 
-- pour les tables d'arêtes (edge)
SELECT OBJECT_ID_FROM_EDGE_ID((SELECT TOP 1 $edge_id FROM T_JOUE_JOU));
SELECT GRAPH_ID_FROM_EDGE_ID((SELECT $edge_id FROM T_JOUE_JOU ORDER BY 1 OFFSET 3 ROWS FETCH NEXT 1 ROW ONLY))
SELECT EDGE_ID_FROM_PARTS(OBJECT_ID('T_JOUE_JOU'), 3)

4 - c'est parti pour du parcours de graphe

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
--> Requête classiques..
 
-- dans quels films joue Cary Grant ?
SELECT FLM_TITRE, FLM_ANNEE, acteur.ACT_PRENOM, acteur.ACT_NOM, JOU_INTERPRETE
FROM   T_FILM_FLM as film, T_JOUE_JOU as joue, T_ACTEUR_ACT as acteur
WHERE  MATCH(film <- (joue) - acteur)
  AND  acteur.ACT_NOM = 'Grant' 
  AND acteur.ACT_PRENOM = 'Cary'
ORDER BY 2, 1, 3, 4 
 
-- qui joue dans quel film ?
SELECT FLM_TITRE, FLM_ANNEE, ACT_PRENOM, ACT_NOM, JOU_INTERPRETE
FROM   T_FILM_FLM as film, T_JOUE_JOU as joue, T_ACTEUR_ACT as acteur
WHERE  MATCH(film <- (joue) - acteur)
ORDER BY 2, 1, 3, 4; 
 
-- qui joue dans le même film que Cary Grant ?
SELECT FLM_TITRE, FLM_ANNEE,
       acteur.ACT_PRENOM, acteur.ACT_NOM, joue.JOU_INTERPRETE AS INTERPRETE, 
       acteur2.ACT_PRENOM, acteur2.ACT_NOM, joue2.JOU_INTERPRETE AS INTERPRETE
FROM   T_FILM_FLM as film, T_JOUE_JOU as joue, T_ACTEUR_ACT as acteur, 
       T_ACTEUR_ACT as acteur2, T_JOUE_JOU as joue2
WHERE  MATCH(film <- (joue) - acteur AND film <- (joue2) - acteur2)
  AND  acteur.ACT_NOM = 'Grant' AND acteur.ACT_PRENOM = 'Cary'
  AND  acteur.ACT_ID <> acteur2.ACT_ID
ORDER  BY 2, 1, 3, 4; 
 
-- même qui ci avant, écriture différente
SELECT FLM_TITRE, FLM_ANNEE,
       acteur.ACT_PRENOM, acteur.ACT_NOM, joue.JOU_INTERPRETE AS INTERPRETE, 
       acteur2.ACT_PRENOM, acteur2.ACT_NOM, joue2.JOU_INTERPRETE AS INTERPRETE
FROM   T_FILM_FLM as film, T_JOUE_JOU as joue, T_ACTEUR_ACT as acteur, 
       T_ACTEUR_ACT as acteur2, T_JOUE_JOU as joue2
WHERE  MATCH(acteur - (joue) -> film <- (joue2) - acteur2)
  AND  acteur.ACT_NOM = 'Grant' AND acteur.ACT_PRENOM = 'Cary'
  AND  acteur.ACT_ID <> acteur2.ACT_ID
ORDER  BY 2, 1, 3, 4; 
 
-- quel acteur a le plus joué et dans quels films
WITH
T AS
(
SELECT acteur.ACT_PRENOM, acteur.ACT_NOM, film.FLM_TITRE, 
       COUNT(*) OVER(PARTITION BY ACT_ID)  AS N
FROM   T_FILM_FLM as film, T_JOUE_JOU as joue, T_ACTEUR_ACT as acteur
WHERE  MATCH(film <- (joue) - acteur)
)
SELECT *
FROM   T
WHERE  N = (SELECT MAX(N) FROM T)
ORDER BY 1, 2, 3;

5 - la recherche du plus court chemin...

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
-- à quelle distance sociale est Cary Grant de Grégory Peck (à travers leurs rôles dans les films d'Hitchcock) 
-- 1re étape, insertion des arêtes inverses dans la table "joue":
 
ALTER TABLE T_JOUE_JOU 
   DROP CONSTRAINT EK_JOU_FLM_ACT;
 
INSERT INTO T_JOUE_JOU
SELECT $to_id, $from_id, JOU_INTERPRETE 
FROM T_JOUE_JOU;
 
-- SET STATISTICS IO ON;
 
WITH
T_SEARCH_PATH AS
(
SELECT acteur0.ACT_PRENOM + ' '+ acteur0.ACT_NOM AS ACTEUR, 
       STRING_AGG(CONCAT(acteurN.ACT_PRENOM, ' ', acteurN.ACT_NOM, ' : ', film.FLM_TITRE), ' -> ') 
          WITHIN GROUP (GRAPH PATH) AS chemin,
       LAST_VALUE(CONCAT(acteurN.ACT_PRENOM, ' ', acteurN.ACT_NOM)) 
          WITHIN GROUP (GRAPH PATH) AS LastNode
FROM   T_ACTEUR_ACT AS acteur0,
       T_JOUE_JOU FOR PATH AS joue,
       T_FILM_FLM FOR PATH as film,
       T_JOUE_JOU FOR PATH AS joue2,
       T_ACTEUR_ACT FOR PATH AS acteurN
WHERE  MATCH(SHORTEST_PATH( acteur0 ( - (joue) -> film - (joue2) -> acteurN ) + ) )
  AND  acteur0.ACT_PRENOM = 'Cary' AND acteur0.ACT_NOM = 'Grant'
)
SELECT ACTEUR, chemin, LastNode AS LIEN
FROM   T_SEARCH_PATH
WHERE  LastNode = 'Gregory Peck';
 
-- vérification 
SELECT FLM_TITRE, FLM_ANNEE, ACT_PRENOM, ACT_NOM, JOU_INTERPRETE
FROM   T_FILM_FLM as film, T_JOUE_JOU as joue, T_ACTEUR_ACT as acteur
WHERE  MATCH(film <- (joue) - acteur)
  AND  ACT_NOM IN ('Grant', 'Peck', 'Bergman')
ORDER BY 2, 1, 3, 4; 
 
-- autre écriture sans CTE :
SELECT ACTEUR, chemin, G.LastNode AS LIEN
FROM
(
SELECT acteur0.ACT_PRENOM + ' ' + acteur0.ACT_NOM AS ACTEUR, 
       STRING_AGG(CONCAT(acteurN.ACT_PRENOM, ' ', acteurN.ACT_NOM, ' : ', film.FLM_TITRE), ' -> ') 
          WITHIN GROUP (GRAPH PATH) AS chemin,
       LAST_VALUE(CONCAT(acteurN.ACT_PRENOM, ' ', acteurN.ACT_NOM)) 
          WITHIN GROUP (GRAPH PATH) AS LastNode
FROM   T_ACTEUR_ACT AS acteur0,
       T_JOUE_JOU FOR PATH AS joue,
       T_FILM_FLM FOR PATH as film,
       T_JOUE_JOU FOR PATH AS joue2,
       T_ACTEUR_ACT FOR PATH AS acteurN
WHERE  MATCH(SHORTEST_PATH( acteur0 ( - (joue) -> film - (joue2) -> acteurN ) + ) )
  AND  acteur0.ACT_PRENOM = 'Cary' AND acteur0.ACT_NOM = 'Grant'
) AS G
WHERE G.LastNode = 'Gregory Peck';

CQFD

[Waldar]

Intéressant, mais j'ai l'impression qu'il manque le cœur du sujet à savoir l'objet PROPERTY GRAPH avec les tables VERTEX & EDGE (je ne m'y connais pas du tout, je ne fais que reprendre l'exemple original).

Les requêtes mises en exemple sont limites plus simple à écrire en SQL classique.

Quid des performances, y a-t-il une différence ?

[SQLpro]

Property graph c'est pas nécessaire. C'est juste l'activation de la fonctionnalité de graphe dans la base de données Par défaut SQL Server fait tout. Pas de module à rajouter comme dans PostGreSQL ni de choses à activer comme dans Oracle.

Vertex et edge, ce sont les types de tables de graphe. La norme les confonds dans un même objet, le grapphe. SQL Server les dissocie entre table de noeud et table d'arrête.

A +

[bullit75]

Je ne reviens pas sur les avancées graphiques et sur le json, ni les GREATEST ET LEAST, je dis bravo pour toutes ces avancées . Par contre, il serait bon qu'ils mettent en standard d'autres concepts qui ont une utilité :

• XOR
• BOOLEAN et ENUM
• SYMMETRIC EXCEPT
• SYMMETRIC BETWEEN
• LIKE ANY () / LIKE ALL ()
• Clauses QUALIFY des fonctions analytiques, WINDOW appelant une autre WINDOW
• Concaténation de chaînes avec “+”
• Manipulation de Tuples, dont comparaison (a, b, c) < (d, e, f) <=> (a < d OR (a = d AND b < e) OR (a = d AND b = e AND c < f))
• INTERVAL et développements (OVERLAPS)
• IS DISTINCT FROM
• WHERE UNIQUE / DISTINCT
• SPLIT(String)
• CROSS/OUTER APPLY ou LATERAL JOIN
• SEMI JOIN, ANTI JOIN
• Clause FILTER
• UPDATE avec JOIN
• UPDATE avec CTE
• Row Constructor
• COLUMN DEFAULT résultat d’un calcul ou d'une fonction
• VIRTUAL COLUMN, MATERIALIZED VIEW
• ALTER TABLE ADD column AT POSITION n
• ORDER BY ... NULLS LAST/FIRST
• SIMILAR TO
• TABLE_A DIVIDE BY TABLE_B ON [ some predicate ] (cf. https://blog.jooq.org/advanced-sql-r...ision-in-jooq/)

Quand je vois les veaux que je dois coder et la simplification qu'induirait certaines de ces commandes

[SQLpro]

Le problème est que certaines choses que vous affirmez ne font pas partie de la norme SQL et d'autre sont totalement fantaisistes...

Alors décortiquons...

XOR n'existe pas dans la norme SQL mais est implémenté sous la forme d'opérateur dans MS SQL Server : ^ ou encore ^=
https://learn.microsoft.com/en-us/sq...l-server-ver16
https://learn.microsoft.com/en-us/sq...l-server-ver16

BOOLEAN existe bien dans la norme depuis la version 1999. Avant c'était BIT ! Comme SQL Server est né en 1983 le bit a été conservé comme l'équivalent du booléen. De plus la norme a conservé, même pour les fonctions les plus récentes le type BIT au détriment du type BOOLEEN...

ENUM n'existe pas dans la norme SQL. En revanche existe les multisets... Entre nous une grosse merde comme tous les types non atomiques genre, ARRAY, ROW... Car il ne sont pas indexables. Mais rein n'empêche de les créer dans du SQL CLR... Tu trouvera des bibliothèques en accès libre pour ce genre d'horreur antirelationnelles !

SYMMETRIC EXCEPT : n 'existe pas dans la norme SQL, facile à faire en requêtes avec les CTE

SYMMETRIC BETWEEN : n 'existe pas dans la norme SQL, juste un NOT me semble t-il

LIKE ANY () / LIKE ALL () : n'existe pas, aucune utilité ANY et ALL sont en voie de disparition dans la norme SQL

Clauses QUALIFY des fonctions analytiques, WINDOW appelant une autre WINDOW : toujours pas normalisé mais devrait l'être. A été proposé par Microsoft au comité de normalisation

Concaténation de chaînes avec “+” : existe dans SQL Server depuis l'origine. pas le même comportement que CONCAT...

Manipulation de Tuples, dont comparaison (a, b, c) < (d, e, f) <=> (a < d OR (a = d AND b < e) OR (a = d AND b = e AND c < f)) : là je suis d'accord, mais peu de gens comprennent cette transformation ! (s’appelle le Row Value Comparator)

INTERVAL et développements (OVERLAPS) : le type INTERVAL n'existe pas dans la norme SQL. Il est inindexable en pratique, raison pour laquelle la norme à mis eux colonne TIMESTAMP pour délimiter les intervalles des tables temporaires. En revanche OVERLAPS existe bien et n'est effectivement pas implémenté dans SQL Server... Dommage mais une simple UDF fait l'affaire

IS DISTINCT FROM : existe dans la norme et est implémenté dans SQL Server
https://learn.microsoft.com/en-us/sq...l-server-ver16

WHERE UNIQUE / DISTINCT : existe dans la norme. En pratique peu d'intérêt car réalisable dans le EXISTS. Très difficile à optimiser

SPLIT(String) : existe et est normalisé sous la forme de STRING_SPLIT (avec son homologue inverse STRING_AGG).Tous deux figurant dans SQL Server

CROSS/OUTER APPLY ou LATERAL JOIN : LATERAL a été une connerie de la norme SQL:1999. Microsoft devant cette bétise à introduit APPLY avec ses deux versions et c'est en cours de normalisation

SEMI JOIN, ANTI JOIN : aucun intérêt et n'existe pas dans la norme. La semi anti jointure pouvant être partielle

Clause FILTER : je ne sais même pas ce que c'est... Désolé !

UPDATE avec JOIN : n'existe pas dans la norme SQL. Inventé par SQL Server Sybase et repris dans MS SQL Server. Gros effet de bord. peut conduire à des résultats faux... !

UPDATE avec CTE : existe depuis l'origine dans la norme SQL. PostGreSQL a mis 15 ans à l'implémenté là ou tous les autres l'avait fait immédaitement

Row Constructor : existe dans la norme. Partiellement effectif dans SQL Server, partout sauf dans la comparaison (Row Value Comparator).

COLUMN DEFAULT résultat d’un calcul ou d'une fonction. N'existe pas dans la norme. Existe dans SQL Server sous la forme de colonne calculée

VIRTUAL COLUMN : n'existe pas dans la norme... Colonne calculée dans SQL Server

MATERIALIZED VIEW : n'existe pas dans la norme. Existe dans SQL Sever sous la forme de vue indexées (=> MATERIALIZED VIEW toujours synchrone : rafraichissement automatique et synchrone)

ALTER TABLE ADD column AT POSITION n : stupide. N'existe pas dans la norme SQL et heureusement. Viole les principes fondamentaux du relationnel

ORDER BY ... NULLS LAST/FIRST : existe bien dans la norme SQL; SQL Server ne l'implémente pas, et je le regrette... votez pour :
https://feedback.azure.com/d365commu...c-0022485030d1
https://feedback.azure.com/d365commu...6-000d3a4f0da0

SIMILAR TO : existe dans la norme SQL. Sujet aux attaques DOS. Remplacé dans certains SGBDR par regex (y compris dans SQL Server) certains SGBDR (SQL Server en particulier) ont un LIKE étendu proche du SIMILAR

TABLE_A DIVIDE BY TABLE_B ON [ some predicate ] (cf. https://blog.jooq.org/advanced-sql-r...ision-in-jooq/)
Totalement foutraque et parfaitement idiot car il existe de multiples façons de faire la division relationnelle. Tenté il y a fort longtemps du temps de CODD et Chris DATE et abandonné en raison des impasse de codage dans le SQL. En effet les différentes façons de faire sont :

• la division exacte
• la division sans reste
• la division avec reste
• la division valuée

et tous les combinaisons de ces différentes division auquel l’article mentionné ne répond en rien alors que les solutions à coup de sous requête y répondent aisément et selon la forme de la requête de manière optimisé pour certains cas de figure...

A +

[tatayo]

Bonjour,

UPDATE avec JOIN : n'existe pas dans la norme SQL. Inventé par SQL Server Sybase et repris dans MS SQL Server. Gros effet de bord. peut conduire à des résultats faux... !

C'est à dire ? Quels sont les effets de bord ? Dans quels cas peut-on avoir un résultat faux ?
J'utilise un update avec jointure dans un trigger INSTEAD OF sur une vue, donc le sujet m'intéresse.
Le but est de lancer un update sur une vue en spécifiant des codes métier, et le trigger s'arrange pour faire la "traduction" de ces codes en identifiants*.

Est-ce que je dois passer par un MERGE à la place ?

Tatayo.

*: j'ai bataillé ferme pour "imposer" des identifiants (IDENTITY) dans mes tables, donc je dois faire en sorte que ça ne gêne pas mes utilisateurs lors d'opérations de maintenance...

[Séb.]

Clause FILTER : je ne sais même pas ce que c'est... Désolé !

Ça doit être la clause de filtrage des fonctions d'agrégat, seuls PostgreSQL et SQLite la supporte.

Au lieu de :

Code :

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

1
2
SELECT COUNT(CASE WHEN result = 'OK' THEN TRUE END)
...

On fait :

Code :

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

1
2
SELECT COUNT(*) FILTER (WHERE result = 'OK')
...

https://www.postgresql.org/docs/curr...orial-agg.html

[SQLpro]

Bonjour,

C'est à dire ? Quels sont les effets de bord ? Dans quels cas peut-on avoir un résultat faux ?
J'utilise un update avec jointure dans un trigger INSTEAD OF sur une vue, donc le sujet m'intéresse.
...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
USE tempdb;
GO
 
TRUNCATE TABLE T_MULTI
 
TRUNCATE TABLE T_MONO
 
CREATE TABLE T_MULTI (ID_MULTI INT PRIMARY KEY, VAL INT, INFO CHAR(1))
INSERT INTO T_MULTI VALUES (1, 99, 'A'), (2, 99, 'B'), (3, 99, 'C'), (4, 100, 'Z');
 
CREATE TABLE T_MONO (ID_MONO INT PRIMARY KEY, VAL INT, INFO2 CHAR(1));
INSERT INTO T_MONO VALUES (-1, 99, 'Z'), (-2, 98, 'Y'), (-3, 97, 'X'), (-4, 96, 'M');

Code :

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

1
2
3
4
5
UPDATE T
SET    INFO2 = N.INFO
FROM   T_MONO AS T
       JOIN T_MULTI AS N
	      ON T.VAL = N.VAL;

Code :

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

1
2
3
4
5
6
7
8
9
SELECT *
FROM   T_MONO;
 
ID_MONO     VAL         INFO2
----------- ----------- -----
-4          96          M
-3          97          X
-2          98          Y
-1          99          A     --> changement

Code :

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

1
2
3
4
5
6
7
CREATE INDEX X ON T_MULTI (VAL, INFO DESC);
 
UPDATE T
SET    INFO2 = N.INFO
FROM   T_MONO AS T
       JOIN T_MULTI AS N
	      ON T.VAL = N.VAL;

Code :

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

1
2
3
4
5
6
ID_MONO     VAL         INFO2
----------- ----------- -----
-4          96          M
-3          97          X
-2          98          Y
-1          99          C  --> changement !!!

Autrement dit, si la cardinalité de la jointure n'est pas strictement du 1 pour 1 dans la jointure, c'est une valeur quelconque qui est assigné au SET...

A +

[SQLpro]

Ça doit être la clause de filtrage des fonctions d'agrégat, seuls PostgreSQL et SQLite la supporte.

Au lieu de :

Code :

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

1
2
SELECT COUNT(CASE WHEN result = 'OK' THEN TRUE END)
...

On fait :

Code :

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

1
2
SELECT COUNT(*) FILTER (WHERE result = 'OK')
...

https://www.postgresql.org/docs/curr...orial-agg.html

Donc de la cosmétique inutile.... la ou le CASE est optimisable (évidemment je ne parle pas d'optimiseur pour SQL lite qui n'en a pas et pour PG dont l'optimiseur ne s'appelle même pas comme ça, mais "planeur" (sans doute par ce qu'il fume...) !

A +

[bullit75]

Le problème est que certaines choses que vous affirmez ne font pas partie de la norme SQL et d'autre sont totalement fantaisistes...

Alors décortiquons... [...]

Bien sûr que ça ne fait pas partie des normes SQL, mais plusieurs ont été implémentées par tel ou tel SGBD :

• XOR l'a été par MySQL
• BOOLEAN semble quand même plus utile qu'un 0 ou 1 mis par bon nombre de développeurs
• LIKE ANY ou LIKE ALL : son utilisation par VERTICA en fait un gros plus pour ce SGBD ; d'une manière générale, les ANY et ALL avec les opérateurs <, >, <= et >=, je demande à voir ce qui va remplacer...
• ENUM : on peut l'implémenter par un CHECK de la liste de valeurs, mais le proposer de manière plus directe ne me semble pas absurde
• SYMMETRIC EXCEPT : je veux bien un exemple, car vu que c'est une opération de base dans les ensembles, voir du code simple implémenter le concept, ça m'intéresse...
• SYMMETRIC BETWEEN : lorsqu'on sait qu'on a des bornes sans savoir laquelle est la min ou la max, proposer qu'on s'en affranchisse ne semble pas idiot
• WHERE UNIQUE : différent du WHERE EXISTS car demandant aussi de pointer l'unicité. On peut le faire avec une fonction analytique row_number() (sur Oracle), mais c'est lourd. Il faut optimiser, mais la requête actuelle ramenant le même résultat sera bien lourde aussi...
• Split() manque
• DISTINCT : on gère les NULL correctement
• UPDATE avec CTE : je veux bien voir un exemple d'implémentation sous Oracle, censée pouvoir se faire, jamais réussi...
• Clause FILTER, on peut le faire avec un CASE dans la fonction, mais c'est un raccourci bien commode
• Concernant la division d'ensemble, c'est aussi un élément important de l'algèbre des ensembles, et les cas d'utilisation sont assez nombreux pour que reprendre une étude à ce sujet puisse être envisagé. Du moins communiquer clairement sur sa faisabilité ou non. S'il faut faire un DESIGN PATTERN pour l'implémenter, je prends la solution.

Je répète, certaines sont implémentées par tel ou tel SGBD, mais pas tous et pas repris par le comité de standardisation. Tu en as déploré certains de ma liste, preuve en est que tout n'est pas à jeter. On peut débattre de la pertinence de tel ou tel.

[SQLpro]

Bien sûr que ça ne fait pas partie des normes SQL mais plusieurs ont été implémentées par tel ou tel SGBD :

LIKE ANY ou LIKE ALL : son utilisation par VERTICA en fait un gros plus pour ce SGBD ; d'une manière générale, les ANY et ALL avec les opérateurs <, >, <= et >=, je demande à voir ce qui va remplacer...

ANY et ALL existe dans la plupart des SGBDR mais comme ils sont en voie d'abandon, ils sont moins bien optimisé que n'importe quelle autre solution algébriquement équivalente

ENUM : on peut l'implémenter par un CHECK de la liste de valeurs, mais le proposer de manière plus directe ne me semble pas absurde

Là encore question d'optimisation. Une contrainte permet d'optimiser certaines requêtes. Un ENUM jamais et il n'est pas indexable

SYMMETRIC EXCEPT : je veux bien un exemple, car vu que c'est une opération de base dans les ensembles, voir du code simple implémenter le concept, ça m'intéresse...

A - B U B - A c'est aussi simple que cela. En SQL

Code :

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

SELECT * FROM TA EXCEPT SELECT * FROM TB UNION SELECT * FROM TB EXCEPT SELECT * FROM TA

WHERE UNIQUE : différent du WHERE EXISTS car demandant aussi de pointer l'unicité. On peut le faire avec une fonction analytique row_number() (sur Oracle), mais c'est lourd. Il faut optimiser, mais la
requête actuelle ramenant le même résultat sera bien lourde aussi...

équivalent de

Code :

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

WHERE EXISTS(SELECT * FROM ... GROUP BY ... HAVING COUNT(*) = 1

Clause FILTER, on peut le faire avec un CASE dans la fonction, mais c'est un raccourci bien commode

Là encore, le CASE est optimisable, pas le FILTER !

Concernant la division d'ensemble, c'est aussi un élément important de l'algèbre des ensembles, et les cas d'utilisation sont assez nombreux pour que reprendre une étude à ce sujet puisse être envisagé. Du moins communiquer clairement sur sa faisabilité ou non. S'il faut faire un DESIGN PATTERN pour l'implémenter, je prends la solution.

Il existe différentes formulations par sous requêtes dont les performances diffèrent en fonction des cardinalités en jeux des sources et du résultat...
https://sqlpro.developpez.com/cours/divrelationnelle/

A +

[bullit75]

ANY et ALL existe dans la plupart des SGBDR mais comme ils sont en voie d'abandon, ils sont moins bien optimisé que n'importe quelle autre solution algébriquement équivalente.

Argument pas forcément très pertinent, quand on sait que IN équivaut a = ANY, NOT IN à NOT = ALL. Après entre un WHERE x > ALL (SELECT x FROM ...) et un X > (SELECT MAX(x) FROM ...), parfaitement équivalents, je reste dans l'idée que le premier est plus rapide que le second.

Là encore question d'optimisation. Une contrainte permet d'optimiser certaines requêtes. Un ENUM jamais et il n'est pas indexable.

Pour le moment, mais je ne suis pas convaincu que ce soit infaisable ou insurmontable. Je ne suis pas éditeur de SGBD, donc je ne m'avancerai pas sur la faisabilité ou non, mais de la même manière que déclarer une PRIMARY KEY au même moment que déclarer la table en précisant un index, déclarer un ENUM comme raccourci d'un CHECK sur une colonne semble plus cosmétique que bouleversant la conception dudit SGBD quelque chose du style

Code :

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

macolonne VARCHAR2(1) NOT NULL ENUM ('A' as a, 'B' as b, 'C' as c)

, suivi d'un WHERE macolonne.a (équivalent à un WHERE macolonne = 'A').

A - B U B - A c'est aussi simple que cela. En SQL

Code :

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

SELECT * FROM TA EXCEPT SELECT * FROM TB UNION SELECT * FROM TB EXCEPT SELECT * FROM TA

C'est ma solution actuellement utilisée : Autrement dit on répète 2 fois les mêmes requêtes et 3 opérateurs pour faire une opération fondamentale d'algèbre des ensembles. On peut faire des CTE, en effet, mais côté lisibilité et lourdeur de traitement, on repassera.

Code :

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

WHERE UNIQUE (SELECT * FROM ...)

équivalent de

Code :

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

WHERE EXISTS(SELECT * FROM ... GROUP BY ... HAVING COUNT(*) = 1

Encore une fois, remplacer une clause

Code :

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

GROUP BY ... HAVING COUNT(*) = 1

qui peut facilement être à rallonge ; remplacé par un simple mot ressemble à une opération de cosmétique, donc optimisable

Là encore, le CASE est optimisable, pas le FILTER !

Cosmétique encore, mais on a bien une clause KEEP associée à MIN et MAX dans Oracle, on pourrait aussi intégrer son prédicat dans le CASE. Or cette clause KEEP allège la lecture...

Je me retrouve assez régulièrement à coder des requêtes impliquant une quinzaine de tables, je ne peux qu'être très attentif à toute optimisation, qu'elle soit de temps de traitement mais aussi de volume de code. Donc si des solutions de simplification de code parmi celles évoquées ci-dessus existaient, ça rendrait déjà le code plus lisible. Des solutions et ajouts au SQL standard ont permis bon nombre de simplification d'écriture, qui rapidement devient aussi importante qu'une optimisation de temps de traitement. C'est sûr que s'il s'agit de rajouter du cosmétique sur des requêtes qui ne dépassent pas les 100 caractères, ça ne sert à rien. Mais si on garde la logique que le cosmétique n'est pas nécessaire, les GREATEST et LEAST pourraient être remplacés par une CTE et un MIN ou un MAX. Donc ici encore, c'est cosmétique...

[Waldar]

[*]LIKE ANY ou LIKE ALL : son utilisation par VERTICA en fait un gros plus pour ce SGBD ; d'une manière générale, les ANY et ALL avec les opérateurs <, >, <= et >=, je demande à voir ce qui va remplacer...

J'ai personnellement poussé la R&D pour avoir cette fonctionnalité, mais rendons à César ce qui lui revient de droit, j'ai découvert cette syntaxe chez Teradata.
[NOT] LIKE ANY / ALL permettent de remplacer une succession de LIKE :

Code :

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

WHERE MaColonne LIKE ANY ('%ABC%', '%123%', '%toto')

<=>

Code :

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

1
2
3
WHERE MaColonne LIKE '%ABC%'
   OR MaColonne LIKE '%123%'
   OR MaColonne LIKE '%toto'

Et LIKE ALL revient à la même chose avec un AND au lieu d'un OR.

Je répète, certaines sont implémentées par tel ou tel SGBD, mais pas tous et pas repris par le comité de standardisation.
Tu en as déploré certains de ma liste, preuve en est que tout n'est pas à jeter. On peut débattre de la pertinence de tel ou tel.

En phase !