Discussion :

[martinbrait]

Accès aux tables en écriture (modification/suppression)

Bonjour,

SCENARIO N°1 :
J'accède au contenu d'une seule table
à travers une vue modifiable, depuis un formulaire, sous-formulaire, page web.
Il me suffit d'écrire un insert/update/delete avec cette vue modifiable.

Objets à créer dans mon SGBD postgres :
Une vue modifiable, qui projette le contenu d'une seule table
'-----------------------------

SCENARIO N°2 :
J'accède au contenu d'une seule vue, qui projette le contenu de plusieurs tables
depuis un formulaire ou une page web
J'ajoute sur ma page web, 3 boutons :
un bouton dédié aux insertions, un bouton dédié aux mises à jours, un bouton dédié aux suppressions
qui déclenche une transaction permettant d'actualiser dans un ordre séquentiel, le contenu de nombreuses tables projetées dans ma vue

Objets à créer dans mon SGBD postgres :
3 fonctions
insert personnalisée pour déclencher une transaction, update personnalisé pour déclencher une transaction, delete personnalisé pour déclencher une transaction
Une vue en lecture seule, qui projette le contenu de plusieurs tables.


Avez-vous une préférence pour le scénario 1 ou le scénario 2, et pourquoi ?
Quelles sont les défis à relever pour le scénario 1 ? avantages et inconvénients ?
Quelles sont les défis à relever pour le scénario 2 ? avantages et inconvénients ?

[tatayo]

Bonjour,
Je dirai: ça dépend.
Dans certains cas l'insertion via une vue est possible, mais pas toujours.
Par exemple pour créer une commande client, je vais ajouter:

• Une ligne dans la table Entête
• Une ligne dans la table des Adresses, pour l'adresse de livraison
• Une ligne dans la table des Adresses, pour l'adresse de facturation
• Une ou plusieurs lignes dans la table des Lignes
• Une ou plusieurs lignes dans la table des Règlement
• 0 ou plusieurs lignes dans une table "annexe"

Je ne vois pas trop comment le faire à travers une vue.

Mais quoi qu'il en soit je passe systématiquement par des transactions quand je fais des mises à jour en base.

Tatayo.

[SQLpro]

PostGreSQL est très limité dans la mise à jour des vues. Il interdit toute mise à jour directe lorsqu'il y a plusieurs tables contrairement aux autres SGBDR (SQL Sever, oracle...)

De toute façon, chaque commande SQL est une transaction implicite.

L'intérêt d'une transaction explicite c'est de combiner plusieurs mises à jour qui doivent rester cohérentes. La transaction est donc nécessaire d'un point de vue fonctionnelle pas d'un point de vue programmatique. Ainsi, dire "je fais toujours des transactions", n'a aucun intérêt, bouffe des ressources et bloque les utilisateurs concurrents inutilement...

Exemple de transaction nécessaire : virement d'un compte bancaire à un compte épargne...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
BEGIN WORK ;
 
UPDATE MonCompteBancaire
SET MonSolde = MonSolde - 500
WHERE MonNumeroClient = 123;
 
UPDATE MonCompteEpargne
SET MonSolde = MonSolde + 500
WHERE MonNumeroClient = 123;
 
COMMIT;
 
SIGNAL:
   ROLLBACK;

En code SQL ISO...

Il est difficile de s'y retrouver si des activités concurrentes d'utilisateurs veulent mettre à jour le compte courant entre les deux UPDATES...

Exemple de transaction inutile : ajout d'un personne et d'un téléphone

Code :

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

1
2
3
4
5
6
7
8
9
BEGIN WORK ;
 
INSERT INTO Personne VALUES (12345, 'Durand', 'Paul');
 
INSERT INTO Telephone VALUES (99999, 12345, '06 78 45 78 41');
COMMIT;
 
SIGNAL:
   ROLLBACK;

En code SQL ISO...

En effet, il est facile de supprimer la personne si le téléphone n'a pas pu être enregistré sans l'aide d'une transaction... Et la concurrence des autres utilisateurs n'a aucune incidence...

A +

[martinbrait]

