Symfony Profiler

<?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 Version20181231180122 extends AbstractMigration
{
public function up(Schema $schema): void
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf('mysql' !== $this->connection->getDatabasePlatform()->getName(), 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('
CREATE TABLE chat_participant (
id INT AUTO_INCREMENT NOT NULL,
chat_id INT DEFAULT NULL,
user_id INT DEFAULT NULL,
open TINYINT(1) NOT NULL,
unread INT DEFAULT NULL,
INDEX IDX_E8ED9C891A9A7125 (chat_id),
INDEX IDX_E8ED9C89A76ED395 (user_id),
PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB
');
$stmt = $this->connection->prepare('SELECT id, participants FROM chats');
$stmt->execute();
$groups = $this->getUsersByGroup();
while ($row = $stmt->fetch(\PDO::FETCH_OBJ)) {
$participants = $row->participants ? unserialize($row->participants) : [];
if ($participants && \count($participants)) {
$users = [];
foreach ($participants as $key => $value) {
if (\is_string($key) && 'g' === substr($key, 0, 1)) {
if (isset($groups[substr($key, 1)])) {
foreach ($groups[substr($key, 1)] as $userId) {
if (!\in_array($userId, $users)) {
$users[$userId] = [
'open' => true,
'unread' => 1,
];
}
}
}
} else {
$users[$key] = $value;
}
}
foreach ($users as $key => $value) {
$this->addSql('INSERT INTO chat_participant (chat_id, user_id, open, unread) VALUES (:chat_id, :user_id, :open, :unread)', [
'chat_id' => $row->id,
'user_id' => $key,
'open' => (int) ($value['open'] ?? true),
'unread' => (int) ($value['unread'] ?? 0),
]);
}
}
}
$this->addSql('RENAME TABLE chats TO chat');
$this->addSql('ALTER TABLE chat ENGINE = InnoDB');
$this->addSql('ALTER TABLE chat CHARACTER SET = utf8mb4');
$this->addSql('
ALTER TABLE chat
DROP participants,
ADD user_id INT DEFAULT NULL,
CHANGE date_added created_at DATETIME NOT NULL,
CHANGE last_modified modified_at DATETIME NOT NULL
');
$this->addSql('CREATE INDEX IDX_659DF2AAA76ED395 ON chat (user_id)');
// get chat creator (user of the first message sent)
$stmt = $this->connection->prepare('SELECT chats_id, administrators_id FROM chats_messages GROUP BY chats_id ORDER BY date_added');
$stmt->execute();
while ($row = $stmt->fetch(\PDO::FETCH_OBJ)) {
$this->addSql('UPDATE chat SET user_id = :user_id WHERE id = :id', [
'id' => $row->chats_id,
'user_id' => $row->administrators_id,
]);
}
$this->addSql('RENAME TABLE chats_messages TO chat_message');
$this->addSql('ALTER TABLE chat_message ENGINE = InnoDB');
$this->addSql('ALTER TABLE chat_message CHARACTER SET = utf8mb4');
$this->addSql('DELETE FROM chat_message WHERE message IS NULL OR message = \'\'');
$this->addSql('
ALTER TABLE chat_message
CHANGE chats_id chat_id INT DEFAULT NULL,
CHANGE administrators_id user_id INT DEFAULT NULL,
CHANGE message body LONGTEXT NOT NULL,
CHANGE file file VARCHAR(255) DEFAULT NULL,
CHANGE date_added created_at DATETIME NOT NULL
');
$this->addSql('DROP INDEX idx_chats_id ON chat_message');
$this->addSql('DROP INDEX idx_administrators_id ON chat_message');
$this->addSql('CREATE INDEX IDX_FAB3FC161A9A7125 ON chat_message (chat_id)');
$this->addSql('CREATE INDEX IDX_FAB3FC16A76ED395 ON chat_message (user_id)');
$this->addSql('DELETE FROM chat WHERE user_id IS NULL');
$this->addSql('UPDATE chat SET user_id = NULL WHERE user_id NOT IN (SELECT id FROM user)');
$this->addSql('DELETE FROM chat_message WHERE chat_id NOT IN (SELECT id FROM chat)');
$this->addSql('UPDATE chat_message SET user_id = NULL WHERE user_id NOT IN (SELECT id FROM user)');
$this->addSql('DELETE FROM chat_participant WHERE chat_id NOT IN (SELECT id FROM chat)');
$this->addSql('DELETE FROM chat_participant WHERE user_id NOT IN (SELECT id FROM user)');
$this->addSql('ALTER TABLE chat ADD CONSTRAINT FK_659DF2AAA76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE SET NULL');
$this->addSql('ALTER TABLE chat_message ADD CONSTRAINT FK_FAB3FC161A9A7125 FOREIGN KEY (chat_id) REFERENCES chat (id) ON DELETE CASCADE');
$this->addSql('ALTER TABLE chat_message ADD CONSTRAINT FK_FAB3FC16A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE SET NULL');
$this->addSql('ALTER TABLE chat_participant ADD CONSTRAINT FK_E8ED9C891A9A7125 FOREIGN KEY (chat_id) REFERENCES chat (id) ON DELETE CASCADE');
$this->addSql('ALTER TABLE chat_participant ADD CONSTRAINT FK_E8ED9C89A76ED395 FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE');
}
public function down(Schema $schema): void
{
// this down() migration is auto-generated, please modify it to your needs
$this->abortIf('mysql' !== $this->connection->getDatabasePlatform()->getName(), 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('ALTER TABLE chat_participant DROP FOREIGN KEY FK_E8ED9C89A76ED395');
$this->addSql('ALTER TABLE chat_participant DROP FOREIGN KEY FK_E8ED9C891A9A7125');
$this->addSql('ALTER TABLE chat_message DROP FOREIGN KEY FK_FAB3FC16A76ED395');
$this->addSql('ALTER TABLE chat_message DROP FOREIGN KEY FK_FAB3FC161A9A7125');
$this->addSql('ALTER TABLE chat DROP FOREIGN KEY FK_659DF2AAA76ED395');
$this->addSql('RENAME TABLE chat TO chats');
$this->addSql('ALTER TABLE chats ENGINE = MyISAM');
$this->addSql('ALTER TABLE chats CHARACTER SET = latin1');
$this->addSql('
ALTER TABLE chats
DROP user_id,
ADD participants LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\' AFTER id,
CHANGE created_at date_added DATETIME DEFAULT NULL,
CHANGE modified_at last_modified DATETIME DEFAULT NULL
');
$stmt = $this->connection->prepare('SELECT * FROM chat_participant');
$stmt->execute();
$chats = [];
while ($row = $stmt->fetch(\PDO::FETCH_OBJ)) {
$chats[$row->chat_id][] = [
$row->user_id = [
'open' => $row->open,
'unread' => $row->unread,
],
];
}
foreach ($chats as $key => $value) {
$participants = serialize($value);
$this->addSql('UPDATE chats SET participants = :participants WHERE id = :id', [
'participants' => $participants,
'id' => $key,
]);
}
$this->addSql('DROP TABLE chat_participant');
$this->addSql('RENAME TABLE chat_message TO chats_messages');
$this->addSql('ALTER TABLE chats_messages ENGINE = InnoDB');
$this->addSql('ALTER TABLE chats_messages CHARACTER SET = utf8mb4');
$this->addSql('
ALTER TABLE chats_messages
CHANGE chat_id chats_id INT NOT NULL,
CHANGE user_id administrators_id INT NOT NULL,
CHANGE body message LONGTEXT DEFAULT NULL,
CHANGE file file VARCHAR(255) DEFAULT NULL,
CHANGE created_at date_added DATETIME DEFAULT NULL
');
$this->addSql('DROP INDEX IDX_FAB3FC161A9A7125 ON chats_messages');
$this->addSql('DROP INDEX IDX_FAB3FC16A76ED395 ON chats_messages');
$this->addSql('CREATE INDEX idx_chats_id ON chats_messages (chats_id)');
$this->addSql('CREATE INDEX idx_administrators_id ON chats_messages (administrators_id)');
}
/**
* @return array
*/
private function getUsersByGroup(): array
{
$groups = [];
$stmt = $this->connection->prepare('SELECT u.id, ujg.group_id FROM user u LEFT JOIN user_join_group ujg ON (u.id = ujg.user_id) WHERE u.enabled = 1');
$stmt->execute();
while ($row = $stmt->fetch(\PDO::FETCH_OBJ)) {
$groups[$row->group_id][] = $row->id;
}
return $groups;
}
}

migrations/Version20181231180122.php line 1