Discussion :

[DarckCrystale]

Bonjour à tous et à toutes.
Aujourd'hui, je dois fabriquer une BDD qui entre autre gère des services (programmes) installés sur des machines (serveurs).

Les règles de gestion sont :
un service peut être hébergé sur plusieurs machines,
une machine peut héberger plusieurs services,
les associations services-machine peuvent être multiples.

Ci-dessous, d'abord ce qui existe, puis ce que je voudrais faire.


J'aimerais savoir lequel des deux à votre avis est l'idéal.

Merci pour votre attention !

EDIT :
J'ai parcouru vos débats sur la justification de l'identification relative. Sachez que dans mon cas, les instances seront utilisées ensuite dans d'autres tables en tant que clés primaires.

C'est surtout vis à vis des difficultés engendrées par l'identification relative que je me pose la question. Je ne sais pas non plus s'il s'agit d'une "bonne pratique" ou de quelque chose de reconnu dans le métier.

[Richard_35]

Bonjour DarckCrystale,

Deux remarques :

• la clé primaire d'une entité ne doit pas comporter de signification : créer un identifiant en n° automatique ;
• le nom d'une entité doit rester au singulier.

D'autre part, je pense qu'il manque une table associative entre Machine, Service et Instance.

Suggestion :

un service peut être hébergé sur plusieurs machines,
une machine peut héberger plusieurs services,

==>

les associations services-machine peuvent être multiples.

==>


Je te laisse en définir les tables qui en découlent.

[DarckCrystale]

Bonjour Richard.

Deux remarques :

• la clé primaire d'une entité ne doit pas comporter de signification : créer un identifiant en n° automatique ;
• le nom d'une entité doit rester au singulier.

Ces règles me sont imposées, je ne peux malheureusement pas changer l'opinion personnelle de mon maître de stage sur le deuxième point. Le premier par contre était utilisé dans un soucis de clareté vis à vis des tests réalisés sur l'application utilisant cette base de données.

D'autre part, je pense qu'il manque une table associative entre Machine, Service et Instance.

Je ne suis pas sûre qu'une table intermédiaire soit utile. C'est justement pour éviter de la fabriquer que j'utiliserais un identifiant relatif.

