Incomplete version table in feature/criann branch

[vignemail1]

Description du problème

Contexte: version de la branche feature/criann, commit

Différentes pages de la section Administrateur sont indisponibles (remontent des Exceptions) dû à des champs de la DB qui sont manquants voire même de table(s) manquante(s).

Par exemple, la page /gramc3/projet/donnees retourne :

An exception occurred while executing 'SELECT t0.etat_version AS etat_version_1, t0.prj_l_labo AS prj_l_labo_2, t0.prj_titre AS prj_titre_3, t0.dem_heures AS dem_heures_4, t0.attr_heures AS attr_heures_5, t0.politique AS politique_6, t0.prj_sous_thematique AS prj_sous_thematique_7, t0.prj_financement AS prj_financement_8, t0.prj_genci_machines AS prj_genci_machines_9, t0.prj_genci_centre AS prj_genci_centre_10, t0.prj_genci_heures AS prj_genci_heures_11, t0.prj_resume AS prj_resume_12, t0.prj_expose AS prj_expose_13, t0.prj_justif_renouv AS prj_justif_renouv_14, t0.prj_algorithme AS prj_algorithme_15, t0.prj_conception AS prj_conception_16, t0.prj_developpement AS prj_developpement_17, t0.prj_parallelisation AS prj_parallelisation_18, t0.prj_utilisation AS prj_utilisation_19, t0.prj_fiche AS prj_fiche_20, t0.prj_fiche_val AS prj_fiche_val_21, t0.prj_genci_dari AS prj_genci_dari_22, t0.code_nom AS code_nom_23, t0.code_langage AS code_langage_24, t0.code_c AS code_c_25, t0.code_cpp AS code_cpp_26, t0.code_for AS code_for_27, t0.code_autre AS code_autre_28, t0.code_licence AS code_licence_29, t0.code_util_sur_mach AS code_util_sur_mach_30, t0.code_heures_p_job AS code_heures_p_job_31, t0.code_ram_p_coeur AS code_ram_p_coeur_32, t0.gpu AS gpu_33, t0.code_ram_part AS code_ram_part_34, t0.code_eff_paral AS code_eff_paral_35, t0.code_vol_donn_tmp AS code_vol_donn_tmp_36, t0.dem_logiciels AS dem_logiciels_37, t0.dem_bib AS dem_bib_38, t0.dem_post_trait AS dem_post_trait_39, t0.dem_form_maison AS dem_form_maison_40, t0.dem_form_prise AS dem_form_prise_41, t0.dem_form_debogage AS dem_form_debogage_42, t0.dem_form_optimisation AS dem_form_optimisation_43, t0.dem_form_autres AS dem_form_autres_44, t0.dem_form_0 AS dem_form_0_45, t0.dem_form_1 AS dem_form_1_46, t0.dem_form_2 AS dem_form_2_47, t0.dem_form_3 AS dem_form_3_48, t0.dem_form_4 AS dem_form_4_49, t0.dem_form_5 AS dem_form_5_50, t0.dem_form_6 AS dem_form_6_51, t0.dem_form_7 AS dem_form_7_52, t0.dem_form_8 AS dem_form_8_53, t0.dem_form_9 AS dem_form_9_54, t0.dem_form_fortran AS dem_form_fortran_55, t0.dem_form_c AS dem_form_c_56, t0.dem_form_cpp AS dem_form_cpp_57, t0.dem_form_python AS dem_form_python_58, t0.dem_form_mpi AS dem_form_mpi_59, t0.dem_form_openmp AS dem_form_openmp_60, t0.dem_form_openacc AS dem_form_openacc_61, t0.dem_form_paraview AS dem_form_paraview_62, t0.libelle_thematique AS libelle_thematique_63, t0.attr_accept AS attr_accept_64, t0.rap_conf AS rap_conf_65, t0.maj_stamp AS maj_stamp_66, t0.sond_vol_donn_perm AS sond_vol_donn_perm_67, t0.sond_duree_donn_perm AS sond_duree_donn_perm_68, t0.prj_fiche_len AS prj_fiche_len_69, t0.penal_heures AS penal_heures_70, t0.attr_heures_ete AS attr_heures_ete_71, t0.sond_justif_donn_perm AS sond_justif_donn_perm_72, t0.dem_form_autres_autres AS dem_form_autres_autres_73, t0.cgu AS cgu_74, t0.id_version AS id_version_75, t0.data_metadataformat AS data_metadataformat_76, t0.data_tailledatasets AS data_tailledatasets_77, t0.data_nombredatasets AS data_nombredatasets_78, t0.fct_stamp AS fct_stamp_79, t0.maj_ind AS maj_ind_80, t0.prj_id_thematique AS prj_id_thematique_81, t0.prj_id_rattachement AS prj_id_rattachement_82, t0.id_session AS id_session_83, t0.id_projet AS id_projet_84 FROM version t0 WHERE t0.id_session IS NULL’:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 't0.dem_form_0' in 'field list'

Proposition de solutions

