zend-blog-3-backend

1
<?php
2

3
namespace Application\Migrations;
4

5
use Doctrine\DBAL\Schema\Schema;
6
use Doctrine\Migrations\AbstractMigration;
7
use Symfony\Component\DependencyInjection\ContainerAwareInterface;
8
use Symfony\Component\DependencyInjection\ContainerInterface;
9

10
/**
11
 * Auto-generated Migration: Please modify to your needs!
12
 */
13
class Version20160425235303 extends AbstractMigration implements ContainerAwareInterface
14
{
15
    /**
16
     * @var ContainerInterface
17
     */
18
    protected $container;
19

20
    /**
21
     * @param ContainerInterface|null $container
22
     */
23
    public function setContainer(ContainerInterface $container = null)
24
    {
25
        $this->container = $container;
26
    }
27

28
    /**
29
     * @param Schema $schema
30
     */
31
    public function up(Schema $schema): void
32
    {
33
        // this up() migration is auto-generated, please modify it to your needs
34
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');
35

36
        $this->addSql('ALTER TABLE posts ADD comments_count INT NOT NULL, ADD views_count INT NOT NULL');
37
        $this->addSql('UPDATE posts AS p JOIN posts_counts AS c ON p.id = c.post_id SET p.comments_count = c.comments, p.views_count = c.views');
38
        $this->addSql('DROP TABLE posts_counts');
39
        $this->addSql('DROP PROCEDURE IF EXISTS `update_comments_count`');
40
    }
41

42
    /**
43
     * @param Schema $schema
44
     */
45
    public function down(Schema $schema): void
46
    {
47
        // this down() migration is auto-generated, please modify it to your needs
48
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');
49

50
        $this->addSql('CREATE TABLE posts_counts (id INT AUTO_INCREMENT NOT NULL, post_id INT DEFAULT NULL, comments INT NOT NULL, views INT NOT NULL, UNIQUE INDEX UNIQ_D23531924B89032C (post_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
51
        $this->addSql('ALTER TABLE posts_counts ADD CONSTRAINT FK_D23531924B89032C FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE');
52
        $this->addSql('ALTER TABLE posts DROP comments_count, DROP views_count');
53
    }
54

55
    /**
56
     * @param Schema $schema
57
     */
58
    public function postUp(Schema $schema): void
59
    {
60
        parent::postUp($schema);
61

62
        $sql = file_get_contents(__DIR__ . '/sql/update_comments_count_01.sql');
63

64
        $em = $this->container->get('doctrine.orm.entity_manager');
65
        $stmt = $em->getConnection()->prepare($sql);
66
        $stmt->execute();
67

68
        $this->write('     <comment>-></comment> CREATE PROCEDURE `update_comments_count`');
69
    }
70
}
71