AlaSQL, un puissant moteur SQL pour développeurs JavaScript

Magazine
Marque
GNU/Linux Magazine
Numéro
250
Mois de parution
juillet 2021
Spécialité(s)


Résumé

Le projet AlaSQL permet aux développeurs JavaScript de bénéficier du meilleur de la norme SQL. Grâce à lui, développer ou redévelopper des règles métiers complexes en JavaScript est une vraie partie de plaisir.


Body

Il y a quelques mois, je travaillais sur un projet nécessitant la création d’un affichage de type planning. Pour alimenter ce planning (généré via D3.js), je m’appuyais sur un jeu de données JSON qui était transmis par une API. Ce jeu de données était « brut de fonderie », le format des données peu adapté, et l’API n’était pas modifiable. Je devais donc composer avec les données fournies par cette API, et essayer de transformer ces données en quelque chose d’exploitable. J’ai alors décidé de donner sa chance au projet AlaSQL, que j’avais découvert quelques mois auparavant. Et la partie du projet qui aurait dû être la plus enquiquinante (je pense bien sûr à la transformation des données) s’est subitement transformée en une vraie partie de plaisir. Je vais essayer de vous expliquer pourquoi ici.

Dans une première partie, je vais brièvement vous expliquer les bases d’AlaSQL. Dans la seconde partie, je vous montrerai qu'AlaSQL est parfaitement capable d’exécuter des requêtes SQL d’un niveau avancé. Pour cette seconde partie, je m’appuierai sur des exemples que j’avais utilisés dans un article que j’avais publié en mars 2018 dans GNU/Linux Magazine [1]. Dans cet article de 2018, j’expliquais comment manipuler des données soumises à date d’effet (comme par exemple, des tarifs) grâce au langage SQL. Il s’agissait de techniques SQL nécessitant l’utilisation de jointures et de sous-requêtes. Je vais vous montrer qu'AlaSQL est parfaitement capable de gérer cette complexité. Je vous parlerai aussi en fin d’article des CTE (Common Table Expression), autre technique SQL avancée que j’avais présentée dans un article publié en janvier 2018 [2]. Car AlaSQL sait gérer certains types de CTE, et il serait dommage de s’en priver.

1. Les bases d'AlaSQL

1.1 Installation du projet

Vous allez voir, l’installation d'AlaSQL est très simple.

Vous trouverez beaucoup d’informations dans la documentation officielle (qui est bien faite) [3], alors je vais vous présenter un condensé des techniques que j’ai utilisées (il y a quelques variantes, décrites dans la doc, je vous laisse les découvrir par vous-même).

Vous pouvez donc utiliser AlaSQL dans votre navigateur en le chargeant localement ou via un CDN :

<script src="https://cdn.jsdelivr.net/npm/alasql@1.7"></script>

Toujours dans votre navigateur, vous pouvez l’utiliser à l’intérieur d’un Worker, via la fonction importScripts, comme ceci :

importScripts('alasql.min.js');

Côté Node.js, vous pouvez installer AlaSQL sous forme de package, via l’une des techniques suivantes :

npm install alasql     # chargement du projet en local
npm install -g alasql # chargement global pour utilisation en ligne de commande

Vous pouvez aussi l’installer via yarn, mais c’est expliqué dans la doc, alors je ne m’attarde pas.

Et une fois que vous avez installé AlaSQL, vous pouvez l’utiliser dans vos scripts Node.js via un require, comme ceci :

let alasql = require('alasql');

1.2 Quelques exemples d’utilisation

Pour cette présentation rapide, on va partir d’un exemple très simple.

Supposons que vous ayez une API REST qui envoie dans votre navigateur, disons… quelques centaines de lignes de produits. Voici un échantillon de ce que pourrait contenir ce jeu de données :

let produits = [
  { id: 80, code_produit: 'BR 500', code_famille: 'BR' },
  { id: 81, code_produit: 'BR 550', code_famille: 'BR' },
  { id: 82, code_produit: 'BR 600', code_famille: 'BR' },
  { id: 83, code_produit: 'BT 121 C', code_famille: 'BT' },
  { id: 31, code_produit: 'BT 45', code_famille: 'BT' },
  { id: 27, code_produit: 'BT 46', code_famille: 'BT' },
  { id: 121, code_produit: 'FR 130 T', code_famille: 'FR' }
]

On a donc un jeu de données contenant un tableau d’objets constitués sur le même modèle. Chaque objet est constitué d’un identifiant, d’un code produit et d’un code famille de produit. C’est l’exemple typique d’un « dataset » (jeu de données) tel que pourrait nous en renvoyer une base de données SQL, par exemple.

Je vous propose de faire un parallèle entre le jargon JavaScript, et celui spécifique au monde SQL. Dans notre exemple de liste de produits ci-dessus :

  • le tableau d’objets JavaScript est l’équivalent d’une table SQL ;
  • chaque objet du tableau JavaScript est une ligne de table au sens SQL du terme ;
  • chaque propriété d’un objet JavaScript équivaut à une colonne de table côté SQL.

Donc si dans la suite de l’article, j’emploie les termes de table, de ligne ou de colonne, vous saurez à quoi je fais référence.

Vous connaissez sûrement le select * en SQL ? Voici un exemple en version AlaSQL :

