Intégrité référentielle

PostgreSQL est un SGBD moderne et puissant qui gère la notion d'intégrité référentielle des données. Cette notion a déjà été évoquée plusieurs fois dans ce blog et notamment dans le billet intitulé PostgreSQL (initiation).
Nous allons y revenir un peu plus en détails.
Considérons la table des employés d'une entreprise. Cette table contient une colonne "service" où est indiqué sous forme d'un code le service auquel appartient l'employé. D'autre part, nous avons une table "services", donnant la signification de ce code, créée avec cette instruction:

CREATE TABLE services
(service_id CHAR(2) PRIMARY KEY,
 dénomination CHAR(25))
;

Cette table contient par exemple les données:

 service_id |       dénomination        
------------+---------------------------
 00         | Direction                
 01         | Secrétariat              
 02         | Gestion du personnel     
 03         | Relations publiques      
 04         | Production               
 05         | Recherches               
 06         | Ventes         

PostgreSQL peut garantir que le code service de chaque employé se trouve dans la table "services", en clair chaque employé doit être affecté dans un service.
Pour cela, il suffit que la table des employés ait été créée postérieurement à la table "services" suivant par exemple cette instruction:

CREATE TABLE employés
(matricule SERIAL PRIMARY KEY,
 nom CHAR(50),
 adresse CHAR(50),
 code_postal CHAR(5),
 cp_seq SMALLINT,
 naissance DATE,
 sexe CHAR(1) CHECK (sexe IN ('M','F')),
 service CHAR(2) REFERENCES services)
;

qui définit (en rouge) une contrainte d'intégrité référentielle.
La colonne service de la table "employés" constitue une clef étrangère (foreign key) qui pointe vers la clef primaire de la table services.
Ici le champ "service" peut prendre la valeur NULL, ce qui correspondrait au cas où le service de l'employé n'est pas connu. Mais la table "employé" peut-être définie de manière à exclure cette possibilité.
Que se passe-t-il si nous essayons de supprimer un service contenant des employés?
Essayons de procéder dans un terminal psql.
La suppression est refusée:

C'est le comportement par défaut. Mais d'autres possibilités sont offertes par PostgreSQL. Le code service de tous les employés  appartenant au service supprimé peut être mis à NULL . Ce sera le cas si l'instruction de création de la table "employés" contient :

service CHAR(2) REFERENCES services ON DELETE SET NULL

Cette table peut également être définie de telle sorte que la suppression d'un service entraîne la suppression de tous les employés. Ce sera le cas si le "CREATE" contient:

service CHAR(2) REFERENCES services ON DELETE CASCADE

Examinons d'un peu plus près cette possibilité afin d'avoir une idée  de ce qu'implique exactement le mot CASCADE.
Dans notre base de données de test nous avons également défini les tables paie et codepaie:

CREATE TABLE codepaie
(code CHAR(2) PRIMARY KEY,
 signification CHAR(20))
;
CREATE TABLE paie
(matricule INTEGER REFERENCES employés,
 mois CHAR(6),
 seq SMALLINT,
 code CHAR(2) REFERENCES codepaie,
 montant NUMERIC(13,2),
 PRIMARY KEY (matricule,mois,seq))
;

La table paie se rapportant au paiement du salaire des employés, chaque opération de paiement doit faire référence à un employé donné. La table codepaie donne la signification des codes utilisés et du fait de la relation d'intégrité référentielle, elle définit également l'ensemble des codes autorisés.
Schématiquement, nous avons cette situation:

Les relations d'intégrité référentielle sont matérialisées par des flèches issues des FK (foreign keys) et pointant vers les PK (primary keys). En rouge, nous avons une relation définie avec 'ON DELETE CASCADE'.
De ce fait, un "delete" au niveau de la table "services" va se propager vers la droite, telle une vague destructrice remontant un courant dont le sens est indiqué par les flèches. La table codepaie est à l'abri (la flèche est à l'envers). Mais qu'en est-il de la table paie? Logiquement elle devrait être protégée car la contrainte "paie => employés" n'a pas été définie de manière particulière (elle n'est pas représentée par une flèche rouge).
Vérifions tout cela dans notre terminal psql. Procédons avec un utilisateur ordinaire (titine) auquel nous donnons tous les droits sur la table services:

Pour les autres tables de bdtest, titine a le droit "select":

mais pas d'autres droits:

Recherchons tout d'abord quel est dans chaque service le nombre d'employés référencés par la table paie:

En fait, deux employés seulement sont concernés: un dans le service '00' et un autre dans le service '01'.
Essayons dans un premier temps de supprimer le service '02':

Opération couronnée de succès: une seule rangée supprimée dans la table "services".
Par contre dans la table "employés"

quatre rangées ont été supprimées alors que titine n'a pas le droit "delete" sur cette table!
Attaquons-nous maintenant au service '01':

La suppression d'un des 4 employés du groupe a échoué: celui référencé depuis la table paie. De ce fait malgré l'option CASCADE, la suppression du service devient impossible. Les autres employés du groupe, qui eux auraient pu être supprimés, sont sauvés:

Formulaires dans libreoffice base

Nous allons revenir sur le sujet traité dans ce billet, à savoir la création de formulaires dans libreoffice base (à l'époque c'était openoffice base).
L'approche sera cette fois différente: nous n'utiliserons pas l'assistant et donnerons un rôle encore plus important au navigateur de formulaires. La base de données utilisée (appelée bd01) est une base de données PostgreSQL concernant comme précédemment la gestion du personnel d'une entreprise.

Création de la structure du formulaire.
Après connexion à cette base de données cliquons sur "Créer un formulaire en mode Ébauche" et ensuite dans la fenêtre qui apparaît cliquons sur l'icône du navigateur de formulaire située dans la barre d'outils horizontale inférieure:

Dans le navigateur, cliquons droit sur "Formulaires" et dans le menu contextuel qui surgit, choisissons "Nouveau Formulaire":

Ce nouveau formulaire s'appelle par défaut "Formulaire". Donnons lui un nom plus parlant. Pour afficher les données qui nous intéressent, nous allons utiliser une table "selection" contenant l'identifiant (matricule) du dossier à sélectionner. nous appellerons donc ce premier formulaire "Sélection".
Recommençons l'opération en cliquant droit sur l'icône du formulaire qui vient d'être créé:

Le nouveau formulaire (qui dépendra de "Sélection") contiendra les données des employés de l'entreprise: nous l’appellerons "Employés".
Continuons de la sorte de manière à arriver à ceci:

"Département" affichera le département du lieu de résidence de l'employé, "Salaire" le salaire pour un mois donné et "Détails" les détails de ce salaire (prime, régularisation, heures sup etc).
Il s'agit d'un exemple.
Autre exemple:

Il s'agit cette fois d'une structure correspondant à l'affichage des données d'un client (d'un magasin) avec date et montant d'une facture et les détails de celle-ci.

Sources de données et liens entre sous-formulaires.
Ouvrons la fenêtre "Propriétés"du formulaire "Sélection". On peut procéder via le menu contextuel de "Sélection" ou en cliquant sur l'icône formulaire de la barre d'outils:

On déclare la source de données comme étant la table selection et on n'autorise ni les ajouts ni les suppressions:

 

Rappelons que la table selection dont voici la description (ouput de la meta commande psql \dS):

  Colonne  |  Type   | Modificateurs
-----------+---------+---------------
 id        | integer | non NULL
 matricule | integer |
Index :
    "selection_pkey" PRIMARY KEY, btree (id)

est une table outil dont le seul but est de nous permettre d'afficher aisément le dossier qui nous intéresse.
(NB: les tables PostgreSQL dont le contenu est amené à être modifié dans libreoffice base doivent être munies d'une clef primaire)
Passons aux propriétés du sous-formulaire "Employés". La source de données à indiquer est la table que nous avons précédemment appelé entrh.
Elle contient les colonnes:

   Colonne   |     Type      | Modificateurs                    
-------------+---------------+---------------
 matricule   | integer       | non NULL
 nom         | character(50) |
 adresse     | character(50) |
 code_postal | character(5)  |
 cp_seq      | smallint      |
 naissance   | date          |
 sexe        | character(1)  |
 service     | character(2)  |

Les colonnes code_postal et cp_seq constituent une clé étrangère (foreign key) établissant une relation d'intégrité référentielle avec une table des codes postaux (frcp dans notre base de données) contenant le nom des localités:

    Colonne    |     Type      | Modificateurs
---------------+---------------+---------------
 code_postal   | character(5)  | non NULL
 cp_seq        | smallint      | non NULL
 localite_nom  | character(50) |
 code_departem | character(2)  |

De même la colonne service est définie comme une clé étrangère pointant vers une table service contenant le nom du service:

   Colonne    |     Type      | Modificateurs
--------------+---------------+---------------
 service_id   | character(2)  | non NULL
 dénomination | character(25) |

Le lien entre les formulaires  "Employés" et "Sélection" sera basé sur la colonne matricule:

Le nom du département se trouve dans une table nommée frdepartem:

   Colonne    |     Type      | Modificateurs
--------------+---------------+---------------
 departem_id  | character(2)  | non NULL
 departem_nom | character(30) |
 code_region  | character(2)  |

Pour avoir la possibilité de déduire le nom du département sur base du code postal, nous pouvons au niveau de PostgreSQL définir une vue liant les tables frcp et frdepartem:

CREATE VIEW cpdep AS
SELECT     
    frcp.code_postal,
    frcp.cp_seq,
    frcp.localite_nom,
    frdepartem.departem_nom
FROM frcp frcp, frdepartem frdepartem
WHERE (frcp.code_departem = frdepartem.departem_id)
; 
GRANT SELECT ON cpdep TO PUBLIC

On peut aussi créer une requête dans libreoffice base.
Pour libreoffice, cette vue apparaît comme une table que nous allons désigner comme source de données du sous-formulaire "Département" qui sera lié à "Employé" via code_postal et cp_seq:

Pour le reste nous avons créé la table paie (et donné les droits adéquats):

CREATE TABLE paie
(matricule INTEGER,
 mois CHAR(6),
 seq SMALLINT,
 code CHAR(2),
 montant NUMERIC(13,2),
 PRIMARY KEY (matricule,mois,seq),
 FOREIGN KEY (matricule) REFERENCES entrh)
;
GRANT ALL ON paie TO gestionrh
;

Cette table est liée dans une relation d'intégrité référentielle avec entrh, de sorte qu'un employé ne peut pas être supprimé s'il existe un paiement le concernant.
Nous avons aussi créé la vue salaire:

CREATE VIEW salaire
AS
SELECT matricule, mois, sum(montant) AS montant
FROM paie
GROUP BY matricule, mois
;
GRANT SELECT ON salaire TO gestionrh
;

Cette vue sera notre source de données ordonnée suivant le mois pour le sous-formulaire "Salaire", la laison avec "Employés" étant basée sur la colonne matricule:

Enfin pour le sous-formulaire "Détails", on établit les propriétés comme suit:

Mise en place des éléments du formulaire.
Passons à l'étape de la mise en place des différents éléments du formulaire.
Choisissons en premier un arrière-plan en passant par le menu  Format -> Page:

soit une couleur, soit une image:

Reste à disposer les différents champs.
Avant de procéder, veillons à ce que "Sélection" soit sélectionné au niveau du navigateur de formulaires.
Dans la barre d'outils verticale de gauche, choisissons "Champ d'étiquette":

Après que nous ayons dimensionné cet élément, cliquons droit pour afficher son menu contextuel:

Via l'élément du menu appelé "Contrôle" nous accédons à la fenêtre des propriétés:

Modifions le nom en "lblmatricule" et la valeur de l'étiquette en "Matricule".
Sélectionnons maintenant "Champ formaté" (toujours dans la barre d'outils verticale):

Nous plaçons ce champ en dessous du champ d'étiquette précédent et dans la fenêtre des propriétés, après avoir renommer ce champ, nous appuyons sur les 3 petits points qui permettent de lui attribuer le champ d'étiquette (label) précédent:

Nous mettons également la Séquence d'activation à 1:

Comme champ de données, nous prenons évidemment "matricule":

Le menu contextuel obtenu lorsque les deux éléments sont sélectionnés nous offre la possibilité de les grouper:

Après avoir sélectionné "Employés" dans le navigateur de formulaires, déposons et déployons un bouton issu de la barre d'outils gauche:

à droite du champ formaté fmtmatricule. Dans la fenêtre propriétés, onglet général, étiquette nous remplaçons la valeur standard "Bouton"  par "OK": 

C'est ce mot qui apparaîtra sur le bouton.

D'autre part, nous attribuons à ce bouton l'action "Rafraîchir le formulaire":

Procédant comme pour le matricule, plaçons sur le formulaire un champ de type zone de texte, surmonté de son étiquette, qui affichera le nom de l'employé.

Rôle du focus.

Après avoir enregistré le formulaire, désactivons le mode ébauche:

Le nom correspondant au matricule qui est inscrit dans la rangée unique de notre table sélection apparaît alors à l'écran:

Dès l'instant où nous modifions le matricule, la petite disquette de la barre d'outils horizontale prend des couleurs: 

signe que le matricule à l'écran n'est plus celui de la base de données.

Frappons la touche "Tabulation": le focus se déplace sur le bouton (ce qui est matérialisé par le rectangle en points tillés autour du mot OK) et la petite disquette perd ses couleurs:

il y a eu écriture du matricule 1007 dans la table sélection. Cela ne fonctionne pas si le bouton appartient au formulaire "Sélection". Autrement dit, l'opération d'écriture se déclenche lorsque le focus quitte le formulaire "Sélection" .

Si nous appuyons maintenant sur le bouton (par exemple en frappant la touche "Enter"), le nom correspondant au matricule 1007 s'affiche:

Nous pouvons aussi directement appuyer sur le bouton à l'aide de la souris (sans y amener le focus à l'aide la touche "tabulation"). Dans ce cas, le fait de cliquer met le focus sur le bouton, ce qui provoque l'écriture, celle-ci étant immédiatement suivie du rafraîchissement (action liée au bouton).

(EDIT: voir dans ce billet comment rafraîchir sans utiliser le bouton OK) 

Cliquer sur le bouton y met le focus à condition que dans les propriétés du bouton l'option (par défaut) 'Focus sur clic' ait été conservée:

Dans le cas contraire, cliquer sur le bouton sans toucher au clavier après saisie du matricule, ne produit aucun effet. Mais comment alors parvenir à ses fins sans plus toucher au clavier? Simplement il suffit avant de cliquer de déposer le curseur dans le champ nom, déclenchant ainsi l'écriture du matricule. Mais cette manipulation n'est évidemment jamais nécessaire puisque par défaut 'Focus sur clic = Oui'. 

Afficher la localité à l'aide de macros.

Continuons maintenant à disposer les différents éléments constitutifs du sous-formulaire "Employés": 

champ de date pour la date de naissance, accessible en cliquant sur "Contrôles supplémentaires"

boutons radio pour le sexe

zone de liste pour le service

adresse, code postal ...

(Pour le détail des manipulations voir ce billet.)

Par la magie de la zone de liste "service", le nom du service apparaît en clair à l'écran et le code derrière (qui est la seule information présente dans entrh) est transparent pour l'utilisateur.

Nous aimerions procéder de même pour la localité de résidence de l'employé et rendre transparent le numéro de séquence (cp_seq) qui associé au code postal pointe vers une localité de frcp. Mais nous allons nous heurter à un gros problème: comment limiter la liste aux localités dont le code postal est celui affiché? Pour parvenir à nos fins, nous allons devoir faire appel à une macro.

Mais tout d'abord, afin d'éliminer un problème  qui se présentera lors de l'utilisation de la macro, définissons une vue:

CREATE VIEW frcpn AS
SELECT frcp.code_postal::numeric AS code_postal,
frcp.cp_seq,
frcp.localite_nom,
frcp.code_departem
FROM frcp
;
GRANT SELECT ON frcpn TO PUBLIC
;

La seule différence entre la vue frcpn et la table originale est que le code postal est maintenant une grandeur de type numérique.
Notre zone de liste sera basée sur cette vue:

Le champ affiché est le nom de la localité:

Et les deux champs qui doivent coïncider sont les deux champs cp_seq:

Nous appellerons cette zone de liste lboxlocalite:

Le contenu de la liste est basé sur une instruction sql:

à laquelle on voudrait ajouter une clause WHERE via l'appel à une macro.

Les macros que nous allons utiliser se trouvent dans un fichier dont voici le contenu:

option explicit
dim  LstLocalite AS Object
'*******************************
sub MajListe
'*******************************
DIM var_code_postal
LstLocalite = Thiscomponent.DrawPage.Forms.GetByName("Sélection").getByName("Employés").getByName("lboxlocalite")
var_code_postal = Thiscomponent.DrawPage.Forms.GetByName("Sélection").getByName("Employés").getByName("txtcode_postal").TEXT
LstLocalite.ListSource() = array("SELECT localite_nom, cp_seq FROM public.frcpn AS frcpn WHERE code_postal =" & var_code_postal)
LstLocalite.refresh 
end sub
'*******************************
sub Actualisation
'*******************************
dim ExoTrav
MajListe  
ExoTrav= ExoRechercheDansTableau(LstLocalite.ValueItemList, LstLocalite.boundField.Value)
if ExoTrav <> "#N/A" then
LstLocalite.SelectedItems = array(ExoTrav)
else
LstLocalite.SelectedItems = array(0)
end if
end sub
'**************************************************
Function ExoRechercheDansTableau(ExoTablo, ExoRech)
'**************************************************
dim i as integer
ExoRechercheDansTableau = "#N/A"
for i = lbound(ExoTablo) to ubound(ExoTablo)
if ExoTablo(i)= ExoRech then
ExoRechercheDansTableau = i
exit for
end if
next i
end function

lbxlocalite correspond au nom de la zone de liste "Localité" et txtcode_postal au nom du champ "Code postal":

Pour intégrer ces macros à notre fichier odb, il faut passer par Outils->Macros-> Gérer les macros:

Sélectionnant bd01.odb (notre fichier odb), on appuie sur "Nouveau":

Après avoir valider "Module 1" dans la fenêtre qui surgit, on atterrit dans un éditeur et on y remplace le texte qui y figure:

par celui des 3 macros ci-dessus.

Assignons la macro "MajListe" à l'événement "Modifié" du champ txtcode_postal. Pour ce faire, on procède via la fenêtre des propriétés du champ, onglet Événement:

Un appui sur les 3 petits points de droite, nous permet d'arriver facilement sur le sélecteur de macro:

De même on assigne la macro "Actualisation" à l'événement "Après le changement d'enregistrement" du formulaire "Employés".

Vérifions si tout est OK:

C'est parfait: le numéro de séquence cp_seq est maintenant transparent pour l'utilisateur du formulaire. Le formulaire est donc considérablement simplifié puisqu'on ne doit plus afficher à l'intention de l'utilisateur une liste des numéros de séquence possibles avec les localités correspondantes. De plus, l'utilisateur ne doit plus encoder un numéro de séquence, mais cliquer sur une localité dans une liste déroulante. Pour éviter les avertissements relatifs à l'utilisation de macros, allons dans Outils -> Options -> Sécurité -> Sécurité des macros, onglet "Sources de confiance" et ajoutons le chemin vers notre fichier odb:

Le formulaire final.

Reste à compléter les sous-formulaires Département, Salaire et Détails.

Voici un exemple de ce à quoi on peut arriver:

(Nous avons remplacé la zone de saisie du matricule par une "boite combinée".)