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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
| -- Table: public.te_personne_prs
-- DROP TABLE public.te_personne_prs;
CREATE TABLE public.te_personne_prs
(
prs_id integer NOT NULL DEFAULT nextval('te_personne_prs_prs_id_seq'::regclass), -- Identifiant de la personne
prs_nom character varying(40) NOT NULL, -- Nom usuel de la personne
prs_prenom character varying(40) NOT NULL DEFAULT ''::character varying, -- Prénom usuel de la personne
prs_date_naissance date NOT NULL DEFAULT ('now'::text)::date,
CONSTRAINT pk_prs_id PRIMARY KEY (prs_id),
CONSTRAINT xu_prs_nom_prenom_naissance UNIQUE (prs_nom, prs_prenom, prs_date_naissance)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.te_personne_prs
OWNER TO postgres;
COMMENT ON TABLE public.te_personne_prs
IS 'Personnes physiques';
COMMENT ON COLUMN public.te_personne_prs.prs_id IS 'Identifiant de la personne';
COMMENT ON COLUMN public.te_personne_prs.prs_nom IS 'Nom usuel de la personne';
COMMENT ON COLUMN public.te_personne_prs.prs_prenom IS 'Prénom usuel de la personne';
-- Table: public.th_utilisateur_uti
-- DROP TABLE public.th_utilisateur_uti;
CREATE TABLE public.th_utilisateur_uti
(
uti_id_personne integer NOT NULL, -- Identifiant de la personne
uti_login character varying(40) NOT NULL, -- Nom d'utilisateur
uti_mot_passe character varying(40) NOT NULL, -- Mot de passe de l'utilisateur
uti_date_creation date NOT NULL DEFAULT ('now'::text)::date, -- Date de création de l'utilisateur
uti_valide boolean NOT NULL DEFAULT false, -- L'utilisateur est-il valide ?
CONSTRAINT pk_uti_id_personne PRIMARY KEY (uti_id_personne),
CONSTRAINT fk_uti_id_personne FOREIGN KEY (uti_id_personne)
REFERENCES public.te_personne_prs (prs_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE, -- Clé étrangère référençant te_personne_prs
CONSTRAINT xu_uti_login UNIQUE (uti_login)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.th_utilisateur_uti
OWNER TO postgres;
COMMENT ON TABLE public.th_utilisateur_uti
IS 'Utilisateurs de l''application';
COMMENT ON COLUMN public.th_utilisateur_uti.uti_id_personne IS 'Identifiant de la personne';
COMMENT ON COLUMN public.th_utilisateur_uti.uti_login IS 'Nom d''utilisateur';
COMMENT ON COLUMN public.th_utilisateur_uti.uti_mot_passe IS 'Mot de passe de l''utilisateur';
COMMENT ON COLUMN public.th_utilisateur_uti.uti_date_creation IS 'Date de création de l''utilisateur';
COMMENT ON COLUMN public.th_utilisateur_uti.uti_valide IS 'L''utilisateur est-il valide ?';
COMMENT ON CONSTRAINT fk_uti_id_personne ON public.th_utilisateur_uti IS 'Clé étrangère référençant te_personne_prs';
-- Vue des utilisateurs avec leur nom et leur prénom
CREATE VIEW v_utilisateur AS
SELECT p.prs_id, p.prs_nom, p.prs_prenom,
u.uti_login
FROM th_utilisateur_uti u
INNER JOIN te_personne_prs p ON p.prs_id = u.uti_id_personne;
-- Procédure de création d'un utilisateur
CREATE OR REPLACE FUNCTION pi_ajout_utilisateur
(
INOUT id_personne BIGINT, -- Identifiant de la personne
-- Données pour la création éventuelle de la personne
IN nom_usuel VARCHAR(40), -- Nom usuel de la personne
IN prenom_usuel VARCHAR(40), -- Prénom usuel de la personne
IN date_naissance DATE, -- Date de naissance de la personne
-- Données pour la création de l'utilisateur
IN login VARCHAR(40), -- Nom d'utilisateur
IN mot_passe VARCHAR(40) -- Mot de passe de l'utilisateur (on suppose qu'il est crypté par l'application)
-- Remarque : la date de création et la validité de l'utilisateur sont automatiques lors de sa création
)
AS
$BODY$
DECLARE msg_erreur CHARACTER VARYING DEFAULT ''; -- Message d'erreur éventuel
BEGIN
-- Vérification de la date de naissance
IF date_naissance NOT BETWEEN CURRENT_DATE - INTERVAL '100' YEAR AND CURRENT_DATE - INTERVAL '10' YEAR THEN
-- Si la date de naissance n'est pas comprise entre il y a 100 ans et il y a 10 ans => Erreur
RAISE EXCEPTION 'Date de naissance incorrecte'
USING ERRCODE = 'DVP01';
END IF;
IF id_personne IS NULL THEN
-- Identifiant de la personne non fourni : on crée d'abord la personne
INSERT INTO te_personne_prs (prs_nom, prs_prenom, prs_date_naissance)
VALUES (nom_usuel, prenom_usuel, date_naissance);
-- Récupération du prs_id généré par l'auto-incrémentation
SELECT CURRVAL('te_personne_prs_prs_id_seq') INTO id_personne;
END IF;
-- Création de l'utilisateur
INSERT INTO th_utilisateur_uti (uti_id_personne, uti_login, uti_mot_passe, uti_date_creation, uti_valide)
VALUES (id_personne, login, mot_passe, CURRENT_DATE, FALSE);
EXCEPTION
WHEN foreign_key_violation THEN
-- En cas d'erreur de clé étrangère, on récupère l'erreur et on la personnalise
GET STACKED DIAGNOSTICS msg_erreur = MESSAGE_TEXT; -- On récupère le message de l'erreur PostgreSQL
IF POSITION('fk_uti_id_personne' IN msg_erreur) > 0 THEN
-- Erreur de clé étrangère sur l'id_personne
RAISE EXCEPTION 'Identifiant de la personne non trouvé' -- Notre texte d'erreur personnalisé
USING ERRCODE = 'DEV01'; -- Notre code d'erreur personnalisé
END IF;
WHEN unique_violation THEN
-- En cas de violation de contrainte d'unicité
GET STACKED DIAGNOSTICS msg_erreur = MESSAGE_TEXT;
IF POSITION('xu_uti_login' IN msg_erreur) > 0 THEN
-- Violation de l'unicité du login
RAISE EXCEPTION 'Login existant' -- Notre texte d'erreur personnalisé
USING ERRCODE = 'DEV02'; -- Notre code d'erreur personnalisé
ELSEIF POSITION('xu_prs_nom_prenom_naissance' IN msg_erreur) > 0 THEN
-- Violation de l'unicité du triplet {prs_nom, psr_prenom, psr_date_naissance}
RAISE EXCEPTION 'Une personne portant ce nom, ce prénom et née ce jour existe déjà.' -- Notre texte d'erreur personnalisé
USING ERRCODE = 'DEV03'; -- Notre code d'erreur personnalisé
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE; |