let query = 'SELECT * FROM ?';
let resultset = alasql(query, [produits]);
console.log(resultset);

Si vous exécutez le code ci-dessus, que ce soit dans un script Node.js ou dans un script exécuté côté navigateur, vous obtiendrez dans la console un jeu de données strictement identique à celui d’origine, ce qui est normal compte tenu de la nature de notre requête SQL.

Dans l’exemple précédent, vous voyez que la fonction alasql attend deux paramètres :

  • le code SQL de la requête à exécuter ;
  • un tableau optionnel contenant la liste des données qui devront remplacer les jokers que vous aurez judicieusement placés dans le code SQL.

Les jokers, ce sont les points d’interrogation. Dans notre exemple, nous avons un point d’interrogation juste après la clause FROM, il s’agit de la table que l’on souhaite interroger et cette table, c’est le tableau des produits que nous avons déclaré quelques paragraphes plus haut. C’est ni plus ni moins le même principe que les requêtes SQL paramétrées que l’on retrouve sur la plupart des SGBD [4].

Mais vous noterez qu’il est très rare qu’un SGBD vous autorise à transmettre une table sous forme de joker. En règle générale, cela n’est pas permis... sauf dans AlaSQL qui est très souple d’utilisation. D’ailleurs, c’est de cette manière que je l’ai utilisé la plupart du temps, en particulier pour filtrer et reformater des jeux de données transmis par des API.

Voyons une requête plus intéressante, dans laquelle on ne sélectionne que les produits de la famille « BT ». On en profite par la même occasion pour renommer les intitulés de certaines colonnes :

let query2 = 'SELECT id AS prod_id, code_produit AS prod_code FROM ? WHERE code_famille = ?';
let resultset2 = alasql(query2, [produits, "BT"]);
console.log(resultset2);

Vous noterez que cette fois, je transmets deux paramètres dans le tableau des paramètres optionnels, le premier étant encore une fois le jeu de données d’origine (le tableau des produits), le second étant le critère de filtrage qui va se substituer au second point d’interrogation que j’ai placé dans la requête SQL, au niveau de la clause WHERE.

On obtient après exécution un nouveau jeu de données, qui est celui-ci :

[
  { prod_id: 83, prod_code: 'BT 121 C' },
  { prod_id: 31, prod_code: 'BT 45' },
  { prod_id: 27, prod_code: 'BT 46' }
]

Quand vous avez une API qui vous transmet des données avec des intitulés de colonnes pas très « user friendly », c’est vraiment génial de pouvoir renommer ainsi, à la volée, les différentes colonnes. Cela permet d’écrire un code beaucoup plus lisible pour manipuler les différentes colonnes de notre jeu de données.

Il faut savoir qu’il est possible d’utiliser des fonctions SQL comme SUBSTRING, TRIM, LOWER, UPPER, etc. Vous trouverez une liste plus complète sur le lien du wiki [3], en cherchant le mot clé « functions ».

Puisque c’est du SQL, on peut bien sûr utiliser des clauses de type GROUP BY ou ORDER BY, comme dans cet exemple, dans lequel on comptabilise le nombre de produits par famille :

let query3 = 'SELECT LOWER(code_famille) AS code_fam, COUNT(*) as comptage FROM ? GROUP BY code_famille';
let resultset3 = alasql(query3, [produits]);
console.log(resultset3);

… ce qui nous donne à l’arrivée :

[
  { code_fam: 'br', comptage: 3 },
  { code_fam: 'bt', comptage: 3 },
  { code_fam: 'fr', comptage: 1 }
]

Là encore, vous pouvez vous reporter au wiki et consulter le lien qui s’intitule « supported SQL statements », vous y trouverez la liste exhaustive des ordres SQL supportés par AlaSQL (il y en a beaucoup). Par exemple, sur ce même lien, vous trouverez un lien secondaire vers l’ordre SQL JOIN que l’on utilisera dans les exemples suivants, et vous pourrez ainsi voir les différents types de jointures supportés par AlaSQL.

Je vous ai montré comment traiter à la volée, via AlaSQL, des données transmises par une API. Il est possible aussi de créer des tables via l’ordre CREATE TABLE, exactement comme on le ferait dans un vrai SGBD SQL. Si on applique cette technique à notre table des produits, voici ce que cela donne :

alasql("CREATE TABLE produits (id INTEGER PRIMARY KEY,
         code_produit STRING, code_famille STRING)");

Pour créer la requête d’alimentation (le INSERT), je déclare une chaîne de type « template literal », avec les apostrophes inverses, car c’est vraiment pratique pour créer des requêtes sur plusieurs lignes :

var insert_datas = `INSERT INTO produits
(id, code_produit, code_famille)
VALUES
(80, 'BR 500', 'BR'),
(81, 'BR 550', 'BR'),
(82, 'BR 600', 'BR'),
(83, 'BT 121 C', 'BT'),
(31, 'BT 45', 'BT'),
(27, 'BT 46', 'BT'),
(121, 'FR 130 T', 'FR');
`;
 
// exécution de l’INSERT
alasql(insert_datas);
// affichage du contenu de la table PRODUITS pour contrôle
console.log(alasql('select * from produits'));

