Effacement en cascade de données

Version imprimable

14/01/2023, 22h00
ddaweb

1 pièce(s) jointe(s)

Effacement en cascade de données

Bonsoir,

Suite à la lecture du topic https://www.developpez.net/forums/d2...lle-existants/ , j'ai un peu cherché à automatiser des effacements via MySQL directement.
Une de mes recherches m'a amenée à une petite vidéo qui fait en partie ce que je souhaite faire :

https://www.youtube.com/watch?v=cd-68RrXo-k

Elle parle de valeurs identiques, dans mon cas ce n'est pas le cas :oops:

Pour vous situer un peu les tables que je compte utiliser (en cours de peaufinent, mais les champs à effacer seront là) : Pièce jointe 632509

En fait, je profite d'une refonte de notre outil de gestion pour améliorer le fonctionnement et surtout mieux appliquer le RGPD des données de nos membres.
Le but est d'effacer tous les enregistrements des nouvelles tables qui sont rosé (table 02_membre_rgpd -> mbr_rgpd_mbr_id) lorsque j'initialise l'effacement RGPD du membre : mon soucis est d'y adjoindre la partie 03_chien_xxx et 04_cours_reservation qui ont l'ID du chien et pas celui du membre.

L'ordre de création des membres et chien via des formulaires :
1. Le membre est ajouté dans 02_membre -> je récupère le dernier enregistrement
2. La partie RGPD est créée dans 02_membre_rgpd
3. Le chien est créé dans 03_chien -> je récupère le dernier enregistrement
4. La partie RGPD est créée dan 03_chien_rgpd
5. J'ajoute éventuellement des données dans les autres tables pour le membre ou le chien

Il y a d'autres tables qui gravitent autour pour des choix dont par ex. : les codes postaux, les vétérinaires, les races, les compagnies RC familiale, ...
Pour plusieurs j'ai choisi de mettre le nom au lieu de l'ID par facilité dans mes pages et comme c'est pour la majorité des données RGPD, ce n'est pas gourmand en taille vu les effacements : il y a presque 2.900 membres dans la DB, mais seulement +/- 250 actifs avec données RGPD et 3.300 chiens, mais seuls les chiens des membres actifs avec données RGPD.

La table 04_cours_reservation a son origine durant la crise du Covid où il fallait réserver les cours avec des critères édictés par notre gouvernement, elle a ensuite été détournée pour des réservations de cours seulement (je garde le champs "reservation_nom" au cas où le Covid devrait revenir et ouvrir les réservations au non membre -> ils mettent leur nom ... sera vide actuellement).

A titre d'information, mes tables sont/groupe :
00_xxxx : la comptabilité
01_xxxx : les données pour l'asbl (code comptabilité, taux de TVA, indemnités, etc...)
02_xxxx : les membres
03_xxxx : les chiens
04_xxxx : les cours
05_xxxx : les droits d'accès dans l'interface
etc ...

J'espère que c'est assez clair.

Merci pour votre aide

NB : je code en mode procédural et pas PDO malheureusement
14/01/2023, 22h32
mathieu

la discussion que vous citez montre où rajouter des règles "on delete", est ce que vous avez essayez cela ?
https://www.developpez.net/forums/d2.../#post11910122
14/01/2023, 23h40
ddaweb

Oui, c'est en effet "on delete" que je compte utiliser : dois-je utiliser "cascade" comme dans la vidéo ?

Dans plusieurs tables, je n'ai pas l'ID du membre que je dois effacer (bien l'ID du chien, mais la table chien a bien l'ID membre) ... dois-je l'ajouter dans ces tables (données redondantes) ? ou faire une liaison, mais comment ?
Ajouter l'ID membre n'est pas trop difficile à faire, il est en session dès que j'ai créé un membre ou travaille sur un membre.

C'est quelque chose de nouveau pour moi, je l'ai toujours tout fait manuellement via des requêtes, il est clair que si MySQL sait le faire pour moi, c'est mieux et certainement moins d'erreurs quand c'est bien mis en place :lol:
15/01/2023, 00h03
mathieu

les champs qui sont en auto-incrément sont appelés des clés primaire, ce sont des identifiants uniques.
ensuite les autres champs qui utilisent ces identifiants s'appellent des clés étrangères comme par exemple le champ "chien_mbr_id" dans la table 03_chien.

