Export stored procedures and function for easy versioning

index.php

<?php declare(strict_types=1);
define('NO_SESSION', true);
define('NO_AUTH', true);
require "../inc/config.inc.php";

function show_routine(string $type, string $name) {
  global $db;
  $query = "SHOW CREATE $type `$name`";
  $result = $db->query( $query );
  foreach( $result as $row ) {
    echo "$name\n";
    if( is_null($row["Create $type"]) ) {
      echo "<p>Not found</p>";
      continue;
    }
    $proc   = $row[$type];
    $source = $row["Create $type"];
    $source = preg_replace( "/(DEFINER=`\w*`@`\w*`)/", "/* $1 */", $source );
    $fh = fopen( "./$type/$name.sql", "w" );
    fwrite( $fh, "DROP $type IF EXISTS `$name`;" . PHP_EOL . PHP_EOL );
    fwrite( $fh, "DELIMITER $$" . PHP_EOL );
    fwrite( $fh, $source );
    fwrite( $fh, "$$" . PHP_EOL );
    fwrite( $fh, "DELIMITER ;" . PHP_EOL );
    fclose( $fh );
  }
}


foreach( $db->query("show procedure status where db = 'minidwh'") as $row ) {
  $name = $row["Name"];
  show_routine("Procedure", $name);
}


foreach( $db->query("show function status where db = 'minidwh'") as $row ) {
  $name = $row["Name"];
  show_routine("Function", $name);
}