Application\Migrations\Version20170402155913

Complexity

Total Complexity

3

Size/Duplication

Total Lines	147
Duplicated Lines	0 %

Coupling/Cohesion

Components	1
Dependencies	3

Importance

Changes

0

2 Methods

Rating	Name	Duplication	Size	Complexity
B	up()	0	119	2
A	down()	0	16	1

<?php

namespace Application\Migrations;

use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;

/**
 * Auto-generated Migration: Please modify to your needs!
 */
class Version20170402155913 extends AbstractMigration
{
    /**
     * @param Schema $schema
     *
     * @throws
     */
    public function up(Schema $schema)
    {
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        $this->connection->beginTransaction();
        try{
            $this->addSql('
                SET @venue_philharmonic = 1;
                
                CREATE TEMPORARY TABLE `temp_venue_sector` LIKE `venue_sector`;
                
                INSERT INTO `temp_venue_sector` (`venue_id`, `title`, `slug`) VALUES
                (@venue_philharmonic, "Партер", "parterre"),
    	        (@venue_philharmonic, "Балкон", "balcony"),
    	        (@venue_philharmonic, "Лоджия Левая", "loggia-left"),
    	        (@venue_philharmonic, "Лоджия Правая", "loggia-right");
    	        
                INSERT INTO `venue_sector` (`venue_id`, `title`, `slug`)
                SELECT  `tvs`.`venue_id`, `tvs`.`title`, `tvs`.`slug`  FROM `temp_venue_sector` `tvs`
                LEFT JOIN `venue_sector` `vs` ON (`vs`.`venue_id` = `tvs`.`venue_id` AND `vs`.`slug` = `tvs`.`slug`) 
                WHERE (`vs`.`id` IS NULL AND `vs`.`slug` IS NULL);	        
            ');

            $this->addSql('
                CREATE TEMPORARY TABLE `autogenerated_values` (
                SELECT (`T_1`.`gv` + `T_2`.`gv` + `T_4`.`gv` + `T_8`.`gv` + `T_16`.`gv` + `T_32`.`gv`) `gv`
                FROM
                (SELECT 0 `gv` UNION ALL SELECT 1 `gv`) `T_1`
                    CROSS JOIN (SELECT 0 `gv` UNION ALL SELECT 2 `gv`) `T_2`
                    CROSS JOIN (SELECT 0 `gv` UNION ALL SELECT 4 `gv`) `T_4`
                    CROSS JOIN (SELECT 0 `gv` UNION ALL SELECT 8 `gv`) `T_8`
                    CROSS JOIN (SELECT 0 `gv` UNION ALL SELECT 16 `gv`) `T_16`
                    CROSS JOIN (SELECT 0 `gv` UNION ALL SELECT 32 `gv`) `T_32`
                LIMIT 1, 60);
            ');

            $this->addSql('
                SELECT @vs_id:=`vs`.`id` FROM `venue_sector` `vs` 
                WHERE `vs`.`venue_id` = @venue_philharmonic AND `vs`.`slug` = "parterre";          
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 1, `gv`, @vs_id FROM `autogenerated_values` LIMIT 26);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 2, `gv`, @vs_id FROM `autogenerated_values` LIMIT 27);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 3, `gv`, @vs_id FROM `autogenerated_values` LIMIT 28);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 4, `gv`, @vs_id FROM `autogenerated_values` LIMIT 29);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 5, `gv`, @vs_id FROM `autogenerated_values` LIMIT 28);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 6, gv, @vs_id FROM `autogenerated_values` LIMIT 29);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 7, `gv`, @vs_id FROM `autogenerated_values` LIMIT 28);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 8, `gv`, @vs_id FROM `autogenerated_values` LIMIT 29);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 9, `gv`, @vs_id FROM `autogenerated_values` LIMIT 28);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 10, `gv`, @vs_id FROM `autogenerated_values` LIMIT 29);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 11, `gv`, @vs_id FROM `autogenerated_values` LIMIT 28);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 12, `gv`, @vs_id FROM `autogenerated_values` LIMIT 29);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 13, gv, @vs_id FROM `autogenerated_values` LIMIT 28);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 14, gv, @vs_id FROM `autogenerated_values` LIMIT 29);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 15, `gv`, @vs_id FROM `autogenerated_values` LIMIT 28);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 16, `gv`, @vs_id FROM `autogenerated_values` LIMIT 27);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 17, `gv`, @vs_id FROM `autogenerated_values` LIMIT 28);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 18, `gv`, @vs_id FROM `autogenerated_values` LIMIT 25);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 19, `gv`, @vs_id FROM `autogenerated_values` LIMIT 24);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 20, `gv`, @vs_id FROM `autogenerated_values` LIMIT 25);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 21, `gv`, @vs_id FROM `autogenerated_values` LIMIT 24);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 22, `gv`, @vs_id FROM `autogenerated_values` LIMIT 25);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 23, `gv`, @vs_id FROM `autogenerated_values` LIMIT 22);

                SELECT @vs_id:=`vs`.`id` FROM `venue_sector` `vs` 
                WHERE `vs`.`venue_id` = @venue_philharmonic AND `vs`.`slug` = "balcony";          
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 1, `gv`, @vs_id FROM `autogenerated_values` LIMIT 30);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 2, `gv`, @vs_id FROM `autogenerated_values` LIMIT 30);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 3, `gv`, @vs_id FROM `autogenerated_values` LIMIT 30);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 4, `gv`, @vs_id FROM `autogenerated_values` LIMIT 30);
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 5, `gv`, @vs_id FROM `autogenerated_values` LIMIT 30);

                SELECT @vs_id:=`vs`.`id` FROM `venue_sector` `vs` 
                WHERE `vs`.`venue_id` = @venue_philharmonic AND `vs`.`slug` = "loggia-left";          
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 1, `gv`, @vs_id FROM `autogenerated_values` LIMIT 10);

                SELECT @vs_id:=`vs`.`id` FROM `venue_sector` `vs` 
                WHERE `vs`.`venue_id` = @venue_philharmonic AND `vs`.`slug` = "loggia-right";          
                INSERT INTO `seat` (`row`, `place`, `venueSector_id`) 
                (SELECT 1, `gv`, @vs_id FROM `autogenerated_values` LIMIT 10);
            ');

            $this->addSql('DROP TABLE IF EXISTS `autogenerated_values`');
            $this->addSql('DROP TABLE IF EXISTS `temp_venue_sector`');

            $this->connection->commit();
        } catch (\Exception $e) {
            $this->connection->rollBack();
            throw $e;
        }
    }

    /**
     * @param Schema $schema
     */
    public function down(Schema $schema)
    {
        $this->addSql('
            SET @venue_philharmonic = 1;
            
            DELETE FROM `seat` WHERE `venueSector_id` IN (
                SELECT `vs`.`id` FROM `venue_sector` `vs` WHERE `vs`.`venue_id` = @venue_philharmonic 
                AND `vs`.`slug` IN ("parterre", "balcony", "loggia-right", "loggia-left")
            );  
            
            DELETE FROM `venue_sector` WHERE `id` IN (  
                SELECT `vs`.`id` FROM `venue_sector` `vs` WHERE `vs`.`venue_id` = @venue_philharmonic 
                AND `vs`.`slug` IN ("parterre", "balcony", "loggia-right", "loggia-left")
            );
        ');
    }
}