05 Structure de données (1) - damiencorpataux/infradon1 GitHub Wiki

Dans la session précédente, nous avons la dérivation de diagrammes de classes jusqu'au niveau implémentation c'est à dire le langage SQL et l'exécution de requêtes par le moteur Postgres.

Nous allons maintenant voir deux primitives structurelle SQL couramment utilisées: les VIEW et les INDEX.

Mais tout d'abord un petit rappel pratique.

Rappel: Requêtes SQL `SELECT`

Structure d'une requête SQL de type SELECT:
Requête SQL SELECT
Source: https://learnsql.com/blog/sql-query-basic-elements/

Notes sur `GROUP BY`:

Avec la clause GROUP BY, nous devons utiliser des fonctions d'aggrégation sur les colonnes SELECTionnées mais non-groupées, c-à-d non-incluses dans le GROUP BY.

Exemples de fonctions d'aggrégation courantes dans Postgres (tuto):
https://www.postgresqltutorial.com/postgresql-aggregate-functions/
AVG(), COUNT(), MAX(), MIN(), SUM()
La fonction STRING_AGG() (tuto):
https://www.postgresqltutorial.com/postgresql-aggregate-functions/postgresql-string_agg-function/
Toutes les fonctions d'aggrégation dans Postgres (regardez la définition de celles que vous avez vues ci-dessus)

Avec la clause GROUP BY, si nous ne mettons pas une fonction d'aggrégation sur une colonne SELECTtionnée, nous devons la mettre dans la clause GROUP BY.

Regargez cet exemples: https://github.com/damiencorpataux/infradon1/tree/main/examples/sessions/05-group-by.sql

Rappel: SQL `JOIN`

Nous avons 4 variantes de jointures: SQL Joins
Source: https://www.w3schools.com/sql/sql_join.asp

Concernant les clauses JOIN, il est important de voir la nature combinatoire du moteur SQL:
SELECT * FROM contact, status nous retourne toutes les combinaisons possibles entre les lignes de la table contact et celles de la table status. Vérifiez par vous-même sur votre Postgres local.

Afin de faire la jointure, nous pouvons exprimer
SELECT * FROM contact, status WHERE contact.status_id = status.id afin de ne retourner que les lignes satisfaisant la condition WHERE, c'est-à-dire les lignes de status qui correspondent à la valeur de contact.status_id. Vérifiez par vous-mêmes.

Ainsi, les 2 requêtes suivantes donnent le même résultat, elle sont équivalentes mais l'une indique clairement que nous voulons faire une jointure (JOIN) tandis que pour l'autre, nous devons deviner (WHERE).

SELECT * FROM contact, status WHERE contact.status_id = status.id
SELECT * FROM contact JOIN contact ON contact.status_id = status.id

Afin de le vérifier de manière pratique, nous pouvons comparer ce que fait le moteur SQL lors de l'exécution de chacune des requêtes:

EXPLAIN SELECT * FROM contact, status WHERE contact.status_id = status.id
EXPLAIN SELECT * FROM contact JOIN status ON contact.status_id = contact.status_id

Notez que la suite des opérations effectuées par le moteur est identique pour les 2 requêtes. Vérifiez par vous-même !

💡 Maintenant traduisez chaque type de clause JOIN (INNER, LEFT, RIGHT, FULL OUTER)
en clause SELECT ... WHERE ..., par exemple:

INNER JOIN ou JOIN:

SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id;
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id;

RIGHT JOIN:

SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.t1_id;
SELECT FROM t1, t2 WHERE t1.id = t2.t1_id OR t2.t1_id = NULL

LEFT JOIN:

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.t1_id;
SELECT FROM t1, t2 WHERE ...

FULL OUTER JOIN: ...

Les quotes (guillemets)

