Introduction
La nuit dernière a été un vrai marathon éveillé, grâce à nos chères petites filles qui ont décidé de danser la Macarena toute la nuit. Mais je me suis réveillé frais comme un gardon, contrairement à ma moitié qui a apparemment fait la nuit blanche, en faisant face à un tri nocturne. En me levant, j'ai eu la surprise de trouver cette pyramide de DVD transmise par mon beau-père.
La vision de cette collection a réveillé mon âme d'aficionado de bases de données, qui me hurle intérieurement d'organiser au plus vite ces chefs-d'œuvre dans une base de données. Après tout, avec une famille qui carbure aux films, il nous fallait logiquement une interface graphique web pour faciliter la vie de tous. Mais pas le temps de rêvasser, ma chérie veut déjà jeter la moitié des DVD, alors que je n'ai même pas encore commencé à trier ! C'est pas une vie, ça
Technologies
Bien sûr, on pourrait demander à notre moteur de recherche favori un bon logiciel de gestion de DVDthèque. On pourrait aussi utiliser LibreOffice Base ou Microsoft Access, mais où serait le plaisir ? Non, le plan, c'est de créer un logiciel from scratch qui gérera les DVD de la famille. Qui utilise encore des DVD de nos jours de toute façon ? La mode actuelle est au dématérialisé. Posséder quelque chose de physique est tellement has-been.
Alors oui, j'ai lu "Clean Architecture" de l'oncle Bob et je sais très bien que la base de données est un détail. C'est pourquoi, pour ce projet, je vais utiliser une architecture centrée sur les données et commencer par faire le schéma, puis les services d'accès aux données, et terminer par l'interface graphique (Ah, au moins j'ai compris que l'interface utilisateur était un détail). Pour le reste, il faudrait probablement que je relise ce livre :D Mais les projets perso, c'est pour vivre d'incroyables aventures, n'est-ce pas ? :)
La vérité, c'est que j'ai une idée derrière la tête : je veux vous faire découvrir PostgREST. Non, ce n'est pas une faute de frappe. Il s'agit d'une application qui sert une API REST à partir d'une base de données PostgreSQL. J'en parle dans le deuxième article de cette série, mais d'abord, il faut que je pose les bases. Alors c'est parti, commençons par analyser ce qu'il nous faut comme données !
Fonctionnalités
- Gestion des infos de base des DVDs (titre, age minimum recommandé, année de sortie, durée)
- Acteurs des films
- Langues des bandes son, langues des sous-titres
- Genres cinématographiques (action, ou bien ... action ! Je ne tolère rien d'autre chez nous de toute façon. À l'exception d'H2G2 bien sûr)
- Emplacement (bibliothèque, bibliothèque privée des parents, vidéothèque privée de Papa)
- PNG de la jaquette
- Prêt (nom, date du prêt, châtiment convenu en cas de perte du DVD)
- Données des membrers de la famille (nom, date de naissance et degré de favoritisme par les parents)
- Dates de vision des films
- Appréciation des films
Sécurité
Rien de spécial au niveau de la sécurité. On doit pouvoir se connecter (les inscriptions seront effectuées par mes soins directement dans la base de données). Pour pimenter un peu les choses, je souhaite que l'authentification soit requise et que, en fonction de la date de naissance de l'utilisateur, les films non recommandés pour son âge ne soient pas affichés. Bon c'est pour plus tard mais comme ça, si j'oublie, je suis certain que quelqu'un pensera à me le rappeler dans les commentaires !
Pour les rôles, on aura ce qui suit :
- Parents (c'est notre dvdthèque, on a tous les droits)
- Enfants (restrictions au niveau de l'âge)
- Anonyme (accès à la table des membres de la famille uniquement, et pour les colonnes contenant le login et le hash du mot de passe uniquement).
SQL !
Allez, c'est parti pour choisir notre SGBDR ! Je sais bien que certains vont penser que j'ai choisi PostgreSQL uniquement pour pouvoir utiliser PostgREST, mais je vous jure que je suis totalement impartial dans ma décision. On ne va pas se laisser influencer par des théories du complot, non mais oh ! Bon, trêve de plaisanterie, on va enfin pouvoir créer notre base de données !
createdb dvdiz
psql -f dvdiz.sql dvdiz
dvdiz.sql :
BEGIN TRANSACTION;
CREATE SCHEMA dvd;
COMMENT ON SCHEMA dvd IS 'Everything about DVDs. The information contained in
this schema is general and not related to one''s personal taste or
organization';
SET search_path TO 'dvd';
CREATE DOMAIN d_dvd_title VARCHAR(255);
CREATE DOMAIN d_age INT CHECK (value BETWEEN 0 AND 18);
CREATE DOMAIN d_actor_name VARCHAR(100);
CREATE DOMAIN d_language_short CHAR(2);
CREATE DOMAIN d_language_complete VARCHAR(50);
CREATE DOMAIN d_genre_name VARCHAR(50);
CREATE TYPE t_track_type AS ENUM ('sound', 'subtitle');
CREATE TABLE dvd (
id SERIAL NOT NULL PRIMARY KEY,
title d_dvd_title NOT NULL UNIQUE,
minimum_age d_age NOT NULL,
release DATE NOT NULL,
added DATE NOT NULL
);
CREATE TABLE actor (
id SERIAL NOT NULL PRIMARY KEY,
name d_actor_name NOT NULL UNIQUE
);
CREATE TABLE dvd_actor (
dvd_id INT NOT NULL REFERENCES dvd,
actor_id INT NOT NULL REFERENCES actor,
PRIMARY KEY (dvd_id, actor_id)
);
CREATE TABLE language (
id SERIAL NOT NULL PRIMARY KEY,
short d_language_short NOT NULL UNIQUE,
complete d_language_complete NOT NULL UNIQUE
);
CREATE TABLE dvd_language (
dvd_id INT NOT NULL REFERENCES dvd,
language_id INT NOT NULL REFERENCES language,
track_type t_track_type NOT NULL,
PRIMARY KEY (dvd_id, language_id, track_type)
);
CREATE TABLE genre (
id SERIAL NOT NULL PRIMARY KEY,
name d_genre_name NOT NULL UNIQUE
);
CREATE TABLE dvd_genre (
dvd_id INT NOT NULL REFERENCES dvd,
genre_id INT NOT NULL REFERENCES genre,
PRIMARY KEY (dvd_id, genre_id)
);
CREATE SCHEMA home;
COMMENT ON SCHEMA home IS 'Everything related to how DVDs are physically
stored or otherwise managed.';
SET search_path TO 'home';
CREATE DOMAIN d_place VARCHAR(255);
CREATE DOMAIN d_lending_borrower VARCHAR(100);
CREATE TABLE dvd_place (
dvd_id INT NOT NULL REFERENCES dvd.dvd,
place d_place NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
PRIMARY KEY (dvd_id, place)
);
CREATE TABLE lending (
dvd_id INT NOT NULL REFERENCES dvd.dvd,
borrower d_lending_borrower NOT NULL,
lend DATE NOT NULL,
-- We could have added quantity here, but we'll assume we're not a store.
PRIMARY KEY (dvd_id, borrower)
);
CREATE SCHEMA spectator;
COMMENT ON SCHEMA spectator IS 'Everything related to the physical people who
watch DVDs.';
SET search_path TO 'spectator';
CREATE DOMAIN d_rating INT CHECK (value BETWEEN 0 AND 10);
CREATE DOMAIN d_spectator_name VARCHAR(100);
CREATE TYPE t_role AS ENUM ('parent', 'child', 'friend');
CREATE TABLE spectator (
id SERIAL NOT NULL PRIMARY KEY,
name d_spectator_name NOT NULL UNIQUE,
password bytea NOT NULL,
role t_role NOT NULL
);
CREATE TABLE vision (
id SERIAL NOT NULL PRIMARY KEY,
dvd_id INT NOT NULL REFERENCES dvd.dvd,
spectator_id INT NOT NULL REFERENCES spectator,
rating d_rating NOT NULL,
watch DATE NOT NULL DEFAULT CURRENT_DATE
);
COMMIT;
Et maintenant ?
Et voilà. C'est tout pour cette fois. Ce premier article de cette série avait juste pour but de fixer un objectif et le schéma de la base de données. Il manque notamment la définition des rôles et les droits associés, ce qui sera fait dans les deux prochains articles (qui parleront respectivement de mon approche quant à l'attribution des droits pour une telle base de données et de l'utilisation de Row Level Security en PostgreSQL). Nous parlerons ensuite de l'utilisation de PostgREST pour servir une API REST à coût minimum à partir de cette base de données. Finalement, pour faire bonne mesure, nous verrons comment utiliser Java et Spring pour tirer profit de cette interface et créer une application web qui interagit avec la base de données que nous venons de définir.
Pour ne rien manquer sur la création d'une base de données avec PostgreSQL, restez connecté !
