TD2 : Node.js et postgreSQL pour la BBI !

1. Introduction : Les services

Ce TD a pour but de vous familiariser avec l'utilisation de la librarie pg de Node.js pour interagir avec une base de données PostgreSQL.

L'objectif final du projet est de créer toute la partie backend d'un site permettant de gérer les informations du laboratoire de recherche botanique intergalactique. Mais pour l'instant, nous allons nous concentrer sur la création de la partie services.

Les services sont des fonctions qui permettent d'interagir directement avec la base de données. Ces fonctions seront ensuite utilisées par le controleur de notre API mais gardons cette étape pour le TD3.

2. Mise en place

Créez un nouveau répertoire pour ce projet que vous pouvez nommer td-psql-bbi.
Initialisez un nouveau projet Node.js avec la commande npm init.
Installez les bibliothèques javascript suivantes :

• pg | npm install pg : Connexion et requêtes vers PostgreSQL en javascript.
• dotenv | npm install dotenv : Gestion des variables d'environnement.

Créez un fichier .env à la racine de votre projet pour stocker les informations de connexion à la base de données PostgreSQL. Voici un exemple de contenu pour ce fichier :

DB_USERNAME=melvyn
DB_PORT=5432
DB_HOST=localhost
DB_DATABASE=td
DB_PASSWORD=postgres

Créez un dossier database/ à la racine de votre projet. Dans ce dossier, créez un fichier db.js permettant de se connecter à la base de données PostgreSQL en utilisant les informations de connexion stockées dans le fichier .env.

const {Pool} = require("pg");
require('dotenv').config();

const credentials = {
    user: process.env.DB_USERNAME,
    host: process.env.DB_HOST,
    database: process.env.DB_DATABASE,
    password: process.env.DB_PASSWORD,
    port: process.env.DB_PORT,
};
const pool =  new Pool(credentials)
module.exports = pool;

Vous pouvez aussi déposer dans ce répertoire vos scripts SQL de création et d'initialisation de la base de données.

Créez un dossier services/ à la racine de votre projet. Dans ce dossier, créez les fichiers : planet_service.js, plant_service.js et scientist_service.js qui contiendront les fonctions permettant d'interagir avec les tables planets, plants et scientists respectivement.

Exemple de fonction de service dans le fichier planet_service.js :

const pool = require("../database/db");

/* Q1 - Retourner la liste des planètes du laboratoire. */
async function get_planets(){
    let client = await pool.connect();
    res = (await client.query("SELECT * FROM planet;")).rows;
    client.release();
    return res;
}

module.exports = {
    get_planets
};

Pour tester vos services créez un fichier test_service.js à la racine de votre projet. Dans ce fichier, importez les services et appelez-les pour vérifier qu'ils fonctionnent correctement.

Par exemple :

let planet_service = require("./services/planet_service");
            
async function test_Q1(){
    try {
        let planets = await planet_service.get_planets();
        console.log("All planets:", planets);
    } catch (err) {
        console.error("Error during test_Q1:", err);
    }
}

// Execute the test :
test_Q1();

3. À vous de jouer !

Implémentez les fonctions de services suivantes en respectant bien les consignes données par la BBI. Faites attention : réaliser les bonnes requêtes est une chose, mais la plus grande difficulté c’est de correctement gérer TOUTES les erreurs. Pour cela, veillez à envisager tous les cas particuliers !

SELECT :

Q1 - Retourner la liste des planètes du laboratoire.

Q2 - Récupérer les informations d’un scientifique à partir de son identifiant.

• Si le scientifique n’existe pas, retourner une liste vide.

Q3 - Récupérer les informations d’une planète à partir de son identifiant.

• Si la planète n’existe pas, retourner une liste vide.

Q4 - Lister les plantes du laboratoire.

• Par défaut, lister toutes les plantes triées par identifiant croissant.
• Inclure dans le résultat le nom de la planète d’origine de la plante avec l’alias planet_name.

Q5 - Lister les plantes étudiées par un scientifique donné.

• Utilisez le paramètre obligatoire scientist_id pour identifier le scientifique.

UPDATE :

Q6 - Mettre à jour le niveau du scientifique.

• Utilisez les paramètres obligatoires scientist_id et authorization_level.
• En cas d’échec, émettre une exception (throw) avec un message d’erreur approprié.

INSERT :

Q7 - Ajouter une nouvelle plante dans la base de données.

• Les paramètres sont les champs de la table plants, à l’exception de l’identifiant qui est auto-généré.
• Certains paramètres peuvent être null.

