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 Version20150620132229 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('DROP TABLE avatar');
37
        $this->addSql('DROP PROCEDURE IF EXISTS `update_comments_count`');
38
    }
39

40
    public function postUp(Schema $schema): void
41
    {
42
        parent::postUp($schema);
43

44
        $sql = '
45
        CREATE PROCEDURE `update_comments_count`(IN topicID INT UNSIGNED)
46
        BEGIN
47
            DECLARE count_comments INT DEFAULT 0;
48

49
            SELECT COUNT( id ) INTO count_comments
50
                FROM `comments`
51
                WHERE (`post_id` = topicID) AND (`deleted` = 0);
52

53
            UPDATE `posts_counts`
54
                SET `comments` = count_comments
55
                WHERE `posts_counts`.`post_id` = topicID;
56

57
            SELECT count_comments;
58
        END';
59

60
        $em = $this->container->get('doctrine.orm.entity_manager');
61
        $stmt = $em->getConnection()->prepare($sql);
62
        $stmt->execute();
63

64
        $this->write('     <comment>-></comment> CREATE PROCEDURE `update_comments_count`');
65
    }
66

67
    /**
68
     * @param Schema $schema
69
     */
70
    public function down(Schema $schema): void
71
    {
72
        // this down() migration is auto-generated, please modify it to your needs
73
        $this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');
74

75
        $this->addSql('DROP PROCEDURE IF EXISTS `update_comments_count`');
76
        $this->addSql('CREATE TABLE avatar (id INT AUTO_INCREMENT NOT NULL, hash VARCHAR(32) NOT NULL, `default` SMALLINT NOT NULL, src VARCHAR(48) NOT NULL, last_modified DATETIME NOT NULL, UNIQUE INDEX UNIQ_1677722FD1B862B8 (hash), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
77
    }
78
}
79