CRM/Utils/SQL/Delete.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  * Dear God Why Do I Have To Write This (Dumb SQL Builder)
  14  *
  15  * Usage:
  16  * @code
  17  * $del = CRM_Utils_SQL_Delete::from('civicrm_activity act')
  18  *     ->where('activity_type_id = #type', array('type' => 234))
  19  *     ->where('status_id IN (#statuses)', array('statuses' => array(1,2,3))
  20  *     ->where('subject like @subj', array('subj' => '%hello%'))
  21  *     ->where('!dynamicColumn = 1', array('dynamicColumn' => 'coalesce(is_active,0)'))
  22  *     ->where('!column = @value', array(
  23  *        'column' => $customField->column_name,
  24  *        'value' => $form['foo']
  25  *      ))
  26  * echo $del->toSQL();
  27  * @endcode
  28  *
  29  * Design principles:
  30  *  - Portable
  31  *    - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
  32  *    - No knowledge of the underlying data model
  33  *  - SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
  34  *  - Variable escaping is concise and controllable based on prefixes, eg
  35  *    - similar to Drupal's t()
  36  *    - use "@varname" to insert the escaped value
  37  *    - use "!varname" to insert raw (unescaped) values
  38  *    - use "#varname" to insert a numerical value (these are validated but not escaped)
  39  *    - to disable any preprocessing, simply omit the variable list
  40  *    - control characters (@!#) are mandatory in expressions but optional in arg-keys
  41  *  - Variables may be individual values or arrays; arrays are imploded with commas
  42  *  - Conditionals are AND'd; if you need OR's, do it yourself
  43  *  - Use classes/functions with documentation (rather than undocumented array-trees)
  44  *  - For any given string, interpolation is only performed once. After an interpolation,
  45  *    a string may never again be subjected to interpolation.
  46  *
  47  * The "interpolate-once" principle can be enforced by either interpolating on input
  48  * xor output. The notations for input and output interpolation are a bit different,
  49  * and they may not be mixed.
  50  *
  51  * @code
  52  * // Interpolate on input. Set params when using them.
  53  * $select->where('activity_type_id = #type', array(
  54  *   'type' => 234,
  55  * ));
  56  *
  57  * // Interpolate on output. Set params independently.
  58  * $select
  59  *     ->where('activity_type_id = #type')
  60  *     ->param('type', 234),
  61  * @endcode
  62  *
  63  * @package CRM
  64  * @copyright CiviCRM LLC https://civicrm.org/licensing
  65  */
  66 class CRM_Utils_SQL_Delete extends CRM_Utils_SQL_BaseParamQuery {
  67
  68   private $from;
  69   private $wheres = [];
  70
  71   /**
  72    * Create a new DELETE query.
  73    *
  74    * @param string $from
  75    *   Table-name and optional alias.
  76    * @param array $options
  77    * @return CRM_Utils_SQL_Delete
  78    */
  79   public static function from($from, $options = []) {
  80     return new self($from, $options);
  81   }
  82
  83   /**
  84    * Create a new DELETE query.
  85    *
  86    * @param string $from
  87    *   Table-name and optional alias.
  88    * @param array $options
  89    */
  90   public function __construct($from, $options = []) {
  91     $this->from = $from;
  92     $this->mode = isset($options['mode']) ? $options['mode'] : self::INTERPOLATE_AUTO;
  93   }
  94
  95   /**
  96    * Make a new copy of this query.
  97    *
  98    * @return CRM_Utils_SQL_Delete
  99    */
 100   public function copy() {
 101     return clone $this;
 102   }
 103
 104   /**
 105    * Merge something or other.
 106    *
 107    * @param CRM_Utils_SQL_Delete $other
 108    * @param array|NULL $parts
 109    *   ex: 'wheres'
 110    * @return CRM_Utils_SQL_Delete
 111    */
 112   public function merge($other, $parts = NULL) {
 113     if ($other === NULL) {
 114       return $this;
 115     }
 116
 117     if ($this->mode === self::INTERPOLATE_AUTO) {
 118       $this->mode = $other->mode;
 119     }
 120     elseif ($other->mode === self::INTERPOLATE_AUTO) {
 121       // Noop.
 122     }
 123     elseif ($this->mode !== $other->mode) {
 124       // Mixing modes will lead to someone getting an expected substitution.
 125       throw new RuntimeException("Cannot merge queries that use different interpolation modes ({$this->mode} vs {$other->mode}).");
 126     }
 127
 128     $arrayFields = ['wheres', 'params'];
 129     foreach ($arrayFields as $f) {
 130       if ($parts === NULL || in_array($f, $parts)) {
 131         $this->{$f} = array_merge($this->{$f}, $other->{$f});
 132       }
 133     }
 134
 135     $flatFields = ['from'];
 136     foreach ($flatFields as $f) {
 137       if ($parts === NULL || in_array($f, $parts)) {
 138         if ($other->{$f} !== NULL) {
 139           $this->{$f} = $other->{$f};
 140         }
 141       }
 142     }
 143
 144     return $this;
 145   }
 146
 147   /**
 148    * Limit results by adding extra condition(s) to the WHERE clause
 149    *
 150    * @param string|array $exprs list of SQL expressions
 151    * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
 152    * @return CRM_Utils_SQL_Delete
 153    */
 154   public function where($exprs, $args = NULL) {
 155     $exprs = (array) $exprs;
 156     foreach ($exprs as $expr) {
 157       $evaluatedExpr = $this->interpolate($expr, $args);
 158       $this->wheres[$evaluatedExpr] = $evaluatedExpr;
 159     }
 160     return $this;
 161   }
 162
 163   /**
 164    * Set one (or multiple) parameters to interpolate into the query.
 165    *
 166    * @param array|string $keys
 167    *   Key name, or an array of key-value pairs.
 168    * @param null|mixed $value
 169    *   The new value of the parameter.
 170    *   Values may be strings, ints, or arrays thereof -- provided that the
 171    *   SQL query uses appropriate prefix (e.g. "@", "!", "#").
 172    * @return \CRM_Utils_SQL_Delete
 173    */
 174   public function param($keys, $value = NULL) {
 175     // Why bother with an override? To provide better type-hinting in `@return`.
 176     return parent::param($keys, $value);
 177   }
 178
 179   /**
 180    * @param array|NULL $parts
 181    *   List of fields to check (e.g. 'wheres').
 182    *   Defaults to all.
 183    * @return bool
 184    */
 185   public function isEmpty($parts = NULL) {
 186     $empty = TRUE;
 187     $fields = [
 188       'from',
 189       'wheres',
 190     ];
 191     if ($parts !== NULL) {
 192       $fields = array_intersect($fields, $parts);
 193     }
 194     foreach ($fields as $field) {
 195       if (!empty($this->{$field})) {
 196         $empty = FALSE;
 197       }
 198     }
 199     return $empty;
 200   }
 201
 202   /**
 203    * @return string
 204    *   SQL statement
 205    */
 206   public function toSQL() {
 207     $sql = 'DELETE ';
 208
 209     if ($this->from !== NULL) {
 210       $sql .= 'FROM ' . $this->from . "\n";
 211     }
 212     if ($this->wheres) {
 213       $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n";
 214     }
 215     if ($this->mode === self::INTERPOLATE_OUTPUT) {
 216       $sql = $this->interpolate($sql, $this->params, self::INTERPOLATE_OUTPUT);
 217     }
 218     return $sql;
 219   }
 220
 221   /**
 222    * Execute the query.
 223    *
 224    * To examine the results, use a function like `fetch()`, `fetchAll()`,
 225    * `fetchValue()`, or `fetchMap()`.
 226    *
 227    * @param string|NULL $daoName
 228    *   The return object should be an instance of this class.
 229    *   Ex: 'CRM_Contact_BAO_Contact'.
 230    * @param bool $i18nRewrite
 231    *   If the system has multilingual features, should the field/table
 232    *   names be rewritten?
 233    * @return CRM_Core_DAO
 234    * @see CRM_Core_DAO::executeQuery
 235    * @see CRM_Core_I18n_Schema::rewriteQuery
 236    */
 237   public function execute($daoName = NULL, $i18nRewrite = TRUE) {
 238     // Don't pass through $params. toSQL() handles interpolation.
 239     $params = [];
 240
 241     // Don't pass through $abort, $trapException. Just use straight-up exceptions.
 242     $abort = TRUE;
 243     $trapException = FALSE;
 244     $errorScope = CRM_Core_TemporaryErrorScope::useException();
 245
 246     // Don't pass through freeDAO. You can do it yourself.
 247     $freeDAO = FALSE;
 248
 249     return CRM_Core_DAO::executeQuery($this->toSQL(), $params, $abort, $daoName,
 250       $freeDAO, $i18nRewrite, $trapException);
 251   }
 252
 253 }