<?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 Version20220420180223 extends AbstractMigration
{
public function getDescription(): string
{
return 'StatDay';
}
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("UPDATE stajournee SET date_stajournee = NULL WHERE `date_stajournee` LIKE '%0000-00-00%'");
$row0 = $this->connection->fetchAssociative('SELECT * FROM stajournee LIMIT 1');
if (!isset($row0['player_id'])) {
$this->connection->executeQuery('ALTER TABLE stajournee ADD player_id INT NOT NULL, ADD league_id INT NOT NULL, ADD team_id INT NOT NULL, ADD game_id INT NOT NULL');
}
$lastId = 0;
$missing = 0;
$leagues = $teams = $games = $players = $seasons = [];
while (true) {
$stats = $this->connection->fetchAllAssociative("SELECT idstajournee, idjoueur_stajournee, idsaison_stajournee, idligue_stajournee, idequipe_stajournee, idmatchservice_stajournee FROM stajournee WHERE idstajournee > {$lastId} AND (player_id IS NULL OR player_id = 0) LIMIT 1000");
if (empty($stats)) {
break;
}
$queries = [];
foreach ($stats as $stat) {
$statId = $lastId = $stat['idstajournee'];
$leagueServiceId = $stat['idligue_stajournee'];
$teamServiceId = $stat['idequipe_stajournee'];
$gameServiceId = $stat['idmatchservice_stajournee'];
$playerServiceId = $stat['idjoueur_stajournee'];
$seasonIdUnsure = $stat['idsaison_stajournee'];
$leagueId = $leagues[$leagueServiceId] ?? $this->connection->fetchOne("SELECT idligue FROM ligue WHERE idligueservice_ligue = {$leagueServiceId}");
$teamId = $teams[$teamServiceId] ?? $this->connection->fetchOne("SELECT idequipe FROM equipe WHERE idequipeservice_equipe = {$teamServiceId}");
$gameId = $games[$gameServiceId] ?? $this->connection->fetchOne("SELECT idmatch FROM `match` WHERE idmatchservice_match = {$gameServiceId}");
$playerId = $players[$playerServiceId] ?? $this->connection->fetchOne("SELECT idjoueur FROM joueur WHERE idjoueurservice_joueur = {$playerServiceId}");
$seasonId = $seasons[$seasonIdUnsure] ?? $this->connection->fetchOne("SELECT idsaison FROM saison WHERE idsaison = {$seasonIdUnsure}");
if ($leagueId && $teamId && $gameId && $playerId && $seasonId) {
$queries[] = "UPDATE stajournee SET league_id = {$leagueId}, team_id = {$teamId}, game_id = {$gameId}, player_id = {$playerId} WHERE idstajournee = {$statId}";
$leagues[$leagueServiceId] = $leagueId;
$teams[$teamServiceId] = $teamId;
$games[$gameServiceId] = $gameId;
$players[$playerServiceId] = $playerId;
$seasons[$seasonIdUnsure] = $seasonId;
} else {
$queries[] = "DELETE FROM stajournee WHERE idstajournee = {$statId}";
++$missing;
if (!$leagueId) {
echo "StatDay.id {$statId} : Missing League.serviceId {$leagueServiceId}\n";
} elseif (!$teamId) {
echo "StatDay.id {$statId} : Missing Team.serviceId {$teamServiceId}\n";
} elseif (!$gameId) {
echo "StatDay.id {$statId} : Missing Game.serviceId {$gameServiceId}\n";
} elseif (!$playerId) {
echo "StatDay.id {$statId} : Missing Player.serviceId {$playerServiceId}\n";
} elseif (!$seasonId) {
echo "StatDay.id {$statId} : Missing Season.id {$seasonId}\n";
}
}
}
if (!empty($queries)) {
$this->connection->executeQuery(implode(';', $queries));
}
echo "OK - {$statId}\n";
}
$this->addSql('ALTER TABLE stajournee ADD CONSTRAINT FK_E81218299E6F5DF FOREIGN KEY (player_id) REFERENCES joueur (idjoueur)');
$this->addSql('ALTER TABLE stajournee ADD CONSTRAINT FK_E8121826F4C4C40 FOREIGN KEY (idsaison_stajournee) REFERENCES saison (idsaison)');
$this->addSql('ALTER TABLE stajournee ADD CONSTRAINT FK_E81218258AFC4DE FOREIGN KEY (league_id) REFERENCES ligue (idligue)');
$this->addSql('ALTER TABLE stajournee ADD CONSTRAINT FK_E812182296CD8AE FOREIGN KEY (team_id) REFERENCES equipe (idequipe)');
$this->addSql('ALTER TABLE stajournee ADD CONSTRAINT FK_E812182E48FD905 FOREIGN KEY (game_id) REFERENCES `match` (idmatch)');
$this->addSql('CREATE INDEX IDX_E81218299E6F5DF ON stajournee (player_id)');
$this->addSql('CREATE INDEX IDX_E81218258AFC4DE ON stajournee (league_id)');
$this->addSql('CREATE INDEX IDX_E812182296CD8AE ON stajournee (team_id)');
$this->addSql('CREATE INDEX IDX_E812182E48FD905 ON stajournee (game_id)');
}
public function down(Schema $schema): void
{
// this down() migration is auto-generated, please modify it to your needs
$this->addSql('ALTER TABLE stajournee DROP FOREIGN KEY FK_E81218299E6F5DF');
$this->addSql('ALTER TABLE stajournee DROP FOREIGN KEY FK_E8121826F4C4C40');
$this->addSql('ALTER TABLE stajournee DROP FOREIGN KEY FK_E81218258AFC4DE');
$this->addSql('ALTER TABLE stajournee DROP FOREIGN KEY FK_E812182296CD8AE');
$this->addSql('ALTER TABLE stajournee DROP FOREIGN KEY FK_E812182E48FD905');
$this->addSql('DROP INDEX IDX_E81218299E6F5DF ON stajournee');
$this->addSql('DROP INDEX IDX_E81218258AFC4DE ON stajournee');
$this->addSql('DROP INDEX IDX_E812182296CD8AE ON stajournee');
$this->addSql('DROP INDEX IDX_E812182E48FD905 ON stajournee');
$this->addSql('ALTER TABLE stajournee DROP player_id, DROP league_id, DROP team_id, DROP game_id');
}
}