tests/phpunit/CRM/Core/DAOTest.php

   1 <?php
   2
   3 /**
   4  * Class CRM_Core_DAOTest
   5  * @group headless
   6  */
   7 class CRM_Core_DAOTest extends CiviUnitTestCase {
   8
   9   const ABORTED_SQL = "_aborted_sql_";
  10
  11   public function testGetReferenceColumns() {
  12     // choose CRM_Core_DAO_Email as an arbitrary example
  13     $emailRefs = CRM_Core_DAO_Email::getReferenceColumns();
  14     $refsByTarget = [];
  15     foreach ($emailRefs as $refSpec) {
  16       $refsByTarget[$refSpec->getTargetTable()] = $refSpec;
  17     }
  18     $this->assertTrue(array_key_exists('civicrm_contact', $refsByTarget));
  19     $contactRef = $refsByTarget['civicrm_contact'];
  20     $this->assertEquals('contact_id', $contactRef->getReferenceKey());
  21     $this->assertEquals('id', $contactRef->getTargetKey());
  22     $this->assertEquals('CRM_Core_Reference_Basic', get_class($contactRef));
  23   }
  24
  25   public function testGetReferencesToTable() {
  26     $refs = CRM_Core_DAO::getReferencesToTable(CRM_Financial_DAO_FinancialType::getTableName());
  27     $refsBySource = [];
  28     foreach ($refs as $refSpec) {
  29       $refsBySource[$refSpec->getReferenceTable()] = $refSpec;
  30     }
  31     $this->assertTrue(array_key_exists('civicrm_entity_financial_account', $refsBySource));
  32     $genericRef = $refsBySource['civicrm_entity_financial_account'];
  33     $this->assertEquals('entity_id', $genericRef->getReferenceKey());
  34     $this->assertEquals('entity_table', $genericRef->getTypeColumn());
  35     $this->assertEquals('id', $genericRef->getTargetKey());
  36     $this->assertEquals('CRM_Core_Reference_Dynamic', get_class($genericRef));
  37   }
  38
  39   public function testFindReferences() {
  40     $params = [
  41       'first_name' => 'Testy',
  42       'last_name' => 'McScallion',
  43       'contact_type' => 'Individual',
  44     ];
  45
  46     $contact = CRM_Contact_BAO_Contact::add($params);
  47     $this->assertNotNull($contact->id);
  48
  49     $params = [
  50       'email' => 'spam@dev.null',
  51       'contact_id' => $contact->id,
  52       'is_primary' => 0,
  53       'location_type_id' => 1,
  54     ];
  55
  56     $email = CRM_Core_BAO_Email::add($params);
  57
  58     $refs = $contact->findReferences();
  59     $refsByTable = [];
  60     foreach ($refs as $refObj) {
  61       $refsByTable[$refObj->__table] = $refObj;
  62     }
  63
  64     $this->assertTrue(array_key_exists('civicrm_email', $refsByTable));
  65     $refDao = $refsByTable['civicrm_email'];
  66     $refDao->find(TRUE);
  67     $this->assertEquals($contact->id, $refDao->contact_id);
  68   }
  69
  70   /**
  71    * @return array
  72    */
  73   public function composeQueryExamples() {
  74     $cases = [];
  75     // $cases[] = array('Input-SQL', 'Input-Params', 'Expected-SQL');
  76
  77     $cases[0] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['', 'String']], 'UPDATE civicrm_foo SET bar = \'\''];
  78     $cases[1] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['the text', 'String']], 'UPDATE civicrm_foo SET bar = \'the text\''];
  79     $cases[2] = ['UPDATE civicrm_foo SET bar = %1', [1 => [NULL, 'String']], self::ABORTED_SQL];
  80     $cases[3] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['null', 'String']], 'UPDATE civicrm_foo SET bar = NULL'];
  81
  82     $cases[3] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['', 'Float']], self::ABORTED_SQL];
  83     $cases[4] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['1.23', 'Float']], 'UPDATE civicrm_foo SET bar = 1.23'];
  84     $cases[5] = ['UPDATE civicrm_foo SET bar = %1', [1 => [NULL, 'Float']], self::ABORTED_SQL];
  85     $cases[6] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['null', 'Float']], self::ABORTED_SQL];
  86
  87     $cases[11] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['', 'Money']], self::ABORTED_SQL];
  88     $cases[12] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['1.23', 'Money']], 'UPDATE civicrm_foo SET bar = 1.23'];
  89     $cases[13] = ['UPDATE civicrm_foo SET bar = %1', [1 => [NULL, 'Money']], self::ABORTED_SQL];
  90     $cases[14] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['null', 'Money']], self::ABORTED_SQL];
  91
  92     $cases[15] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['', 'Int']], self::ABORTED_SQL];
  93     $cases[16] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['123', 'Int']], 'UPDATE civicrm_foo SET bar = 123'];
  94     $cases[17] = ['UPDATE civicrm_foo SET bar = %1', [1 => [NULL, 'Int']], self::ABORTED_SQL];
  95     $cases[18] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['null', 'Int']], self::ABORTED_SQL];
  96
  97     $cases[19] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['', 'Timestamp']], 'UPDATE civicrm_foo SET bar = null'];
  98     $cases[20] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['20150102030405', 'Timestamp']], 'UPDATE civicrm_foo SET bar = 20150102030405'];
  99     $cases[21] = ['UPDATE civicrm_foo SET bar = %1', [1 => [NULL, 'Timestamp']], 'UPDATE civicrm_foo SET bar = null'];
 100     $cases[22] = ['UPDATE civicrm_foo SET bar = %1', [1 => ['null', 'Timestamp']], self::ABORTED_SQL];
 101
 102     // CASE: No params
 103     $cases[1000] = [
 104       'SELECT * FROM whatever',
 105       [],
 106       'SELECT * FROM whatever',
 107     ];
 108
 109     // CASE: Integer param
 110     $cases[1001] = [
 111       'SELECT * FROM whatever WHERE id = %1',
 112       [
 113         1 => [10, 'Integer'],
 114       ],
 115       'SELECT * FROM whatever WHERE id = 10',
 116     ];
 117
 118     // CASE: String param
 119     $cases[1002] = [
 120       'SELECT * FROM whatever WHERE name = %1',
 121       [
 122         1 => ['Alice', 'String'],
 123       ],
 124       'SELECT * FROM whatever WHERE name = \'Alice\'',
 125     ];
 126
 127     // CASE: Two params
 128     $cases[1003] = [
 129       'SELECT * FROM whatever WHERE name = %1 AND title = %2',
 130       [
 131         1 => ['Alice', 'String'],
 132         2 => ['Bob', 'String'],
 133       ],
 134       'SELECT * FROM whatever WHERE name = \'Alice\' AND title = \'Bob\'',
 135     ];
 136
 137     // CASE: Two params with special character (%1)
 138     $cases[1004] = [
 139       'SELECT * FROM whatever WHERE name = %1 AND title = %2',
 140       [
 141         1 => ['Alice %2', 'String'],
 142         2 => ['Bob', 'String'],
 143       ],
 144       'SELECT * FROM whatever WHERE name = \'Alice %2\' AND title = \'Bob\'',
 145     ];
 146
 147     // CASE: Two params with special character ($1)
 148     $cases[1005] = [
 149       'SELECT * FROM whatever WHERE name = %1 AND title = %2',
 150       [
 151         1 => ['Alice $1', 'String'],
 152         2 => ['Bob', 'String'],
 153       ],
 154       'SELECT * FROM whatever WHERE name = \'Alice $1\' AND title = \'Bob\'',
 155     ];
 156
 157     return $cases;
 158   }
 159
 160   /**
 161    * @dataProvider composeQueryExamples
 162    * @param $inputSql
 163    * @param $inputParams
 164    * @param $expectSql
 165    */
 166   public function testComposeQuery($inputSql, $inputParams, $expectSql) {
 167     $scope = CRM_Core_TemporaryErrorScope::useException();
 168     try {
 169       $actualSql = CRM_Core_DAO::composeQuery($inputSql, $inputParams);
 170     }
 171     catch (Exception $e) {
 172       $actualSql = self::ABORTED_SQL;
 173     }
 174     $this->assertEquals($expectSql, $actualSql);
 175   }
 176
 177   /**
 178    * CASE: Two params where the %2 is already present in the query
 179    * NOTE: This case should rightly FAIL, as using strstr in the replace mechanism will turn
 180    * the query into: SELECT * FROM whatever WHERE name = 'Alice' AND title = 'Bob' AND year LIKE ''Bob'012'
 181    * So, to avoid such ERROR, the query should be framed like:
 182    * 'SELECT * FROM whatever WHERE name = %1 AND title = %3 AND year LIKE '%2012'
 183    * $params[3] = array('Bob', 'String');
 184    * i.e. the place holder should be unique and should not contain in any other operational use in query
 185    */
 186   public function testComposeQueryFailure() {
 187     $cases[] = [
 188       'SELECT * FROM whatever WHERE name = %1 AND title = %2 AND year LIKE \'%2012\' ',
 189       [
 190         1 => ['Alice', 'String'],
 191         2 => ['Bob', 'String'],
 192       ],
 193       'SELECT * FROM whatever WHERE name = \'Alice\' AND title = \'Bob\' AND year LIKE \'%2012\' ',
 194     ];
 195     list($inputSql, $inputParams, $expectSql) = $cases[0];
 196     $actualSql = CRM_Core_DAO::composeQuery($inputSql, $inputParams);
 197     $this->assertFalse(($expectSql == $actualSql));
 198     unset($scope);
 199   }
 200
 201   /**
 202    * @return array
 203    */
 204   public function sqlNameDataProvider() {
 205     return [
 206       ['this is a long string', 30, FALSE, 'this is a long string'],
 207       [
 208         'this is an even longer string which is exactly 60 character',
 209         60,
 210         FALSE,
 211         'this is an even longer string which is exactly 60 character',
 212       ],
 213       [
 214         'this is an even longer string which is exactly 60 character',
 215         60,
 216         TRUE,
 217         'this is an even longer string which is exactly 60 character',
 218       ],
 219       [
 220         'this is an even longer string which is a bit more than 60 character',
 221         60,
 222         FALSE,
 223         'this is an even longer string which is a bit more than 60 ch',
 224       ],
 225       [
 226         'this is an even longer string which is a bit more than 60 character',
 227         60,
 228         TRUE,
 229         'this is an even longer string which is a bit more th_c1cbd519',
 230       ],
 231     ];
 232   }
 233
 234   /**
 235    * @dataProvider sqlNameDataProvider
 236    * @param $inputData
 237    * @param $length
 238    * @param $makeRandom
 239    * @param $expectedResult
 240    */
 241   public function testShortenSQLName($inputData, $length, $makeRandom, $expectedResult) {
 242     $this->assertEquals($expectedResult, CRM_Core_DAO::shortenSQLName($inputData, $length, $makeRandom));
 243   }
 244
 245   public function testFindById() {
 246     $params = $this->sampleContact('Individual', 4);
 247     $existing_contact = CRM_Contact_BAO_Contact::add($params);
 248     $contact = CRM_Contact_BAO_Contact::findById($existing_contact->id);
 249     $this->assertEquals($existing_contact->id, $contact->id);
 250     $deleted_contact_id = $existing_contact->id;
 251     CRM_Contact_BAO_Contact::deleteContact($contact->id, FALSE, TRUE);
 252     $exception_thrown = FALSE;
 253     try {
 254       $deleted_contact = CRM_Contact_BAO_Contact::findById($deleted_contact_id);
 255     }
 256     catch (Exception $e) {
 257       $exception_thrown = TRUE;
 258     }
 259     $this->assertTrue($exception_thrown);
 260   }
 261
 262   /**
 263    * requireSafeDBName() method (to check valid database name)
 264    */
 265   public function testRequireSafeDBName() {
 266     $databases = [
 267       'testdb' => TRUE,
 268       'test_db' => TRUE,
 269       'TEST_db' => TRUE,
 270       '123testdb' => TRUE,
 271       'test12db34' => TRUE,
 272       'test_12_db34' => TRUE,
 273       'test-db' => TRUE,
 274       'test;db' => FALSE,
 275       'test*&db' => FALSE,
 276       'testdb;Delete test' => FALSE,
 277       '123456' => FALSE,
 278       'test#$%^&*' => FALSE,
 279     ];
 280     $testDetails = [];
 281     foreach ($databases as $database => $val) {
 282       $this->assertEquals(CRM_Core_DAO::requireSafeDBName($database), $val);
 283     }
 284   }
 285
 286   /**
 287    * Test the function designed to find myIsam tables.
 288    */
 289   public function testMyISAMCheck() {
 290     // Cleanup previous, failed tests.
 291     CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS civicrm_my_isam');
 292
 293     // A manually created MyISAM table should raise a redflag.
 294     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
 295     CRM_Core_DAO::executeQuery('CREATE TABLE civicrm_my_isam (`id` int(10) unsigned NOT NULL) ENGINE = MyISAM');
 296     $this->assertEquals(1, CRM_Core_DAO::isDBMyISAM());
 297     CRM_Core_DAO::executeQuery('DROP TABLE civicrm_my_isam');
 298
 299     // A temp table should not raise flag (static naming).
 300     $tempName = CRM_Core_DAO::createTempTableName('civicrm', FALSE);
 301     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
 302     CRM_Core_DAO::executeQuery("CREATE TABLE $tempName (`id` int(10) unsigned NOT NULL) ENGINE = MyISAM");
 303     // Ignore temp tables
 304     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
 305     CRM_Core_DAO::executeQuery("DROP TABLE $tempName");
 306
 307     // A temp table should not raise flag (randomized naming).
 308     $tempName = CRM_Core_DAO::createTempTableName('civicrm', TRUE);
 309     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
 310     CRM_Core_DAO::executeQuery("CREATE TABLE $tempName (`id` int(10) unsigned NOT NULL) ENGINE = MyISAM");
 311     // Ignore temp tables
 312     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
 313     CRM_Core_DAO::executeQuery("DROP TABLE $tempName");
 314   }
 315
 316   /**
 317    * CRM-19930: Test toArray() function with $format param
 318    */
 319   public function testDAOtoArray() {
 320     $format = 'user[%s]';
 321     $params = [
 322       'first_name' => 'Testy',
 323       'last_name' => 'McScallion',
 324       'contact_type' => 'Individual',
 325     ];
 326
 327     $dao = CRM_Contact_BAO_Contact::add($params);
 328     $query = "SELECT contact_type, display_name FROM civicrm_contact WHERE id={$dao->id}";
 329     $toArray = [
 330       'contact_type' => 'Individual',
 331       'display_name' => 'Testy McScallion',
 332     ];
 333     $modifiedKeyArray = [];
 334     foreach ($toArray as $k => $v) {
 335       $modifiedKeyArray[sprintf($format, $k)] = $v;
 336     }
 337
 338     $dao = CRM_Core_DAO::executeQuery($query);
 339     while ($dao->fetch()) {
 340       $daoToArray = $dao->toArray();
 341       $this->checkArrayEquals($toArray, $daoToArray);
 342       $daoToArray = $dao->toArray($format);
 343       $this->checkArrayEquals($modifiedKeyArray, $daoToArray);
 344     }
 345   }
 346
 347   /**
 348    * CRM-17748: Test internal DAO options
 349    */
 350   public function testDBOptions() {
 351     $contactIDs = [];
 352     for ($i = 0; $i < 10; $i++) {
 353       $contactIDs[] = $this->individualCreate([
 354         'first_name' => 'Alan' . substr(sha1(rand()), 0, 7),
 355         'last_name' => 'Smith' . substr(sha1(rand()), 0, 4),
 356       ]);
 357     }
 358
 359     // Test option 'result_buffering'
 360     $this->_testMemoryUsageForUnbufferedQuery();
 361
 362     // cleanup
 363     foreach ($contactIDs as $contactID) {
 364       $this->callAPISuccess('Contact', 'delete', ['id' => $contactID]);
 365     }
 366   }
 367
 368   /**
 369    * Helper function to test result of buffered and unbuffered query
 370    */
 371   public function _testMemoryUsageForUnbufferedQuery() {
 372     $sql = "SELECT * FROM civicrm_contact WHERE first_name LIKE 'Alan%' AND last_name LIKE 'Smith%' ";
 373
 374     $dao = CRM_Core_DAO::executeQuery($sql);
 375     $contactsFetchedFromBufferedQuery = $dao->fetchAll();
 376
 377     $dao = CRM_Core_DAO::executeUnbufferedQuery($sql);
 378     $contactsFetchedFromUnbufferedQuery = $dao->fetchAll();
 379
 380     $this->checkArrayEquals($contactsFetchedFromBufferedQuery, $contactsFetchedFromUnbufferedQuery);
 381   }
 382
 383   /**
 384    * Test that known sql modes are present in session.
 385    */
 386   public function testSqlModePresent() {
 387     $sqlModes = CRM_Utils_SQL::getSqlModes();
 388     // assert we have strict trans
 389     $this->assertContains('STRICT_TRANS_TABLES', $sqlModes);
 390     if (CRM_Utils_SQL::supportsFullGroupBy()) {
 391       $this->assertContains('ONLY_FULL_GROUP_BY', $sqlModes);
 392     }
 393   }
 394
 395   /**
 396    * @return array
 397    */
 398   public function serializationMethods() {
 399     $constants = [];
 400     $simpleData = [
 401       NULL,
 402       ['Foo', 'Bar', '3', '4', '5'],
 403       [],
 404       ['0'],
 405     ];
 406     $complexData = [
 407       [
 408         'foo' => 'bar',
 409         'baz' => ['1', '2', '3', ['one', 'two']],
 410         '3' => '0',
 411       ],
 412     ];
 413     $daoInfo = new ReflectionClass('CRM_Core_DAO');
 414     foreach ($daoInfo->getConstants() as $constant => $val) {
 415       if ($constant == 'SERIALIZE_JSON' || $constant == 'SERIALIZE_PHP') {
 416         $constants[] = [$val, array_merge($simpleData, $complexData)];
 417       }
 418       elseif (strpos($constant, 'SERIALIZE_') === 0) {
 419         $constants[] = [$val, $simpleData];
 420       }
 421     }
 422     return $constants;
 423   }
 424
 425   public function testFetchGeneratorDao() {
 426     $this->individualCreate([], 0);
 427     $this->individualCreate([], 1);
 428     $this->individualCreate([], 2);
 429     $count = 0;
 430     $g = CRM_Core_DAO::executeQuery('SELECT contact_type FROM civicrm_contact WHERE contact_type = "Individual" LIMIT 3')
 431       ->fetchGenerator();
 432     foreach ($g as $row) {
 433       $this->assertEquals('Individual', $row->contact_type);
 434       $count++;
 435     }
 436     $this->assertEquals(3, $count);
 437   }
 438
 439   public function testFetchGeneratorArray() {
 440     $this->individualCreate([], 0);
 441     $this->individualCreate([], 1);
 442     $this->individualCreate([], 2);
 443     $count = 0;
 444     $g = CRM_Core_DAO::executeQuery('SELECT contact_type FROM civicrm_contact WHERE contact_type = "Individual" LIMIT 3')
 445       ->fetchGenerator('array');
 446     foreach ($g as $row) {
 447       $this->assertEquals('Individual', $row['contact_type']);
 448       $count++;
 449     }
 450     $this->assertEquals(3, $count);
 451   }
 452
 453   /**
 454    * @dataProvider serializationMethods
 455    */
 456   public function testFieldSerialization($method, $sampleData) {
 457     foreach ($sampleData as $value) {
 458       $serialized = CRM_Core_DAO::serializeField($value, $method);
 459       $newValue = CRM_Core_DAO::unSerializeField($serialized, $method);
 460       $this->assertEquals($value, $newValue);
 461     }
 462   }
 463
 464   /**
 465    * Test the DAO cloning method does not hit issues with freeing the result.
 466    */
 467   public function testCloneDAO() {
 468     $dao = CRM_Core_DAO::executeQuery('SELECT * FROM civicrm_domain');
 469     $i = 0;
 470     while ($dao->fetch()) {
 471       $i++;
 472       $cloned = clone($dao);
 473       unset($cloned);
 474     }
 475     $this->assertEquals(2, $i);
 476   }
 477
 478   /**
 479    * Test modifying a query in a hook.
 480    *
 481    * Test that adding a sensible string does not cause failure.
 482    *
 483    * @throws \Exception
 484    */
 485   public function testModifyQuery() {
 486     /**
 487      * @param \Civi\Core\Event\QueryEvent $e
 488      */
 489     $listener = function($e) {
 490       $e->query = '/* User :  hooked */' . $e->query;
 491     };
 492     Civi::dispatcher()->addListener('civi.db.query', $listener);
 493     CRM_Core_DAO::executeQuery('SELECT * FROM civicrm_domain');
 494
 495     Civi::dispatcher()->removeListener('civi.db.query', $listener);
 496   }
 497
 498   /**
 499    * Test modifying a query in a hook.
 500    *
 501    * Demonstrate it is modified showing the query now breaks.
 502    */
 503   public function testModifyAndBreakQuery() {
 504     /**
 505      * @param \Civi\Core\Event\QueryEvent $e
 506      */
 507     $listener = function($e) {
 508       $e->query = '/* Forgot trailing comment marker' . $e->query;
 509     };
 510     Civi::dispatcher()->addListener('civi.db.query', $listener);
 511     try {
 512       CRM_Core_DAO::executeQuery('SELECT * FROM civicrm_domain');
 513     }
 514     catch (PEAR_Exception $e) {
 515       $this->assertEquals(
 516         "SELECT * FROM civicrm_domain [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/* Forgot trailing comment markerSELECT * FROM civicrm_domain' at line 1]",
 517         $e->getCause()->getUserInfo()
 518       );
 519       Civi::dispatcher()->removeListener('civi.db.query', $listener);
 520       return;
 521     }
 522     Civi::dispatcher()->removeListener('civi.db.query', $listener);
 523     $this->fail('String not altered');
 524   }
 525
 526 }