en ajoutant une contrainte "on delete cascade", ça veut dire que si le membre est supprimé, cela supprimera aussi toutes les entrées correspondantes dans la table 03_chien. il faut donc rajouter cette contrainte à la table 03_chien et à toutes les tables qui utilisent des clés étrangères.

à l'inverse si vous voulez gérer les suppression dans le code, vous pouvez mettre "on delete restrict" et au moment de supprimer un membre, la table chien va dire "non non, il y a encore des chiens associés à ce membre" et donc le membre ne sera pas supprimé et la requete de suppression retournera une erreur.
vous pouvez voir la documentation des contraintes là :
https://dev.mysql.com/doc/refman/8.0...eign-keys.html
15/01/2023, 00h24
ddaweb

Merci pour ces informations.

Les enregistrements des tables 02_membre et 03_chien ne sont jamais effacées, uniquement dans les autres tables !
Pourquoi les garder :
- Un membre a son n° à vie et peut revenir avec un nouveau chien ... cela arrive régulièrement et on garde l'historique du passé par la comptabilité
- Garder le chien nous permet de détecter s'il revient avec une autre personne -> on regarde alors à qui il appartient chez DogID qui est sensé avoir tous les chiens identifiés (Puce) ... éventuellement un chien volé ?

Je suis occupé de créer toutes les tables dans une nouvelle BDD et ferai un script qui écrira toutes les données des tables actuelles dans celles-ci.
Dès que c'est fait, je vais faire les essais et reviendrai certainement pour l'un ou l'autre problème :roll:

J'espère arriver à faire les effacements en cascade dans les tables ad hoc.
S'il y a encore des points qui doivent attirer mon attention, n'hésitez pas à les dire :D
15/01/2023, 20h48
ddaweb

1 pièce(s) jointe(s)

Voilà, sans avoir beaucoup de connaissance en MySQL, avec les indication de Mathieu + des recherches, je suis parvenu à quelques chose que je dois encore tester :

Pièce jointe 632544

Je me suis assez vite rendu compte que je n'arrivais pas à programmer les foreign key sans qu'il y ai un champs avec le n° membre dans toutes les tables.
Ensuite j'ai remarquer qu'il fallait créer un index partout où le N° membre n'était pas primaire afin de créer les key.
Après pas mal d'essais et de recherches le résultat ci-dessus ... bien, maintenant il va falloir remplir les tables pour tester :lol:

J'écris donc une query qui split la table actuelle 02_membre en 2 nouvelles tables 02_membre et 02_membre_rgpd dans une autre BDD ... un premier soucis avec le message d'erreur :

Citation:

V-.VCannot add or update a child row: a foreign key constraint fails (`table_test`.`02_membre_rgpd`, CONSTRAINT `fk_chien_rgpd` FOREIGN KEY (`mbr_rgpd_mbr_id`) REFERENCES `03_chien_rgpd` (`chien_rgpd_mbr_id`) ON DELETE CASCADE)

Après une longue recherche, j'ajoute une ligne de code juste avant la query :

Code:

mysqli_query($conn_test,"SET FOREIGN_KEY_CHECKS=0;"); // ----- Evite le blocage des "foreign key"

Il est à noter qu'il est impossible d'ajouter directement des données dans une des tables avec cette relation, il y a le même message d'erreur :furax:

Toutes les tables n'ont pas tous les N° membres !

Étape suivante, je rempli d'autres tables et j’efface un N° membre de la table 02_membre_rgpd et vérifie si ce N° de membre disparait des autres tables ... ce que je cherche à faire.

Je détaille un peu pour les autres personnes qui seraient novice comme moi et veulent faire la même chose ;)

EDIT : il faut que tous les champs avec le N° membre soient du même type sinon il y a un message d'erreur très explicite qui m'a nécéssité un petite recherche :marteau:
16/01/2023, 16h48
ddaweb

1 pièce(s) jointe(s)

J'ai importer les données des membres, des chiens et carnet ... dans phpMyAdmin, je retrouve bien les liens :)

Dans la console SQL j'ai effacé un membre dans 02_membre_rgpd pour tester la cascade :

Code:

DELETE FROM 02_membre_rgpd WHERE mbr_rgpd_mbr_id=2;