DELETE :

Q8 - Suppression d’une plante à partir de son identifiant.

• Régler les problèmes de clés étrangères si nécessaire.
• Utiliser une transaction pour s’assurer que toutes les opérations sont effectuées correctement ou revenir en arrière en cas d’erreur.
• En cas d’échec, émettre une exception (throw) avec un message d’erreur approprié.

REQUÊTES AVANCÉES :

Q9 - Modifier la requête Q4 pour trier les plantes.

• Ajouter un paramètre optionnel sort_by qui peut prendre les valeurs plant_id, latin_name, common_name, is_toxic ou authorization_level.
• Ajouter un paramètre optionnel sort_way qui peut prendre les valeurs ASC ou DESC.
• Les plantes avec des caractéristiques nulles doivent toujours être listées en dernier, quel que soit le sens du tri.
• Les plantes avec des caractéristiques identiques doivent être triées par identifiant croissant.

Q10 - Modifier la requête Q4 pour filtrer les plantes. Ajoutez les paramètres optionnels suivants :

• filter_name : ne lister que les plantes dont le nom latin ou le nom commun contient cette chaîne de caractères (insensible à la casse).
• filter_is_toxic : ne lister que les plantes dont le champ is_toxic correspond à cette valeur (true ou false).
• filter_min_authorization_level : ne lister que les plantes dont le champ authorization_level est supérieur ou égal à cette valeur.
• filter_max_authorization_level : ne lister que les plantes dont le champ authorization_level est inférieur ou égal à cette valeur.

Q11 - Éditer les informations d’une plante à partir de son identifiant.

• Paramètre obligatoire : id.
• Paramètres optionnels : latin_name, common_name, is_toxic, authorization_level, planet_id.
• Renseigner au moins un des paramètres optionnels ou émettre une exception (throw) avec un message d’erreur approprié.
• Si l’une des propriétés de la plante vaut null, il faut définir la propriété à null dans la base de données sauf si une contrainte l’interdit.

Q12 - Permettez à un scientifique d’étudier une nouvelle plante.

• Utilisez les paramètres obligatoires scientist_id et plant_id pour identifier le scientifique et la plante.
• En cas d’échec, émettre une exception (throw) avec un message d’erreur approprié.

Q13 - Permettez à un scientifique d’arrêter d’étudier une plante.

• Utilisez les paramètres obligatoires scientist_id et plant_id pour identifier le scientifique et la plante.
• En cas d’échec, émettre une exception (throw) avec un message d’erreur approprié.

Q14 - Listez tous les scientifiques qui étudient des plantes avec un niveau d’autorisation supérieur au leur.

• Pour chaque scientifique, retournez son identifiant, son nom et son niveau d’autorisation.
• Ajoutez à cela la liste des plantes interdites avec leur identifiant, leur nom latin, leur nom commun et leur niveau d’autorisation.

- Préparation Q15 - Q16 :

• Modifiez la table scientists pour ajouter une nouvelle colonne password de type texte qui ne peut pas être nulle.
• Ajoutez une table scientist_passwords qui enregistre les historiques de mots de passe des scientifiques avec les colonnes suivantes :
  • id : identifiant unique du mot de passe (clé primaire).
  • scientist_id : identifiant du scientifique (clé étrangère vers scientists).
  • password : le mot de passe (texte non nul).
  • changed_at : date et heure du changement de mot de passe (timestamp non nul, valeur par défaut : date et heure actuelle).
• Le mot de passe actuel du scientifique ne doit pas être stocké dans la table scientist_passwords.
• Vérifiez que la requête Q1 ne retourne pas le mot de passe du scientifique.

Q15 - Authentification d’un scientifique.

• Utilisez les paramètres optionnels id, username ou email pour identifier le scientifique (uniquement l’un des trois).
• Utilisez le paramètre obligatoire password pour vérifier le mot de passe du scientifique.
• Retournez true si les informations d’authentification sont correctes, sinon false.

Q16 - Modifiez le mot de passe d’un scientifique.

• Utilisez les paramètres optionnels id, username ou email pour identifier le scientifique (uniquement l’un des trois).
• Utilisez le paramètre obligatoire last_password pour authentifier le scientifique.
• Utilisez le paramètre obligatoire new_password pour définir le nouveau mot de passe du scientifique.
• Utilisez une transaction pour vous assurer que toutes les opérations sont effectuées correctement ou revenez en arrière en cas d’erreur.