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 * Just another collection of static utils functions.
16 * @copyright CiviCRM LLC https://civicrm.org/licensing
21 * Given a string like "UPDATE some_table SET !field = @value", replace "!field" and "@value".
23 * This is syntactic sugar for using CRM_Utils_SQL_*::interpolate() without an OOP representation of the query.
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
32 * PHP NULL's will be treated as SQL NULL's. The PHP string "null" will be treated as a string.
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
{
40 public function __construct() {
41 $this->mode
= CRM_Utils_SQL_BaseParamQuery
::INTERPOLATE_INPUT
;
47 /** @var \CRM_Utils_SQL_BaseParamQuery $qb */
48 $qb = Civi
::$statics[__CLASS__
][__FUNCTION__
];
49 return $qb->strict()->interpolate($expr, $args);
53 * Helper function for adding the permissioned subquery from one entity onto another
55 * @param string $entity
56 * @param string $joinColumn
59 public static function mergeSubquery($entity, $joinColumn = 'id') {
60 require_once 'api/v3/utils.php';
61 $baoName = _civicrm_api3_get_BAO($entity);
62 $bao = new $baoName();
63 $clauses = $subclauses = [];
64 foreach ((array) $bao->addSelectWhereClause() as $field => $vals) {
65 if ($vals && $field == $joinColumn) {
66 $clauses = array_merge($clauses, (array) $vals);
69 $subclauses[] = "$field " . implode(" AND $field ", (array) $vals);
73 $clauses[] = "IN (SELECT `$joinColumn` FROM `" . $bao->tableName() . "` WHERE " . implode(' AND ', $subclauses) . ")";
79 * Get current sqlModes of the session
82 public static function getSqlModes() {
83 $sqlModes = explode(',', CRM_Core_DAO
::singleValueQuery('SELECT @@sql_mode'));
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.
95 public static function supportsFullGroupBy() {
96 // CRM-21455 MariaDB 10.2 does not support ANY_VALUE
97 $version = self
::getDatabaseVersion();
99 if (stripos($version, 'mariadb') !== FALSE) {
103 return version_compare($version, '5.7', '>=');
107 * Disable ONLY_FULL_GROUP_BY for MySQL versions lower then 5.7
111 public static function disableFullGroupByMode() {
112 $sqlModes = self
::getSqlModes();
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) . "'");
127 * CHeck if ONLY_FULL_GROUP_BY is in the global sql_modes
130 public static function isGroupByModeInDefault() {
131 $sqlModes = explode(',', CRM_Core_DAO
::singleValueQuery('SELECT @@global.sql_mode'));
132 if (!in_array('ONLY_FULL_GROUP_BY', $sqlModes)) {
139 * Does the DB version support mutliple locks per
141 * https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_get-lock
143 * This is a conservative measure to introduce the change which we expect to deprecate later.
145 * @todo we only check mariadb & mysql right now but maybe can add percona.
147 public static function supportsMultipleLocks() {
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', '>=');
155 $isSupportLocks = version_compare($version, '5.7.5', '>=');
159 return $isSupportLocks;
163 * Get the version string for the database.
167 public static function getDatabaseVersion() {
168 return CRM_Core_DAO
::singleValueQuery('SELECT VERSION()');
172 * Does the DSN indicate the connection should use ssl.
178 public static function isSSLDSN(string $dsn):bool {
179 // Note that ssl= below is not an official PEAR::DB option. It doesn't know
180 // what to do with it. We made it up because it's not required
181 // to have client-side certificates to use ssl, so here you can specify
182 // you want that by putting ssl=1 in the DSN string.
184 // Cast to bool in case of error which we interpret as no ssl.
185 return (bool) preg_match('/[\?&](key|cert|ca|capath|cipher|ssl)=/', $dsn);
189 * If DB_DSN_MODE is auto then we should replace mysql with mysqli if mysqli is available or the other way around as appropriate
194 public static function autoSwitchDSN($dsn) {
195 if (defined('DB_DSN_MODE') && DB_DSN_MODE
=== 'auto') {
196 if (extension_loaded('mysqli')) {
197 $dsn = preg_replace('/^mysql:/', 'mysqli:', $dsn);
200 $dsn = preg_replace('/^mysqli:/', 'mysql:', $dsn);