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 2023-06-02 14:11:28, par Bill Fassinou, Chroniqueur Actualité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 :

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

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

Code SQL :

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

Possibilité d'effectuer des calculs plus complexes :

Code SQL :

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 :

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

Discussion forum

15 commentaires

SQLpro

Rédacteur

Envoyé par Séb.

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 :

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 :

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 :

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 :

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 :

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

le 06/06/2023 à 0:07

escartefigue

Modérateur

Envoyé par Bill Fassinou

Modifications apportées au langage SQL existant

Code SQL :

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

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

Code SQL :

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

Possibilité d'effectuer des calculs plus complexes :

Code SQL :

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

Envoyé par Bill Fassinou

Code SQL :

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.

le 02/06/2023 à 16:13

Waldar
Modérateur

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 ?

le 06/06/2023 à 11:20
Waldar
Modérateur
Envoyé par bullit75

[*]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 :
```
WHERE MaColonne LIKE ANY ('%ABC%', '%123%', '%toto')
```
<=>
Code :
```
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.

Envoyé par bullit75

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 !
le 06/07/2023 à 13:27
Séb.
Expert éminent

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

le 02/06/2023 à 17:47
SQLpro
Rédacteur

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 +

le 07/06/2023 à 18:51
bullit75
Futur Membre du Club
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-relational-division-in-jooq/)
Quand je vois les veaux que je dois coder et la simplification qu'induirait certaines de ces commandes
le 30/06/2023 à 11:27

SQLpro

Rédacteur

Envoyé par tatayo

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 :

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 :

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 :

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 :

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 :

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 +

le 04/07/2023 à 17:50

SQLpro
Rédacteur
Envoyé par Séb.

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

Au lieu de :

Code :

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

On fait :

Code :

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 +
le 04/07/2023 à 17:52
SQLpro
Rédacteur
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-relational-division-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 +
le 04/07/2023 à 16:16

Poster une réponse