CRM/Core/DAO.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  * Base Database Access Object class.
  30  *
  31  * All DAO classes should inherit from this class.
  32  *
  33  * @package CRM
  34  * @copyright CiviCRM LLC (c) 2004-2019
  35  */
  36
  37 if (!defined('DB_DSN_MODE')) {
  38   define('DB_DSN_MODE', 'auto');
  39 }
  40
  41 require_once 'PEAR.php';
  42 require_once 'DB/DataObject.php';
  43
  44 require_once 'CRM/Core/I18n.php';
  45
  46 /**
  47  * Class CRM_Core_DAO
  48  */
  49 class CRM_Core_DAO extends DB_DataObject {
  50
  51   /**
  52    * How many times has this instance been cloned.
  53    *
  54    * @var int
  55    */
  56   protected $resultCopies = 0;
  57
  58   /**
  59    * @var null
  60    * @deprecated
  61    */
  62   public static $_nullObject = NULL;
  63   /**
  64    * @var array
  65    * @deprecated
  66    */
  67   public static $_nullArray = [];
  68
  69   public static $_dbColumnValueCache = NULL;
  70   const NOT_NULL = 1, IS_NULL = 2,
  71     DB_DAO_NOTNULL = 128,
  72     VALUE_SEPARATOR = "\ 1",
  73     BULK_INSERT_COUNT = 200,
  74     BULK_INSERT_HIGH_COUNT = 200,
  75     QUERY_FORMAT_WILDCARD = 1,
  76     QUERY_FORMAT_NO_QUOTES = 2,
  77
  78     /**
  79      * Serialized string separated by and bookended with VALUE_SEPARATOR
  80      */
  81     SERIALIZE_SEPARATOR_BOOKEND = 1,
  82     /**
  83      * @deprecated format separated by VALUE_SEPARATOR
  84      */
  85     SERIALIZE_SEPARATOR_TRIMMED = 2,
  86     /**
  87      * Recommended serialization format
  88      */
  89     SERIALIZE_JSON = 3,
  90     /**
  91      * @deprecated format using php serialize()
  92      */
  93     SERIALIZE_PHP = 4,
  94     /**
  95      * Comma separated string, no quotes, no spaces
  96      */
  97     SERIALIZE_COMMA = 5;
  98
  99   /**
 100    * Define entities that shouldn't be created or deleted when creating/ deleting
 101    * test objects - this prevents world regions, countries etc from being added / deleted
 102    * @var array
 103    */
 104   public static $_testEntitiesToSkip = [];
 105   /**
 106    * The factory class for this application.
 107    * @var object
 108    */
 109   public static $_factory = NULL;
 110
 111   public static $_checkedSqlFunctionsExist = FALSE;
 112
 113   /**
 114    * https://issues.civicrm.org/jira/browse/CRM-17748
 115    * internal variable for DAO to hold per-query settings
 116    * @var array
 117    */
 118   protected $_options = [];
 119
 120   /**
 121    * Class constructor.
 122    *
 123    * @return \CRM_Core_DAO
 124    */
 125   public function __construct() {
 126     $this->initialize();
 127     $this->__table = $this->getTableName();
 128   }
 129
 130   public function __clone() {
 131     if (!empty($this->_DB_resultid)) {
 132       $this->resultCopies++;
 133     }
 134   }
 135
 136   /**
 137    * Class destructor.
 138    */
 139   public function __destruct() {
 140     if ($this->resultCopies === 0) {
 141       $this->free();
 142     }
 143     $this->resultCopies--;
 144   }
 145
 146   /**
 147    * Empty definition for virtual function.
 148    */
 149   public static function getTableName() {
 150     return NULL;
 151   }
 152
 153   /**
 154    * Initialize the DAO object.
 155    *
 156    * @param string $dsn
 157    *   The database connection string.
 158    */
 159   public static function init($dsn) {
 160     Civi::$statics[__CLASS__]['init'] = 1;
 161     $options = &PEAR::getStaticProperty('DB_DataObject', 'options');
 162     $options['database'] = $dsn;
 163     if (defined('CIVICRM_DAO_DEBUG')) {
 164       self::DebugLevel(CIVICRM_DAO_DEBUG);
 165     }
 166     $factory = new CRM_Contact_DAO_Factory();
 167     CRM_Core_DAO::setFactory($factory);
 168     $currentModes = CRM_Utils_SQL::getSqlModes();
 169     if (CRM_Utils_Constant::value('CIVICRM_MYSQL_STRICT', CRM_Utils_System::isDevelopment())) {
 170       if (CRM_Utils_SQL::supportsFullGroupBy() && !in_array('ONLY_FULL_GROUP_BY', $currentModes) && CRM_Utils_SQL::isGroupByModeInDefault()) {
 171         $currentModes[] = 'ONLY_FULL_GROUP_BY';
 172       }
 173       if (!in_array('STRICT_TRANS_TABLES', $currentModes)) {
 174         $currentModes = array_merge(['STRICT_TRANS_TABLES'], $currentModes);
 175       }
 176       CRM_Core_DAO::executeQuery("SET SESSION sql_mode = %1", [1 => [implode(',', $currentModes), 'String']]);
 177     }
 178     CRM_Core_DAO::executeQuery('SET NAMES utf8');
 179     CRM_Core_DAO::executeQuery('SET @uniqueID = %1', [1 => [CRM_Utils_Request::id(), 'String']]);
 180   }
 181
 182   /**
 183    * @return DB_common
 184    */
 185   public static function getConnection() {
 186     global $_DB_DATAOBJECT;
 187     $dao = new CRM_Core_DAO();
 188     return $_DB_DATAOBJECT['CONNECTIONS'][$dao->_database_dsn_md5];
 189   }
 190
 191   /**
 192    * Disables usage of the ONLY_FULL_GROUP_BY Mode if necessary
 193    */
 194   public static function disableFullGroupByMode() {
 195     $currentModes = CRM_Utils_SQL::getSqlModes();
 196     if (in_array('ONLY_FULL_GROUP_BY', $currentModes) && CRM_Utils_SQL::isGroupByModeInDefault()) {
 197       $key = array_search('ONLY_FULL_GROUP_BY', $currentModes);
 198       unset($currentModes[$key]);
 199       CRM_Core_DAO::executeQuery("SET SESSION sql_mode = %1", [1 => [implode(',', $currentModes), 'String']]);
 200     }
 201   }
 202
 203   /**
 204    * Re-enables ONLY_FULL_GROUP_BY sql_mode as necessary..
 205    */
 206   public static function reenableFullGroupByMode() {
 207     $currentModes = CRM_Utils_SQL::getSqlModes();
 208     if (!in_array('ONLY_FULL_GROUP_BY', $currentModes) && CRM_Utils_SQL::isGroupByModeInDefault()) {
 209       $currentModes[] = 'ONLY_FULL_GROUP_BY';
 210       CRM_Core_DAO::executeQuery("SET SESSION sql_mode = %1", [1 => [implode(',', $currentModes), 'String']]);
 211     }
 212   }
 213
 214   /**
 215    * @param string $fieldName
 216    * @param $fieldDef
 217    * @param array $params
 218    */
 219   protected function assignTestFK($fieldName, $fieldDef, $params) {
 220     $required = CRM_Utils_Array::value('required', $fieldDef);
 221     $FKClassName = CRM_Utils_Array::value('FKClassName', $fieldDef);
 222     $dbName = $fieldDef['name'];
 223     $daoName = str_replace('_BAO_', '_DAO_', get_class($this));
 224
 225     // skip the FK if it is not required
 226     // if it's contact id we should create even if not required
 227     // we'll have a go @ fetching first though
 228     // we WILL create campaigns though for so tests with a campaign pseudoconstant will complete
 229     if ($FKClassName === 'CRM_Campaign_DAO_Campaign' && $daoName != $FKClassName) {
 230       $required = TRUE;
 231     }
 232     if (!$required && $dbName != 'contact_id') {
 233       $fkDAO = new $FKClassName();
 234       if ($fkDAO->find(TRUE)) {
 235         $this->$dbName = $fkDAO->id;
 236       }
 237       $fkDAO->free();
 238     }
 239
 240     elseif (in_array($FKClassName, CRM_Core_DAO::$_testEntitiesToSkip)) {
 241       $depObject = new $FKClassName();
 242       $depObject->find(TRUE);
 243       $this->$dbName = $depObject->id;
 244       $depObject->free();
 245     }
 246     elseif ($daoName == 'CRM_Member_DAO_MembershipType' && $fieldName == 'member_of_contact_id') {
 247       // FIXME: the fields() metadata is not specific enough
 248       $depObject = CRM_Core_DAO::createTestObject($FKClassName, ['contact_type' => 'Organization']);
 249       $this->$dbName = $depObject->id;
 250       $depObject->free();
 251     }
 252     else {
 253       //if it is required we need to generate the dependency object first
 254       $depObject = CRM_Core_DAO::createTestObject($FKClassName, CRM_Utils_Array::value($dbName, $params, 1));
 255       $this->$dbName = $depObject->id;
 256       $depObject->free();
 257     }
 258   }
 259
 260   /**
 261    * Generate and assign an arbitrary value to a field of a test object.
 262    *
 263    * @param string $fieldName
 264    * @param array $fieldDef
 265    * @param int $counter
 266    *   The globally-unique ID of the test object.
 267    */
 268   protected function assignTestValue($fieldName, &$fieldDef, $counter) {
 269     $dbName = $fieldDef['name'];
 270     $daoName = get_class($this);
 271     $handled = FALSE;
 272
 273     if (!$handled && $dbName == 'contact_sub_type') {
 274       //coming up with a rule to set this is too complex let's not set it
 275       $handled = TRUE;
 276     }
 277
 278     // Pick an option value if needed
 279     if (!$handled && $fieldDef['type'] !== CRM_Utils_Type::T_BOOLEAN) {
 280       $options = $daoName::buildOptions($dbName, 'create');
 281       if ($options) {
 282         $this->$dbName = key($options);
 283         $handled = TRUE;
 284       }
 285     }
 286
 287     if (!$handled) {
 288       switch ($fieldDef['type']) {
 289         case CRM_Utils_Type::T_INT:
 290         case CRM_Utils_Type::T_FLOAT:
 291         case CRM_Utils_Type::T_MONEY:
 292           if (isset($fieldDef['precision'])) {
 293             // $object->$dbName = CRM_Utils_Number::createRandomDecimal($value['precision']);
 294             $this->$dbName = CRM_Utils_Number::createTruncatedDecimal($counter, $fieldDef['precision']);
 295           }
 296           else {
 297             $this->$dbName = $counter;
 298           }
 299           break;
 300
 301         case CRM_Utils_Type::T_BOOLEAN:
 302           if (isset($fieldDef['default'])) {
 303             $this->$dbName = $fieldDef['default'];
 304           }
 305           elseif ($fieldDef['name'] == 'is_deleted' || $fieldDef['name'] == 'is_test') {
 306             $this->$dbName = 0;
 307           }
 308           else {
 309             $this->$dbName = 1;
 310           }
 311           break;
 312
 313         case CRM_Utils_Type::T_DATE:
 314         case CRM_Utils_Type::T_DATE + CRM_Utils_Type::T_TIME:
 315           $this->$dbName = '19700101';
 316           if ($dbName == 'end_date') {
 317             // put this in the future
 318             $this->$dbName = '20200101';
 319           }
 320           break;
 321
 322         case CRM_Utils_Type::T_TIMESTAMP:
 323           $this->$dbName = '19700201000000';
 324           break;
 325
 326         case CRM_Utils_Type::T_TIME:
 327           CRM_Core_Error::fatal("T_TIME shouldn't be used.");
 328           //$object->$dbName='000000';
 329           //break;
 330         case CRM_Utils_Type::T_CCNUM:
 331           $this->$dbName = '4111 1111 1111 1111';
 332           break;
 333
 334         case CRM_Utils_Type::T_URL:
 335           $this->$dbName = 'http://www.civicrm.org';
 336           break;
 337
 338         case CRM_Utils_Type::T_STRING:
 339         case CRM_Utils_Type::T_BLOB:
 340         case CRM_Utils_Type::T_MEDIUMBLOB:
 341         case CRM_Utils_Type::T_TEXT:
 342         case CRM_Utils_Type::T_LONGTEXT:
 343         case CRM_Utils_Type::T_EMAIL:
 344         default:
 345           // WAS: if (isset($value['enumValues'])) {
 346           // TODO: see if this works with all pseudoconstants
 347           if (isset($fieldDef['pseudoconstant'], $fieldDef['pseudoconstant']['callback'])) {
 348             if (isset($fieldDef['default'])) {
 349               $this->$dbName = $fieldDef['default'];
 350             }
 351             else {
 352               $options = CRM_Core_PseudoConstant::get($daoName, $fieldName);
 353               if (is_array($options)) {
 354                 $this->$dbName = $options[0];
 355               }
 356               else {
 357                 $defaultValues = explode(',', $options);
 358                 $this->$dbName = $defaultValues[0];
 359               }
 360             }
 361           }
 362           else {
 363             $this->$dbName = $dbName . '_' . $counter;
 364             $maxlength = CRM_Utils_Array::value('maxlength', $fieldDef);
 365             if ($maxlength > 0 && strlen($this->$dbName) > $maxlength) {
 366               $this->$dbName = substr($this->$dbName, 0, $fieldDef['maxlength']);
 367             }
 368           }
 369       }
 370     }
 371   }
 372
 373   /**
 374    * Reset the DAO object.
 375    *
 376    * DAO is kinda crappy in that there is an unwritten rule of one query per DAO.
 377    *
 378    * We attempt to get around this crappy restriction by resetting some of DAO's internal fields. Use this with caution
 379    */
 380   public function reset() {
 381
 382     foreach (array_keys($this->table()) as $field) {
 383       unset($this->$field);
 384     }
 385
 386     /**
 387      * reset the various DB_DAO structures manually
 388      */
 389     $this->_query = [];
 390     $this->whereAdd();
 391     $this->selectAdd();
 392     $this->joinAdd();
 393   }
 394
 395   /**
 396    * @param string $tableName
 397    *
 398    * @return string
 399    */
 400   public static function getLocaleTableName($tableName) {
 401     global $dbLocale;
 402     if ($dbLocale) {
 403       $tables = CRM_Core_I18n_Schema::schemaStructureTables();
 404       if (in_array($tableName, $tables)) {
 405         return $tableName . $dbLocale;
 406       }
 407     }
 408     return $tableName;
 409   }
 410
 411   /**
 412    * Execute a query by the current DAO, localizing it along the way (if needed).
 413    *
 414    * @param string $query
 415    *   The SQL query for execution.
 416    * @param bool $i18nRewrite
 417    *   Whether to rewrite the query.
 418    *
 419    * @return object
 420    *   the current DAO object after the query execution
 421    */
 422   public function query($query, $i18nRewrite = TRUE) {
 423     // rewrite queries that should use $dbLocale-based views for multi-language installs
 424     global $dbLocale, $_DB_DATAOBJECT;
 425
 426     if (empty($_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5])) {
 427       // Will force connection to be populated per CRM-20541.
 428       new CRM_Core_DAO();
 429     }
 430
 431     $conn = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
 432     $orig_options = $conn->options;
 433     $this->_setDBOptions($this->_options);
 434
 435     if ($i18nRewrite and $dbLocale) {
 436       $query = CRM_Core_I18n_Schema::rewriteQuery($query);
 437     }
 438
 439     $ret = parent::query($query);
 440
 441     $this->_setDBOptions($orig_options);
 442     return $ret;
 443   }
 444
 445   /**
 446    * Static function to set the factory instance for this class.
 447    *
 448    * @param object $factory
 449    *   The factory application object.
 450    */
 451   public static function setFactory(&$factory) {
 452     self::$_factory = &$factory;
 453   }
 454
 455   /**
 456    * Factory method to instantiate a new object from a table name.
 457    *
 458    * @param string $table
 459    * @return \DataObject|\PEAR_Error
 460    */
 461   public function factory($table = '') {
 462     if (!isset(self::$_factory)) {
 463       return parent::factory($table);
 464     }
 465
 466     return self::$_factory->create($table);
 467   }
 468
 469   /**
 470    * Initialization for all DAO objects. Since we access DB_DO programatically
 471    * we need to set the links manually.
 472    */
 473   public function initialize() {
 474     $this->_connect();
 475     if (empty(Civi::$statics[__CLASS__]['init'])) {
 476       // CRM_Core_DAO::init() must be called before CRM_Core_DAO->initialize().
 477       // This occurs very early in bootstrap - error handlers may not be wired up.
 478       echo "Inconsistent system initialization sequence. Premature access of (" . get_class($this) . ")";
 479       CRM_Utils_System::civiExit();
 480     }
 481   }
 482
 483   /**
 484    * Defines the default key as 'id'.
 485    *
 486    * @return array
 487    */
 488   public function keys() {
 489     static $keys;
 490     if (!isset($keys)) {
 491       $keys = ['id'];
 492     }
 493     return $keys;
 494   }
 495
 496   /**
 497    * Tells DB_DataObject which keys use autoincrement.
 498    * 'id' is autoincrementing by default.
 499    *
 500    *
 501    * @return array
 502    */
 503   public function sequenceKey() {
 504     static $sequenceKeys;
 505     if (!isset($sequenceKeys)) {
 506       $sequenceKeys = ['id', TRUE];
 507     }
 508     return $sequenceKeys;
 509   }
 510
 511   /**
 512    * Returns list of FK relationships.
 513    *
 514    *
 515    * @return array
 516    *   Array of CRM_Core_Reference_Interface
 517    */
 518   public static function getReferenceColumns() {
 519     return [];
 520   }
 521
 522   /**
 523    * Returns all the column names of this table.
 524    *
 525    *
 526    * @return array
 527    */
 528   public static function &fields() {
 529     $result = NULL;
 530     return $result;
 531   }
 532
 533   /**
 534    * Get/set an associative array of table columns
 535    *
 536    * @return array
 537    *   (associative)
 538    */
 539   public function table() {
 540     $fields = $this->fields();
 541
 542     $table = [];
 543     if ($fields) {
 544       foreach ($fields as $name => $value) {
 545         $table[$value['name']] = $value['type'];
 546         if (!empty($value['required'])) {
 547           $table[$value['name']] += self::DB_DAO_NOTNULL;
 548         }
 549       }
 550     }
 551
 552     return $table;
 553   }
 554
 555   /**
 556    * Save DAO object.
 557    *
 558    * @param bool $hook
 559    *
 560    * @return CRM_Core_DAO
 561    */
 562   public function save($hook = TRUE) {
 563     if (!empty($this->id)) {
 564       $this->update();
 565
 566       if ($hook) {
 567         $event = new \Civi\Core\DAO\Event\PostUpdate($this);
 568         \Civi::service('dispatcher')->dispatch("civi.dao.postUpdate", $event);
 569       }
 570     }
 571     else {
 572       $this->insert();
 573
 574       if ($hook) {
 575         $event = new \Civi\Core\DAO\Event\PostUpdate($this);
 576         \Civi::service('dispatcher')->dispatch("civi.dao.postInsert", $event);
 577       }
 578     }
 579     $this->free();
 580
 581     if ($hook) {
 582       CRM_Utils_Hook::postSave($this);
 583     }
 584
 585     return $this;
 586   }
 587
 588   /**
 589    * Deletes items from table which match current objects variables.
 590    *
 591    * Returns the true on success
 592    *
 593    * for example
 594    *
 595    * Designed to be extended
 596    *
 597    * $object = new mytable();
 598    * $object->ID=123;
 599    * echo $object->delete(); // builds a conditon
 600    *
 601    * $object = new mytable();
 602    * $object->whereAdd('age > 12');
 603    * $object->limit(1);
 604    * $object->orderBy('age DESC');
 605    * $object->delete(true); // dont use object vars, use the conditions, limit and order.
 606    *
 607    * @param bool $useWhere (optional) If DB_DATAOBJECT_WHEREADD_ONLY is passed in then
 608    *             we will build the condition only using the whereAdd's.  Default is to
 609    *             build the condition only using the object parameters.
 610    *
 611    *     * @return mixed Int (No. of rows affected) on success, false on failure, 0 on no data affected
 612    */
 613   public function delete($useWhere = FALSE) {
 614     $preEvent = new \Civi\Core\DAO\Event\PreDelete($this);
 615     \Civi::service('dispatcher')->dispatch("civi.dao.preDelete", $preEvent);
 616
 617     $result = parent::delete($useWhere);
 618
 619     $event = new \Civi\Core\DAO\Event\PostDelete($this, $result);
 620     \Civi::service('dispatcher')->dispatch("civi.dao.postDelete", $event);
 621     $this->free();
 622
 623     return $result;
 624   }
 625
 626   /**
 627    * @param bool $created
 628    */
 629   public function log($created = FALSE) {
 630     static $cid = NULL;
 631
 632     if (!$this->getLog()) {
 633       return;
 634     }
 635
 636     if (!$cid) {
 637       $session = CRM_Core_Session::singleton();
 638       $cid = $session->get('userID');
 639     }
 640
 641     // return is we dont have handle to FK
 642     if (!$cid) {
 643       return;
 644     }
 645
 646     $dao = new CRM_Core_DAO_Log();
 647     $dao->entity_table = $this->getTableName();
 648     $dao->entity_id = $this->id;
 649     $dao->modified_id = $cid;
 650     $dao->modified_date = date("YmdHis");
 651     $dao->insert();
 652   }
 653
 654   /**
 655    * Given an associative array of name/value pairs, extract all the values
 656    * that belong to this object and initialize the object with said values
 657    *
 658    * @param array $params
 659    *   (reference ) associative array of name/value pairs.
 660    * @param bool $serializeArrays
 661    *   Should arrays that are passed in be serialised according to the metadata.
 662    *   Eventually this should be always true / gone, but in the interests of caution
 663    *   it is being grandfathered in. In general an array is not valid on the DAO
 664    *   but there may be instances where this function is called & then some handling
 665    *   takes place on the would-be array.
 666    *
 667    * @return bool
 668    *   Did we copy all null values into the object
 669    */
 670   public function copyValues(&$params, $serializeArrays = FALSE) {
 671     $fields = $this->fields();
 672     $allNull = TRUE;
 673     foreach ($fields as $name => $value) {
 674       $dbName = $value['name'];
 675       if (array_key_exists($dbName, $params)) {
 676         $pValue = $params[$dbName];
 677         $exists = TRUE;
 678       }
 679       elseif (array_key_exists($name, $params)) {
 680         $pValue = $params[$name];
 681         $exists = TRUE;
 682       }
 683       else {
 684         $exists = FALSE;
 685       }
 686
 687       // if there is no value then make the variable NULL
 688       if ($exists) {
 689         if ($pValue === '') {
 690           $this->$dbName = 'null';
 691         }
 692         elseif ($serializeArrays && is_array($pValue) && !empty($value['serialize'])) {
 693           $this->$dbName = CRM_Core_DAO::serializeField($pValue, $value['serialize']);
 694           $allNull = FALSE;
 695         }
 696         else {
 697           if (!$serializeArrays && is_array($pValue) && !empty($value['serialize'])) {
 698             Civi::log()->warning(ts('use copyParams to serialize arrays (' . __CLASS__ . '.' . $name . ')'), ['civi.tag' => 'deprecated']);
 699           }
 700           $maxLength = CRM_Utils_Array::value('maxlength', $value);
 701           if (!is_array($pValue) && $maxLength && mb_strlen($pValue) > $maxLength
 702             && empty($value['pseudoconstant'])
 703           ) {
 704             Civi::log()->warning(ts('A string for field $dbName has been truncated. The original string was %1', [CRM_Utils_Type::escape($pValue, 'String')]));
 705             // The string is too long - what to do what to do? Well losing data is generally bad so lets' truncate
 706             $pValue = CRM_Utils_String::ellipsify($pValue, $maxLength);
 707           }
 708           $this->$dbName = $pValue;
 709           $allNull = FALSE;
 710         }
 711       }
 712     }
 713     return $allNull;
 714   }
 715
 716   /**
 717    * Store all the values from this object in an associative array
 718    * this is a destructive store, calling function is responsible
 719    * for keeping sanity of id's.
 720    *
 721    * @param object $object
 722    *   The object that we are extracting data from.
 723    * @param array $values
 724    *   (reference ) associative array of name/value pairs.
 725    */
 726   public static function storeValues(&$object, &$values) {
 727     $fields = $object->fields();
 728     foreach ($fields as $name => $value) {
 729       $dbName = $value['name'];
 730       if (isset($object->$dbName) && $object->$dbName !== 'null') {
 731         $values[$dbName] = $object->$dbName;
 732         if ($name != $dbName) {
 733           $values[$name] = $object->$dbName;
 734         }
 735       }
 736     }
 737   }
 738
 739   /**
 740    * Create an attribute for this specific field. We only do this for strings and text
 741    *
 742    * @param array $field
 743    *   The field under task.
 744    *
 745    * @return array|null
 746    *   the attributes for the object
 747    */
 748   public static function makeAttribute($field) {
 749     if ($field) {
 750       if (CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_STRING) {
 751         $maxLength = CRM_Utils_Array::value('maxlength', $field);
 752         $size = CRM_Utils_Array::value('size', $field);
 753         if ($maxLength || $size) {
 754           $attributes = [];
 755           if ($maxLength) {
 756             $attributes['maxlength'] = $maxLength;
 757           }
 758           if ($size) {
 759             $attributes['size'] = $size;
 760           }
 761           return $attributes;
 762         }
 763       }
 764       elseif (CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_TEXT) {
 765         $rows = CRM_Utils_Array::value('rows', $field);
 766         if (!isset($rows)) {
 767           $rows = 2;
 768         }
 769         $cols = CRM_Utils_Array::value('cols', $field);
 770         if (!isset($cols)) {
 771           $cols = 80;
 772         }
 773
 774         $attributes = [];
 775         $attributes['rows'] = $rows;
 776         $attributes['cols'] = $cols;
 777         return $attributes;
 778       }
 779       elseif (CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_INT || CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_FLOAT || CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_MONEY) {
 780         $attributes['size'] = 6;
 781         $attributes['maxlength'] = 14;
 782         return $attributes;
 783       }
 784     }
 785     return NULL;
 786   }
 787
 788   /**
 789    * Get the size and maxLength attributes for this text field.
 790    * (or for all text fields) in the DAO object.
 791    *
 792    * @param string $class
 793    *   Name of DAO class.
 794    * @param string $fieldName
 795    *   Field that i'm interested in or null if.
 796    *                          you want the attributes for all DAO text fields
 797    *
 798    * @return array
 799    *   assoc array of name => attribute pairs
 800    */
 801   public static function getAttribute($class, $fieldName = NULL) {
 802     $object = new $class();
 803     $fields = $object->fields();
 804     if ($fieldName != NULL) {
 805       $field = CRM_Utils_Array::value($fieldName, $fields);
 806       return self::makeAttribute($field);
 807     }
 808     else {
 809       $attributes = [];
 810       foreach ($fields as $name => $field) {
 811         $attribute = self::makeAttribute($field);
 812         if ($attribute) {
 813           $attributes[$name] = $attribute;
 814         }
 815       }
 816
 817       if (!empty($attributes)) {
 818         return $attributes;
 819       }
 820     }
 821     return NULL;
 822   }
 823
 824   /**
 825    * Check if there is a record with the same name in the db.
 826    *
 827    * @param string $value
 828    *   The value of the field we are checking.
 829    * @param string $daoName
 830    *   The dao object name.
 831    * @param string $daoID
 832    *   The id of the object being updated. u can change your name.
 833    *                          as long as there is no conflict
 834    * @param string $fieldName
 835    *   The name of the field in the DAO.
 836    *
 837    * @param string $domainID
 838    *   The id of the domain.  Object exists only for the given domain.
 839    *
 840    * @return bool
 841    *   true if object exists
 842    */
 843   public static function objectExists($value, $daoName, $daoID, $fieldName = 'name', $domainID = NULL) {
 844     $object = new $daoName();
 845     $object->$fieldName = $value;
 846     if ($domainID) {
 847       $object->domain_id = $domainID;
 848     }
 849
 850     if ($object->find(TRUE)) {
 851       return ($daoID && $object->id == $daoID) ? TRUE : FALSE;
 852     }
 853     else {
 854       return TRUE;
 855     }
 856   }
 857
 858   /**
 859    * Check if there is a given column in a specific table.
 860    *
 861    * @deprecated
 862    * @see CRM_Core_BAO_SchemaHandler::checkIfFieldExists
 863    *
 864    * @param string $tableName
 865    * @param string $columnName
 866    * @param bool $i18nRewrite
 867    *   Whether to rewrite the query on multilingual setups.
 868    *
 869    * @return bool
 870    *   true if exists, else false
 871    */
 872   public static function checkFieldExists($tableName, $columnName, $i18nRewrite = TRUE) {
 873     return CRM_Core_BAO_SchemaHandler::checkIfFieldExists($tableName, $columnName, $i18nRewrite);
 874   }
 875
 876   /**
 877    * Scans all the tables using a slow query and table name.
 878    *
 879    * @return array
 880    */
 881   public static function getTableNames() {
 882     $dao = CRM_Core_DAO::executeQuery(
 883       "SELECT TABLE_NAME
 884        FROM information_schema.TABLES
 885        WHERE TABLE_SCHEMA = '" . CRM_Core_DAO::getDatabaseName() . "'
 886          AND TABLE_NAME LIKE 'civicrm_%'
 887          AND TABLE_NAME NOT LIKE 'civicrm_import_job_%'
 888          AND TABLE_NAME NOT LIKE '%_temp%'
 889       ");
 890
 891     while ($dao->fetch()) {
 892       $values[] = $dao->TABLE_NAME;
 893     }
 894     return $values;
 895   }
 896
 897   /**
 898    * @param int $maxTablesToCheck
 899    *
 900    * @return bool
 901    */
 902   public static function isDBMyISAM($maxTablesToCheck = 10) {
 903     return CRM_Core_DAO::singleValueQuery(
 904       "SELECT count(*)
 905        FROM information_schema.TABLES
 906        WHERE ENGINE = 'MyISAM'
 907          AND TABLE_SCHEMA = '" . CRM_Core_DAO::getDatabaseName() . "'
 908          AND TABLE_NAME LIKE 'civicrm_%'
 909          AND TABLE_NAME NOT LIKE 'civicrm_import_job_%'
 910          AND TABLE_NAME NOT LIKE '%_temp%'
 911          AND TABLE_NAME NOT LIKE 'civicrm_tmp_%'
 912       ");
 913   }
 914
 915   /**
 916    * Get the name of the CiviCRM database.
 917    *
 918    * @return string
 919    */
 920   public static function getDatabaseName() {
 921     $daoObj = new CRM_Core_DAO();
 922     return $daoObj->database();
 923   }
 924
 925   /**
 926    * Checks if a constraint exists for a specified table.
 927    *
 928    * @param string $tableName
 929    * @param string $constraint
 930    *
 931    * @return bool
 932    *   true if constraint exists, false otherwise
 933    */
 934   public static function checkConstraintExists($tableName, $constraint) {
 935     static $show = [];
 936
 937     if (!array_key_exists($tableName, $show)) {
 938       $query = "SHOW CREATE TABLE $tableName";
 939       $dao = CRM_Core_DAO::executeQuery($query);
 940
 941       if (!$dao->fetch()) {
 942         CRM_Core_Error::fatal();
 943       }
 944
 945       $show[$tableName] = $dao->Create_Table;
 946     }
 947
 948     return preg_match("/\b$constraint\b/i", $show[$tableName]) ? TRUE : FALSE;
 949   }
 950
 951   /**
 952    * Checks if CONSTRAINT keyword exists for a specified table.
 953    *
 954    * @param array $tables
 955    *
 956    * @throws Exception
 957    *
 958    * @return bool
 959    *   true if CONSTRAINT keyword exists, false otherwise
 960    */
 961   public static function schemaRequiresRebuilding($tables = ["civicrm_contact"]) {
 962     $show = [];
 963     foreach ($tables as $tableName) {
 964       if (!array_key_exists($tableName, $show)) {
 965         $query = "SHOW CREATE TABLE $tableName";
 966         $dao = CRM_Core_DAO::executeQuery($query);
 967
 968         if (!$dao->fetch()) {
 969           CRM_Core_Error::fatal();
 970         }
 971
 972         $show[$tableName] = $dao->Create_Table;
 973       }
 974
 975       $result = preg_match("/\bCONSTRAINT\b\s/i", $show[$tableName]) ? TRUE : FALSE;
 976       if ($result == TRUE) {
 977         continue;
 978       }
 979       else {
 980         return FALSE;
 981       }
 982     }
 983     return TRUE;
 984   }
 985
 986   /**
 987    * Checks if the FK constraint name is in the format 'FK_tableName_columnName'
 988    * for a specified column of a table.
 989    *
 990    * @param string $tableName
 991    * @param string $columnName
 992    *
 993    * @return bool
 994    *   true if in format, false otherwise
 995    */
 996   public static function checkFKConstraintInFormat($tableName, $columnName) {
 997     static $show = [];
 998
 999     if (!array_key_exists($tableName, $show)) {
1000       $query = "SHOW CREATE TABLE $tableName";
1001       $dao = CRM_Core_DAO::executeQuery($query);
1002
1003       if (!$dao->fetch()) {
1004         CRM_Core_Error::fatal();
1005       }
1006
1007       $show[$tableName] = $dao->Create_Table;
1008     }
1009     $constraint = "`FK_{$tableName}_{$columnName}`";
1010     $pattern = "/\bCONSTRAINT\b\s+%s\s+\bFOREIGN\s+KEY\b\s/i";
1011     return preg_match(sprintf($pattern, $constraint), $show[$tableName]) ? TRUE : FALSE;
1012   }
1013
1014   /**
1015    * Check whether a specific column in a specific table has always the same value.
1016    *
1017    * @param string $tableName
1018    * @param string $columnName
1019    * @param string $columnValue
1020    *
1021    * @return bool
1022    *   true if the value is always $columnValue, false otherwise
1023    */
1024   public static function checkFieldHasAlwaysValue($tableName, $columnName, $columnValue) {
1025     $query = "SELECT * FROM $tableName WHERE $columnName != '$columnValue'";
1026     $dao = CRM_Core_DAO::executeQuery($query);
1027     $result = $dao->fetch() ? FALSE : TRUE;
1028     return $result;
1029   }
1030
1031   /**
1032    * Check whether a specific column in a specific table is always NULL.
1033    *
1034    * @param string $tableName
1035    * @param string $columnName
1036    *
1037    * @return bool
1038    *   true if if the value is always NULL, false otherwise
1039    */
1040   public static function checkFieldIsAlwaysNull($tableName, $columnName) {
1041     $query = "SELECT * FROM $tableName WHERE $columnName IS NOT NULL";
1042     $dao = CRM_Core_DAO::executeQuery($query);
1043     $result = $dao->fetch() ? FALSE : TRUE;
1044     return $result;
1045   }
1046
1047   /**
1048    * Check if there is a given table in the database.
1049    *
1050    * @param string $tableName
1051    *
1052    * @return bool
1053    *   true if exists, else false
1054    */
1055   public static function checkTableExists($tableName) {
1056     $query = "
1057 SHOW TABLES
1058 LIKE %1
1059 ";
1060     $params = [1 => [$tableName, 'String']];
1061
1062     $dao = CRM_Core_DAO::executeQuery($query, $params);
1063     $result = $dao->fetch() ? TRUE : FALSE;
1064     return $result;
1065   }
1066
1067   /**
1068    * @param $version
1069    *
1070    * @return bool
1071    */
1072   public function checkVersion($version) {
1073     $query = "
1074 SELECT version
1075 FROM   civicrm_domain
1076 ";
1077     $dbVersion = CRM_Core_DAO::singleValueQuery($query);
1078     return trim($version) == trim($dbVersion) ? TRUE : FALSE;
1079   }
1080
1081   /**
1082    * Find a DAO object for the given ID and return it.
1083    *
1084    * @param int $id
1085    *   Id of the DAO object being searched for.
1086    *
1087    * @return CRM_Core_DAO
1088    *   Object of the type of the class that called this function.
1089    *
1090    * @throws Exception
1091    */
1092   public static function findById($id) {
1093     $object = new static();
1094     $object->id = $id;
1095     if (!$object->find(TRUE)) {
1096       throw new Exception("Unable to find a " . get_called_class() . " with id {$id}.");
1097     }
1098     return $object;
1099   }
1100
1101   /**
1102    * Returns all results as array-encoded records.
1103    *
1104    * @return array
1105    */
1106   public function fetchAll() {
1107     $result = [];
1108     while ($this->fetch()) {
1109       $result[] = $this->toArray();
1110     }
1111     return $result;
1112   }
1113
1114   /**
1115    * Return the results as PHP generator.
1116    *
1117    * @param string $type
1118    *   Whether the generator yields 'dao' objects or 'array's.
1119    */
1120   public function fetchGenerator($type = 'dao') {
1121     while ($this->fetch()) {
1122       switch ($type) {
1123         case 'dao':
1124           yield $this;
1125           break;
1126
1127         case 'array':
1128           yield $this->toArray();
1129           break;
1130
1131         default:
1132           throw new \RuntimeException("Invalid record type ($type)");
1133       }
1134     }
1135   }
1136
1137   /**
1138    * Returns a singular value.
1139    *
1140    * @return mixed|NULL
1141    */
1142   public function fetchValue() {
1143     $result = $this->getDatabaseResult();
1144     $row = $result->fetchRow();
1145     $ret = NULL;
1146     if ($row) {
1147       $ret = $row[0];
1148     }
1149     $this->free();
1150     return $ret;
1151   }
1152
1153   /**
1154    * Get all the result records as mapping between columns.
1155    *
1156    * @param string $keyColumn
1157    *   Ex: "name"
1158    * @param string $valueColumn
1159    *   Ex: "label"
1160    * @return array
1161    *   Ex: ["foo" => "The Foo Bar", "baz" => "The Baz Qux"]
1162    */
1163   public function fetchMap($keyColumn, $valueColumn) {
1164     $result = [];
1165     while ($this->fetch()) {
1166       $result[$this->{$keyColumn}] = $this->{$valueColumn};
1167     }
1168     return $result;
1169   }
1170
1171   /**
1172    * Given a DAO name, a column name and a column value, find the record and GET the value of another column in that record
1173    *
1174    * @param string $daoName
1175    *   Name of the DAO (Example: CRM_Contact_DAO_Contact to retrieve value from a contact).
1176    * @param int $searchValue
1177    *   Value of the column you want to search by.
1178    * @param string $returnColumn
1179    *   Name of the column you want to GET the value of.
1180    * @param string $searchColumn
1181    *   Name of the column you want to search by.
1182    * @param bool $force
1183    *   Skip use of the cache.
1184    *
1185    * @return string|null
1186    *   Value of $returnColumn in the retrieved record
1187    */
1188   public static function getFieldValue($daoName, $searchValue, $returnColumn = 'name', $searchColumn = 'id', $force = FALSE) {
1189     if (
1190       empty($searchValue) ||
1191       trim(strtolower($searchValue)) == 'null'
1192     ) {
1193       // adding this here since developers forget to check for an id
1194       // or for the 'null' (which is a bad DAO kludge)
1195       // and hence we get the first value in the db
1196       CRM_Core_Error::fatal();
1197     }
1198
1199     $cacheKey = "{$daoName}:{$searchValue}:{$returnColumn}:{$searchColumn}";
1200     if (self::$_dbColumnValueCache === NULL) {
1201       self::$_dbColumnValueCache = [];
1202     }
1203
1204     if (!array_key_exists($cacheKey, self::$_dbColumnValueCache) || $force) {
1205       $object = new $daoName();
1206       $object->$searchColumn = $searchValue;
1207       $object->selectAdd();
1208       $object->selectAdd($returnColumn);
1209
1210       $result = NULL;
1211       if ($object->find(TRUE)) {
1212         $result = $object->$returnColumn;
1213       }
1214       $object->free();
1215
1216       self::$_dbColumnValueCache[$cacheKey] = $result;
1217     }
1218     return self::$_dbColumnValueCache[$cacheKey];
1219   }
1220
1221   /**
1222    * Given a DAO name, a column name and a column value, find the record and SET the value of another column in that record
1223    *
1224    * @param string $daoName
1225    *   Name of the DAO (Example: CRM_Contact_DAO_Contact to retrieve value from a contact).
1226    * @param int $searchValue
1227    *   Value of the column you want to search by.
1228    * @param string $setColumn
1229    *   Name of the column you want to SET the value of.
1230    * @param string $setValue
1231    *   SET the setColumn to this value.
1232    * @param string $searchColumn
1233    *   Name of the column you want to search by.
1234    *
1235    * @return bool
1236    *   true if we found and updated the object, else false
1237    */
1238   public static function setFieldValue($daoName, $searchValue, $setColumn, $setValue, $searchColumn = 'id') {
1239     $object = new $daoName();
1240     $object->selectAdd();
1241     $object->selectAdd("$searchColumn, $setColumn");
1242     $object->$searchColumn = $searchValue;
1243     $result = FALSE;
1244     if ($object->find(TRUE)) {
1245       $object->$setColumn = $setValue;
1246       if ($object->save()) {
1247         $result = TRUE;
1248       }
1249     }
1250     $object->free();
1251     return $result;
1252   }
1253
1254   /**
1255    * Get sort string.
1256    *
1257    * @param array|object $sort either array or CRM_Utils_Sort
1258    * @param string $default
1259    *   Default sort value.
1260    *
1261    * @return string
1262    */
1263   public static function getSortString($sort, $default = NULL) {
1264     // check if sort is of type CRM_Utils_Sort
1265     if (is_a($sort, 'CRM_Utils_Sort')) {
1266       return $sort->orderBy();
1267     }
1268
1269     $sortString = '';
1270
1271     // is it an array specified as $field => $sortDirection ?
1272     if ($sort) {
1273       foreach ($sort as $k => $v) {
1274         $sortString .= "$k $v,";
1275       }
1276       return rtrim($sortString, ',');
1277     }
1278     return $default;
1279   }
1280
1281   /**
1282    * Fetch object based on array of properties.
1283    *
1284    * @param string $daoName
1285    *   Name of the dao object.
1286    * @param array $params
1287    *   (reference ) an assoc array of name/value pairs.
1288    * @param array $defaults
1289    *   (reference ) an assoc array to hold the flattened values.
1290    * @param array $returnProperities
1291    *   An assoc array of fields that need to be returned, eg array( 'first_name', 'last_name').
1292    *
1293    * @return object
1294    *   an object of type referenced by daoName
1295    */
1296   public static function commonRetrieve($daoName, &$params, &$defaults, $returnProperities = NULL) {
1297     $object = new $daoName();
1298     $object->copyValues($params);
1299
1300     // return only specific fields if returnproperties are sent
1301     if (!empty($returnProperities)) {
1302       $object->selectAdd();
1303       $object->selectAdd(implode(',', $returnProperities));
1304     }
1305
1306     if ($object->find(TRUE)) {
1307       self::storeValues($object, $defaults);
1308       return $object;
1309     }
1310     return NULL;
1311   }
1312
1313   /**
1314    * Delete the object records that are associated with this contact.
1315    *
1316    * @param string $daoName
1317    *   Name of the dao object.
1318    * @param int $contactId
1319    *   Id of the contact to delete.
1320    */
1321   public static function deleteEntityContact($daoName, $contactId) {
1322     $object = new $daoName();
1323
1324     $object->entity_table = 'civicrm_contact';
1325     $object->entity_id = $contactId;
1326     $object->delete();
1327   }
1328
1329   /**
1330    * Execute an unbuffered query.
1331    *
1332    * This is a wrapper around new functionality exposed with CRM-17748.
1333    *
1334    * @param string $query query to be executed
1335    *
1336    * @param array $params
1337    * @param bool $abort
1338    * @param null $daoName
1339    * @param bool $freeDAO
1340    * @param bool $i18nRewrite
1341    * @param bool $trapException
1342    *
1343    * @return CRM_Core_DAO
1344    *   Object that points to an unbuffered result set
1345    */
1346   public static function executeUnbufferedQuery(
1347     $query,
1348     $params = [],
1349     $abort = TRUE,
1350     $daoName = NULL,
1351     $freeDAO = FALSE,
1352     $i18nRewrite = TRUE,
1353     $trapException = FALSE
1354   ) {
1355
1356     return self::executeQuery(
1357       $query,
1358       $params,
1359       $abort,
1360       $daoName,
1361       $freeDAO,
1362       $i18nRewrite,
1363       $trapException,
1364       ['result_buffering' => 0]
1365     );
1366   }
1367
1368   /**
1369    * Execute a query.
1370    *
1371    * @param string $query
1372    *   Query to be executed.
1373    *
1374    * @param array $params
1375    * @param bool $abort
1376    * @param null $daoName
1377    * @param bool $freeDAO
1378    * @param bool $i18nRewrite
1379    * @param bool $trapException
1380    * @param array $options
1381    *
1382    * @return CRM_Core_DAO|object
1383    *   object that holds the results of the query
1384    *   NB - if this is defined as just returning a DAO phpstorm keeps pointing
1385    *   out all the properties that are not part of the DAO
1386    */
1387   public static function &executeQuery(
1388     $query,
1389     $params = [],
1390     $abort = TRUE,
1391     $daoName = NULL,
1392     $freeDAO = FALSE,
1393     $i18nRewrite = TRUE,
1394     $trapException = FALSE,
1395     $options = []
1396   ) {
1397     $queryStr = self::composeQuery($query, $params, $abort);
1398
1399     if (!$daoName) {
1400       $dao = new CRM_Core_DAO();
1401     }
1402     else {
1403       $dao = new $daoName();
1404     }
1405
1406     if ($trapException) {
1407       $errorScope = CRM_Core_TemporaryErrorScope::ignoreException();
1408     }
1409
1410     if ($dao->isValidOption($options)) {
1411       $dao->setOptions($options);
1412     }
1413
1414     $result = $dao->query($queryStr, $i18nRewrite);
1415
1416     // since it is unbuffered, ($dao->N==0) is true.  This blocks the standard fetch() mechanism.
1417     if (CRM_Utils_Array::value('result_buffering', $options) === 0) {
1418       $dao->N = TRUE;
1419     }
1420
1421     if (is_a($result, 'DB_Error')) {
1422       return $result;
1423     }
1424
1425     if ($freeDAO ||
1426       preg_match('/^(insert|update|delete|create|drop|replace)/i', $queryStr)
1427     ) {
1428       // we typically do this for insert/update/delete statements OR if explicitly asked to
1429       // free the dao
1430     }
1431     return $dao;
1432   }
1433
1434   /**
1435    * Wrapper to validate internal DAO options before passing to DB_mysql/DB_Common level
1436    *
1437    * @param array $options
1438    *
1439    * @return bool
1440    *   Provided options are valid
1441    */
1442   public function isValidOption($options) {
1443     $isValid = FALSE;
1444     $validOptions = [
1445       'result_buffering',
1446       'persistent',
1447       'ssl',
1448       'portability',
1449     ];
1450
1451     if (empty($options)) {
1452       return $isValid;
1453     }
1454
1455     foreach (array_keys($options) as $option) {
1456       if (!in_array($option, $validOptions)) {
1457         return FALSE;
1458       }
1459       $isValid = TRUE;
1460     }
1461
1462     return $isValid;
1463   }
1464
1465   /**
1466    * Execute a query and get the single result.
1467    *
1468    * @param string $query
1469    *   Query to be executed.
1470    * @param array $params
1471    * @param bool $abort
1472    * @param bool $i18nRewrite
1473    * @return string|null
1474    *   the result of the query if any
1475    *
1476    */
1477   public static function &singleValueQuery(
1478     $query,
1479     $params = [],
1480     $abort = TRUE,
1481     $i18nRewrite = TRUE
1482   ) {
1483     $queryStr = self::composeQuery($query, $params, $abort);
1484
1485     static $_dao = NULL;
1486
1487     if (!$_dao) {
1488       $_dao = new CRM_Core_DAO();
1489     }
1490
1491     $_dao->query($queryStr, $i18nRewrite);
1492
1493     $result = $_dao->getDatabaseResult();
1494     $ret = NULL;
1495     if ($result) {
1496       $row = $result->fetchRow();
1497       if ($row) {
1498         $ret = $row[0];
1499       }
1500     }
1501     $_dao->free();
1502     return $ret;
1503   }
1504
1505   /**
1506    * Compose the query by merging the parameters into it.
1507    *
1508    * @param string $query
1509    * @param array $params
1510    * @param bool $abort
1511    *
1512    * @return string
1513    * @throws Exception
1514    */
1515   public static function composeQuery($query, $params, $abort = TRUE) {
1516     $tr = [];
1517     foreach ($params as $key => $item) {
1518       if (is_numeric($key)) {
1519         if (CRM_Utils_Type::validate($item[0], $item[1]) !== NULL) {
1520           $item[0] = self::escapeString($item[0]);
1521           if ($item[1] == 'String' ||
1522             $item[1] == 'Memo' ||
1523             $item[1] == 'Link'
1524           ) {
1525             // Support class constants stipulating wildcard characters and/or
1526             // non-quoting of strings. Also support legacy code which may be
1527             // passing in TRUE or 1 for $item[2], which used to indicate the
1528             // use of wildcard characters.
1529             if (!empty($item[2])) {
1530               if ($item[2] & CRM_Core_DAO::QUERY_FORMAT_WILDCARD || $item[2] === TRUE) {
1531                 $item[0] = "'%{$item[0]}%'";
1532               }
1533               elseif (!($item[2] & CRM_Core_DAO::QUERY_FORMAT_NO_QUOTES)) {
1534                 $item[0] = "'{$item[0]}'";
1535               }
1536             }
1537             else {
1538               $item[0] = "'{$item[0]}'";
1539             }
1540           }
1541
1542           if (($item[1] == 'Date' || $item[1] == 'Timestamp') &&
1543             strlen($item[0]) == 0
1544           ) {
1545             $item[0] = 'null';
1546           }
1547
1548           $tr['%' . $key] = $item[0];
1549         }
1550         elseif ($abort) {
1551           CRM_Core_Error::fatal("{$item[0]} is not of type {$item[1]}");
1552         }
1553       }
1554     }
1555
1556     return strtr($query, $tr);
1557   }
1558
1559   /**
1560    * @param null $ids
1561    */
1562   public static function freeResult($ids = NULL) {
1563     global $_DB_DATAOBJECT;
1564
1565     if (!$ids) {
1566       if (!$_DB_DATAOBJECT ||
1567         !isset($_DB_DATAOBJECT['RESULTS'])
1568       ) {
1569         return;
1570       }
1571       $ids = array_keys($_DB_DATAOBJECT['RESULTS']);
1572     }
1573
1574     foreach ($ids as $id) {
1575       if (isset($_DB_DATAOBJECT['RESULTS'][$id])) {
1576         $_DB_DATAOBJECT['RESULTS'][$id]->free();
1577         unset($_DB_DATAOBJECT['RESULTS'][$id]);
1578       }
1579
1580       if (isset($_DB_DATAOBJECT['RESULTFIELDS'][$id])) {
1581         unset($_DB_DATAOBJECT['RESULTFIELDS'][$id]);
1582       }
1583     }
1584   }
1585
1586   /**
1587    * Make a shallow copy of an object and all the fields in the object.
1588    *
1589    * @param string $daoName
1590    *   Name of the dao.
1591    * @param array $criteria
1592    *   Array of all the fields & values.
1593    *   on which basis to copy
1594    * @param array $newData
1595    *   Array of all the fields & values.
1596    *   to be copied besides the other fields
1597    * @param string $fieldsFix
1598    *   Array of fields that you want to prefix/suffix/replace.
1599    * @param string $blockCopyOfDependencies
1600    *   Fields that you want to block from.
1601    *   getting copied
1602    *
1603    * @return CRM_Core_DAO
1604    *   the newly created copy of the object
1605    */
1606   public static function &copyGeneric($daoName, $criteria, $newData = NULL, $fieldsFix = NULL, $blockCopyOfDependencies = NULL) {
1607     $object = new $daoName();
1608     if (!$newData) {
1609       $object->id = $criteria['id'];
1610     }
1611     else {
1612       foreach ($criteria as $key => $value) {
1613         $object->$key = $value;
1614       }
1615     }
1616
1617     $object->find();
1618     while ($object->fetch()) {
1619
1620       // all the objects except with $blockCopyOfDependencies set
1621       // be copied - addresses #CRM-1962
1622
1623       if ($blockCopyOfDependencies && $object->$blockCopyOfDependencies) {
1624         break;
1625       }
1626
1627       $newObject = new $daoName();
1628
1629       $fields = $object->fields();
1630       if (!is_array($fieldsFix)) {
1631         $fieldsToPrefix = [];
1632         $fieldsToSuffix = [];
1633         $fieldsToReplace = [];
1634       }
1635       if (!empty($fieldsFix['prefix'])) {
1636         $fieldsToPrefix = $fieldsFix['prefix'];
1637       }
1638       if (!empty($fieldsFix['suffix'])) {
1639         $fieldsToSuffix = $fieldsFix['suffix'];
1640       }
1641       if (!empty($fieldsFix['replace'])) {
1642         $fieldsToReplace = $fieldsFix['replace'];
1643       }
1644
1645       foreach ($fields as $name => $value) {
1646         if ($name == 'id' || $value['name'] == 'id') {
1647           // copy everything but the id!
1648           continue;
1649         }
1650
1651         $dbName = $value['name'];
1652         $type = CRM_Utils_Type::typeToString($value['type']);
1653         $newObject->$dbName = $object->$dbName;
1654         if (isset($fieldsToPrefix[$dbName])) {
1655           $newObject->$dbName = $fieldsToPrefix[$dbName] . $newObject->$dbName;
1656         }
1657         if (isset($fieldsToSuffix[$dbName])) {
1658           $newObject->$dbName .= $fieldsToSuffix[$dbName];
1659         }
1660         if (isset($fieldsToReplace[$dbName])) {
1661           $newObject->$dbName = $fieldsToReplace[$dbName];
1662         }
1663
1664         if ($type == 'Timestamp' || $type == 'Date') {
1665           $newObject->$dbName = CRM_Utils_Date::isoToMysql($newObject->$dbName);
1666         }
1667
1668         if ($newData) {
1669           $newObject->copyValues($newData);
1670         }
1671       }
1672       $newObject->save();
1673       if (!empty($newData['custom'])) {
1674         CRM_Core_BAO_CustomValueTable::store($newData['custom'], $newObject::getTableName(), $newObject->id);
1675       }
1676       CRM_Utils_Hook::post('create', CRM_Core_DAO_AllCoreTables::getBriefName($daoName), $newObject->id, $newObject);
1677     }
1678
1679     return $newObject;
1680   }
1681
1682   /**
1683    * Cascade update through related entities.
1684    *
1685    * @param string $daoName
1686    * @param $fromId
1687    * @param $toId
1688    * @param array $newData
1689    *
1690    * @return CRM_Core_DAO|null
1691    */
1692   public static function cascadeUpdate($daoName, $fromId, $toId, $newData = []) {
1693     $object = new $daoName();
1694     $object->id = $fromId;
1695
1696     if ($object->find(TRUE)) {
1697       $newObject = new $daoName();
1698       $newObject->id = $toId;
1699
1700       if ($newObject->find(TRUE)) {
1701         $fields = $object->fields();
1702         foreach ($fields as $name => $value) {
1703           if ($name == 'id' || $value['name'] == 'id') {
1704             // copy everything but the id!
1705             continue;
1706           }
1707
1708           $colName = $value['name'];
1709           $newObject->$colName = $object->$colName;
1710
1711           if (substr($name, -5) == '_date' ||
1712             substr($name, -10) == '_date_time'
1713           ) {
1714             $newObject->$colName = CRM_Utils_Date::isoToMysql($newObject->$colName);
1715           }
1716         }
1717         foreach ($newData as $k => $v) {
1718           $newObject->$k = $v;
1719         }
1720         $newObject->save();
1721         return $newObject;
1722       }
1723     }
1724     return NULL;
1725   }
1726
1727   /**
1728    * Given the component id, compute the contact id
1729    * since its used for things like send email
1730    *
1731    * @param $componentIDs
1732    * @param string $tableName
1733    * @param string $idField
1734    *
1735    * @return array
1736    */
1737   public static function getContactIDsFromComponent($componentIDs, $tableName, $idField = 'id') {
1738     $contactIDs = [];
1739
1740     if (empty($componentIDs)) {
1741       return $contactIDs;
1742     }
1743
1744     $IDs = implode(',', $componentIDs);
1745     $query = "
1746 SELECT contact_id
1747   FROM $tableName
1748  WHERE $idField IN ( $IDs )
1749 ";
1750
1751     $dao = CRM_Core_DAO::executeQuery($query);
1752     while ($dao->fetch()) {
1753       $contactIDs[] = $dao->contact_id;
1754     }
1755     return $contactIDs;
1756   }
1757
1758   /**
1759    * Fetch object based on array of properties.
1760    *
1761    * @param string $daoName
1762    *   Name of the dao object.
1763    * @param string $fieldIdName
1764    * @param int $fieldId
1765    * @param $details
1766    * @param array $returnProperities
1767    *   An assoc array of fields that need to be returned, eg array( 'first_name', 'last_name').
1768    *
1769    * @return object
1770    *   an object of type referenced by daoName
1771    */
1772   public static function commonRetrieveAll($daoName, $fieldIdName = 'id', $fieldId, &$details, $returnProperities = NULL) {
1773     require_once str_replace('_', DIRECTORY_SEPARATOR, $daoName) . ".php";
1774     $object = new $daoName();
1775     $object->$fieldIdName = $fieldId;
1776
1777     // return only specific fields if returnproperties are sent
1778     if (!empty($returnProperities)) {
1779       $object->selectAdd();
1780       $object->selectAdd('id');
1781       $object->selectAdd(implode(',', $returnProperities));
1782     }
1783
1784     $object->find();
1785     while ($object->fetch()) {
1786       $defaults = [];
1787       self::storeValues($object, $defaults);
1788       $details[$object->id] = $defaults;
1789     }
1790
1791     return $details;
1792   }
1793
1794   /**
1795    * Drop all CiviCRM tables.
1796    *
1797    * @throws \CRM_Exception
1798    */
1799   public static function dropAllTables() {
1800
1801     // first drop all the custom tables we've created
1802     CRM_Core_BAO_CustomGroup::dropAllTables();
1803
1804     // drop all multilingual views
1805     CRM_Core_I18n_Schema::dropAllViews();
1806
1807     CRM_Utils_File::sourceSQLFile(CIVICRM_DSN,
1808       dirname(__FILE__) . DIRECTORY_SEPARATOR .
1809       '..' . DIRECTORY_SEPARATOR .
1810       '..' . DIRECTORY_SEPARATOR .
1811       'sql' . DIRECTORY_SEPARATOR .
1812       'civicrm_drop.mysql'
1813     );
1814   }
1815
1816   /**
1817    * @param $string
1818    *
1819    * @return string
1820    */
1821   public static function escapeString($string) {
1822     static $_dao = NULL;
1823     if (!$_dao) {
1824       // If this is an atypical case (e.g. preparing .sql file before CiviCRM
1825       // has been installed), then we fallback DB-less str_replace escaping, as
1826       // we can't use mysqli_real_escape_string, as there is no DB connection.
1827       // Note: In typical usage, escapeString() will only check one conditional
1828       // ("if !$_dao") rather than two conditionals ("if !defined(DSN)")
1829       if (!defined('CIVICRM_DSN')) {
1830         // See http://php.net/manual/en/mysqli.real-escape-string.php for the
1831         // list of characters mysqli_real_escape_string escapes.
1832         $search = ["\\", "\x00", "\n", "\r", "'", '"', "\x1a"];
1833         $replace = ["\\\\", "\\0", "\\n", "\\r", "\'", '\"', "\\Z"];
1834         return str_replace($search, $replace, $string);
1835       }
1836       $_dao = new CRM_Core_DAO();
1837     }
1838     return $_dao->escape($string);
1839   }
1840
1841   /**
1842    * Escape a list of strings for use with "WHERE X IN (...)" queries.
1843    *
1844    * @param array $strings
1845    * @param string $default
1846    *   the value to use if $strings has no elements.
1847    * @return string
1848    *   eg "abc","def","ghi"
1849    */
1850   public static function escapeStrings($strings, $default = NULL) {
1851     static $_dao = NULL;
1852     if (!$_dao) {
1853       $_dao = new CRM_Core_DAO();
1854     }
1855
1856     if (empty($strings)) {
1857       return $default;
1858     }
1859
1860     $escapes = array_map([$_dao, 'escape'], $strings);
1861     return '"' . implode('","', $escapes) . '"';
1862   }
1863
1864   /**
1865    * @param $string
1866    *
1867    * @return string
1868    */
1869   public static function escapeWildCardString($string) {
1870     // CRM-9155
1871     // ensure we escape the single characters % and _ which are mysql wild
1872     // card characters and could come in via sortByCharacter
1873     // note that mysql does not escape these characters
1874     if ($string && in_array($string,
1875         ['%', '_', '%%', '_%']
1876       )
1877     ) {
1878       return '\\' . $string;
1879     }
1880
1881     return self::escapeString($string);
1882   }
1883
1884   /**
1885    * Creates a test object, including any required objects it needs via recursion
1886    * createOnly: only create in database, do not store or return the objects (useful for perf testing)
1887    * ONLY USE FOR TESTING
1888    *
1889    * @param string $daoName
1890    * @param array $params
1891    * @param int $numObjects
1892    * @param bool $createOnly
1893    *
1894    * @return object|array|NULL
1895    *   NULL if $createOnly. A single object if $numObjects==1. Otherwise, an array of multiple objects.
1896    */
1897   public static function createTestObject(
1898     $daoName,
1899     $params = [],
1900     $numObjects = 1,
1901     $createOnly = FALSE
1902   ) {
1903     //this is a test function  also backtrace is set for the test suite it sometimes unsets itself
1904     // so we re-set here in case
1905     $config = CRM_Core_Config::singleton();
1906     $config->backtrace = TRUE;
1907
1908     static $counter = 0;
1909     CRM_Core_DAO::$_testEntitiesToSkip = [
1910       'CRM_Core_DAO_Worldregion',
1911       'CRM_Core_DAO_StateProvince',
1912       'CRM_Core_DAO_Country',
1913       'CRM_Core_DAO_Domain',
1914       'CRM_Financial_DAO_FinancialType',
1915       //because valid ones exist & we use pick them due to pseudoconstant can't reliably create & delete these
1916     ];
1917
1918     // Prefer to instantiate BAO's instead of DAO's (when possible)
1919     // so that assignTestValue()/assignTestFK() can be overloaded.
1920     $baoName = str_replace('_DAO_', '_BAO_', $daoName);
1921     if (class_exists($baoName)) {
1922       $daoName = $baoName;
1923     }
1924
1925     for ($i = 0; $i < $numObjects; ++$i) {
1926
1927       ++$counter;
1928       /** @var CRM_Core_DAO $object */
1929       $object = new $daoName();
1930
1931       $fields = $object->fields();
1932       foreach ($fields as $fieldName => $fieldDef) {
1933         $dbName = $fieldDef['name'];
1934         $FKClassName = CRM_Utils_Array::value('FKClassName', $fieldDef);
1935         $required = CRM_Utils_Array::value('required', $fieldDef);
1936
1937         if (CRM_Utils_Array::value($dbName, $params) !== NULL && !is_array($params[$dbName])) {
1938           $object->$dbName = $params[$dbName];
1939         }
1940
1941         elseif ($dbName != 'id') {
1942           if ($FKClassName != NULL) {
1943             $object->assignTestFK($fieldName, $fieldDef, $params);
1944             continue;
1945           }
1946           else {
1947             $object->assignTestValue($fieldName, $fieldDef, $counter);
1948           }
1949         }
1950       }
1951
1952       $object->save();
1953
1954       if (!$createOnly) {
1955         $objects[$i] = $object;
1956       }
1957       else {
1958         unset($object);
1959       }
1960     }
1961
1962     if ($createOnly) {
1963       return NULL;
1964     }
1965     elseif ($numObjects == 1) {
1966       return $objects[0];
1967     }
1968     else {
1969       return $objects;
1970     }
1971   }
1972
1973   /**
1974    * Deletes the this object plus any dependent objects that are associated with it.
1975    * ONLY USE FOR TESTING
1976    *
1977    * @param string $daoName
1978    * @param array $params
1979    */
1980   public static function deleteTestObjects($daoName, $params = []) {
1981     //this is a test function  also backtrace is set for the test suite it sometimes unsets itself
1982     // so we re-set here in case
1983     $config = CRM_Core_Config::singleton();
1984     $config->backtrace = TRUE;
1985
1986     $object = new $daoName();
1987     $object->id = CRM_Utils_Array::value('id', $params);
1988
1989     // array(array(0 => $daoName, 1 => $daoParams))
1990     $deletions = [];
1991     if ($object->find(TRUE)) {
1992
1993       $fields = $object->fields();
1994       foreach ($fields as $name => $value) {
1995
1996         $dbName = $value['name'];
1997
1998         $FKClassName = CRM_Utils_Array::value('FKClassName', $value);
1999         $required = CRM_Utils_Array::value('required', $value);
2000         if ($FKClassName != NULL
2001           && $object->$dbName
2002           && !in_array($FKClassName, CRM_Core_DAO::$_testEntitiesToSkip)
2003           && ($required || $dbName == 'contact_id')
2004           //I'm a bit stuck on this one - we might need to change the singleValueAlter so that the entities don't share a contact
2005           // to make this test process pass - line below makes pass for now
2006           && $dbName != 'member_of_contact_id'
2007         ) {
2008           // x
2009           $deletions[] = [$FKClassName, ['id' => $object->$dbName]];
2010         }
2011       }
2012     }
2013
2014     $object->delete();
2015
2016     foreach ($deletions as $deletion) {
2017       CRM_Core_DAO::deleteTestObjects($deletion[0], $deletion[1]);
2018     }
2019   }
2020
2021   /**
2022    * Set defaults when creating new entity.
2023    * (don't call this set defaults as already in use with different signature in some places)
2024    *
2025    * @param array $params
2026    * @param $defaults
2027    */
2028   public static function setCreateDefaults(&$params, $defaults) {
2029     if (!empty($params['id'])) {
2030       return;
2031     }
2032     foreach ($defaults as $key => $value) {
2033       if (!array_key_exists($key, $params) || $params[$key] === NULL) {
2034         $params[$key] = $value;
2035       }
2036     }
2037   }
2038
2039   /**
2040    * @param string $prefix
2041    * @param bool $addRandomString
2042    * @param null $string
2043    *
2044    * @return string
2045    * @deprecated
2046    * @see CRM_Utils_SQL_TempTable
2047    */
2048   public static function createTempTableName($prefix = 'civicrm', $addRandomString = TRUE, $string = NULL) {
2049     $tableName = $prefix . "_temp";
2050
2051     if ($addRandomString) {
2052       if ($string) {
2053         $tableName .= "_" . $string;
2054       }
2055       else {
2056         $tableName .= "_" . md5(uniqid('', TRUE));
2057       }
2058     }
2059     return $tableName;
2060   }
2061
2062   /**
2063    * @param bool $view
2064    * @param bool $trigger
2065    *
2066    * @return bool
2067    */
2068   public static function checkTriggerViewPermission($view = TRUE, $trigger = TRUE) {
2069     if (\Civi::settings()->get('logging_no_trigger_permission')) {
2070       return TRUE;
2071     }
2072     // test for create view and trigger permissions and if allowed, add the option to go multilingual
2073     // and logging
2074     // I'm not sure why we use the getStaticProperty for an error, rather than checking for DB_Error
2075     CRM_Core_TemporaryErrorScope::ignoreException();
2076     $dao = new CRM_Core_DAO();
2077     if ($view) {
2078       $result = $dao->query('CREATE OR REPLACE VIEW civicrm_domain_view AS SELECT * FROM civicrm_domain');
2079       if (PEAR::getStaticProperty('DB_DataObject', 'lastError') || is_a($result, 'DB_Error')) {
2080         return FALSE;
2081       }
2082     }
2083
2084     if ($trigger) {
2085       $result = $dao->query('CREATE TRIGGER civicrm_domain_trigger BEFORE INSERT ON civicrm_domain FOR EACH ROW BEGIN END');
2086       if (PEAR::getStaticProperty('DB_DataObject', 'lastError') || is_a($result, 'DB_Error')) {
2087         if ($view) {
2088           $dao->query('DROP VIEW IF EXISTS civicrm_domain_view');
2089         }
2090         return FALSE;
2091       }
2092
2093       $dao->query('DROP TRIGGER IF EXISTS civicrm_domain_trigger');
2094       if (PEAR::getStaticProperty('DB_DataObject', 'lastError')) {
2095         if ($view) {
2096           $dao->query('DROP VIEW IF EXISTS civicrm_domain_view');
2097         }
2098         return FALSE;
2099       }
2100     }
2101
2102     if ($view) {
2103       $dao->query('DROP VIEW IF EXISTS civicrm_domain_view');
2104       if (PEAR::getStaticProperty('DB_DataObject', 'lastError')) {
2105         return FALSE;
2106       }
2107     }
2108
2109     return TRUE;
2110   }
2111
2112   /**
2113    * @param null $message
2114    * @param bool $printDAO
2115    */
2116   public static function debugPrint($message = NULL, $printDAO = TRUE) {
2117     CRM_Utils_System::xMemory("{$message}: ");
2118
2119     if ($printDAO) {
2120       global $_DB_DATAOBJECT;
2121       $q = [];
2122       foreach (array_keys($_DB_DATAOBJECT['RESULTS']) as $id) {
2123         $q[] = $_DB_DATAOBJECT['RESULTS'][$id]->query;
2124       }
2125       CRM_Core_Error::debug('_DB_DATAOBJECT', $q);
2126     }
2127   }
2128
2129   /**
2130    * Build a list of triggers via hook and add them to (err, reconcile them
2131    * with) the database.
2132    *
2133    * @param string $tableName
2134    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
2135    * @param bool $force
2136    * @deprecated
2137    *
2138    * @see CRM-9716
2139    */
2140   public static function triggerRebuild($tableName = NULL, $force = FALSE) {
2141     Civi::service('sql_triggers')->rebuild($tableName, $force);
2142   }
2143
2144   /**
2145    * Because sql functions are sometimes lost, esp during db migration, we check here to avoid numerous support requests
2146    * @see http://issues.civicrm.org/jira/browse/CRM-13822
2147    * TODO: Alternative solutions might be
2148    *  * Stop using functions and find another way to strip numeric characters from phones
2149    *  * Give better error messages (currently a missing fn fatals with "unknown error")
2150    */
2151   public static function checkSqlFunctionsExist() {
2152     if (!self::$_checkedSqlFunctionsExist) {
2153       self::$_checkedSqlFunctionsExist = TRUE;
2154       $dao = CRM_Core_DAO::executeQuery("SHOW function status WHERE db = database() AND name = 'civicrm_strip_non_numeric'");
2155       if (!$dao->fetch()) {
2156         self::triggerRebuild();
2157       }
2158     }
2159   }
2160
2161   /**
2162    * Wrapper function to drop triggers.
2163    *
2164    * @param string $tableName
2165    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
2166    * @deprecated
2167    */
2168   public static function dropTriggers($tableName = NULL) {
2169     Civi::service('sql_triggers')->dropTriggers($tableName);
2170   }
2171
2172   /**
2173    * @param array $info
2174    *   per hook_civicrm_triggerInfo.
2175    * @param string $onlyTableName
2176    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
2177    * @deprecated
2178    */
2179   public static function createTriggers(&$info, $onlyTableName = NULL) {
2180     Civi::service('sql_triggers')->createTriggers($info, $onlyTableName);
2181   }
2182
2183   /**
2184    * Given a list of fields, create a list of references.
2185    *
2186    * @param string $className
2187    *   BAO/DAO class name.
2188    * @return array<CRM_Core_Reference_Interface>
2189    */
2190   public static function createReferenceColumns($className) {
2191     $result = [];
2192     $fields = $className::fields();
2193     foreach ($fields as $field) {
2194       if (isset($field['pseudoconstant'], $field['pseudoconstant']['optionGroupName'])) {
2195         $result[] = new CRM_Core_Reference_OptionValue(
2196           $className::getTableName(),
2197           $field['name'],
2198           'civicrm_option_value',
2199           CRM_Utils_Array::value('keyColumn', $field['pseudoconstant'], 'value'),
2200           $field['pseudoconstant']['optionGroupName']
2201         );
2202       }
2203     }
2204     return $result;
2205   }
2206
2207   /**
2208    * Find all records which refer to this entity.
2209    *
2210    * @return array
2211    *   Array of objects referencing this
2212    */
2213   public function findReferences() {
2214     $links = self::getReferencesToTable(static::getTableName());
2215
2216     $occurrences = [];
2217     foreach ($links as $refSpec) {
2218       /** @var $refSpec CRM_Core_Reference_Interface */
2219       $daoName = CRM_Core_DAO_AllCoreTables::getClassForTable($refSpec->getReferenceTable());
2220       $result = $refSpec->findReferences($this);
2221       if ($result) {
2222         while ($result->fetch()) {
2223           $obj = new $daoName();
2224           $obj->id = $result->id;
2225           $occurrences[] = $obj;
2226         }
2227       }
2228     }
2229
2230     return $occurrences;
2231   }
2232
2233   /**
2234    * @return array
2235    *   each item has keys:
2236    *   - name: string
2237    *   - type: string
2238    *   - count: int
2239    *   - table: string|null SQL table name
2240    *   - key: string|null SQL column name
2241    */
2242   public function getReferenceCounts() {
2243     $links = self::getReferencesToTable(static::getTableName());
2244
2245     $counts = [];
2246     foreach ($links as $refSpec) {
2247       /** @var $refSpec CRM_Core_Reference_Interface */
2248       $count = $refSpec->getReferenceCount($this);
2249       if ($count['count'] != 0) {
2250         $counts[] = $count;
2251       }
2252     }
2253
2254     foreach (CRM_Core_Component::getEnabledComponents() as $component) {
2255       /** @var $component CRM_Core_Component_Info */
2256       $counts = array_merge($counts, $component->getReferenceCounts($this));
2257     }
2258     CRM_Utils_Hook::referenceCounts($this, $counts);
2259
2260     return $counts;
2261   }
2262
2263   /**
2264    * List all tables which have hard foreign keys to this table.
2265    *
2266    * For now, this returns a description of every entity_id/entity_table
2267    * reference.
2268    * TODO: filter dynamic entity references on the $tableName, based on
2269    * schema metadata in dynamicForeignKey which enumerates a restricted
2270    * set of possible entity_table's.
2271    *
2272    * @param string $tableName
2273    *   Table referred to.
2274    *
2275    * @return array
2276    *   structure of table and column, listing every table with a
2277    *   foreign key reference to $tableName, and the column where the key appears.
2278    */
2279   public static function getReferencesToTable($tableName) {
2280     $refsFound = [];
2281     foreach (CRM_Core_DAO_AllCoreTables::getClasses() as $daoClassName) {
2282       $links = $daoClassName::getReferenceColumns();
2283       $daoTableName = $daoClassName::getTableName();
2284
2285       foreach ($links as $refSpec) {
2286         /** @var $refSpec CRM_Core_Reference_Interface */
2287         if ($refSpec->matchesTargetTable($tableName)) {
2288           $refsFound[] = $refSpec;
2289         }
2290       }
2291     }
2292     return $refsFound;
2293   }
2294
2295   /**
2296    * Get all references to contact table.
2297    *
2298    * This includes core tables, custom group tables, tables added by the merge
2299    * hook and  the entity_tag table.
2300    *
2301    * Refer to CRM-17454 for information on the danger of querying the information
2302    * schema to derive this.
2303    */
2304   public static function getReferencesToContactTable() {
2305     if (isset(\Civi::$statics[__CLASS__]) && isset(\Civi::$statics[__CLASS__]['contact_references'])) {
2306       return \Civi::$statics[__CLASS__]['contact_references'];
2307     }
2308     $contactReferences = [];
2309     $coreReferences = CRM_Core_DAO::getReferencesToTable('civicrm_contact');
2310     foreach ($coreReferences as $coreReference) {
2311       if (!is_a($coreReference, 'CRM_Core_Reference_Dynamic')) {
2312         $contactReferences[$coreReference->getReferenceTable()][] = $coreReference->getReferenceKey();
2313       }
2314     }
2315     self::appendCustomTablesExtendingContacts($contactReferences);
2316
2317     // FixME for time being adding below line statically as no Foreign key constraint defined for table 'civicrm_entity_tag'
2318     $contactReferences['civicrm_entity_tag'][] = 'entity_id';
2319     \Civi::$statics[__CLASS__]['contact_references'] = $contactReferences;
2320     return \Civi::$statics[__CLASS__]['contact_references'];
2321   }
2322
2323   /**
2324    * Add custom tables that extend contacts to the list of contact references.
2325    *
2326    * CRM_Core_BAO_CustomGroup::getAllCustomGroupsByBaseEntity seems like a safe-ish
2327    * function to be sure all are retrieved & we don't miss subtypes or inactive or multiples
2328    * - the down side is it is not cached.
2329    *
2330    * Further changes should be include tests in the CRM_Core_MergerTest class
2331    * to ensure that disabled, subtype, multiple etc groups are still captured.
2332    *
2333    * @param array $cidRefs
2334    */
2335   public static function appendCustomTablesExtendingContacts(&$cidRefs) {
2336     $customValueTables = CRM_Core_BAO_CustomGroup::getAllCustomGroupsByBaseEntity('Contact');
2337     $customValueTables->find();
2338     while ($customValueTables->fetch()) {
2339       $cidRefs[$customValueTables->table_name] = ['entity_id'];
2340     }
2341   }
2342
2343   /**
2344    * Lookup the value of a MySQL global configuration variable.
2345    *
2346    * @param string $name
2347    *   E.g. "thread_stack".
2348    * @param mixed $default
2349    * @return mixed
2350    */
2351   public static function getGlobalSetting($name, $default = NULL) {
2352     // Alternatively, SELECT @@GLOBAL.thread_stack, but
2353     // that has been reported to fail under MySQL 5.0 for OS X
2354     $escapedName = self::escapeString($name);
2355     $dao = CRM_Core_DAO::executeQuery("SHOW VARIABLES LIKE '$escapedName'");
2356     if ($dao->fetch()) {
2357       return $dao->Value;
2358     }
2359     else {
2360       return $default;
2361     }
2362   }
2363
2364   /**
2365    * Update the fields array to also hold keys for pseudoconstant fields that relate to contained fields.
2366    *
2367    * This is relevant where we want to offer both the ID field and the label field
2368    * as an option, e.g. search builder.
2369    *
2370    * It is currently limited for optionGroupName & id+ name+ FK combos for purposes keeping the scope of the
2371    * change small, but is appropriate for other sorts of pseudoconstants.
2372    *
2373    * @param array $fields
2374    */
2375   public static function appendPseudoConstantsToFields(&$fields) {
2376     foreach ($fields as $field) {
2377       if (!empty($field['pseudoconstant'])) {
2378         $pseudoConstant = $field['pseudoconstant'];
2379         if (!empty($pseudoConstant['optionGroupName'])) {
2380           $fields[$pseudoConstant['optionGroupName']] = [
2381             'title' => CRM_Core_BAO_OptionGroup::getTitleByName($pseudoConstant['optionGroupName']),
2382             'name' => $pseudoConstant['optionGroupName'],
2383             'data_type' => CRM_Utils_Type::T_STRING,
2384             'is_pseudofield_for' => $field['name'],
2385           ];
2386         }
2387         // We restrict to id + name + FK as we are extending this a bit, but cautiously.
2388         elseif (
2389           !empty($field['FKClassName'])
2390           && CRM_Utils_Array::value('keyColumn', $pseudoConstant) === 'id'
2391           && CRM_Utils_Array::value('labelColumn', $pseudoConstant) === 'name'
2392         ) {
2393           $pseudoFieldName = str_replace('_' . $pseudoConstant['keyColumn'], '', $field['name']);
2394           // This if is just an extra caution when adding change.
2395           if (!isset($fields[$pseudoFieldName])) {
2396             $daoName = $field['FKClassName'];
2397             $fkFields = $daoName::fields();
2398             foreach ($fkFields as $fkField) {
2399               if ($fkField['name'] === $pseudoConstant['labelColumn']) {
2400                 $fields[$pseudoFieldName] = [
2401                   'name' => $pseudoFieldName,
2402                   'is_pseudofield_for' => $field['name'],
2403                   'title' => $fkField['title'],
2404                   'data_type' => $fkField['type'],
2405                   'where' => $field['where'],
2406                 ];
2407               }
2408             }
2409           }
2410         }
2411       }
2412     }
2413   }
2414
2415   /**
2416    * Get options for the called BAO object's field.
2417    *
2418    * This function can be overridden by each BAO to add more logic related to context.
2419    * The overriding function will generally call the lower-level CRM_Core_PseudoConstant::get
2420    *
2421    * @param string $fieldName
2422    * @param string $context
2423    * @see CRM_Core_DAO::buildOptionsContext
2424    * @param array $props
2425    *   whatever is known about this bao object.
2426    *
2427    * @return array|bool
2428    */
2429   public static function buildOptions($fieldName, $context = NULL, $props = []) {
2430     // If a given bao does not override this function
2431     $baoName = get_called_class();
2432     return CRM_Core_PseudoConstant::get($baoName, $fieldName, $props, $context);
2433   }
2434
2435   /**
2436    * Populate option labels for this object's fields.
2437    *
2438    * @throws exception if called directly on the base class
2439    */
2440   public function getOptionLabels() {
2441     $fields = $this->fields();
2442     if ($fields === NULL) {
2443       throw new Exception('Cannot call getOptionLabels on CRM_Core_DAO');
2444     }
2445     foreach ($fields as $field) {
2446       $name = CRM_Utils_Array::value('name', $field);
2447       if ($name && isset($this->$name)) {
2448         $label = CRM_Core_PseudoConstant::getLabel(get_class($this), $name, $this->$name);
2449         if ($label !== FALSE) {
2450           // Append 'label' onto the field name
2451           $labelName = $name . '_label';
2452           $this->$labelName = $label;
2453         }
2454       }
2455     }
2456   }
2457
2458   /**
2459    * Provides documentation and validation for the buildOptions $context param
2460    *
2461    * @param string $context
2462    *
2463    * @throws Exception
2464    * @return array
2465    */
2466   public static function buildOptionsContext($context = NULL) {
2467     $contexts = [
2468       'get' => "get: all options are returned, even if they are disabled; labels are translated.",
2469       'create' => "create: options are filtered appropriately for the object being created/updated; labels are translated.",
2470       'search' => "search: searchable options are returned; labels are translated.",
2471       'validate' => "validate: all options are returned, even if they are disabled; machine names are used in place of labels.",
2472       'abbreviate' => "abbreviate: enabled options are returned; labels are replaced with abbreviations.",
2473       'match' => "match: enabled options are returned using machine names as keys; labels are translated.",
2474     ];
2475     // Validation: enforce uniformity of this param
2476     if ($context !== NULL && !isset($contexts[$context])) {
2477       throw new Exception("'$context' is not a valid context for buildOptions.");
2478     }
2479     return $contexts;
2480   }
2481
2482   /**
2483    * @param string $fieldName
2484    * @return bool|array
2485    */
2486   public function getFieldSpec($fieldName) {
2487     $fields = $this->fields();
2488     $fieldKeys = $this->fieldKeys();
2489
2490     // Support "unique names" as well as sql names
2491     $fieldKey = $fieldName;
2492     if (empty($fields[$fieldKey])) {
2493       $fieldKey = CRM_Utils_Array::value($fieldName, $fieldKeys);
2494     }
2495     // If neither worked then this field doesn't exist. Return false.
2496     if (empty($fields[$fieldKey])) {
2497       return FALSE;
2498     }
2499     return $fields[$fieldKey];
2500   }
2501
2502   /**
2503    * Get SQL where clause for SQL filter syntax input parameters.
2504    *
2505    * SQL version of api function to assign filters to the DAO based on the syntax
2506    * $field => array('IN' => array(4,6,9))
2507    * OR
2508    * $field => array('LIKE' => array('%me%))
2509    * etc
2510    *
2511    * @param string $fieldName
2512    *   Name of fields.
2513    * @param array $filter
2514    *   filter to be applied indexed by operator.
2515    * @param string $type
2516    *   type of field (not actually used - nor in api @todo ).
2517    * @param string $alias
2518    *   alternative field name ('as') @todo- not actually used.
2519    * @param bool $returnSanitisedArray
2520    *   Return a sanitised array instead of a clause.
2521    *   this is primarily so we can add filters @ the api level to the Query object based fields
2522    *
2523    * @throws Exception
2524    *
2525    * @return NULL|string|array
2526    *   a string is returned if $returnSanitisedArray is not set, otherwise and Array or NULL
2527    *   depending on whether it is supported as yet
2528    */
2529   public static function createSQLFilter($fieldName, $filter, $type = NULL, $alias = NULL, $returnSanitisedArray = FALSE) {
2530     foreach ($filter as $operator => $criteria) {
2531       if (in_array($operator, self::acceptedSQLOperators(), TRUE)) {
2532         switch ($operator) {
2533           // unary operators
2534           case 'IS NULL':
2535           case 'IS NOT NULL':
2536             if (!$returnSanitisedArray) {
2537               return (sprintf('%s %s', $fieldName, $operator));
2538             }
2539             else {
2540               return (sprintf('%s %s ', $fieldName, $operator));
2541             }
2542             break;
2543
2544           // ternary operators
2545           case 'BETWEEN':
2546           case 'NOT BETWEEN':
2547             if ((empty($criteria[0]) && !in_array($criteria[0], ['0', 0]))|| (empty($criteria[1]) &&  !in_array($criteria[1], ['0', 0]))) {
2548               throw new Exception("invalid criteria for $operator");
2549             }
2550             if (!$returnSanitisedArray) {
2551               return (sprintf('%s ' . $operator . ' "%s" AND "%s"', $fieldName, CRM_Core_DAO::escapeString($criteria[0]), CRM_Core_DAO::escapeString($criteria[1])));
2552             }
2553             else {
2554               // not yet implemented (tests required to implement)
2555               return NULL;
2556             }
2557             break;
2558
2559           // n-ary operators
2560           case 'IN':
2561           case 'NOT IN':
2562             if (empty($criteria)) {
2563               throw new Exception("invalid criteria for $operator");
2564             }
2565             $escapedCriteria = array_map([
2566               'CRM_Core_DAO',
2567               'escapeString',
2568             ], $criteria);
2569             if (!$returnSanitisedArray) {
2570               return (sprintf('%s %s ("%s")', $fieldName, $operator, implode('", "', $escapedCriteria)));
2571             }
2572             return $escapedCriteria;
2573
2574           // binary operators
2575
2576           default:
2577             if (!$returnSanitisedArray) {
2578               return (sprintf('%s %s "%s"', $fieldName, $operator, CRM_Core_DAO::escapeString($criteria)));
2579             }
2580             else {
2581               // not yet implemented (tests required to implement)
2582               return NULL;
2583             }
2584         }
2585       }
2586     }
2587   }
2588
2589   /**
2590    * @see http://issues.civicrm.org/jira/browse/CRM-9150
2591    * support for other syntaxes is discussed in ticket but being put off for now
2592    * @return array
2593    */
2594   public static function acceptedSQLOperators() {
2595     return [
2596       '=',
2597       '<=',
2598       '>=',
2599       '>',
2600       '<',
2601       'LIKE',
2602       "<>",
2603       "!=",
2604       "NOT LIKE",
2605       'IN',
2606       'NOT IN',
2607       'BETWEEN',
2608       'NOT BETWEEN',
2609       'IS NOT NULL',
2610       'IS NULL',
2611     ];
2612   }
2613
2614   /**
2615    * SQL has a limit of 64 characters on various names:
2616    * table name, trigger name, column name ...
2617    *
2618    * For custom groups and fields we generated names from user entered input
2619    * which can be longer than this length, this function helps with creating
2620    * strings that meet various criteria.
2621    *
2622    * @param string $string
2623    *   The string to be shortened.
2624    * @param int $length
2625    *   The max length of the string.
2626    *
2627    * @param bool $makeRandom
2628    *
2629    * @return string
2630    */
2631   public static function shortenSQLName($string, $length = 60, $makeRandom = FALSE) {
2632     // early return for strings that meet the requirements
2633     if (strlen($string) <= $length) {
2634       return $string;
2635     }
2636
2637     // easy return for calls that dont need a randomized uniq string
2638     if (!$makeRandom) {
2639       return substr($string, 0, $length);
2640     }
2641
2642     // the string is longer than the length and we need a uniq string
2643     // for the same tablename we need the same uniq string every time
2644     // hence we use md5 on the string, which is not random
2645     // we'll append 8 characters to the end of the tableName
2646     $md5string = substr(md5($string), 0, 8);
2647     return substr($string, 0, $length - 8) . "_{$md5string}";
2648   }
2649
2650   /**
2651    * https://issues.civicrm.org/jira/browse/CRM-17748
2652    * Sets the internal options to be used on a query
2653    *
2654    * @param array $options
2655    *
2656    */
2657   public function setOptions($options) {
2658     if (is_array($options)) {
2659       $this->_options = $options;
2660     }
2661   }
2662
2663   /**
2664    * https://issues.civicrm.org/jira/browse/CRM-17748
2665    * wrapper to pass internal DAO options down to DB_mysql/DB_Common level
2666    *
2667    * @param array $options
2668    *
2669    */
2670   protected function _setDBOptions($options) {
2671     global $_DB_DATAOBJECT;
2672
2673     if (is_array($options) && count($options)) {
2674       $conn = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
2675       foreach ($options as $option_name => $option_value) {
2676         $conn->setOption($option_name, $option_value);
2677       }
2678     }
2679   }
2680
2681   /**
2682    * @deprecated
2683    * @param array $params
2684    */
2685   public function setApiFilter(&$params) {
2686   }
2687
2688   /**
2689    * Generates acl clauses suitable for adding to WHERE or ON when doing an api.get for this entity
2690    *
2691    * Return format is in the form of fieldname => clauses starting with an operator. e.g.:
2692    * @code
2693    *   array(
2694    *     'location_type_id' => array('IS NOT NULL', 'IN (1,2,3)')
2695    *   )
2696    * @endcode
2697    *
2698    * Note that all array keys must be actual field names in this entity. Use subqueries to filter on other tables e.g. custom values.
2699    *
2700    * @return array
2701    */
2702   public function addSelectWhereClause() {
2703     $clauses = [];
2704     $fields = $this->fields();
2705     foreach ($fields as $fieldName => $field) {
2706       // Clause for contact-related entities like Email, Relationship, etc.
2707       if (strpos($fieldName, 'contact_id') === 0 && CRM_Utils_Array::value('FKClassName', $field) == 'CRM_Contact_DAO_Contact') {
2708         $clauses[$fieldName] = CRM_Utils_SQL::mergeSubquery('Contact');
2709       }
2710       // Clause for an entity_table/entity_id combo
2711       if ($fieldName == 'entity_id' && isset($fields['entity_table'])) {
2712         $relatedClauses = [];
2713         $relatedEntities = $this->buildOptions('entity_table', 'get');
2714         foreach ((array) $relatedEntities as $table => $ent) {
2715           if (!empty($ent)) {
2716             $ent = CRM_Core_DAO_AllCoreTables::getBriefName(CRM_Core_DAO_AllCoreTables::getClassForTable($table));
2717             $subquery = CRM_Utils_SQL::mergeSubquery($ent);
2718             if ($subquery) {
2719               $relatedClauses[] = "(entity_table = '$table' AND entity_id " . implode(' AND entity_id ', $subquery) . ")";
2720             }
2721             else {
2722               $relatedClauses[] = "(entity_table = '$table')";
2723             }
2724           }
2725         }
2726         if ($relatedClauses) {
2727           $clauses['id'] = 'IN (SELECT id FROM `' . $this->tableName() . '` WHERE (' . implode(') OR (', $relatedClauses) . '))';
2728         }
2729       }
2730     }
2731     CRM_Utils_Hook::selectWhereClause($this, $clauses);
2732     return $clauses;
2733   }
2734
2735   /**
2736    * This returns the final permissioned query string for this entity
2737    *
2738    * With acls from related entities + additional clauses from hook_civicrm_selectWhereClause
2739    *
2740    * @param string $tableAlias
2741    * @return array
2742    */
2743   public static function getSelectWhereClause($tableAlias = NULL) {
2744     $bao = new static();
2745     if ($tableAlias === NULL) {
2746       $tableAlias = $bao->tableName();
2747     }
2748     $clauses = [];
2749     foreach ((array) $bao->addSelectWhereClause() as $field => $vals) {
2750       $clauses[$field] = NULL;
2751       if ($vals) {
2752         $clauses[$field] = "(`$tableAlias`.`$field` IS NULL OR (`$tableAlias`.`$field` " . implode(" AND `$tableAlias`.`$field` ", (array) $vals) . '))';
2753       }
2754     }
2755     return $clauses;
2756   }
2757
2758   /**
2759    * ensure database name is 'safe', i.e. only contains word characters (includes underscores)
2760    * and dashes, and contains at least one [a-z] case insenstive.
2761    *
2762    * @param $database
2763    *
2764    * @return bool
2765    */
2766   public static function requireSafeDBName($database) {
2767     $matches = [];
2768     preg_match(
2769       "/^[\w\-]*[a-z]+[\w\-]*$/i",
2770       $database,
2771       $matches
2772     );
2773     if (empty($matches)) {
2774       return FALSE;
2775     }
2776     return TRUE;
2777   }
2778
2779   /**
2780    * Transform an array to a serialized string for database storage.
2781    *
2782    * @param array|NULL $value
2783    * @param $serializationType
2784    * @return string|NULL
2785    * @throws \Exception
2786    */
2787   public static function serializeField($value, $serializationType) {
2788     if ($value === NULL) {
2789       return NULL;
2790     }
2791     switch ($serializationType) {
2792       case self::SERIALIZE_SEPARATOR_BOOKEND:
2793         return $value === [] ? '' : CRM_Utils_Array::implodePadded($value);
2794
2795       case self::SERIALIZE_SEPARATOR_TRIMMED:
2796         return is_array($value) ? implode(self::VALUE_SEPARATOR, $value) : $value;
2797
2798       case self::SERIALIZE_JSON:
2799         return is_array($value) ? json_encode($value) : $value;
2800
2801       case self::SERIALIZE_PHP:
2802         return is_array($value) ? serialize($value) : $value;
2803
2804       case self::SERIALIZE_COMMA:
2805         return is_array($value) ? implode(',', $value) : $value;
2806
2807       default:
2808         throw new Exception('Unknown serialization method for field.');
2809     }
2810   }
2811
2812   /**
2813    * Transform a serialized string from the database into an array.
2814    *
2815    * @param string|null $value
2816    * @param $serializationType
2817    * @return array|null
2818    * @throws \Exception
2819    */
2820   public static function unSerializeField($value, $serializationType) {
2821     if ($value === NULL) {
2822       return NULL;
2823     }
2824     if ($value === '') {
2825       return [];
2826     }
2827     switch ($serializationType) {
2828       case self::SERIALIZE_SEPARATOR_BOOKEND:
2829         return (array) CRM_Utils_Array::explodePadded($value);
2830
2831       case self::SERIALIZE_SEPARATOR_TRIMMED:
2832         return explode(self::VALUE_SEPARATOR, trim($value));
2833
2834       case self::SERIALIZE_JSON:
2835         return strlen($value) ? json_decode($value, TRUE) : [];
2836
2837       case self::SERIALIZE_PHP:
2838         return strlen($value) ? unserialize($value) : [];
2839
2840       case self::SERIALIZE_COMMA:
2841         return explode(',', trim(str_replace(', ', '', $value)));
2842
2843       default:
2844         throw new Exception('Unknown serialization method for field.');
2845     }
2846   }
2847
2848   /**
2849    * @return array
2850    */
2851   public static function getEntityRefFilters() {
2852     return [];
2853   }
2854
2855   /**
2856    * Get exportable fields with pseudoconstants rendered as an extra field.
2857    *
2858    * @param string $baoClass
2859    *
2860    * @return array
2861    */
2862   public static function getExportableFieldsWithPseudoConstants($baoClass) {
2863     if (method_exists($baoClass, 'exportableFields')) {
2864       $fields = $baoClass::exportableFields();
2865     }
2866     else {
2867       $fields = $baoClass::export();
2868     }
2869     CRM_Core_DAO::appendPseudoConstantsToFields($fields);
2870     return $fields;
2871   }
2872
2873 }