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

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