1. Mettre à jour le dump SQL qui permet d'initialiser la base (si les modifications ont pour but d'être intégrées dans la branche principale
2. Convertir le dump SQL initial en migrations à dérouler à l'installation et ajouter les migrations complémentaires au gré de l'évolution de l'application

• La première solution a l'avantage de s'appliquer sur une base existante ou nouvelle.
• La deuxième solution est sans doute la plus pérenne dans le temps et permet de transformer une base de donnée existante au fil de l'eau.

Il pourrait également être intéressant d'étudier le cas d'un champ de type JSON pour stocker les données de formulaire dont la structure peut évoluer au gré des besoins (https://mariadb.com/kb/en/json_search/ , https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html#json).

[vignemail1]

La conversion du dump SQL initial en une migration (structure de tables uniquement) peut être réalisée avec la commande suivante dans un projet ayant toutes les tables et champs chargées en base de données.

root@gramc:/var/www/gramc3# ./bin/console doctrine:migrations:dump-schema

 Dumped your schema to a new migration class at "/var/www/gramc3/migrations/Version20210610164732.php"

 To run just this migration for testing purposes, you can use migrations:execute --up 'DoctrineMigrations\\Version20210610164732'

 To revert the migration you can use migrations:execute --down 'DoctrineMigrations\\Version20210610164732'

 To use this as a rollup migration you can use the migrations:rollup command.

[vignemail1]

En utilisant ./bin/console doctrine:migrations:diff et les entities existantes, cela génère une migration qui contient les instructions nécessaires pour rajouter les champs manquants ainsi que la table formation elle aussi manquante.

root@gramc:/var/www/gramc3# ./bin/console doctrine:migrations:diff

 Generated new migration class to "/var/www/gramc3/migrations/Version20210610193644.php"

 To run just this migration for testing purposes, you can use migrations:execute --up 'DoctrineMigrations\\Version20210610193644'

 To revert the migration you can use migrations:execute --down 'DoctrineMigrations\\Version20210610193644'

root@gramc:/var/www/gramc3# ./bin/console doctrine:migrations:execute --up 'DoctrineMigrations\Version20210610193644'

 WARNING! You are about to execute a database migration that could result in schema changes and data loss. Are you sure you wish to continue? (yes/no) [yes]:
 > yes

[notice] Executing DoctrineMigrations\Version20210610193644 up
[notice] finished in 972.4ms, used 18M memory, 1 migrations executed, 4 sql queries

migration générée :

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
final class Version20210610193644 extends AbstractMigration
{
    public function getDescription() : string
    {
        return '';
    }

    public function up(Schema $schema) : void
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE TABLE formation (id INT AUTO_INCREMENT NOT NULL, numero_form INT DEFAULT NULL, acro_form VARCHAR(15) DEFAULT NULL, nom_form VARCHAR(100) DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');
        $this->addSql('DROP TABLE consommation');
        $this->addSql('ALTER TABLE user ADD cpassword VARCHAR(200) DEFAULT NULL');
        $this->addSql('ALTER TABLE version ADD dem_form_0 INT DEFAULT NULL, ADD dem_form_1 INT DEFAULT NULL, ADD dem_form_2 INT DEFAULT NULL, ADD dem_form_3 INT DEFAULT NULL, ADD dem_form_4 INT DEFAULT NULL, ADD dem_form_5 INT DEFAULT NULL, ADD dem_form_6 INT DEFAULT NULL, ADD dem_form_7 INT DEFAULT NULL, ADD dem_form_8 INT DEFAULT NULL, ADD dem_form_9 INT DEFAULT NULL');
    }

    public function down(Schema $schema) : void
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->addSql('CREATE TABLE consommation (id INT AUTO_INCREMENT NOT NULL, id_projet VARCHAR(6) CHARACTER SET utf8 NOT NULL COLLATE `utf8_unicode_ci`, annee INT NOT NULL, limite INT NOT NULL, m01 INT NOT NULL, m02 INT NOT NULL, m03 INT NOT NULL, m04 INT NOT NULL, m05 INT NOT NULL, m06 INT NOT NULL, m07 INT NOT NULL, m08 INT NOT NULL, m09 INT NOT NULL, m10 INT NOT NULL, m11 INT NOT NULL, m12 INT NOT NULL, INDEX annee_2 (annee), INDEX id_projet_2 (id_projet), INDEX id_projet (id_projet), INDEX annee (annee), UNIQUE INDEX id_projet_3 (id_projet, annee), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB COMMENT = \'\' ');
        $this->addSql('DROP TABLE formation');
        $this->addSql('ALTER TABLE user DROP cpassword');
        $this->addSql('ALTER TABLE version DROP dem_form_0, DROP dem_form_1, DROP dem_form_2, DROP dem_form_3, DROP dem_form_4, DROP dem_form_5, DROP dem_form_6, DROP dem_form_7, DROP dem_form_8, DROP dem_form_9');
    }
}

[calmip]

Nous utilisons les doctrine/data-fixtures pour faire évoluer la base de données. Pour installer une base de données, que ce soit une base exportée depuis la prod ou une base, on doit exécuter le script reload-db dans le répertoire reprise