Bonjour tatayo, SQLpro

Merci pour vos réponses !

@ SQLpro

exemple 1 : la mise à jour d'un virement entre compte courant et compte épargne, concerne 2 tables qui n'ont pas de jointures entre elles.
C'est la règle métier qui stipule qu'un débit sur le compte courant doit apparaître en crédit sur le compte épargne, et vice versa.
Aucune relation avec mises à jour en cascade ou suppression en cascade entre la table 'compte_courant' et la table 'compte_épargne'.
On doit obligatoirement créer une transaction pour passer l'écriture.


exemple 2 : la table 'telephone' est fille de la table 'personne'.
une relation de mise à jour en cascade et suppression en cascade gère l'intégrité référentielle entre le téléphone et la personne.
C'est cette relation qui nous permet d'agir en modification/mise à jour/suppression, sur tout ou partie des données de la relation.

On évite de bloquer l'écriture dans ces tables, par plusieurs gestionnaires simultanément.
La transaction explicite serait redondante avec le comportement par défaut du moteur SGBD.
On s'abstient d'écrire une transaction.

Ainsi, lorsque 2 à 3 tables maximum, sont liées avec une relation de mise à jour en cascade et suppressions en cascade dans le SGBD,
on les présente aux gestionnaires, à travers des vues modifiables.
Eviter de créer une transaction explicite, pour effectuer les insertions, mises à jour, suppression.

AI JE BIEN TOUT COMPRIS ?

[tatayo]

Si je puis me permettre:

En effet, il est facile de supprimer la personne si le téléphone n'a pas pu être enregistré sans l'aide d'une transaction... Et la concurrence des autres utilisateurs n'a aucune incidence...

C'est valable si l'application "garde le contrôle", mais en cas d'arrêt non contrôlé de l'application (BSOD, coupure de courant, plantage pur et simple), comment identifier ce qui doit être supprimé ?
Si tout est fait dans une transaction, le SGBD s'occupera d'annuler la transaction, et la base reste propre.

Tatayo.

[martinbrait]

Inventaire de toutes mes tables :

• etat_civil
• sortie
• sortie_doss
• telephone
• hist_grade
• hist_grade_ech
• hist_fonctionnel
• hist_fonctionnel_ech
• hist_posadmin
• hist_affectation

Mes tables historiques sont justifiées, car les données historiques
sont nécessaires pour préparer des mouvements d'avancement et des mouvements de mutation.
(critères pour établir des promouvabilité de personnels, et des catégories de viviers de promouvables.)


Les tables
état_civil
historique d'affectation,
historique de grade,
historique d'échelon de grade
historique d'emploi fonctionnel,
historique d'échelon d'emploi fonctionnel,

sont réactualisées mensuellement par pré-irrigation de données depuis un SI externe

Les tables
sortie,
telephone,
sortie_doss,
sont réactualisées quotidiennement par mise à jour de données avec les gestionnaires

