From 3176b04cb62b0e8f94454e367736f50454f89de8 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Tue, 31 Mar 2020 17:33:19 -0400 Subject: [PATCH] APIv4 - Add SQL expression handling and aggregate functions Adds a series of SqlExpression classes to parse and validate sql expressions used in SELECT, GROUP BY and ORDER BY clauses. The framework can handle most sql functions, but this first commit adds support for the aggregate functions AVG, COUNT, MAX, MIN & SUM --- Civi/Api4/Generic/AbstractGetAction.php | 2 +- Civi/Api4/Generic/DAOGetFieldsAction.php | 4 + Civi/Api4/Query/Api4SelectQuery.php | 76 ++++--- Civi/Api4/Query/SqlExpression.php | 144 +++++++++++++ Civi/Api4/Query/SqlField.php | 30 +++ Civi/Api4/Query/SqlFunction.php | 190 ++++++++++++++++++ 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 +++ .../api/v4/Action/SqlExpressionTest.php | 75 +++++++ .../phpunit/api/v4/Action/SqlFunctionTest.php | 62 ++++++ .../api/v4/Query/SqlExpressionParserTest.php | 90 +++++++++ tests/phpunit/api/v4/UnitTestCase.php | 2 + 19 files changed, 888 insertions(+), 34 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/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 38c46ad887..09e95f147e 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -47,6 +47,11 @@ class Api4SelectQuery extends SelectQuery { */ protected $joinedTables = []; + /** + * @var array + */ + protected $selectAliases = []; + /** * If set to an array, this will start collecting debug info. * @@ -124,12 +129,10 @@ class Api4SelectQuery extends SelectQuery { break; } $results[$id] = []; - foreach ($this->select as $alias) { + foreach ($this->selectAliases as $alias) { $returnName = $alias; - if ($this->isOneToOneField($alias)) { - $alias = str_replace('.', '_', $alias); - $results[$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); @@ -155,7 +158,7 @@ class Api4SelectQuery extends SelectQuery { // 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)); @@ -165,20 +168,26 @@ class Api4SelectQuery extends SelectQuery { } $this->select = array_unique($this->select); } - foreach ($this->select as $fieldName) { - $field = $this->getField($fieldName); - // Remove unknown fields without raising an error - if (!$field) { - $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; } } - elseif ($field['is_many']) { - continue; - } - elseif ($field) { - $this->query->select($field['sql_name'] . " AS `$fieldName`"); + if ($valid) { + $alias = $this->selectAliases[] = $expr->getAlias(); + $this->query->select($expr->render($this->apiFieldSpec) . " AS `$alias`"); } } } @@ -197,11 +206,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"); + } + $expr = SqlExpression::convert($item); + foreach ($expr->getFields() as $fieldName) { + $this->getField($fieldName, TRUE); } - $this->query->orderBy($this->getField($fieldName, TRUE)['sql_name'] . " $dir"); + $this->query->orderBy($expr->render($this->apiFieldSpec) . " $dir"); } } @@ -215,16 +228,15 @@ class Api4SelectQuery extends SelectQuery { } /** - * + * Adds GROUP BY clause to query */ protected function buildGroupBy() { - foreach ($this->groupBy as $field) { - if ($this->isOneToOneField($field) && $this->getField($field)) { - $this->query->groupBy($field['sql_name']); - } - else { - throw new \API_Exception("Invalid field. Cannot group by $field"); + 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)); } } @@ -300,6 +312,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 @@ -310,13 +323,10 @@ class Api4SelectQuery extends SelectQuery { $this->joinFK($fieldName); } $field = $this->apiFieldSpec[$fieldName] ?? NULL; - if ($field) { - return $field; - } - elseif ($strict) { + if ($strict && !$field) { throw new \API_Exception("Invalid field '$fieldName'"); } - return NULL; + return $field; } /** diff --git a/Civi/Api4/Query/SqlExpression.php b/Civi/Api4/Query/SqlExpression.php new file mode 100644 index 0000000000..198ce1c51c --- /dev/null +++ b/Civi/Api4/Query/SqlExpression.php @@ -0,0 +1,144 @@ +arg = $arg; + $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); + // 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; + $expr = substr($expr, $bracketPos + 1, -1); + } + // 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; + + /** + * 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->arg); + } + +} diff --git a/Civi/Api4/Query/SqlField.php b/Civi/Api4/Query/SqlField.php new file mode 100644 index 0000000000..488e3b052c --- /dev/null +++ b/Civi/Api4/Query/SqlField.php @@ -0,0 +1,30 @@ +fields[] = $this->arg; + } + + public function render(array $fieldList): string { + if (empty($fieldList[$this->arg])) { + throw new \API_Exception("Invalid field '{$this->arg}'"); + } + return $fieldList[$this->arg]['sql_name']; + } + +} diff --git a/Civi/Api4/Query/SqlFunction.php b/Civi/Api4/Query/SqlFunction.php new file mode 100644 index 0000000000..df93b66603 --- /dev/null +++ b/Civi/Api4/Query/SqlFunction.php @@ -0,0 +1,190 @@ +arg; + 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 function getName(): string { + $className = get_class($this); + $pos = strrpos($className, 'SqlFunction'); + return substr($className, $pos + 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 @@ +arg, 'Float'); + } + + public function render(array $fieldList): string { + return $this->arg; + } + +} diff --git a/Civi/Api4/Query/SqlString.php b/Civi/Api4/Query/SqlString.php new file mode 100644 index 0000000000..12c85ab393 --- /dev/null +++ b/Civi/Api4/Query/SqlString.php @@ -0,0 +1,32 @@ +arg, 1, -1); + // Unescape the outer quote character inside the string to prevent double-escaping in render() + $quot = substr($this->arg, 0, 1); + $backslash = chr(0) . 'backslash' . chr(0); + $this->arg = str_replace(['\\\\', "\\$quot", $backslash], [$backslash, $quot, '\\\\'], $str); + } + + public function render(array $fieldList): string { + return '"' . \CRM_Core_DAO::escapeString($this->arg) . '"'; + } + +} 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 @@ +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..9b33c594c3 --- /dev/null +++ b/tests/phpunit/api/v4/Action/SqlFunctionTest.php @@ -0,0 +1,62 @@ +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']); + } + +} diff --git a/tests/phpunit/api/v4/Query/SqlExpressionParserTest.php b/tests/phpunit/api/v4/Query/SqlExpressionParserTest.php new file mode 100644 index 0000000000..cf43dbd6c0 --- /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('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