migrations/Version20220816202727.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 Version20220816202727 extends AbstractMigration
  14.     {
  15.         public function getDescription(): string
  16.         {
  17.             return 'View for account balance';
  18.         }
  20.         public function up(Schema $schema): void
  21.         {
  22.             $this->addSql(
  23.                 'CREATE VIEW account_balance AS
  24. SELECT account_id, SUM(amount) as sum
  25. FROM account_changes ac
  26. GROUP BY account_id'
  27.             );
  29.             $this->addSql(
  30.                 'CREATE TRIGGER calculate_balance_on_insert
  31.     AFTER INSERT
  32.     ON account_changes
  33.     FOR EACH ROW
  34. BEGIN
  35.     UPDATE accounts
  36.         LEFT JOIN account_balance ON accounts.id = account_balance.account_id
  37.     SET accounts.balance = IFNULL(account_balance.sum, 0)
  38.     WHERE accounts.id = NEW.account_id;
  39. END;'
  40.             );
  41.             $this->addSql(
  42.                 'CREATE TRIGGER calculate_balance_on_update
  43.     AFTER UPDATE
  44.     ON account_changes
  45.     FOR EACH ROW
  46. BEGIN
  47.     UPDATE accounts
  48.         LEFT JOIN account_balance ON accounts.id = account_balance.account_id
  49.     SET accounts.balance = IFNULL(account_balance.sum, 0)
  50.     WHERE accounts.id IN (NEW.account_id, OLD.account_id);
  51. END;'
  52.             );
  54.             $this->addSql(
  55.                 'CREATE TRIGGER calculate_balance_on_delete
  56.     AFTER DELETE
  57.     ON account_changes
  58.     FOR EACH ROW
  59. BEGIN
  60.     UPDATE accounts
  61.         LEFT JOIN account_balance ON accounts.id = account_balance.account_id
  62.     SET accounts.balance = IFNULL(account_balance.sum, 0)
  63.     WHERE accounts.id = OLD.account_id;
  64. END;'
  65.             );
  66.         }
  68.         public function down(Schema $schema): void
  69.         {
  70.             $this->addSql('DROP VIEW account_balance');
  71.             $this->addSql('DROP TRIGGER calculate_balance_on_insert');
  72.             $this->addSql('DROP TRIGGER calculate_balance_on_update');
  73.             $this->addSql('DROP TRIGGER calculate_balance_on_delete');
  74.         }
  75.     }