Cartouche

Introduction

Le but de ce sujet va être d’avancer sur la partie base de données du projet VenueBoard. Vous aller faire la conception, l’écriture des tables et les composants d’accès aux données.

Le projet VenueBoard a pour but de proposer des affichages dynamiques sur des événements ouverts au public comme des conventions, des salons, des journées portes ouvertes, etc. Ces affichages serviront dans les halls, couloirs et salle à informer le public des activités en cours (conférences, ateliers, démonstrations, dédicaces, restauration, etc.), d’afficher des éventuels retards, annulations, changements de salle mais aussi les modalités des activités comme le temps moyen passé sur le pole, la nécessité de s’inscrire, le nombre de place, etc.

Nous distinguons plusieurs utilisateurs :

• les gérants d’événement,
• les intervenants d’activité,
• les invités (qui n’ont pas besoin de se connecter pour l’instant),
• les afficheurs (qui n’ont pas besoin de se connecter pour l’instant)

Nous allons commencer par modéliser une partie de ces fonctionnalités, en se limitant à :

• gestion d’événements (nommés, avec description avec date de début et de fin)
• gestion d’activités (nommés, avec description, salle, intervenant, début prévu, fin prévue, début réel, fin réelle, nombre de places)
• gestion des utilisateurs (profils côté métier et identifiants côtés technique)

Étape 1 : modélisation

Pour valider l’étape : présentez vos schéma et faites les valider par votre enseignant·e. Fichier à rendre :

• si vous avez utilisé mocodo : MCD dans conceptual.mcd, MLD dans relational.mcd
• si vous avez modélisé autrement : mettez dans un conception.md un lien vers un dossier OneDrive qui contient les fichiers de votre logiciel de conception et ou des captures d’écrans de vos schéma, votre dossier doit être lisible par n’importe qui en possession du lien.

Produisez les schémas MCD et MLD qui permettent de modéliser en anglais les fonctionnalités ci-dessus.

Prévoyez une entité/table profil utilisateur et authentification séparées de sorte que les particularités liés à la sécurité ne polluent pas le métier. Nous travaillerons sur l’aspect sécurité dans un TD ultérieur.

Pour rappel:

• MCD = Modèle Conceptuel des Données, il représente les entités et leurs relations avec des verbes et des flèches. Il n’y a pas de notions de clés primaires, secondaires ou d’index, ni de table d’association ;
• MLD = Modèle Logique des Données, il représente les tables que l’on doit créer en base de données, cette fois-ci avec les clés primaires et étrangères. Les verbes d’associations sont remplacés par les tables d’association.

Vous pouvez utiliser les outils que vous souhaitez pour faire ces modélisations, parmi les options vous avez :

• Mocodo (documentation version en ligne) une base de MLD pouvant être générée à partir d’un MCD
  • sur la version en ligne avec “conversion en sortie” → “diagramme relationnel en sortie” puis accessible dans “autres sorties” après un rafraîchissement ;
  • sur la version CLI avec mocodo --input votre_fichier.mcd --transform diagram ;
  • dans les deux cas le code mocodo produit peut être mis à nouveau en entrée de mocodo pour qu’il affiche le MLD généré.

Étape 2 : SGDB PostgreSQL

Fichier à rendre :

• .env pour les installation natives ou via docker ;
• .env.example si vous utilisez un hébergeur cloud ;
• .gitignore si vous utilisez un hébergeur cloud (pour ignorer .env) ;
• docker/{docker-compose.yml, *} si vous utilisez docker.

Pour la suite vous avez besoin d’un SGDB PostgeSQL. Pour en déployer un avec docker, vous pouvez utiliser : https://git.ecole-89.com/eriizu/2025_wap201_pg.

Vous pouvez aussi faire appel à un hébergeur ou installer PostgreSQL en natif :

• sur Linux avec votre gestionnaire de paquet ;
• sur Windows avec le site officiel.

sudo -u postgres psql

