From f0acec37c2647423201338e181a37ac5cbf0fda7 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Mon, 20 Apr 2020 11:43:18 -0400 Subject: [PATCH] Restore #16947 - APIv4 support for sql functions and grouping --- Civi/Api4/Generic/AbstractGetAction.php | 2 +- Civi/Api4/Generic/DAOGetAction.php | 56 ++++++ Civi/Api4/Generic/DAOGetFieldsAction.php | 4 + Civi/Api4/Query/Api4SelectQuery.php | 159 ++++++++++----- Civi/Api4/Query/SqlExpression.php | 151 ++++++++++++++ Civi/Api4/Query/SqlField.php | 30 +++ Civi/Api4/Query/SqlFunction.php | 189 ++++++++++++++++++ Civi/Api4/Query/SqlFunctionAVG.php | 27 +++ Civi/Api4/Query/SqlFunctionCOUNT.php | 28 +++ Civi/Api4/Query/SqlFunctionMAX.php | 27 +++ Civi/Api4/Query/SqlFunctionMIN.php | 27 +++ Civi/Api4/Query/SqlFunctionSUM.php | 27 +++ Civi/Api4/Query/SqlNull.php | 26 +++ Civi/Api4/Query/SqlNumber.php | 27 +++ Civi/Api4/Query/SqlString.php | 32 +++ Civi/Api4/Query/SqlWild.php | 26 +++ ang/api4Explorer/Explorer.html | 50 ++++- ang/api4Explorer/Explorer.js | 42 +++- .../api/v4/Action/SqlExpressionTest.php | 75 +++++++ .../phpunit/api/v4/Action/SqlFunctionTest.php | 108 ++++++++++ .../api/v4/Query/SqlExpressionParserTest.php | 90 +++++++++ tests/phpunit/api/v4/UnitTestCase.php | 2 + 22 files changed, 1135 insertions(+), 70 deletions(-) create mode 100644 Civi/Api4/Query/SqlExpression.php create mode 100644 Civi/Api4/Query/SqlField.php create mode 100644 Civi/Api4/Query/SqlFunction.php create mode 100644 Civi/Api4/Query/SqlFunctionAVG.php create mode 100644 Civi/Api4/Query/SqlFunctionCOUNT.php create mode 100644 Civi/Api4/Query/SqlFunctionMAX.php create mode 100644 Civi/Api4/Query/SqlFunctionMIN.php create mode 100644 Civi/Api4/Query/SqlFunctionSUM.php create mode 100644 Civi/Api4/Query/SqlNull.php create mode 100644 Civi/Api4/Query/SqlNumber.php create mode 100644 Civi/Api4/Query/SqlString.php create mode 100644 Civi/Api4/Query/SqlWild.php create mode 100644 tests/phpunit/api/v4/Action/SqlExpressionTest.php create mode 100644 tests/phpunit/api/v4/Action/SqlFunctionTest.php create mode 100644 tests/phpunit/api/v4/Query/SqlExpressionParserTest.php diff --git a/Civi/Api4/Generic/AbstractGetAction.php b/Civi/Api4/Generic/AbstractGetAction.php index d82e7071b1..dc020f4af1 100644 --- a/Civi/Api4/Generic/AbstractGetAction.php +++ b/Civi/Api4/Generic/AbstractGetAction.php @@ -81,7 +81,7 @@ abstract class AbstractGetAction extends AbstractQueryAction { */ protected function expandSelectClauseWildcards() { $wildFields = array_filter($this->select, function($item) { - return strpos($item, '*') !== FALSE && strpos($item, '.') === FALSE; + return strpos($item, '*') !== FALSE && strpos($item, '.') === FALSE && strpos($item, '(') === FALSE && strpos($item, ' ') === FALSE; }); foreach ($wildFields as $item) { $pos = array_search($item, array_values($this->select)); diff --git a/Civi/Api4/Generic/DAOGetAction.php b/Civi/Api4/Generic/DAOGetAction.php index 43785e0f52..ab7d85b966 100644 --- a/Civi/Api4/Generic/DAOGetAction.php +++ b/Civi/Api4/Generic/DAOGetAction.php @@ -47,6 +47,22 @@ class DAOGetAction extends AbstractGetAction { */ protected $select = []; + /** + * Field(s) by which to group the results. + * + * @var array + */ + protected $groupBy = []; + + /** + * Clause for filtering results after grouping and filters are applied. + * + * Each expression should correspond to an item from the SELECT array. + * + * @var array + */ + protected $having = []; + public function _run(Result $result) { $this->setDefaultWhereClause(); $this->expandSelectClauseWildcards(); @@ -66,4 +82,44 @@ class DAOGetAction extends AbstractGetAction { return $result; } + /** + * @return array + */ + public function getGroupBy(): array { + return $this->groupBy; + } + + /** + * @param array $groupBy + * @return $this + */ + public function setGroupBy(array $groupBy) { + $this->groupBy = $groupBy; + return $this; + } + + /** + * @param string $field + * @return $this + */ + public function addGroupBy(string $field) { + $this->groupBy[] = $field; + return $this; + } + + /** + * @param string $expr + * @param string $op + * @param mixed $value + * @return $this + * @throws \API_Exception + */ + public function addHaving(string $expr, string $op, $value = NULL) { + if (!in_array($op, \CRM_Core_DAO::acceptedSQLOperators())) { + throw new \API_Exception('Unsupported operator'); + } + $this->having[] = [$expr, $op, $value]; + return $this; + } + } diff --git a/Civi/Api4/Generic/DAOGetFieldsAction.php b/Civi/Api4/Generic/DAOGetFieldsAction.php index 7d7ac1849d..74afc1715f 100644 --- a/Civi/Api4/Generic/DAOGetFieldsAction.php +++ b/Civi/Api4/Generic/DAOGetFieldsAction.php @@ -63,6 +63,10 @@ class DAOGetFieldsAction extends BasicGetFieldsAction { 'name' => 'help_post', 'data_type' => 'String', ]; + $fields[] = [ + 'name' => 'column_name', + 'data_type' => 'String', + ]; $fields[] = [ 'name' => 'custom_field_id', 'data_type' => 'Integer', diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php index 898cbbfa9d..21298de2a0 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -41,18 +41,17 @@ class Api4SelectQuery extends SelectQuery { */ protected $apiVersion = 4; - /** - * @var array - * Maps select fields to [, ] - */ - protected $fkSelectAliases = []; - /** * @var \Civi\Api4\Service\Schema\Joinable\Joinable[] * The joinable tables that have been joined so far */ protected $joinedTables = []; + /** + * @var array + */ + protected $selectAliases = []; + /** * If set to an array, this will start collecting debug info. * @@ -60,6 +59,18 @@ class Api4SelectQuery extends SelectQuery { */ public $debugOutput = NULL; + /** + * @var array + */ + public $groupBy = []; + + public $forceSelectId = TRUE; + + /** + * @var array + */ + public $having = []; + /** * @param \Civi\Api4\Generic\DAOGetAction $apiGet */ @@ -68,6 +79,7 @@ class Api4SelectQuery extends SelectQuery { $this->checkPermissions = $apiGet->getCheckPermissions(); $this->select = $apiGet->getSelect(); $this->where = $apiGet->getWhere(); + $this->groupBy = $apiGet->getGroupBy(); $this->orderBy = $apiGet->getOrderBy(); $this->limit = $apiGet->getLimit(); $this->offset = $apiGet->getOffset(); @@ -79,9 +91,9 @@ class Api4SelectQuery extends SelectQuery { } $baoName = CoreUtil::getBAOFromApiName($this->entity); $this->entityFieldNames = array_column($baoName::fields(), 'name'); - $this->apiFieldSpec = $apiGet->entityFields(); - foreach ($this->apiFieldSpec as $key => $field) { - $this->apiFieldSpec[$key]['sql_name'] = '`' . self::MAIN_TABLE_ALIAS . '`.`' . $field['column_name'] . '`'; + foreach ($apiGet->entityFields() as $path => $field) { + $field['sql_name'] = '`' . self::MAIN_TABLE_ALIAS . '`.`' . $field['column_name'] . '`'; + $this->addSpecField($path, $field); } $this->constructQueryObject($baoName); @@ -103,6 +115,8 @@ class Api4SelectQuery extends SelectQuery { $this->buildWhereClause(); $this->buildOrderBy(); $this->buildLimit(); + $this->buildGroupBy(); + $this->buildHavingClause(); return $this->query->toSQL(); } @@ -118,20 +132,19 @@ class Api4SelectQuery extends SelectQuery { $this->debugOutput['sql'][] = $sql; } $query = \CRM_Core_DAO::executeQuery($sql); - + $i = 0; while ($query->fetch()) { + $id = $query->id ?? $i++; if (in_array('row_count', $this->select)) { $results[]['row_count'] = (int) $query->c; break; } - $results[$query->id] = []; - foreach ($this->select as $alias) { + $results[$id] = []; + foreach ($this->selectAliases as $alias => $expr) { $returnName = $alias; - if ($this->isOneToOneField($alias)) { - $alias = str_replace('.', '_', $alias); - $results[$query->id][$returnName] = property_exists($query, $alias) ? $query->$alias : NULL; - } - }; + $alias = str_replace('.', '_', $alias); + $results[$id][$returnName] = property_exists($query, $alias) ? $query->$alias : NULL; + } } $event = new PostSelectQueryEvent($results, $this); \Civi::dispatcher()->dispatch(Events::POST_SELECT_QUERY, $event); @@ -140,6 +153,7 @@ class Api4SelectQuery extends SelectQuery { } protected function buildSelectClause() { + // An empty select is the same as * if (empty($this->select)) { $this->select = $this->entityFieldNames; } @@ -148,12 +162,13 @@ class Api4SelectQuery extends SelectQuery { return; } else { - // Always select id field - $this->select = array_merge(['id'], $this->select); + if ($this->forceSelectId) { + $this->select = array_merge(['id'], $this->select); + } // Expand wildcards in joins (the api wrapper already expanded non-joined wildcards) $wildFields = array_filter($this->select, function($item) { - return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE; + return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE && strpos($item, '(') === FALSE && strpos($item, ' ') === FALSE; }); foreach ($wildFields as $item) { $pos = array_search($item, array_values($this->select)); @@ -163,20 +178,27 @@ class Api4SelectQuery extends SelectQuery { } $this->select = array_unique($this->select); } - foreach ($this->select as $fieldName) { - $field = $this->getField($fieldName); - if (!$this->isOneToOneField($fieldName)) { - continue; - } - elseif ($field) { - $this->query->select($field['sql_name'] . " AS `$fieldName`"); - } - // Remove unknown fields without raising an error - else { - $this->select = array_diff($this->select, [$fieldName]); - if (is_array($this->debugOutput)) { - $this->debugOutput['undefined_fields'][] = $fieldName; + foreach ($this->select as $item) { + $expr = SqlExpression::convert($item, TRUE); + $valid = TRUE; + foreach ($expr->getFields() as $fieldName) { + $field = $this->getField($fieldName); + // Remove expressions with unknown fields without raising an error + if (!$field) { + $this->select = array_diff($this->select, [$item]); + if (is_array($this->debugOutput)) { + $this->debugOutput['undefined_fields'][] = $fieldName; + } + $valid = FALSE; } + elseif ($field['is_many']) { + $valid = FALSE; + } + } + if ($valid) { + $alias = $expr->getAlias(); + $this->selectAliases[$alias] = $expr->getExpr(); + $this->query->select($expr->render($this->apiFieldSpec) . " AS `$alias`"); } } } @@ -205,11 +227,15 @@ class Api4SelectQuery extends SelectQuery { * @inheritDoc */ protected function buildOrderBy() { - foreach ($this->orderBy as $fieldName => $dir) { + foreach ($this->orderBy as $item => $dir) { if ($dir !== 'ASC' && $dir !== 'DESC') { - throw new \API_Exception("Invalid sort direction. Cannot order by $fieldName $dir"); + throw new \API_Exception("Invalid sort direction. Cannot order by $item $dir"); } - $this->query->orderBy($this->getField($fieldName, TRUE)['sql_name'] . " $dir"); + $expr = SqlExpression::convert($item); + foreach ($expr->getFields() as $fieldName) { + $this->getField($fieldName, TRUE); + } + $this->query->orderBy($expr->render($this->apiFieldSpec) . " $dir"); } } @@ -223,6 +249,19 @@ class Api4SelectQuery extends SelectQuery { } } + /** + * Adds GROUP BY clause to query + */ + protected function buildGroupBy() { + foreach ($this->groupBy as $item) { + $expr = SqlExpression::convert($item); + foreach ($expr->getFields() as $fieldName) { + $this->getField($fieldName, TRUE); + } + $this->query->groupBy($expr->render($this->apiFieldSpec)); + } + } + /** * Recursively validate and transform a branch or leaf clause array to SQL. * @@ -314,6 +353,7 @@ class Api4SelectQuery extends SelectQuery { * * @param string $fieldName * @param bool $strict + * In strict mode, this will throw an exception if the field doesn't exist * * @return string|null * @throws \API_Exception @@ -324,27 +364,22 @@ class Api4SelectQuery extends SelectQuery { $this->joinFK($fieldName); } $field = $this->apiFieldSpec[$fieldName] ?? NULL; - // Check if field exists and we have permission to view it - if ($field && (!$this->checkPermissions || empty($field['permission']) || \CRM_Core_Permission::check($field['permission']))) { - return $field; - } - elseif ($strict) { + if ($strict && !$field) { throw new \API_Exception("Invalid field '$fieldName'"); } - return NULL; + return $field; } /** * Joins a path and adds all fields in the joined eneity to apiFieldSpec * * @param $key - * @return bool * @throws \API_Exception * @throws \Exception */ protected function joinFK($key) { if (isset($this->apiFieldSpec[$key])) { - return TRUE; + return; } $pathArray = explode('.', $key); @@ -356,15 +391,24 @@ class Api4SelectQuery extends SelectQuery { $pathString = implode('.', $pathArray); if (!$joiner->canJoin($this, $pathString)) { - return FALSE; + return; } $joinPath = $joiner->join($this, $pathString); + + $isMany = FALSE; + foreach ($joinPath as $joinable) { + if ($joinable->getJoinType() === Joinable::JOIN_TYPE_ONE_TO_MANY) { + $isMany = TRUE; + } + } + /** @var \Civi\Api4\Service\Schema\Joinable\Joinable $lastLink */ $lastLink = array_pop($joinPath); // Custom field names are already prefixed - if ($lastLink instanceof CustomGroupJoinable) { + $isCustom = $lastLink instanceof CustomGroupJoinable; + if ($isCustom) { array_pop($pathArray); } $prefix = $pathArray ? implode('.', $pathArray) . '.' : ''; @@ -373,10 +417,11 @@ class Api4SelectQuery extends SelectQuery { foreach ($lastLink->getEntityFields() as $fieldObject) { $fieldArray = ['entity' => $joinEntity] + $fieldObject->toArray(); $fieldArray['sql_name'] = '`' . $lastLink->getAlias() . '`.`' . $fieldArray['column_name'] . '`'; - $this->apiFieldSpec[$prefix . $fieldArray['name']] = $fieldArray; + $fieldArray['is_custom'] = $isCustom; + $fieldArray['is_join'] = TRUE; + $fieldArray['is_many'] = $isMany; + $this->addSpecField($prefix . $fieldArray['name'], $fieldArray); } - - return TRUE; } /** @@ -583,4 +628,20 @@ class Api4SelectQuery extends SelectQuery { return $path; } + /** + * @param $path + * @param $field + */ + private function addSpecField($path, $field) { + // Only add field to spec if we have permission + if ($this->checkPermissions && !empty($field['permission']) && !\CRM_Core_Permission::check($field['permission'])) { + $this->apiFieldSpec[$path] = FALSE; + return; + } + $defaults = []; + $defaults['is_custom'] = $defaults['is_join'] = $defaults['is_many'] = FALSE; + $field += $defaults; + $this->apiFieldSpec[$path] = $field; + } + } diff --git a/Civi/Api4/Query/SqlExpression.php b/Civi/Api4/Query/SqlExpression.php new file mode 100644 index 0000000000..e21f3880f4 --- /dev/null +++ b/Civi/Api4/Query/SqlExpression.php @@ -0,0 +1,151 @@ +expr = $expr; + $this->alias = $alias; + $this->initialize(); + } + + abstract protected function initialize(); + + /** + * Converts a string to a SqlExpression object. + * + * E.g. the expression "SUM(foo)" would return a SqlFunctionSUM object. + * + * @param string $expression + * @param bool $parseAlias + * @param array $mustBe + * @param array $cantBe + * @return SqlExpression + * @throws \API_Exception + */ + public static function convert(string $expression, $parseAlias = FALSE, $mustBe = [], $cantBe = ['SqlWild']) { + $as = $parseAlias ? strrpos($expression, ' AS ') : FALSE; + $expr = $as ? substr($expression, 0, $as) : $expression; + $alias = $as ? \CRM_Utils_String::munge(substr($expression, $as + 4)) : NULL; + $bracketPos = strpos($expr, '('); + $firstChar = substr($expr, 0, 1); + $lastChar = substr($expr, -1); + // If there are brackets but not the first character, we have a function + if ($bracketPos && $lastChar === ')') { + $fnName = substr($expr, 0, $bracketPos); + if ($fnName !== strtoupper($fnName)) { + throw new \API_Exception('Sql function must be uppercase.'); + } + $className = 'SqlFunction' . $fnName; + } + // String expression + elseif ($firstChar === $lastChar && in_array($firstChar, ['"', "'"], TRUE)) { + $className = 'SqlString'; + } + elseif ($expr === 'NULL') { + $className = 'SqlNull'; + } + elseif ($expr === '*') { + $className = 'SqlWild'; + } + elseif (is_numeric($expr)) { + $className = 'SqlNumber'; + } + // If none of the above, assume it's a field name + else { + $className = 'SqlField'; + } + $className = __NAMESPACE__ . '\\' . $className; + if (!class_exists($className)) { + throw new \API_Exception('Unable to parse sql expression: ' . $expression); + } + $sqlExpression = new $className($expr, $alias); + foreach ($cantBe as $cant) { + if (is_a($sqlExpression, __NAMESPACE__ . '\\' . $cant)) { + throw new \API_Exception('Illegal sql expression.'); + } + } + if ($mustBe) { + foreach ($mustBe as $must) { + if (is_a($sqlExpression, __NAMESPACE__ . '\\' . $must)) { + return $sqlExpression; + } + } + throw new \API_Exception('Illegal sql expression.'); + } + return $sqlExpression; + } + + /** + * Returns the field names of all sql columns that are arguments to this expression. + * + * @return array + */ + public function getFields(): array { + return $this->fields; + } + + /** + * Renders expression to a sql string, replacing field names with column names. + * + * @param array $fieldList + * @return string + */ + abstract public function render(array $fieldList): string; + + /** + * @return string + */ + public function getExpr(): string { + return $this->expr; + } + + /** + * Returns the alias to use for SELECT AS. + * + * @return string + */ + public function getAlias(): string { + return $this->alias ?? $this->fields[0] ?? \CRM_Utils_String::munge($this->expr); + } + +} diff --git a/Civi/Api4/Query/SqlField.php b/Civi/Api4/Query/SqlField.php new file mode 100644 index 0000000000..712bd7b8cc --- /dev/null +++ b/Civi/Api4/Query/SqlField.php @@ -0,0 +1,30 @@ +fields[] = $this->expr; + } + + public function render(array $fieldList): string { + if (empty($fieldList[$this->expr])) { + throw new \API_Exception("Invalid field '{$this->expr}'"); + } + return $fieldList[$this->expr]['sql_name']; + } + +} diff --git a/Civi/Api4/Query/SqlFunction.php b/Civi/Api4/Query/SqlFunction.php new file mode 100644 index 0000000000..19753d91ac --- /dev/null +++ b/Civi/Api4/Query/SqlFunction.php @@ -0,0 +1,189 @@ +expr, strpos($this->expr, '(') + 1, -1)); + foreach ($this->getParams() as $param) { + $prefix = $this->captureKeyword($param['prefix'], $arg); + if ($param['expr'] && isset($prefix) || in_array('', $param['prefix']) || !$param['optional']) { + $this->captureExpressions($arg, $param['expr'], $param['must_be'], $param['cant_be']); + $this->captureKeyword($param['suffix'], $arg); + } + } + } + + /** + * Shift a keyword off the beginning of the argument string and into the argument array. + * + * @param array $keywords + * Whitelist of keywords + * @param string $arg + * @return mixed|null + */ + private function captureKeyword($keywords, &$arg) { + foreach (array_filter($keywords) as $key) { + if (strpos($arg, $key . ' ') === 0) { + $this->args[] = $key; + $arg = ltrim(substr($arg, strlen($key))); + return $key; + } + } + return NULL; + } + + /** + * Shifts 0 or more expressions off the argument string and into the argument array + * + * @param string $arg + * @param int $limit + * @param array $mustBe + * @param array $cantBe + * @throws \API_Exception + */ + private function captureExpressions(&$arg, $limit, $mustBe, $cantBe) { + $captured = 0; + $arg = ltrim($arg); + while ($arg) { + $item = $this->captureExpression($arg); + $arg = ltrim(substr($arg, strlen($item))); + $expr = SqlExpression::convert($item, FALSE, $mustBe, $cantBe); + $this->fields = array_merge($this->fields, $expr->getFields()); + if ($captured) { + $this->args[] = ','; + } + $this->args[] = $expr; + $captured++; + // Keep going if we have a comma indicating another expression follows + if ($captured < $limit && substr($arg, 0, 1) === ',') { + $arg = ltrim(substr($arg, 1)); + } + else { + return; + } + } + } + + /** + * Scans the beginning of a string for an expression; stops when it hits delimiter + * + * @param $arg + * @return string + */ + private function captureExpression($arg) { + $chars = str_split($arg); + $isEscaped = $quote = NULL; + $item = ''; + $quotes = ['"', "'"]; + $brackets = [ + ')' => '(', + ]; + $enclosures = array_fill_keys($brackets, 0); + foreach ($chars as $index => $char) { + if (!$isEscaped && in_array($char, $quotes, TRUE)) { + // Open quotes - we'll ignore everything inside + if (!$quote) { + $quote = $char; + } + // Close quotes + elseif ($char === $quote) { + $quote = NULL; + } + } + if (!$quote) { + // Delineates end of expression + if (($char == ',' || $char == ' ') && !array_filter($enclosures)) { + return $item; + } + // Open brackets - we'll ignore delineators inside + if (isset($enclosures[$char])) { + $enclosures[$char]++; + } + // Close brackets + if (isset($brackets[$char]) && $enclosures[$brackets[$char]]) { + $enclosures[$brackets[$char]]--; + } + } + $item .= $char; + // We are escaping the next char if this is a backslash not preceded by an odd number of backslashes + $isEscaped = $char === '\\' && ((strlen($item) - strlen(rtrim($item, '\\'))) % 2); + } + return $item; + } + + public function render(array $fieldList): string { + $output = $this->getName() . '('; + foreach ($this->args as $index => $arg) { + if ($index && $arg !== ',') { + $output .= ' '; + } + if (is_object($arg)) { + $output .= $arg->render($fieldList); + } + else { + $output .= $arg; + } + } + return $output . ')'; + } + + /** + * @inheritDoc + */ + public function getAlias(): string { + return $this->alias ?? $this->getName() . ':' . implode('_', $this->fields); + } + + /** + * Get the name of this sql function. + * @return string + */ + public static function getName(): string { + $className = static::class; + return substr($className, strrpos($className, 'SqlFunction') + 11); + } + + /** + * Get the param metadata for this sql function. + * @return array + */ + public static function getParams(): array { + $params = []; + foreach (static::$params as $param) { + // Merge in defaults to ensure each param has these properties + $params[] = $param + [ + 'prefix' => [], + 'expr' => 1, + 'suffix' => [], + 'optional' => FALSE, + 'must_be' => [], + 'cant_be' => ['SqlWild'], + ]; + } + return $params; + } + +} diff --git a/Civi/Api4/Query/SqlFunctionAVG.php b/Civi/Api4/Query/SqlFunctionAVG.php new file mode 100644 index 0000000000..9a064135e9 --- /dev/null +++ b/Civi/Api4/Query/SqlFunctionAVG.php @@ -0,0 +1,27 @@ + ['', 'DISTINCT', 'ALL'], + 'expr' => 1, + 'must_be' => ['SqlField'], + ], + ]; + +} diff --git a/Civi/Api4/Query/SqlFunctionCOUNT.php b/Civi/Api4/Query/SqlFunctionCOUNT.php new file mode 100644 index 0000000000..d444675350 --- /dev/null +++ b/Civi/Api4/Query/SqlFunctionCOUNT.php @@ -0,0 +1,28 @@ + ['', 'DISTINCT', 'ALL'], + 'expr' => 1, + 'must_be' => ['SqlField', 'SqlWild'], + 'cant_be' => [], + ], + ]; + +} diff --git a/Civi/Api4/Query/SqlFunctionMAX.php b/Civi/Api4/Query/SqlFunctionMAX.php new file mode 100644 index 0000000000..f80ebeea15 --- /dev/null +++ b/Civi/Api4/Query/SqlFunctionMAX.php @@ -0,0 +1,27 @@ + ['', 'DISTINCT', 'ALL'], + 'expr' => 1, + 'must_be' => ['SqlField'], + ], + ]; + +} diff --git a/Civi/Api4/Query/SqlFunctionMIN.php b/Civi/Api4/Query/SqlFunctionMIN.php new file mode 100644 index 0000000000..993a5b18eb --- /dev/null +++ b/Civi/Api4/Query/SqlFunctionMIN.php @@ -0,0 +1,27 @@ + ['', 'DISTINCT', 'ALL'], + 'expr' => 1, + 'must_be' => ['SqlField'], + ], + ]; + +} diff --git a/Civi/Api4/Query/SqlFunctionSUM.php b/Civi/Api4/Query/SqlFunctionSUM.php new file mode 100644 index 0000000000..36f4ebb3cc --- /dev/null +++ b/Civi/Api4/Query/SqlFunctionSUM.php @@ -0,0 +1,27 @@ + ['', 'DISTINCT', 'ALL'], + 'expr' => 1, + 'must_be' => ['SqlField'], + ], + ]; + +} diff --git a/Civi/Api4/Query/SqlNull.php b/Civi/Api4/Query/SqlNull.php new file mode 100644 index 0000000000..046d04c749 --- /dev/null +++ b/Civi/Api4/Query/SqlNull.php @@ -0,0 +1,26 @@ +expr, 'Float'); + } + + public function render(array $fieldList): string { + return $this->expr; + } + +} diff --git a/Civi/Api4/Query/SqlString.php b/Civi/Api4/Query/SqlString.php new file mode 100644 index 0000000000..8ea9c00137 --- /dev/null +++ b/Civi/Api4/Query/SqlString.php @@ -0,0 +1,32 @@ +expr, 1, -1); + // Unescape the outer quote character inside the string to prevent double-escaping in render() + $quot = substr($this->expr, 0, 1); + $backslash = chr(0) . 'backslash' . chr(0); + $this->expr = str_replace(['\\\\', "\\$quot", $backslash], [$backslash, $quot, '\\\\'], $str); + } + + public function render(array $fieldList): string { + return '"' . \CRM_Core_DAO::escapeString($this->expr) . '"'; + } + +} diff --git a/Civi/Api4/Query/SqlWild.php b/Civi/Api4/Query/SqlWild.php new file mode 100644 index 0000000000..7799042102 --- /dev/null +++ b/Civi/Api4/Query/SqlWild.php @@ -0,0 +1,26 @@ + -
- orderBy * -
- - +
+ groupBy * +
+
+ + + +
+
+
+ +
+
+
+
+
+ orderBy * +
+
+ + + + +
-
+
+
+ + + + + + + + + +
+
+
chain
diff --git a/ang/api4Explorer/Explorer.js b/ang/api4Explorer/Explorer.js index 1db4d564f8..369474fc0b 100644 --- a/ang/api4Explorer/Explorer.js +++ b/ang/api4Explorer/Explorer.js @@ -222,9 +222,25 @@ } }; - $scope.isSpecial = function(name) { - var specialParams = ['select', 'fields', 'action', 'where', 'values', 'defaults', 'orderBy', 'chain']; - return _.contains(specialParams, name); + // Gets params that should be represented as generic input fields in the explorer + // This fn doesn't have to be particularly efficient as its output is cached in one-time bindings + $scope.getGenericParams = function(paramType, defaultNull) { + // Returns undefined if params are not yet set; one-time bindings will stabilize when this function returns a value + if (_.isEmpty($scope.availableParams)) { + return; + } + var specialParams = ['select', 'fields', 'action', 'where', 'values', 'defaults', 'orderBy', 'chain', 'groupBy', 'having']; + if ($scope.availableParams.limit && $scope.availableParams.offset) { + specialParams.push('limit', 'offset'); + } + return _.transform($scope.availableParams, function(genericParams, param, name) { + if (!_.contains(specialParams, name) && + !(typeof paramType !== 'undefined' && !_.contains(paramType, param.type[0])) && + !(typeof defaultNull !== 'undefined' && ((param.default === null) !== defaultNull)) + ) { + genericParams[name] = param; + } + }); }; $scope.selectRowCount = function() { @@ -383,8 +399,8 @@ deep: format === 'json' }); } - if (typeof objectParams[name] !== 'undefined') { - $scope.$watch('params.' + name, function(values) { + if (typeof objectParams[name] !== 'undefined' && name !== 'orderBy') { + $scope.$watch('params.' + name, function (values) { // Remove empty values _.each(values, function (clause, index) { if (!clause || !clause[0]) { @@ -408,13 +424,17 @@ var field = value; $timeout(function() { if (field) { - var defaultOp = _.cloneDeep(objectParams[name]); - if (name === 'chain') { - var num = $scope.params.chain.length; - defaultOp[0] = field; - field = 'name_me_' + num; + if (typeof objectParams[name] === 'undefined') { + $scope.params[name].push(field); + } else { + var defaultOp = _.cloneDeep(objectParams[name]); + if (name === 'chain') { + var num = $scope.params.chain.length; + defaultOp[0] = field; + field = 'name_me_' + num; + } + $scope.params[name].push([field, defaultOp]); } - $scope.params[name].push([field, defaultOp]); $scope.controls[name] = null; } }); diff --git a/tests/phpunit/api/v4/Action/SqlExpressionTest.php b/tests/phpunit/api/v4/Action/SqlExpressionTest.php new file mode 100644 index 0000000000..3145c9d656 --- /dev/null +++ b/tests/phpunit/api/v4/Action/SqlExpressionTest.php @@ -0,0 +1,75 @@ +addValue('first_name', 'bob')->setCheckPermissions(FALSE)->execute(); + $result = Contact::get() + ->addSelect('NULL AS nothing', 'NULL', 'NULL AS b*d char', 'first_name AS firsty') + ->addWhere('first_name', '=', 'bob') + ->setLimit(1) + ->execute() + ->first(); + $this->assertNull($result['nothing']); + $this->assertNull($result['NULL']); + $this->assertNull($result['b_d_char']); + $this->assertEquals('bob', $result['firsty']); + $this->assertArrayNotHasKey('b*d char', $result); + } + + public function testSelectNumbers() { + Contact::create()->addValue('first_name', 'bob')->setCheckPermissions(FALSE)->execute(); + $result = Contact::get() + ->addSelect('first_name', 123, 45.678, '-55 AS neg') + ->addWhere('first_name', '=', 'bob') + ->setLimit(1) + ->execute() + ->first(); + $this->assertEquals('bob', $result['first_name']); + $this->assertEquals('123', $result['123']); + $this->assertEquals('-55', $result['neg']); + $this->assertEquals('45.678', $result['45_678']); + } + + public function testSelectStrings() { + Contact::create()->addValue('first_name', 'bob')->setCheckPermissions(FALSE)->execute(); + $result = Contact::get() + ->addSelect('first_name AS bob') + ->addSelect('"hello world" AS hi') + ->addSelect('"can\'t \"quote\"" AS quot') + ->addWhere('first_name', '=', 'bob') + ->setLimit(1) + ->execute() + ->first(); + $this->assertEquals('bob', $result['bob']); + $this->assertEquals('hello world', $result['hi']); + $this->assertEquals('can\'t "quote"', $result['quot']); + } + +} diff --git a/tests/phpunit/api/v4/Action/SqlFunctionTest.php b/tests/phpunit/api/v4/Action/SqlFunctionTest.php new file mode 100644 index 0000000000..f948b22db4 --- /dev/null +++ b/tests/phpunit/api/v4/Action/SqlFunctionTest.php @@ -0,0 +1,108 @@ +assertArrayHasKey('SUM', $functions); + $this->assertArrayNotHasKey('', $functions); + $this->assertArrayNotHasKey('SqlFunction', $functions); + $this->assertEquals(1, $functions['MAX']['params'][0]['expr']); + } + + public function testGroupAggregates() { + $cid = Contact::create()->setCheckPermissions(FALSE)->addValue('first_name', 'bill')->execute()->first()['id']; + Contribution::save() + ->setCheckPermissions(FALSE) + ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1]) + ->setRecords([ + ['total_amount' => 100, 'receive_date' => '2020-01-01'], + ['total_amount' => 200, 'receive_date' => '2020-01-01'], + ['total_amount' => 300, 'receive_date' => '2020-01-01'], + ['total_amount' => 400, 'receive_date' => '2020-01-01'], + ]) + ->execute(); + $agg = Contribution::get() + ->setCheckPermissions(FALSE) + ->addGroupBy('contact_id') + ->addWhere('contact_id', '=', $cid) + ->addSelect('AVG(total_amount) AS average') + ->addSelect('SUM(total_amount)') + ->addSelect('MAX(total_amount)') + ->addSelect('MIN(total_amount)') + ->addSelect('COUNT(*) AS count') + ->execute() + ->first(); + $this->assertEquals(250, $agg['average']); + $this->assertEquals(1000, $agg['SUM:total_amount']); + $this->assertEquals(400, $agg['MAX:total_amount']); + $this->assertEquals(100, $agg['MIN:total_amount']); + $this->assertEquals(4, $agg['count']); + } + + public function testGroupHaving() { + $cid = Contact::create()->setCheckPermissions(FALSE)->addValue('first_name', 'donor')->execute()->first()['id']; + Contribution::save() + ->setCheckPermissions(FALSE) + ->setDefaults(['contact_id' => $cid, 'financial_type_id' => 1]) + ->setRecords([ + ['total_amount' => 100, 'receive_date' => '2020-02-02'], + ['total_amount' => 200, 'receive_date' => '2020-02-02'], + ['total_amount' => 300, 'receive_date' => '2020-03-03'], + ['total_amount' => 400, 'receive_date' => '2020-04-04'], + ]) + ->execute(); + $result = Contribution::get() + ->setCheckPermissions(FALSE) + ->addGroupBy('contact_id') + ->addGroupBy('receive_date') + ->addSelect('contact_id') + ->addSelect('receive_date') + ->addSelect('AVG(total_amount) AS average') + ->addSelect('SUM(total_amount)') + ->addSelect('MAX(total_amount)') + ->addSelect('MIN(total_amount)') + ->addSelect('COUNT(*) AS count') + ->addOrderBy('receive_date') + ->addHaving('contact_id', '=', $cid) + ->addHaving('receive_date', '<', '2020-04-01') + ->execute(); + $this->assertCount(2, $result); + $this->assertEquals(150, $result[0]['average']); + $this->assertEquals(300, $result[1]['average']); + $this->assertEquals(300, $result[0]['SUM:total_amount']); + $this->assertEquals(300, $result[1]['SUM:total_amount']); + $this->assertEquals(200, $result[0]['MAX:total_amount']); + $this->assertEquals(100, $result[0]['MIN:total_amount']); + $this->assertEquals(2, $result[0]['count']); + $this->assertEquals(1, $result[1]['count']); + } + +} diff --git a/tests/phpunit/api/v4/Query/SqlExpressionParserTest.php b/tests/phpunit/api/v4/Query/SqlExpressionParserTest.php new file mode 100644 index 0000000000..4be10cefec --- /dev/null +++ b/tests/phpunit/api/v4/Query/SqlExpressionParserTest.php @@ -0,0 +1,90 @@ +assertNotEmpty($params[0]['prefix']); + $this->assertEmpty($params[0]['suffix']); + + $sqlFn = new $className($fnName . '(total)'); + $this->assertEquals($fnName, $sqlFn->getName()); + $this->assertEquals(['total'], $sqlFn->getFields()); + $this->assertCount(1, $this->getArgs($sqlFn)); + + $sqlFn = SqlExpression::convert($fnName . '(DISTINCT stuff)'); + $this->assertEquals($fnName, $sqlFn->getName()); + $this->assertEquals("Civi\Api4\Query\SqlFunction$fnName", get_class($sqlFn)); + $this->assertEquals($params, $sqlFn->getParams()); + $this->assertEquals(['stuff'], $sqlFn->getFields()); + $this->assertCount(2, $this->getArgs($sqlFn)); + + try { + $sqlFn = SqlExpression::convert($fnName . '(*)'); + if ($fnName === 'COUNT') { + $this->assertTrue(is_a($this->getArgs($sqlFn)[0], 'Civi\Api4\Query\SqlWild')); + } + else { + $this->fail('SqlWild should only be allowed in COUNT.'); + } + } + catch (\API_Exception $e) { + $this->assertContains('Illegal', $e->getMessage()); + } + } + + /** + * @param \Civi\Api4\Query\SqlFunction $fn + * @return array + * @throws \ReflectionException + */ + private function getArgs($fn) { + $ref = new \ReflectionClass($fn); + $args = $ref->getProperty('args'); + $args->setAccessible(TRUE); + return $args->getValue($fn); + } + +} diff --git a/tests/phpunit/api/v4/UnitTestCase.php b/tests/phpunit/api/v4/UnitTestCase.php index 977e737a60..ebe220b584 100644 --- a/tests/phpunit/api/v4/UnitTestCase.php +++ b/tests/phpunit/api/v4/UnitTestCase.php @@ -25,6 +25,8 @@ use api\v4\Traits\TestDataLoaderTrait; use Civi\Test\HeadlessInterface; use Civi\Test\TransactionalInterface; +require_once 'api/Exception.php'; + /** * @group headless */ -- 2.25.1