| 1 | <?php |
| 2 | |
| 3 | /** |
| 4 | * Dear God Why Do I Have To Write This (Dumb SQL Builder) |
| 5 | * |
| 6 | * Usage: |
| 7 | * @code |
| 8 | * $select = CRM_Utils_SQL_Select::from('civicrm_activity act') |
| 9 | * ->join('absence', 'inner join civicrm_activity absence on absence.id = act.source_record_id') |
| 10 | * ->where('activity_type_id = #type', array('type' => 234)) |
| 11 | * ->where('status_id IN (#statuses)', array('statuses' => array(1,2,3)) |
| 12 | * ->where('subject like @subj', array('subj' => '%hello%')) |
| 13 | * ->where('!dynamicColumn = 1', array('dynamicColumn' => 'coalesce(is_active,0)')) |
| 14 | * ->where('!column = @value', array( |
| 15 | * 'column' => $customField->column_name, |
| 16 | * 'value' => $form['foo'] |
| 17 | * )) |
| 18 | * echo $select->toSQL(); |
| 19 | * @endcode |
| 20 | * |
| 21 | * Design principles: |
| 22 | * - Portable |
| 23 | * - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString) |
| 24 | * - No knowledge of the underlying data model |
| 25 | * - Single file |
| 26 | * - SQL clauses correspond to PHP functions ($select->where("foo_id=123")) |
| 27 | * - Variable escaping is concise and controllable based on prefixes, eg |
| 28 | * - similar to Drupal's t() |
| 29 | * - use "@varname" to insert the escaped value |
| 30 | * - use "!varname" to insert raw (unescaped) values |
| 31 | * - use "#varname" to insert a numerical value (these are validated but not escaped) |
| 32 | * - to disable any preprocessing, simply omit the variable list |
| 33 | * - control characters (@!#) are mandatory in expressions but optional in arg-keys |
| 34 | * - Variables may be individual values or arrays; arrays are imploded with commas |
| 35 | * - Conditionals are AND'd; if you need OR's, do it yourself |
| 36 | * - Use classes/functions with documentation (rather than undocumented array-trees) |
| 37 | * - For any given string, interpolation is only performed once. After an interpolation, |
| 38 | * a string may never again be subjected to interpolation. |
| 39 | * |
| 40 | * The "interpolate-once" principle can be enforced by either interpolating on input |
| 41 | * xor output. The notations for input and output interpolation are a bit different, |
| 42 | * and they may not be mixed. |
| 43 | * |
| 44 | * @code |
| 45 | * // Interpolate on input. Set params when using them. |
| 46 | * $select->where('activity_type_id = #type', array( |
| 47 | * 'type' => 234, |
| 48 | * )); |
| 49 | * |
| 50 | * // Interpolate on output. Set params independently. |
| 51 | * $select |
| 52 | * ->where('activity_type_id = #type') |
| 53 | * ->param('type', 234), |
| 54 | * @endcode |
| 55 | */ |
| 56 | class CRM_Utils_SQL_Select implements ArrayAccess { |
| 57 | |
| 58 | /** |
| 59 | * Interpolate values as soon as they are passed in (where(), join(), etc). |
| 60 | * |
| 61 | * Default. |
| 62 | * |
| 63 | * Pro: Every clause has its own unique namespace for parameters. |
| 64 | * Con: Probably slower. |
| 65 | * Advice: Use this when aggregating SQL fragments from agents who |
| 66 | * maintained by different parties. |
| 67 | */ |
| 68 | const INTERPOLATE_INPUT = 'in'; |
| 69 | |
| 70 | /** |
| 71 | * Interpolate values when rendering SQL output (toSQL()). |
| 72 | * |
| 73 | * Pro: Probably faster. |
| 74 | * Con: Must maintain an aggregated list of all parameters. |
| 75 | * Advice: Use this when you have control over the entire query. |
| 76 | */ |
| 77 | const INTERPOLATE_OUTPUT = 'out'; |
| 78 | |
| 79 | /** |
| 80 | * Determine mode automatically. When the first attempt is made |
| 81 | * to use input-interpolation (eg `where(..., array(...))`) or |
| 82 | * output-interpolation (eg `param(...)`), the mode will be |
| 83 | * set. Subsequent calls will be validated using the same mode. |
| 84 | */ |
| 85 | const INTERPOLATE_AUTO = 'auto'; |
| 86 | |
| 87 | private $mode = NULL; |
| 88 | private $insertInto = NULL; |
| 89 | private $insertIntoFields = array(); |
| 90 | private $selects = array(); |
| 91 | private $from; |
| 92 | private $joins = array(); |
| 93 | private $wheres = array(); |
| 94 | private $groupBys = array(); |
| 95 | private $havings = array(); |
| 96 | private $orderBys = array(); |
| 97 | private $limit = NULL; |
| 98 | private $offset = NULL; |
| 99 | private $params = array(); |
| 100 | |
| 101 | // Public to work-around PHP 5.3 limit. |
| 102 | public $strict = NULL; |
| 103 | |
| 104 | /** |
| 105 | * Create a new SELECT query. |
| 106 | * |
| 107 | * @param string $from |
| 108 | * Table-name and optional alias. |
| 109 | * @param array $options |
| 110 | * @return CRM_Utils_SQL_Select |
| 111 | */ |
| 112 | public static function from($from, $options = array()) { |
| 113 | return new self($from, $options); |
| 114 | } |
| 115 | |
| 116 | /** |
| 117 | * Create a partial SELECT query. |
| 118 | * |
| 119 | * @param array $options |
| 120 | * @return CRM_Utils_SQL_Select |
| 121 | */ |
| 122 | public static function fragment($options = array()) { |
| 123 | return new self(NULL, $options); |
| 124 | } |
| 125 | |
| 126 | /** |
| 127 | * Create a new SELECT query. |
| 128 | * |
| 129 | * @param string $from |
| 130 | * Table-name and optional alias. |
| 131 | * @param array $options |
| 132 | */ |
| 133 | public function __construct($from, $options = array()) { |
| 134 | $this->from = $from; |
| 135 | $this->mode = isset($options['mode']) ? $options['mode'] : self::INTERPOLATE_AUTO; |
| 136 | } |
| 137 | |
| 138 | /** |
| 139 | * Make a new copy of this query. |
| 140 | * |
| 141 | * @return CRM_Utils_SQL_Select |
| 142 | */ |
| 143 | public function copy() { |
| 144 | return clone $this; |
| 145 | } |
| 146 | |
| 147 | /** |
| 148 | * @param CRM_Utils_SQL_Select $other |
| 149 | * @param array|NULL $parts |
| 150 | * ex: 'joins', 'wheres' |
| 151 | * @return $this |
| 152 | */ |
| 153 | public function merge($other, $parts = NULL) { |
| 154 | if ($other === NULL) { |
| 155 | return $this; |
| 156 | } |
| 157 | |
| 158 | if ($this->mode === self::INTERPOLATE_AUTO) { |
| 159 | $this->mode = $other->mode; |
| 160 | } |
| 161 | elseif ($other->mode === self::INTERPOLATE_AUTO) { |
| 162 | // Noop. |
| 163 | } |
| 164 | elseif ($this->mode !== $other->mode) { |
| 165 | // Mixing modes will lead to someone getting an expected substitution. |
| 166 | throw new RuntimeException("Cannot merge queries that use different interpolation modes ({$this->mode} vs {$other->mode})."); |
| 167 | } |
| 168 | |
| 169 | $arrayFields = array('insertIntoFields', 'selects', 'joins', 'wheres', 'groupBys', 'havings', 'orderBys', 'params'); |
| 170 | foreach ($arrayFields as $f) { |
| 171 | if ($parts === NULL || in_array($f, $parts)) { |
| 172 | $this->{$f} = array_merge($this->{$f}, $other->{$f}); |
| 173 | } |
| 174 | } |
| 175 | |
| 176 | $flatFields = array('insertInto', 'from', 'limit', 'offset'); |
| 177 | foreach ($flatFields as $f) { |
| 178 | if ($parts === NULL || in_array($f, $parts)) { |
| 179 | if ($other->{$f} !== NULL) { |
| 180 | $this->{$f} = $other->{$f}; |
| 181 | } |
| 182 | } |
| 183 | } |
| 184 | |
| 185 | return $this; |
| 186 | } |
| 187 | |
| 188 | /** |
| 189 | * Add a new JOIN clause. |
| 190 | * |
| 191 | * Note: To add multiple JOINs at once, use $name===NULL and |
| 192 | * pass an array of $exprs. |
| 193 | * |
| 194 | * @param string|NULL $name |
| 195 | * The effective alias of the joined table. |
| 196 | * @param string|array $exprs |
| 197 | * The complete join expression (eg "INNER JOIN mytable myalias ON mytable.id = maintable.foo_id"). |
| 198 | * @param array|null $args |
| 199 | * @return CRM_Utils_SQL_Select |
| 200 | */ |
| 201 | public function join($name, $exprs, $args = NULL) { |
| 202 | if ($name !== NULL) { |
| 203 | $this->joins[$name] = $this->interpolate($exprs, $args); |
| 204 | } |
| 205 | else { |
| 206 | foreach ($exprs as $name => $expr) { |
| 207 | $this->joins[$name] = $this->interpolate($expr, $args); |
| 208 | } |
| 209 | return $this; |
| 210 | } |
| 211 | return $this; |
| 212 | } |
| 213 | |
| 214 | /** |
| 215 | * Specify the column(s)/value(s) to return by adding to the SELECT clause |
| 216 | * |
| 217 | * @param string|array $exprs list of SQL expressions |
| 218 | * @param null|array $args use NULL to disable interpolation; use an array of variables to enable |
| 219 | * @return CRM_Utils_SQL_Select |
| 220 | */ |
| 221 | public function select($exprs, $args = NULL) { |
| 222 | $exprs = (array) $exprs; |
| 223 | foreach ($exprs as $expr) { |
| 224 | $this->selects[$expr] = $this->interpolate($expr, $args); |
| 225 | } |
| 226 | return $this; |
| 227 | } |
| 228 | |
| 229 | /** |
| 230 | * Limit results by adding extra condition(s) to the WHERE clause |
| 231 | * |
| 232 | * @param string|array $exprs list of SQL expressions |
| 233 | * @param null|array $args use NULL to disable interpolation; use an array of variables to enable |
| 234 | * @return CRM_Utils_SQL_Select |
| 235 | */ |
| 236 | public function where($exprs, $args = NULL) { |
| 237 | $exprs = (array) $exprs; |
| 238 | foreach ($exprs as $expr) { |
| 239 | $this->wheres[$expr] = $this->interpolate($expr, $args); |
| 240 | } |
| 241 | return $this; |
| 242 | } |
| 243 | |
| 244 | /** |
| 245 | * Group results by adding extra items to the GROUP BY clause. |
| 246 | * |
| 247 | * @param string|array $exprs list of SQL expressions |
| 248 | * @param null|array $args use NULL to disable interpolation; use an array of variables to enable |
| 249 | * @return CRM_Utils_SQL_Select |
| 250 | */ |
| 251 | public function groupBy($exprs, $args = NULL) { |
| 252 | $exprs = (array) $exprs; |
| 253 | foreach ($exprs as $expr) { |
| 254 | $this->groupBys[$expr] = $this->interpolate($expr, $args); |
| 255 | } |
| 256 | return $this; |
| 257 | } |
| 258 | |
| 259 | /** |
| 260 | * Limit results by adding extra condition(s) to the HAVING clause |
| 261 | * |
| 262 | * @param string|array $exprs list of SQL expressions |
| 263 | * @param null|array $args use NULL to disable interpolation; use an array of variables to enable |
| 264 | * @return CRM_Utils_SQL_Select |
| 265 | */ |
| 266 | public function having($exprs, $args = NULL) { |
| 267 | $exprs = (array) $exprs; |
| 268 | foreach ($exprs as $expr) { |
| 269 | $this->havings[$expr] = $this->interpolate($expr, $args); |
| 270 | } |
| 271 | return $this; |
| 272 | } |
| 273 | |
| 274 | /** |
| 275 | * Sort results by adding extra items to the ORDER BY clause. |
| 276 | * |
| 277 | * @param string|array $exprs list of SQL expressions |
| 278 | * @param null|array $args use NULL to disable interpolation; use an array of variables to enable |
| 279 | * @return CRM_Utils_SQL_Select |
| 280 | */ |
| 281 | public function orderBy($exprs, $args = NULL) { |
| 282 | $exprs = (array) $exprs; |
| 283 | foreach ($exprs as $expr) { |
| 284 | $this->orderBys[$expr] = $this->interpolate($expr, $args); |
| 285 | } |
| 286 | return $this; |
| 287 | } |
| 288 | |
| 289 | /** |
| 290 | * Set one (or multiple) parameters to interpolate into the query. |
| 291 | * |
| 292 | * @param array|string $keys |
| 293 | * Key name, or an array of key-value pairs. |
| 294 | * @param null|mixed $value |
| 295 | * @return $this |
| 296 | */ |
| 297 | public function param($keys, $value = NULL) { |
| 298 | if ($this->mode === self::INTERPOLATE_AUTO) { |
| 299 | $this->mode = self::INTERPOLATE_OUTPUT; |
| 300 | } |
| 301 | elseif ($this->mode !== self::INTERPOLATE_OUTPUT) { |
| 302 | throw new RuntimeException("Select::param() only makes sense when interpolating on output."); |
| 303 | } |
| 304 | |
| 305 | if (is_array($keys)) { |
| 306 | foreach ($keys as $k => $v) { |
| 307 | $this->params[$k] = $v; |
| 308 | } |
| 309 | } |
| 310 | else { |
| 311 | $this->params[$keys] = $value; |
| 312 | } |
| 313 | return $this; |
| 314 | } |
| 315 | |
| 316 | /** |
| 317 | * Set a limit on the number of records to return. |
| 318 | * |
| 319 | * @param int $limit |
| 320 | * @param int $offset |
| 321 | * @return CRM_Utils_SQL_Select |
| 322 | * @throws CRM_Core_Exception |
| 323 | */ |
| 324 | public function limit($limit, $offset = 0) { |
| 325 | if ($limit !== NULL && !is_numeric($limit)) { |
| 326 | throw new CRM_Core_Exception("Illegal limit"); |
| 327 | } |
| 328 | if ($offset !== NULL && !is_numeric($offset)) { |
| 329 | throw new CRM_Core_Exception("Illegal offset"); |
| 330 | } |
| 331 | $this->limit = $limit; |
| 332 | $this->offset = $offset; |
| 333 | return $this; |
| 334 | } |
| 335 | |
| 336 | /** |
| 337 | * Insert the results of the SELECT query into another |
| 338 | * table. |
| 339 | * |
| 340 | * @param string $table |
| 341 | * The name of the other table (which receives new data). |
| 342 | * @param array $fields |
| 343 | * The fields to fill in the other table (in order). |
| 344 | * @return $this |
| 345 | * @see insertIntoField |
| 346 | */ |
| 347 | public function insertInto($table, $fields = array()) { |
| 348 | $this->insertInto = $table; |
| 349 | $this->insertIntoField($fields); |
| 350 | return $this; |
| 351 | } |
| 352 | |
| 353 | /** |
| 354 | * @param array $fields |
| 355 | * The fields to fill in the other table (in order). |
| 356 | * @return $this |
| 357 | */ |
| 358 | public function insertIntoField($fields) { |
| 359 | $fields = (array) $fields; |
| 360 | foreach ($fields as $field) { |
| 361 | $this->insertIntoFields[] = $field; |
| 362 | } |
| 363 | return $this; |
| 364 | } |
| 365 | |
| 366 | /** |
| 367 | * @param array|NULL $parts |
| 368 | * List of fields to check (e.g. 'selects', 'joins'). |
| 369 | * Defaults to all. |
| 370 | * @return bool |
| 371 | */ |
| 372 | public function isEmpty($parts = NULL) { |
| 373 | $empty = TRUE; |
| 374 | $fields = array( |
| 375 | 'insertInto', |
| 376 | 'insertIntoFields', |
| 377 | 'selects', |
| 378 | 'from', |
| 379 | 'joins', |
| 380 | 'wheres', |
| 381 | 'groupBys', |
| 382 | 'havings', |
| 383 | 'orderBys', |
| 384 | 'limit', |
| 385 | 'offset', |
| 386 | ); |
| 387 | if ($parts !== NULL) { |
| 388 | $fields = array_intersect($fields, $parts); |
| 389 | } |
| 390 | foreach ($fields as $field) { |
| 391 | if (!empty($this->{$field})) { |
| 392 | $empty = FALSE; |
| 393 | } |
| 394 | } |
| 395 | return $empty; |
| 396 | } |
| 397 | |
| 398 | /** |
| 399 | * Enable (or disable) strict mode. |
| 400 | * |
| 401 | * In strict mode, unknown variables will generate exceptions. |
| 402 | * |
| 403 | * @param bool $strict |
| 404 | * @return $this |
| 405 | */ |
| 406 | public function strict($strict = TRUE) { |
| 407 | $this->strict = $strict; |
| 408 | return $this; |
| 409 | } |
| 410 | |
| 411 | /** |
| 412 | * Given a string like "field_name = @value", replace "@value" with an escaped SQL string |
| 413 | * |
| 414 | * @param $expr SQL expression |
| 415 | * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded: |
| 416 | * prefix '@' => escape SQL |
| 417 | * prefix '#' => literal number, skip escaping but do validation |
| 418 | * prefix '!' => literal, skip escaping and validation |
| 419 | * if a value is an array, then it will be imploded |
| 420 | * |
| 421 | * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string. |
| 422 | * |
| 423 | * @throws CRM_Core_Exception |
| 424 | * @return string |
| 425 | * SQL expression |
| 426 | */ |
| 427 | public function interpolate($expr, $args, $activeMode = self::INTERPOLATE_INPUT) { |
| 428 | if ($args === NULL) { |
| 429 | return $expr; |
| 430 | } |
| 431 | else { |
| 432 | if ($this->mode === self::INTERPOLATE_AUTO) { |
| 433 | $this->mode = $activeMode; |
| 434 | } |
| 435 | elseif ($activeMode !== $this->mode) { |
| 436 | throw new RuntimeException("Cannot mix interpolation modes."); |
| 437 | } |
| 438 | |
| 439 | $select = $this; |
| 440 | return preg_replace_callback('/([#!@])([a-zA-Z0-9_]+)/', function($m) use ($select, $args) { |
| 441 | if (isset($args[$m[2]])) { |
| 442 | $values = $args[$m[2]]; |
| 443 | } |
| 444 | elseif (isset($args[$m[1] . $m[2]])) { |
| 445 | // Backward compat. Keys in $args look like "#myNumber" or "@myString". |
| 446 | $values = $args[$m[1] . $m[2]]; |
| 447 | } |
| 448 | elseif ($select->strict) { |
| 449 | throw new CRM_Core_Exception('Cannot build query. Variable "' . $m[1] . $m[2] . '" is unknown.'); |
| 450 | } |
| 451 | else { |
| 452 | // Unrecognized variables are ignored. Mitigate risk of accidents. |
| 453 | return $m[0]; |
| 454 | } |
| 455 | $values = is_array($values) ? $values : array($values); |
| 456 | switch ($m[1]) { |
| 457 | case '@': |
| 458 | $parts = array_map(array($select, 'escapeString'), $values); |
| 459 | return implode(', ', $parts); |
| 460 | |
| 461 | case '!': |
| 462 | return implode(', ', $values); |
| 463 | |
| 464 | case '#': |
| 465 | foreach ($values as $valueKey => $value) { |
| 466 | if ($value === NULL) { |
| 467 | $values[$valueKey] = 'NULL'; |
| 468 | } |
| 469 | elseif (!is_numeric($value)) { |
| 470 | //throw new API_Exception("Failed encoding non-numeric value" . var_export(array($key => $args[$key]), TRUE)); |
| 471 | throw new CRM_Core_Exception("Failed encoding non-numeric value"); |
| 472 | } |
| 473 | } |
| 474 | return implode(', ', $values); |
| 475 | |
| 476 | default: |
| 477 | throw new CRM_Core_Exception("Unrecognized prefix"); |
| 478 | } |
| 479 | }, $expr); |
| 480 | } |
| 481 | } |
| 482 | |
| 483 | /** |
| 484 | * @param string|NULL $value |
| 485 | * @return string |
| 486 | * SQL expression, e.g. "it\'s great" (with-quotes) or NULL (without-quotes) |
| 487 | */ |
| 488 | public function escapeString($value) { |
| 489 | return $value === NULL ? 'NULL' : '"' . CRM_Core_DAO::escapeString($value) . '"'; |
| 490 | } |
| 491 | |
| 492 | /** |
| 493 | * @return string |
| 494 | * SQL statement |
| 495 | */ |
| 496 | public function toSQL() { |
| 497 | $sql = ''; |
| 498 | if ($this->insertInto) { |
| 499 | $sql .= 'INSERT INTO ' . $this->insertInto . ' ('; |
| 500 | $sql .= implode(', ', $this->insertIntoFields); |
| 501 | $sql .= ")\n"; |
| 502 | } |
| 503 | if ($this->selects) { |
| 504 | $sql .= 'SELECT ' . implode(', ', $this->selects) . "\n"; |
| 505 | } |
| 506 | else { |
| 507 | $sql .= 'SELECT *' . "\n"; |
| 508 | } |
| 509 | if ($this->from !== NULL) { |
| 510 | $sql .= 'FROM ' . $this->from . "\n"; |
| 511 | } |
| 512 | foreach ($this->joins as $join) { |
| 513 | $sql .= $join . "\n"; |
| 514 | } |
| 515 | if ($this->wheres) { |
| 516 | $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n"; |
| 517 | } |
| 518 | if ($this->groupBys) { |
| 519 | $sql .= 'GROUP BY ' . implode(', ', $this->groupBys) . "\n"; |
| 520 | } |
| 521 | if ($this->havings) { |
| 522 | $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n"; |
| 523 | } |
| 524 | if ($this->orderBys) { |
| 525 | $sql .= 'ORDER BY ' . implode(', ', $this->orderBys) . "\n"; |
| 526 | } |
| 527 | if ($this->limit !== NULL) { |
| 528 | $sql .= 'LIMIT ' . $this->limit . "\n"; |
| 529 | if ($this->offset !== NULL) { |
| 530 | $sql .= 'OFFSET ' . $this->offset . "\n"; |
| 531 | } |
| 532 | } |
| 533 | if ($this->mode === self::INTERPOLATE_OUTPUT) { |
| 534 | $sql = $this->interpolate($sql, $this->params, self::INTERPOLATE_OUTPUT); |
| 535 | } |
| 536 | return $sql; |
| 537 | } |
| 538 | |
| 539 | public function offsetExists($offset) { |
| 540 | return isset($this->params[$offset]); |
| 541 | } |
| 542 | |
| 543 | public function offsetGet($offset) { |
| 544 | return $this->params[$offset]; |
| 545 | } |
| 546 | |
| 547 | public function offsetSet($offset, $value) { |
| 548 | $this->param($offset, $value); |
| 549 | } |
| 550 | |
| 551 | public function offsetUnset($offset) { |
| 552 | unset($this->params[$offset]); |
| 553 | } |
| 554 | |
| 555 | } |