Gestion de la recursivité sur les commentaires

**fsmrel** · 15/11/2011, 12h49

Bonjour,

Je vous ai abandonné un temps, mais je ne vous oublie pas.

Je ne connais pas grand-chose à MySQL, mais en consultant le document « Manuel de référence MySQL 5.0 », au paragraphe 15.7.4 « Contraintes de clés étrangères FOREIGN KEY », on lit ceci :

Si MySQL vous retourne une numéro d'erreur 1005 lors de la comande CREATE TABLE, et un message d'erreur de numéro 150, alors la création de la table a échoué à cause de la contrainte de clé étrangère, qui n'a pas été correctement formulée. Similairement, si une commande ALTER TABLE échoue et indique une erreur 150, c'est que la définition de la clé étrangère est incorrectement formulée dans la table modifiée. Depuis la version 4.0.13, vous pouvez utiliser la commande SHOW INNODB STATUS pour avoir une explication détaillée de la dernière erreur de clé étrangère InnoDB sur le serveur.

Une question donc : le type de l’attribut u_id est-il le même dans les deux table en cause ?

Par ailleurs, la table UTILISATEUR est référencée par quelques autres tables : NOTER_ARTICLE, STATISTIQUES, SONDAGE_CHOISIR_POSSIBILITE, COMMENTAIRE, FICHIER_GPS_SANS_ARTICLE, ARTICLE_UTIL.

Si les tables appartiennent à des schémas (au sens SQL) distincts, ne pourrait-on pas envisager de considérer la table UTILISATEUR comme une image de la table FSB2_USERS et d’établir la contrainte référentielle seulement entre ces deux tables ?

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
ALTER TABLE UTILISATEUR ADD CONSTRAINT FK_UTILISATEUR 
      FOREIGN KEY (id_utilisateur) REFERENCES FSB2_USERS(u_id) 
              ON DELETE ... ON UPDATE RESTRICT ;

Mais il semble que toutes les tables sont maintenant dans le même schéma.

**heretik25** · 15/11/2011, 13h47

Bonjour,

Je vous ai abandonné un temps, mais je ne vous oublie pas.

Merci !

Je ne connais pas grand-chose à MySQL, mais en consultant le document « Manuel de référence MySQL 5.0 », au paragraphe 15.7.4 « Contraintes de clés étrangères FOREIGN KEY », on lit ceci :

Si MySQL vous retourne une numéro d'erreur 1005 lors de la comande CREATE TABLE, et un message d'erreur de numéro 150, alors la création de la table a échoué à cause de la contrainte de clé étrangère, qui n'a pas été correctement formulée. Similairement, si une commande ALTER TABLE échoue et indique une erreur 150, c'est que la définition de la clé étrangère est incorrectement formulée dans la table modifiée. Depuis la version 4.0.13, vous pouvez utiliser la commande SHOW INNODB STATUS pour avoir une explication détaillée de la dernière erreur de clé étrangère InnoDB sur le serveur.

Une question donc : le type de l’attribut u_id est-il le même dans les deux table en cause ?

Du côté de FSB2_USERS, c'est en int(11) en AUTO_INCREMENT alors que sur "CHOISIR_POSS", c'est en int(11) mais sans AUTO_INCREMENT. Est-ce là le souci ?

Par ailleurs, la table UTILISATEUR est référencée par quelques autres tables : NOTER_ARTICLE, STATISTIQUES, SONDAGE_CHOISIR_POSSIBILITE, COMMENTAIRE, FICHIER_GPS_SANS_ARTICLE, ARTICLE_UTIL.

Si les tables appartiennent à des schémas (au sens SQL) distincts, ne pourrait-on pas envisager de considérer la table UTILISATEUR comme une image de la table FSB2_USERS et d’établir la contrainte référentielle seulement entre ces deux tables ?

C'est à dire, dupliquer les informations des FSB2_USERS (ou tout du moins "u_id") dans "UTILISATEURS" ? Aussi, je ne sais pas si MYSQL est capable de créer des schémas comme le fait par exemple PostrgreSQL.

Quelle est la solution la plus pertinente dans notre cas ?