Vous noterez dans la dernière requête, celle qui se trouve dans le console.log, que je n’ai plus besoin de transmettre mon tableau de produits sous forme de joker. Maintenant, la table des produits est réellement référencée par AlaSQL, on doit donc l’invoquer directement dans la clause FROM.

Pour conclure avec cette brève présentation, notez que vous pouvez interfacer AlaSQL avec des librairies annexes comme Numeral.js, Moment.js et Sprintf.js. Vous pouvez aussi l’utiliser avec des projets comme D3.js et avec les principaux frameworks du marché. Tout cela est expliqué dans la documentation officielle. De même, la doc explique qu'AlaSQL peut être utilisé sous forme de Promise, et on peut même l’utiliser pour générer un Worker à la volée (ça, je ne l’ai pas testé, mais ça a l’air cool). On peut aussi charger des données dans AlaSQL à partir d’un fichier XLSX (ça a l’air cool aussi ça, il faudra que je le teste). On peut enfin compiler son code SQL au moyen de la méthode compile (encore un truc qu’il faut que je teste… je ne suis pas rendu…).

OK, on a fait le tour des généralités, il est temps de passer aux choses sérieuses.

Nous allons maintenant étudier des techniques SQL de pointe, que vous ne trouverez pas dans la doc d'AlaSQL (en tout cas, pas sous cette forme), et qui pourraient vous rendre de grands services.

2. Jointures et dates d’effet

2.1 Une table des tarifs

Pour pouvoir faire des jointures avec la table des produits que nous avons vue dans la partie précédente, il nous faut une table des tarifs. Je vous propose de la créer avec la technique du CREATE TABLE :

alasql("CREATE TABLE tarifs (id INTEGER AUTOINCREMENT PRIMARY KEY,
        produit_id INTEGER, date_effet DATE, prix DECIMAL )");
 
var insert_datas = `INSERT INTO tarifs
(produit_id, date_effet, prix)
VALUES
(80, '2021-01-01', 459),
(81, '2021-01-01', 500),
(82, '2021-01-01', 584),
(83, '2021-01-01', 1109),
(31, '2021-01-01', 1325),
(27, '2021-01-01', 1620),
(121, '2021-01-01', 2390);
`;
 
alasql(insert_datas);

Vous noterez dans ce code une petite astuce que je n’avais pas utilisée pour la création de la table des produits, à savoir l’utilisation de la clause AUTOINCREMENT sur la colonne id. Grâce à cette technique, je n’ai pas eu besoin d’alimenter cette colonne dans la liste des valeurs transmises lors du INSERT, c’est AlaSQL qui s’est chargé d’incrémenter automatiquement cette colonne. C’est tant mieux, car il s’agit d’une colonne purement technique, qui n’a pas d’intérêt métier, contrairement aux autres colonnes (identifiant produit, date d’effet et prix).

Elle nous sera quand même utile, cette colonne id, mais plus tard, dans un cas très particulier : la détection de périodes de chevauchement (on verra ça vers la fin de l’article).

2.2 Première jointure entre produits et tarifs

Tout est dans le titre, alors c’est parti :

var sql = `SELECT prod.code_produit, tar.date_effet, tar.prix
FROM produits prod
INNER JOIN tarifs tar
  ON prod.id = tar.produit_id
`;
console.log(alasql(sql));

Voici le résultat sous la forme d’un tableau, pour plus de lisibilité :

code_produit

date_effet

prix

BR 500

2021-01-01

459

BR 550

2021-01-01

500

BR 600

2021-01-01

584

BT 121 C

2021-01-01

1109

BT 45

2021-01-01

1325

BT 46

2021-01-01

1620

FR 130 T

2021-01-01

2390

On voit que nos 7 produits ont tous un tarif applicable au 1er janvier 2021.

On décide d’appliquer une augmentation de tarif sur certains de nos produits... uniquement sur ceux dont le prix de vente est inférieur à 1500 euros. Cette augmentation sera de 5 %, et prendra effet au 1er juillet 2021.

En SQL, ça donne ça :

var params = ['2021-01-01', 1500];
var sql_increase_prices = `SELECT produit_id, '2021-07-01' as date_effet, prix * 1.05 as prix
FROM tarifs
WHERE date_effet = ? AND prix < ?
`;
console.log(alasql(sql_increase_prices, params));

Le console.log nous renvoie ceci :

[
  { produit_id: 80, date_effet: '2021-07-01', prix: 481.95000000000005},
  { produit_id: 81, date_effet: '2021-07-01', prix: 525 },
  { produit_id: 82, date_effet: '2021-07-01', prix: 613.2 },
  { produit_id: 83, date_effet: '2021-07-01', prix: 1164.45 },
  { produit_id: 31, date_effet: '2021-07-01', prix: 1391.25 }
]

Sur les 7 produits, on en a 5 qui ont été augmentés, car leurs prix respectifs étaient inférieurs à 1500 euros.

On voit qu’il y a un petit problème d’arrondi sur le premier produit, c’est dû au fait que les nombres en JavaScript sont traités selon la norme IEEE 754. Il existe différentes manières de contourner ce problème en JavaScript, je ne m’attarde pas sur le sujet maintenant, mais j’y reviendrai dans un prochain article. Et vous pouvez aussi approfondir cette question au travers d’un article paru dans GLMF n° 113 [5].

