tests/phpunit/CRM/Utils/SQL/SelectTest.php

   1 <?php
   2
   3 /**
   4  * Class CRM_Utils_SQL_SelectTest
   5  * @group headless
   6  */
   7 class CRM_Utils_SQL_SelectTest extends CiviUnitTestCase {
   8   public function testGetDefault() {
   9     $select = CRM_Utils_SQL_Select::from('foo bar');
  10     $this->assertLike('SELECT * FROM foo bar', $select->toSQL());
  11   }
  12
  13   public function testExecute_OK_fetch() {
  14     $select = CRM_Utils_SQL_Select::from('civicrm_contact')->select('count(*) as cnt');
  15     $this->assertLike('SELECT count(*) as cnt FROM civicrm_contact', $select->toSQL());
  16
  17     $select = CRM_Utils_SQL_Select::from('civicrm_contact')
  18       ->select('count(*) as cnt');
  19     $rows = 0;
  20     $dao = $select->execute();
  21     while ($dao->fetch()) {
  22       $rows++;
  23       $this->assertTrue(is_numeric($dao->cnt), "Expect query to execute");
  24     }
  25     $this->assertEquals(1, $rows);
  26   }
  27
  28   public function testExecute_OK_fetchValue() {
  29     $select = CRM_Utils_SQL_Select::from('civicrm_contact')->select('count(*) as cnt');
  30     $this->assertLike('SELECT count(*) as cnt FROM civicrm_contact', $select->toSQL());
  31     $this->assertTrue(is_numeric($select->execute()->fetchValue()));
  32   }
  33
  34   public function testExecute_OK_fetchAll() {
  35     $select = CRM_Utils_SQL_Select::from('civicrm_contact')->select('count(*) as cnt');
  36     $this->assertLike('SELECT count(*) as cnt FROM civicrm_contact', $select->toSQL());
  37     $records = $select->execute()->fetchAll();
  38     $this->assertTrue(is_numeric($records[0]['cnt']));
  39   }
  40
  41   public function testExecute_Error() {
  42     $select = CRM_Utils_SQL_Select::from('civicrm_contact')->select('snarb;barg');
  43
  44     try {
  45       $select->execute();
  46       $this->fail('Expected an exception');
  47     }
  48     catch (PEAR_Exception $e) {
  49       $this->assertTrue(TRUE, "Received expected exception");
  50     }
  51   }
  52
  53   public function testGetFields() {
  54     $select = CRM_Utils_SQL_Select::from('foo')
  55       ->select('bar')
  56       ->select(array('whiz', 'bang'));
  57     $this->assertLike('SELECT bar, whiz, bang FROM foo', $select->toSQL());
  58   }
  59
  60   public function testWherePlain() {
  61     $select = CRM_Utils_SQL_Select::from('foo')
  62       ->where('foo = bar')
  63       ->where(array('whiz = bang', 'frob > nicate'));
  64     $this->assertLike('SELECT * FROM foo WHERE (foo = bar) AND (whiz = bang) AND (frob > nicate)', $select->toSQL());
  65   }
  66
  67   public function testWhereArg() {
  68     $select = CRM_Utils_SQL_Select::from('foo')
  69       ->where('foo = @value', array('@value' => 'not"valid'))
  70       ->where(array('whiz > @base', 'frob != @base'), array('@base' => 'in"valid'));
  71     $this->assertLike('SELECT * FROM foo WHERE (foo = "not\\"valid") AND (whiz > "in\\"valid") AND (frob != "in\\"valid")', $select->toSQL());
  72   }
  73
  74   public function testGroupByPlain() {
  75     $select = CRM_Utils_SQL_Select::from('foo')
  76       ->groupBy("bar_id")
  77       ->groupBy(array('whiz_id*2', 'lower(bang)'));
  78     $this->assertLike('SELECT * FROM foo GROUP BY bar_id, whiz_id*2, lower(bang)', $select->toSQL());
  79   }
  80
  81   public function testHavingPlain() {
  82     $select = CRM_Utils_SQL_Select::from('foo')
  83       ->groupBy("bar_id")
  84       ->having('count(id) > 2')
  85       ->having(array('sum(id) > 10', 'avg(id) < 200'));
  86     $this->assertLike('SELECT * FROM foo GROUP BY bar_id HAVING (count(id) > 2) AND (sum(id) > 10) AND (avg(id) < 200)', $select->toSQL());
  87   }
  88
  89   public function testHavingArg() {
  90     $select = CRM_Utils_SQL_Select::from('foo')
  91       ->groupBy("bar_id")
  92       ->having('count(id) > #mincnt', array('#mincnt' => 2))
  93       ->having(array('sum(id) > #whiz', 'avg(id) < #whiz'), array('#whiz' => 10));
  94     $this->assertLike('SELECT * FROM foo GROUP BY bar_id HAVING (count(id) > 2) AND (sum(id) > 10) AND (avg(id) < 10)', $select->toSQL());
  95   }
  96
  97   public function testOrderByPlain() {
  98     $select = CRM_Utils_SQL_Select::from('foo bar')
  99       ->orderBy('first asc')
 100       ->orderBy(array('second desc', 'third'));
 101     $this->assertLike('SELECT * FROM foo bar ORDER BY first asc, second desc, third', $select->toSQL());
 102   }
 103
 104   public function testLimit_defaultOffset() {
 105     $select = CRM_Utils_SQL_Select::from('foo bar')
 106       ->limit(20);
 107     $this->assertLike('SELECT * FROM foo bar LIMIT 20 OFFSET 0', $select->toSQL());
 108   }
 109
 110   public function testLimit_withOffset() {
 111     $select = CRM_Utils_SQL_Select::from('foo bar')
 112       ->limit(20, 60);
 113     $this->assertLike('SELECT * FROM foo bar LIMIT 20 OFFSET 60', $select->toSQL());
 114   }
 115
 116   public function testLimit_disable() {
 117     $select = CRM_Utils_SQL_Select::from('foo bar')
 118       ->limit(20, 60)
 119       ->limit(NULL, NULL);
 120     $this->assertLike('SELECT * FROM foo bar', $select->toSQL());
 121   }
 122
 123   public function testModeOutput() {
 124     $select = CRM_Utils_SQL_Select::from('foo', array('mode' => 'out'))
 125       ->where('foo = @value')
 126       ->where(array(
 127         'whiz > @base',
 128         'frob != @base',
 129       ))
 130       ->param('@value', 'not"valid')
 131       ->param(array(
 132         '@base' => 'in"valid',
 133       ));
 134     $this->assertLike('SELECT * FROM foo WHERE (foo = "not\\"valid") AND (whiz > "in\\"valid") AND (frob != "in\\"valid")', $select->toSQL());
 135
 136     try {
 137       CRM_Utils_SQL_Select::from('foo', array('mode' => 'out'))
 138         ->where('foo = @value', array('@value' => 'not"valid'));
 139       $this->fail('In output mode, we should reject requests to interpolate inputs.');
 140     }
 141     catch (Exception $e) {
 142       $this->assertRegExp("/Cannot mix interpolation modes/", $e->getMessage());
 143     }
 144
 145     $outputModeFragment = CRM_Utils_SQL_Select::fragment()
 146       ->param('value', 'whatever');
 147     $inputModeFragment = CRM_Utils_SQL_Select::fragment()
 148       ->where('foo = @value', array('@value' => 'not"valid'));
 149     try {
 150       $outputModeFragment->merge($inputModeFragment);
 151       $this->fail('In output-mode, we should reject requests to merge from input-mode.');
 152     }
 153     catch (Exception $e) {
 154       $this->assertRegExp("/Cannot merge queries that use different interpolation modes/", $e->getMessage());
 155     }
 156   }
 157
 158   public function testBig() {
 159     $select = CRM_Utils_SQL_Select::from('foo')
 160       ->select('foo.id')
 161       ->join('rel1', 'INNER JOIN rel1_table rel1 ON foo.id = rel1.foo_id')
 162       ->join('rel2', 'LEFT JOIN rel2_table rel2 ON foo.id = rel2.foo_id')
 163       ->where('foo.type = @theType', array('@theType' => 'mytype'))
 164       ->groupBy("foo.id")
 165       ->having('sum(rel1.stat) > 10')
 166       ->orderBy('rel2.whiz')
 167       ->limit(100, 300);
 168     $this->assertLike(
 169       "SELECT foo.id FROM foo"
 170       . " INNER JOIN rel1_table rel1 ON foo.id = rel1.foo_id"
 171       . " LEFT JOIN rel2_table rel2 ON foo.id = rel2.foo_id "
 172       . " WHERE (foo.type = \"mytype\")"
 173       . " GROUP BY foo.id"
 174       . " HAVING (sum(rel1.stat) > 10)"
 175       . " ORDER BY rel2.whiz"
 176       . " LIMIT 100 OFFSET 300",
 177       $select->toSQL()
 178     );
 179   }
 180
 181   /**
 182    * Parameter-values could include control characters like
 183    * '"@" or "!", but they should never be evaluated.
 184    */
 185   public function testNoIterativeInterpolation() {
 186     $select = CRM_Utils_SQL_Select::from('foo')
 187       ->where('a = @a and b = @b and c = @c', array(
 188         'a' => '@b',
 189         'b' => '@c',
 190         'c' => '@a',
 191       ));
 192     $this->assertLike('SELECT * FROM foo WHERE (a = "@b" and b = "@c" and c = "@a")', $select->toSQL());
 193   }
 194
 195   public function testInterpolate() {
 196     $actual = CRM_Utils_SQL_Select::from('ignore')->interpolate(
 197       '@escaped !unescaped #validated',
 198       array(
 199         '@escaped' => 'foo"bar',
 200         '!unescaped' => 'concat(foo,bar)',
 201         '#validated' => 15.2,
 202       )
 203     );
 204     $this->assertLike('"foo\"bar" concat(foo,bar) 15.2', $actual);
 205   }
 206
 207   public function testInterpolateWildcard() {
 208     $actual = CRM_Utils_SQL_Select::from('ignore')->interpolate(
 209       'escaped @escaped unescaped !unescaped validated #validated',
 210       array(
 211         'escaped' => 'foo"bar',
 212         'unescaped' => 'concat(foo,bar)',
 213         'validated' => 15.2,
 214       )
 215     );
 216     $this->assertLike('escaped "foo\"bar" unescaped concat(foo,bar) validated 15.2', $actual);
 217   }
 218
 219   public function testInterpolateUnknown() {
 220     $actual = CRM_Utils_SQL_Select::from('ignore')->interpolate(
 221       'escaped @escaped unescaped !unescaped validated #validated',
 222       array(
 223         'irrelevant' => 'foo',
 224       )
 225     );
 226     $this->assertLike('escaped @escaped unescaped !unescaped validated #validated', $actual);
 227   }
 228
 229   public function testInterpolateUnknownStrict() {
 230     try {
 231       CRM_Utils_SQL_Select::from('ignore')
 232         ->strict()
 233         ->interpolate('@johnMcClane',
 234           array(
 235             'irrelevant' => 'foo',
 236           )
 237         );
 238       $this->fail('Unknown variables should throw errors in strict mode.');
 239     }
 240     catch (Exception $e) {
 241       $this->assertRegExp('/Cannot build query. Variable "@johnMcClane" is unknown./', $e->getMessage());
 242     }
 243   }
 244
 245   public function testInterpolateArray() {
 246     $actual = CRM_Utils_SQL_Select::from('ignore')->interpolate(
 247       '(@escaped) (!unescaped) (#validated)',
 248       array(
 249         '@escaped' => array('foo"bar', "whiz", "null", NULL, "bang"),
 250         '!unescaped' => array('foo"bar', 'bar'),
 251         '#validated' => array(1, 10, NULL, 100.1),
 252       )
 253     );
 254     $this->assertLike('("foo\\"bar", "whiz", "null", NULL, "bang") (foo"bar, bar) (1, 10, NULL, 100.1)', $actual);
 255   }
 256
 257   public function testInterpolateBadNumber() {
 258     try {
 259       $result = CRM_Utils_SQL_Select::from('ignore')->interpolate('#num', array(
 260         '#num' => '5not-a-number5',
 261       ));
 262       $this->fail('Expected exception; got: ' . var_export($result, TRUE));
 263     }
 264     catch (CRM_Core_Exception $e) {
 265       $this->assertTrue(TRUE, "Caught expected exception");
 266     }
 267
 268     try {
 269       $result = CRM_Utils_SQL_Select::from('ignore')->interpolate('#num', array(
 270         '#num' => array(1, '5not-a-number5', 2),
 271       ));
 272       $this->fail('Expected exception; got: ' . var_export($result, TRUE));
 273     }
 274     catch (CRM_Core_Exception $e) {
 275       $this->assertTrue(TRUE, "Caught expected exception");
 276     }
 277   }
 278
 279   public function testMerge() {
 280     $fragmentOutMode = CRM_Utils_SQL_Select::fragment()
 281       ->select(array('a', 'b'))
 282       ->where('a = #two')
 283       ->param('two', 2);
 284     $fragmentAutoMode = CRM_Utils_SQL_Select::fragment()
 285       ->select('e')
 286       ->where('whipit()');
 287     $query = CRM_Utils_SQL_Select::from('foo')
 288       ->select(array('c', 'd'))
 289       ->where('c = @four')
 290       ->param('four', 4)
 291       ->merge($fragmentOutMode)
 292       ->merge($fragmentAutoMode);
 293     $this->assertLike('SELECT c, d, a, b, e FROM foo WHERE (c = "4") AND (a = 2) AND (whipit())', $query->toSQL());
 294   }
 295
 296   public function testArrayGet() {
 297     $select = CRM_Utils_SQL_Select::from("foo")
 298       ->param('hello', 'world');
 299     $this->assertEquals('world', $select['hello']);
 300   }
 301
 302   public function testInsertInto_WithDupes() {
 303     $select = CRM_Utils_SQL_Select::from('foo')
 304       ->insertInto('bar', array('first', 'second', 'third', 'fourth'))
 305       ->select('fid')
 306       ->select('1')
 307       ->select('fid')
 308       ->select('1')
 309       ->where('!field = #value', array('field' => 'zoo', 'value' => 3))
 310       ->where('!field = #value', array('field' => 'aviary', 'value' => 3))
 311       ->where('!field = #value', array('field' => 'zoo', 'value' => 3))
 312       ->groupBy('!colName', array('colName' => 'noodle'))
 313       ->groupBy('!colName', array('colName' => 'sauce'))
 314       ->groupBy('!colName', array('colName' => 'noodle'));
 315     $this->assertLike('INSERT INTO bar (first, second, third, fourth) SELECT fid, 1, fid, 1 FROM foo WHERE (zoo = 3) AND (aviary = 3) GROUP BY noodle, sauce', $select->toSQL());
 316   }
 317
 318   /**
 319    * @param $expected
 320    * @param $actual
 321    * @param string $message
 322    */
 323   public function assertLike($expected, $actual, $message = '') {
 324     $expected = trim((preg_replace('/[ \r\n\t]+/', ' ', $expected)));
 325     $actual = trim((preg_replace('/[ \r\n\t]+/', ' ', $actual)));
 326     $this->assertEquals($expected, $actual, $message);
 327   }
 328
 329 }