mhVXA5FH

1use p1703235;
2SET SQL_SAFE_UPDATES = 0;
3
4DROP TABLE IF EXISTS associations_achievements, associations_competitions, achievements, associations, competitions;
5
6CREATE TABLE associations(
7	id INT NOT NULL AUTO_INCREMENT,
8    Nom Text,
9    Description Varchar(254),
10    Domaine Text,
11    MDP Varchar(64),
12    Score int,
13    Classement int DEFAULT 0,
14    Groupe text,
15    PRIMARY KEY(id)
16);
17
18CREATE TABLE competitions(
19	id INT NOT NULL AUTO_INCREMENT,
20    NomCompetition Text,
21    Lieu Text,
22    DateCompet datetime,
23    terminee bool,
24    PRIMARY KEY(id)
25);
26
27CREATE TABLE achievements(
28	id int not null auto_increment,
29    titre text,
30    PRIMARY KEY(id)
31);
32
33CREATE TABLE associations_achievements(
34	association_id int,
35    achievement_id int,
36    date_obtention datetime,
37    PRIMARY KEY(association_id, achievement_id),
38    CONSTRAINT FOREIGN KEY(association_id) REFERENCES associations(id),
39    CONSTRAINT FOREIGN KEY(achievement_id) REFERENCES achievements(id)
40);
41
42CREATE TABLE associations_competitions(
43	association_id int,
44    competition_id int,
45    PRIMARY KEY(association_id, competition_id),
46    CONSTRAINT FOREIGN KEY(association_id) REFERENCES associations(id),
47    CONSTRAINT FOREIGN KEY(competition_id) REFERENCES competitions(id)
48);
49
50DROP TRIGGER IF EXISTS updateClassement;
51DROP TRIGGER IF EXISTS newClassement;
52DROP TRIGGER IF EXISTS updateScore;
53DROP FUNCTION IF EXISTS getFirstAssocScore;
54
55DELIMITER $
56
57CREATE TRIGGER updateClassement AFTER UPDATE ON associations
58FOR EACH ROW
59BEGIN
60	IF NOT (NEW.Classement = OLD.Classement) THEN
61	UPDATE Associations SET Classement = Classement+1 WHERE Classement >= NEW.Classement;
62    END IF;
63END$
64
65CREATE FUNCTION getFirstAssocScore(score int , classement int) RETURNS INT
66BEGIN
67	DECLARE endloop BOOL;
68	DECLARE parcoursAssoc CURSOR FOR (SELECT classement, score FROM associations);
69    DECLARE CONTINUE HANDLER FOR NOT FOUND SET endloop:=1;
70    OPEN parcoursAssoc;
71    WHILE NOT (endloop) DO
72		IF parcoursAssoc.score < score THEN
73			SET classement := parcoursAssoc.classement;
74        END IF;
75    END WHILE;
76    RETURN id;
77END$
78
79CREATE TRIGGER updateScore BEFORE UPDATE ON associations
80FOR EACH ROW
81BEGIN
82	IF NOT (NEW.Score = OLD.Score) THEN
83			SET NEW.Classement = getFirstAssocScore(NEW.Score);
84    END IF;
85END$
86 
87  INSERT INTO associations VALUES(null, "Park'O'Drone", "Park’o drone est une entreprise proposant des services événementiels sur mesure pour les entreprises, institutions et associations.", "Séminaires", "2ee61124a8695f5f3491df998d58a9160d7acac1ee28ec3578d158a1ff026ed4", 0, 1,'admin')$
88 
89 CREATE TRIGGER newClassement BEFORE INSERT ON associations
90 FOR EACH ROW
91 BEGIN
92	SET NEW.Classement = (SELECT Classement FROM associations ORDER BY Classement DESC LIMIT 1)+1;
93 END$
94 
95 DELIMITER ;
96 
97INSERT INTO associations VALUES(null, "IUT Lyon 1", "L'Excellence Technologique", "Enseignement", "043e44016b72f2c1630a9db609238712e5f85da6cee0a8c0b73a357715191735", 0, 0, 'user');
98
99INSERT INTO competitions VALUES(null, "Compétition Test", "Bourg-en-Bresse", "2018-12-25, 00:00:00", 0);
100INSERT INTO competitions VALUES(null, "Compétition Test Over", "Ambérieux", "2018-12-25, 00:00:00", 1);
101 
102 SELECT * FROM competitions;