[Summary view]

Differences Between: [Versions 400 and 401] [Versions 400 and 402] [Versions 400 and 403]

   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       * Generates unique parameter name that must be used in generated SQL
  51       *
  52       * @return string
  53       */
  54      public static function generate_param_name(): string {
  55          static $paramcount = 0;
  56  
  57          return static::GENERATE_PARAM_PREFIX . ($paramcount++);
  58      }
  59  
  60      /**
  61       * Validate that parameter names were generated using {@see generate_param_name}.
  62       *
  63       * @param array $params
  64       * @return bool
  65       * @throws coding_exception For invalid params.
  66       */
  67      public static function validate_params(array $params): bool {
  68          $nonmatchingkeys = array_filter($params, static function($key): bool {
  69              return !preg_match('/^' . static::GENERATE_PARAM_PREFIX . '[\d]+/', $key);
  70          }, ARRAY_FILTER_USE_KEY);
  71  
  72          if (!empty($nonmatchingkeys)) {
  73              throw new coding_exception('Invalid parameter names', implode(', ', array_keys($nonmatchingkeys)));
  74          }
  75  
  76          return true;
  77      }
  78  
  79      /**
  80       * Replace parameter names within given SQL expression, allowing caller to specify callback to handle their replacement
  81       * primarily to ensure uniqueness when the expression is to be used as part of a larger query
  82       *
  83       * @param string $sql
  84       * @param array $params
  85       * @param callable $callback Method that takes a single string parameter, and returns another string
  86       * @return string
  87       */
  88      public static function sql_replace_parameter_names(string $sql, array $params, callable $callback): string {
  89          foreach ($params as $param) {
  90  
  91              // Pattern to look for param within the SQL.
  92              $pattern = '/:(?<param>' . preg_quote($param) . ')\b/';
  93  
  94              $sql = preg_replace_callback($pattern, function(array $matches) use ($callback): string {
  95                  return ':' . $callback($matches['param']);
  96              }, $sql);
  97          }
  98  
  99          return $sql;
 100      }
 101  
 102      /**
 103       * Generate SQL expression for sorting group concatenated fields
 104       *
 105       * @param string $field The original field or SQL expression
 106       * @param string|null $sort A valid SQL ORDER BY to sort the concatenated fields, if omitted then $field will be used
 107       * @return string
 108       */
 109      public static function sql_group_concat_sort(string $field, string $sort = null): string {
 110          global $DB;
 111  
 112          // Fallback to sorting by the specified field, unless it contains parameters which would be duplicated.
 113          if ($sort === null && !preg_match('/[:?$]/', $field)) {
 114              $fieldsort = $field;
 115          } else {
 116              $fieldsort = $sort;
 117          }
 118  
 119          // Nothing to sort by.
 120          if ($fieldsort === null) {
 121              return '';
 122          }
 123  
 124          // If the sort specifies a direction, we need to handle that differently in Postgres.
 125          if ($DB->get_dbfamily() === 'postgres') {
 126              $fieldsortdirection = '';
 127  
 128              preg_match('/(?<direction>ASC|DESC)?$/i', $fieldsort, $matches);
 129              if (array_key_exists('direction', $matches)) {
 130                  $fieldsortdirection = $matches['direction'];
 131                  $fieldsort = core_text::substr($fieldsort, 0, -(core_text::strlen($fieldsortdirection)));
 132              }
 133  
 134              // Cast sort, stick the direction on the end.
 135              $fieldsort = "CAST({$fieldsort} AS VARCHAR) {$fieldsortdirection}";
 136          }
 137  
 138          return $fieldsort;
 139      }
 140  }