3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2019 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
29 * Dear God Why Do I Have To Write This (Dumb SQL Builder)
33 * $del = CRM_Utils_SQL_Delete::from('civicrm_activity act')
34 * ->where('activity_type_id = #type', array('type' => 234))
35 * ->where('status_id IN (#statuses)', array('statuses' => array(1,2,3))
36 * ->where('subject like @subj', array('subj' => '%hello%'))
37 * ->where('!dynamicColumn = 1', array('dynamicColumn' => 'coalesce(is_active,0)'))
38 * ->where('!column = @value', array(
39 * 'column' => $customField->column_name,
40 * 'value' => $form['foo']
47 * - No knowledge of the underlying SQL API (except for escaping -- CRM_Core_DAO::escapeString)
48 * - No knowledge of the underlying data model
49 * - SQL clauses correspond to PHP functions ($select->where("foo_id=123"))
50 * - Variable escaping is concise and controllable based on prefixes, eg
51 * - similar to Drupal's t()
52 * - use "@varname" to insert the escaped value
53 * - use "!varname" to insert raw (unescaped) values
54 * - use "#varname" to insert a numerical value (these are validated but not escaped)
55 * - to disable any preprocessing, simply omit the variable list
56 * - control characters (@!#) are mandatory in expressions but optional in arg-keys
57 * - Variables may be individual values or arrays; arrays are imploded with commas
58 * - Conditionals are AND'd; if you need OR's, do it yourself
59 * - Use classes/functions with documentation (rather than undocumented array-trees)
60 * - For any given string, interpolation is only performed once. After an interpolation,
61 * a string may never again be subjected to interpolation.
63 * The "interpolate-once" principle can be enforced by either interpolating on input
64 * xor output. The notations for input and output interpolation are a bit different,
65 * and they may not be mixed.
68 * // Interpolate on input. Set params when using them.
69 * $select->where('activity_type_id = #type', array(
73 * // Interpolate on output. Set params independently.
75 * ->where('activity_type_id = #type')
76 * ->param('type', 234),
80 * @copyright CiviCRM LLC (c) 2004-2019
82 class CRM_Utils_SQL_Delete
extends CRM_Utils_SQL_BaseParamQuery
{
85 private $wheres = array();
88 * Create a new DELETE query.
91 * Table-name and optional alias.
92 * @param array $options
93 * @return CRM_Utils_SQL_Delete
95 public static function from($from, $options = array()) {
96 return new self($from, $options);
100 * Create a new DELETE query.
102 * @param string $from
103 * Table-name and optional alias.
104 * @param array $options
106 public function __construct($from, $options = array()) {
108 $this->mode
= isset($options['mode']) ?
$options['mode'] : self
::INTERPOLATE_AUTO
;
112 * Make a new copy of this query.
114 * @return CRM_Utils_SQL_Delete
116 public function copy() {
121 * Merge something or other.
123 * @param CRM_Utils_SQL_Delete $other
124 * @param array|NULL $parts
126 * @return CRM_Utils_SQL_Delete
128 public function merge($other, $parts = NULL) {
129 if ($other === NULL) {
133 if ($this->mode
=== self
::INTERPOLATE_AUTO
) {
134 $this->mode
= $other->mode
;
136 elseif ($other->mode
=== self
::INTERPOLATE_AUTO
) {
139 elseif ($this->mode
!== $other->mode
) {
140 // Mixing modes will lead to someone getting an expected substitution.
141 throw new RuntimeException("Cannot merge queries that use different interpolation modes ({$this->mode} vs {$other->mode}).");
144 $arrayFields = array('wheres', 'params');
145 foreach ($arrayFields as $f) {
146 if ($parts === NULL ||
in_array($f, $parts)) {
147 $this->{$f} = array_merge($this->{$f}, $other->{$f});
151 $flatFields = array('from');
152 foreach ($flatFields as $f) {
153 if ($parts === NULL ||
in_array($f, $parts)) {
154 if ($other->{$f} !== NULL) {
155 $this->{$f} = $other->{$f};
164 * Limit results by adding extra condition(s) to the WHERE clause
166 * @param string|array $exprs list of SQL expressions
167 * @param null|array $args use NULL to disable interpolation; use an array of variables to enable
168 * @return CRM_Utils_SQL_Delete
170 public function where($exprs, $args = NULL) {
171 $exprs = (array) $exprs;
172 foreach ($exprs as $expr) {
173 $evaluatedExpr = $this->interpolate($expr, $args);
174 $this->wheres
[$evaluatedExpr] = $evaluatedExpr;
180 * Set one (or multiple) parameters to interpolate into the query.
182 * @param array|string $keys
183 * Key name, or an array of key-value pairs.
184 * @param null|mixed $value
185 * The new value of the parameter.
186 * Values may be strings, ints, or arrays thereof -- provided that the
187 * SQL query uses appropriate prefix (e.g. "@", "!", "#").
188 * @return \CRM_Utils_SQL_Delete
190 public function param($keys, $value = NULL) {
191 // Why bother with an override? To provide better type-hinting in `@return`.
192 return parent
::param($keys, $value);
196 * @param array|NULL $parts
197 * List of fields to check (e.g. 'wheres').
201 public function isEmpty($parts = NULL) {
207 if ($parts !== NULL) {
208 $fields = array_intersect($fields, $parts);
210 foreach ($fields as $field) {
211 if (!empty($this->{$field})) {
222 public function toSQL() {
225 if ($this->from
!== NULL) {
226 $sql .= 'FROM ' . $this->from
. "\n";
229 $sql .= 'WHERE (' . implode(') AND (', $this->wheres
) . ")\n";
231 if ($this->mode
=== self
::INTERPOLATE_OUTPUT
) {
232 $sql = $this->interpolate($sql, $this->params
, self
::INTERPOLATE_OUTPUT
);
240 * To examine the results, use a function like `fetch()`, `fetchAll()`,
241 * `fetchValue()`, or `fetchMap()`.
243 * @param string|NULL $daoName
244 * The return object should be an instance of this class.
245 * Ex: 'CRM_Contact_BAO_Contact'.
246 * @param bool $i18nRewrite
247 * If the system has multilingual features, should the field/table
248 * names be rewritten?
249 * @return CRM_Core_DAO
250 * @see CRM_Core_DAO::executeQuery
251 * @see CRM_Core_I18n_Schema::rewriteQuery
253 public function execute($daoName = NULL, $i18nRewrite = TRUE) {
254 // Don't pass through $params. toSQL() handles interpolation.
257 // Don't pass through $abort, $trapException. Just use straight-up exceptions.
259 $trapException = FALSE;
260 $errorScope = CRM_Core_TemporaryErrorScope
::useException();
262 // Don't pass through freeDAO. You can do it yourself.
265 return CRM_Core_DAO
::executeQuery($this->toSQL(), $params, $abort, $daoName,
266 $freeDAO, $i18nRewrite, $trapException);