CRM/Utils/SQL.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 5                                                  |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2019                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  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.   |
  13  |                                                                    |
  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.        |
  18  |                                                                    |
  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  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  * Just another collection of static utils functions.
  30  *
  31  * @package CRM
  32  * @copyright CiviCRM LLC (c) 2004-2019
  33  */
  34 class CRM_Utils_SQL {
  35
  36   /**
  37    * Helper function for adding the permissioned subquery from one entity onto another
  38    *
  39    * @param string $entity
  40    * @param string $joinColumn
  41    * @return array
  42    */
  43   public static function mergeSubquery($entity, $joinColumn = 'id') {
  44     require_once 'api/v3/utils.php';
  45     $baoName = _civicrm_api3_get_BAO($entity);
  46     $bao = new $baoName();
  47     $clauses = $subclauses = [];
  48     foreach ((array) $bao->addSelectWhereClause() as $field => $vals) {
  49       if ($vals && $field == $joinColumn) {
  50         $clauses = array_merge($clauses, (array) $vals);
  51       }
  52       elseif ($vals) {
  53         $subclauses[] = "$field " . implode(" AND $field ", (array) $vals);
  54       }
  55     }
  56     if ($subclauses) {
  57       $clauses[] = "IN (SELECT `$joinColumn` FROM `" . $bao->tableName() . "` WHERE " . implode(' AND ', $subclauses) . ")";
  58     }
  59     return $clauses;
  60   }
  61
  62   /**
  63    * Get current sqlModes of the session
  64    * @return array
  65    */
  66   public static function getSqlModes() {
  67     $sqlModes = explode(',', CRM_Core_DAO::singleValueQuery('SELECT @@sql_mode'));
  68     return $sqlModes;
  69   }
  70
  71   /**
  72    * Checks if this system enforce the MYSQL mode ONLY_FULL_GROUP_BY.
  73    * This function should be named supportsAnyValueAndEnforcesFullGroupBY(),
  74    * but should be deprecated instead.
  75    *
  76    * @return mixed
  77    * @deprecated
  78    */
  79   public static function supportsFullGroupBy() {
  80     // CRM-21455 MariaDB 10.2 does not support ANY_VALUE
  81     $version = self::getDatabaseVersion();
  82
  83     if (stripos($version, 'mariadb') !== FALSE) {
  84       return FALSE;
  85     }
  86
  87     return version_compare($version, '5.7', '>=');
  88   }
  89
  90   /**
  91    * Disable ONLY_FULL_GROUP_BY for MySQL versions lower then 5.7
  92    *
  93    * @return bool
  94    */
  95   public static function disableFullGroupByMode() {
  96     $sqlModes = self::getSqlModes();
  97
  98     // Disable only_full_group_by mode for lower sql versions.
  99     if (!self::supportsFullGroupBy() || (!empty($sqlModes) && !in_array('ONLY_FULL_GROUP_BY', $sqlModes))) {
 100       if ($key = array_search('ONLY_FULL_GROUP_BY', $sqlModes)) {
 101         unset($sqlModes[$key]);
 102         CRM_Core_DAO::executeQuery("SET SESSION sql_mode = '" . implode(',', $sqlModes) . "'");
 103       }
 104       return TRUE;
 105     }
 106
 107     return FALSE;
 108   }
 109
 110   /**
 111    * CHeck if ONLY_FULL_GROUP_BY is in the global sql_modes
 112    * @return bool
 113    */
 114   public static function isGroupByModeInDefault() {
 115     $sqlModes = explode(',', CRM_Core_DAO::singleValueQuery('SELECT @@global.sql_mode'));
 116     if (!in_array('ONLY_FULL_GROUP_BY', $sqlModes)) {
 117       return FALSE;
 118     }
 119     return TRUE;
 120   }
 121
 122   /**
 123    * Does the DB version support mutliple locks per
 124    *
 125    * https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_get-lock
 126    *
 127    * This is a conservative measure to introduce the change which we expect to deprecate later.
 128    *
 129    * @todo we only check mariadb & mysql right now but maybe can add percona.
 130    */
 131   public static function supportsMultipleLocks() {
 132     static $isSupportLocks = NULL;
 133     if (!isset($isSupportLocks)) {
 134       $version = self::getDatabaseVersion();
 135       if (stripos($version, 'mariadb') !== FALSE) {
 136         $isSupportLocks = version_compare($version, '10.0.2', '>=');
 137       }
 138       else {
 139         $isSupportLocks = version_compare($version, '5.7.5', '>=');
 140       }
 141     }
 142
 143     return $isSupportLocks;
 144   }
 145
 146   /**
 147    * Get the version string for the database.
 148    *
 149    * @return string
 150    */
 151   public static function getDatabaseVersion() {
 152     return CRM_Core_DAO::singleValueQuery('SELECT VERSION()');
 153   }
 154
 155 }