Merci pour vos lumières.

------------------------------------------
EDIT : Je viens de remarquer que les tables du forum étaient en Mysiam alors que celles générées par Poweramc étaient en Innodb. En forcant en Mysiam les tables du site, tout passe correctement sauf que toutes les clés étrangères ne sont pas générées ! J'ai donc mis la table FSB2_USERS en Innodb. De ce fait, je conserve toutes mes contraintes d'unicité.

Serait-ce la bonne solution ?

**fsmrel** · 16/11/2011, 16h35

Bonjour,

Envoyé par heretik25

Du côté de FSB2_USERS, c'est en int(11) en AUTO_INCREMENT alors que sur "CHOISIR_POSS", c'est en int(11) mais sans AUTO_INCREMENT. Est-ce là le souci ?

Normalement non.

Envoyé par heretik25

C'est à dire, dupliquer les informations des FSB2_USERS (ou tout du moins "u_id") dans "UTILISATEURS" ?

L’attribut u-id seulement, puisqu’il s’agit seulement d’établir un pont nécessaire et suffisant entre les deux univers.

Envoyé par heretik25

Je viens de remarquer que les tables du forum étaient en Mysiam alors que celles générées par Poweramc étaient en Innodb. En forcant en Mysiam les tables du site, tout passe correctement sauf que toutes les clés étrangères ne sont pas générées ! J'ai donc mis la table FSB2_USERS en Innodb. De ce fait, je conserve toutes mes contraintes d'unicité.

Serait-ce la bonne solution ?

Il faut du InnoDB systématiquement, MyISAM est bon pour la poubelle, il n’offre aucune sécurité quant àl’intégrité des données comme vous venez de le constater...

Avez-vous toujours le message d’injure #1005 - Ne peut créer la table 'pevtt_forum.#sql-a4_8d' (Errcode: 150) ?

**heretik25** · 16/11/2011, 16h56

Avez-vous toujours le message d’injure #1005 - Ne peut créer la table 'pevtt_forum.#sql-a4_8d' (Errcode: 150) ?

En passant la table FSB2_USERS en innodb, non, aucune erreur et toutes les contrainte sont ajoutées.

Après quelques tests d'insertions, cela me paraît très bien.

Maintenant, mieux vaut-il séparer nos deux "mondes" ou communiquer directement avec le forum ?

**fsmrel** · 16/11/2011, 19h54

Hélas !

On sort de mon domaine de compétences...

**heretik25** · 16/11/2011, 21h43

Bonsoir,

Si des personnes ont une idée sur la question merci de partager leur savoir !

Ce que je ne voudrais surtout pas, c'est de me retrouver pieds et poings lié au forum surtout si jour, je souhaite changer de forum !

Sinon, je vais partir comme cela car j'ai commencé à paramétrer mon ETL pour envoyer les données existantes dans la nouvelle modélisation.

Il va falloir que je réfléchisse aussi pour garder les référencements, faut-il créer un url_rewriting pour chaque url's ?

152 articles !

Bonne soirée.

**fsmrel** · 17/11/2011, 13h51

Bonjour,

Envoyé par heretik25

Ce que je ne voudrais surtout pas, c'est de me retrouver pieds et poings lié au forum surtout si jour, je souhaite changer de forum !

Même si vous ne deviez pas changer avant quelques années, rien n'empêche — si ce n'est déjà fait — de vous exercer à tout sauvegarder et, last but not least, restaurer le tout sur un autre forum. Navré, mais concernant MySQL dans cet exercice je ne peux pas vous aider...

Techniquement parlant, qu'entendez-vous par « garder les référencements » ? Il n'y a pas moyen d'automatiser en SQL les UPDATE des attributs impactés (URL je suppose) ? Si vous pouviez illustrer le besoin à l'aide d'un exemple ?

**heretik25** · 17/11/2011, 14h55

Bonjour et merci pour la réponse,

Prenons un exemple,

Aujourd'hui pour une balade à pied, j'avais une url du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

http://www.partir-en-vtt.com/php/balades/voir_balade.php?numdep=39&voir_balade=283&type_balade=1