Puisque notre requête d’augmentation fonctionne bien, j’aimerais l’utiliser pour réinjecter ces nouvelles données à l’intérieur de la table des tarifs, sans écraser les données existantes.

Pour faire cela, il y a une technique SQL que j’aime beaucoup et qui est supportée par AlaSQL. Elle consiste à combiner en une seule requête un INSERT avec un SELECT. Je vous montre ce que ça donne :

var params = ['2021-01-01', 1500];
var sql_new_prices = `INSERT INTO tarifs (produit_id, date_effet, prix)
SELECT produit_id, '2021-07-01' as date_effet, prix * 1.05 as prix
FROM tarifs
WHERE date_effet = ? AND prix < ?
`;
alasql(sql_new_prices, params);

Voilà, c’est du SQL « deux en un », mais c’est surtout du SQL parfaitement standard. Vous pouvez donc utiliser cette technique avec la plupart des SGBD du marché (sauf un que je ne citerai pas).

Elle n’est pas belle, la vie ?

Je vous invite à relancer un simple SELECT sur la table des tarifs, vous constaterez que l’on a maintenant 12 lignes, dont 7 correspondent à des tarifs applicables au 1er janvier 2021, et 5 correspondent à des tarifs applicables au 1er juillet 2021.

Mais si on fait maintenant une jointure entre la table des produits et la table des tarifs, on va peut-être avoir un problème. Je dis « peut être », car tout dépend de ce que vous voulez afficher. Si vous voulez voir l’exhaustivité des tarifs par produit, tout va bien. Mais si vous voulez voir uniquement les tarifs au 1er mars, ou ceux applicables au 1er août, c’est une autre paire de manches !

Pour que ce soit clair, refaites « tourner » la jointure entre les deux tables, vous devriez obtenir ceci :

code_produit

date_effet

prix

BR 500

2021-01-01

459

BR 500

2021-07-01

481.95000000000005

BR 550

2021-01-01

500

BR 550

2021-07-01

525

BR 600

2021-01-01

584

BR 600

2021-07-01

613.2

BT 121 C

2021-01-01

1109

BT 121 C

2021-07-01

1164.45

BT 45

2021-01-01

1325

BT 45

2021-07-01

1391.25

BT 46

2021-01-01

1620

FR 130 T

2021-01-01

2390

Si on prend l’exemple de l’article BR550, son tarif applicable au 1er janvier 2021 comme au 1er mars de la même année, c’est 500. Mais à partir du 1er juillet et pour toutes les dates suivantes, son tarif est de 525.

Pour identifier le bon tarif en fonction de la date qui nous intéresse, la solution passe par l’utilisation d’une sous-requête spécialisée, dont l’unique but est de rechercher la date d’application la plus proche de la date demandée, et de renvoyer cette date à la requête principale. On placera cette sous-requête à la place des points d’interrogation que j’ai indiqués temporairement dans la clause WHERE ci-dessous :

SELECT prod.id, prod.code_produit, tar.date_effet, tar.prix
FROM produits prod
INNER JOIN tarifs tar
  ON prod.id = tar.produit_id
WHERE tar.date_effet = ( ???? )
ORDER BY prod.code_produit, tar.date_effet

Que va-t-on mettre dans cette sous-requête ? Eh bien, si on veut les tarifs applicables au 1er août, on écrira ceci :

SELECT max(tmp.date_effet) FROM tarifs tmp
WHERE tmp.produit_id = prod.id
  AND tmp.date_effet <= '2021-08-01'

Vous noterez la présence de la fonction max(). Elle est stratégique, puisque c’est elle qui va nous permettre de récupérer la date la plus proche de la date souhaitée (par rapport au 1er août 2021, puisque c’est notre argument de recherche).

Vous noterez également que cette sous-requête est une forme particulière de jointure, puisqu’elle fait le lien entre des lignes de la requête principale et des lignes produites par la sous-requête elle-même. Cette jointure se fait directement à l’intérieur de la clause WHERE de la sous-requête (c’est la condition tmp.produit_id = prod.id).

La requête finalisée est la suivante :

SELECT prod.code_produit, tar.date_effet, tar.prix
FROM produits prod
INNER JOIN tarifs tar
  ON prod.id = tar.produit_id
WHERE `tar.date_effet = ( SELECT max(tmp.date_effet) FROM tarifs tmp
                          WHERE tmp.produit_id = prod.id
                          AND tmp.date_effet <= '2021-08-01')`
ORDER BY prod.code_produit, tar.date_effet

À l’arrivée, nous obtenons le jeu de données suivant, ce qui correspond bien au résultat souhaité :

code_produit

date_effet

prix

BR 500

2021-07-01

481.95000000000005

BR 550

2021-07-01

525

BR 600

2021-07-01

613.2

BT 121 C

2021-07-01

1164.45

BT 45

2021-07-01

1391.25

BT 46

2021-01-01

1620

FR 130 T

2021-01-01

2390

On voit dans notre exemple que les produits BT 46 et FR 130 T ont un prix en date du 1er janvier 2021 : c’est normal, vu qu’ils n’ont pas subi d’augmentation au 1er juillet. Pour les autres produits, le prix qui a été retenu, c’est celui qui est applicable au 1er juillet. Je rappelle que j’ai demandé dans ma requête à afficher les tarifs applicables au 1er août 2021, donc on est bon.

