3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 +--------------------------------------------------------------------+
13 * Dear God Why Do I Have To Write This (Dumb SQL Builder)
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']
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.
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.
52 * // Interpolate on input. Set params when using them.
53 * $select->where('activity_type_id = #type', array(
57 * // Interpolate on output. Set params independently.
59 * ->where('activity_type_id = #type')
60 * ->param('type', 234),
64 * @copyright CiviCRM LLC https://civicrm.org/licensing
66 class CRM_Utils_SQL_Delete
extends CRM_Utils_SQL_BaseParamQuery
{
72 * Create a new DELETE query.
75 * Table-name and optional alias.
76 * @param array $options
77 * @return CRM_Utils_SQL_Delete
79 public static function from($from, $options = []) {
80 return new self($from, $options);
84 * Create a new DELETE query.
87 * Table-name and optional alias.
88 * @param array $options
90 public function __construct($from, $options = []) {
92 $this->mode
= $options['mode'] ?? self
::INTERPOLATE_AUTO
;
96 * Make a new copy of this query.
98 * @return CRM_Utils_SQL_Delete
100 public function copy() {
105 * Merge something or other.
107 * @param CRM_Utils_SQL_Delete $other
108 * @param array|NULL $parts
110 * @return CRM_Utils_SQL_Delete
112 public function merge($other, $parts = NULL) {
113 if ($other === NULL) {
117 if ($this->mode
=== self
::INTERPOLATE_AUTO
) {
118 $this->mode
= $other->mode
;
120 elseif ($other->mode
=== self
::INTERPOLATE_AUTO
) {
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}).");
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});
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};
148 * Limit results by adding extra condition(s) to the WHERE clause
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
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;
164 * Set one (or multiple) parameters to interpolate into the query.
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
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);
180 * @param array|NULL $parts
181 * List of fields to check (e.g. 'wheres').
185 public function isEmpty($parts = NULL) {
191 if ($parts !== NULL) {
192 $fields = array_intersect($fields, $parts);
194 foreach ($fields as $field) {
195 if (!empty($this->{$field})) {
206 public function toSQL() {
209 if ($this->from
!== NULL) {
210 $sql .= 'FROM ' . $this->from
. "\n";
213 $sql .= 'WHERE (' . implode(') AND (', $this->wheres
) . ")\n";
215 if ($this->mode
=== self
::INTERPOLATE_OUTPUT
) {
216 $sql = $this->interpolate($sql, $this->params
, self
::INTERPOLATE_OUTPUT
);
224 * To examine the results, use a function like `fetch()`, `fetchAll()`,
225 * `fetchValue()`, or `fetchMap()`.
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
237 public function execute($daoName = NULL, $i18nRewrite = TRUE) {
238 // Don't pass through $params. toSQL() handles interpolation.
241 // Don't pass through $abort, $trapException. Just use straight-up exceptions.
243 $trapException = FALSE;
245 // Don't pass through freeDAO. You can do it yourself.
248 return CRM_Core_DAO
::executeQuery($this->toSQL(), $params, $abort, $daoName,
249 $freeDAO, $i18nRewrite, $trapException);