Civi/Api4/Query/SqlExpression.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 namespace Civi\Api4\Query;
  13
  14 /**
  15  * Base class for SqlColumn, SqlString, SqlBool, and SqlFunction classes.
  16  *
  17  * These are used to validate and format sql expressions in Api4 select queries.
  18  *
  19  * @package Civi\Api4\Query
  20  */
  21 abstract class SqlExpression {
  22
  23   /**
  24    * @var array
  25    */
  26   protected $fields = [];
  27
  28   /**
  29    * The SELECT alias (if null it will be calculated by getAlias)
  30    * @var string|null
  31    */
  32   protected $alias;
  33
  34   /**
  35    * The raw expression, minus the alias.
  36    * @var string
  37    */
  38   public $expr = '';
  39
  40   /**
  41    * Whether or not pseudoconstant suffixes should be evaluated during output.
  42    *
  43    * @var bool
  44    * @see \Civi\Api4\Utils\FormattingUtil::formatOutputValues
  45    */
  46   public $supportsExpansion = FALSE;
  47
  48   /**
  49    * Data type output by this expression
  50    *
  51    * @var string
  52    */
  53   protected static $dataType;
  54
  55   /**
  56    * SqlFunction constructor.
  57    * @param string $expr
  58    * @param string|null $alias
  59    */
  60   public function __construct(string $expr, $alias = NULL) {
  61     $this->expr = $expr;
  62     $this->alias = $alias;
  63     $this->initialize();
  64   }
  65
  66   abstract protected function initialize();
  67
  68   /**
  69    * Converts a string to a SqlExpression object.
  70    *
  71    * E.g. the expression "SUM(foo)" would return a SqlFunctionSUM object.
  72    *
  73    * @param string $expression
  74    * @param bool $parseAlias
  75    * @param array $mustBe
  76    * @return SqlExpression
  77    * @throws \API_Exception
  78    */
  79   public static function convert(string $expression, $parseAlias = FALSE, $mustBe = []) {
  80     $as = $parseAlias ? strrpos($expression, ' AS ') : FALSE;
  81     $expr = $as ? substr($expression, 0, $as) : $expression;
  82     $alias = $as ? \CRM_Utils_String::munge(substr($expression, $as + 4), '_', 256) : NULL;
  83     $bracketPos = strpos($expr, '(');
  84     $firstChar = substr($expr, 0, 1);
  85     $lastChar = substr($expr, -1);
  86     // Statement surrounded by brackets is an equation
  87     if ($firstChar === '(' && $lastChar === ')') {
  88       $className = 'SqlEquation';
  89     }
  90     // If there are brackets but not the first character, we have a function
  91     elseif ($bracketPos && $lastChar === ')') {
  92       $fnName = substr($expr, 0, $bracketPos);
  93       if ($fnName !== strtoupper($fnName)) {
  94         throw new \API_Exception('Sql function must be uppercase.');
  95       }
  96       $className = 'SqlFunction' . $fnName;
  97     }
  98     // String expression
  99     elseif ($firstChar === $lastChar && in_array($firstChar, ['"', "'"], TRUE)) {
 100       $className = 'SqlString';
 101     }
 102     elseif ($expr === 'NULL') {
 103       $className = 'SqlNull';
 104     }
 105     elseif ($expr === '*') {
 106       $className = 'SqlWild';
 107     }
 108     elseif (is_numeric($expr)) {
 109       $className = 'SqlNumber';
 110     }
 111     // If none of the above, assume it's a field name
 112     else {
 113       $className = 'SqlField';
 114     }
 115     $className = __NAMESPACE__ . '\\' . $className;
 116     if (!class_exists($className)) {
 117       throw new \API_Exception('Unable to parse sql expression: ' . $expression);
 118     }
 119     $sqlExpression = new $className($expr, $alias);
 120     if ($mustBe) {
 121       foreach ($mustBe as $must) {
 122         if (is_a($sqlExpression, __NAMESPACE__ . '\\' . $must)) {
 123           return $sqlExpression;
 124         }
 125       }
 126       throw new \API_Exception('Illegal sql expression.');
 127     }
 128     return $sqlExpression;
 129   }
 130
 131   /**
 132    * Returns the field names of all sql columns that are arguments to this expression.
 133    *
 134    * @return array
 135    */
 136   public function getFields(): array {
 137     return $this->fields;
 138   }
 139
 140   /**
 141    * Renders expression to a sql string, replacing field names with column names.
 142    *
 143    * @param array $fieldList
 144    * @return string
 145    */
 146   abstract public function render(array $fieldList): string;
 147
 148   /**
 149    * @return string
 150    */
 151   public function getExpr(): string {
 152     return $this->expr;
 153   }
 154
 155   /**
 156    * Returns the alias to use for SELECT AS.
 157    *
 158    * @return string
 159    */
 160   public function getAlias(): string {
 161     return $this->alias ?? $this->fields[0] ?? \CRM_Utils_String::munge($this->expr, '_', 256);
 162   }
 163
 164   /**
 165    * Returns the name of this sql expression class.
 166    *
 167    * @return string
 168    */
 169   public function getType(): string {
 170     $className = get_class($this);
 171     return substr($className, strrpos($className, '\\') + 1);
 172   }
 173
 174   /**
 175    * @return string
 176    */
 177   abstract public static function getTitle(): string;
 178
 179   /**
 180    * @return string|NULL
 181    */
 182   public static function getDataType():? string {
 183     return static::$dataType;
 184   }
 185
 186   /**
 187    * Shift a keyword off the beginning of the argument string and return it.
 188    *
 189    * @param array $keywords
 190    *   Whitelist of keywords
 191    * @param string $arg
 192    * @return mixed|null
 193    */
 194   protected function captureKeyword($keywords, &$arg) {
 195     foreach ($keywords as $key) {
 196       // Match keyword followed by a space or eol
 197       if (strpos($arg, $key . ' ') === 0 || rtrim($arg) === $key) {
 198         $arg = ltrim(substr($arg, strlen($key)));
 199         return $key;
 200       }
 201     }
 202     return NULL;
 203   }
 204
 205   /**
 206    * Shifts 0 or more expressions off the argument string and returns them
 207    *
 208    * @param string $arg
 209    * @param array $mustBe
 210    * @param int $max
 211    * @return SqlExpression[]
 212    * @throws \API_Exception
 213    */
 214   protected function captureExpressions(string &$arg, array $mustBe, int $max) {
 215     $captured = [];
 216     $arg = ltrim($arg);
 217     while ($arg) {
 218       $item = $this->captureExpression($arg);
 219       $arg = ltrim(substr($arg, strlen($item)));
 220       $expr = self::convert($item, FALSE, $mustBe);
 221       $this->fields = array_merge($this->fields, $expr->getFields());
 222       $captured[] = $expr;
 223       // Keep going if we have a comma indicating another expression follows
 224       if (count($captured) < $max && substr($arg, 0, 1) === ',') {
 225         $arg = ltrim(substr($arg, 1));
 226       }
 227       else {
 228         break;
 229       }
 230     }
 231     return $captured;
 232   }
 233
 234   /**
 235    * Scans the beginning of a string for an expression; stops when it hits delimiter
 236    *
 237    * @param $arg
 238    * @return string
 239    */
 240   protected function captureExpression($arg) {
 241     $isEscaped = $quote = NULL;
 242     $item = '';
 243     $quotes = ['"', "'"];
 244     $brackets = [
 245       ')' => '(',
 246     ];
 247     $enclosures = array_fill_keys($brackets, 0);
 248     foreach (str_split($arg) as $char) {
 249       if (!$isEscaped && in_array($char, $quotes, TRUE)) {
 250         // Open quotes - we'll ignore everything inside
 251         if (!$quote) {
 252           $quote = $char;
 253         }
 254         // Close quotes
 255         elseif ($char === $quote) {
 256           $quote = NULL;
 257         }
 258       }
 259       if (!$quote) {
 260         // Delineates end of expression
 261         if (($char == ',' || $char == ' ') && !array_filter($enclosures)) {
 262           return $item;
 263         }
 264         // Open brackets - we'll ignore delineators inside
 265         if (isset($enclosures[$char])) {
 266           $enclosures[$char]++;
 267         }
 268         // Close brackets
 269         if (isset($brackets[$char]) && $enclosures[$brackets[$char]]) {
 270           $enclosures[$brackets[$char]]--;
 271         }
 272       }
 273       $item .= $char;
 274       // We are escaping the next char if this is a backslash not preceded by an odd number of backslashes
 275       $isEscaped = $char === '\\' && ((strlen($item) - strlen(rtrim($item, '\\'))) % 2);
 276     }
 277     return $item;
 278   }
 279
 280 }