Select Git revision
-
Jean-Christophe authoredJean-Christophe authored
ScriptSQL.sql 4.13 KiB
DROP TABLE IF EXISTS Messages CASCADE;
DROP TABLE IF EXISTS Conversations CASCADE;
DROP TABLE IF EXISTS Utilisateur CASCADE;
CREATE TABLE Utilisateur
(
pseudo VARCHAR,
email VARCHAR,
pwd VARCHAR,
CONSTRAINT pk_Utilisateur PRIMARY KEY (email)
);
CREATE TABLE Conversations
(
idConv SERIAL,
nomConv VARCHAR,
createur VARCHAR,
dateCrea DATE default CURRENT_DATE,
pwd VARCHAR,
CONSTRAINT pk_Conversations PRIMARY KEY (idConv),
CONSTRAINT fk_Utilisateur FOREIGN KEY (createur)
REFERENCES Utilisateur(email)
);
CREATE TABLE Messages
(
idMessage SERIAL,
idConv int,
jour DATE default CURRENT_DATE,
heure TIME default CURRENT_TIME,
message VARCHAR,
utilisateur VARCHAR,
CONSTRAINT pk_Messages PRIMARY KEY (idMessage),
CONSTRAINT fk_Utilisateur FOREIGN KEY (utilisateur)
REFERENCES Utilisateur(email),
CONSTRAINT fk_Conversations FOREIGN KEY (idConv)
REFERENCES Conversations(idConv)
);
-- Création des utilisateur
INSERT INTO Utilisateur(pseudo, email, pwd)
VALUES('Alex2n','alexandre.dehaine.etu@univ-lille.fr', MD5('mdp'));
INSERT INTO Utilisateur(pseudo, email, pwd)
VALUES('Shiira02','audrey.v.etu@univ-lille.fr', MD5('mdp'));
-- Créations d'une Conversation 1 et de messages
INSERT INTO Conversations(nomConv, createur, pwd)
VALUES('Conversation 1', (SELECT email FROM Utilisateur WHERE email = 'audrey.v.etu@univ-lille.fr'), MD5('mdp'));
INSERT INTO Messages(message, utilisateur, idConv)
VALUES('Salut', (SELECT email FROM Utilisateur WHERE email = 'alexandre.dehaine.etu@univ-lille.fr'), (SELECT idConv FROM Conversations WHERE nomConv = 'Conversation 1'));
INSERT INTO Messages(message, utilisateur, idConv)
VALUES('Hello', (SELECT email FROM Utilisateur WHERE email = 'audrey.v.etu@univ-lille.fr'), (SELECT idConv FROM Conversations WHERE nomConv = 'Conversation 1'));
-- Créations d'une Conversation 2 et de messages
INSERT INTO Conversations(nomConv, createur, pwd)
VALUES('Conversation 2', (SELECT email FROM Utilisateur WHERE email = 'alexandre.dehaine.etu@univ-lille.fr'), MD5('mdp'));
INSERT INTO Messages(message, utilisateur, idConv)
VALUES('Conv 2', (SELECT email FROM Utilisateur WHERE email = 'alexandre.dehaine.etu@univ-lille.fr'), (SELECT idConv FROM Conversations WHERE nomConv = 'Conversation 2'));
INSERT INTO Messages(message, utilisateur, idConv)
VALUES('Conv 2', (SELECT email FROM Utilisateur WHERE email = 'audrey.v.etu@univ-lille.fr'), (SELECT idConv FROM Conversations WHERE nomConv = 'Conversation 2'));
-- Ajout d'un autre message dans la conv 1
INSERT INTO Messages(message, utilisateur, idConv)
VALUES('Comment ca va ?', (SELECT email FROM Utilisateur WHERE email = 'alexandre.dehaine.etu@univ-lille.fr'), (SELECT idConv FROM Conversations WHERE nomConv = 'Conversation 1'));
INSERT INTO Messages(message, utilisateur, idConv)
VALUES('Ca va et toi ?', (SELECT email FROM Utilisateur WHERE email = 'audrey.v.etu@univ-lille.fr'), (SELECT idConv FROM Conversations WHERE nomConv = 'Conversation 1'));
-- Select pratiques
SELECT pseudo FROM Utilisateur WHERE email = 'alexandre.dehaine.etu@univ-lille.fr'; -- récupère le nom d'utilisateur depuis l'email
SELECT u.pseudo, m.message FROM Messages AS m JOIN Utilisateur AS u ON m.utilisateur = u.email where idConv = (SELECT idConv FROM Conversations where nomConv = 'Conversation 1') ORDER BY m.jour, m.heure; -- récupère toute une conversation avec le nom des utilisateur ainsi que les messages qu'ils ont envoyé dans l'ordre de lecture classique
SELECT u.pseudo, m.message FROM Messages AS m JOIN Utilisateur AS u ON m.utilisateur = u.email where idConv = (SELECT idConv FROM Conversations where nomConv = 'Conversation 1') AND LOWER(m.message) LIKE '%ca va%' ORDER BY m.jour, m.heure; -- récupère tous les messages d'une conversation avec le nom des utilisateur ainsi que les messages qu'ils ont envoyé contenant les mot "ca va" dans l'ordre de lecture classique
SELECT DISTINCT utilisateur FROM Messages WHERE idConv = 1;