Recentemente, in qualità di docente in un corso base di amministrazione di database MySQL, ho affrontato anche tematiche un po' più avanzate come le stored function e le stored procedure.
Al termine del percorso formativo, richiesto di preparare un esercizio riassuntivo, ho optato per la realizzazione di un simulatore di campionato di calcio, il che in realtà presenta diversi problemi.
La struttura dati
Queste le tabelle (per la gestione delle squadre, degli incontri di calendario e della classifica) ed i vincoli previsti per l'esercizio:
CREATE TABLE "teams" (
"teamId" INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
"name" VARCHAR(191) NOT NULL
) ENGINE 'innodb' CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
CREATE TABLE "matches" (
"matchId" INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
"day" TINYINT UNSIGNED NOT NULL,
"team1Id" INTEGER UNSIGNED NOT NULL,
"goals1" TINYINT UNSIGNED,
"team2Id" INTEGER UNSIGNED NOT NULL,
"goals2" TINYINT UNSIGNED
) ENGINE 'innodb' CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE "matches" ADD CONSTRAINT "matches.team1Id.fk" FOREIGN KEY ("team1Id") REFERENCES "teams" ("teamId") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "matches" ADD CONSTRAINT "matches.team2Id.fk" FOREIGN KEY ("team2Id") REFERENCES "teams" ("teamId") ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE "standings" (
"standingId" INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
"teamId" INTEGER UNSIGNED NOT NULL,
"wonMatches" TINYINT UNSIGNED NOT NULL,
"drawnMatches" TINYINT UNSIGNED NOT NULL,
"lostMatches" TINYINT UNSIGNED NOT NULL,
"points" TINYINT UNSIGNED NOT NULL
) ENGINE 'innodb' CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
ALTER TABLE "standings" ADD CONSTRAINT "standings.teamId.fk" FOREIGN KEY ("teamId") REFERENCES "teams" ("teamId") ON DELETE CASCADE ON UPDATE CASCADE;
La procedura principale
Banalmente, l'esercizio richiede di inserire il nome delle squadre partecipanti al campionato e di giocarlo, stampando al termine la classifica finale.
In termine di codice, quindi, è sufficiente eseguire:
INSERT INTO "teams" ("name") VALUES
('Inter'),
('Milan'),
('Napoli'),
('Roma');
CALL "MZ_PLAY_SEASON"();
e preparare una procedura opportuna MZ_PLAY_SEASON che, a sua volta, ne richiami altre per eseguire i singoli compiti di controllo e calcolo:
--
-- Gioca la stagione.
--
CREATE PROCEDURE "MZ_PLAY_SEASON"()
BEGIN
-- Azzera i dati della stagione precedente.
CALL "MZ_RESET_SEASON"();
-- Controlla il numero di squadre partecipanti.
CALL "MZ_CHECK_TEAM_COUNT"();
-- Prepara il calendario.
CALL "MZ_SETUP_MATCHES"();
-- Prepara la classifica.
CALL "MZ_SETUP_STANDINGS"();
-- Gioca tutte le partite nella stagione.
CALL "MZ_PLAY_MATCHES"();
-- Stampa tutte le partite giocate nella stagione.
CALL "MZ_PRINT_MATCHES"();
-- Stampa la classifica.
CALL "MZ_PRINT_STANDINGS"();
END $$$
I controlli preliminari
Prima di giocare il campionato, è necessario effettuare alcune operazioni di pulizia e controllo.
Anzitutto bisogna eliminare i dati del campionato precedente (procedura MZ_RESET_SEASON):
--
-- Azzera i dati della stagione precedente.
--
CREATE PROCEDURE "MZ_RESET_SEASON"()
BEGIN
-- Svuota le tabelle persistenti.
TRUNCATE "standings";
TRUNCATE "matches";
-- Elimina le tabelle temporanee (che non dovrebbero comunque più esistere).
DROP TABLE IF EXISTS "temporaryHalfSeasonPossibleMatches";
DROP TABLE IF EXISTS "temporaryTeams";
END $$$
poi bisogna controllare che il numero di squadre partecipanti sia pari (procedura MZ_CHECK_TEAM_COUNT e funzione MZ_GET_TEAM_COUNT):
--
-- Conta il numero di squadre partecipanti.
--
CREATE FUNCTION "MZ_GET_TEAM_COUNT"() RETURNS INTEGER UNSIGNED NOT DETERMINISTIC
BEGIN
-- Dichiara le variabili utilizzate.
DECLARE teamCount INTEGER UNSIGNED;
-- Conta il numero di squadre.
SELECT COUNT("teams"."teamId") INTO teamCount
FROM "teams";
--
RETURN teamCount;
END $$$
--
-- Controlla il numero di squadre partecipanti.
--
CREATE PROCEDURE "MZ_CHECK_TEAM_COUNT"()
BEGIN
-- Controlla che il numero di squadre non sia dispari (resto della divisione
-- per 2 diverso da zero), nel qual caso interrompi la esecuzione segnalando un
-- errore.
IF ("MZ_GET_TEAM_COUNT"() % 2) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Il numero di squadre partecipanti deve essere pari!';
END IF;
END $$$
La preparazione del calendario
Questa è senz'altro la fase più delicata.
E' richiesto anzitutto (fase 1) di definire tutte le possibili partite giocabili nel solo girone di andata, incrociando i nomi delle squadre tra loro e salvando il risultato in una tabella temporanea.
Successivamente (fase 2) si genera il girone di andata, selezionando - per ogni giornata - una squadra a caso tra quelle che devono ancora giocare e, in seconda battuta, una partita a caso tra quelle dove tale squadra è coinvolta. Viene poi deciso, sempre casualmente, quali tra queste due squadre giocherà in casa.
Infine si genera il girone di ritorno, duplicando semplicemente i dati di quello di andata ma invertendo - per ogni incontro - il nome della squadra di casa e di quella fuori casa.
Il tutto come meglio specificato nella procedura MZ_SETUP_MATCHES:
--
-- Prepara il calendario.
--
CREATE PROCEDURE "MZ_SETUP_MATCHES"()
BEGIN
-- Dichiara le variabili utilizzate.
DECLARE _day, days TINYINT UNSIGNED DEFAULT 0;
DECLARE temporaryTeamCount INTEGER UNSIGNED DEFAULT 0;
DECLARE lastTeam1 BOOLEAN DEFAULT FALSE;
DECLARE _team1Id, _team2Id INTEGER UNSIGNED;
-- Dichiara i cursori e gli handler utilizzati.
DECLARE team1Cursor CURSOR FOR
SELECT "teams"."teamId"
FROM "teams"
ORDER BY "teams"."teamId";
DECLARE CONTINUE HANDLER FOR NOT FOUND SET lastTeam1 = TRUE;
--
-- Fase 1: definisci tutte le possibili partite del solo girone di andata.
--
-- Crea la tabella temporanea che conterrà tutte le possibili partite del solo
-- girone di andata.
CREATE TEMPORARY TABLE "temporaryHalfSeasonPossibleMatches" (
"matchId" INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
"team1Id" INTEGER UNSIGNED NOT NULL,
"team2Id" INTEGER UNSIGNED NOT NULL
) ENGINE 'innodb' CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
-- Per ogni squadra partecipante (squadra1)...
OPEN team1Cursor;
team1Loop: LOOP
-- ...prova a recuperare la chiave primaria della squadra, uscendo dal loop
-- se era l'ultimo record;
FETCH team1Cursor INTO _team1Id;
IF (lastTeam1) THEN
LEAVE team1Loop;
END IF;
-- ...(blocco secondario);
BEGIN
-- Dichiara le variabili utilizzate.
DECLARE lastTeam2 BOOLEAN DEFAULT FALSE;
-- DECLARE _team2Id INTEGER UNSIGNED;
-- Dichiara i cursori e gli handler utilizzati.
DECLARE team2Cursor CURSOR FOR
SELECT "teams"."teamId"
FROM "teams"
WHERE "teams"."teamId" > _team1Id
ORDER BY "teams"."teamId";
DECLARE CONTINUE HANDLER FOR NOT FOUND SET lastTeam2 = TRUE;
-- Per ogni altra squadra partecipante (squadra2, diversa da squadra1)...
OPEN team2Cursor;
team2Loop: LOOP
-- ...prova a recuperare la chiave primaria della squadra, uscendo dal
-- loop se era l'ultimo record;
FETCH team2Cursor INTO _team2Id;
IF (lastTeam2) THEN
LEAVE team2Loop;
END IF;
-- ...inserisci il record relativo alla partita possibile.
INSERT INTO "temporaryHalfSeasonPossibleMatches" ("team1Id", "team2Id") VALUES
(_team1Id, _team2Id);
END LOOP team2Loop;
CLOSE team2Cursor;
END;
END LOOP team1Loop;
CLOSE team1Cursor;
--
-- Fase 2: definisci il calendario giornata per giornata del girone di
-- andata), estraendo le partite da giocare tra quelle possibili
-- individuate nella fase precedente.
--
-- Calcola il numero di giornate totali.
SET days = ("MZ_GET_TEAM_COUNT"() - 1) * 2;
-- Per ogni giornata del girone di andata...
WHILE (_day < (days / 2)) DO
-- ...imposta il numero della giornata;
SET _day = _day + 1;
-- ...crea la tabella temporanea che conterrà tutte le squadre partecipanti
-- che devono ancora giocare nella giornata corrente, e calcola il loro
-- numero iniziale;
CREATE TEMPORARY TABLE "temporaryTeams" SELECT * FROM "teams";
SELECT COUNT("temporaryTeams"."teamId") INTO temporaryTeamCount
FROM "temporaryTeams";
-- ...fino a quando ci sono ancora squadre partecipanti che devono giocare
-- nella giornata corrente...
WHILE (temporaryTeamCount > 0) DO
-- ...estrai una squadra tra quelle residue (squadra1);
SELECT "temporaryTeams"."teamId" INTO _team1Id
FROM "temporaryTeams"
ORDER BY RAND()
LIMIT 1;
-- ...estrai una partita tra quelle possibili residue del girone di
-- andata, dove squadra1 giochi in casa o fuori casa, in modo da
-- determinare la squadra avversaria tra quelle che ancora devono giocare
-- nella giornata corrente (squadra2, diversa da squadra1);
SELECT IF("temporaryHalfSeasonPossibleMatches"."team1Id" = _team1Id, "temporaryHalfSeasonPossibleMatches"."team2Id", "temporaryHalfSeasonPossibleMatches"."team1Id") AS "x" INTO _team2Id
FROM "temporaryHalfSeasonPossibleMatches"
WHERE ("temporaryHalfSeasonPossibleMatches"."team1Id" = _team1Id)
OR ("temporaryHalfSeasonPossibleMatches"."team2Id" = _team1Id)
HAVING "x" IN ( SELECT "temporaryTeams"."teamId" FROM "temporaryTeams" )
ORDER BY RAND()
LIMIT 1;
-- ...determina casualmente chi deve giocare in casa e chi fuori;
IF (RAND() < 0.5) THEN
INSERT INTO "matches" ("day", "team1Id", "team2Id") VALUES
(_day, _team1Id, _team2Id);
ELSE
INSERT INTO "matches" ("day", "team1Id", "team2Id") VALUES
(_day, _team2Id, _team1Id);
END IF;
-- ...rimuovi la partita in questione tra quelle possibili del girone di
-- andata, indipendentemente dal fatto che le due squadre giochino in
-- casa o fuori casa;
DELETE
FROM "temporaryHalfSeasonPossibleMatches"
WHERE (("temporaryHalfSeasonPossibleMatches"."team1Id" = _team1Id) AND ("temporaryHalfSeasonPossibleMatches"."team2Id" = _team2Id))
OR (("temporaryHalfSeasonPossibleMatches"."team1Id" = _team2Id) AND ("temporaryHalfSeasonPossibleMatches"."team2Id" = _team1Id));
-- ...elimina le due squadre in questione da quelle che devono ancora
-- giocare nella giornata corrente;
DELETE
FROM "temporaryTeams"
WHERE ("temporaryTeams"."teamId" = _team1Id)
OR ("temporaryTeams"."teamId" = _team2Id);
-- ...ricalcola il numero delle squadre partecipanti che devono ancora
-- giocare nella giornata corrente;
SELECT COUNT("temporaryTeams"."teamId") INTO temporaryTeamCount
FROM "temporaryTeams";
END WHILE;
-- ...elimina la tabella delle squadre che devono ancora giocare nella
-- giornata corrente (che a questo punto è comunque vuota).
DROP TABLE IF EXISTS "temporaryTeams";
END WHILE;
-- Elimina la tabella di tutte le possibili partite del girone di andata (che
-- a questo punto è comunque vuota).
DROP TABLE IF EXISTS "temporaryHalfSeasonPossibleMatches";
-- Genera il girone di ritorno, copiando i record delle partite finora
-- inserite (girone di andata), aggiornando il numero della giornata ed
-- invertendo la squadra in casa e quella fuori casa.
INSERT INTO "matches" ("day", "team1Id", "team2Id")
SELECT "matches"."day" + (days / 2), "matches"."team2Id", "matches"."team1Id"
FROM "matches";
END $$$
La preparazione della classifica
La procedura MZ_SETUP_STANDINGS si occupa semplicemente di inizializzare i dati della classifica a zero per tutte le squadre partecipanti:
--
-- Prepara la classifica.
--
CREATE PROCEDURE "MZ_SETUP_STANDINGS"()
BEGIN
-- Dichiara le variabili utilizzate.
DECLARE lastTeam BOOLEAN DEFAULT FALSE;
DECLARE _teamId INTEGER UNSIGNED;
-- Dichiara i cursori e gli handler utilizzati.
DECLARE teamCursor CURSOR FOR
SELECT "teams"."teamId"
FROM "teams";
DECLARE CONTINUE HANDLER FOR NOT FOUND SET lastTeam = TRUE;
-- Per ogni squadra partecipante...
OPEN teamCursor;
teamLoop: LOOP
-- ...prova a recuperare la chiave primaria della squadra, uscendo dal loop
-- se era l'ultimo record;
FETCH teamCursor INTO _teamId;
IF (lastTeam) THEN
LEAVE teamLoop;
END IF;
-- ...inserisci la squadra nella classifica, inizializzando con 0 vittorie,
-- 0 pareggi, 0 sconfitte e 0 punti.
INSERT INTO "standings" ("teamId", "wonMatches", "drawnMatches", "lostMatches", "points") VALUES
(_teamId, 0, 0, 0, 0);
END LOOP teamLoop;
CLOSE teamCursor;
END $$$
Le giocate delle partite
Giocare tutti gli incontri del campionato è molto semplice, ed è compito della procedura MZ_PLAY_MATCHES che a sua volta, per comodità, richiama la procedura MZ_PLAY_DAY_MATCHES per giocare tutti gli incontri di una singola giornata; quest'ultima procedura, per ogni partita, assegna ad ogni squadra un numero di goal casuale tra 0 e 3 per poi valutare chi ha vinto - oppure se vi è stato un pareggio - ed aggiornare la classifica di conseguenza:
--
-- Gioca tutte le partite in una giornata.
--
CREATE PROCEDURE "MZ_PLAY_DAY_MATCHES"(IN _day TINYINT UNSIGNED)
BEGIN
-- Dichiara le variabili utilizzate.
DECLARE lastMatch BOOLEAN DEFAULT FALSE;
DECLARE _matchId, _team1Id, _team2Id INTEGER UNSIGNED;
DECLARE _goals1, _goals2 TINYINT UNSIGNED;
-- Dichiara i cursori e gli handler utilizzati.
DECLARE matchCursor CURSOR FOR
SELECT "matches"."matchId", "matches"."team1Id", "matches"."team2Id"
FROM "matches"
WHERE "matches"."day" = _day;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET lastMatch = TRUE;
-- Per ogni partita...
OPEN matchCursor;
matchLoop: LOOP
-- ...prova a recuperare la chiave primaria della partita e delle squadre in
-- casa e fuori casa, uscendo dal loop se era l'ultimo record;
FETCH matchCursor INTO _matchId, _team1Id, _team2Id;
IF (lastMatch) THEN
LEAVE matchLoop;
END IF;
-- ...decidi casualmente il numero di goal delle due squadre;
SET _goals1 = ROUND(RAND() * 3);
SET _goals2 = ROUND(RAND() * 3);
UPDATE "matches"
SET "goals1" = _goals1,
"goals2" = _goals2
WHERE "matchId" = _matchId;
-- ...valuta il risultato finale ed aggiorna la classifica di conseguenza.
IF (_goals1 > _goals2) THEN
UPDATE "standings"
SET "wonMatches" = "wonMatches" + 1,
"points" = "points" + 3
WHERE "standings"."teamId" = _team1Id;
UPDATE "standings"
SET "lostMatches" = "lostMatches" + 1
WHERE "standings"."teamId" = _team2Id;
ELSEIF (_goals1 = _goals2) THEN
UPDATE "standings"
SET "drawnMatches" = "drawnMatches" + 1,
"points" = "points" + 1
WHERE "standings"."teamId" = _team1Id;
UPDATE "standings"
SET "drawnMatches" = "drawnMatches" + 1,
"points" = "points" + 1
WHERE "standings"."teamId" = _team2Id;
ELSE
UPDATE "standings"
SET "lostMatches" = "lostMatches" + 1
WHERE "standings"."teamId" = _team1Id;
UPDATE "standings"
SET "wonMatches" = "wonMatches" + 1,
"points" = "points" + 3
WHERE "standings"."teamId" = _team2Id;
END IF;
END LOOP matchLoop;
CLOSE matchCursor;
END $$$
--
-- Gioca tutte le partite nella stagione.
--
CREATE PROCEDURE "MZ_PLAY_MATCHES"()
BEGIN
-- Dichiara le variabili utilizzate.
DECLARE _day, days TINYINT UNSIGNED DEFAULT 0;
-- Calcola il numero di giornate totali.
SET days = ("MZ_GET_TEAM_COUNT"() - 1) * 2;
-- Per ogni giornata...
WHILE (_day < days) DO
SET _day = _day + 1;
-- ...gioca tutte le partite nella giornata.
CALL "MZ_PLAY_DAY_MATCHES"(_day);
END WHILE;
END $$$
La stampa delle partite giocate
Una volta terminati gli incontri, la stampa dei risultati è compito della procedura MZ_PRINT_MATCHES che nuovamente, per comodità, si appoggia alla procedura MZ_PRINT_DAY_MATCHES:
--
-- Stampa tutte le partite giocate in una giornata.
--
CREATE PROCEDURE "MZ_PRINT_DAY_MATCHES"(IN _day TINYINT UNSIGNED)
BEGIN
-- Estrai i dati necessari per stampare le partite giocate nella giornata.
SELECT "matches"."day", "teams1"."name" AS "team1", "teams2"."name" AS "team2", "matches"."goals1", "matches"."goals2"
FROM "matches"
JOIN "teams" AS "teams1" ON "matches"."team1Id" = "teams1"."teamId"
JOIN "teams" AS "teams2" ON "matches"."team2Id" = "teams2"."teamId"
WHERE "matches"."day" = _day
ORDER BY "teams1"."name";
END $$$
--
-- Stampa tutte le partite giocate nella stagione.
--
CREATE PROCEDURE "MZ_PRINT_MATCHES"()
BEGIN
-- Dichiara le variabili utilizzate.
DECLARE _day, days TINYINT UNSIGNED DEFAULT 0;
-- Calcola il numero di giornate totali.
SET days = ("MZ_GET_TEAM_COUNT"() - 1) * 2;
-- Per ogni giornata...
WHILE (_day < days) DO
SET _day = _day + 1;
-- ...stampa tutte le partite giocate nella giornata.
CALL "MZ_PRINT_DAY_MATCHES"(_day);
END WHILE;
END $$$
La stampa della classifica finale
Infine, la stampa della situazione al termine del campionato è affidata alla procedura MZ_PRINT_STANDINGS:
--
-- Stampa la classifica.
--
CREATE PROCEDURE "MZ_PRINT_STANDINGS"()
BEGIN
-- Estrai i dati necessari per stampare la classifica ordinata.
SELECT "teams"."name", "standings"."wonMatches" AS "W", "standings"."drawnMatches" AS "D", "standings"."lostMatches" AS "L", "standings"."points" AS "p"
FROM "standings"
JOIN "teams" ON "standings"."teamId" = "teams"."teamId"
ORDER BY "standings"."points" DESC, "standings"."wonMatches" DESC, "standings"."drawnMatches" DESC;
END $$$
Conclusioni
Senz'altro il codice è migliorabile in alcuni punti, ma la base di questo esercizio-gioco può risultare utile per dimostrare la potenza delle stored procedure, delle stored function e delle tabelle temporanee.