Commit | Line | Data |
---|---|---|
d1d3c04a CW |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
bc77d7c0 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
d1d3c04a | 5 | | | |
bc77d7c0 TO |
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 | | |
d1d3c04a CW |
9 | +--------------------------------------------------------------------+ |
10 | */ | |
11 | ||
12 | /** | |
13 | * Just another collection of static utils functions. | |
14 | * | |
15 | * @package CRM | |
ca5cec67 | 16 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
d1d3c04a CW |
17 | */ |
18 | class CRM_Utils_SQL { | |
19 | ||
8d61954a TO |
20 | /** |
21 | * Given a string like "UPDATE some_table SET !field = @value", replace "!field" and "@value". | |
22 | * | |
23 | * This is syntactic sugar for using CRM_Utils_SQL_*::interpolate() without an OOP representation of the query. | |
24 | * | |
25 | * @param string $expr SQL expression | |
26 | * @param null|array $args a list of values to insert into the SQL expression; keys are prefix-coded: | |
27 | * prefix '@' => escape SQL | |
28 | * prefix '#' => literal number, skip escaping but do validation | |
29 | * prefix '!' => literal, skip escaping and validation | |
30 | * if a value is an array, then it will be imploded | |
31 | * | |
32 | * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string. | |
33 | * | |
34 | * @return string | |
35 | */ | |
36 | public static function interpolate($expr, $args) { | |
37 | if (!isset(Civi::$statics[__CLASS__][__FUNCTION__])) { | |
38 | Civi::$statics[__CLASS__][__FUNCTION__] = new class extends CRM_Utils_SQL_BaseParamQuery { | |
39 | ||
40 | public function __construct() { | |
41 | $this->mode = CRM_Utils_SQL_BaseParamQuery::INTERPOLATE_INPUT; | |
42 | $this->strict(); | |
43 | } | |
44 | ||
45 | }; | |
46 | } | |
47 | /** @var \CRM_Utils_SQL_BaseParamQuery $qb */ | |
48 | $qb = Civi::$statics[__CLASS__][__FUNCTION__]; | |
49 | return $qb->strict()->interpolate($expr, $args); | |
50 | } | |
51 | ||
d1d3c04a CW |
52 | /** |
53 | * Helper function for adding the permissioned subquery from one entity onto another | |
54 | * | |
55 | * @param string $entity | |
56 | * @param string $joinColumn | |
57 | * @return array | |
58 | */ | |
59 | public static function mergeSubquery($entity, $joinColumn = 'id') { | |
4f0e32e0 | 60 | require_once 'api/v3/utils.php'; |
d1d3c04a CW |
61 | $baoName = _civicrm_api3_get_BAO($entity); |
62 | $bao = new $baoName(); | |
be2fb01f | 63 | $clauses = $subclauses = []; |
d1d3c04a CW |
64 | foreach ((array) $bao->addSelectWhereClause() as $field => $vals) { |
65 | if ($vals && $field == $joinColumn) { | |
66 | $clauses = array_merge($clauses, (array) $vals); | |
67 | } | |
68 | elseif ($vals) { | |
69 | $subclauses[] = "$field " . implode(" AND $field ", (array) $vals); | |
70 | } | |
71 | } | |
72 | if ($subclauses) { | |
73 | $clauses[] = "IN (SELECT `$joinColumn` FROM `" . $bao->tableName() . "` WHERE " . implode(' AND ', $subclauses) . ")"; | |
74 | } | |
75 | return $clauses; | |
76 | } | |
77 | ||
712e729f SL |
78 | /** |
79 | * Get current sqlModes of the session | |
80 | * @return array | |
81 | */ | |
82 | public static function getSqlModes() { | |
83 | $sqlModes = explode(',', CRM_Core_DAO::singleValueQuery('SELECT @@sql_mode')); | |
84 | return $sqlModes; | |
85 | } | |
86 | ||
87 | /** | |
2c40ef6b ML |
88 | * Checks if this system enforce the MYSQL mode ONLY_FULL_GROUP_BY. |
89 | * This function should be named supportsAnyValueAndEnforcesFullGroupBY(), | |
90 | * but should be deprecated instead. | |
91 | * | |
712e729f | 92 | * @return mixed |
2c40ef6b | 93 | * @deprecated |
712e729f SL |
94 | */ |
95 | public static function supportsFullGroupBy() { | |
2c40ef6b | 96 | // CRM-21455 MariaDB 10.2 does not support ANY_VALUE |
79ee443c | 97 | $version = self::getDatabaseVersion(); |
2c40ef6b | 98 | |
6454ff54 | 99 | if (stripos($version, 'mariadb') !== FALSE) { |
2c40ef6b ML |
100 | return FALSE; |
101 | } | |
102 | ||
103 | return version_compare($version, '5.7', '>='); | |
712e729f SL |
104 | } |
105 | ||
b07c960a | 106 | /** |
107 | * Disable ONLY_FULL_GROUP_BY for MySQL versions lower then 5.7 | |
108 | * | |
109 | * @return bool | |
110 | */ | |
111 | public static function disableFullGroupByMode() { | |
112 | $sqlModes = self::getSqlModes(); | |
113 | ||
114 | // Disable only_full_group_by mode for lower sql versions. | |
115 | if (!self::supportsFullGroupBy() || (!empty($sqlModes) && !in_array('ONLY_FULL_GROUP_BY', $sqlModes))) { | |
116 | if ($key = array_search('ONLY_FULL_GROUP_BY', $sqlModes)) { | |
117 | unset($sqlModes[$key]); | |
118 | CRM_Core_DAO::executeQuery("SET SESSION sql_mode = '" . implode(',', $sqlModes) . "'"); | |
119 | } | |
120 | return TRUE; | |
121 | } | |
122 | ||
123 | return FALSE; | |
124 | } | |
125 | ||
a2ed62b3 SL |
126 | /** |
127 | * CHeck if ONLY_FULL_GROUP_BY is in the global sql_modes | |
128 | * @return bool | |
129 | */ | |
130 | public static function isGroupByModeInDefault() { | |
a2ed62b3 SL |
131 | $sqlModes = explode(',', CRM_Core_DAO::singleValueQuery('SELECT @@global.sql_mode')); |
132 | if (!in_array('ONLY_FULL_GROUP_BY', $sqlModes)) { | |
133 | return FALSE; | |
134 | } | |
135 | return TRUE; | |
136 | } | |
137 | ||
79ee443c | 138 | /** |
139 | * Does the DB version support mutliple locks per | |
140 | * | |
141 | * https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_get-lock | |
142 | * | |
79ee443c | 143 | * This is a conservative measure to introduce the change which we expect to deprecate later. |
144 | * | |
145 | * @todo we only check mariadb & mysql right now but maybe can add percona. | |
146 | */ | |
147 | public static function supportsMultipleLocks() { | |
79ee443c | 148 | static $isSupportLocks = NULL; |
149 | if (!isset($isSupportLocks)) { | |
150 | $version = self::getDatabaseVersion(); | |
151 | if (stripos($version, 'mariadb') !== FALSE) { | |
152 | $isSupportLocks = version_compare($version, '10.0.2', '>='); | |
153 | } | |
154 | else { | |
155 | $isSupportLocks = version_compare($version, '5.7.5', '>='); | |
156 | } | |
157 | } | |
158 | ||
159 | return $isSupportLocks; | |
160 | } | |
161 | ||
162 | /** | |
163 | * Get the version string for the database. | |
164 | * | |
165 | * @return string | |
166 | */ | |
167 | public static function getDatabaseVersion() { | |
168 | return CRM_Core_DAO::singleValueQuery('SELECT VERSION()'); | |
169 | } | |
170 | ||
d1d3c04a | 171 | } |