Cas d'utilisation :
On prépare des dossiers de personnel partant à la retraite.
On indique l'état civil de la personne.
On indique la position administrative actuelle de la personne (en activité ou non).
On indique l'affectation actuelle de la personne, actualisée en permanence, tout au long de la construction du dossier de sortie.
On indique le grade actuel de la personne, avec un échelon actuel de grade.
On indique que l'affectation actuelle correspond éventuellement à un emploi fonctionnel, avec un échelon d'emploi fonctionnel.
(La durée d'affectation sur un emploi fonctionnel est prise en compte dans le calcul des taux).
On indique la date de prévision de retraite, et la date effective de retraite
On enregistre une prolongation d'activité MEPA03 (suivi, date et transmission des arrêtés, réception des documents administratifs).
On enregistre une prolongation d'activité MEPA08 (suivi, date et transmission des arrêtés, réception des documents administratifs).
On enregistre une retraite anticipé RAPA (suivi, date et transmission des arrêtés, réception des documents administratifs).


Vue(s) à composer pour mes 10 tables, avec le nom des colonnes ci-dessous :
etat_civil.matricule character varying(7) NOT NULL,
etat_civil."deces_O_N" character varying(1),
etat_civil.nom character varying(55),
etat_civil.prenom character varying(55),
etat_civil.sexe character varying(50),
etat_civil.date_naissance timestamp without time zone,
etat_civil.adresse_rue text,
etat_civil.adresse_codepost character varying(8),
etat_civil.adresse_ville character varying(20),
etat_civil.hors_budget character varying(10),
etat_civil.qualite_statutaire character varying(50),
hist_posadmin.sit_administrative character varying(50),
hist_posadmin.date_sit_administrative timestamp without time zone,
hist_grade.ref_grade character varying(14),
hist_grade_ech.echelon_grade character varying(2),
hist_fonctionnel.emploi character varying(50),
hist_fonctionnel_ech.echelon_emploi character varying(50),
hist_affectation."libstructFIN" character varying(255),
hist_affectation."libstructRH" character varying(255),
hist_affectation."SAA" character varying(255),
hist_affectation."SAO" character varying(255),
hist_affectation."dirPN" character varying(255),
hist_affectation."libZonDef" character varying(255),
hist_affectation.ref_direction character varying(10),
hist_affectation.service character varying(90),
hist_affectation.departement character varying(255),
hist_affectation.ref_sgap character varying(2),
sortie.date_depart timestamp without time zone,
sortie.ref_motifsortie character varying(15),
sortie.observation text,
sortie.date_prevision timestamp without time zone,
sortie.motif_prevision character varying(50),
sortie.date_maintien timestamp without time zone,
sortie.date_taux_max_pension timestamp without time zone,
sortie.pension numeric(19,4),
sortie.taux_pension numeric(19,4),
sortie.date_dem_pension timestamp without time zone,
sortie."actRC" character varying(3),
sortie.surcotisation boolean,
sortie.date_transmission_bpai timestamp without time zone,
sortie.gestionnaire character varying(5),
sortie_doss.mepa03_arret_sign boolean,
sortie_doss.mepa03_certif_med boolean,
sortie_doss.mepa03_avis_direction boolean,
sortie_doss.mepa03_date_debut timestamp without time zone,
sortie_doss.mepa03_date_dem timestamp without time zone,
sortie_doss.mepa03_date_edit timestamp without time zone,
sortie_doss.mepa03_taux numeric(19,4),
sortie_doss.mepa08_arret_sign boolean,
sortie_doss.mepa08_certif_med boolean,
sortie_doss.mepa08_date_debut timestamp without time zone,
sortie_doss.mepa08_taux numeric(19,4),
sortie_doss.mepa08_date_dem timestamp without time zone,
sortie_doss.mepa08_date_edit timestamp without time zone,
sortie.retraite_arret_edit boolean,
sortie.retraite_date_edit timestamp without time zone,
sortie.rapa_date_debut timestamp without time zone,
sortie.rapa_certif_scolaire boolean,
sortie.rapa_livret_famille boolean,
sortie_doss.rapa_arret boolean,
sortie_doss.rapa_certif_med boolean,
sortie_doss.rapa_date_dem timestamp without time zone,
sortie_doss.rapa_date_edit timestamp without time zone,
sortie_doss.rapa_taux numeric(19,4),
telephone.protelfix character varying(15),
telephone.protelmob character varying(15),
telephone.promail character varying(150),
telephone.persotelfix character varying(15),
telephone.persotelmob character varying(15),
telephone.persomail character varying(150)
);

'--------------------------

Ma tentation, faute de mieux, est de créer une table temporaire fourre-tout,ayant des droits d'accès, lecture, écriture des données
Je crée des scripts pour pré-irriguer certaines colonnes, avec le contenu du SI externe.
exemple :