On y trouvait des identifiants dans des variables, notamment l'id unique de la balade

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

voir_balade=283

Demain, l'URL va changer complétement pour devenir très certainement quelque chose du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

http://www.partir-en-vtt.com/php/article.php?id=1

Comment géreriez-vous ce genre de cas ?

**fsmrel** · 17/11/2011, 15h17

OK. Je fais un test et vous réponds dès que possible.

**fsmrel** · 17/11/2011, 17h01

Bonjour,

En première approche du problème... Soit la table ARTICLE, nouvelle version :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE ARTICLE (
   ArticleId            INT                  NOT NULL  auto_increment,
   ArtTitre             VARCHAR(64)          NOT NULL,
   Art_URL_Photo        VARCHAR(255)         NOT NULL,
   CONSTRAINT ARTICLE_PK PRIMARY KEY  (ArticleId)
) ;

Et supposons qu’elle ait par exemple été chargée à partir de l’ancienne version, avec les nouvelles valeurs pour ArticleId, mais pas pour les autres attributs. Dans ces conditions, prenons l’exemple de quelques lignes de la table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
ArticleId    ArtTitre      Art_URL_Photo
        1    Ça plane      http://www.partir-en-vtt.com/php/balades/voir_balade.php?numdep=39&voir_balade=283&type_balade=1
        2    Vive lui      http://www.partir-en-vtt.com...
        3    Le Jura       http://www.partir-en-vtt.com...
        4    C’est beau !  http://www.partir-en-vtt.com...
      100    Pédalons      http://www.partir-en-vtt.com...
      314    Poulidor      http://www.partir-en-vtt.com/php/balades/voir_balade.php?numdep=93&voir_balade=123&type_balade=1

Vu la nouvelle valeur de l’URL pour l’article 1 :

http://www.partir-en-vtt.com/php/article.php?id=1

Si l’id (id=1 en l’occurrence) est celle de l’article, alors on peut remplacer ainsi les anciennes URL (en SQL Server) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
UPDATE ARTICLE
       SET  Art_URL_Photo = 'http://www.partir-en-vtt.com/php/article.php?id=' + CAST (ArticleId AS VARCHAR(9))
       WHERE Art_URL_Photo LIKE 'http://www.partir-en-vtt.com/php/balades/voir_balade.php%'
;

L’opérateur « + » symbolise ici la concaténation de deux chaînes de caractères (pour la petite histoire, on dit qu’il y a surcharge de l’opérateur...) D’après ce que je lis, avec MySQL il faudrait remplacer :

truc + machin

par :

CONCAT (truc, machin)

La fonction CAST permet de convertir le type d’une donnée (en l’occurrence INT) en un autre type (en l’occurrence VARCHAR).

Le prédicat LIKE permet de sélectionner tous les URL commençant par :

http://www.partir-en-vtt.com/php/balades/voir_balade.php

Le caractère « % » qui suit cette chaîne est appelé caractère d’échappement. Il permet de dire : « à partir de cet endroit, on ne considère pas la suite de la chaîne dans l’opération de comparaison ».

Après UPDATE, on obtient ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
ArticleId    ArtTitre      Art_URL_Photo
        1    Ça plane      http://www.partir-en-vtt.com/php/article.php?id=1
        2    Vive lui      http://www.partir-en-vtt.com...
        3    Le Jura       http://www.partir-en-vtt.com...
        4    C’est beau !  http://www.partir-en-vtt.com...
      100    Pédalons      http://www.partir-en-vtt.com...
      314    Poulidor      http://www.partir-en-vtt.com/php/article.php?id=314

Tend-on vers ce que l’on souhaite ?

**heretik25** · 26/11/2011, 17h26

Merci pour la proposition

toujours une explication parfaite

Je regarderai ceci un peu plus tard.

-------------------
Je suis actuellement en train de re-développer le site et suis confronté à un problème.

Ce dernier se situe au niveau des tables "fichier_gps" et "coordonnee_gps".

En effet, la table fichier_gps va récupérer le nom du fichier gps mais aussi des statistiques sur le fichier GPS.

Ces statistiques sont issues des coordonnées GPS.