Dans cette table c'est bien effacé, mais la cascade n'a pas lieue :calim2:
Il reste ce membre dans 03_chien_rgpd et dans 03_chien_carnet malgré les foreign key.

Voici comment je les ai programmées :
Pièce jointe 632582

Quelqu'un a une idée du pourquoi ?
18/01/2023, 13h15
escartefigue

Bonjour,

Pouvez-vous communiquer le DDL complet des différentes tables en jeu, c'est à dire le script contenant les ordre CREATE TABLE
18/01/2023, 20h14
ddaweb

1 pièce(s) jointe(s)

Bonsoir,

Oui bien sur, j'ai vidé les tables vu les données de nos membres présentes et fait un export de la BDD avec ajout "create database" (je suppose que c'est cela le DDL).
Je joins le fichier SQL.

Je vais maintenant y jeter un coup d’œil car j'ai tout fait via phpMyAdmin : je n'ai pas songé de regarder comme cela.
C'est la première fois que j'utilise les foreign key (je suis très basique en BDD, tout via query manuelles), mais j'y vois un grand intérêt pour une BDD plus correcte.

Merci pour votre intérêt à ma demande.
18/01/2023, 21h07
ddaweb

1 pièce(s) jointe(s)

Une petite explication de comment les données sont introduites :

1. On crée le membre, donc dans les tables 02_membre et 02_membre_rgpd
2. On ajoute au membre 1 ou plusieurs chiens, donc dans les tables 03_chien et 03_chien_rgpd
3. Suivant les cas, on ajoute dans les autres tables :
- Si un chien fait des concours, on ajoute son ou ses carnets de travail : 03_chien_carnet
- Si le membre deviens instructeur (moniteur) : ajout dans la table 04_cours_moniteur (il peut l'être dans plusieurs sections)
- Si le membre a une fonction au sein de l'asbl et qu'il doit utiliser l'interface, on lui donne les droits qui lui incombe : 05_droits_membre (1 entrée / membre)

J'ai une query qui liste les membres qui ne sont plus en ordre de cotisation depuis >3 mois (action manuelle) : on efface donc toutes les données RGPD.
Une autre query liste les membre qui ne sont plus en ordre de cotisation >=3semaines et <=3mois (action manuelle également) : le membre devient externe, les abonnements sont mis à 0, le moniteur et les droits ne sont plus actifs

Si un membre n'est plus moniteur -> dans cette table le status change par "mon_actif" = non
Si un membre n'a plus accès à notre interface : "droits_actif" = non
C'est parfois une situation momentanée, c'est pour cela que j'ai opté pour le définitif lors de l'effacement RGPD

J'ai un exemple d'un membre avec reset RGPD ... si toutes les suppressions (liste de plusieurs membres) : cocher la case et valider, un par un : via le petit v en dessous de actif

Pièce jointe 632730
C'est là que l'effacement automatique des tables sera une grande avancée pour moi :)
19/01/2023, 03h59
mathieu

Citation:

Envoyé par ddaweb

Je joins le fichier SQL.

si on regarde les contraintes de ce fichier, les entrées dans 03_chien_rgpd ne sont pas liées à 02_membre_rgpd.
si on remonte la chaine des contraintes on a 03_chien_rgpd -> 03_chien_carnet -> 04_cours_moniteur -> 05_droits_membre donc pas de trace de 02_membre_rgpd.

J'avoue un peu nager dans les contraintes, dans le fichier sql :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
--
-- Contraintes pour la table `02_membre_rgpd`
--
ALTER TABLE `02_membre_rgpd`
  ADD CONSTRAINT `fk_chien_rgpd` FOREIGN KEY (`mbr_rgpd_mbr_id`) REFERENCES `03_chien_rgpd` (`chien_rgpd_mbr_id`) ON DELETE CASCADE;
 
--
-- Contraintes pour la table `03_chien_carnet`
--
ALTER TABLE `03_chien_carnet`
  ADD CONSTRAINT `fk_chien_carnet_moniteur` FOREIGN KEY (`carnet_mbr_id`) REFERENCES `04_cours_moniteur` (`mon_mbr_id`) ON DELETE CASCADE;
 
