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

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