Je vous donne maintenant le bout de code vous permettant de tester la requête dans AlaSQL :

var sql_new_price = `SELECT prod.code_produit, tar.date_effet, tar.prix
FROM produits prod
INNER JOIN tarifs tar
  ON prod.id = tar.produit_id
WHERE tar.date_effet = ( SELECT max(tmp.date_effet) FROM tarifs tmp
                          WHERE tmp.produit_id = prod.id
                          AND tmp.date_effet <= ?)
ORDER BY prod.code_produit, tar.date_effet`;
 
console.log(alasql(sql_new_price, ['2021-08-01']));

2.3 Jointures et coups tordus

Il y a un cas de figure un peu tordu avec les tarifs, c’est celui dans lequel on peut avoir des chevauchements de date.

Les bases de données de certains progiciels offrent cette possibilité. Voyons les problèmes que cela pose, et comment les contourner.

Pour cela, je vous propose de créer une seconde table de tarifs. Je l’ai appelée tarif2, elle a la structure suivante :

alasql("CREATE TABLE tarifs2 (id INTEGER AUTOINCREMENT PRIMARY KEY,
        produit_id INTEGER, date_eff_deb DATE, date_eff_fin DATE, prix DECIMAL )");

On voit que cette table a deux colonnes de type date, une date de début de tarif et une date de fin.

Je vous propose un jeu de données simplifié, avec 4 lignes de tarifs pour le même produit, et bien sûr des chevauchements de date :

var insert_datas = `INSERT INTO tarifs2
(produit_id, date_eff_deb, date_eff_fin, prix)
VALUES
(80, '2021-01-01', '2021-12-31', 459),
(80, '2021-07-01', '2021-12-31', 481.95),
(80, '2021-12-01', '2021-12-31', 491.589),
(80, '2021-12-01', '2021-12-15', 490);
`;
alasql(insert_datas);

Je vous avais parlé au début de l’article de la colonne id (en incrémentation automatique), et du fait qu’elle nous servirait tôt ou tard. Ça y est, c’est le moment. Car pour détecter les corrélations entre la requête principale et la sous-requête de détermination du tarif applicable, la transmission de cet identifiant est la seule technique qui fonctionne avec AlaSQL :

var sql = `SELECT prod.code_produit, tar.date_eff_deb, tar.date_eff_fin, tar.prix
FROM produits prod
INNER JOIN tarifs2 tar
  ON prod.id = tar.produit_id
WHERE tar.id =
( SELECT tmp.id
   FROM tarifs2 tmp
   WHERE tmp.produit_id = prod.id
   AND tmp.date_eff_deb <= ?
   AND tmp.date_eff_fin >= ?
   ORDER BY tmp.date_eff_deb DESC, tmp.date_eff_fin ASC
   LIMIT 1
)
ORDER BY prod.code_produit, tar.date_eff_deb
`;
 
console.log(alasql(sql, ['2021-12-15', '2021-12-15']));

Faites l’essai en exécutant la requête plusieurs fois, avec des dates différentes, par exemple :

  • entre le 1er et le 15 décembre ;
  • après le 15 décembre ;
  • avant le 1er décembre.

J’ai fait beaucoup de tests, jusqu’ici je n’ai pas réussi à mettre en défaut cette requête. Si vous y arrivez, je veux bien que vous me préveniez (en me donnant tous les détails), histoire que j’essaie de l’améliorer, si c’est possible.

3. AlaSQL et les CTE

3.1 Rappel du principe des CTE

En introduction de cet article, je précisais que j’avais publié en 2018 un article sur les CTE (Common Table Expression). À l’époque, le SGBD MariaDB venait d’intégrer cette fonctionnalité, que j’avais longuement expérimentée auparavant sur le SGBD DB2.

Les CTE sont vraiment très pratiques, car elles permettent dans de nombreux cas d’écrire un code SQL plus lisible, et donc plus maintenable. Il se trouve qu'AlaSQL sait gérer les CTE au moins partiellement, alors voyons comment cela fonctionne.

Vous savez peut-être qu’en SQL, il est possible d’imbriquer des requêtes SQL les unes dans les autres. Grosso modo, au lieu de faire un FROM sur une table, vous faites un FROM sur une sous-requête, qui elle-même peut aussi – soyons fou – faire un FROM sur une autre sous-requête. J’ai vu sur certains projets des requêtes imbriquées sur 4 ou 5 niveaux, je vous garantis que ça pique les yeux.

Donc, si vous travaillez sur une base de données qui ne permet pas d’écrire de CTE (par exemple MySQL), vous pouvez être amené à écrire des horreurs comme celle-ci :

SELECT *
FROM (
   SELECT *
   FROM (
      SELECT *
      FROM (
         SELECT * FROM tablex WHERE x <> y
      )
      WHERE condition machin
   )
   WHERE condition truc
)
WHERE condition bidule

Dans cet exemple, la requête la plus « en profondeur » est exécutée en premier, pour renvoyer un jeu de données à la requête de niveau supérieur, qui elle-même renvoie un jeu de données à la requête de niveau supérieur, qui...

Je ne sais pas vous, mais moi ça me fait penser aux poupées gigognes... Mais j’aime bien aussi l’analogie avec le film « Inception » de Christopher Nolan, et son concept de rêves imbriqués sur plusieurs niveaux.