--
-- Contraintes pour la table `03_chien_rgpd`
--
ALTER TABLE `03_chien_rgpd`
  ADD CONSTRAINT `fk_chien_rgpd_carnet` FOREIGN KEY (`chien_rgpd_mbr_id`) REFERENCES `03_chien_carnet` (`carnet_mbr_id`) ON DELETE CASCADE;
 
--
-- Contraintes pour la table `04_cours_moniteur`
--
ALTER TABLE `04_cours_moniteur`
  ADD CONSTRAINT `fk_mon_droits` FOREIGN KEY (`mon_mbr_id`) REFERENCES `05_droits_membre` (`droits_mbr_id`) ON DELETE CASCADE;
COMMIT;

Il me semble que j'ai bien de 02_membre_rgpd -> 03_chien_rgpd : lignes 4-5 ci-dessus ... ou alors je n'ai vraiment rien compris, ce qui est possible :mouarf:
Les contraintes semblent être triées par ordre alphabétique sur la table de départ (lignes 193 -> 216) :)

19/01/2023, 21h37
ddaweb

1 pièce(s) jointe(s)

Je viens de découvrir "Adminer" dans mon serveur Wamp : version simple en PHP de phpMyAdmin.
Il semble montrer dans son schéma de la base de données que les foreign key sont correctes :

Pièce jointe 632788

Demain je referai un test car là je ne comprend plus :weird:

Je vais également devoir mettre MariaDB au lieu de Mysql vu que mon hébergement est en MariaDB :whistle:

Une question que je me pose : les champs "carnet_id" et "mon_id" sont-ils vraiment nécessaire ?
20/01/2023, 17h21
ddaweb

1 pièce(s) jointe(s)

Je viens de retester, cela ne fonctionne toujours pas :oops:

Est-ce possible que cela provienne de données incohérentes ?

J'ai trouvé une situation qui devrait être impossible dans la table 03_chien_rgpd (et 03_chien) : un chien qui a le membre 0 qui n'existe pas avec l'auto incrément du numéro de membre !!

Pièce jointe 632849

Des vieilles données, 2014 et 2015, ces chiens n'ont jamais suivit de cours en plus, je ne sais donc pas retrouver le N° membre :? ... je dois avoir un membre fourre-tout quand le secrétariat fait du n'importe quoi :mouarf:

Si c'est bien cela, je devrai d'abord vérifier l'intégrité des données avant toute chose ...
26/01/2023, 11h13
escartefigue

Bonjour,

La seule façon de bien modéliser une base de données est de commencer par établir le Modèle Conceptuel de Données (MCD).

Pour pouvoir établir ce MCD, il faut connaître les règles de gestion, c'est à dire les associations entre types d'entité (les acteurs de votre univers, comme les membres, les vétérinaires, les chiens...).
Exemples de règles :
R001 : un membre possède zéro à plusieurs chiens
R002 : un chien est possédé par un et un seul membre
R003 : un chien suit un à plusieurs examens
etc.

Avec tout bon logiciel de modélisation, la génération des tables et des contraintes se fait en un clic une fois le MCD établi.
Il n'y a ainsi aucune difficulté à avoir des contraintes exhaustives et fiables et ainsi éviter les problèmes que vous rencontrez actuellement.
Et commencer par le MCD évite bien d'autres écueils, vos tables sont très mal définies.
Un modèle de données mal conçu est la source de requêtes complexes et contre performantes et surtout de redondances et de données non fiabilisées.

L'un des défauts majeurs de MySQL est d'ailleurs de proposer MySQL workbench dans lequel on ne peut pas réaliser de MCD on attaque directement en créant les tables, source de bien des modèles complètement bancals.

Vous pouvez télécharger un logiciel gratuit de modélisation, par exemple l'excellent Looping accessible ICI .

Vous pouvez vous faire aider dans la modélisation de vos données en posant vos questions dans le forum consacré à ce sujet et qui se trouve ICI
26/01/2023, 21h15
ddaweb

Merci pour ce conseil escartefigue, je l'ai téléchargé depuis un autre topic ;)

Concernant mon problème, je devines que cela provient de mon code : je n'ai certainement pas bien compris comment fonctionnent les Foreign Key et qu'elles sont donc mal écrites.
Quelqu'un a une idée de mon erreur ?

J'ai entre-temps rectifié les données inconsistantes.