<?php
declare(strict_types=1);
namespace DoctrineMigrations;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
/**
* Auto-generated Migration: Please modify to your needs!
*/
final class Version20220419114109 extends AbstractMigration
{
public function getDescription(): string
{
return 'Game + Group';
}
public function up(Schema $schema): void
{
// this up() migration is auto-generated, please modify it to your needs
ini_set('memory_limit', '-1');
$this->connection->executeQuery('ALTER TABLE `match` ADD league_id INT NOT NULL, ADD team1_id INT NOT NULL, ADD team2_id INT NOT NULL, ADD team_winner_id INT DEFAULT NULL');
$lastId = 0;
$missing = $missingGroups = 0;
$leagues = $teams = $groups = [];
while (true) {
$games = $this->connection->fetchAllAssociative("SELECT idmatch, idligue_match, idequipe1_match, idequipe2_match, idequipegagnante_match, idgroupe_match FROM `match` WHERE idmatch > {$lastId} LIMIT 1000");
if (empty($games)) {
break;
}
$queries = [];
foreach ($games as $game) {
$gameId = $lastId = $game['idmatch'];
$leagueServiceId = $game['idligue_match'];
$team1ServiceId = $game['idequipe1_match'];
$team2ServiceId = $game['idequipe2_match'];
$winnerTeamServiceId = $game['idequipegagnante_match'];
$groupId = $game['idgroupe_match'];
$leagueId = $leagues[$leagueServiceId] ?? $this->connection->fetchOne("SELECT idligue FROM ligue WHERE idligueservice_ligue = {$leagueServiceId}");
$team1Id = $teams[$team1ServiceId] ?? $this->connection->fetchOne("SELECT idequipe FROM equipe WHERE idequipeservice_equipe = {$team1ServiceId}");
$team2Id = $teams[$team2ServiceId] ?? $this->connection->fetchOne("SELECT idequipe FROM equipe WHERE idequipeservice_equipe = {$team2ServiceId}");
$winnerTeamId = !$winnerTeamServiceId ? null : ($teams[$winnerTeamServiceId] ?? $this->connection->fetchOne("SELECT idequipe FROM equipe WHERE idequipeservice_equipe = {$winnerTeamServiceId}"));
$groupIdVerified = null === $groupId ? null : ($groups[$groupId] ?? $this->connection->fetchOne("SELECT idgroupe FROM groupe WHERE idgroupe = {$groupId}"));
if (null !== $groupId && !$groupIdVerified) {
echo "Game.id {$gameId} : Missing Group.id {$groupId}\n";
$queries[] = "UPDATE `match` SET idgroupe_match = NULL WHERE idmatch = {$gameId}";
++$missingGroups;
}
if ($leagueId && $team1Id && $team2Id) {
$queries[] = "UPDATE `match` SET league_id = {$leagueId}, team1_id = {$team1Id}, team2_id = {$team2Id}, team_winner_id = " . ($winnerTeamId ?? 'NULL') . " WHERE idmatch = {$gameId}";
} else {
$queries[] = "DELETE FROM `match` WHERE idmatch = {$gameId}";
++$missing;
if (!$leagueId) {
echo "Game.id {$gameId} : Missing League.serviceId {$leagueServiceId}\n";
} elseif (!$team1Id) {
echo "Game.id {$gameId} : Missing Team1.serviceId {$team1ServiceId}\n";
} elseif (!$team2Id) {
echo "Game.id {$gameId} : Missing Team2.serviceId {$team2ServiceId}\n";
}
}
}
if (!empty($queries)) {
$this->connection->executeQuery(implode(';', $queries));
}
}
echo "Finished. {$missing} Games have been deleted and {$missingGroups} Game.groupId have been set to NULL\n";
$this->addSql('ALTER TABLE `match` ADD CONSTRAINT FK_7A5BC50588CB7EA5 FOREIGN KEY (idsaison_match) REFERENCES saison (idsaison)');
$this->addSql('ALTER TABLE `match` ADD CONSTRAINT FK_7A5BC50558AFC4DE FOREIGN KEY (league_id) REFERENCES ligue (idligue)');
$this->addSql('ALTER TABLE `match` ADD CONSTRAINT FK_7A5BC505E72BCFA4 FOREIGN KEY (team1_id) REFERENCES equipe (idequipe)');
$this->addSql('ALTER TABLE `match` ADD CONSTRAINT FK_7A5BC505F59E604A FOREIGN KEY (team2_id) REFERENCES equipe (idequipe)');
$this->addSql('ALTER TABLE `match` ADD CONSTRAINT FK_7A5BC505EAF9CA5F FOREIGN KEY (team_winner_id) REFERENCES equipe (idequipe)');
$this->addSql('ALTER TABLE `match` ADD CONSTRAINT FK_7A5BC5056959745C FOREIGN KEY (idgroupe_match) REFERENCES groupe (idgroupe)');
$this->addSql('CREATE INDEX IDX_7A5BC50558AFC4DE ON `match` (league_id)');
$this->addSql('CREATE INDEX IDX_7A5BC505E72BCFA4 ON `match` (team1_id)');
$this->addSql('CREATE INDEX IDX_7A5BC505F59E604A ON `match` (team2_id)');
$this->addSql('CREATE INDEX IDX_7A5BC505EAF9CA5F ON `match` (team_winner_id)');
$this->addSql('CREATE INDEX IDX_7A5BC5056959745C ON `match` (idgroupe_match)');
}
public function down(Schema $schema): void
{
// this down() migration is auto-generated, please modify it to your needs
$this->addSql('ALTER TABLE `match` DROP FOREIGN KEY FK_7A5BC50588CB7EA5');
$this->addSql('ALTER TABLE `match` DROP FOREIGN KEY FK_7A5BC50558AFC4DE');
$this->addSql('ALTER TABLE `match` DROP FOREIGN KEY FK_7A5BC505E72BCFA4');
$this->addSql('ALTER TABLE `match` DROP FOREIGN KEY FK_7A5BC505F59E604A');
$this->addSql('ALTER TABLE `match` DROP FOREIGN KEY FK_7A5BC505EAF9CA5F');
$this->addSql('ALTER TABLE `match` DROP FOREIGN KEY FK_7A5BC5056959745C');
$this->addSql('DROP INDEX IDX_7A5BC50558AFC4DE ON `match`');
$this->addSql('DROP INDEX IDX_7A5BC505E72BCFA4 ON `match`');
$this->addSql('DROP INDEX IDX_7A5BC505F59E604A ON `match`');
$this->addSql('DROP INDEX IDX_7A5BC505EAF9CA5F ON `match`');
$this->addSql('DROP INDEX IDX_7A5BC5056959745C ON `match`');
$this->addSql('ALTER TABLE `match` DROP league_id, DROP team1_id, DROP team2_id, DROP team_winner_id');
}
}