Bon, là je vous ai mis un exemple plutôt « scolaire », mais il faut vous imaginer une requête équivalente avec des noms de colonnes au lieu d’astérisques, et beaucoup de colonnes en plus, parce que c’est généralement ce que l’on trouve dans les applications métiers. Et là, je vous garantis que vous êtes mal…

Alors, comment transformer notre requête mochissime en quelque chose de plus lisible grâce aux CTE ? Eh bien comme ceci :

 
WITH
cte1 AS (
   SELECT * FROM tablex WHERE x <> y
)
,
cte2 AS (
   SELECT * FROM cte1 WHERE condition machin
)
,
cte3 AS (
   SELECT * FROM cte2 WHERE condition truc
)
,
cte4 AS (
   SELECT * FROM cte1 WHERE condition bidule
)
 
SELECT * FROM cte4

Le mot clé WITH permet d’initialiser une CTE, ou comme ici une série de CTE (ici, cte1, cte2, etc.). Chaque CTE peut être totalement indépendante des autres, ou au contraire exploiter le résultat des CTE qui la précèdent. On peut par exemple écrire une CTE qui effectue une jointure entre une CTE précédente et une table SQL. Sachant que vous définissez comme vous voulez le nom de vos CTE, n’hésitez pas à utiliser des noms plus parlants que ceux que j’ai utilisés ici. Attention toutefois aux conflits de nommage avec des objets SQL déjà existants par ailleurs. C’est la raison pour laquelle j’ai pris pour habitude de préfixer mes CTE par cte_, mais ce n’est pas une obligation.

On notera qu’entre chaque CTE, il faut impérativement placer une virgule, et qu’il n’y a pas de virgule avant la toute dernière requête, qui est la seule à renvoyer le résultat final au client de la requête.

Un point très important : une CTE peut être réutilisée plusieurs fois (par les CTE suivantes et par la requête finale, si besoin est). Ce n’était pas possible avec le système des sous-requêtes imbriquées, tel qu’il était appliqué dans la requête précédente. C’est un axe important d’optimisation pour corriger des problèmes de performance.

3.2 Mise en pratique dans AlaSQL

Je vais en profiter pour vous montrer une autre manière d’injecter du code SQL dans AlaSQL, c’est une technique que j’ai découverte récemment en lisant des « issues » dans le dépôt GitHub du projet AlaSQL.

Le problème est le suivant : j’ai une table des ventes par pays, ville et date. J’aimerais obtenir une consolidation des ventes par pays, avec en plus un total général tous pays confondus.

Voici le code AlaSQL pour la création et l’alimentation de la table des ventes. Cette fois-ci, on fait du « tout en un » avec l’utilisation d’une fonction anonyme qui encapsule un CREATE TABLE suivi d’un INSERT, le tout placé dans un commentaire. Cela surprend un peu quand on n’a pas l’habitude, mais je vous garantis que ça fonctionne :

alasql(function(){/*
      -- Create an Employee table.
CREATE TABLE t_ventes (
  codpays VARCHAR(20),
  ville VARCHAR(20),
  mnt_vte DECIMAL(10,2),
  dat_vte DATE
);
-- Insert dataset
INSERT INTO t_ventes (codpays, ville, mnt_vte, dat_vte)
VALUES
('FR', 'Paris', 200, '2017-10-01'),
('FR', 'Paris', 800, '2017-09-01'),
('FR', 'Paris', 190, '2017-08-01'),
('FR', 'Paris', 230, '2017-10-03'),
('FR', 'Lyon', 200, '2017-10-05'),
('FR', 'Lyon', 390, '2017-09-05'),
('FR', 'Lyon', 720, '2017-08-05'),
('FR', 'Lyon', 110, '2017-10-05'),
('FR', 'Bordeaux', 160, '2017-08-03'),
('FR', 'Bordeaux', 500, '2017-10-05'),
('FR', 'Bordeaux', 330, '2017-09-05'),
('FR', 'Bordeaux', 120, '2017-08-05'),
('FR', 'Toulouse', 360, '2017-08-03'),
('FR', 'Toulouse', 600, '2017-10-05'),
('FR', 'Toulouse', 450, '2017-09-05'),
('FR', 'Toulouse', 720, '2017-08-05'),
('UK', 'Londres', 450, '2017-10-05'),
('UK', 'Londres', 530, '2017-09-05'),
('UK', 'Londres', 790, '2017-08-05'),
('UK', 'Londres', 330, '2017-07-05'),
('UK', 'Manchester', 200, '2017-08-01'),
('UK', 'Manchester', 330, '2017-07-01'),
('UK', 'Manchester', 120, '2017-10-03'),
('UK', 'Manchester', 640, '2017-09-03');
    */});

C’est pratique, non ?

Voyons maintenant la requête de consolidation des ventes. Elle se compose de deux CTE et d’une requête finale. Pour l’exécuter, j’ai utilisé la même technique que dans la requête précédente, avec une fonction anonyme encapsulant le code SQL mis en commentaire :

var res = alasql(function(){/*
WITH
cte_tot_country AS (
   SELECT codpays, SUM(mnt_vte) AS tot_vte
   FROM t_ventes
   GROUP BY codpays
),
cte_tot_general AS (
   SELECT SUM(tot_vte) as tot_vte
   FROM cte_tot_country
)
SELECT codpays, tot_vte
FROM cte_tot_country
UNION
SELECT 'Total général', tot_vte
FROM cte_tot_general;
    */});
 