Code :

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
  CREATE TABLE public.sortie (
    matricule character varying(7) NOT NULL,
    "deces_O_N" character varying(1),
    nom character varying(55),
    prenom character varying(55),
    sexe character varying(50),
    date_naissance timestamp without time zone,
    ref_grade character varying(14),
    echelon_grade character varying(2),
    emploi character varying(50),
    echelon_emploi character varying(50),
    hors_budget character varying(10),
    qualite_statutaire character varying(50),
    sit_administrative character varying(50),
    date_sit_administrative timestamp without time zone,
    "libstructFIN" character varying(255),
    "libstructRH" character varying(255),
    "SAA" character varying(255),
    "SAO" character varying(255),
    "dirPN" character varying(255),
    "libZonDef" character varying(255),
    ref_direction character varying(10),
    service character varying(90),
    departement character varying(255),
    ref_sgap character varying(2),
    date_depart timestamp without time zone,
    ref_motifsortie character varying(15),
    observation text,
    date_prevision timestamp without time zone,
    motif_prevision character varying(50),
    mepa03_arret_sign boolean,
    mepa03_certif_med boolean,
    mepa03_avis_direction boolean,
    mepa03_date_debut timestamp without time zone,
    mepa03_date_dem timestamp without time zone,
    mepa03_date_edit timestamp without time zone,
    mepa03_taux numeric(19,4),
    mepa08_arret_sign boolean,
    mepa08_certif_med boolean,
    mepa08_date_debut timestamp without time zone,
    mepa08_taux numeric(19,4),
    mepa08_date_dem timestamp without time zone,
    mepa08_date_edit timestamp without time zone,
    retraite_arret_edit boolean,
    retraite_date_edit timestamp without time zone,
    rapa_date_debut timestamp without time zone,
    rapa_certif_scolaire boolean,
    rapa_livret_famille boolean,
    rapa_arret boolean,
    rapa_certif_med boolean,
    rapa_date_dem timestamp without time zone,
    rapa_date_edit timestamp without time zone,
    rapa_taux numeric(19,4),
    date_maintien timestamp without time zone,
    date_taux_max_pension timestamp without time zone,
    pension numeric(19,4),
    taux_pension numeric(19,4),
    date_dem_pension timestamp without time zone,
    "actRC" character varying(3),
    surcotisation boolean,
    date_transmission_bpai timestamp without time zone,
    adresse_rue text,
    adresse_codepost character varying(8),
    adresse_ville character varying(20),
    protelfix character varying(15),
    protelmob character varying(15),
    promail character varying(150),
    persotelfix character varying(15),
    persotelmob character varying(15),
    persomail character varying(150),
    gestionnaire character varying(5)
);

1. Multiplier les objets : vues en lecture seule, vues modifiables et transactions n'est il pas plus complexe,
   que de créer cette table temporaire, puis de passer les mises à jour dans les 10 tables du projet ?
   
2. Comment m'en sortir en restant le plus simple possible (le moins compliqué possible) ?
   
3. Comment transmettre au prochain DBA, mon projet clair, efficace à gérer,
   plutôt que de lui transmettre mon projet flou composé d'un salmigondi d'objets créés trop nombreux et illogiques ?

[SQLpro]

...
exemple 2 : la table 'telephone' est fille de la table 'personne'.
une relation de mise à jour en cascade et suppression en cascade gère l'intégrité référentielle entre le téléphone et la personne.
C'est cette relation qui nous permet d'agir en modification/mise à jour/suppression, sur tout ou partie des données de la relation....

D'abord on éviter de dire relation... La relation c'est en fait la table elle même. Ce dont tu parles est une contrainte d'intégrité référentielle (ou référence) qui découle d'une association entre deux entités.

Ensuite on évite le mode cascade autant que faire se peut... En effet ce mode est bloquant et peut vite devenir un cauchemar dans dans de grandes bases de données. Il ne faut pas oublier que toute transaction est journalisée, et journaliser des milliers de lignes à supprimer, cela coute et dure.... On préférera les mode ON DELETE SET NULL ou ON DELETE SET DEFAULT et on fera du ménage dans des batch aux heures creuses.....

A +

[martinbrait]

...on évite le mode cascade autant que faire se peut... En effet ce mode est bloquant et peut vite devenir un cauchemar dans dans de grandes bases de données.
Il ne faut pas oublier que toute transaction est journalisée, et journaliser des milliers de lignes à supprimer, cela coute et dure....
On préférera les mode ON DELETE SET NULL ou ON DELETE SET DEFAULT et on fera du ménage dans des batch aux heures creuses.....

