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