Discussion :

[Invité]

Bonjour,

J'ai tenté de coder le SQL que vous avez bien voulu fournir à destination de SQLITE et cela a fonctionné.
(Pour situer mon niveau, j'ai débuté PYTHON il y a deux mois sans rien connaitre avant)

J'ai pu observé la bonne création des tables en utilisant DB BROWSER FOR SQLITE.

Néanmoins, j'ai souhaité ajouté deux autres tables: PROPRIETAIRE ET MANAGER.
Je crois avoir utilisé la bonne manière de coder en plagiant ce que vous avez publié.

Or, cela ne fonctionne pas.
PYTHON me renvoie le message d'erreur joint infra :






Pour tout dire, mes tables sont "listées" dans un dictionnaire.






J'ai beau comme à l'accoutumée me relire pendant des heures, en vain, je ne parviens pas à trouver le problème.
Qu'en pensez-vous ?

[fsmrel]

Bonjour ceststef,


Utilisez VARCHAR plutôt que TEXT. Ce que dit en effet la doc SQL Server :

Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

De mon côté, concernant la table PROPRIETAIRE, si SQL Server accepte ceci :

    ProprietaireNom  TEXT   NOT NULL  

Il rejette :

    ProprietaireNom  TEXT(48)   NOT NULL 

La création de la table PROPRIETAIRE est alors refusée, en conséquence de quoi la clé étrangère le sera, d’autant plus que votre table CHEVAL ne comporte pas de colonne ProprietaireId...

Ce qui fonctionne (je bannis le type TEXT) :

CREATE TABLE PROPRIETAIRE 
(
        ProprietaireId   INT           NOT NULL 
      , ProprietaireNom  VARCHAR(48)   NOT NULL 
    , CONSTRAINT PROPRIETAIRE_PK PRIMARY KEY (ProprietaireId)
) ;

CREATE TABLE CHEVAL 
(
        ChevalId              INT           NOT NULL
      , ChevalNom             VARCHAR(48)   NOT NULL
      , ChevalDateNaissance   DATE          NOT NULL
      , ChevalSexe            CHAR(1)       NOT NULL
      , ProprietaireId        INT           NOT NULL 
    , CONSTRAINT CHEVAL_PK PRIMARY KEY (ChevalId)
    , CONSTRAINT CHEVAL_PROPRIETAIRE_FK FOREIGN KEY (ProprietaireId)
          REFERENCES PROPRIETAIRE  (ProprietaireId) 
) ;

Même principe pour les entraîneurs.

 

[Invité]

Merci, ça fonctionne. C'est génial.

J'avais omis la création des colonnes Id dans CHEVAL, c'était pourtant clairement indiqué dans le message d'erreur.

Comme quoi, un plagiat n'est jamais qu'une pâle copie.
Et puis j'ai modifié TEXT pour VARCHAR mais il semble que cela n'avait pas d'incidence,
à tout le moins pour la création des tables sous SQLITE.

Maintenant je vais m'atteler à la création d'un formulaire de remplissage des tables.
Encore une fois, merci pour la leçon.

[fsmrel]

On a eu chaud !

Je ne connais rien aux formulaires, mais pour les INSERT ça peut aller : si problème, n'hésitez pas !

 

[Invité]

Bonsoir,

Merci pour le coup de main, c'est le rêve pour un débutant comme moi.
J'ai à nouveau remanié le MLD en ajoutant des tables.






Pensez-vous que je sois dans le juste ?
Pour un partant qui aurait un handicap de distance ou de poids mais pas les deux, la fonction XOR ne semble pas exister avec sqlite. Il me faudra je pense le prévoir dans mon programme principal.

En tout cas, merci, mais ça je crois que je l'ai déjà dit.

[fsmrel]

Bonjour ceststef,


Selon votre diagramme, vous prenez en compte l’aspect HISTORIQUE. Pour arriver à modéliser correctement, il faut impérativement commencer par raconter l’histoire des chevaux, en l’occurrence leur possession par leurs propriétaires successifs (ceci vaut évidemment pour leurs entraîneurs successifs).

Comme le montre l’étude assez approfondie à laquelle je me suis livré, il faut impérativement dissocier le « DEPUIS » du « DURANT » : d’une part, depuis quelle date tel cheval appartient à tel propriétaire, et d’autre part, à qui ce cheval a appartenu durant quelle période antérieure.

Il est temps d’illustrer (par commodité, je remplace les valeurs des ids par des valeurs parlantes) :

Le 1er octobre 2019, Fernand et Raoul ont déjeuné ensemble puis ont acheté des chevaux. Totoche est une jeune pouliche qui n’a pas de passé, c’est la 1re fois qu’elle a fait l’objet d’une acquisition par un propriétaire, à savoir Fernand. Zaza a d’abord appartenu à Paul puis à Raoul avant d’appartenir à Fernand. Pipeau a appartenu à Pascal avant d’être acheté par Raoul.

Sous forme tabulaire :

 
PROPRIETAIRE {ProprietaireId    ...}
              Fernand           ...
              Raoul             ...
              Paul              ...
              Pascal            ...

CHEVAL {ChevalId    ProprietaireId   ProprietaireDepuis    ...}
        Totoche     Fernand          01/10/2019            ... 
        Zaza        Fernand          01/10/2019            ... 
        Pipeau      Raoul            01/10/2019            ... 

PROPRIETAIRE_HISTO {ChevalId    ProprietaireId   ProprietaireDurant        ...}
                    Zaza        Paul             [07/08/2014:14/08/2015]   ...
                    Zaza        Raoul            [15/08/2015:30/09/2019]   ...
                    Pipeau      Pascal           [14/07/2017:30/09/2019]   ...

Conséquences sur la modélisation :

La table CHEVAL est enrichie de l’attribut ProprietaireDepuis, de type DATE :

CREATE TABLE CHEVAL 
(
        ChevalId              INT           NOT NULL
      , ChevalNom             VARCHAR(48)   NOT NULL
      , ChevalDateNaissance   DATE          NOT NULL
      , ChevalSexe            CHAR(1)       NOT NULL
      , ProprietaireId        INT           NOT NULL 
      , ProprietaireDepuis    DATE          NOT NULL 
    , CONSTRAINT CHEVAL_PK PRIMARY KEY (ChevalId)
    , CONSTRAINT CHEVAL_PROPRIETAIRE_FK FOREIGN KEY (ProprietaireId)
          REFERENCES PROPRIETAIRE  (ProprietaireId) 
) ;

Et il y a mise en oeuvre de la table PROPRIETAIRE_HISTO. Avec PostgreSQL on peut gérer les intervalles de dates grâce au type DATERANGE :

CREATE TABLE PROPRIETAIRE_HISTO
(
        ChevalId              INT           NOT NULL
      , ProprietaireId        INT           NOT NULL
      , ProprietaireDurant    DATERANGE     NOT NULL
    , CONSTRAINT PROPRIETAIRE_HISTO_PK PRIMARY KEY (ChevalId, ProprietaireDurant)
    , CONSTRAINT PROPRIETAIRE_HISTO_CHEVAL_FK FOREIGN KEY (ChevalId)
          REFERENCES CHEVAL  (ChevalId)
    , CONSTRAINT PROPRIETAIRE_HISTO_PROPRIO_FK FOREIGN KEY (ProprietaireId)
          REFERENCES PROPRIETAIRE (ProprietaireId)
) ; 

Avec MySQL, en l’absence d’un type équivalent à DATERANGE, on est réduit à gérer les intervalles au moyen de deux attributs de type DATE :

CREATE TABLE PROPRIETAIRE_HISTO
(
        ChevalId              INT           NOT NULL
      , ProprietaireId        INT           NOT NULL
      , ProprietaireDebut     DATE          NOT NULL
      , ProprietaireFin       DATE          NOT NULL
    , CONSTRAINT PROPRIETAIRE_HISTO_PK PRIMARY KEY (ChevalId, ProprietaireDebut)
    , CONSTRAINT PROPRIETAIRE_HISTO_CHEVAL_FK FOREIGN KEY (ChevalId)
          REFERENCES CHEVAL  (ChevalId)
    , CONSTRAINT PROPRIETAIRE_HISTO_PROPRIO_FK FOREIGN KEY (ProprietaireId)
          REFERENCES PROPRIETAIRE (ProprietaireId)
) ; 

Notez les clés primaires !


Au stade MLD :

Par référence à l’exemple que j’ai pris, vous observerez que selon votre MLD :

(1) On sait dire que Fernand est actuellement propriétaire de Totoche et Zaza, mais on ne sait pas depuis quand. Même remarque concernant Raoul actuellement propriétaire de Pipeau.

(2) Concernant l’aspect historique, on peut seulement dire par exemple que Raoul fut propriétaire durant les années 2015 à 2019, mais sans pouvoir dire de quels chevaux.

Modéliser les historiques n’est pas a priori chose aussi simple que ça en a l’air, il faut de la méthode et de la rigueur, ce que montre l’étude évoquée et qu’il faut suivre...
 

[fsmrel]

Concernant le XOR : cela n’est résolu en principe qu’avec une assertion dans le cas de la norme SQL (instruction CREATE ASSERTION), ou d’un trigger dans le cas des SGBD qui ne proposent pas cette instruction (à ce jour, aucun SGBD ne la propose !)

Mais on peut avantageusement pallier cette lacune au moyen d’une contrainte appliquée à la table PARTANT, voyez la contrainte PARTANT_HANDICAP ci-dessous :

 CREATE TABLE PARTANT 
(
        ReunionId        INT            NOT NULL
      , CourseId         INT            NOT NULL
      , ChevalId         INT            NOT NULL
      , JockeyId         INT            NOT NULL
      , RangId           INT            NOT NULL
      , PartantNumero    INT            NOT NULL
      , PartantCote      VARCHAR(16)    NOT NULL
      , PartantPoids     DECIMAL(4,2)   NOT NULL
      , DistanceAcourir  INT            NOT NULL
      , PoidsAsupporter  DECIMAL(4,2)   NOT NULL
      , PartantCommentaire VARCHAR(256) NOT NULL
    , CONSTRAINT PARTANT_PK PRIMARY KEY (ReunionId, CourseId, ChevalId)
    , CONSTRAINT PARTANT_JOCKEY_AK UNIQUE (ReunionId, CourseId, JockeyId)
    , CONSTRAINT PARTANT_NUMERO_AK UNIQUE (ReunionId, CourseId, PartantNumero)
    , CONSTRAINT PARTANT_COURSE_FK FOREIGN KEY (ReunionId, CourseId)
      REFERENCES COURSE (ReunionId, CourseId)
    , CONSTRAINT PARTANT_CHEVAL_FK FOREIGN KEY (ChevalId)
          REFERENCES CHEVAL (ChevalId)
    , CONSTRAINT PARTANT_JOCKEY_FK FOREIGN KEY (JockeyId)
          REFERENCES JOCKEY (JockeyId)
    , CONSTRAINT PARTANT_RANG_FK FOREIGN KEY (RangId)
          REFERENCES RANG (RangId)
    , CONSTRAINT PARTANT_HANDICAP CHECK (
                                         (PoidsAsupporter = 0 AND DistanceAcourir = 0)
                                         OR (PoidsAsupporter > 0 AND DistanceAcourir = 0)
                                         OR (PoidsAsupporter = 0 AND DistanceAcourir > 0)
                                        )
) ; 

Ceci vous évite d’avoir à contrôler par vous-même


 

[Invité]

Bonjour,

Les tables sont toutes créées. Le code fonctionne à merveille.
Néanmoins, j'ai bien intégré le cheminement pour parvenir à votre résultat.
Encore une fois, j'étais dans les choux avec mon diagramme.

Il me reste du chemin avant de parvenir à rendre opérationnel la bd mais je vous tiendrai au courant
dès que j'y serai parvenu (ça se compte en mois).

Je vous ferai un feed-back.
Pas besoin de le redire, mais je le fais quand même: merci.

[fsmrel]

Bonjour ceststef,

Ainsi votre projet progresse dans le bon sens, j’en suis ravi. Certes Paris ne s’est pas fait en un jour et il vous reste du pain sur la planche, mais si vous avez besoin d’un petit coup de main de temps en temps, n’hésitez pas à en faire part.

Peut-être que It’s a long way to Tipperary, mais bonne route !

 

[Invité]

Bonjour,

Je connais de nouveaux déboires mais il n'est pas impossible que ma question ne soit plus liée à ce post.
Enfin, j'essaie tout de même.

Je tente désormais de nourrir ma base de données. Or, j'essuie des erreurs et cela est liée à sa structure même.
En effet, pour exemple, je tente de débuter par la table REUNION puisqu'elle contient la colonne ReunionDate qui sera le point central de la base.

La difficulté est qu'on ne peut par essence qu'insérer des données que dans une table à la fois. Or, tous les attributs de la table ont des contraintes NOT NULL.
Mais puisque l'attribut TerrainId est une clé étrangère tirée de la table TERRAIN, mécaniquement la table TERRAIN n'a pas encore de données insérées, et donc mon programme sous PYTHON renvoie une erreur.

Bref, si j'ai bien compris comment alimenter une simple table avec la fonction INSERT, faire la même chose avec des tables liées par des clés étrangères me pose problème: il y a sans doute quelque chose que je ne connais pas encore.
Auriez vous une idée de quelle chose il s'agit ?
Est-ce que cela a trait aux sujet des ' transactions ' ou une fonction approchante permettant d'alimenter de façon simultanée des tables ?
J'espère que vous pardonnerez la question du profane car j'avance à petits pas mais j'avance ...

[escartefigue]

Bonjour,

Bonjour,

Il y a plusieurs solutions possibles

• soit vous respectez l'ordre d'alimentation de chaque table en tenant compte des clefs étrangères, ça peut facilement être assez fastidieux pour une base de données vide ayant de nombreuses tables
• soit vous chargez plusieurs tables simultanément et vous faites des commits par paquets fonctionnels cohérents, l'intégrité référentielle n'étant vérifiée qu'au moment du "commit".
• soit encore, avec certains SGBD (SQL-Server ou DB2 par exemple), vous faites un chargement en masse sans vous préoccuper de l'ordre des tables à charger et en activant une option qui ignore les contraintes. Vous contrôlerez les contraintes uniquement après avoir chargé toutes les tables.

[Invité]

Bonjour Escartefigue,

Merci beaucoup pour la réponse, je l'ai bien comprise.
Aussi, s'agissant de la dernière solution liée à une option capable de suspendre les contraintes, avez vous connaissance d'une telle fonction se rapportant à SQLITE ?

[escartefigue]

Bonjour,

Malheureusement non, je ne maîtrise pas ce SGBD, mais vous trouverez peut-être la réponse ici :
https://www.sqlite.org/foreignkeys.html

[Invité]

Merci mais c'est un peu aride comme lecture pour qui ne parle pas couramment anglais.
Il est dommage qu'on ne puisse pas trouver la même documentation à propos de Sqlite en français.
Mais tant pis je vais tenter de m'en accommoder. Merci bien en tout cas.

[Invité]

Bonjour,

J'ai une question simple mais je veux être certain de ne pas faire fausse route avant de poursuivre.

A l'occasion de l'insertion des données dans une base de données SQLITE, les champs nommés PRIMARY KEY "s'auto-incrémentent" même sans utiliser la fonction AUTO INCREMENT, tout va bien.

Mais s'agissant d'une clé étrangère (qui est une clé primaire dans une table liée), me faut-il systématiquement récupérer sa valeur dans la table où elle est primaire et venir l'insérer manuellement lors de l'insertion des données dans la table où elle est clé secondaire ?

SQLITE ne peut il pas le faire mécaniquement dans la mesure où le lien ou référence entre ces tables est clairement déclarée à l'occasion de la création des tables ?

J'espère que ma question est recevable car un peu brouillon, je l'admets.

[escartefigue]

Il faut que l'application récupère la valeur de la PK de la table parente, car le SGBD ne peut pas deviner tout seul à quel parent on souhaite rattacher une ligne dans une table liée.
On peut par exemple ajouter une ligne de commande à une commande existante, il faut bien savoir de quelle commande il s'agit et récupérer la valeur de la PK correspondante avant d'insérer cette nouvelle ligne

[Invité]

Merci beaucoup pour la réponse formée. J'avais subodoré ce qu'il fallait faire.
J'avais effectivement fait l'essai en récupérant la valeur de la PK pour "alimenter" le FK de l'autre table liée.
Donc si je comprends bien, il n'y a que les PK qui se "remplissent" seules, le reste est à faire par le programmeur. Correct ?

En ce cas, que devient donc l'utilité de la commande "PRAGMA foreign_keys = ON" ?

[escartefigue]

Donc si je comprends bien, il n'y a que les PK qui se "remplissent" seules, le reste est à faire par le programmeur. Correct ?

Oui.

En ce cas, quelle est donc l'utilité de la commande "PRAGMA foreign_keys = ON" ?

Encore une fois, je ne maîtrise pas SQLite, mais je suppose (par analogie avec d'autres SGBD) qu'il s'agit de configurer le moment où le SGBD vérifie les contraintes : à chaque ordre ou au moment du commit
À chaque ordre : il faut impérativement créer les lignes parentes avant les lignes qui y font référence
Au commit : l'ordre importe peu, l'intégrité référentielle n'est vérifiée que lors du commit.

Bien évidemment, si la PK est de type attribué par le SGBD, il faut de toute façon insérer la ligne possédant la PK en premier. Ce paramètre n'a de sens que pour des PK attribuées par le traitement

[Invité]

Si on reprend la table COURSE,
la clé primaire est constituée par ReunionId et CourseId.
Problème: SQLITE m'insère un NULL dans la colonne CourseId quand je remplis les colonnes de cette table.

Je comprends que ReunionId existe déjà car il est PK dans la table REUNION, donc je récupère sa valeur pour alimenter la table COURSE.
En revanche, je ne comprends pas pourquoi il n'y a pas autoincrémentation pour CourseId qui est déclaré PK à la création de la table.

Aussi je souhaiterais savoir ce qu'il faut faire. Me faut-il créer manuellement la valeur de CourseId?

Ce qui signifie que pour les autres créations de course, il me faudra toujours récupérer la valeur précédente de CourseId afin de lui attribuer la suivante ?

En tout cas, si quelqu'un à la réponse, bien entendu je suis preneur, impatient de savoir ce que je ne connais pas encore.
Merci

[fsmrel]

Bonsoir ceststef,

je ne comprends pas pourquoi il n'y a pas autoincrémentation pour CourseId qui est déclaré PK à la création de la table.

Une colonne n’est auto-incrémentée que si on demande à ce qu’elle le soit. Par exemple dans le post #16, on n’a pas demandé l’auto-incrémentation de CourseId.

Maintenant, utilisons l’auto-incrémentation pour les tables suivantes :

CREATE TABLE HIPPODROME 
(
        HippodromeId     INT           IDENTITY(1,1) 
      , HippodromeNom    VARCHAR(48)   NOT NULL 
    , CONSTRAINT HIPPODROME_PK PRIMARY KEY (HippodromeId)
) ;

CREATE TABLE TERRAIN 
(
        TerrainId        INT           IDENTITY(1,1) 
      , TerrainEtat      VARCHAR(16)   NOT NULL 
    , CONSTRAINT TERRAIN_PK PRIMARY KEY (TerrainId)
) ;

CREATE TABLE PRIX 
(
        PrixId                INT           IDENTITY(1,1)
      , PrixNom               VARCHAR(48)   NOT NULL
    , CONSTRAINT PRIX_PK PRIMARY KEY (PrixId)
) ;

CREATE TABLE REUNION 
(
        ReunionId        INT           IDENTITY(1,1)
      , TerrainId        INT           NOT NULL 
      , HippodromeId     INT           NOT NULL
      , ReunionNumero    INT           NOT NULL
      , ReunionDate      DATE          NOT NULL
    , CONSTRAINT REUNION_PK PRIMARY KEY (ReunionId)
    , CONSTRAINT REUNION_AK UNIQUE (ReunionNumero)
    , CONSTRAINT REUNION_TERRAIN_FK FOREIGN KEY (TerrainId)
          REFERENCES TERRAIN
    , CONSTRAINT REUNION_HIPPODROME_FK FOREIGN KEY (HippodromeId)
          REFERENCES HIPPODROME (HippodromeId)
) ;

CREATE TABLE COURSE 
(
        ReunionId        INT           NOT NULL
      , CourseId         INT           IDENTITY(1,1)
      , PrixId           INT           NOT NULL
      , CourseNumero     INT           NOT NULL
      , CourseDuree      TIME          NOT NULL
      , CourseAllocation INT           NOT NULL
    , CONSTRAINT COURSE_PK PRIMARY KEY (ReunionId, CourseId)
    , CONSTRAINT COURSE_AK UNIQUE (ReunionId, CourseNumero)
    , CONSTRAINT COURSE_REUNION_FK FOREIGN KEY (ReunionId)
          REFERENCES REUNION
    , CONSTRAINT COURSE_PRIX_FK FOREIGN KEY (PrixId)
          REFERENCES PRIX (PrixId)
) ;

Je ne connais pas SQLITE, mais en tout cas voici un bout de jeu d’essai qui fonctionne avec SQL Server :

INSERT INTO HIPPODROME (HippodromeNom)
    VALUES ('Longchamps'), ('Auteuil') 
;
SELECT '' AS HIPPODROME, * FROM HIPPODROME
;
INSERT INTO PRIX (PrixNom)
    VALUES ('Grand prix du roi'), ('Grand prix de la reine')
;
SELECT '' AS PRIX, * FROM PRIX
;
INSERT INTO TERRAIN (TerrainEtat)
   VALUES ('sec'), ('mou'), ('boueux'), ('lourd')
;
SELECT '' AS TERRAIN, * FROM TERRAIN
;
INSERT INTO REUNION (TerrainId, HippodromeId, ReunionNumero, ReunionDate)
 SELECT    
    (SELECT TerrainId FROM TERRAIN WHERE TerrainEtat = 'lourd')
  , (SELECT HippodromeId FROM HIPPODROME WHERE HippodromeNom = 'Auteuil') 
  , 87, '2020-01-29'
;
INSERT INTO REUNION (TerrainId, HippodromeId, ReunionNumero, ReunionDate)
  SELECT    
    (SELECT TerrainId FROM TERRAIN WHERE TerrainEtat = 'sec')
  , (SELECT HippodromeId FROM HIPPODROME WHERE HippodromeNom = 'Auteuil') 
  , 116, '2020-02-17'
;
SELECT '' AS REUNION, * FROM REUNION
;
INSERT INTO COURSE (ReunionId, PrixId, CourseNumero, CourseDuree, CourseAllocation)
  SELECT
    (SELECT ReunionId FROM REUNION WHERE ReunionNumero = 116)
  , (SELECT PrixId FROM PRIX WHERE PrixNom = 'Grand prix de la reine')
  , 67, '02:05', 5000
;
SELECT '' AS COURSE, * FROM COURSE
;

Au résultat :

HIPPODROME (HippodromeId    HippodromeNom)
            1               Longchamps
            2               Auteuil

PRIX (PrixId    PrixNom)
      1         Grand prix du roi
      2         Grand prix de la reine

TERRAIN (TerrainId    TerrainEtat)
         1            sec
         2            mou
         3            boueux
         4            lourd

REUNION (ReunionId  TerrainId  HippodromeId  ReunionNumero  ReunionDate)
         1          4          2             87             2020-01-29
         2          1          2             116            2020-02-17

COURSE (ReunionId  CourseId  PrixId  CourseNumero  CourseDuree  CourseAllocation)
        2          1         2       67            00:02:05     5000