Bien reçu SQLpro ! J'adore les directives simples, pour nous éviter un cauchemar. Au delà de 20 tables, fuir les liaisons en cascade.
Programmer des batch réguliers pour le nettoyage des données.

...pour créer une commande client, je vais ajouter:

• Une ligne dans la table Entête
• Une ligne dans la table des Adresses, pour l'adresse de livraison
• Une ligne dans la table des Adresses, pour l'adresse de facturation
• Une ou plusieurs lignes dans la table des Lignes
• Une ou plusieurs lignes dans la table des Règlement
• 0 ou plusieurs lignes dans une table "annexe"

Mais quoi qu'il en soit je passe systématiquement par des transactions quand je fais des mises à jour en base.

En plein dans le mille!
Ma question est :
Dois je mettre à jour la table Entête,Adresses,Lignes,Règlement,Annexe directement ?
Autrement dit :

1. Dois-je présenter à mon développeur directement les 5 tables ?
   auquel cas le développeur doit probablement programmer 5 formulaires et doit être vigilant à présenter des champs à mettre à jour, dans un ordre précis.
2. Dois-je présenter à mon développeur 5 vues modifiables, comportant des noms de colonnes aliasés, pour passer les écritures DELETE/INSERT/UPDATE ? auquel cas le développeur doit probablement programmer 5 formulaires et doit être vigilant à présenter des champs à mettre à jour, dans un ordre précis.
3. Dois-je présenter à mon développeur 1 table temporaire dont la structure contient toutes les colonnes de mes 5 tables.
   auquel cas, le développeur a plus de liberté et plus de facilité à programmer les formulaires.
   Je me chargerai de la mise à jour de contenu entre ma table temporaire et les contenus dans mes 5 tables, via un bouton déclenchant une transaction.

Y a t il des risques que ça devienne trop compliqué, avec une table temporaire par thématique / métier / schéma ?

[SQLpro]

aucune de vos interrogations 1, 2 ou 3 n'est la bonne...

Il faut utiliser des procédures stockées pour mettre à jour globalement l'ensemble des informations des 5 tables.

Une seule procédure suffit pour faire à la foi l'insert et l'udpate. Si la valeur de l'id autoincrémenté (IDENTITY) de la table de tête est passé en argument dans la proc, alors c'est un UPDATE sinon c'est un INSERT...

A +

[escartefigue]

Le typage des données et leur longueur ne doivent pas être choisis au hasard :

etat_civil.matricule character varying(7) NOT NULL,
etat_civil."deces_O_N" character varying(1),
etat_civil.nom character varying(55),
etat_civil.adresse_rue text,
etat_civil.adresse_codepost character varying(8),
[. . .]
etat_civil.hors_budget character varying(10),
hist_grade_ech.echelon_grade character varying(2),
[. . .]
hist_affectation.ref_sgap character varying(2),
[. . .]
etat_civil.adresse_ville character varying(20),
[. . .]
sexe character varying(50)

Du varchar (1) est aberrant ! Ça prend plus de place que du char fixe.
Du varchar de moins de 15 à 20 caractères est contre productif, car le varchar présente de nombreux inconvénients : déplacement dans les pages data et index en cas de modification effective de la longueur, ce qui provoque la désorganisation des pages, besoin d'alignement sur une longueur fixe pour les opérations de tri et de regroupement (ORDER BY, DISTINCT, GROUP BY, PARTITION BY)
Bref, remplacez les varchar courts par du char fixe.
Si le code postal ne concerne que des adresses françaises, du char(5) est requis
Quant à la ville, 20 caractères c'est très insuffisant, et la ville n'a rien à faire dans la table des adresses.
Voyez ICI comment modéliser les adresses, villes et codes postaux.
Enfin, le sexe sur du varchar(50)...

De plus, la table public.sortie n'a aucune PK , pas plus de FK

[SQLpro]

...
Enfin, le sexe sur du varchar(50)...

C'est parce qu'il doit être très long chez martinbrait, et varchar(50) c'est 400 bites... De quoi faire !