Il est important de comprendre la partie syntaxe SQL concernant la signification des guillemets (quotes). En effet, le moteur SQL Postgres interprète les différents guillemets (', " et `) de manière bien précise.

Démonstration:

CREATE TABLE test espace (name espace TEXT NOT NULL);
CREATE TABLE 'test espace' ('name espace' TEXT NOT NULL);
CREATE TABLE "test espace" ("name espace" TEXT NOT NULL);
INSERT INTO 'test espace' ('name espace') VALUES ('Il faut échapper (escape) l''apostrophe');
INSERT INTO "test espace" ("name espace") VALUES ("Ca s'rait sympa mais ca marche pas: les guillemets, c'est pour désigner une colonne ou une table");
INSERT INTO "test espace" ("name espace") VALUES ('Il faut échapper (escape) l''apostrophe');
DROP TABLE "test espace";

A vous: Quelle déduction mnémo-technique faites-vous de ceci ?

Rappel: SQL Contenu: `INSERT INTO` & Cie

Il y a plusieurs moyens d'insérer des données. Nous allons utiliser pour l'instant INSERT INTO:

Pour insérer insérer un enregistrement unique:

INSERT INTO <table> (<colonnes-csv>) VALUES (<values-csv>)

Pour insérer insérer des enregistrements multiples:

INSERT INTO <table> (<colonnes-csv>) VALUES
  (<values-csv>),
  (<values-csv>),
  ...
  (<values-csv>);

Note: csv signifie Comma-Separated Values: valeurs séparées par des virgules - remplacez les blocs génériques <...> par vos termes spécifiques.

Nous verrons plus tard les autres moyens d'insérer des données:

En groupe avec l'instruction SQL TRANSACTION
En masse avec l'instruction SQL COPY
En masse avec les commandes CLI pg_dump et pg_restore, à exécuter dans shell système (bash, zsh ou powershell)

Les `VIEW`

En SQL, les vues permettent de factoriser une requête SQL afin de la réutiliser. Pour ce faire, nous déclarons une vue:

CREATE VIEW view_contact_status AS
    SELECT
        contact.id AS id,
        status.id AS status_id,
        contact.name AS name,
        status.name AS status_name
    FROM contact
    JOIN status ON contact.status_id = status.id;

Ensuite, nous pouvons interroger cette vue:

SELECT * FROM view_contact_status;

L'usage des VIEW améliore la lisibilité des requêtes, mais pas la performance de ces requêtes: le moteur SQL exécute les SELECT sur les VIEW exactement comme il exécuterait les requêtes sous-jacentes.
https://dba.stackexchange.com/a/151220

Une vue se comporte comme une table, nous pouvons donc applique les clauses WHERE, ORDER BY, GROUP BY, JOIN, etc.

Essayez avec la structure SQL de l'exercice 02-micromessenger-model.sql:

SELECT *
FROM view_contact_status
WHERE status_id = 1

SELECT *
FROM view_contact_status
ORDER BY status_id DESC

SELECT status_id, status_name, COUNT(status_id)
FROM view_contact_status
GROUP BY status_id

SELECT status_id, status_name, COUNT(status_id)
FROM view_contact_status
GROUP BY status_id

SELECT *
FROM view_contact_status
JOIN rel_message AS rel_message_source ON rel_message_source.contact_id_source = view_contact_status.id
JOIN rel_message AS rel_message_destination ON rel_message_destination.contact_id_destination = view_contact_status.id

SELECT
  rel_message_source.contact_id_source AS source_contact_id,
  rel_message_destination.contact_id_destination AS destination_contact_id,
  rel_message_source.content AS content
FROM view_contact_status
JOIN rel_message AS rel_message_source ON rel_message_source.contact_id_source = view_contact_status.id
JOIN rel_message AS rel_message_destination ON rel_message_destination.contact_id_destination = view_contact_status.id

SELECT
  rel_message.creation AS creation,
  rel_message.content AS content,
  view_contact_status_source.id AS contact_id_source,
  view_contact_status_destination.id AS contact_id_destination,
  view_contact_status_source.name AS contact_name_source,
  view_contact_status_destination.name AS contact_name_destination,
FROM rel_message
JOIN view_contact_status AS view_contact_status_source ON view_contact_status_source.id = rel_message.contact_id_source
JOIN view_contact_status AS view_contact_status_destination ON view_contact_status_destination.id = rel_message.contact_id_destination

Faites une requête SQL de base sur la table view_contact_status et ajouter successivement les clauses et fonctions ci-dessus: WHERE, JOIN, GROUP BY, ORDER BY
Pouvez-vous expliquer ce qui se passe derrière, dans le moteur SQL de Postgres, pour chacune de ces requêtes ?
Maintenant, préfixez chacune des requêtes successives que vous avez produites avec l'instruction EXPLAIN
Pour chaque requête expliquez au professeur ce que vous lisez, à tour de role pour chaque requête

Les `MATERIALIZED VIEWS`

Les vues matérialisées sont plus rapides que les vues car les données sont pré-calculées. Cependant, lorsque les données des tables impliquées dans la requête sous-jacente, les vues matérialisées doivent être re-calculées de manière explicite via l'instruction REFRESH MATERIALIZED VIEW <view_name> pour que les données soient à jour.

Par exemple, dans la vue matérialisée décrite ci-dessous, lorsque je modifie le contenu des table t1 ou t2, je dois mettre à jour la vue matérialisée. Sinon, les données de cette dernière ne reflèteront pas le nouvel état de ces tables:

CREATE MATERIALIZED VIEW mview_t1_t2 AS
  SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id;

UPDATE t2 SET t1_id = 2 WHERE id = 1;
SELECT * FROM mview_t1_t2;

REFRESH MATERIALIZED VIEW mview_t1_t2;
SELECT * FROM mview_t1_t2;

Explication: https://www.postgresqltutorial.com/postgresql-views/postgresql-materialized-views/

A vous de jouer: Vérifiez pratiquement le fonctionnement des vues matérialisées décrites dans le tutoriel ci-dessus.

En ajoutant EXPLAIN au début de la requête voyez la différence de traitement entre l'exécution de la requête SQL et de la même requête dans une vue matérialisée.

EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id;
EXPLAIN SELECT * FROM mview_t1_t2;

Les `INDEX`

Pour accélérer les clauses WHERE, nous pouvons créer des index pour les colonnes sur lesquelles nous appliquons des clauses WHERE.

A vous de jouer:

Trouver des explications sur comment déclarer un INDEX sur une colonne

Ajouter 10'000'000 de lignes à une table d'une database d'exemple (par exemple contacts) et faire un SELECT contenant une clause WHERE sur une colonne en testant les 2 cas: avec et sans INDEX

INSERT INTO contact (name)
  SELECT md5
  FROM (
    SELECT md5(random()::text) AS md5
    FROM generate_series(1,10000000)
  ) AS subquery;

SELECT COUNT(*) FROM contact WHERE name LIKE '%ab%';
SELECT COUNT(*) FROM contact WHERE name = 'Toto';
EXPLAIN ANALYZE SELECT COUNT(*) FROM contact WHERE name = 'Toto';

CREATE INDEX contact_name_index ON contact(name);
SELECT COUNT(*) FROM contact WHERE name = 'Toto';
EXPLAIN ANALYZE SELECT COUNT(*) FROM contact WHERE name = 'Toto';

Les `SEQUENCE`

A vous de jouer: voyez par vous-même. Par exemple:

CREATE TABLE test (id SERIAL, name NOT NULL)
Que se passe-t-il ici ?
\d
Que comprenez vous ici ?
\d test
Qu'est-ce que c'est ceci ?
\d test_id_seq
Qu'est-ce que c'est cela ?
INSERT INTO test (name) VALUES ('Inséré sans spécifier l''id')
Que se passe-t-il ici ?
By the way, le '' ? Est-ce nécessaire ? Si oui, pourquoi ?
INSERT INTO test (id, name) VALUES (2, 'Inséré en spécifiant l''id')
Que se passe-t-il là ?
SELECT * FROM test
Quels mécanismes ont été actionner pour produire cet état des choses ?
INSERT INTO test (name) VALUES ('Inséré sans spécifier l''id')
Que s'est-il passé ici ?
INSERT INTO test (name) VALUES ('Inséré sans spécifier l''id') Que s'est-il passé là ?
DROP TABLE test Pourquoi fait-on ceci ?

Exercice: Import des données d'exemple

Nous allons importer un gros modèle de données pour expérimenter avec les éléments de requêtage SQL que nous avons vu jusqu'ici.

Nous importons le jeu d'exemple afin de pratiquer l'utilisation du langage SQL sur Postgres.

A nous de jouer:

Inspectons le jeu de données dans le répertoire /data du dépôt github

A vous de jouer:

Population du jeu de données d'exemple pour les 2 environnements
- Expliquez ce qui s'est passé par une liste d'étape sous forme de bullet-points, dont vous choisissez le niveau de granularité.
Populez la base de données avec le jeu de données d'exemple en suivant les instruction pratiques
Essayez des requêtes SQL de type SELECT sur le jeu de données
- SELECT * FROM customer;
- SELECT COUNT(*) FROM customer;
Comprenons la base de données et jouons un peu avec
- Examiner le contenu des fichiers à importer
- Lister les tables et voir leur description (\l, \d, \d <table>)
- Extraire le diagramme relationnel avec PgAmin4 - trouvez comment sur un moteur de recherche !

Si vous n'êtes pas présent à cette séance, il vous est fortement recommendé de la rattraper en communiquant avec vos collègues - pour avoir le jeu de données d'exemple que nous sommes susceptibles d'utiliser ces prochaines séances pour pratiquer, expérimenter et se démontrer comment fonctionne le moteur Postgres.

05 Structure de données (1) - damiencorpataux/infradon1 GitHub Wiki

Rappel: Requêtes SQL SELECT

Notes sur GROUP BY:

Rappel: SQL JOIN

Les quotes (guillemets)

Rappel: SQL Contenu: INSERT INTO & Cie

Les VIEW

Les MATERIALIZED VIEWS

Les INDEX

Les SEQUENCE