Dump TRIGGER definitions _after_ data (#100)

mpdude · web-flow · commit 481b94af21eb · 2022-12-21T11:44:24.000+01:00
When the new CSV output mode with the concept of "output drivers" was added in #92, dumping `TRIGGER` definitions was made an implementation detail of the MySQL output driver. This caused `TRIGGER` definitions to be dumped right after the `CREATE TABLE ...` commands, before the actual data `INSERT` statements. This potentially breaks the generated SQL files, since a newly created trigger may be relevant for the subsequent `INSERT` statements; however, MySQL requires that tables used in the trigger are also included in the `LOCK TABLES` statements. The aim of this PR is to revert that change, i. e. to dump trigger definitions for a table _after_ the data insert statements for it. I think it is not necessary to move all trigger definitions to the very end of the output – that is, after _all_ tables have been created and filled with data: A trigger depends on insert/update/deletes for a particular table and is executed only on these events. So, it is not a problem if a trigger refers to a table that has not been created/loaded yet as long as the trigger is not run (and avoiding to run it is the aim of this PR).
diff --git a/src/Webfactory/Slimdump/Database/CsvOutputFormatDriver.php b/src/Webfactory/Slimdump/Database/CsvOutputFormatDriver.php
@@ -75,4 +75,8 @@ public function dumpTableRow(array $row, Schema\Table $asset, Table $config): vo
 
         fputcsv($this->outputFile, $row);
     }
+
+    public function dumpTriggerDefinition(Schema\Table $asset, Table $config): void
+    {
+    }
 }
diff --git a/src/Webfactory/Slimdump/Database/Dumper.php b/src/Webfactory/Slimdump/Database/Dumper.php
@@ -77,13 +77,22 @@ private function dumpTable(Schema\Table $asset, Table $config): void
         if ($config->isDataDumpRequired()) {
             $this->dumpData($asset, $config);
         }
+
+        if ($config->isTriggerDumpRequired()) {
+            $this->dumpTriggers($asset, $config);
+        }
     }
 
     private function dumpView(Schema\View $asset, Table $config): void
     {
         $this->outputFormatDriver->dumpViewDefinition($asset, $config);
     }
 
+    private function dumpTriggers(Schema\Table $asset, Table $config): void
+    {
+        $this->outputFormatDriver->dumpTriggerDefinition($asset, $config);
+    }
+
     private function dumpData(Schema\Table $asset, Table $tableConfig): void
     {
         $table = $asset->getName();
diff --git a/src/Webfactory/Slimdump/Database/MysqlOutputFormatDriver.php b/src/Webfactory/Slimdump/Database/MysqlOutputFormatDriver.php
@@ -60,23 +60,19 @@ public function dumpTableStructure(Schema\Table $asset, Table $config): void
         }
 
         $this->output->writeln($tableCreationCommand.";\n", OutputInterface::OUTPUT_RAW);
-
-        if ($config->isTriggerDumpRequired()) {
-            $this->dumpTriggers($tableName, $config->getDumpTriggersLevel());
-        }
     }
 
