SetBased\Stratum\MySql\Helper\RoutineLoaderHelper

Complexity

Total Complexity

135

Size/Duplication

Total Lines	1006
Duplicated Lines	0 %

Test Coverage

Coverage

8.01%

Importance

Changes	2
Bugs	0	Features	0

23 Methods

Rating	Name	Duplication	Size	Complexity
B	validateReturnType()	0	25	8
A	__construct()	0	19	1
B	extractExtendedParametersInfo()	0	26	8
A	loadStoredRoutine()	0	28	2
A	updateMetadata()	0	14	1
A	updateParametersInfo()	0	19	6
D	extractColumnsFromTableDescription()	0	107	30
B	extractReturnType()	0	25	7
B	mustLoadStoredRoutine()	0	31	10
A	extractRoutineTypeAndName()	0	13	3
A	extractRoutineParametersInfo()	0	24	5
B	extractSyntax()	0	30	8
A	logUnknownPlaceholders()	0	19	3
A	extractDocBlockPartsWrapper()	0	20	3
C	extractDesignationType()	0	38	12
A	loadRoutineFile()	0	15	2
A	extractBulkInsertTableColumnsInfo()	0	32	5
A	findFirstMatchingLine()	0	11	3
A	validateParameterLists()	0	28	5
A	dropRoutineIfExists()	0	5	2
A	readSourceCode()	0	28	5
A	substitutePlaceHolders()	0	23	2
A	extractPlaceholders()	0	21	4

<?php
declare(strict_types=1);

namespace SetBased\Stratum\MySql\Helper;

use SetBased\Exception\FallenException;
use SetBased\Helper\Cast;
use SetBased\Helper\InvalidCastException;
use SetBased\Stratum\Backend\StratumStyle;
use SetBased\Stratum\Common\DocBlock\DocBlockReflection;
use SetBased\Stratum\Common\Exception\RoutineLoaderException;
use SetBased\Stratum\Middle\Exception\ResultException;
use SetBased\Stratum\MySql\Exception\MySqlQueryErrorException;
use SetBased\Stratum\MySql\MySqlMetaDataLayer;
use Symfony\Component\Console\Formatter\OutputFormatter;

/**
 * Class for loading a single stored routine into a MySQL instance from pseudo SQL file.
 */
class RoutineLoaderHelper
{
  //--------------------------------------------------------------------------------------------------------------------
  /**
   * MySQL's and MariaDB's SQL/PSM syntax.
   */
  const SQL_PSM_SYNTAX = 1;

  /**
   * Oracle PL/SQL syntax.
   */
  const PL_SQL_SYNTAX = 2;

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * The metadata of the table columns of the table for bulk insert.
   *
   * @var array[]
   */
  private $bulkInsertColumns;

  /**
   * The keys in the nested array for bulk inserting data.
   *
   * @var string[]
   */
  private $bulkInsertKeys;

  /**
   * The name of table for bulk insert.
   *
   * @var string
   */
  private $bulkInsertTableName;

  /**
   * The default character set under which the stored routine will be loaded and run.
   *
   * @var string
   */
  private $characterSet;

  /**
   * The default collate under which the stored routine will be loaded and run.
   *
   * @var string
   */
  private $collate;

  /**
   * The designation type of the stored routine.
   *
   * @var string
   */
  private $designationType;

  /**
   * The meta data layer.
   *
   * @var MySqlMetaDataLayer
   */
  private $dl;

  /**
   * The DocBlock reflection object.
   *
   * @var DocBlockReflection|null
   */
  private $docBlockReflection;

  /**
   * Information about parameters with specific format (string in CSV format etc.).
   *
   * @var array
   */
  private $extendedParameters;

  /**
   * The last modification time of the source file.
   *
   * @var int
   */
  private $filemtime;

  /**
   * The key or index columns (depending on the designation type) of the stored routine.
   *
   * @var string[]
   */
  private $indexColumns;

  /**
   * The Output decorator
   *
   * @var StratumStyle
   */
  private $io;

