migrations/Version20230411050246.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 Version20230411050246 extends AbstractMigration
  14. {
  15.     public function getDescription(): string
  16.     {
  17.         return '';
  18.     }
  20.     public function up(Schema $schema): void
  21.     {
  22.         //CUSTOMER
  23.         $this->addSql("CREATE PROCEDURE ProcessCustomers()
  24.                BEGIN
  25.                    DECLARE done INT DEFAULT 0;
  26.                    DECLARE customerId INT;
  27.                    DECLARE line1 VARCHAR(255);
  28.                    DECLARE city VARCHAR(255);
  29.                    DECLARE zip VARCHAR(255);
  30.                    DECLARE country VARCHAR(255);
  31.                    DECLARE details VARCHAR(255);
  32.                    DECLARE newUuid CHAR(36);
  33.                    DECLARE newAddressId INT;
  35.                    DECLARE cur CURSOR FOR
  36.                        SELECT
  37.                            customer.id,
  38.                            CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(address_details, '$.address')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(address_details, '$.address')) END as line1,
  39.                            CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(address_details, '$.city')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(address_details, '$.city')) END as city,
  40.                            CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(address_details, '$.zip')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(address_details, '$.zip')) END as zip,
  41.                            COALESCE(JSON_UNQUOTE(JSON_EXTRACT(address_details, '$.country')), 'Unknown') as country,
  42.                            CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(address_details, '$.district')) = 'null' THEN NULL ELSE JSON_UNQUOTE(JSON_EXTRACT(address_details, '$.district')) END as details,
  43.                            UUID() AS new_uuid
  44.                        FROM customer
  45.                        WHERE JSON_LENGTH(address_details) > 0;
  47.                    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  49.                    OPEN cur;
  51.                    read_loop: LOOP
  52.                        FETCH cur INTO customerId, line1, city, zip, country, details, newUuid;
  54.                        IF done THEN
  55.                            LEAVE read_loop;
  56.                        END IF;
  58.                        INSERT INTO address (line1, city, zip, country, details, uuid)
  59.                        VALUES (
  60.                            NULLIF(line1, ''),
  61.                            NULLIF(city, ''),
  62.                            NULLIF(zip, ''),
  63.                            NULLIF(country, ''),
  64.                            NULLIF(details, ''),
  65.                            newUuid
  66.                        );
  68.                        SET newAddressId = LAST_INSERT_ID();
  70.                        UPDATE customer
  71.                        SET address_id = newAddressId
  72.                        WHERE id = customerId;
  73.                    END LOOP;
  75.                    CLOSE cur;
  76.                END;");
  77.         $this->addSql("CALL ProcessCustomers();");
  78.         $this->addSql("DROP PROCEDURE ProcessCustomers");
  79.     }
  81.     public function down(Schema $schema): void
  82.     {
  83.         // this down() migration is auto-generated, please modify it to your needs
  84.     }
  85. }