(désolée si je parais nier des règles de bon sens, mais je suis plutôt malhabile avec les bases de données en général, et bien plus habituée au modèle relationnel qu'au modèle merise -sans parler du fait que je ne connais même pas l'uml - donc n'hésitez pas à m'expliquer en détaillant : je comprends vite, il faut juste m'expliquer lentement)

[fsmrel]

Bonsoir DarckCrystale,

C'est surtout vis à vis des difficultés engendrées par l'identification relative que je me pose la question. Je ne sais pas non plus s'il s'agit d'une "bonne pratique" ou de quelque chose de reconnu dans le métier.

Pourriez-vous énumérer les difficultés engendrées par l'identification relative que vous avez rencontrées ?

Cela dit, l’identification relative fait partie de Merise 2 :

Extrait du document afcet - Le formalisme de données Merise - Extensions du pouvoir d’expression - Journée d’étude organisée par le Groupe de Travail 135 « Conception des systèmes d’information » (Collège AFCET-GID) - Jeudi 15 novembre 1990, Paris.


Voyez aussi la FAQ Merise (rédigée par un des fondateurs de Merise, Dominique Nanci).

Ou encore la relation de composition dans les diagrammes de classes UML.

Si l’identification relative est donc formellement décrite dans Merise au niveau conceptuel, dès qu’on passe à la théorie relationnelle ça n’est pas le cas, c'est le mutisme total (voyez par exemple la référence incontournable An Introduction to Database Systems (8th Edition) de C.J. Date).

Néanmoins, certains outils comme MySQL Workbench (de niveau « relationnel » pour faire court) connaissent et proposent le concept :



L’identification relative permet souvent de résoudre le problème de la contrainte de chemin (illustré avec l’exemple du mélange de la motorisation et de la finition des modèles Renault et Peugeot).

Au niveau physique, un prototypage des performances peut conduire à la propagation de l’identification relative (voyez ici). Mais bien entendu, l’identification relative a ses détracteurs farouches, sinon ça ne serait pas drôle...

Les associations services-machine peuvent être multiples.

Est-ce à dire qu’un service peut être installé plus d’une fois sur la même machine ?

je suis plutôt malhabile avec les bases de données en général, et bien plus habituée au modèle relationnel qu'au modèle merise

D’accord, on va donc répondre tranquillement. Pour ma part, j’attends déjà que vous répondiez à la question que je viens de poser.

[DarckCrystale]

Bonjour fsmrel,

Je n'ai sûrement pas utilisé le bon terme quant à la modélisation. Je ne sais pas s'il existe un terme pour le désigner.
Exemple :
t_services(nom, label, desc)
t_machines(id, label, desc)
t_instances(id, desc, #nomservice, #idmachine)
ou
t_instances(#idmachine, #nomservice, numero, desc)
Avec les # signifiant une clé étrangère (ici, les relations sont explicites).

Pourriez-vous énumérer les difficultés engendrées par l'identification relative que vous avez rencontrées ?

Tout d'abord : les outils qui enregistreront les lignes dans la BDD transmettent leurs données via une url. Donc soit il faut faire du traitement sur une chaîne de caractères pour en extraire les clés, soit il faut séparer les clés dans l'url. Mais dans tous les cas, ça demande un peu plus de traitement.
Ce n'est pas moi qui m'occupe actuellement de ça.

Ensuite : la base de données va voir son schéma différer totalement de ce qui existait déjà. Pour plus d'éclaircissements :
t_services(nom, label, desc)
t_sections(nom, label, desc, #nomservice) //un service renvoit un json -par exemple- et une section est une entité qui permet de 'scinder' le service : une section n'aura qu'un seul champ du json qui lui sera associé, mettons pour SEC01 le champ "connexionsSite" et pour SEC02 le champ "espaceDisk".
t_machines(id, label, desc)
t_instances(#idmachine, #nomservice, numero, desc) //si on part avec l'identification relative, voici un aperçu d'une autre table.
t_borner(#(#idmachine, #nomservice, numero), #nomsection, mini, maxi)
Avec les # signifiant une clé étrangère (ici, les relations sont explicites).

Or je ne sais pas créer de contraintes de clés primaires comme pour t_borner (sur n'importe quel SGBD en général), et ça, c'est à moi de m'en occuper. On travaille sous PostgreSQL en local, et j'utilise le phpPgAdmin et PgAdmin III.

Ce sont les principales difficultés posées par ce type de clé primaire.

Est-ce à dire qu’un service peut être installé plus d’une fois sur la même machine ?

Oui, car partant du principe que c'est un programme, on peut l'installer avec différentes configuration.

Je rajouterais enfin que la forme des id n'est pas fixée, c'est à dire qu'on peut avoir pour les id des instances non pas quelque chose comme sur la figure ci-dessous, mais un très gros nombre (522161041041023000, 646650120460327000, 882161088711199000, 844650511045846000, 587465445441023000 par exemple)




Au vu de ces éclaircissement, une identification relative est-elle justifiée ?

[Richard_35]

Bonjour à tous,

DarckCrystale, dans t_instances, la section n'est-elle pas remontée ?

[fsmrel]

Bonsoir,


Avant toute autre chose, il s’agit d’utiliser une méthode correcte d’identification. Combien ai-je vu d’applications devoir être reprises à zéro à cause d’un système d’identification calamiteux... Bon, d’accord, ça m’a permis dans bien des projets dans les entreprises d’être moteur dans la modélisation de bases de données remises à plat, intellectuellement c’est épatant, mais quand même...


D’une manière générale, un identifiant (par voie de conséquence une clé primaire en SQL) doit être non significatif et invariant. Par exemple si un login est utilisé pour une clé primaire, il n’en demeure pas moins qu’il peut être modifié (ça arrive même chez Developpez.com...) et alors bonjour les problèmes si des clés étrangères y font référence.

Il y a plus de 25 ans, l’excellentissime Yves Tabourier a écrit ceci (De l’autre côté de MERISE, page 80), et c’est une règle d’or qui a quand même plus de trente ans mais reste malheureusement trop souvent méconnue :

« ... la fonction d’une propriété est de décrire les objets (et les rencontres), alors que l’identifiant ne décrit rien. Son rôle fondamental est d’être sûr de distinguer deux jumeaux parfaits, malgré des descriptions identiques.
L’expérience montre d’ailleurs que l’usage des “identifiants significatifs” (ou “codes significatifs”) a pu provoquer des dégâts tellement coûteux que la sagesse est d’éviter avec le plus grand soin de construire des identifiants décrivant les objets ou, pis encore, leurs liens avec d’autres objets... »

Remontons le passé et situons-nous par exemple en 2005. Imaginez une application dans laquelle la clé primaire de la table VEHICULE est {NoImmat} où NoImmat représente le numéro d’immatriculation des véhicules en France : chaque véhicule est identifié par son immatriculation. Comme par hasard, un développeur livre des programmes dans lesquels il se sert des deux chiffres de droite pour tirer des conclusions sur la localisation du véhicule dans quelque département. En 2009, un nouveau système d’immatriculation est mis en place, celui qu’on utilise aujourd’hui en France. Quelles conséquences sur les programmes existants ? Sur la base de données ? (En effet, il se peut que plusieurs tables référencent la table VEHICULE par le mécanisme des clés étrangères...)

Dans le même ordre d’idées, je me souviens des affres vécus par les informaticiens chez les opérateurs téléphoniques lors du passage à la numérotation de 8 à 10 chiffres...

Un exemple dont je me sers de temps en temps : il s’agit du système préconisé par les concepteurs dans une très grande banque, consistant à identifier les entreprises par leur numéro Siren, fourni par un organisme extérieur, à savoir l’INSEE. Par voie de conséquence, au niveau du MLD (Modèle Logique de Données selon Merise), puis SQL, ce Siren devait être propagé dans toute la base de données par le jeu des liens clé primaire - clé étrangère. Les concepteurs avaient entrepris le montage d’une véritable usine à gaz pour maintenir la cohérence entre les tables, parce que l’INSEE envoyait tous les mois les nombreux correctifs modifiant les numéros de Siren erronés. Tout en leur commentant l’ouvrage d’Y. Tabourier, je leur suggérai que l’identifiant de l’entreprise fît l’objet d’une propriété nouvelle et artificielle, invariante et sans signification, EntrepriseId, le numéro Siren devenant pour sa part identifiant alternatif, c'est-à-dire conservant sa spécificité : être unique et pouvant subir toutes les modifications de la part de l’utilisateur. Au niveau MLD et SQL, l’ex clé primaire, le numéro Siren fit donc l’objet d’une clé alternative, point d'entrée dans la table, permettant à l'utilisateur d’accéder aux données de chaque entreprise, la suite des opérations se passant de façon transparente, encapsulée pour utiliser un jargon à la mode, grâce à la nouvelle clé primaire {EntrepriseId} prenant le relais de sa copine clé alternative : du point de vue de l’utilisateur et des règles fonctionnelles, rien de changé, mais l’usine à gaz disparut en fumée, des économies furent réalisées, et tout le monde fut content.

Je n'ai sûrement pas utilisé le bon terme quant à la modélisation. Je ne sais pas s'il existe un terme pour le désigner.
Exemple :
t_services(nom, label, desc)
t_machines(id, label, desc)
t_instances(id, desc, #nomservice, #idmachine)
ou
t_instances(#idmachine, #nomservice, numero, desc)
Avec les # signifiant une clé étrangère (ici, les relations sont explicites).

Je suppose que vous voulez parler de la pertinence de l’emploi ici du terme « modélisation ». Si c’est cela, vous pouvez plutôt utiliser l’expression « notation » ou « convention de notation » ou « mode de représentation », etc. En tant que telle, la modélisation est plutôt un type activité : on modélise et l’on représente le résultat de cette activité selon une notation convenue.

les outils qui enregistreront les lignes dans la BDD transmettent leurs données via une url. Donc soit il faut faire du traitement sur une chaîne de caractères pour en extraire les clés, soit il faut séparer les clés dans l'url. Mais dans tous les cas, ça demande un peu plus de traitement.

Ça sent la bidouille qui mène aux dégâts que dénonce Tabourier...

la base de données va voir son schéma différer totalement de ce qui existait déjà. Pour plus d'éclaircissements :
t_services(nom, label, desc)
t_sections(nom, label, desc, #nomservice) //un service renvoit un json -par exemple- et une section est une entité qui permet de 'scinder' le service : une section n'aura qu'un seul champ du json qui lui sera associé, mettons pour SEC01 le champ "connexionsSite" et pour SEC02 le champ "espaceDisk".
t_machines(id, label, desc)
t_instances(#idmachine, #nomservice, numero, desc) //si on part avec l'identification relative, voici un aperçu d'une autre table.
t_borner(#(#idmachine, #nomservice, numero), #nomsection, mini, maxi)

Que présentez-vous ici ? L’existant ? Le futur ?
Si j’ai bien compris, un service est plutôt un logiciel (un programme) et non pas une entité, un département de l’entreprise. L’en-tête de votre table t_services a pour éléments les attributs : nom, label, desc. Vu vos exemples, je suppose que l’attribut nom représente plutôt un code : "APA01", "APA02", "DSK01", etc., mais quelle est la définition des attributs label et desc ? (merci de donner des exemples).

Pour les sections, je redoute le pire. Pourriez-vous donner une définition compréhensible pour le profane que je suis eu égard à l’activité de l’entreprise ? Qu’est-ce que ce « json » bricolé dans tous les sens ? C’est un peu comme si je vous disais : « on va scinder le service en passant par un SVC 254 » : ça cause au programmeur système IBM mainframe, mais pour les autres pauvres mortels c'est forcément clair comme de l'eau de boudin...

Où sont représentés les champs attributs connexionsSite, espaceDisk ?

je ne sais pas créer de contraintes de clés primaires comme pour t_borner (sur n'importe quel SGBD en général), et ça, c'est à moi de m'en occuper. On travaille sous PostgreSQL en local, et j'utilise le phpPgAdmin et PgAdmin III.

On va arranger cela. Mais ne parlez-pas de choses (phpPgAdmin et PgAdmin) qui n’ont rien à voir avec la modélisation des données.

Je pense que j’aurai le temps de vous proposer une ébauche de prototype en SQL pour vous montrer comment réaliser l’identification (j’utilise SQL Server, mais l’adaptation à PostgreSQL ne devrait pas poser de problèmes). Au préalable, sans avoir tout compris de vos intentions, voici une ébauche de représentation avec MySQL Workbench de ce que je pense avoir saisi :




Vos noterez que SECTION est identifiée relativement à SERVICE. Cela permet de résoudre un problème de contrainte de chemin (voyez mon précédent message) concernant BORNER : le service déterminé par les deux pattes de branchement sur INSTANCE et SECTION doit être le même.

Je rajouterais enfin que la forme des id n'est pas fixée

Vous parlez d’« id » alternatifs. Les Id du diagramme seront conformes aux prescriptions de Tabourier, méditez le coup du Siren des entreprises.


On ne lâche rien !

[DarckCrystale]

Avant toute autre chose [...] et tout le monde fut content.

Nous sommes tout à fait d'accord sur ce point, c'est d'ailleurs dans ce souci que je me suis inscrite ici.

Ça sent la bidouille qui mène aux dégâts que dénonce Tabourier...

Comme je l'ai précisé dans mon post précédent, je ne m'occupe pas de cette partie du projet. Je n'ai donc aucune prise sur ces contraintes (et aucune envie de les gérer non plus). C'est à dire que j'aimerais avoir une base solide avant de m'éparpiller en multiples activités.

Je ne peux donc pas donner plus de renseignements que ceux que j'ai (donc que les paramètres sont passés par url).

Que présentez-vous ici ? L’existant ? Le futur ?

J'ai représenté ce que je pensais faire avant de demander conseil.
Pour le reste de cette note, on prendra pour postulat que la base de données est :

t_services(nom, label, desc)
t_sections(nom, label, desc, #nomservice)
t_machines(id, label, desc)
t_instances(id, #idmachine, #nomservice, desc)
t_borner(#idinstance, #nomsection, mini, maxi)
t_brute(#idinstance, date, json)
t_valeur(#nomsection, date, valeur)

J'explique tout au long de mon message les détails.

Si j’ai bien compris, un service est plutôt un logiciel (un programme) et non pas une entité, un département de l’entreprise. L’en-tête de votre table t_services a pour éléments les attributs : nom, label, desc. Vu vos exemples, je suppose que l’attribut nom représente plutôt un code : "APA01", "APA02", "DSK01", etc., mais quelle est la définition des attributs label et desc ? (merci de donner des exemples).

Un service est effectivement un programme, un webservice pour être plus précise.
- nom serait un numéro quelconque et unique // les noms DSK01, APA01, etc.. étaient, je le précise à nouveau, donnés pour pouvoir debuger l'application exploitant cette base de données, et non pas à l'image des codes siren ou numéros de téléphone !
- label serait un titre (par exemple "Espace disque")
- description serait une description brève du service (par exemple "Ce service renvoie l'espace disque disponible sur les différents disques d'un serveur, ainsi que l'espace total disponible sur le serveur.")

Pour les sections, je redoute le pire. Pourriez-vous donner une définition compréhensible pour le profane que je suis eu égard à l’activité de l’entreprise ? Qu’est-ce que ce « json » bricolé dans tous les sens ? C’est un peu comme si je vous disais : « on va scinder le service en passant par un SVC 254 » : ça cause au programmeur système IBM mainframe, mais pour les autres pauvres mortels c'est forcément clair comme de l'eau de boudin...

Un service est donc un webservice, qui renvoie un fichier. Ce fichier est un json (truc.json) : le json est une manière d'encoder des données qui ressemble au XML, si vous connaissez, mais en un peu moins lourd.

en XML :
<mamifère>

<chat>

Raminagrobis

</chat>

</mamifère>

en json :
{

"mamifère": {

"chat" : "Raminagrobis"

}

}

On a moins de caractères en json qu'en xml pour les balises. Et c'est normal que ça ressemble à du javascript car json est l'acronyme de JavaScript Object Notation.

Dans tout ça, une section, c'est un "morceau" de webservice. Par exemple :
Le service (45002, "Espace Disque", "Ce service renvoie l'espace disque disponible sur les différents disques d'un serveur, ainsi que l'espace total disponible sur le serveur.")
Va être 'scindé' en deux sections :
section (00001, "Espace libre disque C:", "Cette section enregistre l'espace libre sur le disque C: d'une machine", 45002)
section (00002, "Espace libre total", "Cette section enregistre l'espace libre total sur une machine", 45002)

Un service revoie 'plusieurs données', tandis qu'une section ne renvoie 'qu'un seul type de données'.

Où sont représentés les champs attributs connexionsSite, espaceDisk ?

J'ai l'impression de parler chinois en me relisant -_- un autre exemple, et oubliez ces deux champs.

Le service (45002, "Espace Disque", "Ce service renvoie l'espace disque disponible sur les différents disques d'un serveur, ainsi que l'espace total disponible sur le serveur.")
a renvoyé ce json :
{

"date": "2013-06-25 03:05:59"

}
{

"service": "45002"

}
{

"section": {

"00001" : "75001310"
"00002" : "90111284100356411"

}

}

Ce json est enregistré dans la table suivante:
t_brute(#idinstance, date, json)

On voit clairement les id des sections se distinguer, ci-dessus en rouge.
Les données qui correspondent à ces sections seront extraites par un script et enregistrées dans la table :
t_valeur(#idsection, date, valeur)

Ici, ça créera les deux lignes suivantes :
00001, 2013-06-25 03:05:59, 75001310
00002, 2013-06-25 03:05:59, 90111284100356411

Et ces données numérique, je les utilise pour fabriquer des graphiques, dans une application web.

Vos noterez que SECTION est identifiée relativement à SERVICE. Cela permet de résoudre un problème de contrainte de chemin (voyez mon précédent message) concernant BORNER : le service déterminé par les deux pattes de branchement sur INSTANCE et SECTION doit être le même.

Ce qui est une excellente idée, mais ensuite ça va me créer des colonnes :
t_valeur(#idsection, date, valeur)
devient alors
t_valeur(#nomservice, #idsection, date, valeur)

Et là, on a quantité de lignes. Parce qu'un service envoie un json par quart d'heure, dont on extrait n valeurs enregistrées : avec en moyenne 4 sections par service, ça fait beaucoup surtout que tout ça tournera tant que l'entreprise ne fait pas faillite.
Pour un seul service sur une seule machine, avec une seule configuration (l'instance), le faire tourner pendant un an, ça fait déjà plus de 35.000 lignes dans t_brute, donc plus de 140.000 lignes dans t_valeur. Une colonne en rab ne coûterait donc-t-elle pas au final plus qu'elle n'apporte ?

Vous parlez d’« id » alternatifs. Les Id du diagramme seront conformes aux prescriptions de Tabourier, méditez le coup du Siren des entreprises.

J'imagine donc que t_section gagnera un champ, pour que l'id identifiant la section dans le json soit différent de l'id identifiant la section dans la base de données, n'est-il pas ?

Merci beaucoup pour le temps que vous passez à me répondre !

[fsmrel]

Bonsoir,

J'imagine donc que t_section gagnera un champ, pour que l'id identifiant la section dans le json soit différent de l'id identifiant la section dans la base de données, n'est-il pas ?

Plaît-il ? Je ne suis pas bien sûr de bien capter ce que vous voulez dire par « gagner un champ » (en notant que « champ » est un terme réservé pour structurer les fichiers, en relationnel on utilise le terme « attribut » et en SQL de préférence le terme « colonne »)...

Cela dit, je procède à la dérivation en SQL du diagramme que j’ai proposé. Comme j’utilise ici MS SQL Server, je lui sous-traite l’incrémentation des clés primaires (mot-clé IDENTITY), à adapter avec PostgreSQL (type SERIAL ?)

Notez les clés alternatives dont l’utilisateur peut faire ce qu’il veut (MachineCode, ServiceCode, SdectionCode, InstanceCode).

TABLE MACHINE

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE MACHINE
 (
        MachineId        INT            NOT NULL  IDENTITY,
        MachineCode      CHAR(5)        NOT NULL,
        MachineLibelle   VARCHAR(45)    NOT NULL,
    CONSTRAINT MACHINE_PK PRIMARY KEY (MachineId),  
    CONSTRAINT MACHINE_AK UNIQUE (MachineCode)  
) ;

TABLE SERVICE

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE SERVICE
(
        ServiceId        INT            NOT NULL  IDENTITY,
        ServiceCode      CHAR(5)        NOT NULL,
        ServiceLibelle   VARCHAR(45)    NOT NULL,
    CONSTRAINT SERVICE_PK PRIMARY KEY (ServiceId),  
    CONSTRAINT SERVICE_AK UNIQUE (ServiceCode) 
) ;

TABLE SECTION

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE SECTION
(
        ServiceId        INT            NOT NULL,
        SectionId        INT            NOT NULL,
        SectionCode      CHAR(5)        NOT NULL,
        SectionLibelle   VARCHAR(45)    NOT NULL,
    CONSTRAINT SECTION_PK PRIMARY KEY (ServiceId, SectionId),
    CONSTRAINT SECTION_SERVICE_FK FOREIGN KEY (ServiceId) REFERENCES SERVICE (ServiceId)
) ;

TABLE INSTANCE

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE INSTANCE 
(
        ServiceId        INT            NOT NULL,
        MachineId        INT            NOT NULL,
        InstanceId       INT            NOT NULL,
        InstanceCode     VARCHAR(45)    NOT NULL,
        InstanceLibelle  VARCHAR(45)    NOT NULL,
    CONSTRAINT INSTANCE_PK PRIMARY KEY (ServiceId, MachineId, InstanceId),
    CONSTRAINT INSTANCE_MACHINE_FK FOREIGN KEY (MachineId) REFERENCES MACHINE (MachineId),
    CONSTRAINT INSTANCE_SERVICE_FK FOREIGN KEY (ServiceId) REFERENCES SERVICE (ServiceId)
) ;

TABLE BORNER

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE BORNER 
(
        ServiceId        INT            NOT NULL,
        MachineId        INT            NOT NULL,
        InstanceId       INT            NOT NULL,
        SectionId        INT            NOT NULL,
        Mini             INT            NOT NULL,
        Maxi             INT            NOT NULL,
    CONSTRAINT BORNER_PK PRIMARY KEY (ServiceId, MachineId, InstanceId, SectionId),
    CONSTRAINT BORNER_INSTANCE_FK FOREIGN KEY (ServiceId, MachineId, InstanceId) REFERENCES INSTANCE (ServiceId, MachineId, InstanceId),
    CONSTRAINT BORNER_SECTION_FK  FOREIGN KEY (ServiceId, SectionId) REFERENCES SECTION (ServiceId, SectionId)
) ;

C'est parti pour un zest de début de jeu d’essai.

Quelques machines (observez que le SGBD se charge de valoriser MachineId) :

Code SQL :

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

1
2
3
4
5
6
 
INSERT INTO MACHINE (MachineCode, MachineLibelle) VALUES ('MCH01', 'Le grand biglotron') ;
INSERT INTO MACHINE (MachineCode, MachineLibelle) VALUES ('MCH02', 'Le schmilblick') ;
INSERT INTO MACHINE (MachineCode, MachineLibelle) VALUES ('MCH03', 'L’ordinateur géant') ;
 
SELECT '' AS 'MACHINE', * FROM MACHINE ;

=>

MachineId    MachineCode    MachineLibelle
---------    -----------    ------------------
        1    MCH01          Le grand biglotron
        2    MCH02          Le schmilblick
        3    MCH03          L’ordinateur géant 

Quelques services :

Code SQL :

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

1
2
3
4
5
6
7
 
INSERT INTO SERVICE (ServiceCode, ServiceLibelle) VALUES ('APA01', 'Le service APA 1') ;
INSERT INTO SERVICE (ServiceCode, ServiceLibelle) VALUES ('APA02', 'Le service APA 2') ;
INSERT INTO SERVICE (ServiceCode, ServiceLibelle) VALUES ('DSK01', 'Le service disque 1') ;
INSERT INTO SERVICE (ServiceCode, ServiceLibelle) VALUES ('DSK02', 'Le service disque 2') ;
 
SELECT '' AS 'SERVICE', * FROM SERVICE ;

=>

ServiceId    ServiceCode    ServiceLibelle
---------    -----------    -------------------
        1    APA01          Le service APA 1
        2    APA02          Le service APA 2
        3    DSK01          Le service disque 1
        4    DSK02          Le service disque 2 

Quelques sections :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
INSERT INTO SECTION (ServiceId, SectionId, SectionCode, SectionLibelle)
    SELECT (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'APA01'), 
           (SELECT COALESCE(MAX(SectionId), 0) + 1
            FROM   SERVICE AS x JOIN SECTION AS y ON x.ServiceId = y.ServiceId
            WHERE  x.ServiceCode = 'APA01'), 
           'SEC01', 'Section 1' ;
INSERT INTO SECTION (ServiceId, SectionId, SectionCode, SectionLibelle)
    SELECT (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'APA01'), 
           (SELECT COALESCE(MAX(SectionId), 0) + 1
            FROM   SERVICE AS x JOIN SECTION AS y ON x.ServiceId = y.ServiceId
            WHERE  x.ServiceCode = 'APA01'), 
           'SEC02', 'Section 2' ;
INSERT INTO SECTION (ServiceId, SectionId, SectionCode, SectionLibelle)
    SELECT (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'APA01'), 
           (SELECT COALESCE(MAX(SectionId), 0) + 1
            FROM   SERVICE AS x JOIN SECTION AS y ON x.ServiceId = y.ServiceId
            WHERE  x.ServiceCode = 'APA01'), 
           'SEC03', 'Section 3' ;
INSERT INTO SECTION (ServiceId, SectionId, SectionCode, SectionLibelle)
    SELECT (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'APA02'), 
           (SELECT COALESCE(MAX(SectionId), 0) + 1 
            FROM   SERVICE AS x JOIN SECTION AS y ON x.ServiceId = y.ServiceId
            WHERE  x.ServiceCode = 'APA02'), 
           'SEC01', 'Section 1' ;
INSERT INTO SECTION (ServiceId, SectionId, SectionCode, SectionLibelle)
    SELECT (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'DSK01'), 
           (SELECT COALESCE(MAX(SectionId), 0) + 1
            FROM   SERVICE AS x JOIN SECTION AS y ON x.ServiceId = y.ServiceId
            WHERE  x.ServiceCode = 'DSK01'), 
           'SEC01', 'Section 1' ;
INSERT INTO SECTION (ServiceId, SectionId, SectionCode, SectionLibelle)
    SELECT (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'DSK01'), 
           (SELECT COALESCE(MAX(SectionId), 0) + 1
            FROM   SERVICE AS x JOIN SECTION AS y ON x.ServiceId = y.ServiceId
            WHERE  x.ServiceCode = 'DSK01'), 
           'SEC02', 'Section 2' ;
 
SELECT '' AS 'SECTION', * FROM SECTION ;

=>

ServiceId    SectionId    SectionCode    SectionLibelle
---------    ---------    -----------    --------------
        1            1    SEC01          Section 1
        1            2    SEC02          Section 2
        1            3    SEC03          Section 3
        2            1    SEC01          Section 1
        3            1    SEC01          Section 1
        3            2    SEC02          Section 2 

Quelques instances :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
INSERT INTO INSTANCE (ServiceId, MachineId, InstanceId, InstanceCode, InstanceLibelle)
    SELECT (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'APA01'),
           (SELECT MachineId FROM MACHINE WHERE MachineCode = 'MCH02'), 
           (SELECT COALESCE(MAX(InstanceId), 0) + 1
            FROM   SERVICE AS x JOIN INSTANCE AS y ON x.ServiceId = y.ServiceId
                                JOIN MACHINE AS z ON y.MachineId = z.MachineId
            WHERE  x.ServiceCode = 'APA01' AND z.MachineCode = 'MCH02'), 
           'INST01', 'Instance 1' ;
INSERT INTO INSTANCE (ServiceId, MachineId, InstanceId, InstanceCode, InstanceLibelle)
    SELECT (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'APA01'),
           (SELECT MachineId FROM MACHINE WHERE MachineCode = 'MCH02'), 
           (SELECT COALESCE(MAX(InstanceId), 0) + 1
            FROM   SERVICE AS x JOIN INSTANCE AS y ON x.ServiceId = y.ServiceId
                                JOIN MACHINE AS z ON y.MachineId = z.MachineId
            WHERE  x.ServiceCode = 'APA01' AND z.MachineCode = 'MCH02'), 
           'INST02', 'Instance 2' ;
INSERT INTO INSTANCE (ServiceId, MachineId, InstanceId, InstanceCode, InstanceLibelle)
    SELECT (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'APA01'),
           (SELECT MachineId FROM MACHINE WHERE MachineCode = 'MCH03'), 
           (SELECT COALESCE(MAX(InstanceId), 0) + 1
            FROM   SERVICE AS x JOIN INSTANCE AS y ON x.ServiceId = y.ServiceId
                                JOIN MACHINE AS z ON y.MachineId = z.MachineId
            WHERE  x.ServiceCode = 'APA01' AND z.MachineCode = 'MCH03'), 
           'INST01', 'Instance 1' ;
 
SELECT '' AS 'INSTANCE', * FROM INSTANCE ;

=>

ServiceId    MachineId    InstanceId    InstanceCode    InstanceLibelle
---------    ---------    ----------    ------------    ---------------
        1            2             1    INST01          Instance 1
        1            2             2    INST02          Instance 2
        1            3	           1    INST01          Instance 1 

Dans tout cela, on retiendra que c’est le SGBD qui calcule les valeurs des clés primaires. De son côté, l’utilisateur a ses propres clés (qui sont donc alternatives) dont il peut faire ce qu’il veut, en échange de quoi on ne lui montre pas les clés primaires. A quoi ressemble la table MACHINE pour lui ? Il n’en connaîtra qu’une vue contenant les attributs qui lui sont nécessaires et suffisants :

Code SQL :

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

1
2
3
4
CREATE VIEW MACHINE_V (MachineCode, MachineLibelle) 
AS
    SELECT MachineCode, MachineLibelle
    FROM   MACHINE ;

Vue pour les services :

Code SQL :

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

1
2
3
4
CREATE VIEW SERVICE_V (ServiceCode, ServiceLibelle) 
AS
    SELECT ServiceCode, ServiceLibelle
    FROM   SERVICE ;

Vue pour les sections :

Code SQL :

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

1
2
3
4
CREATE VIEW SECTION_V (ServiceCode, SectionCode, SectionLibelle) 
AS
    SELECT ServiceCode, SectionCode, SectionLibelle
    FROM   SERVICE AS x JOIN SECTION AS y ON x.ServiceId = y.ServiceId ;

Vue pour les instances :

Code SQL :

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

1
2
3
4
5
CREATE VIEW INSTANCE_V (ServiceCode, MachineCode, InstanceCode, InstanceLibelle) 
AS
    SELECT ServiceCode, MachineCode, InstanceCode, InstanceLibelle
    FROM   SERVICE AS x JOIN INSTANCE AS y ON x.ServiceId = y.ServiceId 
                        JOIN MACHINE AS z ON y.MachineId = z.MachineId ;

Vue pour les bornes :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE VIEW BORNER_V (ServiceCode, MachineCode, InstanceCode, SectionCode, Mini, Maxi) 
AS
    SELECT ServiceCode, MachineCode, InstanceCode, SectionCode, Mini, Maxi
    FROM   SERVICE AS x JOIN INSTANCE AS y ON x.ServiceId = y.ServiceId 
                        JOIN MACHINE AS z  ON y.MachineId = z.MachineId 
                        JOIN BORNER AS t   ON y.ServiceId = t.ServiceId 
                                          AND y.MachineId = t.MachineId 
                                          AND y.InstanceId = t.InstanceId
                        JOIN SECTION AS u  ON t.ServiceId = u.ServiceId
                                          AND t.SectionId = u.SectionId ;

Exploitation des vues par les utilisateurs :

Code SQL :

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

1
2
SELECT MachineCode, MachineLibelle
FROM   MACHINE_V ;

=>

MachineCode    MachineLibelle
-----------    ------------------
      MCH01    Le grand biglotron
      MCH02    Le schmilblick
      MCH03    L’ordinateur géant 

Code SQL :

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

1
2
SELECT ServiceCode, ServiceLibelle
FROM   SERVICE_V ;

=>

ServiceCode    ServiceLibelle
-----------    -------------------
APA01          Le service APA 1
APA02          Le service APA 2
DSK01          Le service disque 1
DSK02          Le service disque 2 

Code SQL :

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

1
2
SELECT ServiceCode, SectionCode, SectionLibelle
FROM   SECTION_V ;

=>

ServiceCode    SectionCode    SectionLibelle
-----------    -----------    --------------
APA01          SEC01          Section 1
APA01          SEC02          Section 2
APA01          SEC03          Section 3
APA02          SEC01          Section 1
DSK01          SEC01          Section 1
DSK01          SEC02          Section 2 

Code SQL :

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

1
2
SELECT ServiceCode, MachineCode, InstanceCode, InstanceLibelle
FROM   INSTANCE_V ;

=>

ServiceCode    MachineCode    InstanceCode    InstanceLibelle
-----------    -----------    ------------    ---------------
APA01          MCH02          INST01          Instance 1
APA01          MCH02          INST02          Instance 2
APA01          MCH03          INST01          Instance 1

Code SQL :

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

1
2
SELECT ServiceCode, MachineCode, InstanceCode, SectionCode, Mini, Maxi
FROM   BORNER_V ;

=>

ServiceCode    MachineCode    InstanceCode   SectionCode    Mini    Maxi
-----------    -----------    ------------   -----------    ----    ----
APA01          MCH02          INST01         SEC01            10      20 

Dans tout cela, l’utilisateur n’a jamais vu une clé primaire, seulement ses clés à lui.

Le service (45002, "Espace Disque", "Ce service renvoie l'espace disque disponible sur les différents disques d'un serveur, ainsi que l'espace total disponible sur le serveur.")
a renvoyé ce json :
{

"date": "2013-06-25 03:05:59"

}
{

"service": "45002"

}
{

"section": {

"00001" : "75001310"
"00002" : "90111284100356411"

}

}

Ce json est enregistré dans la table suivante:
t_brute(#idinstance, date, json)

On voit clairement les id des sections se distinguer, ci-dessus en rouge.
Les données qui correspondent à ces sections seront extraites par un script et enregistrées dans la table :
t_valeur(#idsection, date, valeur)

Ici, ça créera les deux lignes suivantes :
00001, 2013-06-25 03:05:59, 75001310
00002, 2013-06-25 03:05:59, 90111284100356411

En relation avec ce qui précède, le contenu du json n’est pas concerné par les clés primaires que j’ai proposées, mais seulement par les clés alternatives les seules à être visibles, « publiques », donc pour le json il n’y a rien de changé, c'est transparent.

Vos noterez que SECTION est identifiée relativement à SERVICE. Cela permet de résoudre un problème de contrainte de chemin (voyez mon précédent message) concernant BORNER : le service déterminé par les deux pattes de branchement sur INSTANCE et SECTION doit être le même.

Ce qui est une excellente idée, mais ensuite ça va me créer des colonnes :
t_valeur(#idsection, date, valeur)
devient alors
t_valeur(#nomservice, #idsection, date, valeur)

Et là, on a quantité de lignes. Parce qu'un service envoie un json par quart d'heure, dont on extrait n valeurs enregistrées : avec en moyenne 4 sections par service, ça fait beaucoup surtout que tout ça tournera tant que l'entreprise ne fait pas faillite.
Pour un seul service sur une seule machine, avec une seule configuration (l'instance), le faire tourner pendant un an, ça fait déjà plus de 35.000 lignes dans t_brute, donc plus de 140.000 lignes dans t_valeur. Une colonne en rab ne coûterait donc-t-elle pas au final plus qu'elle n'apporte ?

Si vous êtes sûre de ne pas dépasser 32767 services, alors avec le système que je propose, la colonne utilisée pour la clé primaire de la table SERVICE (colonne ServiceId) nécessite 2 octets (type SMALLINT). Si vous pensez pouvoir dépasser 32767 services, alors ça sera 4 octets (type INT). A l’occasion de l’ajout de cette colonne dans l’en-tête de la table VALEUR, pour 140000 lignes, le surcoût sera au pire de 280000 ou 560000 octets (ce surcoût peut du reste être epsilonesque s’il reste de la place dans les enregistrements physiques), ce qui ne paraît quand même pas excessif (même chose pour les index). Je suppose aussi que vous avez prévu un système de purge de cette table.

En passant, quelle est l’occupation en octets des colonnes nomservice et idsection dans votre système ?

Et n’oubliez pas que l’identification de SECTION relativement à SERVICE vous fait économiser un mécanisme de contrôle (disons par trigger) garantissant que le service de la section et celui de l’instance sont bien le même.

Hasta luego!

[DarckCrystale]

Bonjour

Plaît-il ? Je ne suis pas bien sûr de bien capter ce que vous voulez dire par « gagner un champ » (en notant que « champ » est un terme réservé pour structurer les fichiers, en relationnel on utilise le terme « attribut » et en SQL de préférence le terme « colonne »)...

Vous l'avez fait dans votre démonstration : ajouter une colonne à la table section.

Cela dit, je procède à la dérivation en SQL du diagramme que j’ai proposé. Comme j’utilise ici MS SQL Server, je lui sous-traite l’incrémentation des clés primaires (mot-clé IDENTITY), à adapter avec PostgreSQL (type SERIAL ?)

Effectivement, le type sous PostgreSQL est bien SERIAL. De même, pour VARCHAR on a CHARACTER VARYING et pour INT, INTEGER. Mais pour ces deux derniers, PostgreSQL sait comprendre les requêtes et change les types tout seul.

Notez les clés alternatives dont l’utilisateur peut faire ce qu’il veut (MachineCode, ServiceCode, SdectionCode, InstanceCode).

Ne pas les remarquer eut été compliqué !

Quelques machines (observez que le SGBD se charge de valoriser MachineId)

Un avantage certain !

Quelques sections :

Code SQL :

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

1
2
3
4
5
6
INSERT INTO SECTION (ServiceId, SectionId, SectionCode, SectionLibelle)
    SELECT (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'APA01'), 
           (SELECT COALESCE(MAX(SectionId), 0) + 1
            FROM   SERVICE AS x JOIN SECTION AS y ON x.ServiceId = y.ServiceId
            WHERE  x.ServiceCode = 'APA01'), 
           'SEC01', 'Section 1' ;

Ci dessus, en rouge, à quoi sert cette sous-requête ?
(j'ai oublié de préciser, mais je ne suis qu'en première année de BTS informatique, ça fait donc moins d'un an que je pratique le développement en général, d'où les -très- grosses lacunes)

Quelques instances :

Code SQL :

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

1
2
3
4
5
6
7
8
INSERT INTO INSTANCE (ServiceId, MachineId, InstanceId, InstanceCode, InstanceLibelle)
    SELECT (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'APA01'),
           (SELECT MachineId FROM MACHINE WHERE MachineCode = 'MCH02'), 
           (SELECT COALESCE(MAX(InstanceId), 0) + 1
            FROM   SERVICE AS x JOIN INSTANCE AS y ON x.ServiceId = y.ServiceId
                                JOIN MACHINE AS z ON y.MachineId = z.MachineId
            WHERE  x.ServiceCode = 'APA01' AND z.MachineCode = 'MCH02'), 
           'INST01', 'Instance 1' ;

Même question, c'est à dire : que fait cette sous-requête ?

Dans tout cela, on retiendra que c’est le SGBD qui calcule les valeurs des clés primaires. De son côté, l’utilisateur a ses propres clés (qui sont donc alternatives) dont il peut faire ce qu’il veut, en échange de quoi on ne lui montre pas les clés primaires. A quoi ressemble la table MACHINE pour lui ? Il n’en connaîtra qu’une vue contenant les attributs qui lui sont nécessaires et suffisants :

Code SQL :

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

1
2
3
4
CREATE VIEW MACHINE_V (MachineCode, MachineLibelle) 
AS
    SELECT MachineCode, MachineLibelle
    FROM   MACHINE ;

Je ne connaissais pas ces requêtes ! (et ça a l'air juste super pratique)
Merci beaucoup

Vue pour les sections :

Code SQL :

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

1
2
3
4
CREATE VIEW SECTION_V (ServiceCode, SectionCode, SectionLibelle) 
AS
    SELECT ServiceCode, SectionCode, SectionLibelle
    FROM   SERVICE AS x JOIN SECTION AS y ON x.ServiceId = y.ServiceId ;

Je n'ai jamais utilisé JOIN. C'est une jointure ? D'ordinaire, on nous fait simplement écrire :

Code :

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

1
2
FROM SERVICE, SECTION
WHERE SERVICE.ServiceId = SECTION.ServiceId

ou encore :

Code :

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

1
2
FROM x.SERVICE, y.ECTION
WHERE x.ServiceId = y.ServiceId

Est-ce le même principe ? Qu'elle sont les différences, s'il y en a ?

Dans tout cela, l’utilisateur n’a jamais vu une clé primaire, seulement ses clés à lui.

En relation avec ce qui précède, le contenu du json n’est pas concerné par les clés primaires que j’ai proposées, mais seulement par les clés alternatives les seules à être visibles, « publiques », donc pour le json il n’y a rien de changé, c'est transparent.

C'est parfait !

Si vous êtes sûre de ne pas dépasser 32767 services, alors avec le système que je propose, la colonne utilisée pour la clé primaire de la table SERVICE (colonne ServiceId) nécessite 2 octets (type SMALLINT). Si vous pensez pouvoir dépasser 32767 services, alors ça sera 4 octets (type INT). A l’occasion de l’ajout de cette colonne dans l’en-tête de la table VALEUR, pour 140000 lignes, le surcoût sera au pire de 280000 ou 560000 octets (ce surcoût peut du reste être epsilonesque s’il reste de la place dans les enregistrements physiques), ce qui ne paraît quand même pas excessif (même chose pour les index). Je suppose aussi que vous avez prévu un système de purge de cette table.

Je me répète, mais mon niveau d'expérience ne me permet pas d'avoir d'excellentes connaissances en BDD. Donc non, je n'ai pas prévu de purge (et même sans dire que je ne sais pas comment en mettre en place, je ne sais pas non plus à quoi ça sert).

En passant, quelle est l’occupation en octets des colonnes nomservice et idsection dans votre système ?

A 5 caractères alphanumériques, on a cinq octets pour un nomservice et cinq autres pour un idsection.

Et n’oubliez pas que l’identification de SECTION relativement à SERVICE vous fait économiser un mécanisme de contrôle (disons par trigger) garantissant que le service de la section et celui de l’instance sont bien le même.

Je n'avais pas pensé à ce genre de contrôle. Et encore une fois, je ne connais pas ce nom (lu pour la première fois dans les liens que vous avez postés dans votre premier message).

[fsmrel]

Bonsoir DarckCrystale,

Je n'ai pas prévu de purge (et même sans dire que je ne sais pas comment en mettre en place, je ne sais pas non plus à quoi ça sert).

Purger consiste à supprimer de la base de données les données considérées comme obsolètes. C’est à la MOE (maîtrise d’œuvre) de fournir les conditions de purge. Ainsi, dans quelles conditions les tables VALEUR et BRUTE peuvent elles subir une cure d’amaigrissement ? En général, les purges ne sont pas effectuées toutes les secondes, on en détermine la fréquence : quotidienne, hebdomadaire, mensuelle, semestrielle, annuelle, etc. Cela conditionne souvent ce qu'on appelle le partitionnement des tables, mais cela se passe sous le capot, c'est du ressort du DBA, donc n'allons pas plus loin.

Je n'ai jamais utilisé JOIN. C'est une jointure ?

Au vu du mot employé : « JOIN », alors JOIN est bien une jointure...

Blague à part, le style qu’on vous a enseigné est celui d’origine (appelons-le « ancien style »), décrit il y a 40 ans par les inventeurs de SQL, Raymond Boyce et Donald Chamberlin (tous deux d’IBM). Voyez l’article SEQUEL: A Structured English Query Language à la page 11 du PDF (page 259 de la publication).

Le style que j’ai utilisé (appelons-le « nouveau style ») est celui qu’a proposé Chris Date il y a 30 ans (quand il était chez IBM), dans Proc. 2nd International Conference on Databases (M. Deen and P. Hammersley (Cambridge), copyright 1983 by Wiley Heyden Ltd). Ce style a été pris en compte il y a 20 ans dans la norme SQL:1992. Les éditeurs de SGBD s’y sont ensuite mis plus ou moins vite, par exemple IBM en 1995 (DB2 V4), tandis qu'Oracle a attendu 2001 (Oracle9i).

Quel que soit le style qu’on utilise, le résultat est le même (ouf !) Disons qu’avec le nouveau style on se sert directement de l’opérateur JOIN (ici c’est plus précisément un équi-join) :

FROM SERVICE JOIN SECTION ON SERVICE.ServiceId = SECTION.ServiceId

Alors qu’avec l'ancien style on utilise un produit cartésien :

FROM SERVICE, SECTION

Et une restriction :

WHERE SERVICE.ServiceId = SECTION.ServiceId

A charge du SGBD de se dépatouiller, ce qu’il fait sans problème ! On lui propose le QUOI sous la forme qu’on préfère et il se charge du COMMENT.

D'ordinaire, on nous fait simplement écrire...

On devrait vous enseigner les deux. Si l’on vous enseigne l'ancien style, c’est qu’on considère que le produit et la restriction font partie des opérateurs primitifs, mais pas la jointure. Cela dit, il n’est pas interdit de considérer la jointure comme opérateur primitif, tandis que le produit en est alors considéré comme la version dégénérée (absence de restriction, c'est-à-dire du WHERE dans l'ancien style).

Code SQL :

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

1
2
3
4
5
6
INSERT INTO SECTION (ServiceId, SectionId, SectionCode, SectionLibelle)
    SELECT (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'APA01'), 
           (SELECT COALESCE(MAX(SectionId), 0) + 1
            FROM   SERVICE AS x JOIN SECTION AS y ON x.ServiceId = y.ServiceId
            WHERE  x.ServiceCode = 'APA01'), 
           'SEC01', 'Section 1' ;

Ci dessus, en rouge, à quoi sert cette sous-requête ?

Tout d’abord, il faut observer que dans un bloc SELECT, FROM, WHERE, la séquence des opérations est en réalité différente de ce qu’on lit :

C’est d’abord la partie FROM (jointure nouveau style) qui est évaluée, puis la partie WHERE (restriction) et enfin la partie SELECT (projection finale).

Supposons que le contenu de la table SERVICE soit le suivant :

 
(T1) ServiceId    ServiceCode    ServiceLibelle
     ---------    -----------    -------------------
             1    APA01          Le service APA 1
             3    DSK01          Le service disque 1

Et celui de la table SECTION :

 
(T2) ServiceId    SectionId    SectionCode    SectionLibelle
     ---------    ---------    -----------    --------------
             3            1    SEC01          Section 1
             3            2    SEC02          Section 2 

Il y a donc dans un 1er temps une jointure des tables SERVICE et SECTION sur l’attribut ServiceId. Au résultat :

 
(T3) ServiceId    ServiceCode    ServiceLibelle        SectionId    SectionCode    SectionLibelle    
     ---------    -----------    -------------------   ---------    -----------    --------------
             3    DSK01          Le service disque 1           1    SEC01          Section 1
             3    DSK01          Le service disque 1           2    SEC02          Section 2 

En notant qu’il n’y a pas encore de section pour le service 'APA01'.


Dans un 2e temps, la restriction WHERE x.ServiceCode = 'APA01' est effectuée. Au résultat :

(T4) ServiceId    ServiceCode    ServiceLibelle        SectionId    SectionCode    SectionLibelle    
     ---------    -----------    --------------        ---------    -----------    --------------

C'est-à-dire une table vide.


Dans un 3e temps, la projection (SELECT) est effectuée :

COALESCE(MAX(SectionId), 0) + 1

MAX(SectionId) permet d’obtenir la plus grande valeur de SectionId dans la table T4. Comme cette table est vide, SQL pallie à sa façon par une marque NULL (manifestation de l’infâme bonhomme Null...)

A son tour, COALESCE(MAX(SectionId), 0) s’interprète ainsi : si MAX(SectionId) est marqué NULL, alors remplacer par la valeur figurant comme 2e paramètre de la fonction COALESCE, c'est-à-dire 0 dans l’exemple.

COALESCE(MAX(SectionId), 0) + 1 prend donc la valeur 0 + 1, c'est-à-dire 1.

Au final, l’INSERT est effectué, avec SectionId = 1 et la table SECTION devient :

 
(T5) ServiceId    SectionId    SectionCode    SectionLibelle
     ---------    ---------    -----------    --------------
             1            1    SEC01          Section 1
             3            1    SEC01          Section 1
             3            2    SEC02          Section 2 

Ajoutons à nouveau une section pour le service 'APA01' :

Code SQL :

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

1
2
3
4
5
6
INSERT INTO SECTION (ServiceId, SectionId, SectionCode, SectionLibelle)
    SELECT (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'APA01'), 
           (SELECT COALESCE(MAX(SectionId), 0) + 1
            FROM   SERVICE AS x JOIN SECTION AS y ON x.ServiceId = y.ServiceId
            WHERE  x.ServiceCode = 'APA01'), 
           'SEC02', 'Section 2' ;

Comme ci-dessus, il y a dans un 1er temps une jointure des tables SERVICE et SECTION sur l’attribut ServiceId. Au résultat :

 
(T6) ServiceId    ServiceCode    ServiceLibelle        SectionId    SectionCode    SectionLibelle    
     ---------    -----------    -------------------   ---------    -----------    --------------
             1    APA01          Le service APA 1               1    SEC01          Section 1
             3    DSK01          Le service disque 1            1    SEC01          Section 1
             3    DSK01          Le service disque 1            2    SEC02          Section 2 

Dans un 2e temps, la restriction WHERE x.ServiceCode = 'APA01' est effectuée. Au résultat :

 
(T7) ServiceId    ServiceCode    ServiceLibelle        SectionId    SectionCode    SectionLibelle    
     ---------    -----------    -------------------   ---------    -----------    --------------
             1    APA01          Le service APA 1               1    SEC01          Section 1 

Dans un 3e temps, la projection (SELECT) est effectuée :

COALESCE(MAX(SectionId), 0) + 1

MAX(SectionId) permet d’obtenir la plus grande valeur de SectionId dans T7, à savoir 1.

A son tour, COALESCE(MAX(SectionId), 0) se passe ainsi : comme le résultat de MAX(SectionId) n’est pas marqué NULL, alors c’est la valeur 1 qu’on vient d’obtenir qui est retenue.

COALESCE(MAX(SectionId), 0) + 1 prend donc la valeur 1 + 1, c'est-à-dire 2.

Au final, l’INSERT est effectué, avec SectionId = 2 et la table SECTION devient :

 
(T8) ServiceId    SectionId    SectionCode    SectionLibelle
     ---------    ---------    -----------    --------------
             1            1    SEC01          Section 1
             1            2    SEC02          Section 2
             3            1    SEC01          Section 1
             3            2    SEC02          Section 2 

Etc.

J’espère que vous n’aurez maintenant pas de mal à décoder les INSERT dans la table INSTANCE.

[DarckCrystale]

Bonjour fsmrel,

Purger consiste à supprimer de la base de données les données considérées comme obsolètes. C’est à la MOE (maîtrise d’œuvre) de fournir les conditions de purge. Ainsi, dans quelles conditions les tables VALEUR et BRUTE peuvent elles subir une cure d’amaigrissement ? En général, les purges ne sont pas effectuées toutes les secondes, on en détermine la fréquence : quotidienne, hebdomadaire, mensuelle, semestrielle, annuelle, etc. Cela conditionne souvent ce qu'on appelle le partitionnement des tables, mais cela se passe sous le capot, c'est du ressort du DBA, donc n'allons pas plus loin.

La table VALEUR pourra éventuellement être vidée, mais dans un souci d'historique, le contenu de la table BRUTE sera laissé intact, normalement.

Quel que soit le style qu’on utilise, le résultat est le même (ouf !) Disons qu’avec le nouveau style on se sert directement de l’opérateur JOIN (ici c’est plus précisément un équi-join)
[...]
A charge du SGBD de se dépatouiller, ce qu’il fait sans problème ! On lui propose le QUOI sous la forme qu’on préfère et il se charge du COMMENT.

Est-ce que ça change quelque chose en terme de vitesse de calcul, ou est-ce traité de la même manière par le SGBD ? Ou est-ce que ça dépend du SGBD ?

On devrait vous enseigner les deux. Si l’on vous enseigne l'ancien style, c’est qu’on considère que le produit et la restriction font partie des opérateurs primitifs, mais pas la jointure. Cela dit, il n’est pas interdit de considérer la jointure comme opérateur primitif, tandis que le produit en est alors considéré comme la version dégénérée (absence de restriction, c'est-à-dire du WHERE dans l'ancien style).

C'est à dire que l'année étant bien pleine, on ne fait du SQL que pendant le premier semestre, on verra peut-être ce type de jointure en deuxième année, soit à partir de septembre 2014.

A son tour, COALESCE(MAX(SectionId), 0) s’interprète ainsi : si MAX(SectionId) est marqué NULL, alors remplacer par la valeur figurant comme 2e paramètre de la fonction COALESCE, c'est-à-dire 0 dans l’exemple.

COALESCE sert donc à tester la valeur de manière plus concise qu'un IF ?

J’espère que vous n’aurez maintenant pas de mal à décoder les INSERT dans la table INSTANCE.

C'est effectivement tout de suite plus clair, et j'en comprends toute l'utilité.

Merci encore pour le temps que vous passez sur ce topic !

[Kropernic]

Bonjour,

Sachant fsmrel plutôt noctambule, je me permets de m'immiscer dans votre conversation pour répondre à une question dont je suis certain de la réponse.

Je laisse le soin à fsmrel de répondre aux autres questions de manière précise et rigoureuse comme il sait si bien le faire (pour mon plus grand plaisir).

COALESCE sert donc à tester la valeur de manière plus concise qu'un IF ?

COALESCE est une fonction qui peut recevoir N paramètres (je ne connais malheureusement pas la limite de N et j'imagine que cela va varier d'un SGDB à l'autre suivant la manière dont ils ont implémenté la fonctioner) et qui retourne le premier paramètre dans l'ordre de la liste qui lui est fournie dont la valeur est non nulle ('NOT NULL' et pas '<> 0').

[DarckCrystale]

Bonjour Kropernic,

Sachant fsmrel plutôt noctambule, je me permets de m'immiscer dans votre conversation pour répondre à une question dont je suis certain de la réponse.

Et vous faites bien, merci !

COALESCE est une fonction qui peut recevoir N paramètres (je ne connais malheureusement pas la limite de N et j'imagine que cela va varier d'un SGDB à l'autre suivant la manière dont ils ont implémenté la fonctioner) et qui retourne le premier paramètre dans l'ordre de la liste qui lui est fournie dont la valeur est non nulle ('NOT NULL' et pas '<> 0').

D'accord. Est-ce vous auriez d'autres exemples dans lesquels COALESCE peut être utile ? C'est juste une question de culture générale.

Merci à vous de vous être penché sur cette question

[Kropernic]

Il est utile à chaque fois que vous pourriez avoir une valeur un marqueur nulle et que vous souhaitez avoir une valeur de remplacement dans cette colonne.

Code :

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

COALESCE(VAR1, VAR2, VAR3, VAR4, 0)

L'exemple ci-dessus va tester successivement les variables VAR1, VAR2, VAR3 ET VAR4 pour voir si elles sont nulles (IS NULL) ou non et renvoyé la première qui ne l'est pas. Si aucune ne l'est, elle renverra la valeur 0 que j'ai spécifié en dernier paramètre. J'aurais tout aussi bien pu spécifier un texte à la place du zéro comme par exemple 'Aucune valeur non nulle.'.

Voici la documentation de la fonction COALESCE pour microsoft (sql server). Tout y est fort bien expliquer.

[fsmrel]

Bonsoir DarckCrystale & Krop,

Est-ce que ça change quelque chose en terme de vitesse de calcul, ou est-ce traité de la même manière par le SGBD ? Ou est-ce que ça dépend du SGBD ?

Les systèmes experts des moteurs des SGBD relationnels n’ont pas été développés par des perdreaux de l’année, ils ont fait l’objet de nombreuses thèses de la part des mathématiciens qui ont trouvé là de fort bons sujets de recherche, il suffit d’explorer les bibliothèques de l’ACM (Association for Computing Machinery) pour en juger.

Bien sûr, ces systèmes se bonifient au fil des ans. Quoi qu’il en soit, ils optimisent (réécrivent) nos requêtes, estiment les coûts des différentes stratégies possibles et retiennent la meilleure (ou la moins mauvaise...) pour produire un résultat et il est évident que, pour un SGBD digne de ce nom, la durée correspondante ne dépend pas du style (ancien ou nouveau) qu’on a employé. Pour vous faire une idée, je vous recommande la lecture de l’ouvrage de Jeffrey Ullman : Principles of Database Systems (Second Edition, Computer Science Press), voire l’ouvrage de Claude Delobel et Michel Adiba : Bases de données et systèmes relationnels (Dunod informatique), et si vous avez un tube d’aspirine à portée de main, voyez The Theory of Relational Databases de David Maier, au chapitre 11.

Les moteurs des SGBD ne sont évidemment pas tous au même niveau de raffinement. Je ne suis pas sûr que tous soient aussi efficaces que DB2, lequel joue notamment à fond la carte de la fermeture transitive et réécrit au mieux la requête que je lui ai proposée.

Exemple :

Le client dont le Siret est '123456789000015' passe commande. Une commande se décline en lignes de commande. Pour chaque ligne de commande, on s'engage en fonction des disponibilités des produits en stock, en cours de fabrication, des approvisionnements en cours, etc. ; un engagement est composé à son tour de parties livrables en fonction du nombre de camions nécessaires pour l'acheminement, etc. Au final, un camion ou plusieurs camions effectuent les livraisons. On aimerait savoir quels camions ont déjà livré le client en question.

MLD :



Dans le diagramme, les clés primaires sont soulignées, les clés étrangères sont en italiques, sachant que les attributs appartenant à une clé primaire peuvent aussi appartenir à une clé étrangère ; les principales clés alternatives — celles qui sont connues de l'utilisateur — sont soulignées en traits discontinus.

Pour récupérer la liste des camions, je peux proposer une requête bien classique :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT DISTINCT Camion.CamImmat
FROM   Client JOIN Commande
                ON  Client.CliId = Commande.CliId
              JOIN LigneCde  
                ON  Commande.CliId = LigneCde.CliId
                AND Commande.CdeId = LigneCde.CdeId
              JOIN Engagement  
                ON  LigneCde.CliId = Engagement.CliId
                AND LigneCde.CdeId = Engagement.CdeId
                AND LigneCde.LigneId = Engagement.LigneId
              JOIN Livraison  
                ON  Engagement.CliId = Livraison.CliId
                AND Engagement.CdeId = Livraison.CdeId
                AND Engagement.LigneId = Livraison.LigneId
                AND Engagement.EngId = Livraison.EngId
              JOIN Camion  
                ON  Livraison.CamionId = Camion.CamionId
WHERE  CliSiret = '123456789000015' ;

Mais DB2 réécrit ainsi la requête :

Code SQL :

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

1
2
3
4
SELECT DISTINCT Camion.CamImmat
FROM   Client JOIN Livraison ON  Client.CliId = Livraison.CliId
              JOIN Camion ON  Livraison.CamionId = Camion.CamionId
WHERE  CliSiret = '123456789000015' ;

Le nombre de jointures a été réduit à 2 (seules 3 tables sont parties prenantes). A chacun de voir comment son SGBD optimise la requête initiale.

C'est à dire que l'année étant bien pleine, on ne fait du SQL que pendant le premier semestre, on verra peut-être ce type de jointure en deuxième année, soit à partir de septembre 2014.

Heu... « ce type de jointure » c’est bien du SQL, normé il y a 20 ans...

COALESCE sert donc à tester la valeur de manière plus concise qu'un IF ?

Kropernic a répondu. Pour compléter, vous pouvez télécharger et consulter aussi la doc concernant votre SGBD, PostgreSQL (téléchargement du PDF).

N.B. Le rédacteur de la doc PostgreSQL se plante en écrivant que NULL est une valeur, mais suite à la remarque faite par Kropernic vous rectifierez de vous-même.

P.-S. J’espère que personne n’oublie de voter quand il a appris quelque chose au cours de la discussion...

[DarckCrystale]

Bonjour, merci pour votre patience et désolée du temps que j'aurai mis à répondre.

Vous m'avez aidé à y vois plus clair, et le schéma final répond (enfin !) à mes attentes

Cependant, malgré le bien fondé du dédoublement des identifiants, je ne suis pas parvenue à le faire accepter. Mais ceci dit, c'est une pratique que je mettrai en œuvre pour mes propres projets, merci beaucoup !

[DarckCrystale]

Voici au final le schéma de la base de données :

t_machines(id, label, description)
t_services(id, label, description)
t_sections(#serviceid, id, label, description)
t_instances(#machineid, #(#serviceid, id), numero, description, max, min)
t_brute(#machineid, #serviceid, date, fichierjson)
t_valeur(#(#machineid, #(#serviceid, nom), id), date, valeur)

Est-ce que ça vous paraît convenable ?