CRM/Core/DAO.php

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