  /**
   * The information about the parameters of the stored routine.
   *
   * @var array[]
   */
  private $parameters = [];

  /**
   * The metadata of the stored routine. Note: this data is stored in the metadata file and is generated by PhpStratum.
   *
   * @var array
   */
  private $phpStratumMetadata;

  /**
   * The old metadata of the stored routine.  Note: this data comes from the metadata file.
   *
   * @var array
   */
  private $phpStratumOldMetadata;

  /**
   * The old metadata of the stored routine. Note: this data comes from information_schema.ROUTINES.
   *
   * @var array
   */
  private $rdbmsOldRoutineMetadata;

  /**
   * The replace pairs (i.e. placeholders and their actual values, see strst).
   *
   * @var array
   */
  private $replace = [];

  /**
   * A map from placeholders to their actual values.
   *
   * @var array
   */
  private $replacePairs;

  /**
   * The return type of the stored routine (only if designation type singleton0, singleton1, or function).
   *
   * @var string|null
   */
  private $returnType;

  /**
   * The name of the stored routine.
   *
   * @var string
   */
  private $routineName;

  /**
   * The source code as a single string of the stored routine.
   *
   * @var string
   */
  private $routineSourceCode;

  /**
   * The source code as an array of lines string of the stored routine.
   *
   * @var array
   */
  private $routineSourceCodeLines;

  /**
   * The source filename holding the stored routine.
   *
   * @var string
   */
  private $sourceFilename;

  /**
   * The SQL mode helper object.
   *
   * @var SqlModeHelper
   */
  private $sqlModeHelper;

