gr2BE1HA

1DROP TABLE IF EXISTS Liste, Contient, Message, Destinataire, Utilisateur CASCADE;
2
3CREATE TABLE Utilisateur (
4	login text PRIMARY KEY,
5	email text CHECK (email like '%@%.%'),
6	UNIQUE (email)
7);
8
9
10CREATE TABLE Liste (
11	login text REFERENCES Utilisateur,
12	idListe int primary key,
13	nom text NOT NULL
14);
15
16CREATE TABLE Contient (
17	login text REFERENCES Utilisateur(login),
18	idListe int REFERENCES Liste(idListe),
19	PRIMARY KEY (login, idListe)
20);
21
22CREATE TABLE Message (
23	idMessage int PRIMARY KEY,
24	heureMsg timestamp NOT NULL,
25	textMsg text NOT NULL,
26	login text REFERENCES Utilisateur(login)
27);
28
29CREATE TABLE Destinataire (
30	idMessage int REFERENCES Message,
31	idListe int REFERENCES Liste,
32	PRIMARY KEY (idMessage, idListe)
33);
34
35INSERT INTO Utilisateur VALUES
36	('username','username@gmail.com'),
37	('Admin','admin@gmail.com');
38INSERT INTO Liste VALUES ('username',1,'BDD');
39INSERT INTO Contient VALUES ('username',1);    
40INSERT INTO Contient VALUES ('Admin',1);
41INSERT INTO Message VALUES (1,current_timestamp,'SELECT ?','username');
42INSERT INTO Destinataire VALUES (1,1);
43INSERT INTO Liste VALUES ('Admin',2,'test');
44INSERT INTO Utilisateur VALUES ('Antoine', 'antoine@domainname.com');
45INSERT INTO Liste VALUES ('username', 3, 'nomListe');
46INSERT INTO Contient VALUES ('Antoine',2);
47INSERT INTO Message VALUES (2,current_timestamp,'eh parle bien', 'username');
48
49UPDATE Message SET textMsg = 'Eh parle bien !' WHERE idMessage = 2;
50DELETE FROM Destinataire WHERE idMessage = 1 and idListe = 1; DELETE FROM Message WHERE idMessage = 1; 
51SELECT * FROM Message JOIN Utilisateur USING (login) WHERE login='username' ORDER BY heureMsg;
52SELECT login FROM Liste WHERE idListe=1 ORDER BY login;
53SELECT login FROM contient WHERE login='username' ORDER BY login;
54SELECT Liste.nom, Count(contient.login) FROM Liste JOIN contient USING (idListe)  WHERE Liste.login = 'username' GROUP BY Liste.nom;
55SELECT textMsg, heureMsg, login FROM Message JOIN Destinataire USING (idMessage) JOIN Liste USING (idListe) JOIN Contient USING (idListe) JOIN Utilisateur USING (login) WHERE login='username' ORDER BY heureMsg;