102 lines
5.4 KiB
SQL
102 lines
5.4 KiB
SQL
-- =============================================================================
|
|
-- init.sql — Script de création du schéma GestHub (MariaDB / InnoDB)
|
|
-- =============================================================================
|
|
-- Respecte la 3ème forme normale (3NF) : aucune donnée redondante, toutes les
|
|
-- dépendances transitives éliminées. La référence aux utilisateurs se fait
|
|
-- uniquement via `sub` (identifiant OIDC Keycloak), sans duplication des
|
|
-- données du fournisseur d'identité (Bloc 2 - C4).
|
|
--
|
|
-- Moteur InnoDB : transactions ACID, contraintes de clé étrangère.
|
|
-- Exécuté automatiquement au premier démarrage du conteneur MariaDB
|
|
-- (monté sur /docker-entrypoint-initdb.d/init.sql, voir docker-compose.yml).
|
|
-- =============================================================================
|
|
|
|
SET NAMES utf8mb4;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- Table : annonces
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS annonces (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
titre VARCHAR(150) NOT NULL,
|
|
contenu TEXT NOT NULL,
|
|
epinglee TINYINT(1) NOT NULL DEFAULT 0,
|
|
auteur_sub VARCHAR(64) NOT NULL, -- sub OIDC Keycloak (pas de FK vers Keycloak)
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
INDEX idx_annonces_auteur (auteur_sub),
|
|
INDEX idx_annonces_created (created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- Table : fichiers
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS fichiers (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
nom_original VARCHAR(255) NOT NULL,
|
|
nom_stocke VARCHAR(64) NOT NULL UNIQUE, -- nom UUID sur disque (anti path traversal)
|
|
mime_type VARCHAR(127) NOT NULL,
|
|
taille_octets BIGINT UNSIGNED NOT NULL, -- BIGINT : fichiers volumineux possibles
|
|
uploader_sub VARCHAR(64) NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_fichiers_uploader (uploader_sub)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- Table : evenements (module planning)
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS evenements (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
titre VARCHAR(150) NOT NULL,
|
|
description TEXT NULL,
|
|
date_debut DATETIME NOT NULL,
|
|
date_fin DATETIME NOT NULL,
|
|
createur_sub VARCHAR(64) NOT NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT chk_evenements_dates CHECK (date_fin >= date_debut),
|
|
INDEX idx_evenements_dates (date_debut, date_fin)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- Table : blocks (widgets du tableau de bord — module historique, conservé)
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS blocks (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
block_type VARCHAR(50) NOT NULL, -- 'iframe' | 'buttons' | 'html'
|
|
column_name VARCHAR(20) NOT NULL, -- 'left' | 'center' | 'right'
|
|
position INT UNSIGNED NOT NULL DEFAULT 0,
|
|
data TEXT NULL, -- JSON sérialisé (url, titre, liens...)
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- Table : audit_log (traçabilité des actions sensibles — Bloc 1 - C10)
|
|
-- -----------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
action VARCHAR(50) NOT NULL, -- CREATE_ANNONCE, DELETE_FILE, LOGIN, LOGOUT, REFUSE...
|
|
ressource VARCHAR(150) NULL, -- ex. 'annonce:42', 'fichier:uuid'
|
|
statut VARCHAR(20) NOT NULL, -- SUCCESS | REFUSED | ERROR
|
|
utilisateur_sub VARCHAR(64) NULL,
|
|
ip_address VARCHAR(45) NULL, -- IPv4 ou IPv6
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_audit_action (action),
|
|
INDEX idx_audit_created (created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- -----------------------------------------------------------------------------
|
|
-- Projection méthodologique (non implémentée) : trigger de traçabilité des
|
|
-- modifications directes en base, hors application (voir Bloc 1 - C10) :
|
|
--
|
|
-- DELIMITER $$
|
|
-- CREATE TRIGGER trg_annonces_after_update
|
|
-- AFTER UPDATE ON annonces
|
|
-- FOR EACH ROW
|
|
-- BEGIN
|
|
-- INSERT INTO audit_log (action, ressource, statut, utilisateur_sub)
|
|
-- VALUES ('DIRECT_UPDATE', CONCAT('annonce:', NEW.id), 'SUCCESS', NULL);
|
|
-- END$$
|
|
-- DELIMITER ;
|
|
-- -----------------------------------------------------------------------------
|