-    /**
-     * @param int $level One of the Table::TRIGGER_* constants
-     */
-    private function dumpTriggers(string $tableName, int $level = Table::DEFINER_NO_DEFINER): void
+    public function dumpTriggerDefinition(Schema\Table $asset, Table $config): void
     {
+        $tableName = $asset->getName();
+
         $triggers = $this->db->fetchAll(sprintf('SHOW TRIGGERS LIKE %s', $this->db->quote($tableName)));
 
         if (!$triggers) {
             return;
         }
 
+        $level = $config->getDumpTriggersLevel();
         $this->output->writeln("-- BEGIN TRIGGERS $tableName", OutputInterface::OUTPUT_RAW);
 
         $this->output->writeln("DELIMITER ;;\n");
diff --git a/src/Webfactory/Slimdump/Database/OutputFormatDriverInterface.php b/src/Webfactory/Slimdump/Database/OutputFormatDriverInterface.php
@@ -27,6 +27,11 @@ public function dumpTableStructure(Schema\Table $asset, Table $config): void;
      */
     public function dumpViewDefinition(Schema\View $asset, Table $config): void;
 
+    /**
+     * Called to dump trigger definitions.
+     */
+    public function dumpTriggerDefinition(Schema\Table $asset, Table $config): void;
+
     /**
      * Called at the beginning when dumping data for a single table.
      */

Original file line number	Diff line number	Diff line change
`@@ -75,4 +75,8 @@ public function dumpTableRow(array $row, Schema\Table $asset, Table $config): vo`
`75`	`75`
`76`	`76`	`fputcsv($this->outputFile, $row);`
`77`	`77`	`}`
	`78`	`+`
	`79`	`+ public function dumpTriggerDefinition(Schema\Table $asset, Table $config): void`
	`80`	`+ {`
	`81`	`+ }`
`78`	`82`	`}`
Original file line number	Diff line number	Diff line change
`@@ -77,13 +77,22 @@ private function dumpTable(Schema\Table $asset, Table $config): void`
`77`	`77`	`if ($config->isDataDumpRequired()) {`
`78`	`78`	`$this->dumpData($asset, $config);`
`79`	`79`	`}`
	`80`	`+`
	`81`	`+ if ($config->isTriggerDumpRequired()) {`
	`82`	`+ $this->dumpTriggers($asset, $config);`
	`83`	`+ }`
`80`	`84`	`}`
`81`	`85`
`82`	`86`	`private function dumpView(Schema\View $asset, Table $config): void`
`83`	`87`	`{`
`84`	`88`	`$this->outputFormatDriver->dumpViewDefinition($asset, $config);`
`85`	`89`	`}`
`86`	`90`
	`91`	`+ private function dumpTriggers(Schema\Table $asset, Table $config): void`
	`92`	`+ {`
	`93`	`+ $this->outputFormatDriver->dumpTriggerDefinition($asset, $config);`
	`94`	`+ }`
	`95`	`+`
`87`	`96`	`private function dumpData(Schema\Table $asset, Table $tableConfig): void`
`88`	`97`	`{`
`89`	`98`	`$table = $asset->getName();`
Original file line number	Diff line number	Diff line change
`@@ -60,23 +60,19 @@ public function dumpTableStructure(Schema\Table $asset, Table $config): void`
`60`	`60`	`}`
`61`	`61`
`62`	`62`	`$this->output->writeln($tableCreationCommand.";\n", OutputInterface::OUTPUT_RAW);`
`63`		`-`
`64`		`- if ($config->isTriggerDumpRequired()) {`
`65`		`- $this->dumpTriggers($tableName, $config->getDumpTriggersLevel());`
`66`		`- }`
`67`	`63`	`}`
`68`	`64`
`69`		`- /**`
`70`		`- * @param int $level One of the Table::TRIGGER_* constants`
`71`		`- */`
`72`		`- private function dumpTriggers(string $tableName, int $level = Table::DEFINER_NO_DEFINER): void`
	`65`	`+ public function dumpTriggerDefinition(Schema\Table $asset, Table $config): void`
`73`	`66`	`{`
	`67`	`+ $tableName = $asset->getName();`
	`68`	`+`
`74`	`69`	`$triggers = $this->db->fetchAll(sprintf('SHOW TRIGGERS LIKE %s', $this->db->quote($tableName)));`
`75`	`70`
`76`	`71`	`if (!$triggers) {`
`77`	`72`	`return;`
`78`	`73`	`}`
`79`	`74`
	`75`	`+ $level = $config->getDumpTriggersLevel();`
`80`	`76`	`$this->output->writeln("-- BEGIN TRIGGERS $tableName", OutputInterface::OUTPUT_RAW);`
`81`	`77`
`82`	`78`	`$this->output->writeln("DELIMITER ;;\n");`