Discussion :

[Alexandre T]

Bonjour,

Je dispose de deux tables te_circuit_cir et tj_historiquecircuit_hci
La première table est l'implémentation de l'entité circuit. Un circuit est en fait un trajet routier qu'effectue un véhicule. La seconde table est l'historique d'activité de ce circuit.

La première table est composée des colonnes suivantes

Code :

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

te_circuit_cir (cir_id, cir_lib, cir_cod, cir_longueur)

Les colonnes sont respectivement l'identifiant, le libellé, le code et la longueur de mon circuit.

La seconde table est composée des colonnes suivantes

Code :

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

te_historiquecircuit_hci(hci_id,cir_id,hci_debut,hci_duree)

les colonnes sont respectivement l'identifiant, la clef étrangère du circuit, la date et l'heure de debut de démarrage du circuit et le dernier hci_duree est un intervalle indiquant la durée du circuit.

hci_duree peut être null. Typiquement cela signifie que le circuit est actuellement encore en activité. Pour savoir les circuits en activité, j'exécute cette requête

Code :

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

SELECT cir_id FROM tj_historiquecircuit_hci WHERE  hci_duree IS NULL

Mon problème est le suivant, je ne veux pas avoir deux tuples avec hci_duree à NULL pour le même circuit dans la table tj_historiquecircuit_hci. En clair, le circuit 25 ne peut pas avoir été lancé ce jour à 11h00 et ce même jour à 11h30 sans que le premier lancement soit fermé (et donc que l'intervalle hci_duree ait une valeur comme "23 minutes" par exemple)

Quelle est la meilleure façon d'intégrer cette contrainte de table selon la norme SQL ? (voire selon PostgreSQL)

[elbj]

Bonjour

On pourrait mettre une contrainte d'unicité sur ce couple de champs : cir_id et hci_duree mais cela reviendrai à dire qu'un circuit ne peut avoir été "utilisé" deux fois avec la même durée.

Il est possible de mettre un trigger vérifiant s'il n'y a pas déjà une valeur null dans hci_duree, maintenant je ne sais pas si postgre le permet.

Enfin, cette problématique pourrait être gérée par la partie cliente, en gros le "trigger" y est déporté.

Cordialement

[Alexandre T]

La partie cliente le gère. On ne peut pas accéder aux fonctions d'activation d'un circuit si il est actif. Mais comme cela passe par le web, je dois améliorer la sécurité. Donc il y a une vérification par Java avant. Mais j'aime avoir une base bien modélisée surtout que certains clients sont adeptes du "je code directement dans la BDD..." .

Pour ta solution, deux valeurs nulles sont toujours distinctes donc cela ne marcherait pas.

Je crois que je n'ai pas le choix la seule option reste un trigger. PostgreSQL gère très bien les triggers, mais ça me plaît moyen niveau performances, surtout pour un trigger en update.

[SQLpro]

Voyez si PostGreSQL permet de faire des colonnes calculées indexées. Dans ce cas ajoutez la colonne à l'aide de l'expression suivante :
ISNULL(COALESCE(hci_duree, 0), hci_duree) AS hci_duree_CALC
Vous pouvez maintenant faire un index UNIQUE sur le couple (cir_id, hci_duree_CALC).

A +

[fsmrel]

Bonjour,

Si je puis donner un avis...

Lors de l’étape de modélisation, il est important de séparer ce qui est en cours de ce qui ne l’est plus.
Dans ces conditions, la table te_circuit_cir devrait comporter une colonne (cir_debut) permettant de préciser depuis quand un circuit est actif :

te_circuit_cir (cir_id, cir_lib, cir_cod, cir_longueur, cir_debut)

Il va sans dire que chaque colonne de cette table est déclarée NOT NULL.

La table te_historiquecircuit_hci conserve sa structure mais ne concerne vraiment que ce qui est terminé, ce qui fait que l’attribut hci_duree doit être déclaré NOT NULL, comme toutes les colonnes de cette table.

Mais cela ne résout pas les problèmes de chevauchement, à savoir qu’il ne doit pas exister de ligne de la table te_historiquecircuit_hci telle que :

te_historiquecircuit_hci.hci_debut + te_historiquecircuit_hci.hci_duree > te_circuit_cir.cir_debut

Cette contrainte peut être mise en œuvre au moyen de triggers concernant les inserts d’une part et les updates d’autre part (modification des dates et durées).

Le principe est le même pour s’assurer qu’il n’y a pas de chevauchement des périodes au sein de la table te_historiquecircuit_hci elle-même.

J’ai déjà évoqué ces problèmes de chevauchement de périodes avec lesultan2007 et lui avais proposé des triggers (façon SQL Server) :

http://www.developpez.net/forums/sho...6&postcount=14

Bon courage

[Alexandre T]

Bonjour,

Je vous remercie de votre aide. Pour la solution de SqlPro, je ne peux pas l'utiliser pour deux raisons :
Après 20 minutes de recherches, PostGreSQL ne semble pas accepté les colonnes calculées
Certes, l'intervalle est précis jusqu'à la millisecondes, mais ces circuits sont executés plusieurs fois par jour et ont en moyenne une durée de 30 minutes, au bout d'un an, la probabilité que deux exécutions de circuits identiques aient exactement la même durée (30 minutes 23 secondes et 255 milisecondes) est trop forte pour que je laisse une telle contrainte.

Je vais donc choisir l'option du déclencheur. Par contre, je pense faire un test avec la fonction overlaps* qui me permettra de tester plus simplement le chevauchement de période et en même temps me valdiera ma contrainte d'unicité. Désormais les modification et suppression sur cette table sont interdits aux utilisateurs se connectant (avec le profil de l'application associé), je ne devrais pas avoir de gros ralentissements.

la requête de teste de mon trigger aura la forme

Code :

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

1
2
3
 
SELECT COUNT(*) FROM COALESCE(OVERLAPS(hci_debut,hci_duree,:date_debut,:interval_duree),true)
AND cir_id = :cir_id

Ainsi si, pour le même circuit, deux période se chevauchent, je récupère un nombre de lignes supérieur à zéro et lève une erreur.

Si j'essaie d'insérer une ligne avec :interval_duree valant null dans une table contenant (pour le même circuit) déjà une période ouverte (cad hci_duree valant null)) alors mon test OVERLAPS(hci_debut,hci_duree,:date_debut,:interval_duree retournera NULL et COALESCE retournera le deuxième élément "true" et donc remontera ma ligne.

En effet, si je ne dis pas de bétise, la reqûete ci-dessous retourne null

Code :

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

SELECT OVERLAPS(CAST('2008-07-23' as DATE),CAST(NULL AS INTERVAL), CAST('2008-07-24' as DATE),CAST(NULL AS INTERVAL))

Qu'en pensez-vous ?

Alexandre

PS : Est-ce que les colonnes calculées sont conformes à la norme ? Si oui, soit j'ai mal cherché soit cette fonctionnalité sera rapidement ajoutée.