<?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 Version20220412115006 extends AbstractMigration
{
public function getDescription(): string
{
return 'Roster';
}
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 roster ADD player_id INT NOT NULL, ADD league_id INT DEFAULT NULL, ADD team_id INT NOT NULL');
$lastId = 0;
$missing = 0;
$players = $leagues = $teams = $seasons = [];
while (true) {
$rosters = $this->connection->fetchAllAssociative("SELECT idroster, idjoueur_roster, idligue_roster, idequipe_roster, idsaison_roster FROM roster WHERE idroster > {$lastId} LIMIT 100");
if (empty($rosters)) {
break;
}
$queries = [];
foreach ($rosters as $roster) {
$rosterId = $lastId = $roster['idroster'];
$playerServiceId = $roster['idjoueur_roster'];
$leagueServiceId = $roster['idligue_roster'];
$teamServiceId = $roster['idequipe_roster'];
$seasonId = $roster['idsaison_roster'];
$playerId = !$playerServiceId ? null : ($players[$playerServiceId] ?? $this->connection->fetchOne("SELECT idjoueur from joueur WHERE idjoueurservice_joueur = {$playerServiceId}") ?? null);
$leagueId = !$leagueServiceId ? null : ($leagues[$leagueServiceId] ?? $this->connection->fetchOne("SELECT idligue from ligue WHERE idligueservice_ligue = {$leagueServiceId} AND (lang_ligue IS NULL OR lang_ligue = '')") ?? null);
$teamId = !$teamServiceId ? null : ($teams[$teamServiceId] ?? $this->connection->fetchOne("SELECT idequipe from equipe WHERE idequipeservice_equipe = {$teamServiceId}") ?? null);
$seasonId = !$seasonId ? null : ($seasons[$seasonId] ?? $this->connection->fetchOne("SELECT idsaison from saison WHERE idsaison = {$seasonId}") ?? null);
if ($playerId && $teamId && $seasonId) {
$players[$playerServiceId] = $playerId;
$leagues[$leagueServiceId] = $leagueId;
$teams[$teamServiceId] = $teamId;
$queries[] = "UPDATE roster SET player_id = {$playerId}, league_id = " . ($leagueId ?? 'NULL') . ", team_id = {$teamId} WHERE idroster = {$rosterId}";
} else {
$queries[] = "DELETE FROM roster WHERE idroster = {$rosterId}";
++$missing;
if (!$playerId) {
echo "Roster {$rosterId} : Missing Player.serviceId {$playerServiceId}\n";
} elseif (!$teamId) {
echo "Roster {$rosterId} : Missing Team.serviceId {$teamServiceId}\n";
} elseif (!$seasonId) {
echo "Roster {$rosterId} : Missing Season.id {$seasonId}\n";
}
}
}
if (!empty($queries)) {
$this->connection->executeQuery(implode(';', $queries));
}
}
$rosters = $this->connection->fetchAllAssociative('SELECT idroster FROM roster WHERE player_id = 0');
foreach ($rosters as $roster) {
$rosterId = $roster['idroster'];
$this->connection->executeQuery("DELETE FROM roster WHERE idroster = {$rosterId}");
echo "Roster {$rosterId} : Missing Player.id (0)\n";
++$missing;
}
$rosters = $this->connection->fetchAllAssociative('SELECT idroster FROM roster WHERE team_id = 0');
foreach ($rosters as $roster) {
$rosterId = $roster['idroster'];
$this->connection->executeQuery("DELETE FROM roster WHERE idroster = {$rosterId}");
echo "Roster {$rosterId} : Missing Team.id (0)\n";
++$missing;
}
echo "Finish - {$missing} Rosters have been deleted";
$this->addSql('ALTER TABLE roster ADD CONSTRAINT FK_60B9ADF999E6F5DF FOREIGN KEY (player_id) REFERENCES joueur (idjoueur)');
$this->addSql('ALTER TABLE roster ADD CONSTRAINT FK_60B9ADF9B69D9EAA FOREIGN KEY (idsaison_roster) REFERENCES saison (idsaison)');
$this->addSql('ALTER TABLE roster ADD CONSTRAINT FK_60B9ADF958AFC4DE FOREIGN KEY (league_id) REFERENCES ligue (idligue)');
$this->addSql('ALTER TABLE roster ADD CONSTRAINT FK_60B9ADF9296CD8AE FOREIGN KEY (team_id) REFERENCES equipe (idequipe)');
$this->addSql('CREATE INDEX IDX_60B9ADF999E6F5DF ON roster (player_id)');
$this->addSql('CREATE INDEX IDX_60B9ADF958AFC4DE ON roster (league_id)');
$this->addSql('CREATE INDEX IDX_60B9ADF9296CD8AE ON roster (team_id)');
}
public function down(Schema $schema): void
{
// this down() migration is auto-generated, please modify it to your needs
$this->addSql('ALTER TABLE roster DROP FOREIGN KEY FK_60B9ADF999E6F5DF');
$this->addSql('ALTER TABLE roster DROP FOREIGN KEY FK_60B9ADF9B69D9EAA');
$this->addSql('ALTER TABLE roster DROP FOREIGN KEY FK_60B9ADF958AFC4DE');
$this->addSql('ALTER TABLE roster DROP FOREIGN KEY FK_60B9ADF9296CD8AE');
$this->addSql('DROP INDEX IDX_60B9ADF999E6F5DF ON roster');
$this->addSql('DROP INDEX IDX_60B9ADF958AFC4DE ON roster');
$this->addSql('DROP INDEX IDX_60B9ADF9296CD8AE ON roster');
$this->addSql('ALTER TABLE roster DROP player_id, DROP league_id, DROP team_id');
}
}