Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Membre expert
    Prévoir les requêtes SQL et ne les exécuter que si pas d'erreurs (V2)
    Bonsoir,

    le titre de la précédente discussion convient encore mais je préfère en créer une nouvelle plutôt que de réactiver l'autre.

    Le contexte est le même : je lis un fichier CSV (donc N enregistrements) et à chaque ligne, j'alimente un tableau des requêtes SQL (INSERT ou UPDATE) à exécuter mais j'attends la fin pour les exécuter car je ne veux le faire que si aucune erreur n'a été trouvée (ne pas polluer la bdd avec des données erronées). Le critère pour décider entre un INSERT ou UPDATE est si la clé primaire est déjà présente en bdd ou non (je pense).
    Pour être plus clair dans la suite, je vais préciser que l'une des colonnes du CSV est "applicationname" et que j'ai une table application dans laquelle je vais mettre toutes les applications.
    Code sql :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE `application` (
      `application_key` smallint NOT NULL AUTO_INCREMENT,
      `application_name` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
      `platform` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
      `platform_owner` bigint DEFAULT NULL,
      `publisher` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
      PRIMARY KEY (`application_key`),
      UNIQUE KEY `UK_appli_name` (`application_name`) 
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

    Supposons qu'au départ, la bdd est vide. A la première ligne lue du CSV, je vais donc décider de faire un INSERT d'une nouvelle application. Supposons aussi que chaque ligne du CSV porte sur la même application. A la 2e ligne lue, je vais donc retrouver la même application. Mais comme elle n'est pas encore en bdd, je vais de nouveau décider de faire un INSERT. Donc si les N enregistrements du CSV portent sur la même application, ça fera N INSERT identiques. A moins, je pense de faire des INSERT IGNORE. Cela permettra de faire un seul INSERT. Mais je souhaite aussi compter le nombre de INSERT (et de UPDATE). Quand je vais exécuter le INSERT IGNORE, aurai-je l'information s'il y a eu INSERT ou non ?
    Il vaut mieux viser la perfection et la manquer que viser l'imperfection et l'atteindre. - Bertrand Russell

  2. #2
    Expert éminent sénior
    Bonjour,
    Tu peux faire un insert into... on duplicate key update. Ainsi si tu comptes les inset/update via un trigger, ça fonctionne.

    Sinon tu peux aussi le faire côté application. JE ne connais pas le langage utilisé, il faudra peut-être faire des adaptation.
    Tu commences par déclarer une structure/une classe avec un id (celui de l'application), et deux entiers pour avoir le nombre d'insert et d'update.
    A chaque lecture d'une ligne, tu cherches dans un tableau/une liste si l'id est présent. Si c'est le cas, il faut faire un update.
    Sinon tu cherches dans la base, et si la ligne existe tu fais aussi un update, et tu ajoutes l'id dans la liste/tableau. Sinon tu fais un insert.
    Et à chaque fois tu mets à jour les compteurs.
    Ainsi tu ne fais qu'une recherche dans la base par id, tu as les bonnes requêtes et tu peux compter les insert/update (par id, au total...).
    Tout ceci étant d'ailleurs compatible avec un comptage comme indiqué dans la première hypothèse.

    Tatayo.

  3. #3
    Membre expert
    Merci pour ta réponse.

    Je retiens l'idée du tableau avec les ID.

    Raison : je ne sais pas pourquoi, mais mon "client", qui connait le SQL, ne veut pas de insert...on duplicate key update. On se voit demain, et je lui demanderai pourquoi.

    Sinon, la classe existe déjà (CSVimport) et elle a déjà 2 propriétés nb_insert et nb_update !

    Le langage que j'utilise est le PHP.

    Le CSV comporte 24 colonnes et je répartie les données sur 5 tables. L'information "application" serait suffisante pour la table application mais insuffisante pour les autres : en effet, dans le CSV, il peut y avoir plusieurs lignes qui portent sur la même application. Il va donc falloir que je rajoute d'autres données.

    La recherche d'un ID, je la ferai en effet dans le tableau et dans la bdd : en effet, les données peuvent avoir déjà été insérées et être déjà dans la bdd, mais il peut y avoir aussi des doublons dans le CSV qui justifieraient plutôt un update. En réfléchissant, je pense qu'il me faudra 5 ID car il y a 5 tables, et pour chacune, il faut choisir entre un insert et update.

    Je continue de réfléchir à la question et je reviendrai, soit pour poser de nouvelles questions, soit pour exposer la solution adoptée...
    Il vaut mieux viser la perfection et la manquer que viser l'imperfection et l'atteindre. - Bertrand Russell

  4. #4
    Modérateur

    Syntaxe de INSERT ... ON DUPLICATE KEY UPDATE : https://dev.mysql.com/doc/refman/8.0...duplicate.html
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  5. #5
    Membre expert
    Citation Envoyé par laurentSc Voir le message

    Raison : je ne sais pas pourquoi, mais mon "client", qui connait le SQL, ne veut pas de insert...on duplicate key update. On se voit demain, et je lui demanderai pourquoi.
    La raison est qu'il craint des effets de bord pernicieux venant du SGBD et préfère laisser cette gestion à l'application.
    Il vaut mieux viser la perfection et la manquer que viser l'imperfection et l'atteindre. - Bertrand Russell

  6. #6
    Modérateur

    Autrement dit l'application peut faire n'importe quoi alors que ça devrait être le SGBD qui est maître de la qualité des données !
    Ton client connaît peut-être le SQL mais pas bien les SGBD !
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  7. #7
    Expert éminent sénior
    Bonjour

    Si le but est de dupliquer à l'identique les données d'une BDD vers une autre, la présence dans le fichier CSV des identifiants techniques servant de PK peut être utile. Mais l'utilisation d'un CSV n'est pas la meilleure solution.
    Dans tous les autres cas, les identifiants techniques servant de PK n'ont rien à faire dans le fichier CSV. Si présents, vous devez les ignorer et déléguer au SGBD l'attribution de ces identifiants techniques. Par contre, le fichier CSV doit comporter la ou les colonnes fonctionnelles dont la combinaison permet de déterminer la PK et ce pour chacune des tables cibles.

    Exemple :
    dans le fichier CSV j'ai un nom, un prénom une date de naissance et un n° de sécurité sociale associés à une PK technique de valeur V.
    Il est fort possible que dans ma base de données, je connaisse déjà cette combinaison nom, prénom, ddn et nni mais associée à une autre valeur de PK
    Il ne faudra pas dans ce cas créer une nouvelle ligne dans la table, mais peut être modifier certaines valeurs connues en table pour les remplacer par celles du fichier.

    Si plusieurs lignes du CSV comportent cette même valeur de clef fonctionnelle, alors il conviendra de déterminer laquelle des lignes doit être retenue pour mettre à jour votre base de données (la plus récente par exemple).

  8. #8
    Membre expert
    Bonjour,

    merci pour votre contribution.

    Citation Envoyé par escartefigue Voir le message
    Mais l'utilisation d'un CSV n'est pas la meilleure solution.
    Quelle est alors la meilleure solution ?

    Citation Envoyé par escartefigue Voir le message
    Si le but est de dupliquer à l'identique les données d'une BDD vers une autre, la présence dans le fichier CSV des identifiants techniques servant de PK peut être utile
    Ce n'est pas le cas aujourd'hui. Dans le fichier CSV, ne figurent que les données fonctionnelles. Le contenu est certes identique mais pas l'organisation en tables.

    Citation Envoyé par escartefigue Voir le message
    Si plusieurs lignes du CSV comportent cette même valeur de clef fonctionnelle,
    Je suppose que vous vouliez plutôt dire "clef technique", non ?
    Il vaut mieux viser la perfection et la manquer que viser l'imperfection et l'atteindre. - Bertrand Russell

  9. #9
    Expert éminent sénior
    Citation Envoyé par laurentSc Voir le message
    Quelle est alors la meilleure solution ?
    La réplication si les bases doivent être synchrones ou à peu près ou un fichier pourquoi pas, mais utilisé avec LOAD DATA INFILE.


    Citation Envoyé par laurentSc Voir le message
    Ce n'est pas le cas aujourd'hui. Dans le fichier CSV, ne figurent que les données fonctionnelles.
    Dans le premier post, il est pourtant mentionné que le fichier CSV contient les ID.
    Faut il comprendre que les ID sont en réalité des identifiants fonctionnels ?


    Citation Envoyé par laurentSc Voir le message
    Je suppose que vous vouliez plutôt dire "clef technique", non ?
    Non : prénom, nom, n° de sécurité sociale pris isolément ou combinés sont des identifiants fonctionnels. Les identifiants techniques sont des valeurs asémantiques, le plus souvent des chronos attribués par le SGBD.

  10. #10
    Membre expert
    Citation Envoyé par escartefigue Voir le message

    Faut il comprendre que les ID sont en réalité des identifiants fonctionnels ?
    Le seul endroit où je parle d'ID est le post #3. Le terme ID est trompeur car ce ne sont pas des identifiants. J'ai repris le terme de Tatayo (au post #2) mais pas sûr d'avoir suivi son idée.

    En fait, mon idée, c'est qu'il est nécessaire, quand je lis le CSV et que je décide de faire un insert, de mémoriser (dans un tableau nommé ID_xyz) la valeur insérée pour que si je la retrouve plus tard dans la lecture du CSV, je fasse un update plutôt qu'un insert.
    Il vaut mieux viser la perfection et la manquer que viser l'imperfection et l'atteindre. - Bertrand Russell

  11. #11
    Expert éminent sénior
    Effectivement c'était le post n°3 et non le 1 et ok, je comprends mieux

    Pour le coup, ne suffirait-il pas de prendre la dernière occurrence du fichier CSV (le mouvement le plus récent ?) et de faire un seul insert ?

  12. #12
    Membre expert
    Pas sûr qu'on se comprenne ; dernière occurrence du CSV. OK, mais on a déjà reçu (et traité) des CSV. Donc la bdd a déjà été alimentée. Donc si on reçoit un nouveau CSV, il faudra choisir, pour chaque donnée un insert ou un update (ou rien si la colonne a déjà la même valeur dans la bdd)...
    Il vaut mieux viser la perfection et la manquer que viser l'imperfection et l'atteindre. - Bertrand Russell

  13. #13
    Modérateur

    Si tu n'as pas besoin de validation des informations par un opérateur, je ferais ainsi :

    1) Import du CSV dans une table d'import
    2) Procédure SQL qui balaie la table et fait le boulot d'insertion ou de mise à jour
    3) Vidage de la table d'import
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  14. #14
    Membre expert
    Mais ce qui est prévu : avant de faire un insert ou un update, l'utilisateur (donc celui qui aura lancé l'import du CSV) doit accepter ou refuser cette action sur la bdd...
    Il vaut mieux viser la perfection et la manquer que viser l'imperfection et l'atteindre. - Bertrand Russell

  15. #15
    Expert éminent sénior
    Bonjour LaurentSc

    Citation Envoyé par laurentSc Voir le message
    Pas sûr qu'on se comprenne ; dernière occurrence du CSV. OK, mais on a déjà reçu (et traité) des CSV. Donc la bdd a déjà été alimentée. Donc si on reçoit un nouveau CSV, il faudra choisir, pour chaque donnée un insert ou un update (ou rien si la colonne a déjà la même valeur dans la bdd)...
    Effectivement, les échanges par écrit sont souvent plus difficile que par oral
    Cela étant, que la base de données cible soit déjà chargée ne change rien à l'affaire, je réagissais essentiellement par rapport à cette remarque :

    Citation Envoyé par laurentSc Voir le message
    En fait, mon idée, c'est qu'il est nécessaire, quand je lis le CSV et que je décide de faire un insert, de mémoriser (dans un tableau nommé ID_xyz) la valeur insérée pour que si je la retrouve plus tard dans la lecture du CSV, je fasse un update plutôt qu'un insert.
    Tout ce travail est inutile : si pour un même identifiant PK de la table cible il y a plusieurs occurrences dans le CSV, seule l'occurrence la plus pertinente du CSV, donc probablement la dernière d'un point de vue chronologique, doit être traitée.
    Si on a 5 occurrences dans le CSV qui concernent toutes la même personne, on ne va pas faire un insert à partir de la première puis 4 update avec les suivantes, faire un insert (ou un update si déjà présent en base) à partir de la dernière occurrence du CSV produira exactement le même résultat et sera beaucoup plus rapide.

  16. #16
    Membre expert
    Bonjour,

    votre remarque me fait réfléchir de nouveau à ce qu'il faudrait faire pour traiter les CSV. Voici finalement ce que je pense : d'abord, les données vont être réparties dans 5 tables, dont 4 ont une seule PK et une en a 2. Donc, si dans le CSV, je tombe sur une (ou 2) PK non présentes en bdd, je pose la question à l'utilisateur si on fait un insert, en lui montrant l'ensemble des données à insérer. De cette façon, le contenu de la bdd sera validé.
    Donc, si plus tard (que ce soit dans le même CSV ou un autre), je trouve un ou 2 PK déjà en bdd, comme le contenu de la bdd est validé, il n'y a rien à faire, si ce n'est afficher un warning à l'utilisateur. Est-ce OK ?
    Il vaut mieux viser la perfection et la manquer que viser l'imperfection et l'atteindre. - Bertrand Russell

  17. #17
    Modérateur

    dont 4 ont une seule PK et une en a 2

    Euh... une table ne peut avoir qu'une seule PK (PRIMARY KEY) mais cette PK peut être composée de plusieurs colonnes.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  18. #18
    Membre expert
    Tout à fait, CinePhil, c'est ce que je voulais dire : quand je disais 2 PK, en fait, il s'agit effectivement d'une seule PK constituée de 2 colonnes (alors que dans les 4 autres tables, la PK est constituée d'une seule colonne). Il s'agit d'un raccourci, dû à mon inexpérience...
    Il vaut mieux viser la perfection et la manquer que viser l'imperfection et l'atteindre. - Bertrand Russell

  19. #19
    Expert éminent sénior
    Citation Envoyé par laurentSc Voir le message
    d'abord, les données vont être réparties dans 5 tables, dont 4 ont une seule PK et une en a 2.
    une table ne peut avoir qu'une seule PK, par contre, cette PK peut se composer d'une seule ou de plusieurs colonnes, nuance


    Citation Envoyé par laurentSc Voir le message

    Donc, si dans le CSV, je tombe sur une (ou 2) PK non présentes en bdd, je pose la question à l'utilisateur si on fait un insert, en lui montrant l'ensemble des données à insérer. De cette façon, le contenu de la bdd sera validé.
    Donc, si plus tard (que ce soit dans le même CSV ou un autre), je trouve un ou 2 PK déjà en bdd, comme le contenu de la bdd est validé, il n'y a rien à faire, si ce n'est afficher un warning à l'utilisateur. Est-ce OK ?
    Il manque des éléments de contexte pour savoir ce qu'il faut faire.
    Est-ce que plusieurs utilisateurs peuvent, au travers du fichier CSV, faire des demandes de modif qui concernent les mêmes lignes dans la base de données
    Exemple :
    à l'instant T0, l'utilisateur UTIL001 crée une ligne dans le CSV contenant

    [table="width: 500", class: grid] [tr] [td]Nom[/td] [td]Prénom[/td] [td]date_MàJ[/td] [td]Util_MàJ[/td] [td]Code[/td] [/tr] [tr] [td]Dupont [/td] [td]Sidonie [/td] [td]2020-10-02[/td] [td]UTIL001 [/td] [td]Q5[/td] [/tr] [/table] Sidonie Dupont n'étant pas connue dans la base de données, la ligne est créée avec une nouvelle PK, jusqu'ici aucun problème à l'instant T1, un nouveau fichier CSV est reçu, il contient les lignes suivantes [table="width: 500", class: grid] [tr] [td]Nom[/td] [td]Prénom[/td] [td]date_MàJ[/td] [td]Util_MàJ[/td] [td]Code[/td] [/tr] [tr] [td]Dupont [/td] [td]Sidonie [/td] [td]2020-10-19[/td] [td]UTIL001 [/td] [td]R3[/td] [/tr] [tr] [td]Azhalif [/td] [td]Hassan [/td] [td]2020-10-21[/td] [td]UTIL001 [/td] [td]C4[/td] [/tr] [tr] [td]Dupont [/td] [td]Sidonie [/td] [td]2020-10-22[/td] [td]UTIL005 [/td] [td]Q2[/td] [/tr] [/table] Qu'allez-vous faire ? Appeler les deux utilisateurs UTIL001 et UTIL005 pour vérifier si le bon code de Sidonie Dupont est R3 ou Q2 ou bien considérer que la dernière mise à jour est forcément celle dont la date de mise à jour est la plus récente (et que donc Q2 est la bonne valeur) ? S'il faut contacter chaque utilisateur, ca devient compliqué. Surtout que là j'ai simplifié avec une seule colonne modifiée et seulement deux demandes de mise à jour pour une même ligne, mais si on enrichit, selon la fréquence des mises à jour et le nombre de colonnes concernées, ça devient ingérable :? Pire : deux demandes de MàJ concernent la même personne mais des colonnes différentes, comment gérez vous la cohérence des données entre-elles si besoin.

  20. #20
    Membre expert
    Citation Envoyé par escartefigue Voir le message
    une table ne peut avoir qu'une seule PK, par contre, cette PK peut se composer d'une seule ou de plusieurs colonnes, nuance
    Citation Envoyé par CinePhil Voir le message

    Euh... une table ne peut avoir qu'une seule PK (PRIMARY KEY) mais cette PK peut être composée de plusieurs colonnes.
    Citation Envoyé par laurentSc Voir le message
    Tout à fait, CinePhil, c'est ce que je voulais dire : quand je disais 2 PK, en fait, il s'agit effectivement d'une seule PK constituée de 2 colonnes (alors que dans les 4 autres tables, la PK est constituée d'une seule colonne). Il s'agit d'un raccourci, dû à mon inexpérience...


    Pour en venir au reste de votre remarque : les CSV (pour l'instant) ne concernent pas des demandes de modif mais contiennent des informations sur des licences sur des applications (infos sur le propriétaire, sur l'application et sur la licence elle-même (dates de début, éventuellement de fin, et encore active ou non)). Autrement dit, les variations des valeurs dans le CSV ne peuvent porter que sur des variations des infos sur le propriétaire, sur l'application et sur les dates. Concernant les informations sur le propriétaire ou sur l'application, l'utilisateur les connait donc saura si c'est juste ou non. Pour les dates, si on a plusieurs lignes pour le même propriétaire et la même application, il y a visiblement une erreur dans le fichier CSV (donc à signaler).
    Il vaut mieux viser la perfection et la manquer que viser l'imperfection et l'atteindre. - Bertrand Russell

###raw>template_hook.ano_emploi###