console.log(res);

Et voilà le résultat :

[
  { codpays: 'Total général', tot_vte: 9470 },
  { codpays: 'FR',            tot_vte: 6080 },
  { codpays: 'UK',            tot_vte: 3390 }
]

Si vous avez du mal à comprendre comment cela fonctionne, je vous invite à exécuter chaque CTE individuellement. Par exemple, pour exécuter la première CTE, vous pouvez écrire ceci :

WITH
cte_tot_country AS (
   SELECT codpays, SUM(mnt_vte) AS tot_vte
   FROM t_ventes
   GROUP BY codpays
)
SELECT * FROM cte_tot_country ;

En procédant ainsi, étape par étape, vous allez vous approprier plus facilement le code et vous allez très rapidement maîtriser le code de la requête complète.

Dans mon article de janvier 2018, je parlais aussi des CTE récursives. Malheureusement, à l’heure où j’écris cet article, AlaSQL ne sait pas les gérer, donc j’arrête là.

3.3 Et si on parlait des perfs

Très honnêtement, je n’ai pas fait de mesures de performance précises pour comparer AlaSQL à des styles de programmation plus classiques (procédurale ou fonctionnelle). Mais je vais vous conter une petite anecdote qui me semble révélatrice.

Lorsque je développais le système de planning évoqué au début de cet article, j’ai eu quelques états d’âme. Je m’étais en effet servi d'AlaSQL pour, à partir d’un jeu de données assez volumineux transmis par une API (de 20 000 lignes en moyenne), extraire des données destinées à alimenter des listes déroulantes dans des balises HTML de type select. Grosso modo, cela se résumait à quelques requêtes SQL de ce type :

SELECT macolonne, count(*) as comptage FROM matable GROUP BY macolonne

Je me suis dit que j’avais peut être exagéré en faisant ça avec AlaSQL, car je pourrais obtenir sensiblement le même résultat avec de la programmation fonctionnelle, d’autant que les fonctions JavaScript map et reduce étaient disponibles depuis peu (c’était dans le courant de l’année 2019).

J’ai alors réécrit certaines parties du code en style fonctionnel... et j’ai obtenu des performances désastreuses. Mon planning ne s’affichait plus en 2 secondes, mais en 10. Surpris, et un peu dépité, j’ai vite réactivé mon code AlaSQL pour retrouver des performances correctes, en me disant qu’il faudrait que j’investigue sur le sujet un de ces jours. Faute de temps, je ne l’ai pas fait, mais j’ai constaté depuis, à plusieurs reprises, qu’une simple boucle for était plus rapide que les fonctions map, reduce, et même foreach. Ce problème est particulièrement sensible quand vous travaillez sur de gros volumes de données.

Tout ça pour dire que je n’ai plus d’état d’âme à utiliser AlaSQL, car je trouve ses performances tout simplement bluffantes. Je tire d’ailleurs mon chapeau à Andrey Gershun, le chef de file du projet, et à son équipe, parce qu’ils ont fait un sacré bon boulot.

 

Conclusion

J’ai essayé au travers de différents exemples de vous faire découvrir cet excellent outil qu’est AlaSQL.

Mais pour être honnête, je pense n’avoir qu’effleuré tout le potentiel de l’outil, car il y a beaucoup de choses que j’ai aperçues dans la documentation officielle et que je n’ai pas encore testées.

Pour qu'AlaSQL soit un moteur SQL vraiment complet, je pense qu’il conviendrait de lui ajouter le support des CTE récursives, ainsi que le support des fonctions OLAP [6]. Je vous recommande d’ailleurs la lecture du tableau de compatibilité SQL fourni dans la doc officielle [7], tableau qui permet de bien cerner les possibilités et les limites de l’outil. Je crois utile de souligner que, puisque l’on se trouve dans l’écosystème JavaScript, on peut largement profiter de la souplesse de ce langage pour combler certaines lacunes d'AlaSQL. Pour illustrer mon propos, je suis en train de préparer un jeu de requêtes SQL plus complexes que celles que nous avons vues dans le présent article. Je vous présenterai ces requêtes dans un second article consacré à AlaSQL (qui paraîtra très prochainement).

Je ne vous cache pas que je suis assez emballé par les possibilités qu’apporte AlaSQL aux développeurs JavaScript. Si vous avez envie de monter un fan-club avec moi, prévenez la rédaction du magazine, ils me transmettront le message ;).

Références

[1] G. JARRIGE, « Gérez les dates comme un pro avec SQL », GNU/Linux Magazine n°213, mars 2018 : https://connect.ed-diamond.com/GNU-Linux-Magazine/GLMF-213/Gerez-les-dates-comme-un-pro-avec-SQL

[2] G. JARRIGE, « CTE et Window Functions avec MariaDB », GNU/Linux Magazine n°211, janvier 2018 : https://connect.ed-diamond.com/GNU-Linux-Magazine/GLMF-211/CTE-et-Window-Functions-avec-MariaDB

[3] Le wiki d'AlaSQL : https://github.com/agershun/alasql/wiki

