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       $fkDAO->free();
 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       $depObject->free();
 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       $depObject->free();
 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       $depObject->free();
 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     $this->free();
 524
 525     return $result;
 526   }
 527
 528   /**
 529    * @param bool $created
 530    */
 531   public function log($created = FALSE) {
 532     static $cid = NULL;
 533
 534     if (!$this->getLog()) {
 535       return;
 536     }
 537
 538     if (!$cid) {
 539       $session = CRM_Core_Session::singleton();
 540       $cid = $session->get('userID');
 541     }
 542
 543     // return is we dont have handle to FK
 544     if (!$cid) {
 545       return;
 546     }
 547
 548     $dao = new CRM_Core_DAO_Log();
 549     $dao->entity_table = $this->getTableName();
 550     $dao->entity_id = $this->id;
 551     $dao->modified_id = $cid;
 552     $dao->modified_date = date("YmdHis");
 553     $dao->insert();
 554   }
 555
 556   /**
 557    * Given an associative array of name/value pairs, extract all the values
 558    * that belong to this object and initialize the object with said values
 559    *
 560    * @param array $params
 561    *   (reference ) associative array of name/value pairs.
 562    *
 563    * @return bool
 564    *   Did we copy all null values into the object
 565    */
 566   public function copyValues(&$params) {
 567     $fields = &$this->fields();
 568     $allNull = TRUE;
 569     foreach ($fields as $name => $value) {
 570       $dbName = $value['name'];
 571       if (array_key_exists($dbName, $params)) {
 572         $pValue = $params[$dbName];
 573         $exists = TRUE;
 574       }
 575       elseif (array_key_exists($name, $params)) {
 576         $pValue = $params[$name];
 577         $exists = TRUE;
 578       }
 579       else {
 580         $exists = FALSE;
 581       }
 582
 583       // if there is no value then make the variable NULL
 584       if ($exists) {
 585         if ($pValue === '') {
 586           $this->$dbName = 'null';
 587         }
 588         else {
 589           $this->$dbName = $pValue;
 590           $allNull = FALSE;
 591         }
 592       }
 593     }
 594     return $allNull;
 595   }
 596
 597   /**
 598    * Store all the values from this object in an associative array
 599    * this is a destructive store, calling function is responsible
 600    * for keeping sanity of id's.
 601    *
 602    * @param object $object
 603    *   The object that we are extracting data from.
 604    * @param array $values
 605    *   (reference ) associative array of name/value pairs.
 606    */
 607   public static function storeValues(&$object, &$values) {
 608     $fields = &$object->fields();
 609     foreach ($fields as $name => $value) {
 610       $dbName = $value['name'];
 611       if (isset($object->$dbName) && $object->$dbName !== 'null') {
 612         $values[$dbName] = $object->$dbName;
 613         if ($name != $dbName) {
 614           $values[$name] = $object->$dbName;
 615         }
 616       }
 617     }
 618   }
 619
 620   /**
 621    * Create an attribute for this specific field. We only do this for strings and text
 622    *
 623    * @param array $field
 624    *   The field under task.
 625    *
 626    * @return array|null
 627    *   the attributes for the object
 628    */
 629   public static function makeAttribute($field) {
 630     if ($field) {
 631       if (CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_STRING) {
 632         $maxLength = CRM_Utils_Array::value('maxlength', $field);
 633         $size = CRM_Utils_Array::value('size', $field);
 634         if ($maxLength || $size) {
 635           $attributes = array();
 636           if ($maxLength) {
 637             $attributes['maxlength'] = $maxLength;
 638           }
 639           if ($size) {
 640             $attributes['size'] = $size;
 641           }
 642           return $attributes;
 643         }
 644       }
 645       elseif (CRM_Utils_Array::value('type', $field) == CRM_Utils_Type::T_TEXT) {
 646         $rows = CRM_Utils_Array::value('rows', $field);
 647         if (!isset($rows)) {
 648           $rows = 2;
 649         }
 650         $cols = CRM_Utils_Array::value('cols', $field);
 651         if (!isset($cols)) {
 652           $cols = 80;
 653         }
 654
 655         $attributes = array();
 656         $attributes['rows'] = $rows;
 657         $attributes['cols'] = $cols;
 658         return $attributes;
 659       }
 660       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) {
 661         $attributes['size'] = 6;
 662         $attributes['maxlength'] = 14;
 663         return $attributes;
 664       }
 665     }
 666     return NULL;
 667   }
 668
 669   /**
 670    * Get the size and maxLength attributes for this text field.
 671    * (or for all text fields) in the DAO object.
 672    *
 673    * @param string $class
 674    *   Name of DAO class.
 675    * @param string $fieldName
 676    *   Field that i'm interested in or null if.
 677    *                          you want the attributes for all DAO text fields
 678    *
 679    * @return array
 680    *   assoc array of name => attribute pairs
 681    */
 682   public static function getAttribute($class, $fieldName = NULL) {
 683     $object = new $class();
 684     $fields = &$object->fields();
 685     if ($fieldName != NULL) {
 686       $field = CRM_Utils_Array::value($fieldName, $fields);
 687       return self::makeAttribute($field);
 688     }
 689     else {
 690       $attributes = array();
 691       foreach ($fields as $name => $field) {
 692         $attribute = self::makeAttribute($field);
 693         if ($attribute) {
 694           $attributes[$name] = $attribute;
 695         }
 696       }
 697
 698       if (!empty($attributes)) {
 699         return $attributes;
 700       }
 701     }
 702     return NULL;
 703   }
 704
 705   /**
 706    * @param $type
 707    *
 708    * @throws Exception
 709    */
 710   public static function transaction($type) {
 711     CRM_Core_Error::fatal('This function is obsolete, please use CRM_Core_Transaction');
 712   }
 713
 714   /**
 715    * Check if there is a record with the same name in the db.
 716    *
 717    * @param string $value
 718    *   The value of the field we are checking.
 719    * @param string $daoName
 720    *   The dao object name.
 721    * @param string $daoID
 722    *   The id of the object being updated. u can change your name.
 723    *                          as long as there is no conflict
 724    * @param string $fieldName
 725    *   The name of the field in the DAO.
 726    *
 727    * @return bool
 728    *   true if object exists
 729    */
 730   public static function objectExists($value, $daoName, $daoID, $fieldName = 'name') {
 731     $object = new $daoName();
 732     $object->$fieldName = $value;
 733
 734     $config = CRM_Core_Config::singleton();
 735
 736     if ($object->find(TRUE)) {
 737       return ($daoID && $object->id == $daoID) ? TRUE : FALSE;
 738     }
 739     else {
 740       return TRUE;
 741     }
 742   }
 743
 744   /**
 745    * Check if there is a given column in a specific table.
 746    *
 747    * @param string $tableName
 748    * @param string $columnName
 749    * @param bool $i18nRewrite
 750    *   Whether to rewrite the query on multilingual setups.
 751    *
 752    * @return bool
 753    *   true if exists, else false
 754    */
 755   public static function checkFieldExists($tableName, $columnName, $i18nRewrite = TRUE) {
 756     $query = "
 757 SHOW COLUMNS
 758 FROM $tableName
 759 LIKE %1
 760 ";
 761     $params = array(1 => array($columnName, 'String'));
 762     $dao = CRM_Core_DAO::executeQuery($query, $params, TRUE, NULL, FALSE, $i18nRewrite);
 763     $result = $dao->fetch() ? TRUE : FALSE;
 764     $dao->free();
 765     return $result;
 766   }
 767
 768   /**
 769    * Returns the storage engine used by given table-name(optional).
 770    * Otherwise scans all the tables and return an array of all the
 771    * distinct storage engines being used.
 772    *
 773    * @param string $tableName
 774    *
 775    * @param int $maxTablesToCheck
 776    * @param string $fieldName
 777    *
 778    * @return array
 779    */
 780   public static function getStorageValues($tableName = NULL, $maxTablesToCheck = 10, $fieldName = 'Engine') {
 781     $values = array();
 782     $query = "SHOW TABLE STATUS LIKE %1";
 783
 784     $params = array();
 785
 786     if (isset($tableName)) {
 787       $params = array(1 => array($tableName, 'String'));
 788     }
 789     else {
 790       $params = array(1 => array('civicrm_%', 'String'));
 791     }
 792
 793     $dao = CRM_Core_DAO::executeQuery($query, $params);
 794
 795     $count = 0;
 796     while ($dao->fetch()) {
 797       if (isset($values[$dao->$fieldName]) ||
 798         // ignore import and other temp tables
 799         strpos($dao->Name, 'civicrm_import_job_') !== FALSE ||
 800         strpos($dao->Name, '_temp') !== FALSE
 801       ) {
 802         continue;
 803       }
 804       $values[$dao->$fieldName] = 1;
 805       $count++;
 806       if ($maxTablesToCheck &&
 807         $count >= $maxTablesToCheck
 808       ) {
 809         break;
 810       }
 811     }
 812     $dao->free();
 813     return $values;
 814   }
 815
 816   /**
 817    * @param int $maxTablesToCheck
 818    *
 819    * @return bool
 820    */
 821   public static function isDBMyISAM($maxTablesToCheck = 10) {
 822     // show error if any of the tables, use 'MyISAM' storage engine.
 823     $engines = self::getStorageValues(NULL, $maxTablesToCheck);
 824     if (array_key_exists('MyISAM', $engines)) {
 825       return TRUE;
 826     }
 827     return FALSE;
 828   }
 829
 830   /**
 831    * Checks if a constraint exists for a specified table.
 832    *
 833    * @param string $tableName
 834    * @param string $constraint
 835    *
 836    * @return bool
 837    *   true if constraint exists, false otherwise
 838    */
 839   public static function checkConstraintExists($tableName, $constraint) {
 840     static $show = array();
 841
 842     if (!array_key_exists($tableName, $show)) {
 843       $query = "SHOW CREATE TABLE $tableName";
 844       $dao = CRM_Core_DAO::executeQuery($query);
 845
 846       if (!$dao->fetch()) {
 847         CRM_Core_Error::fatal();
 848       }
 849
 850       $dao->free();
 851       $show[$tableName] = $dao->Create_Table;
 852     }
 853
 854     return preg_match("/\b$constraint\b/i", $show[$tableName]) ? TRUE : FALSE;
 855   }
 856
 857   /**
 858    * Checks if CONSTRAINT keyword exists for a specified table.
 859    *
 860    * @param array $tables
 861    *
 862    * @throws Exception
 863    *
 864    * @return bool
 865    *   true if CONSTRAINT keyword exists, false otherwise
 866    */
 867   public static function schemaRequiresRebuilding($tables = array("civicrm_contact")) {
 868     $show = array();
 869     foreach ($tables as $tableName) {
 870       if (!array_key_exists($tableName, $show)) {
 871         $query = "SHOW CREATE TABLE $tableName";
 872         $dao = CRM_Core_DAO::executeQuery($query);
 873
 874         if (!$dao->fetch()) {
 875           CRM_Core_Error::fatal();
 876         }
 877
 878         $dao->free();
 879         $show[$tableName] = $dao->Create_Table;
 880       }
 881
 882       $result = preg_match("/\bCONSTRAINT\b\s/i", $show[$tableName]) ? TRUE : FALSE;
 883       if ($result == TRUE) {
 884         continue;
 885       }
 886       else {
 887         return FALSE;
 888       }
 889     }
 890     return TRUE;
 891   }
 892
 893   /**
 894    * Checks if the FK constraint name is in the format 'FK_tableName_columnName'
 895    * for a specified column of a table.
 896    *
 897    * @param string $tableName
 898    * @param string $columnName
 899    *
 900    * @return bool
 901    *   true if in format, false otherwise
 902    */
 903   public static function checkFKConstraintInFormat($tableName, $columnName) {
 904     static $show = array();
 905
 906     if (!array_key_exists($tableName, $show)) {
 907       $query = "SHOW CREATE TABLE $tableName";
 908       $dao = CRM_Core_DAO::executeQuery($query);
 909
 910       if (!$dao->fetch()) {
 911         CRM_Core_Error::fatal();
 912       }
 913
 914       $dao->free();
 915       $show[$tableName] = $dao->Create_Table;
 916     }
 917     $constraint = "`FK_{$tableName}_{$columnName}`";
 918     $pattern = "/\bCONSTRAINT\b\s+%s\s+\bFOREIGN\s+KEY\b\s/i";
 919     return preg_match(sprintf($pattern, $constraint), $show[$tableName]) ? TRUE : FALSE;
 920   }
 921
 922   /**
 923    * Check whether a specific column in a specific table has always the same value.
 924    *
 925    * @param string $tableName
 926    * @param string $columnName
 927    * @param string $columnValue
 928    *
 929    * @return bool
 930    *   true if the value is always $columnValue, false otherwise
 931    */
 932   public static function checkFieldHasAlwaysValue($tableName, $columnName, $columnValue) {
 933     $query = "SELECT * FROM $tableName WHERE $columnName != '$columnValue'";
 934     $dao = CRM_Core_DAO::executeQuery($query);
 935     $result = $dao->fetch() ? FALSE : TRUE;
 936     $dao->free();
 937     return $result;
 938   }
 939
 940   /**
 941    * Check whether a specific column in a specific table is always NULL.
 942    *
 943    * @param string $tableName
 944    * @param string $columnName
 945    *
 946    * @return bool
 947    *   true if if the value is always NULL, false otherwise
 948    */
 949   public static function checkFieldIsAlwaysNull($tableName, $columnName) {
 950     $query = "SELECT * FROM $tableName WHERE $columnName IS NOT NULL";
 951     $dao = CRM_Core_DAO::executeQuery($query);
 952     $result = $dao->fetch() ? FALSE : TRUE;
 953     $dao->free();
 954     return $result;
 955   }
 956
 957   /**
 958    * Check if there is a given table in the database.
 959    *
 960    * @param string $tableName
 961    *
 962    * @return bool
 963    *   true if exists, else false
 964    */
 965   public static function checkTableExists($tableName) {
 966     $query = "
 967 SHOW TABLES
 968 LIKE %1
 969 ";
 970     $params = array(1 => array($tableName, 'String'));
 971
 972     $dao = CRM_Core_DAO::executeQuery($query, $params);
 973     $result = $dao->fetch() ? TRUE : FALSE;
 974     $dao->free();
 975     return $result;
 976   }
 977
 978   /**
 979    * @param $version
 980    *
 981    * @return bool
 982    */
 983   public function checkVersion($version) {
 984     $query = "
 985 SELECT version
 986 FROM   civicrm_domain
 987 ";
 988     $dbVersion = CRM_Core_DAO::singleValueQuery($query);
 989     return trim($version) == trim($dbVersion) ? TRUE : FALSE;
 990   }
 991
 992   /**
 993    * Find a DAO object for the given ID and return it.
 994    *
 995    * @param int $id
 996    *   Id of the DAO object being searched for.
 997    *
 998    * @return object
 999    *   Object of the type of the class that called this function.
1000    */
1001   public static function findById($id) {
1002     $object = new static();
1003     $object->id = $id;
1004     if (!$object->find(TRUE)) {
1005       throw new Exception("Unable to find a " . get_called_class() . " with id {$id}.");
1006     }
1007     return $object;
1008   }
1009
1010   /**
1011    * Returns all results as array-encoded records.
1012    *
1013    * @return array
1014    */
1015   public function fetchAll() {
1016     $result = array();
1017     while ($this->fetch()) {
1018       $result[] = $this->toArray();
1019     }
1020     return $result;
1021   }
1022
1023   /**
1024    * Given a DAO name, a column name and a column value, find the record and GET the value of another column in that record
1025    *
1026    * @param string $daoName
1027    *   Name of the DAO (Example: CRM_Contact_DAO_Contact to retrieve value from a contact).
1028    * @param int $searchValue
1029    *   Value of the column you want to search by.
1030    * @param string $returnColumn
1031    *   Name of the column you want to GET the value of.
1032    * @param string $searchColumn
1033    *   Name of the column you want to search by.
1034    * @param bool $force
1035    *   Skip use of the cache.
1036    *
1037    * @return string|null
1038    *   Value of $returnColumn in the retrieved record
1039    */
1040   public static function getFieldValue($daoName, $searchValue, $returnColumn = 'name', $searchColumn = 'id', $force = FALSE) {
1041     if (
1042       empty($searchValue) ||
1043       trim(strtolower($searchValue)) == 'null'
1044     ) {
1045       // adding this year since developers forget to check for an id
1046       // or for the 'null' (which is a bad DAO kludge)
1047       // and hence we get the first value in the db
1048       CRM_Core_Error::fatal();
1049     }
1050
1051     $cacheKey = "{$daoName}:{$searchValue}:{$returnColumn}:{$searchColumn}";
1052     if (self::$_dbColumnValueCache === NULL) {
1053       self::$_dbColumnValueCache = array();
1054     }
1055
1056     if (!array_key_exists($cacheKey, self::$_dbColumnValueCache) || $force) {
1057       $object = new $daoName();
1058       $object->$searchColumn = $searchValue;
1059       $object->selectAdd();
1060       $object->selectAdd($returnColumn);
1061
1062       $result = NULL;
1063       if ($object->find(TRUE)) {
1064         $result = $object->$returnColumn;
1065       }
1066       $object->free();
1067
1068       self::$_dbColumnValueCache[$cacheKey] = $result;
1069     }
1070     return self::$_dbColumnValueCache[$cacheKey];
1071   }
1072
1073   /**
1074    * Given a DAO name, a column name and a column value, find the record and SET the value of another column in that record
1075    *
1076    * @param string $daoName
1077    *   Name of the DAO (Example: CRM_Contact_DAO_Contact to retrieve value from a contact).
1078    * @param int $searchValue
1079    *   Value of the column you want to search by.
1080    * @param string $setColumn
1081    *   Name of the column you want to SET the value of.
1082    * @param string $setValue
1083    *   SET the setColumn to this value.
1084    * @param string $searchColumn
1085    *   Name of the column you want to search by.
1086    *
1087    * @return bool
1088    *   true if we found and updated the object, else false
1089    */
1090   public static function setFieldValue($daoName, $searchValue, $setColumn, $setValue, $searchColumn = 'id') {
1091     $object = new $daoName();
1092     $object->selectAdd();
1093     $object->selectAdd("$searchColumn, $setColumn");
1094     $object->$searchColumn = $searchValue;
1095     $result = FALSE;
1096     if ($object->find(TRUE)) {
1097       $object->$setColumn = $setValue;
1098       if ($object->save()) {
1099         $result = TRUE;
1100       }
1101     }
1102     $object->free();
1103     return $result;
1104   }
1105
1106   /**
1107    * Get sort string.
1108    *
1109    * @param array|object $sort either array or CRM_Utils_Sort
1110    * @param string $default
1111    *   Default sort value.
1112    *
1113    * @return string
1114    *   sortString
1115    */
1116   public static function getSortString($sort, $default = NULL) {
1117     // check if sort is of type CRM_Utils_Sort
1118     if (is_a($sort, 'CRM_Utils_Sort')) {
1119       return $sort->orderBy();
1120     }
1121
1122     // is it an array specified as $field => $sortDirection ?
1123     if ($sort) {
1124       foreach ($sort as $k => $v) {
1125         $sortString .= "$k $v,";
1126       }
1127       return rtrim($sortString, ',');
1128     }
1129     return $default;
1130   }
1131
1132   /**
1133    * Fetch object based on array of properties.
1134    *
1135    * @param string $daoName
1136    *   Name of the dao object.
1137    * @param array $params
1138    *   (reference ) an assoc array of name/value pairs.
1139    * @param array $defaults
1140    *   (reference ) an assoc array to hold the flattened values.
1141    * @param array $returnProperities
1142    *   An assoc array of fields that need to be returned, eg array( 'first_name', 'last_name').
1143    *
1144    * @return object
1145    *   an object of type referenced by daoName
1146    */
1147   public static function commonRetrieve($daoName, &$params, &$defaults, $returnProperities = NULL) {
1148     $object = new $daoName();
1149     $object->copyValues($params);
1150
1151     // return only specific fields if returnproperties are sent
1152     if (!empty($returnProperities)) {
1153       $object->selectAdd();
1154       $object->selectAdd(implode(',', $returnProperities));
1155     }
1156
1157     if ($object->find(TRUE)) {
1158       self::storeValues($object, $defaults);
1159       return $object;
1160     }
1161     return NULL;
1162   }
1163
1164   /**
1165    * Delete the object records that are associated with this contact.
1166    *
1167    * @param string $daoName
1168    *   Name of the dao object.
1169    * @param int $contactId
1170    *   Id of the contact to delete.
1171    */
1172   public static function deleteEntityContact($daoName, $contactId) {
1173     $object = new $daoName();
1174
1175     $object->entity_table = 'civicrm_contact';
1176     $object->entity_id = $contactId;
1177     $object->delete();
1178   }
1179
1180   /**
1181    * execute an unbuffered query.  This is a wrapper around new functionality
1182    * exposed with CRM-17748.
1183    *
1184    * @param string $query query to be executed
1185    *
1186    * @return Object CRM_Core_DAO object that points to an unbuffered result set
1187    * @static
1188    * @access public
1189    */
1190   static public function executeUnbufferedQuery(
1191     $query,
1192     $params = array(),
1193     $abort = TRUE,
1194     $daoName = NULL,
1195     $freeDAO = FALSE,
1196     $i18nRewrite = TRUE,
1197     $trapException = FALSE
1198   ) {
1199     $queryStr = self::composeQuery($query, $params, $abort);
1200     //CRM_Core_Error::debug( 'q', $queryStr );
1201     if (!$daoName) {
1202       $dao = new CRM_Core_DAO();
1203     }
1204     else {
1205       $dao = new $daoName();
1206     }
1207
1208     if ($trapException) {
1209       CRM_Core_Error::ignoreException();
1210     }
1211
1212     // set the DAO object to use an unbuffered query
1213     $dao->setOptions(array('result_buffering' => 0));
1214
1215     $result = $dao->query($queryStr, $i18nRewrite);
1216
1217     if ($trapException) {
1218       CRM_Core_Error::setCallback();
1219     }
1220
1221     if (is_a($result, 'DB_Error')) {
1222       return $result;
1223     }
1224
1225     // since it is unbuffered, ($dao->N==0) is true.  This blocks the standard fetch() mechanism.
1226     $dao->N = TRUE;
1227
1228     if ($freeDAO ||
1229       preg_match('/^(insert|update|delete|create|drop|replace)/i', $queryStr)
1230     ) {
1231       // we typically do this for insert/update/delete stataments OR if explicitly asked to
1232       // free the dao
1233       $dao->free();
1234     }
1235     return $dao;
1236   }
1237
1238   /**
1239    * Execute a query.
1240    *
1241    * @param string $query
1242    *   Query to be executed.
1243    *
1244    * @param array $params
1245    * @param bool $abort
1246    * @param null $daoName
1247    * @param bool $freeDAO
1248    * @param bool $i18nRewrite
1249    * @param bool $trapException
1250    *
1251    * @return CRM_Core_DAO|object
1252    *   object that holds the results of the query
1253    *   NB - if this is defined as just returning a DAO phpstorm keeps pointing
1254    *   out all the properties that are not part of the DAO
1255    */
1256   public static function &executeQuery(
1257     $query,
1258     $params = array(),
1259     $abort = TRUE,
1260     $daoName = NULL,
1261     $freeDAO = FALSE,
1262     $i18nRewrite = TRUE,
1263     $trapException = FALSE
1264   ) {
1265     $queryStr = self::composeQuery($query, $params, $abort);
1266
1267     if (!$daoName) {
1268       $dao = new CRM_Core_DAO();
1269     }
1270     else {
1271       $dao = new $daoName();
1272     }
1273
1274     if ($trapException) {
1275       $errorScope = CRM_Core_TemporaryErrorScope::ignoreException();
1276     }
1277
1278     $result = $dao->query($queryStr, $i18nRewrite);
1279
1280     if (is_a($result, 'DB_Error')) {
1281       return $result;
1282     }
1283
1284     if ($freeDAO ||
1285       preg_match('/^(insert|update|delete|create|drop|replace)/i', $queryStr)
1286     ) {
1287       // we typically do this for insert/update/delete statements OR if explicitly asked to
1288       // free the dao
1289       $dao->free();
1290     }
1291     return $dao;
1292   }
1293
1294   /**
1295    * Execute a query and get the single result.
1296    *
1297    * @param string $query
1298    *   Query to be executed.
1299    * @param array $params
1300    * @param bool $abort
1301    * @param bool $i18nRewrite
1302    * @return string|null
1303    *   the result of the query if any
1304    *
1305    */
1306   public static function &singleValueQuery(
1307     $query,
1308     $params = array(),
1309     $abort = TRUE,
1310     $i18nRewrite = TRUE
1311   ) {
1312     $queryStr = self::composeQuery($query, $params, $abort);
1313
1314     static $_dao = NULL;
1315
1316     if (!$_dao) {
1317       $_dao = new CRM_Core_DAO();
1318     }
1319
1320     $_dao->query($queryStr, $i18nRewrite);
1321
1322     $result = $_dao->getDatabaseResult();
1323     $ret = NULL;
1324     if ($result) {
1325       $row = $result->fetchRow();
1326       if ($row) {
1327         $ret = $row[0];
1328       }
1329     }
1330     $_dao->free();
1331     return $ret;
1332   }
1333
1334   /**
1335    * Compose the query by merging the parameters into it.
1336    *
1337    * @param string $query
1338    * @param array $params
1339    * @param bool $abort
1340    *
1341    * @return string
1342    * @throws Exception
1343    */
1344   public static function composeQuery($query, $params, $abort = TRUE) {
1345     $tr = array();
1346     foreach ($params as $key => $item) {
1347       if (is_numeric($key)) {
1348         if (CRM_Utils_Type::validate($item[0], $item[1]) !== NULL) {
1349           $item[0] = self::escapeString($item[0]);
1350           if ($item[1] == 'String' ||
1351             $item[1] == 'Memo' ||
1352             $item[1] == 'Link'
1353           ) {
1354             // Support class constants stipulating wildcard characters and/or
1355             // non-quoting of strings. Also support legacy code which may be
1356             // passing in TRUE or 1 for $item[2], which used to indicate the
1357             // use of wildcard characters.
1358             if (!empty($item[2])) {
1359               if ($item[2] & CRM_Core_DAO::QUERY_FORMAT_WILDCARD || $item[2] === TRUE) {
1360                 $item[0] = "'%{$item[0]}%'";
1361               }
1362               elseif (!($item[2] & CRM_Core_DAO::QUERY_FORMAT_NO_QUOTES)) {
1363                 $item[0] = "'{$item[0]}'";
1364               }
1365             }
1366             else {
1367               $item[0] = "'{$item[0]}'";
1368             }
1369           }
1370
1371           if (($item[1] == 'Date' || $item[1] == 'Timestamp') &&
1372             strlen($item[0]) == 0
1373           ) {
1374             $item[0] = 'null';
1375           }
1376
1377           $tr['%' . $key] = $item[0];
1378         }
1379         elseif ($abort) {
1380           CRM_Core_Error::fatal("{$item[0]} is not of type {$item[1]}");
1381         }
1382       }
1383     }
1384
1385     return strtr($query, $tr);
1386   }
1387
1388   /**
1389    * @param null $ids
1390    */
1391   public static function freeResult($ids = NULL) {
1392     global $_DB_DATAOBJECT;
1393
1394     if (!$ids) {
1395       if (!$_DB_DATAOBJECT ||
1396         !isset($_DB_DATAOBJECT['RESULTS'])
1397       ) {
1398         return;
1399       }
1400       $ids = array_keys($_DB_DATAOBJECT['RESULTS']);
1401     }
1402
1403     foreach ($ids as $id) {
1404       if (isset($_DB_DATAOBJECT['RESULTS'][$id])) {
1405         if (is_resource($_DB_DATAOBJECT['RESULTS'][$id]->result)) {
1406           mysql_free_result($_DB_DATAOBJECT['RESULTS'][$id]->result);
1407         }
1408         unset($_DB_DATAOBJECT['RESULTS'][$id]);
1409       }
1410
1411       if (isset($_DB_DATAOBJECT['RESULTFIELDS'][$id])) {
1412         unset($_DB_DATAOBJECT['RESULTFIELDS'][$id]);
1413       }
1414     }
1415   }
1416
1417   /**
1418    * make a shallow copy of an object.
1419    * and all the fields in the object
1420    *
1421    * @param string $daoName
1422    *   Name of the dao.
1423    * @param array $criteria
1424    *   Array of all the fields & values.
1425    *                                        on which basis to copy
1426    * @param array $newData
1427    *   Array of all the fields & values.
1428    *                                        to be copied besides the other fields
1429    * @param string $fieldsFix
1430    *   Array of fields that you want to prefix/suffix/replace.
1431    * @param string $blockCopyOfDependencies
1432    *   Fields that you want to block from.
1433    *                                        getting copied
1434    *
1435    *
1436    * @return CRM_Core_DAO
1437    *   the newly created copy of the object
1438    */
1439   public static function &copyGeneric($daoName, $criteria, $newData = NULL, $fieldsFix = NULL, $blockCopyOfDependencies = NULL) {
1440     $object = new $daoName();
1441     if (!$newData) {
1442       $object->id = $criteria['id'];
1443     }
1444     else {
1445       foreach ($criteria as $key => $value) {
1446         $object->$key = $value;
1447       }
1448     }
1449
1450     $object->find();
1451     while ($object->fetch()) {
1452
1453       // all the objects except with $blockCopyOfDependencies set
1454       // be copied - addresses #CRM-1962
1455
1456       if ($blockCopyOfDependencies && $object->$blockCopyOfDependencies) {
1457         break;
1458       }
1459
1460       $newObject = new $daoName();
1461
1462       $fields = &$object->fields();
1463       if (!is_array($fieldsFix)) {
1464         $fieldsToPrefix = array();
1465         $fieldsToSuffix = array();
1466         $fieldsToReplace = array();
1467       }
1468       if (!empty($fieldsFix['prefix'])) {
1469         $fieldsToPrefix = $fieldsFix['prefix'];
1470       }
1471       if (!empty($fieldsFix['suffix'])) {
1472         $fieldsToSuffix = $fieldsFix['suffix'];
1473       }
1474       if (!empty($fieldsFix['replace'])) {
1475         $fieldsToReplace = $fieldsFix['replace'];
1476       }
1477
1478       foreach ($fields as $name => $value) {
1479         if ($name == 'id' || $value['name'] == 'id') {
1480           // copy everything but the id!
1481           continue;
1482         }
1483
1484         $dbName = $value['name'];
1485         $type = CRM_Utils_Type::typeToString($value['type']);
1486         $newObject->$dbName = $object->$dbName;
1487         if (isset($fieldsToPrefix[$dbName])) {
1488           $newObject->$dbName = $fieldsToPrefix[$dbName] . $newObject->$dbName;
1489         }
1490         if (isset($fieldsToSuffix[$dbName])) {
1491           $newObject->$dbName .= $fieldsToSuffix[$dbName];
1492         }
1493         if (isset($fieldsToReplace[$dbName])) {
1494           $newObject->$dbName = $fieldsToReplace[$dbName];
1495         }
1496
1497         if ($type == 'Timestamp' || $type == 'Date') {
1498           $newObject->$dbName = CRM_Utils_Date::isoToMysql($newObject->$dbName);
1499         }
1500
1501         if ($newData) {
1502           foreach ($newData as $k => $v) {
1503             $newObject->$k = $v;
1504           }
1505         }
1506       }
1507       $newObject->save();
1508     }
1509     return $newObject;
1510   }
1511
1512   /**
1513    * Cascade update through related entities.
1514    *
1515    * @param string $daoName
1516    * @param $fromId
1517    * @param $toId
1518    * @param array $newData
1519    *
1520    * @return null
1521    */
1522   public static function cascadeUpdate($daoName, $fromId, $toId, $newData = array()) {
1523     $object = new $daoName();
1524     $object->id = $fromId;
1525
1526     if ($object->find(TRUE)) {
1527       $newObject = new $daoName();
1528       $newObject->id = $toId;
1529
1530       if ($newObject->find(TRUE)) {
1531         $fields = &$object->fields();
1532         foreach ($fields as $name => $value) {
1533           if ($name == 'id' || $value['name'] == 'id') {
1534             // copy everything but the id!
1535             continue;
1536           }
1537
1538           $colName = $value['name'];
1539           $newObject->$colName = $object->$colName;
1540
1541           if (substr($name, -5) == '_date' ||
1542             substr($name, -10) == '_date_time'
1543           ) {
1544             $newObject->$colName = CRM_Utils_Date::isoToMysql($newObject->$colName);
1545           }
1546         }
1547         foreach ($newData as $k => $v) {
1548           $newObject->$k = $v;
1549         }
1550         $newObject->save();
1551         return $newObject;
1552       }
1553     }
1554     return CRM_Core_DAO::$_nullObject;
1555   }
1556
1557   /**
1558    * Given the component id, compute the contact id
1559    * since its used for things like send email
1560    *
1561    * @param $componentIDs
1562    * @param string $tableName
1563    *
1564    * @return array
1565    */
1566   public static function &getContactIDsFromComponent(&$componentIDs, $tableName) {
1567     $contactIDs = array();
1568
1569     if (empty($componentIDs)) {
1570       return $contactIDs;
1571     }
1572
1573     $IDs = implode(',', $componentIDs);
1574     $query = "
1575 SELECT contact_id
1576   FROM $tableName
1577  WHERE id IN ( $IDs )
1578 ";
1579
1580     $dao = CRM_Core_DAO::executeQuery($query);
1581     while ($dao->fetch()) {
1582       $contactIDs[] = $dao->contact_id;
1583     }
1584     return $contactIDs;
1585   }
1586
1587   /**
1588    * Fetch object based on array of properties.
1589    *
1590    * @param string $daoName
1591    *   Name of the dao object.
1592    * @param string $fieldIdName
1593    * @param int $fieldId
1594    * @param $details
1595    * @param array $returnProperities
1596    *   An assoc array of fields that need to be returned, eg array( 'first_name', 'last_name').
1597    *
1598    * @return object
1599    *   an object of type referenced by daoName
1600    */
1601   public static function commonRetrieveAll($daoName, $fieldIdName = 'id', $fieldId, &$details, $returnProperities = NULL) {
1602     require_once str_replace('_', DIRECTORY_SEPARATOR, $daoName) . ".php";
1603     $object = new $daoName();
1604     $object->$fieldIdName = $fieldId;
1605
1606     // return only specific fields if returnproperties are sent
1607     if (!empty($returnProperities)) {
1608       $object->selectAdd();
1609       $object->selectAdd('id');
1610       $object->selectAdd(implode(',', $returnProperities));
1611     }
1612
1613     $object->find();
1614     while ($object->fetch()) {
1615       $defaults = array();
1616       self::storeValues($object, $defaults);
1617       $details[$object->id] = $defaults;
1618     }
1619
1620     return $details;
1621   }
1622
1623   public static function dropAllTables() {
1624
1625     // first drop all the custom tables we've created
1626     CRM_Core_BAO_CustomGroup::dropAllTables();
1627
1628     // drop all multilingual views
1629     CRM_Core_I18n_Schema::dropAllViews();
1630
1631     CRM_Utils_File::sourceSQLFile(CIVICRM_DSN,
1632       dirname(__FILE__) . DIRECTORY_SEPARATOR .
1633       '..' . DIRECTORY_SEPARATOR .
1634       '..' . DIRECTORY_SEPARATOR .
1635       'sql' . DIRECTORY_SEPARATOR .
1636       'civicrm_drop.mysql'
1637     );
1638   }
1639
1640   /**
1641    * @param $string
1642    *
1643    * @return string
1644    */
1645   public static function escapeString($string) {
1646     static $_dao = NULL;
1647
1648     if (!$_dao) {
1649       // If this is an atypical case (e.g. preparing .sql files
1650       // before Civi has been installed), then we fallback to
1651       // DB-less escaping helper (mysql_real_escape_string).
1652       // Note: In typical usage, escapeString() will only
1653       // check one conditional ("if !$_dao") rather than
1654       // two conditionals ("if !defined(DSN)")
1655       if (!defined('CIVICRM_DSN')) {
1656         if (function_exists('mysql_real_escape_string')) {
1657           return mysql_real_escape_string($string);
1658         }
1659         else {
1660           throw new CRM_Core_Exception("Cannot generate SQL. \"mysql_real_escape_string\" is missing. Have you installed PHP \"mysql\" extension?");
1661         }
1662       }
1663
1664       $_dao = new CRM_Core_DAO();
1665     }
1666
1667     return $_dao->escape($string);
1668   }
1669
1670   /**
1671    * Escape a list of strings for use with "WHERE X IN (...)" queries.
1672    *
1673    * @param array $strings
1674    * @param string $default
1675    *   the value to use if $strings has no elements.
1676    * @return string
1677    *   eg "abc","def","ghi"
1678    */
1679   public static function escapeStrings($strings, $default = NULL) {
1680     static $_dao = NULL;
1681     if (!$_dao) {
1682       $_dao = new CRM_Core_DAO();
1683     }
1684
1685     if (empty($strings)) {
1686       return $default;
1687     }
1688
1689     $escapes = array_map(array($_dao, 'escape'), $strings);
1690     return '"' . implode('","', $escapes) . '"';
1691   }
1692
1693   /**
1694    * @param $string
1695    *
1696    * @return string
1697    */
1698   public static function escapeWildCardString($string) {
1699     // CRM-9155
1700     // ensure we escape the single characters % and _ which are mysql wild
1701     // card characters and could come in via sortByCharacter
1702     // note that mysql does not escape these characters
1703     if ($string && in_array($string,
1704         array('%', '_', '%%', '_%')
1705       )
1706     ) {
1707       return '\\' . $string;
1708     }
1709
1710     return self::escapeString($string);
1711   }
1712
1713   /**
1714    * Creates a test object, including any required objects it needs via recursion
1715    * createOnly: only create in database, do not store or return the objects (useful for perf testing)
1716    * ONLY USE FOR TESTING
1717    *
1718    * @param string $daoName
1719    * @param array $params
1720    * @param int $numObjects
1721    * @param bool $createOnly
1722    *
1723    * @return object|array|NULL
1724    *   NULL if $createOnly. A single object if $numObjects==1. Otherwise, an array of multiple objects.
1725    */
1726   public static function createTestObject(
1727     $daoName,
1728     $params = array(),
1729     $numObjects = 1,
1730     $createOnly = FALSE
1731   ) {
1732     //this is a test function  also backtrace is set for the test suite it sometimes unsets itself
1733     // so we re-set here in case
1734     $config = CRM_Core_Config::singleton();
1735     $config->backtrace = TRUE;
1736
1737     static $counter = 0;
1738     CRM_Core_DAO::$_testEntitiesToSkip = array(
1739       'CRM_Core_DAO_Worldregion',
1740       'CRM_Core_DAO_StateProvince',
1741       'CRM_Core_DAO_Country',
1742       'CRM_Core_DAO_Domain',
1743       'CRM_Financial_DAO_FinancialType',
1744       //because valid ones exist & we use pick them due to pseudoconstant can't reliably create & delete these
1745     );
1746
1747     // Prefer to instantiate BAO's instead of DAO's (when possible)
1748     // so that assignTestValue()/assignTestFK() can be overloaded.
1749     $baoName = str_replace('_DAO_', '_BAO_', $daoName);
1750     if (class_exists($baoName)) {
1751       $daoName = $baoName;
1752     }
1753
1754     for ($i = 0; $i < $numObjects; ++$i) {
1755
1756       ++$counter;
1757       /** @var CRM_Core_DAO $object */
1758       $object = new $daoName();
1759
1760       $fields = &$object->fields();
1761       foreach ($fields as $fieldName => $fieldDef) {
1762         $dbName = $fieldDef['name'];
1763         $FKClassName = CRM_Utils_Array::value('FKClassName', $fieldDef);
1764         $required = CRM_Utils_Array::value('required', $fieldDef);
1765
1766         if (CRM_Utils_Array::value($dbName, $params) !== NULL && !is_array($params[$dbName])) {
1767           $object->$dbName = $params[$dbName];
1768         }
1769
1770         elseif ($dbName != 'id') {
1771           if ($FKClassName != NULL) {
1772             $object->assignTestFK($fieldName, $fieldDef, $params);
1773             continue;
1774           }
1775           else {
1776             $object->assignTestValue($fieldName, $fieldDef, $counter);
1777           }
1778         }
1779       }
1780
1781       $object->save();
1782
1783       if (!$createOnly) {
1784         $objects[$i] = $object;
1785       }
1786       else {
1787         unset($object);
1788       }
1789     }
1790
1791     if ($createOnly) {
1792       return NULL;
1793     }
1794     elseif ($numObjects == 1) {
1795       return $objects[0];
1796     }
1797     else {
1798       return $objects;
1799     }
1800   }
1801
1802   /**
1803    * Deletes the this object plus any dependent objects that are associated with it.
1804    * ONLY USE FOR TESTING
1805    *
1806    * @param string $daoName
1807    * @param array $params
1808    */
1809   public static function deleteTestObjects($daoName, $params = array()) {
1810     //this is a test function  also backtrace is set for the test suite it sometimes unsets itself
1811     // so we re-set here in case
1812     $config = CRM_Core_Config::singleton();
1813     $config->backtrace = TRUE;
1814
1815     $object = new $daoName();
1816     $object->id = CRM_Utils_Array::value('id', $params);
1817
1818     $deletions = array(); // array(array(0 => $daoName, 1 => $daoParams))
1819     if ($object->find(TRUE)) {
1820
1821       $fields = &$object->fields();
1822       foreach ($fields as $name => $value) {
1823
1824         $dbName = $value['name'];
1825
1826         $FKClassName = CRM_Utils_Array::value('FKClassName', $value);
1827         $required = CRM_Utils_Array::value('required', $value);
1828         if ($FKClassName != NULL
1829           && $object->$dbName
1830           && !in_array($FKClassName, CRM_Core_DAO::$_testEntitiesToSkip)
1831           && ($required || $dbName == 'contact_id')
1832           //I'm a bit stuck on this one - we might need to change the singleValueAlter so that the entities don't share a contact
1833           // to make this test process pass - line below makes pass for now
1834           && $dbName != 'member_of_contact_id'
1835         ) {
1836           $deletions[] = array($FKClassName, array('id' => $object->$dbName)); // x
1837         }
1838       }
1839     }
1840
1841     $object->delete();
1842
1843     foreach ($deletions as $deletion) {
1844       CRM_Core_DAO::deleteTestObjects($deletion[0], $deletion[1]);
1845     }
1846   }
1847
1848   /**
1849    * Set defaults when creating new entity.
1850    * (don't call this set defaults as already in use with different signature in some places)
1851    *
1852    * @param array $params
1853    * @param $defaults
1854    */
1855   public static function setCreateDefaults(&$params, $defaults) {
1856     if (!empty($params['id'])) {
1857       return;
1858     }
1859     foreach ($defaults as $key => $value) {
1860       if (!array_key_exists($key, $params) || $params[$key] === NULL) {
1861         $params[$key] = $value;
1862       }
1863     }
1864   }
1865
1866   /**
1867    * @param string $prefix
1868    * @param bool $addRandomString
1869    * @param null $string
1870    *
1871    * @return string
1872    */
1873   public static function createTempTableName($prefix = 'civicrm', $addRandomString = TRUE, $string = NULL) {
1874     $tableName = $prefix . "_temp";
1875
1876     if ($addRandomString) {
1877       if ($string) {
1878         $tableName .= "_" . $string;
1879       }
1880       else {
1881         $tableName .= "_" . md5(uniqid('', TRUE));
1882       }
1883     }
1884     return $tableName;
1885   }
1886
1887   /**
1888    * @param bool $view
1889    * @param bool $trigger
1890    *
1891    * @return bool
1892    */
1893   public static function checkTriggerViewPermission($view = TRUE, $trigger = TRUE) {
1894     // test for create view and trigger permissions and if allowed, add the option to go multilingual
1895     // and logging
1896     // I'm not sure why we use the getStaticProperty for an error, rather than checking for DB_Error
1897     $errorScope = CRM_Core_TemporaryErrorScope::ignoreException();
1898     $dao = new CRM_Core_DAO();
1899     if ($view) {
1900       $dao->query('CREATE OR REPLACE VIEW civicrm_domain_view AS SELECT * FROM civicrm_domain');
1901       if (PEAR::getStaticProperty('DB_DataObject', 'lastError')) {
1902         return FALSE;
1903       }
1904     }
1905
1906     if ($trigger) {
1907       $result = $dao->query('CREATE TRIGGER civicrm_domain_trigger BEFORE INSERT ON civicrm_domain FOR EACH ROW BEGIN END');
1908       if (PEAR::getStaticProperty('DB_DataObject', 'lastError') || is_a($result, 'DB_Error')) {
1909         if ($view) {
1910           $dao->query('DROP VIEW IF EXISTS civicrm_domain_view');
1911         }
1912         return FALSE;
1913       }
1914
1915       $dao->query('DROP TRIGGER IF EXISTS civicrm_domain_trigger');
1916       if (PEAR::getStaticProperty('DB_DataObject', 'lastError')) {
1917         if ($view) {
1918           $dao->query('DROP VIEW IF EXISTS civicrm_domain_view');
1919         }
1920         return FALSE;
1921       }
1922     }
1923
1924     if ($view) {
1925       $dao->query('DROP VIEW IF EXISTS civicrm_domain_view');
1926       if (PEAR::getStaticProperty('DB_DataObject', 'lastError')) {
1927         return FALSE;
1928       }
1929     }
1930
1931     return TRUE;
1932   }
1933
1934   /**
1935    * @param null $message
1936    * @param bool $printDAO
1937    */
1938   public static function debugPrint($message = NULL, $printDAO = TRUE) {
1939     CRM_Utils_System::xMemory("{$message}: ");
1940
1941     if ($printDAO) {
1942       global $_DB_DATAOBJECT;
1943       $q = array();
1944       foreach (array_keys($_DB_DATAOBJECT['RESULTS']) as $id) {
1945         $q[] = $_DB_DATAOBJECT['RESULTS'][$id]->query;
1946       }
1947       CRM_Core_Error::debug('_DB_DATAOBJECT', $q);
1948     }
1949   }
1950
1951   /**
1952    * Build a list of triggers via hook and add them to (err, reconcile them
1953    * with) the database.
1954    *
1955    * @param string $tableName
1956    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
1957    * @param bool $force
1958    * @deprecated
1959    *
1960    * @see CRM-9716
1961    */
1962   public static function triggerRebuild($tableName = NULL, $force = FALSE) {
1963     Civi::service('sql_triggers')->rebuild($tableName, $force);
1964   }
1965
1966   /**
1967    * Because sql functions are sometimes lost, esp during db migration, we check here to avoid numerous support requests
1968    * @see http://issues.civicrm.org/jira/browse/CRM-13822
1969    * TODO: Alternative solutions might be
1970    *  * Stop using functions and find another way to strip numeric characters from phones
1971    *  * Give better error messages (currently a missing fn fatals with "unknown error")
1972    */
1973   public static function checkSqlFunctionsExist() {
1974     if (!self::$_checkedSqlFunctionsExist) {
1975       self::$_checkedSqlFunctionsExist = TRUE;
1976       $dao = CRM_Core_DAO::executeQuery("SHOW function status WHERE db = database() AND name = 'civicrm_strip_non_numeric'");
1977       if (!$dao->fetch()) {
1978         self::triggerRebuild();
1979       }
1980     }
1981   }
1982
1983   /**
1984    * Wrapper function to drop triggers.
1985    *
1986    * @param string $tableName
1987    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
1988    * @deprecated
1989    */
1990   public static function dropTriggers($tableName = NULL) {
1991     Civi::service('sql_triggers')->dropTriggers($tableName);
1992   }
1993
1994   /**
1995    * @param array $info
1996    *   per hook_civicrm_triggerInfo.
1997    * @param string $onlyTableName
1998    *   the specific table requiring a rebuild; or NULL to rebuild all tables.
1999    * @deprecated
2000    */
2001   public static function createTriggers(&$info, $onlyTableName = NULL) {
2002     Civi::service('sql_triggers')->createTriggers($info, $onlyTableName);
2003   }
2004
2005   /**
2006    * Given a list of fields, create a list of references.
2007    *
2008    * @param string $className
2009    *   BAO/DAO class name.
2010    * @return array<CRM_Core_Reference_Interface>
2011    */
2012   public static function createReferenceColumns($className) {
2013     $result = array();
2014     $fields = $className::fields();
2015     foreach ($fields as $field) {
2016       if (isset($field['pseudoconstant'], $field['pseudoconstant']['optionGroupName'])) {
2017         $result[] = new CRM_Core_Reference_OptionValue(
2018           $className::getTableName(),
2019           $field['name'],
2020           'civicrm_option_value',
2021           CRM_Utils_Array::value('keyColumn', $field['pseudoconstant'], 'value'),
2022           $field['pseudoconstant']['optionGroupName']
2023         );
2024       }
2025     }
2026     return $result;
2027   }
2028
2029   /**
2030    * Find all records which refer to this entity.
2031    *
2032    * @return array
2033    *   Array of objects referencing this
2034    */
2035   public function findReferences() {
2036     $links = self::getReferencesToTable(static::getTableName());
2037
2038     $occurrences = array();
2039     foreach ($links as $refSpec) {
2040       /** @var $refSpec CRM_Core_Reference_Interface */
2041       $daoName = CRM_Core_DAO_AllCoreTables::getClassForTable($refSpec->getReferenceTable());
2042       $result = $refSpec->findReferences($this);
2043       if ($result) {
2044         while ($result->fetch()) {
2045           $obj = new $daoName();
2046           $obj->id = $result->id;
2047           $occurrences[] = $obj;
2048         }
2049       }
2050     }
2051
2052     return $occurrences;
2053   }
2054
2055   /**
2056    * @return array
2057    *   each item has keys:
2058    *   - name: string
2059    *   - type: string
2060    *   - count: int
2061    *   - table: string|null SQL table name
2062    *   - key: string|null SQL column name
2063    */
2064   public function getReferenceCounts() {
2065     $links = self::getReferencesToTable(static::getTableName());
2066
2067     $counts = array();
2068     foreach ($links as $refSpec) {
2069       /** @var $refSpec CRM_Core_Reference_Interface */
2070       $count = $refSpec->getReferenceCount($this);
2071       if ($count['count'] != 0) {
2072         $counts[] = $count;
2073       }
2074     }
2075
2076     foreach (CRM_Core_Component::getEnabledComponents() as $component) {
2077       /** @var $component CRM_Core_Component_Info */
2078       $counts = array_merge($counts, $component->getReferenceCounts($this));
2079     }
2080     CRM_Utils_Hook::referenceCounts($this, $counts);
2081
2082     return $counts;
2083   }
2084
2085   /**
2086    * List all tables which have hard foreign keys to this table.
2087    *
2088    * For now, this returns a description of every entity_id/entity_table
2089    * reference.
2090    * TODO: filter dynamic entity references on the $tableName, based on
2091    * schema metadata in dynamicForeignKey which enumerates a restricted
2092    * set of possible entity_table's.
2093    *
2094    * @param string $tableName
2095    *   Table referred to.
2096    *
2097    * @return array
2098    *   structure of table and column, listing every table with a
2099    *   foreign key reference to $tableName, and the column where the key appears.
2100    */
2101   public static function getReferencesToTable($tableName) {
2102     $refsFound = array();
2103     foreach (CRM_Core_DAO_AllCoreTables::getClasses() as $daoClassName) {
2104       $links = $daoClassName::getReferenceColumns();
2105       $daoTableName = $daoClassName::getTableName();
2106
2107       foreach ($links as $refSpec) {
2108         /** @var $refSpec CRM_Core_Reference_Interface */
2109         if ($refSpec->matchesTargetTable($tableName)) {
2110           $refsFound[] = $refSpec;
2111         }
2112       }
2113     }
2114     return $refsFound;
2115   }
2116
2117   /**
2118    * Lookup the value of a MySQL global configuration variable.
2119    *
2120    * @param string $name
2121    *   E.g. "thread_stack".
2122    * @param mixed $default
2123    * @return mixed
2124    */
2125   public static function getGlobalSetting($name, $default = NULL) {
2126     // Alternatively, SELECT @@GLOBAL.thread_stack, but
2127     // that has been reported to fail under MySQL 5.0 for OS X
2128     $escapedName = self::escapeString($name);
2129     $dao = CRM_Core_DAO::executeQuery("SHOW VARIABLES LIKE '$escapedName'");
2130     if ($dao->fetch()) {
2131       return $dao->Value;
2132     }
2133     else {
2134       return $default;
2135     }
2136   }
2137
2138   /**
2139    * Get options for the called BAO object's field.
2140    * This function can be overridden by each BAO to add more logic related to context.
2141    * The overriding function will generally call the lower-level CRM_Core_PseudoConstant::get
2142    *
2143    * @param string $fieldName
2144    * @param string $context
2145    * @see CRM_Core_DAO::buildOptionsContext
2146    * @param array $props
2147    *   whatever is known about this bao object.
2148    *
2149    * @return array|bool
2150    */
2151   public static function buildOptions($fieldName, $context = NULL, $props = array()) {
2152     // If a given bao does not override this function
2153     $baoName = get_called_class();
2154     return CRM_Core_PseudoConstant::get($baoName, $fieldName, array(), $context);
2155   }
2156
2157   /**
2158    * Populate option labels for this object's fields.
2159    *
2160    * @throws exception if called directly on the base class
2161    */
2162   public function getOptionLabels() {
2163     $fields = $this->fields();
2164     if ($fields === NULL) {
2165       throw new Exception('Cannot call getOptionLabels on CRM_Core_DAO');
2166     }
2167     foreach ($fields as $field) {
2168       $name = CRM_Utils_Array::value('name', $field);
2169       if ($name && isset($this->$name)) {
2170         $label = CRM_Core_PseudoConstant::getLabel(get_class($this), $name, $this->$name);
2171         if ($label !== FALSE) {
2172           // Append 'label' onto the field name
2173           $labelName = $name . '_label';
2174           $this->$labelName = $label;
2175         }
2176       }
2177     }
2178   }
2179
2180   /**
2181    * Provides documentation and validation for the buildOptions $context param
2182    *
2183    * @param string $context
2184    *
2185    * @throws Exception
2186    * @return array
2187    */
2188   public static function buildOptionsContext($context = NULL) {
2189     $contexts = array(
2190       'get' => "get: all options are returned, even if they are disabled; labels are translated.",
2191       'create' => "create: options are filtered appropriately for the object being created/updated; labels are translated.",
2192       'search' => "search: searchable options are returned; labels are translated.",
2193       'validate' => "validate: all options are returned, even if they are disabled; machine names are used in place of labels.",
2194       'abbreviate' => "abbreviate: enabled options are returned; labels are replaced with abbreviations.",
2195       'match' => "match: enabled options are returned using machine names as keys; labels are translated.",
2196     );
2197     // Validation: enforce uniformity of this param
2198     if ($context !== NULL && !isset($contexts[$context])) {
2199       throw new Exception("'$context' is not a valid context for buildOptions.");
2200     }
2201     return $contexts;
2202   }
2203
2204   /**
2205    * @param string $fieldName
2206    * @return bool|array
2207    */
2208   public function getFieldSpec($fieldName) {
2209     $fields = $this->fields();
2210     $fieldKeys = $this->fieldKeys();
2211
2212     // Support "unique names" as well as sql names
2213     $fieldKey = $fieldName;
2214     if (empty($fields[$fieldKey])) {
2215       $fieldKey = CRM_Utils_Array::value($fieldName, $fieldKeys);
2216     }
2217     // If neither worked then this field doesn't exist. Return false.
2218     if (empty($fields[$fieldKey])) {
2219       return FALSE;
2220     }
2221     return $fields[$fieldKey];
2222   }
2223
2224   /**
2225    * Get SQL where clause for SQL filter syntax input parameters.
2226    *
2227    * SQL version of api function to assign filters to the DAO based on the syntax
2228    * $field => array('IN' => array(4,6,9))
2229    * OR
2230    * $field => array('LIKE' => array('%me%))
2231    * etc
2232    *
2233    * @param string $fieldName
2234    *   Name of fields.
2235    * @param array $filter
2236    *   filter to be applied indexed by operator.
2237    * @param string $type
2238    *   type of field (not actually used - nor in api @todo ).
2239    * @param string $alias
2240    *   alternative field name ('as') @todo- not actually used.
2241    * @param bool $returnSanitisedArray
2242    *   Return a sanitised array instead of a clause.
2243    *   this is primarily so we can add filters @ the api level to the Query object based fields
2244    *
2245    * @throws Exception
2246    *
2247    * @return NULL|string|array
2248    *   a string is returned if $returnSanitisedArray is not set, otherwise and Array or NULL
2249    *   depending on whether it is supported as yet
2250    */
2251   public static function createSQLFilter($fieldName, $filter, $type = NULL, $alias = NULL, $returnSanitisedArray = FALSE) {
2252     foreach ($filter as $operator => $criteria) {
2253       if (in_array($operator, self::acceptedSQLOperators(), TRUE)) {
2254         switch ($operator) {
2255           // unary operators
2256           case 'IS NULL':
2257           case 'IS NOT NULL':
2258             if (!$returnSanitisedArray) {
2259               return (sprintf('%s %s', $fieldName, $operator));
2260             }
2261             else {
2262               return (sprintf('%s %s ', $fieldName, $operator));
2263             }
2264             break;
2265
2266           // ternary operators
2267           case 'BETWEEN':
2268           case 'NOT BETWEEN':
2269             if (empty($criteria[0]) || empty($criteria[1])) {
2270               throw new Exception("invalid criteria for $operator");
2271             }
2272             if (!$returnSanitisedArray) {
2273               return (sprintf('%s ' . $operator . ' "%s" AND "%s"', $fieldName, CRM_Core_DAO::escapeString($criteria[0]), CRM_Core_DAO::escapeString($criteria[1])));
2274             }
2275             else {
2276               return NULL;  // not yet implemented (tests required to implement)
2277             }
2278             break;
2279
2280           // n-ary operators
2281           case 'IN':
2282           case 'NOT IN':
2283             if (empty($criteria)) {
2284               throw new Exception("invalid criteria for $operator");
2285             }
2286             $escapedCriteria = array_map(array(
2287               'CRM_Core_DAO',
2288               'escapeString',
2289             ), $criteria);
2290             if (!$returnSanitisedArray) {
2291               return (sprintf('%s %s ("%s")', $fieldName, $operator, implode('", "', $escapedCriteria)));
2292             }
2293             return $escapedCriteria;
2294
2295           // binary operators
2296
2297           default:
2298             if (!$returnSanitisedArray) {
2299               return (sprintf('%s %s "%s"', $fieldName, $operator, CRM_Core_DAO::escapeString($criteria)));
2300             }
2301             else {
2302               return NULL; // not yet implemented (tests required to implement)
2303             }
2304         }
2305       }
2306     }
2307   }
2308
2309   /**
2310    * @see http://issues.civicrm.org/jira/browse/CRM-9150
2311    * support for other syntaxes is discussed in ticket but being put off for now
2312    * @return array
2313    */
2314   public static function acceptedSQLOperators() {
2315     return array(
2316       '=',
2317       '<=',
2318       '>=',
2319       '>',
2320       '<',
2321       'LIKE',
2322       "<>",
2323       "!=",
2324       "NOT LIKE",
2325       'IN',
2326       'NOT IN',
2327       'BETWEEN',
2328       'NOT BETWEEN',
2329       'IS NOT NULL',
2330       'IS NULL',
2331     );
2332   }
2333
2334   /**
2335    * SQL has a limit of 64 characters on various names:
2336    * table name, trigger name, column name ...
2337    *
2338    * For custom groups and fields we generated names from user entered input
2339    * which can be longer than this length, this function helps with creating
2340    * strings that meet various criteria.
2341    *
2342    * @param string $string
2343    *   The string to be shortened.
2344    * @param int $length
2345    *   The max length of the string.
2346    *
2347    * @param bool $makeRandom
2348    *
2349    * @return string
2350    */
2351   public static function shortenSQLName($string, $length = 60, $makeRandom = FALSE) {
2352     // early return for strings that meet the requirements
2353     if (strlen($string) <= $length) {
2354       return $string;
2355     }
2356
2357     // easy return for calls that dont need a randomized uniq string
2358     if (!$makeRandom) {
2359       return substr($string, 0, $length);
2360     }
2361
2362     // the string is longer than the length and we need a uniq string
2363     // for the same tablename we need the same uniq string every time
2364     // hence we use md5 on the string, which is not random
2365     // we'll append 8 characters to the end of the tableName
2366     $md5string = substr(md5($string), 0, 8);
2367     return substr($string, 0, $length - 8) . "_{$md5string}";
2368   }
2369
2370   /**
2371    * https://issues.civicrm.org/jira/browse/CRM-17748
2372    * Sets the internal options to be used on a query
2373    *
2374    * @param array $options
2375    *
2376    */
2377   public function setOptions($options) {
2378     if (is_array($options)) {
2379       $this->_options = $options;
2380     }
2381   }
2382
2383   /**
2384    * https://issues.civicrm.org/jira/browse/CRM-17748
2385    * wrapper to pass internal DAO options down to DB_mysql/DB_Common level
2386    *
2387    * @param array $options
2388    *
2389    */
2390   protected function _setDBOptions($options) {
2391     global $_DB_DATAOBJECT;
2392
2393     if (is_array($options) && count($options)) {
2394       $conn = &$_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
2395       foreach ($options as $option_name => $option_value) {
2396         $conn->setOption($option_name, $option_value);
2397       }
2398     }
2399   }
2400
2401   /**
2402    * @deprecated
2403    * @param array $params
2404    */
2405   public function setApiFilter(&$params) {
2406   }
2407
2408   /**
2409    * Generates acl clauses suitable for adding to WHERE or ON when doing an api.get for this entity
2410    *
2411    * Return format is in the form of fieldname => clauses starting with an operator. e.g.:
2412    * @code
2413    *   array(
2414    *     'location_type_id' => array('IS NOT NULL', 'IN (1,2,3)')
2415    *   )
2416    * @endcode
2417    *
2418    * Note that all array keys must be actual field names in this entity. Use subqueries to filter on other tables e.g. custom values.
2419    *
2420    * @return array
2421    */
2422   public function addSelectWhereClause() {
2423     // This is the default fallback, and works for contact-related entities like Email, Relationship, etc.
2424     $clauses = array();
2425     foreach ($this->fields() as $fieldName => $field) {
2426       if (strpos($fieldName, 'contact_id') === 0 && CRM_Utils_Array::value('FKClassName', $field) == 'CRM_Contact_DAO_Contact') {
2427         $clauses[$fieldName] = CRM_Utils_SQL::mergeSubquery('Contact');
2428       }
2429     }
2430     CRM_Utils_Hook::selectWhereClause($this, $clauses);
2431     return $clauses;
2432   }
2433
2434   /**
2435    * This returns the final permissioned query string for this entity
2436    *
2437    * With acls from related entities + additional clauses from hook_civicrm_selectWhereClause
2438    *
2439    * @param string $tableAlias
2440    * @return array
2441    */
2442   public static function getSelectWhereClause($tableAlias = NULL) {
2443     $bao = new static();
2444     if ($tableAlias === NULL) {
2445       $tableAlias = $bao->tableName();
2446     }
2447     $clauses = array();
2448     foreach ((array) $bao->addSelectWhereClause() as $field => $vals) {
2449       $clauses[$field] = NULL;
2450       if ($vals) {
2451         $clauses[$field] = "`$tableAlias`.`$field` " . implode(" AND `$tableAlias`.`$field` ", (array) $vals);
2452       }
2453     }
2454     return $clauses;
2455   }
2456
2457   /**
2458    * function to check valid db name containing only characters in [0-9,a-z,A-Z_]
2459    *
2460    * @param $database
2461    *
2462    * @return bool
2463    */
2464   public static function requireValidDBName($database) {
2465     $matches = array();
2466     preg_match(
2467       "/^[0-9]*[a-zA-Z_]+[a-zA-Z0-9_]*$/",
2468       $database,
2469       $matches
2470     );
2471     if (empty($matches)) {
2472       return FALSE;
2473     }
2474     return TRUE;
2475   }
2476
2477 }