generated from DevLab-umontp/template-DiscordBot
-
Notifications
You must be signed in to change notification settings - Fork 1
/
createTables.sql
35 lines (35 loc) · 827 Bytes
/
createTables.sql
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
CREATE TABLE Affection (
idUser BIGINT UNSIGNED,
idGuild BIGINT UNSIGNED,
indexPseudo INT(3) UNSIGNED,
CONSTRAINT pk_Affection PRIMARY KEY (idUser, idGuild)
);
--
CREATE TABLE Pseudos(
indexPseudo INT(3) UNSIGNED,
CONSTRAINT pk_Pseudos PRIMARY KEY (indexPseudo)
);
--
DELIMITER / /
CREATE OR REPLACE PROCEDURE init_pseudos (MaxPseudo INT) BEGIN WHILE MaxPseudo >= 0 DO
INSERT INTO Pseudos (indexPseudo)
VALUES (MaxPseudo);
SET MaxPseudo = MaxPseudo - 1;
END WHILE;
END init_pseudos;
/ /
DELIMITER / /
CREATE OR REPLACE FUNCTION recupere_pseudo_non_utilise (idGuild_p BIGINT UNSIGNED) RETURNS INT BEGIN
DECLARE resultat INT;
SELECT * INTO resultat
FROM Pseudos
WHERE indexPseudo NOT IN (
SELECT indexPseudo
FROM Affection
WHERE idGuild = idGuild_p
)
ORDER BY RAND()
LIMIT 1;
RETURN resultat;
END;
/ /