Sauf qu'il m'est impossible d'insérer les coordonnées GPS si le fichier gps n'y est pas. Inversement, je ne peux générer les statistiques si les coordonnées gps ne sont pas intégrées.

Je me retrouve donc face à un dilemme.

Dans l'idéale il faudrait donc que je puisse insérer les coordonnées gps avant le fichier gps...

J'ai bien pensé faire un insert fictif dans la table fichier_gps pour par la suite faire un update mais je trouve la solution assez moyenne.

Comment pourrait-on faire ?

**fsmrel** · 27/11/2011, 23h09

Bonsoir,

C'est le parcours du combattant

Envoyé par heretik25

Comment pourrait-on faire ?

Peut-être s’inspirer de Salomon : pourquoi ne pas envisager de couper le bébé en deux ? (Sans le jeter avec l’eau du bain...

)
Je veux dire, évacuer dans une entité-type STATS_GPS les données qui ~~marchent~~ roulent avec les coordonnées ? Par exemple (je ne préjuge pas de la distribution des attributs entre les entités-types FICHIER_GPS et STATS_GPS, c'est vous qui voyez) :

Ainsi, on crée d’abord les données insensibles aux calculs statistiques (hébergées par l'entité-type FICHIER_GPS), puis ensuite le reste (entité-type COORDONNEES_GPS à suivre par STATS_GPS).

**heretik25** · 28/11/2011, 10h27

Bonjour,

La proposition semble parfaite...comme toujours

Encore un grand merci.

Au niveau des contraintes est-ce que cela vous semble ok :

@bientôt

**fsmrel** · 28/11/2011, 17h53

Bonsoir,

Envoyé par heretik25

Au niveau des contraintes est-ce que cela vous semble ok

Oui. Par contre, les attributs fichier_gps_intitule et fichier_gps_utl_stockage figurent à la fois dans les entités-types pevtt_fichier_gps et pevtt_statistique_fichier_gps : il faudrait les évacuer d’une des deux entités-types.

**heretik25** · 28/11/2011, 18h33

Bonsoir,

Effectivemment

**heretik25** · 29/11/2011, 12h15

Je viens de nouveau vers vous afin de m'éclaircir les idées sur la gestion des photographies.

Un article peut-être agrémenté de 0 ou n photos.

A ce jour, au niveau de l'article, j'ai juste un champ qui me permet de stocker l'url du dossier où sont les photographies.

Est-ce plus pertinent de gérer les photos via un dossier de stockage ou directement en base en y stockant leur nom et/ou leur url ?

Autre particularité :

Ces photos peuvent ensuite être géolocalisées s'il s'agit d'un article pouvant recevoir des coordonnées GPS (balades, périples).

A ce jour, j'ai un champ "photo" dans la table "coordonee_gps" qui récupère le nom de la photo géolocalisée. Sauf que ce champ "photo" est très rarement rempli car par exemple, sur 1000 coordonnées GPS il ne peut n'y avoir aucune photo géolocalisée tout comme une dizaine.

En théorie, une coordonnée GPS peut recevoir donc 0 ou n photo. Cependant, pour faire simple restons sur le fait qu'une coordonnée GPS peut recevoir 0 ou 1 photo.

Comment géreriez vous ces particularités ?

**fsmrel** · 29/11/2011, 17h43

Bonsoir,

Est-ce plus pertinent de gérer les photos via un dossier de stockage ou directement en base en y stockant leur nom et/ou leur url ?

Je ne saisis pas complètement... Voulez-vous dire que vous créeriez une table pour stocker les photos ? Un petit dessin sous forme d’entités-types (ou de code SQL) serait le bienvenu pour exposer votre plan.

Comment géreriez vous ces particularités ?

Pourquoi vous limiter à 0,1 ? On pourrait définir une entité-type PHOTO_GPS identifiée relativement à COORDONNES_GPS :

Avec CASCADE, bien entendu...

**heretik25** · 29/11/2011, 20h39

Bonsoir,

Je ne saisis pas complètement... Voulez-vous dire que vous créeriez une table pour stocker les photos ? Un petit dessin sous forme d’entités-types (ou de code SQL) serait le bienvenu pour exposer votre plan.

Oui effectivement j'ai du mal à me décider sur le fait de :

Soit créer une table photos_article qui contiendrait les noms de chaque photo de l'article.
Soit avoir un champ "url_dossier_photo" dans la table ARTICLE qui contiendrait l'url du dossier unique de l'article.

Si nous retenons la seconde méthode, les photos seraient alors stockées comme ceci :

id_utilisateur
id_type_article
id_article

Soit ex : 2/4/115/*.jpg

L'objectif est ensuite de pouvoir brider l'utilisateur dans la gestion de ses images. En effet, je ne voudrais pas qu'un utilisateur A puisse aller utiliser les photos de l'utilisateur B.

Je vois un inconvénient à procéder par dossier. Si un jour l'id de l'article change, le nom du dossier doit changer.

En espérant avoir été un peu plus clair

Pour la modélisation des photos des coordonnées GPS :

Côté MPD, n'y aurait-il pas une clé inutile dans la table pevtt_photo_gps ?

Comme ça, je dirai que la clé id_fichier_gps n'est pas utile. Si oui, quelle manière permet sa suppression ?

Merci !

**fsmrel** · 30/11/2011, 16h42

Bonjour,

Pourriez-vous me faire parvenir vos derniers MCD/MPD ?

Je réponds déjà à propos de la clé de la table pevtt_photo_gps. Pour le reste, je vais regarder.

Envoyé par heretik25

Côté MPD, n'y aurait-il pas une clé inutile dans la table pevtt_photo_gps ? Comme ça, je dirai que la clé id_fichier_gps n'est pas utile.

Hum... Considérons le contenu de la table pevtt_fichier_gps :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
id_fichier_gps    fichier_gps_intitule    ...
             1              fichier 01    ...
             2              fichier 02    ...
           ...                     ...    ...

Passons au contenu correspondant de la table pevtt_coordonnee_gps. Pour le fichier GPS 1 on a 3 lignes relatives aux coordonnées et deux lignes pour le fichier 2. Dans le cadre de l’identification relative, vous noterez que les valeurs prises par l’attribut id_coord_gps recommencent à 1 pour chaque fichier (un peu comme les lignes de facture d’une facture...) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
id_fichier_gps    id_coord_gps    coord_gps_lat    ...
             1               1              x11    ...
             1               2              x12    ...
             1               3              x13    ...
           ...             ...              ...    ...
             2               1              x21    ...
             2               2              x22    ...
           ...             ...              ...    ...

Passons au contenu de la table pevtt_photo_gps. Pour le fichier GPS 1, on a 6 lignes, à savoir 3 lignes pour l’attribut id_coord_gps prenant la valeur 1, 2 lignes quand l’attribut id_coord_gps prend la valeur 2 et une ligne quand l’attribut id_coord_gps prend la valeur 3. Même principe pour le fichier GPS 2. Notez soigneusement que la valeur de l’attribut id_photo_gps recommence à 1 chaque fois que le couple {id_fichier_gps, id_coord_gps} change de valeur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
id_fichier_gps    id_coord_gps    id_photo_gps    photo_gps_nom
             1               1               1           nom111
             1               1               2           nom112
             1               1               3           nom113
             1               2               1           nom121
             1               2               2           nom122
             1               3               1           nom131
           ...             ...             ...              ...
             2               1               1           nom211
             2               1               2           nom212
             2               2               1           nom221
             2               2               2           nom222
             2               2               3           nom223
           ...             ...             ...              ...

Si l’attribut id_fichier_gps n’appartenait pas à la clé de la table pevtt_photo_gps, cette clé serait réduite au couple {id_coord_gps, id_photo_gps}. Dans cette hypothèse, la clé aurait la valeur <1, 1> à la fois pour les photos nom111 et nom211, elle aurait la valeur <1, 2> à la fois pour les photos, nom112 et nom212, etc. : la règle d’UNICITÉ qui régit les clés serait copieusement violée et le SGBD vous renverrait dans vos vingt-deux...

=> Vous me copierez 100 fois : « La règle d’UNICITÉ qui régit les clés est inviolable ».