CRM/Core/DAO.php

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