Puis en SQL créez l'utilisateur du projet et sa base de données :
```sql
-- create a role that can log in and has a password

CREATE ROLE example_name WITH LOGIN PASSWORD ‘example_password’; – create a database owned by that role CREATE DATABASE example_name OWNER example_name;

Avec les trois méthodes vous vous retrouverez avec des identifiants de connexion et un nom de base de données. Consignez-les dans un fichier .env de cette façon :

PGHOST=localhost
PGPORT=5432
PGUSER=alice
PGDATABASE=alice
PGPASSWORD=mypassword

Si vous utilisez un hébergeur :

• le PGHOST sera différent de localhost ;
• N’AJOUTEZ PAS VOTRE FICHIER À UN COMMIT ;
• rendez seulement un .env.example sans le champ mot de passe (ni d’hôte, s’il contient des informations confidentielles).

L’avantage de ce fichier .env c’est que la commande psql et le paquet postgres pour NodeJS seront capable de s’en servir pour vous connecter, sans configuration supplémentaire.

Sur Linux, si vous voulez utiliser la commande psql, pour activer des variables d’environnement issues d’un .env depuis bash ou zsh :

set -a
source .env
set +a

En NodeJS, pour les activer depuis votre code :

process.loadEnvFile();

Étape 3 : migration initiale

Fichiers à rendre : migrations/00.sql

Écrivez dans un fichier migrations/00.sql la première migration pour une base de donnée postgres. Suivez les conseils suivants :

• les clés primaires prennent le type SERIAL
• les clés étrangères sont annotées par REFERENCES et ont une close ON DELETE
• les colones qui sont des chaînes de caractère ont le type TEXT
• les tables d’association ont pour index la combinaison des deux clés étrangères.

Voici un exemple de déclarations de tables avec ces éléments pour votre inspiration :

CREATE TABLE IF NOT EXISTS focus (
    focus_id SERIAL PRIMARY KEY,
    focus_name TEXT NOT NULL,
    focus_ttl DATE,
    author_id INT REFERENCES "user_profile"(id) ON DELETE CASCADE NOT NULL,
    focus_created_on TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE jt_focus_tag (
    focus_id INT REFERENCES "focus"(focus_id) ON DELETE CASCADE NOT NULL,
    tag_id INT REFERENCES "tag"(tag_id) ON DELETE CASCADE NOT NULL,
    CONSTRAINT focus_tag_pkey PRIMARY KEY (focus_id, tag_id) -- # seen here https://stackoverflow.com/questions/9789736/how-to-implement-a-many-to-many-relationship-in-postgresql#9790225 seconded by https://dev.natestephens.com/postgresql-many-to-many
);

Écrivez un fichier queries/seed.sql une série de requête qui remplie vos tables avec des données d’exemple, pour faciliter la suite du travail.

Testez le tout sur votre base de données.

Étape 4 : le code, requêtes CRUD

Fichier à rendre : src/db.ts

Pour travailler avec postgres, le plus simple c’est d’utiliser le client NodeJS (et deno) éponyme. Il a pour avantage de :

• gérer le typescript nativement ;
• faciliter la génération conditionnelle de requêtes ;
• prévenir des injections SQL tout en utilisant la même syntaxe que les chaînes de formatage JS.

Avec les variables d’environnement paramétrées et chargées comme décrit au dessus la connexion à votre base de donnée s’exprimera ainsi :

import postgres from "postgres";

process.loadEnvFile();
const sql = postgres();

type AuthUser = {
  auth_username: string;
  auth_path_hash: string;
  auth_roles: string[];
};

sql<AuthUser[]>`SELECT * FROM "auth"`
  .then((auth_users) => {
    console.log(auth_users);
    sql.end();
  })
  .catch(console.error);

Vous pouvez faire cet essai avec un type et une requête qui concordent avec vos tables.

Dans un module db créez la classe Repository à laquelle vous créerez une méthode par requête dont vous avez besoin dans le projet.

export class Repository {
  sql: postgres.Sql;

  constructor() {
    this.sql = postgres();
  }

  async getUser() {
    return await this.sql<AuthUser[]>`SELECT * FROM "auth"`;
  }

  // INFO: call this when the program is done, otherwise it will hang on the open connection.
  async end() {
    return this.sql.end();
  }
}

Pour commencer, écrivez pour chacune de vos tables des requêtes pour :

• ajouter une ligne,
• récupérer une ligne par son ID,
• modifier une ligne par son ID et en utilisant un objet partiel,
• supprimer une ligne.

type AuthUser = { auth_username: string; auth_path_hash: string; auth_roles: string[]; };

type PartialAuthUser = Partial<AuthUser>; // is equivalent to: type PartialAuthUser = { auth_username?: string; auth_path_hash?: string; auth_roles?: string[]; }

Étape 5 : requêtes “complexes”

Nos événements peuvent avoir plusieurs activités, et il est fréquent de vouloir récupérer événements et les activités en même temps.

Pour faciliter cette opération de récupération, écrivez une méthode qui renvoie un événement et toutes ses activités associées. Le tout doit être fait avec une seule requête SQL. Le résultat doit être un objet qui représente l’événement et dont l’un des champs est un tableau d’activités.

Exemple tronqué de ce à quoi peut ressembler le résultat de cette query :

{
	"name": "Paris Games Week",
	"starts_on": "...",
	"ends_on": "...",
	"activities": [
		{ "name": "Démonstrations Nintendo", location_id: 10 },
		{ "name": "Finale Esport Master", location_id: 42 },
		{ "name": "Stand Écoel 89", location_id: 5 }
	]
}

Conclusion

Grâce à ces étapes de conception, migration, connexion, et d’écriture des requêtes dans un repository, vous avez tous les éléments nécessaire pour avancer sur la partie métier du projet, puis sur l’implémentation de l’API en elle-même.

Si nous avions eu plus de temps, nous aurions pu faire un dépôt de données (un repository) en RAM, sans utiliser une base de données. Son avantage ? Faciliter les tests de l’API, car ils ne nécessiteraient pas de base de données pour fonctionner. De plus en faisant respecter une même interface à ces deux dépôts, ils seraient interchangeables, injectables, sans avoir a modifier de code ailleurs.