tests/phpunit/api/v4/Action/SqlExpressionTest.php

   1 <?php
   2
   3 /*
   4  +--------------------------------------------------------------------+
   5  | Copyright CiviCRM LLC. All rights reserved.                        |
   6  |                                                                    |
   7  | This work is published under the GNU AGPLv3 license with some      |
   8  | permitted exceptions and without any warranty. For full license    |
   9  | and copyright information, see https://civicrm.org/licensing       |
  10  +--------------------------------------------------------------------+
  11  */
  12
  13 /**
  14  *
  15  * @package CRM
  16  * @copyright CiviCRM LLC https://civicrm.org/licensing
  17  */
  18
  19
  20 namespace api\v4\Action;
  21
  22 use api\v4\UnitTestCase;
  23 use Civi\Api4\Contact;
  24 use Civi\Api4\Email;
  25
  26 /**
  27  * @group headless
  28  */
  29 class SqlExpressionTest extends UnitTestCase {
  30
  31   public function testSelectNull() {
  32     Contact::create()->addValue('first_name', 'bob')->setCheckPermissions(FALSE)->execute();
  33     $result = Contact::get()
  34       ->addSelect('NULL AS nothing', 'NULL', 'NULL AS b*d char', 'first_name')
  35       ->addWhere('first_name', '=', 'bob')
  36       ->setLimit(1)
  37       ->execute()
  38       ->first();
  39     $this->assertNull($result['nothing']);
  40     $this->assertNull($result['NULL']);
  41     $this->assertNull($result['b_d_char']);
  42     $this->assertEquals('bob', $result['first_name']);
  43     $this->assertArrayNotHasKey('b*d char', $result);
  44   }
  45
  46   public function testSelectNumbers() {
  47     Contact::create()->addValue('first_name', 'bob')->setCheckPermissions(FALSE)->execute();
  48     $result = Contact::get()
  49       ->addSelect('first_name', 123, 45.678, '-55 AS neg')
  50       ->addWhere('first_name', '=', 'bob')
  51       ->setLimit(1)
  52       ->execute()
  53       ->first();
  54     $this->assertEquals('bob', $result['first_name']);
  55     $this->assertEquals('123', $result['123']);
  56     $this->assertEquals('-55', $result['neg']);
  57     $this->assertEquals('45.678', $result['45_678']);
  58   }
  59
  60   public function testSelectStrings() {
  61     Contact::create()->addValue('first_name', 'bob')->setCheckPermissions(FALSE)->execute();
  62     $result = Contact::get()
  63       ->addSelect('first_name')
  64       ->addSelect('"hello world" AS hi')
  65       ->addSelect('"can\'t \"quote\"" AS quot')
  66       ->addWhere('first_name', '=', 'bob')
  67       ->setLimit(1)
  68       ->execute()
  69       ->first();
  70     $this->assertEquals('bob', $result['first_name']);
  71     $this->assertEquals('hello world', $result['hi']);
  72     $this->assertEquals('can\'t "quote"', $result['quot']);
  73   }
  74
  75   public function testSelectAlias() {
  76     try {
  77       Contact::get()
  78         ->addSelect('first_name AS bob')
  79         ->execute();
  80     }
  81     catch (\API_Exception $e) {
  82       $msg = $e->getMessage();
  83     }
  84     $this->assertStringContainsString('alias', $msg);
  85     try {
  86       Contact::get()
  87         ->addSelect('55 AS sort_name')
  88         ->execute();
  89     }
  90     catch (\API_Exception $e) {
  91       $msg = $e->getMessage();
  92     }
  93     $this->assertStringContainsString('existing field name', $msg);
  94     Contact::get()
  95       ->addSelect('55 AS ok_alias')
  96       ->execute();
  97   }
  98
  99   public function testSelectEquations() {
 100     $contact = Contact::create(FALSE)->addValue('first_name', 'bob')
 101       ->addChain('email', Email::create()->setValues(['email' => 'hello@example.com', 'contact_id' => '$id']))
 102       ->execute()->first();
 103     $result = Email::get(FALSE)
 104       ->setSelect([
 105         'IF((contact_id.first_name = "bob"), "Yes", "No") AS is_bob',
 106         'IF((contact_id.first_name != "fred"), "No", "Yes") AS is_fred',
 107         '(5 * 11)',
 108         '(5 > 11) AS five_greater_eleven',
 109         '(5 <= 11) AS five_less_eleven',
 110         '(1 BETWEEN 0 AND contact_id) AS is_between',
 111         // These fields don't exist
 112         '(illegal * stuff) AS illegal_stuff',
 113         // This field will be null
 114         '(hold_date + 5) AS null_plus_five',
 115       ])
 116       ->addWhere('contact_id', '=', $contact['id'])
 117       ->setLimit(1)
 118       ->execute()
 119       ->first();
 120     $this->assertEquals('Yes', $result['is_bob']);
 121     $this->assertEquals('No', $result['is_fred']);
 122     $this->assertEquals('55', $result['5_11']);
 123     $this->assertFalse($result['five_greater_eleven']);
 124     $this->assertTrue($result['five_less_eleven']);
 125     $this->assertTrue($result['is_between']);
 126     $this->assertArrayNotHasKey('illegal_stuff', $result);
 127     $this->assertEquals('5', $result['null_plus_five']);
 128   }
 129
 130 }