[Summary view]

Differences Between: [Versions 400 and 401]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  declare(strict_types=1);
  18  
  19  namespace core_reportbuilder\local\helpers;
  20  
  21  use coding_exception;
  22  use core_text;
  23  
  24  /**
  25   * Helper functions for DB manipulations
  26   *
  27   * @package     core_reportbuilder
  28   * @copyright   2019 Marina Glancy
  29   * @license     http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  30   */
  31  class database {
  32  
  33      /** @var string Prefix for generated aliases */
  34      private const GENERATE_ALIAS_PREFIX = 'rbalias';
  35  
  36      /** @var string Prefix for generated param names names */
  37      private const GENERATE_PARAM_PREFIX = 'rbparam';
  38  
  39      /**
  40       * Generates unique table/column alias that must be used in generated SQL
  41       *
  42       * @return string
  43       */
  44      public static function generate_alias(): string {
  45          static $aliascount = 0;
  46  
  47          return static::GENERATE_ALIAS_PREFIX . ($aliascount++);
  48      }
  49  
  50      /**
  51       * Generate multiple unique table/column aliases, see {@see generate_alias} for info
  52       *
  53       * @param int $count
  54       * @return string[]
  55       */
  56      public static function generate_aliases(int $count): array {
  57          return array_map([
  58              static::class, 'generate_alias'
  59          ], array_fill(0, $count, null));
  60      }
  61  
  62      /**
  63       * Generates unique parameter name that must be used in generated SQL
  64       *
  65       * @return string
  66       */
  67      public static function generate_param_name(): string {
  68          static $paramcount = 0;
  69  
  70          return static::GENERATE_PARAM_PREFIX . ($paramcount++);
  71      }
  72  
  73      /**
  74       * Generate multiple unique parameter names, see {@see generate_param_name} for info
  75       *
  76       * @param int $count
  77       * @return string[]
  78       */
  79      public static function generate_param_names(int $count): array {
  80          return array_map([
  81              static::class, 'generate_param_name'
  82          ], array_fill(0, $count, null));
  83      }
  84  
  85      /**
  86       * Validate that parameter names were generated using {@see generate_param_name}.
  87       *
  88       * @param array $params
  89       * @return bool
  90       * @throws coding_exception For invalid params.
  91       */
  92      public static function validate_params(array $params): bool {
  93          $nonmatchingkeys = array_filter($params, static function($key): bool {
  94              return !preg_match('/^' . static::GENERATE_PARAM_PREFIX . '[\d]+/', $key);
  95          }, ARRAY_FILTER_USE_KEY);
  96  
  97          if (!empty($nonmatchingkeys)) {
  98              throw new coding_exception('Invalid parameter names', implode(', ', array_keys($nonmatchingkeys)));
  99          }
 100  
 101          return true;
 102      }
 103  
 104      /**
 105       * Replace parameter names within given SQL expression, allowing caller to specify callback to handle their replacement
 106       * primarily to ensure uniqueness when the expression is to be used as part of a larger query
 107       *
 108       * @param string $sql
 109       * @param array $params
 110       * @param callable $callback Method that takes a single string parameter, and returns another string
 111       * @return string
 112       */
 113      public static function sql_replace_parameter_names(string $sql, array $params, callable $callback): string {
 114          foreach ($params as $param) {
 115  
 116              // Pattern to look for param within the SQL.
 117              $pattern = '/:(?<param>' . preg_quote($param) . ')\b/';
 118  
 119              $sql = preg_replace_callback($pattern, function(array $matches) use ($callback): string {
 120                  return ':' . $callback($matches['param']);
 121              }, $sql);
 122          }
 123  
 124          return $sql;
 125      }
 126  
 127      /**
 128       * Generate SQL expression for sorting group concatenated fields
 129       *
 130       * @param string $field The original field or SQL expression
 131       * @param string|null $sort A valid SQL ORDER BY to sort the concatenated fields, if omitted then $field will be used
 132       * @return string
 133       */
 134      public static function sql_group_concat_sort(string $field, string $sort = null): string {
 135          global $DB;
 136  
 137          // Fallback to sorting by the specified field, unless it contains parameters which would be duplicated.
 138          if ($sort === null && !preg_match('/[:?$]/', $field)) {
 139              $fieldsort = $field;
 140          } else {
 141              $fieldsort = $sort;
 142          }
 143  
 144          // Nothing to sort by.
 145          if ($fieldsort === null) {
 146              return '';
 147          }
 148  
 149          // If the sort specifies a direction, we need to handle that differently in Postgres.
 150          if ($DB->get_dbfamily() === 'postgres') {
 151              $fieldsortdirection = '';
 152  
 153              preg_match('/(?<direction>ASC|DESC)?$/i', $fieldsort, $matches);
 154              if (array_key_exists('direction', $matches)) {
 155                  $fieldsortdirection = $matches['direction'];
 156                  $fieldsort = core_text::substr($fieldsort, 0, -(core_text::strlen($fieldsortdirection)));
 157              }
 158  
 159              // Cast sort, stick the direction on the end.
 160              $fieldsort = $DB->sql_cast_to_char($fieldsort) . ' ' . $fieldsortdirection;
 161          }
 162  
 163          return $fieldsort;
 164      }
 165  }