CRM/Core/DAO.php

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