From f4138bc4aee38bd264d3b2f800bc6a3703d62c29 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Mon, 20 Sep 2021 12:33:07 -0400 Subject: [PATCH] APIv4 - Add support for sql equations --- Civi/Api4/Query/SqlEquation.php | 109 ++++++++++++++++++ Civi/Api4/Query/SqlExpression.php | 99 +++++++++++++++- Civi/Api4/Query/SqlFunction.php | 94 +-------------- Civi/Api4/Query/SqlFunctionIF.php | 1 + .../api/v4/Action/ContactApiKeyTest.php | 8 ++ .../api/v4/Action/SqlExpressionTest.php | 28 +++++ 6 files changed, 245 insertions(+), 94 deletions(-) create mode 100644 Civi/Api4/Query/SqlEquation.php diff --git a/Civi/Api4/Query/SqlEquation.php b/Civi/Api4/Query/SqlEquation.php new file mode 100644 index 0000000000..7585ab0d08 --- /dev/null +++ b/Civi/Api4/Query/SqlEquation.php @@ -0,0 +1,109 @@ +=', + '<', + '>', + '=', + '!=', + '<=>', + 'IS NOT', + 'IS', + 'BETWEEN', + 'AND', + ]; + + protected function initialize() { + $arg = trim(substr($this->expr, strpos($this->expr, '(') + 1, -1)); + $permitted = ['SqlField', 'SqlString', 'SqlNumber', 'SqlNull']; + $operators = array_merge(self::$arithmeticOperators, self::$comparisonOperators); + while (strlen($arg)) { + $this->args = array_merge($this->args, $this->captureExpressions($arg, $permitted, FALSE)); + $op = $this->captureKeyword($operators, $arg); + if ($op) { + $this->args[] = $op; + } + } + } + + /** + * Render the expression for insertion into the sql query + * + * @param array $fieldList + * @return string + */ + public function render(array $fieldList): string { + $output = []; + foreach ($this->args as $arg) { + $output[] = is_string($arg) ? $arg : $arg->render($fieldList); + } + return '(' . implode(' ', $output) . ')'; + } + + /** + * Returns the alias to use for SELECT AS. + * + * @return string + */ + public function getAlias(): string { + return $this->alias ?? \CRM_Utils_String::munge(trim($this->expr, ' ()'), '_', 256); + } + + /** + * Change $dataType according to operator used in equation + * + * @see \Civi\Api4\Utils\FormattingUtil::formatOutputValues + * @param string $value + * @param string $dataType + * @return string + */ + public function formatOutputValue($value, &$dataType) { + foreach (self::$comparisonOperators as $op) { + if (strpos($this->expr, " $op ")) { + $dataType = 'Boolean'; + } + } + foreach (self::$arithmeticOperators as $op) { + if (strpos($this->expr, " $op ")) { + $dataType = 'Float'; + } + } + return $value; + } + +} diff --git a/Civi/Api4/Query/SqlExpression.php b/Civi/Api4/Query/SqlExpression.php index 338ae5b057..c052281315 100644 --- a/Civi/Api4/Query/SqlExpression.php +++ b/Civi/Api4/Query/SqlExpression.php @@ -83,8 +83,12 @@ abstract class SqlExpression { $bracketPos = strpos($expr, '('); $firstChar = substr($expr, 0, 1); $lastChar = substr($expr, -1); + // Statement surrounded by brackets is an equation + if ($firstChar === '(' && $lastChar === ')') { + $className = 'SqlEquation'; + } // If there are brackets but not the first character, we have a function - if ($bracketPos && $lastChar === ')') { + elseif ($bracketPos && $lastChar === ')') { $fnName = substr($expr, 0, $bracketPos); if ($fnName !== strtoupper($fnName)) { throw new \API_Exception('Sql function must be uppercase.'); @@ -174,4 +178,97 @@ abstract class SqlExpression { return static::$dataType; } + /** + * Shift a keyword off the beginning of the argument string and return it. + * + * @param array $keywords + * Whitelist of keywords + * @param string $arg + * @return mixed|null + */ + protected function captureKeyword($keywords, &$arg) { + foreach ($keywords as $key) { + if (strpos($arg, $key . ' ') === 0) { + $arg = ltrim(substr($arg, strlen($key))); + return $key; + } + } + return NULL; + } + + /** + * Shifts 0 or more expressions off the argument string and returns them + * + * @param string $arg + * @param array $mustBe + * @param bool $multi + * @return SqlExpression[] + * @throws \API_Exception + */ + protected function captureExpressions(string &$arg, array $mustBe, bool $multi) { + $captured = []; + $arg = ltrim($arg); + while ($arg) { + $item = $this->captureExpression($arg); + $arg = ltrim(substr($arg, strlen($item))); + $expr = self::convert($item, FALSE, $mustBe); + $this->fields = array_merge($this->fields, $expr->getFields()); + $captured[] = $expr; + // Keep going if we have a comma indicating another expression follows + if ($multi && substr($arg, 0, 1) === ',') { + $arg = ltrim(substr($arg, 1)); + } + else { + break; + } + } + return $captured; + } + + /** + * Scans the beginning of a string for an expression; stops when it hits delimiter + * + * @param $arg + * @return string + */ + protected function captureExpression($arg) { + $isEscaped = $quote = NULL; + $item = ''; + $quotes = ['"', "'"]; + $brackets = [ + ')' => '(', + ]; + $enclosures = array_fill_keys($brackets, 0); + foreach (str_split($arg) as $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; + } + } diff --git a/Civi/Api4/Query/SqlFunction.php b/Civi/Api4/Query/SqlFunction.php index 94bf31b85d..fd10f80cca 100644 --- a/Civi/Api4/Query/SqlFunction.php +++ b/Civi/Api4/Query/SqlFunction.php @@ -67,7 +67,7 @@ abstract class SqlFunction extends SqlExpression { 'suffix' => [], ]; if ($param['max_expr'] && (!$param['name'] || $param['name'] === $prefix)) { - $exprs = $this->captureExpressions($arg, $param['must_be']); + $exprs = $this->captureExpressions($arg, $param['must_be'], TRUE); if (count($exprs) < $param['min_expr'] || count($exprs) > $param['max_expr']) { throw new \API_Exception('Incorrect number of arguments for SQL function ' . static::getName()); } @@ -93,98 +93,6 @@ abstract class SqlFunction extends SqlExpression { return $value; } - /** - * Shift a keyword off the beginning of the argument string and return it. - * - * @param array $keywords - * Whitelist of keywords - * @param string $arg - * @return mixed|null - */ - private function captureKeyword($keywords, &$arg) { - foreach ($keywords as $key) { - if (strpos($arg, $key . ' ') === 0) { - $arg = ltrim(substr($arg, strlen($key))); - return $key; - } - } - return NULL; - } - - /** - * Shifts 0 or more expressions off the argument string and returns them - * - * @param string $arg - * @param array $mustBe - * @return array - * @throws \API_Exception - */ - private function captureExpressions(&$arg, $mustBe) { - $captured = []; - $arg = ltrim($arg); - while ($arg) { - $item = $this->captureExpression($arg); - $arg = ltrim(substr($arg, strlen($item))); - $expr = SqlExpression::convert($item, FALSE, $mustBe); - $this->fields = array_merge($this->fields, $expr->getFields()); - $captured[] = $expr; - // Keep going if we have a comma indicating another expression follows - if (substr($arg, 0, 1) === ',') { - $arg = ltrim(substr($arg, 1)); - } - else { - break; - } - } - return $captured; - } - - /** - * Scans the beginning of a string for an expression; stops when it hits delimiter - * - * @param $arg - * @return string - */ - private function captureExpression($arg) { - $isEscaped = $quote = NULL; - $item = ''; - $quotes = ['"', "'"]; - $brackets = [ - ')' => '(', - ]; - $enclosures = array_fill_keys($brackets, 0); - foreach (str_split($arg) as $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; - } - /** * Render the expression for insertion into the sql query * diff --git a/Civi/Api4/Query/SqlFunctionIF.php b/Civi/Api4/Query/SqlFunctionIF.php index 24e2faa65a..e9bda2705a 100644 --- a/Civi/Api4/Query/SqlFunctionIF.php +++ b/Civi/Api4/Query/SqlFunctionIF.php @@ -26,6 +26,7 @@ class SqlFunctionIF extends SqlFunction { 'min_expr' => 3, 'max_expr' => 3, 'optional' => FALSE, + 'must_be' => ['SqlEquation', 'SqlField', 'SqlFunction', 'SqlString', 'SqlNumber', 'SqlNull'], 'ui_defaults' => [ ['type' => 'SqlField', 'placeholder' => ts('If')], ['type' => 'SqlField', 'placeholder' => ts('Then')], diff --git a/tests/phpunit/api/v4/Action/ContactApiKeyTest.php b/tests/phpunit/api/v4/Action/ContactApiKeyTest.php index 57d6b0ab85..de323c8c54 100644 --- a/tests/phpunit/api/v4/Action/ContactApiKeyTest.php +++ b/tests/phpunit/api/v4/Action/ContactApiKeyTest.php @@ -50,17 +50,21 @@ class ContactApiKeyTest extends \api\v4\UnitTestCase { $result = Contact::get() ->addWhere('id', '=', $contact['id']) ->addSelect('api_key') + ->addSelect('IF((api_key IS NULL), "yes", "no") AS is_api_key_null') ->execute() ->first(); $this->assertEquals($key, $result['api_key']); + $this->assertEquals('no', $result['is_api_key_null']); $this->assertFalse($isSafe($result), "Should reveal secret details ($key): " . var_export($result, 1)); // Can also be fetched via join $email = Email::get() ->addSelect('contact_id.api_key') + ->addSelect('IF((contact_id.api_key IS NULL), "yes", "no") AS is_api_key_null') ->addWhere('id', '=', $contact['email']['id']) ->execute()->first(); $this->assertEquals($key, $email['contact_id.api_key']); + $this->assertEquals('no', $result['is_api_key_null']); $this->assertFalse($isSafe($email), "Should reveal secret details ($key): " . var_export($email, 1)); // Remove permission and we should not see the key @@ -68,20 +72,24 @@ class ContactApiKeyTest extends \api\v4\UnitTestCase { $result = Contact::get() ->addWhere('id', '=', $contact['id']) ->addSelect('api_key') + ->addSelect('IF((api_key IS NULL), "yes", "no") AS is_api_key_null') ->setDebug(TRUE) ->execute(); $this->assertContains('api_key', $result->debug['unauthorized_fields']); $this->assertArrayNotHasKey('api_key', $result[0]); + $this->assertArrayNotHasKey('is_api_key_null', $result[0]); $this->assertTrue($isSafe($result[0]), "Should NOT reveal secret details ($key): " . var_export($result[0], 1)); // Also not available via join $email = Email::get() ->addSelect('contact_id.api_key') + ->addSelect('IF((contact_id.api_key IS NULL), "yes", "no") AS is_api_key_null') ->addWhere('id', '=', $contact['email']['id']) ->setDebug(TRUE) ->execute(); $this->assertContains('contact_id.api_key', $email->debug['unauthorized_fields']); $this->assertArrayNotHasKey('contact_id.api_key', $email[0]); + $this->assertArrayNotHasKey('is_api_key_null', $result[0]); $this->assertTrue($isSafe($email[0]), "Should NOT reveal secret details ($key): " . var_export($email[0], 1)); $result = Contact::get() diff --git a/tests/phpunit/api/v4/Action/SqlExpressionTest.php b/tests/phpunit/api/v4/Action/SqlExpressionTest.php index 1de05f18e2..b5fb0a15f6 100644 --- a/tests/phpunit/api/v4/Action/SqlExpressionTest.php +++ b/tests/phpunit/api/v4/Action/SqlExpressionTest.php @@ -21,6 +21,7 @@ namespace api\v4\Action; use api\v4\UnitTestCase; use Civi\Api4\Contact; +use Civi\Api4\Email; /** * @group headless @@ -95,4 +96,31 @@ class SqlExpressionTest extends UnitTestCase { ->execute(); } + public function testSelectEquations() { + $contact = Contact::create(FALSE)->addValue('first_name', 'bob') + ->addChain('email', Email::create()->setValues(['email' => 'hello@example.com', 'contact_id' => '$id'])) + ->execute()->first(); + $result = Email::get(FALSE) + ->setSelect([ + 'IF((contact_id.first_name = "bob"), "Yes", "No") AS is_bob', + 'IF((contact_id.first_name != "fred"), "No", "Yes") AS is_fred', + '(5 * 11)', + '(5 > 11) AS five_greater_eleven', + '(5 <= 11) AS five_less_eleven', + '(1 BETWEEN 0 AND contact_id) AS is_between', + '(illegal * stuff) AS illegal_stuff', + ]) + ->addWhere('contact_id', '=', $contact['id']) + ->setLimit(1) + ->execute() + ->first(); + $this->assertEquals('Yes', $result['is_bob']); + $this->assertEquals('No', $result['is_fred']); + $this->assertEquals('55', $result['5_11']); + $this->assertFalse($result['five_greater_eleven']); + $this->assertTrue($result['five_less_eleven']); + $this->assertTrue($result['is_between']); + $this->assertArrayNotHasKey('illegal_stuff', $result); + } + } -- 2.25.1