migrations/Version20230413121653.php line 1

Open in your IDE?
  1. <?php
  3. declare(strict_types=1);
  5. namespace DoctrineMigrations;
  7. use Doctrine\DBAL\Schema\Schema;
  8. use Doctrine\Migrations\AbstractMigration;
  10. /**
  11.  * Auto-generated Migration: Please modify to your needs!
  12.  */
  13. final class Version20230413121653 extends AbstractMigration
  14. {
  15.     public function getDescription(): string
  16.     {
  17.         return '';
  18.     }
  22.     public function up(Schema $schema): void
  23.     {
  24.         //SAMPLE
  25.         $this->addSql("CREATE PROCEDURE ProcessSampleInbounds()
  26.                BEGIN
  27.                    DECLARE done INT DEFAULT 0;
  28.                    DECLARE sampleId INT;
  29.                    DECLARE line1 VARCHAR(255);
  30.                    DECLARE city VARCHAR(255);
  31.                    DECLARE zip VARCHAR(255);
  32.                    DECLARE country VARCHAR(255);
  33.                    DECLARE details VARCHAR(255);
  34.                    DECLARE inboundType VARCHAR(255);
  35.                    DECLARE newUuid CHAR(36);
  36.                    DECLARE newAddressId INT;
  38.                    DECLARE cur CURSOR FOR
  39.                        SELECT
  40.                            sample.id,
  41.                            CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(inbound_address_old, '$.address')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(inbound_address_old, '$.address')) END as line1,
  42.                            CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(inbound_address_old, '$.city')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(inbound_address_old, '$.city')) END as city,
  43.                            CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(inbound_address_old, '$.zip')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(inbound_address_old, '$.zip')) END as zip,
  44.                            COALESCE(JSON_UNQUOTE(JSON_EXTRACT(inbound_address_old, '$.country')), 'Unknown') as country,
  45.                            CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(inbound_address_old, '$.district')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(inbound_address_old, '$.district')) END as details,
  46.                            'address_type.legal' as inboundType,
  47.                            UUID() AS new_uuid
  48.                        FROM sample
  49.                        WHERE JSON_LENGTH(inbound_address_old) > 0;
  51.                    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  53.                    OPEN cur;
  55.                    read_loop: LOOP
  56.                        FETCH cur INTO sampleId, line1, city, zip, country, details, inboundType, newUuid;
  58.                        IF done THEN
  59.                            LEAVE read_loop;
  60.                        END IF;
  62.                        INSERT INTO address (line1, city, zip, country, details, type, uuid)
  63.                        VALUES (
  64.                            NULLIF(line1, ''),
  65.                            NULLIF(city, ''),
  66.                            NULLIF(zip, ''),
  67.                            NULLIF(country, ''),
  68.                            NULLIF(details, ''),
  69.                            NULLIF(inboundType, ''),
  70.                            newUuid
  71.                        );
  73.                        SET newAddressId = LAST_INSERT_ID();
  75.                        UPDATE sample
  76.                        SET inbound_address_id = newAddressId
  77.                        WHERE id = sampleId;
  78.                    END LOOP;
  80.                    CLOSE cur;
  81.                END;");
  82.         $this->addSql("CALL ProcessSampleInbounds();");
  83.         $this->addSql("DROP PROCEDURE ProcessSampleInbounds");
  86.         $this->addSql("CREATE PROCEDURE ProcessSampleOutbounds()
  87.                BEGIN
  88.                    DECLARE done INT DEFAULT 0;
  89.                    DECLARE sampleId INT;
  90.                    DECLARE line1 VARCHAR(255);
  91.                    DECLARE city VARCHAR(255);
  92.                    DECLARE zip VARCHAR(255);
  93.                    DECLARE country VARCHAR(255);
  94.                    DECLARE details VARCHAR(255);
  95.                    DECLARE outboundType VARCHAR(255);
  96.                    DECLARE newUuid CHAR(36);
  97.                    DECLARE newAddressId INT;
  99.                    DECLARE cur CURSOR FOR
  100.                        SELECT
  101.                            sample.id,
  102.                            CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(outbound_address_old, '$.address')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(outbound_address_old, '$.address')) END as line1,
  103.                            CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(outbound_address_old, '$.city')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(outbound_address_old, '$.city')) END as city,
  104.                            CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(outbound_address_old, '$.zip')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(outbound_address_old, '$.zip')) END as zip,
  105.                            COALESCE(JSON_UNQUOTE(JSON_EXTRACT(outbound_address_old, '$.country')), 'Unknown') as country,
  106.                            CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(outbound_address_old, '$.district')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(outbound_address_old, '$.district')) END as details,
  107.                            'address_type.delivery' as outboundType,
  108.                            UUID() AS new_uuid
  109.                        FROM sample                       
  110.                        WHERE JSON_LENGTH(outbound_address_old) > 0;
  112.                    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  114.                    OPEN cur;
  116.                    read_loop: LOOP
  117.                        FETCH cur INTO sampleId, line1, city, zip, country, details, outboundType, newUuid;
  119.                        IF done THEN
  120.                            LEAVE read_loop;
  121.                        END IF;
  123.                        INSERT INTO address (line1, city, zip, country, details, type, uuid)
  124.                        VALUES (
  125.                            NULLIF(line1, ''),
  126.                            NULLIF(city, ''),
  127.                            NULLIF(zip, ''),
  128.                            NULLIF(country, ''),
  129.                            NULLIF(details, ''),
  130.                            NULLIF(outboundType, ''),
  131.                            newUuid
  132.                        );
  134.                        SET newAddressId = LAST_INSERT_ID();
  136.                        UPDATE sample
  137.                        SET outbound_address_id = newAddressId
  138.                        WHERE id = sampleId;
  139.                    END LOOP;
  141.                    CLOSE cur;
  142.                END;");
  143.         $this->addSql("CALL ProcessSampleOutbounds();");
  144.         $this->addSql("DROP PROCEDURE ProcessSampleOutbounds");
  145.     }
  147.     public function down(Schema $schema): void
  148.     {
  149.         // this down() migration is auto-generated, please modify it to your needs
  150.     }
  151. }