Projet INSEE -- base de données avancée --------------------------------------- Ce projet est disponible sur notre serveur git [à cette adresse](https://git.epha.se/ephase/projet_INSEE). Le projet va continuer à vivre après le rendu. En effet nos obligations professionnelles[^note] ont joué contre nous et on a manqué de temps malgrès notre travail acahné pour produire des élements de qualité. [^note]:notamment une semaine d'astreinte ou il y a eu des interventions 3 nuits de rang ## Installation Il est vivement conseiller de faire fonctionner ce projet dans un **environnement virtuel python** : 1. cloner le dépôt de code suivant: ``` git clone https://git.epha.se/ephase/projet_INSEE ``` 2. créer l'environnement virtuel: ``` cd projet_INSEE python -m venv .venv source .venv/bin/activate ``` 3. intaller les prérequis: ``` pip install -r requirements.txt ``` L'installation est maintenant opérationnelle. Il faut penser à activer l'environnement virtuel à chaque nouvelle ouverture d'un terminal. ### ATTENTION! L'ensemble des script présent ici nécessitent **IMPÉRATIVEMENT** Python3. Il est parfois nécessaire d'adapter les commandes ci-dessus en fonction de votre configuration. Au CREMI par exemple, la création de l'environnement virtuel doit se faire aevc la commande suivante: ``` python3 -m venv .venv ``` ## Les éléments du projet L'ensemble des scripts présents dans ce dépôt permettent l'affichage d'informations supplémentaires via des paramètres de la ligne de commandes: * *messages d'informations* via l'option -V. Ces informations renseignent l'utilisateurs sur les temps d'exécutions des différentes parties de chacun des scripts. * *message de debug* via l'option `--debug`. Affiche des iunformations détaillées sur les actions et résultats des scripts. ## Consolider les fichiers bruts C'est le script `csvprocess.py` qui se charge de l'analyse des fichiers `csv` bruts et de la génération des fichiers `csv` consolidés. Ce script accepte plusieurs arguments qu'il est possible de visualiser avec l'aide en ligne intégrée: ```sh ./csvprocess -help ``` ## Créer la base de donnée C'est le script `createdatabase.py` qui se charge de la création de la base de données et de l'importation des fichiers `csv` consolidés créés par le précédent script. La création des éléments se fait dans un nouveau schéma, il se nomme par défaut `insee` mais peut être changé via le paramètre `--schema-name`. Une aide en ligne est aussi disponible pour les détails des options disponibles. ```sh ./createdatabase.py --help ``` ### Nom dynamique de schéma et injection SQL La definition dynamique du nom du schema nous oblige à utiliser la fonction `AsIs`. Cette fonction ne permet pas à `cur.execute()` la mise en place de sécuritées afin de prévenir les injections SQL. Lors de la définition de la valeur de ce paramètre, nous le vérifions avec la fonction `check_schema_name()`. Afin de définir le nombre maximum de caractères composant le nom d'un schéma, nous avons vérifié dans le code source de Postgre : il est de 63 caractètes (fichier pg_config_manual.h). Cette vérification nous permet donc d'éviter le problème des injections et est utilisée dans chacun des scripts accèdant à la base. ### Paramètres de connexion à la base Un fichier de configuration avec les paramètres de connection à la base PostgreSQL est nécessaire . Ce fichier prend la forme suivante: ``` host= user= password= ``` Les valeurs sont à remplacer par les votres. Par défaut les scripts qui nécessite une connexion à la base utilisent le fichier `.pgconn` dans le répertoire courant. Il est possible de le changer avec le paramètre `-f` (version longe `--connexion-file`) de chacun des scripts. ## Question 1: mettre en place des requêtes Le script `get_state_statistics` permet d'afficher des informations sur une région en particulier. Il suffit de lui passer via l'option `-t` (ou `--state` en version longue) la région désirée. Il est aussi possible de lui demander une année en particulier pour l'affichage des informations demographiques viam l'option `--year` comme pour l'exemple ci-dessous: ``` ./get_states_statistics.py --state 'Nouvelle-Aquitaine' --year 1999 ``` ## Question 2: ajouter des vues C'est le script `create_view.py` qui se charge de la création des deux vues. Il est possible de tester les vues créer en les interrogeant avec l'option `-t` (version longue `--test`): ``` ./create_view.py -t ``` ## Question 3: création d'une procédure Le script `create_procedure.py` permet de créer les éléments nécessaire à l'exécution de la procédure stockée demandée: les colonnes dans les tables région et département et la procédure. Avec l'option `-c` (version longe `--call`) le script applique manuellement la procédure sur la base. ``` ./create_procedure.py --call ``` ## Question 4: création d'un Trigger Le script `create_trigger.py` permet de mettre en place plusieurs chose: * Le blocage de ma modification des éléments des tables *département* et *région* * Le calcul de la population totale d'un département puis d'une région et la modification de la colonne dans ces même tables. Pour la modification des colonnes, nous désactivons les *triggers* empêchant la modification de ces tables le temps de la modification puis les réactivons. L'utilisation du script est simple: ``` ./create_trigger.py ``` ## Question 5: Trigger avancés Le script `create_advanced_trigger.py` permet de répondre à la question 6. Il permet de créer une seconde procédure pour mettre à jour les colonnes *population* de nos tables *departement* et *région* en fonction des informations de la table *commune*. L'utilisation du script est simple: ``` ./create_advanced_trigger.py ``` Vous remarquerez que nous acvons créé une nouvelle fonction `fun_mag_pop_2` et `prc_pop_reg_dep_2` au lieu de remplacer les anciennent. Ce n;est pas optimisé mais le maue de temps nous a poussé à laisser tel quel. ## Question 6: EXPLAIN Afin d'étudier le fonctionnement de l;ordonnanceur de PostGreSQL, nous avons exécuter trois requêtes de taille différente que se soit en terme de jointures ou de nombre de ligne retournées. ```sql -- petite requête : une jointure explain SELECT ID_REGION, r.REG, r.NCC,d.DEP FROM INSEE.REGION r INNER JOIN INSEE.DEPARTEMENT d ON d.REG = r.REG WHERE r.NCC = 'BRETAGNE'; ``` ``` QUERY PLAN Hash Join (cost=1.24..6.58 rows=6 width=23) Hash Cond: ((d.reg)::text = (r.reg)::text) -> Seq Scan on departement d (cost=0.00..5.01 rows=101 width=6) -> Hash (cost=1.23..1.23 rows=1 width=20) -> Seq Scan on region r (cost=0.00..1.23 rows=1 width=20) Filter: ((ncc)::text = 'BRETAGNE'::text) ``` ```sql -- requête moyenne : deux jointures explain SELECT ID_REGION, r.REG, r.NCC FROM INSEE.REGION r INNER JOIN INSEE.DEPARTEMENT d ON d.REG = r.REG INNER JOIN INSEE.COMMUNE c ON c.DEP = d.DEP GROUP BY ID_REGION,r.REG, r.NCC; ``` ``` QUERY PLAN HashAggregate (cost=990.76..990.94 rows=18 width=20) Group Key: r.id_region -> Hash Join (cost=7.68..903.35 rows=34965 width=20) Hash Cond: ((d.reg)::text = (r.reg)::text) -> Hash Join (cost=6.27..788.55 rows=34965 width=3) Hash Cond: ((c.dep)::text = (d.dep)::text) -> Seq Scan on commune c (cost=0.00..686.65 rows=34965 width=3) -> Hash (cost=5.01..5.01 rows=101 width=6) -> Seq Scan on departement d (cost=0.00..5.01 rows=101 width=6) -> Hash (cost=1.18..1.18 rows=18 width=20) -> Seq Scan on region r (cost=0.00..1.18 rows=18 width=20) ``` ```sql -- grosse : trois jointures avec la table STATISTIQUES qui contient beaucoup de données explain SELECT ID_REGION, r.REG, r.NCC, s.ID_INDICATEUR, s.DATE_DEBUT, SUM(s.VALEUR) FROM INSEE.REGION r INNER JOIN INSEE.DEPARTEMENT d ON d.REG = r.REG INNER JOIN INSEE.COMMUNE c ON c.DEP = d.DEP INNER JOIN INSEE.STATISTIQUE s ON s.COM = c.COM GROUP BY ID_REGION,r.REG, s.DATE_DEBUT, r.NCC, s.ID_INDICATEUR ORDER BY ID_INDICATEUR; ``` ``` QUERY PLAN Finalize GroupAggregate (cost=26461.68..26660.78 rows=756 width=34) Group Key: s.id_indicateur, r.id_region, s.date_debut -> Gather Merge (cost=26461.68..26638.10 rows=1512 width=34) Workers Planned: 2 -> Sort (cost=25461.66..25463.55 rows=756 width=34) Sort Key: s.id_indicateur, r.id_region, s.date_debut -> Partial HashAggregate (cost=25417.95..25425.51 rows=756 width=34) Group Key: s.id_indicateur, r.id_region, s.date_debut -> Hash Join (cost=1131.39..19884.52 rows=553343 width=34) Hash Cond: ((d.reg)::text = (r.reg)::text) -> Hash Join (cost=1129.99..18088.60 rows=553343 width=17) Hash Cond: ((c.dep)::text = (d.dep)::text) -> Hash Join (cost=1123.71..16568.85 rows=553343 width=17) Hash Cond: ((s.com)::text = (c.com)::text) -> Parallel Seq Scan on statistique s (cost=0.00..13992.43 rows=553343 width=20) -> Hash (cost=686.65..686.65 rows=34965 width=9) -> Seq Scan on commune c (cost=0.00..686.65 rows=34965 width=9) -> Hash (cost=5.01..5.01 rows=101 width=6) -> Seq Scan on departement d (cost=0.00..5.01 rows=101 width=6) -> Hash (cost=1.18..1.18 rows=18 width=20) -> Seq Scan on region r (cost=0.00..1.18 rows=18 width=20) ``` * Plus le nombre de jointure est importante et plus la requête va être longue à s'exécuter et coûteuse. C'est même la première source de consommation de ressource pour des jointures impliquant le plus de lignes dans nos exemples. * Le nombre de ligne dans les tables jouent également sur le temps d’exécution et le coût qu'elle génère. * Il faut prioriser de petites requête en évitant des jointure inutile en utilisant un EXISTS par exemple.