Skip to content
Snippets Groups Projects
Select Git revision
  • 1de65d2ee92d656509068a4e32f344f1f0b279a0
  • main default protected
2 results

ScriptSQL.sql

Blame
  • 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;