[4] SGBD : acronyme de « Système de Gestion de Base de Données »

[5] T. COLOMBO, « Au-delà des réels, l'aventure continue... », GNU/Linux Magazine n°113, février 2009 : https://connect.ed-diamond.com/GNU-Linux-Magazine/GLMF-113/Au-dela-des-reels-l-aventure-continue...    

[6] OLAP : acronyme de « OnLine Analytical Processing »

[7] Tableau de compatibilité SQL : https://github.com/agershun/alasql/wiki/SQL%20keywords



Article rédigé par

Par le(s) même(s) auteur(s)

Techniques avancées avec AlaSQL

Magazine
Marque
GNU/Linux Magazine
Numéro
253
Mois de parution
novembre 2021
Spécialité(s)
Résumé

Dans un précédent article [1], j’avais présenté le projet AlaSQL au travers de techniques SQL présentant un niveau de difficulté allant de facile à moyennement complexe. Parmi les techniques les plus complexes, nous avions vu comment manipuler des données liées à des dates d’effet. Dans ce nouvel épisode, j’ai voulu tester AlaSQL sur un panel de techniques un peu plus large, dans le but de déterminer jusqu’à quel point cet outil est en mesure de nous accompagner dans le développement de règles métier. On va voir que malgré quelques limites, AlaSQL peut couvrir de nombreux besoins avec efficacité, et même élégance (au moins, dans certains cas).

Et si nous retrouvions l’agilité, la vraie ?

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
115
Mois de parution
juillet 2021
Spécialité(s)
Résumé

Depuis quelques années, je croise de plus en plus de jeunes développeurs et développeuses, déboussolé-e-s, qui n’en peuvent plus, et veulent désespérément sortir du monde de l’IT. Pourtant, certaines de ces personnes sont particulièrement brillantes. Pourquoi sont-elles essorées, cramées, au bout de quelques années ? J’ai remarqué que toutes travaillent en mode agile. Alors… y aurait-il quelque chose de pourri au royaume de l’agilité ?

Les derniers articles Premiums

Les derniers articles Premium

Stubby : protection de votre vie privée via le chiffrement des requêtes DNS

Magazine
Marque
Contenu Premium
Spécialité(s)
Résumé

Depuis les révélations d’Edward Snowden sur l’espionnage de masse des communications sur Internet par la NSA, un effort massif a été fait pour protéger la vie en ligne des internautes. Cet effort s’est principalement concentré sur les outils de communication avec la généralisation de l’usage du chiffrement sur le web (désormais, plus de 90 % des échanges se font en HTTPS) et l’adoption en masse des messageries utilisant des protocoles de chiffrement de bout en bout. Cependant, toutes ces communications, bien que chiffrées, utilisent un protocole qui, lui, n’est pas chiffré par défaut, loin de là : le DNS. Voyons ensemble quels sont les risques que cela induit pour les internautes et comment nous pouvons améliorer la situation.

Surveillez la consommation énergétique de votre code

Magazine
Marque
Contenu Premium
Spécialité(s)
Résumé

Être en mesure de surveiller la consommation énergétique de nos applications est une idée attrayante, qui n'est que trop souvent mise à la marge aujourd'hui. C'est d'ailleurs paradoxal, quand on pense que de plus en plus de voitures permettent de connaître la consommation instantanée et la consommation moyenne du véhicule, mais que nos chers ordinateurs, fleurons de la technologie, ne le permettent pas pour nos applications... Mais c'est aussi une tendance qui s'affirme petit à petit et à laquelle à terme, il devrait être difficile d'échapper. Car même si ce n'est qu'un effet de bord, elle nous amène à créer des programmes plus efficaces, qui sont également moins chers à exécuter.

Donnez une autre dimension à vos logs avec Vector

Magazine
Marque
Contenu Premium
Spécialité(s)
Résumé

Avoir des informations précises et détaillées sur ce qu’il se passe dans une infrastructure, et sur les applications qu'elle héberge est un enjeu critique pour votre business. Cependant, ça demande du temps, temps qu'on préfère parfois se réserver pour d'autres tâches jugées plus prioritaires. Mais qu'un système plante, qu'une application perde les pédales ou qu'une faille de sécurité soit découverte et c'est la panique à bord ! Alors je vous le demande, qui voudrait rester aveugle quand l'observabilité a tout à vous offrir ?

Les listes de lecture

9 article(s) - ajoutée le 01/07/2020
Vous désirez apprendre le langage Python, mais ne savez pas trop par où commencer ? Cette liste de lecture vous permettra de faire vos premiers pas en découvrant l'écosystème de Python et en écrivant de petits scripts.
11 article(s) - ajoutée le 01/07/2020
La base de tout programme effectuant une tâche un tant soit peu complexe est un algorithme, une méthode permettant de manipuler des données pour obtenir un résultat attendu. Dans cette liste, vous pourrez découvrir quelques spécimens d'algorithmes.
10 article(s) - ajoutée le 01/07/2020
À quoi bon se targuer de posséder des pétaoctets de données si l'on est incapable d'analyser ces dernières ? Cette liste vous aidera à "faire parler" vos données.
Voir les 60 listes de lecture

Abonnez-vous maintenant

et profitez de tous les contenus en illimité

Je découvre les offres

Déjà abonné ? Connectez-vous