  /**
   * The syntax of the stored routine. Either SQL_PSM_SYNTAX or PL_SQL_SYNTAX.
   *
   * @var int
   */
  private $syntax;

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Object constructor.
   *
   * @param MySqlMetaDataLayer $dl                      The meta data layer.
   * @param StratumStyle       $io                      The output for log messages.
   * @param SqlModeHelper      $sqlModeHelper
   * @param string             $routineFilename         The filename of the source of the stored routine.
   * @param array              $phpStratumMetadata      The metadata of the stored routine from PhpStratum.
   * @param array              $replacePairs            A map from placeholders to their actual values.
   * @param array              $rdbmsOldRoutineMetadata The old metadata of the stored routine from MySQL.
   * @param string             $characterSet            The default character set under which the stored routine will
   *                                                    be loaded and run.
   * @param string             $collate                 The key or index columns (depending on the designation type) of
   *                                                    the stored routine.
   */
  public function __construct(MySqlMetaDataLayer $dl,
                              StratumStyle $io,
                              SqlModeHelper $sqlModeHelper,
                              string $routineFilename,
                              array $phpStratumMetadata,
                              array $replacePairs,
                              array $rdbmsOldRoutineMetadata,
                              string $characterSet,
                              string $collate)
  {
    $this->dl                      = $dl;
    $this->io                      = $io;
    $this->sqlModeHelper           = $sqlModeHelper;
    $this->sourceFilename          = $routineFilename;
    $this->phpStratumMetadata      = $phpStratumMetadata;
    $this->replacePairs            = $replacePairs;
    $this->rdbmsOldRoutineMetadata = $rdbmsOldRoutineMetadata;
    $this->characterSet            = $characterSet;
    $this->collate                 = $collate;
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Extract column metadata from the rows returned by the SQL statement 'describe table'.
   *
   * @param array $description The description of the table.
   *
   * @return array
   *
   * @throws InvalidCastException
   */
  private static function extractColumnsFromTableDescription(array $description): array
  {
    $ret = [];

    foreach ($description as $column)
    {
      preg_match('/^(?<data_type>\w+)(?<extra>.*)?$/', $column['Type'], $parts1);

      $tmp = ['column_name'       => $column['Field'],
              'data_type'         => $parts1['data_type'],
              'numeric_precision' => null,
              'numeric_scale'     => null,
              'dtd_identifier'    => $column['Type']];

      switch ($parts1[1])
      {
        case 'tinyint':
          preg_match('/^\((?<precision>\d+)\)/', $parts1['extra'], $parts2);
          $tmp['numeric_precision'] = Cast::toManInt($parts2['precision'] ?? 4);
          $tmp['numeric_scale']     = 0;
          break;

        case 'smallint':
          preg_match('/^\((?<precision>\d+)\)/', $parts1['extra'], $parts2);
          $tmp['numeric_precision'] = Cast::toManInt($parts2['precision'] ?? 6);
          $tmp['numeric_scale']     = 0;
          break;

        case 'mediumint':
          preg_match('/^\((?<precision>\d+)\)/', $parts1['extra'], $parts2);
          $tmp['numeric_precision'] = Cast::toManInt($parts2['precision'] ?? 9);
          $tmp['numeric_scale']     = 0;
          break;

        case 'int':
          preg_match('/^\((?<precision>\d+)\)/', $parts1['extra'], $parts2);
          $tmp['numeric_precision'] = Cast::toManInt($parts2['precision'] ?? 11);
          $tmp['numeric_scale']     = 0;
          break;

        case 'bigint':
          preg_match('/^\((?<precision>\d+)\)/', $parts1['extra'], $parts2);
          $tmp['numeric_precision'] = Cast::toManInt($parts2['precision'] ?? 20);
          $tmp['numeric_scale']     = 0;
          break;

        case 'year':
          // Nothing to do.
          break;

        case 'float':
          $tmp['numeric_precision'] = 12;
          break;

        case 'double':
          $tmp['numeric_precision'] = 22;
          break;

        case 'binary':
        case 'char':
        case 'varbinary':
        case 'varchar':
          // Nothing to do (binary) strings.
          break;

        case 'decimal':
          preg_match('/^\((?<precision>\d+),(<?scale>\d+)\)$/', $parts1['extra'], $parts2);
          $tmp['numeric_precision'] = Cast::toManInt($parts2['precision'] ?? 65);
          $tmp['numeric_scale']     = Cast::toManInt($parts2['scale'] ?? 0);
          break;

        case 'time':
        case 'timestamp':
        case 'date':
        case 'datetime':
          // Nothing to do date and time.
          break;

        case 'enum':
        case 'set':
          // Nothing to do sets.
          break;

        case 'bit':
          preg_match('/^\((?<precision>\d+)\)$/', $parts1['extra'], $parts2);
          $tmp['numeric_precision'] = Cast::toManInt($parts2['precision']);
          break;

        case 'tinytext':
        case 'text':
        case 'mediumtext':
        case 'longtext':
        case 'tinyblob':
        case 'blob':
        case 'mediumblob':
        case 'longblob':
          // Nothing to do CLOBs and BLOBs.
          break;

        default:
          throw new FallenException('data type', $parts1[1]);
      }

      $ret[] = $tmp;
    }

    return $ret;
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Loads the stored routine into the instance of MySQL and returns the metadata of the stored routine.
   *
   * @return array
   *
   * @throws RoutineLoaderException
   * @throws MySqlQueryErrorException
   * @throws ResultException
   * @throws InvalidCastException
   */
  public function loadStoredRoutine(): array
  {
    $this->routineName           = pathinfo($this->sourceFilename, PATHINFO_FILENAME);
    $this->phpStratumOldMetadata = $this->phpStratumMetadata;
    $this->filemtime             = filemtime($this->sourceFilename);

    $load = $this->mustLoadStoredRoutine();
    if ($load)
    {
      $this->io->text(sprintf('Loading routine <dbo>%s</dbo>', OutputFormatter::escape($this->routineName)));

      $this->readSourceCode();
      $this->extractPlaceholders();
      $this->extractDesignationType();
      $this->extractReturnType();
      $this->extractRoutineTypeAndName();
      $this->extractSyntax();
      $this->validateReturnType();
      $this->loadRoutineFile();
      $this->extractBulkInsertTableColumnsInfo();
      $this->extractExtendedParametersInfo();
      $this->extractRoutineParametersInfo();
      $this->extractDocBlockPartsWrapper();
      $this->validateParameterLists();
      $this->updateMetadata();
    }

    return $this->phpStratumMetadata;
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Drops the stored routine if it exists.
   *
   * @throws MySqlQueryErrorException
   */
  private function dropRoutineIfExists(): void
  {
    if (!empty($this->rdbmsOldRoutineMetadata))
    {
      $this->dl->dropRoutine($this->rdbmsOldRoutineMetadata['routine_type'], $this->routineName);
    }
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   *  Extracts the column names and column types of the current table for bulk insert.
   *
   * @throws InvalidCastException
   * @throws MySqlQueryErrorException
   * @throws ResultException
   * @throws RoutineLoaderException
   */
  private function extractBulkInsertTableColumnsInfo(): void
  {
    // Return immediately if designation type is not appropriate for this method.
    if ($this->designationType!='bulk_insert') return;

    // Check if table is a temporary table or a non-temporary table.
    $table_is_non_temporary = $this->dl->checkTableExists($this->bulkInsertTableName);

    // Create temporary table if table is non-temporary table.
    if (!$table_is_non_temporary)
    {
      $this->dl->callProcedure($this->routineName);
    }

    // Get information about the columns of the table.
    $description = $this->dl->describeTable($this->bulkInsertTableName);

    // Drop temporary table if table is non-temporary.
    if (!$table_is_non_temporary)
    {
      $this->dl->dropTemporaryTable($this->bulkInsertTableName);
    }

    // Check number of columns in the table match the number of fields given in the designation type.
    $n1 = sizeof($this->bulkInsertKeys);
    $n2 = sizeof($description);
    if ($n1!=$n2)
    {
      throw new RoutineLoaderException("Number of fields %d and number of columns %d don't match.", $n1, $n2);
    }

    $this->bulkInsertColumns = self::extractColumnsFromTableDescription($description);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Extracts the designation type of the stored routine.
   *
   * @throws RoutineLoaderException
   */
  private function extractDesignationType(): void
  {
    $tags = $this->docBlockReflection->getTags('type');

The method getTags() does not exist on null.

    if (count($tags)===0)
    {
      throw new RoutineLoaderException('Tag @type not found in DocBlock.');
    }
    elseif (count($tags)>1)
    {
      throw new RoutineLoaderException('Multiple @type tags found in DocBlock.');
    }

    $tag                   = $tags[0];
    $this->designationType = $tag['arguments'][0];
    switch ($this->designationType)
    {
      case 'bulk_insert':
        if ($tag['arguments'][1]==='' || $tag['arguments'][2]==='' || $tag['description'][0]!='')
        {
          throw new RoutineLoaderException('Invalid @type tag. Expected: @type bulk_insert <table_name> <columns>');
        }
        $this->bulkInsertTableName = $tag['arguments'][1];
        $this->bulkInsertKeys      = explode(',', $tag['arguments'][2]);
        break;

      case 'rows_with_key':
      case 'rows_with_index':
        if ($tag['arguments'][1]==='' || $tag['arguments'][2]!=='')
        {
          throw new RoutineLoaderException('Invalid @type tag. Expected: @type %s <columns>', $this->designationType);
        }
        $this->indexColumns = explode(',', $tag['arguments'][1]);
        break;

      default:
        if ($tag['arguments'][1]!=='')
        {
          throw new RoutineLoaderException('Error: Expected: @type %s', $this->designationType);
        }
    }
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Detects the syntax of the stored procedure. Either SQL/PSM or PL/SQL.
   *
   * @throws RoutineLoaderException
   */
  private function extractSyntax(): void
  {
    if ($this->sqlModeHelper->hasOracleMode())
    {
      if ($this->findFirstMatchingLine('/^\s*(modifies|reads)\s+sql\s+data\s*$/i')!==null)
      {
        $this->syntax = self::SQL_PSM_SYNTAX;
      }
      else
      {
        $key1 = $this->findFirstMatchingLine('/^\s*(as|is)\s*$/i');
        $key2 = $this->findFirstMatchingLine('/^\s*begin\s*$/i');

        if ($key1!==null and $key2!==null and $key1 < $key2)

Using logical operators such as and instead of && is generally not recommended.

        {
          $this->syntax = self::PL_SQL_SYNTAX;
        }
        elseif ($key1===null && $key2!==null)
        {
          $this->syntax = self::SQL_PSM_SYNTAX;
        }
        else
        {
          throw new RoutineLoaderException('Unable to derive syntax (SQL/PSM or PL/SQL) from stored routine.');
        }
      }
    }
    else
    {
      $this->syntax = self::SQL_PSM_SYNTAX;
    }
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Extracts DocBlock parts to be used by the wrapper generator.
   */
  private function extractDocBlockPartsWrapper(): array
  {
    $lookup = [];
    foreach ($this->docBlockReflection->getTags('param') as $tag)
    {
      $lookup[$tag['arguments'][0]] = $tag['description'];
    }

    $parameters = [];
    foreach ($this->parameters as $parameter)
    {
      $parameters[] = ['parameter_name'       => $parameter['parameter_name'],
                       'php_type'             => DataTypeHelper::columnTypeToPhpTypeHinting($parameter).'|null',
                       'data_type_descriptor' => $parameter['data_type_descriptor'],
                       'description'          => $lookup[($parameter['parameter_name'])] ?? []];
    }

    return ['sort_description' => $this->docBlockReflection->getShortDescription(),
            'long_description' => $this->docBlockReflection->getLongDescription(),
            'parameters'       => $parameters];
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Extracts extended info of the routine parameters.
   *
   * @throws RoutineLoaderException
   */
  private function extractExtendedParametersInfo(): void
  {
    $tags = $this->docBlockReflection->getTags('paramAddendum');
    foreach ($tags as $tag)
    {
      $parameterName = $tag['arguments'][0];
      $dataType      = $tag['arguments'][1];
      $delimiter     = $tag['arguments'][2];
      $enclosure     = $tag['arguments'][3];
      $escape        = $tag['arguments'][4];

      if ($parameterName==='' || $dataType=='' || $delimiter==='' || $enclosure==='' || $escape==='')
      {
        throw new RoutineLoaderException('Expected: @paramAddendum <field_name> <type_of_list> <delimiter> <enclosure> <escape>.');
      }

      if (isset($this->extendedParameters[$parameterName]))
      {
        throw new RoutineLoaderException("Duplicate @paramAddendum tag for parameter '%s'", $parameterName);
      }

      $this->extendedParameters[$parameterName] = ['name'      => $parameterName,
                                                   'data_type' => $dataType,
                                                   'delimiter' => $delimiter,
                                                   'enclosure' => $enclosure,
                                                   'escape'    => $escape];
    }
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Extracts the placeholders from the stored routine source.
   *
   * @throws RoutineLoaderException
   */
  private function extractPlaceholders(): void
  {
    $unknown = [];

    preg_match_all('(@[A-Za-z0-9_.]+(%(type|sort))?@)', $this->routineSourceCode, $matches);
    if (!empty($matches[0]))
    {
      foreach ($matches[0] as $placeholder)
      {
        if (isset($this->replacePairs[strtoupper($placeholder)]))
        {
          $this->replace[$placeholder] = $this->replacePairs[strtoupper($placeholder)];
        }
        else
        {
          $unknown[] = $placeholder;
        }
      }
    }

    $this->logUnknownPlaceholders($unknown);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Extracts the return type of the stored routine.
   *
   * @throws RoutineLoaderException
   */
  private function extractReturnType(): void
  {
    $tags = $this->docBlockReflection->getTags('return');

    switch ($this->designationType)
    {
      case 'function':
      case 'singleton0':
      case 'singleton1':
        if (count($tags)===0)
        {
          throw new RoutineLoaderException('Tag @return not found in DocBlock.');
        }
        $tag = $tags[0];
        if ($tag['arguments'][0]==='')
        {
          throw new RoutineLoaderException('Invalid return tag. Expected: @return <type>.');
        }
        $this->returnType = $tag['arguments'][0];
        break;

      default:
        if (count($tags)!==0)
        {
          throw new RoutineLoaderException('Redundant @type tag found in DocBlock.');
        }
    }
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Extracts info about the parameters of the stored routine.
   *
   * @throws RoutineLoaderException
   * @throws MySqlQueryErrorException
   */
  private function extractRoutineParametersInfo(): void
  {
    $routine_parameters = $this->dl->routineParameters($this->routineName);
    foreach ($routine_parameters as $key => $routine_parameter)
    {
      if ($routine_parameter['parameter_name'])
      {
        $data_type_descriptor = $routine_parameter['dtd_identifier'];
        if (isset($routine_parameter['character_set_name']))
        {
          $data_type_descriptor .= ' character set '.$routine_parameter['character_set_name'];
        }
        if (isset($routine_parameter['collation_name']))
        {
          $data_type_descriptor .= ' collation '.$routine_parameter['collation_name'];
        }

        $routine_parameter['data_type_descriptor'] = $data_type_descriptor;

        $this->parameters[$key] = $routine_parameter;
      }
    }

    $this->updateParametersInfo();
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Extracts the name of the stored routine and the stored routine type (i.e. procedure or function) source.
   *
   * @throws RoutineLoaderException
   */
  private function extractRoutineTypeAndName(): void
  {
    $n = preg_match('/create\\s+(procedure|function)\\s+([a-zA-Z0-9_]+)/i', $this->routineSourceCode, $matches);
    if ($n==1)
    {
      if ($this->routineName!=$matches[2])
      {
        throw new RoutineLoaderException("Stored routine name '%s' does not corresponds with filename", $matches[2]);
      }
    }
    else
    {
      throw new RoutineLoaderException('Unable to find the stored routine name and type');
    }
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Returns the key of the source line that match a regex pattern.
   *
   * @param string $pattern The regex pattern.
   *
   * @return int|null
   */
  private function findFirstMatchingLine(string $pattern): ?int
  {
    foreach ($this->routineSourceCodeLines as $key => $line)
    {
      if (preg_match($pattern, $line)===1)
      {
        return $key;
      }
    }

    return null;
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Loads the stored routine into the database.
   *
   * @throws MySqlQueryErrorException
   */
  private function loadRoutineFile(): void
  {
    if ($this->syntax===self::PL_SQL_SYNTAX)
    {
      $this->sqlModeHelper->addIfRequiredOracleMode();
    }
    else
    {
      $this->sqlModeHelper->removeIfRequiredOracleMode();
    }

    $routineSource = $this->substitutePlaceHolders();
    $this->dropRoutineIfExists();
    $this->dl->setCharacterSet($this->characterSet, $this->collate);
    $this->dl->loadRoutine($routineSource);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Logs the unknown placeholder (if any).
   *
   * @param array $unknown The unknown placeholders.
   *
   * @throws RoutineLoaderException
   */
  private function logUnknownPlaceholders(array $unknown): void
  {
    // Return immediately if there are no unknown placeholders.
    if (empty($unknown)) return;

    sort($unknown);
    $this->io->text('Unknown placeholder(s):');
    $this->io->listing($unknown);

    $replace = [];
    foreach ($unknown as $placeholder)
    {
      $replace[$placeholder] = '<error>'.$placeholder.'</error>';
    }
    $code = strtr(OutputFormatter::escape($this->routineSourceCode), $replace);

    $this->io->text(explode(PHP_EOL, $code));

    throw new RoutineLoaderException('Unknown placeholder(s) found');
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Returns true if the source file must be load or reloaded. Otherwise returns false.
   *
   * @return bool
   */
  private function mustLoadStoredRoutine(): bool
  {
    // If this is the first time we see the source file it must be loaded.
    if (empty($this->phpStratumOldMetadata)) return true;

    // If the source file has changed the source file must be loaded.
    if ($this->phpStratumOldMetadata['timestamp']!==$this->filemtime) return true;

    // If the value of a placeholder has changed the source file must be loaded.
    foreach ($this->phpStratumOldMetadata['replace'] as $place_holder => $old_value)
    {
      if (!isset($this->replacePairs[strtoupper($place_holder)]) ||
        $this->replacePairs[strtoupper($place_holder)]!==$old_value)
      {
        return true;
      }
    }

    // If stored routine not exists in database the source file must be loaded.
    if (empty($this->rdbmsOldRoutineMetadata)) return true;

    // If current sql-mode is different the source file must reload.
    if (!$this->sqlModeHelper->compare($this->rdbmsOldRoutineMetadata['sql_mode'])) return true;

    // If current character set is different the source file must reload.
    if ($this->rdbmsOldRoutineMetadata['character_set_client']!==$this->characterSet) return true;

    // If current collation is different the source file must reload.
    if ($this->rdbmsOldRoutineMetadata['collation_connection']!==$this->collate) return true;

    return false;
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Reads the source code of the stored routine.
   *
   * @throws RoutineLoaderException
   */
  private function readSourceCode(): void
  {
    $this->routineSourceCode      = file_get_contents($this->sourceFilename);
    $this->routineSourceCodeLines = explode(PHP_EOL, $this->routineSourceCode);

    if ($this->routineSourceCodeLines===false)
    {
      throw new RoutineLoaderException('Source file is empty');
    }

    $start = $this->findFirstMatchingLine('/^\s*\/\*\*\s*$/');
    $end   = $this->findFirstMatchingLine('/^\s*\*\/\s*$/');;
    if ($start!==null && $end!==null && $start<$end)
    {
      $lines    = array_slice($this->routineSourceCodeLines, $start, $end - $start + 1);
      $docBlock = implode(PHP_EOL, (array)$lines);
    }
    else
    {
      $docBlock = '';
    }

    DocBlockReflection::setTagParameters('param', 1);
    DocBlockReflection::setTagParameters('type', 3);
    DocBlockReflection::setTagParameters('return', 1);
    DocBlockReflection::setTagParameters('paramAddendum', 5);

    $this->docBlockReflection = new DocBlockReflection($docBlock);
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Returns the source of the routine with all placeholders substituted with their values.
   *
   * @return string
   */
  private function substitutePlaceHolders(): string
  {
    $realpath = realpath($this->sourceFilename);

    $this->replace['__FILE__']    = "'".$this->dl->realEscapeString($realpath)."'";
    $this->replace['__ROUTINE__'] = "'".$this->routineName."'";
    $this->replace['__DIR__']     = "'".$this->dl->realEscapeString(dirname($realpath))."'";

    $lines          = explode(PHP_EOL, $this->routineSourceCode);
    $routine_source = [];
    foreach ($lines as $i => $line)
    {
      $this->replace['__LINE__'] = $i + 1;
      $routine_source[$i]        = strtr($line, $this->replace);
    }
    $routine_source = implode(PHP_EOL, $routine_source);

    unset($this->replace['__FILE__']);
    unset($this->replace['__ROUTINE__']);
    unset($this->replace['__DIR__']);
    unset($this->replace['__LINE__']);

    return $routine_source;
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Updates the metadata for the stored routine.
   */
  private function updateMetadata(): void
  {
    $this->phpStratumMetadata['routine_name']           = $this->routineName;
    $this->phpStratumMetadata['designation']            = $this->designationType;
    $this->phpStratumMetadata['return']                 = $this->returnType;
    $this->phpStratumMetadata['parameters']             = $this->parameters;
    $this->phpStratumMetadata['timestamp']              = $this->filemtime;
    $this->phpStratumMetadata['replace']                = $this->replace;
    $this->phpStratumMetadata['phpdoc']                 = $this->extractDocBlockPartsWrapper();
    $this->phpStratumMetadata['spec_params']            = $this->extendedParameters;
    $this->phpStratumMetadata['index_columns']          = $this->indexColumns;
    $this->phpStratumMetadata['bulk_insert_table_name'] = $this->bulkInsertTableName;
    $this->phpStratumMetadata['bulk_insert_columns']    = $this->bulkInsertColumns;
    $this->phpStratumMetadata['bulk_insert_keys']       = $this->bulkInsertKeys;
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Update information about specific parameters of stored routine.
   *
   * @throws RoutineLoaderException
   */
  private function updateParametersInfo(): void
  {
    if (!empty($this->extendedParameters))
    {
      foreach ($this->extendedParameters as $spec_param_name => $spec_param_info)
      {
        $param_not_exist = true;
        foreach ($this->parameters as $key => $param_info)
        {
          if ($param_info['parameter_name']==$spec_param_name)
          {
            $this->parameters[$key] = array_merge($this->parameters[$key], $spec_param_info);
            $param_not_exist        = false;
            break;
          }
        }
        if ($param_not_exist)
        {
          throw new RoutineLoaderException("Specific parameter '%s' does not exist", $spec_param_name);
        }
      }
    }
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Validates the parameters found the DocBlock in the source of the stored routine against the parameters from the
   * metadata of MySQL and reports missing and unknown parameters names.
   */
  private function validateParameterLists(): void
  {
    // Make list with names of parameters used in database.
    $databaseParametersNames = [];
    foreach ($this->parameters as $parameter)
    {
      $databaseParametersNames[] = $parameter['parameter_name'];
    }

    // Make list with names of parameters used in dock block of routine.
    $docBlockParametersNames = [];
    foreach ($this->docBlockReflection->getTags('param') as $tag)
    {
      $docBlockParametersNames[] = $tag['arguments'][0];
    }

    // Check and show warning if any parameters is missing in doc block.
    $tmp = array_diff($databaseParametersNames, $docBlockParametersNames);
    foreach ($tmp as $name)
    {
      $this->io->logNote('Parameter <dbo>%s</dbo> is missing from doc block', $name);
    }

    // Check and show warning if find unknown parameters in doc block.
    $tmp = array_diff($docBlockParametersNames, $databaseParametersNames);
    foreach ($tmp as $name)
    {
      $this->io->logNote('Unknown parameter <dbo>%s</dbo> found in doc block', $name);
    }
  }

  //--------------------------------------------------------------------------------------------------------------------
  /**
   * Validates the specified return type of the stored routine.
   *
   * @throws RoutineLoaderException
   */
  private function validateReturnType(): void
  {
    // Return immediately if designation type is not appropriate for this method.
    if (!in_array($this->designationType, ['function', 'singleton0', 'singleton1'])) return;

    $types = explode('|', $this->returnType);
    $diff  = array_diff($types, ['string', 'int', 'float', 'double', 'bool', 'null']);

    if (!($this->returnType=='mixed' || $this->returnType=='bool' || empty($diff)))
    {
      throw new RoutineLoaderException("Return type must be 'mixed', 'bool', or a combination of 'int', 'float', 'string', and 'null'");
    }

    // The following tests are applicable for singleton0 routines only.
    if (!in_array($this->designationType, ['singleton0'])) return;

    // Return mixed is OK.
    if (in_array($this->returnType, ['bool', 'mixed'])) return;

    // In all other cases return type must contain null.
    $parts = explode('|', $this->returnType);
    $key   = array_search('null', $parts);
    if ($key===false)
    {
      throw new RoutineLoaderException("Return type must be 'mixed', 'bool', or contain 'null' (with a combination of 'int', 'float', and 'string')");
    }
  }

  //--------------------------------------------------------------------------------------------------------------------
}

//----------------------------------------------------------------------------------------------------------------------