Symfony Profiler

<?php
declare(strict_types=1);
namespace DoctrineMigrations;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
/**
* Composite primary key (creator_id, device_id) for device.
* The same device_id can belong to different creators; data is preserved.
*/
final class Version20250731132801 extends AbstractMigration
{
private const DEVICE_TABLE = 'device';
private const DEVICE_CREATOR_FK = 'FK_92FB68E61220EA6';
public function getDescription(): string
{
return 'Device: composite primary key (creator_id, device_id), preserve existing data';
}
public function up(Schema $schema): void
{
$this->addSql('DELETE FROM device WHERE creator_id IS NULL');
if ($this->foreignKeyExists(self::DEVICE_TABLE, self::DEVICE_CREATOR_FK)) {
$this->addSql('ALTER TABLE device DROP FOREIGN KEY FK_92FB68E61220EA6');
}
if (!$this->columnExists(self::DEVICE_TABLE, 'device_id')) {
$this->addSql('ALTER TABLE device ADD device_id BINARY(16) DEFAULT NULL COMMENT \'(DC2Type:uuid)\'');
}
if ($this->columnExists(self::DEVICE_TABLE, 'id')) {
$this->addSql('UPDATE device SET device_id = id WHERE device_id IS NULL');
}
$this->addSql('ALTER TABLE device MODIFY device_id BINARY(16) NOT NULL COMMENT \'(DC2Type:uuid)\'');
$this->addSql('ALTER TABLE device MODIFY creator_id BIGINT UNSIGNED NOT NULL');
if ($this->columnExists(self::DEVICE_TABLE, 'id')) {
$this->addSql('ALTER TABLE device DROP PRIMARY KEY');
$this->addSql('ALTER TABLE device DROP COLUMN id');
}
if (!$this->primaryKeyExists(self::DEVICE_TABLE, ['creator_id', 'device_id'])) {
$this->addSql('ALTER TABLE device ADD PRIMARY KEY (creator_id, device_id)');
}
if (!$this->foreignKeyExists(self::DEVICE_TABLE, self::DEVICE_CREATOR_FK)) {
$this->addSql('ALTER TABLE device ADD CONSTRAINT FK_92FB68E61220EA6 FOREIGN KEY (creator_id) REFERENCES creator (id) ON DELETE CASCADE');
}
}
public function down(Schema $schema): void
{
if ($this->foreignKeyExists(self::DEVICE_TABLE, self::DEVICE_CREATOR_FK)) {
$this->addSql('ALTER TABLE device DROP FOREIGN KEY FK_92FB68E61220EA6');
}
$this->addSql('ALTER TABLE device DROP PRIMARY KEY');
$this->addSql('ALTER TABLE device ADD id BINARY(16) DEFAULT NULL COMMENT \'(DC2Type:uuid)\'');
$this->addSql('UPDATE device SET id = device_id');
$this->addSql('ALTER TABLE device MODIFY id BINARY(16) NOT NULL COMMENT \'(DC2Type:uuid)\'');
$this->addSql('ALTER TABLE device ADD PRIMARY KEY (id)');
$this->addSql('ALTER TABLE device DROP COLUMN device_id');
$this->addSql('ALTER TABLE device MODIFY creator_id BIGINT UNSIGNED DEFAULT NULL');
$this->addSql('ALTER TABLE device ADD CONSTRAINT FK_92FB68E61220EA6 FOREIGN KEY (creator_id) REFERENCES creator (id) ON DELETE CASCADE');
}
public function isTransactional(): bool
{
return false;
}
private function foreignKeyExists(string $tableName, string $constraintName): bool
{
$sql = <<<'SQL'
SELECT COUNT(*)
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
AND TABLE_NAME = :tableName
AND CONSTRAINT_NAME = :constraintName
AND CONSTRAINT_TYPE = 'FOREIGN KEY'
SQL;
return (int) $this->connection->fetchOne($sql, [
'tableName' => $tableName,
'constraintName' => $constraintName,
]) > 0;
}
private function columnExists(string $tableName, string $columnName): bool
{
$sql = <<<'SQL'
SELECT COUNT(*)
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = :tableName
AND COLUMN_NAME = :columnName
SQL;
return (int) $this->connection->fetchOne($sql, [
'tableName' => $tableName,
'columnName' => $columnName,
]) > 0;
}
/**
* @param list<string> $expectedColumns
*/
private function primaryKeyExists(string $tableName, array $expectedColumns): bool
{
$sql = <<<'SQL'
SELECT COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = :tableName
AND CONSTRAINT_NAME = 'PRIMARY'
ORDER BY ORDINAL_POSITION
SQL;
$actualColumns = $this->connection->fetchFirstColumn($sql, [
'tableName' => $tableName,
]);
return $actualColumns === $expectedColumns;
}
}

migrations/Version20250731132801.php line 1