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.
 300     $tempTableName = CRM_Utils_SQL_TempTable::build()->setCategory('myisam')->getName();
 301     // A temp table should not raise flag (randomized naming).
 302     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
 303     CRM_Core_DAO::executeQuery("CREATE TABLE $tempTableName (`id` int(10) unsigned NOT NULL) ENGINE = MyISAM");
 304     // Ignore temp tables
 305     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
 306     CRM_Core_DAO::executeQuery("DROP TABLE $tempTableName");
 307   }
 308
 309   /**
 310    * CRM-19930: Test toArray() function with $format param
 311    */
 312   public function testDAOtoArray() {
 313     $format = 'user[%s]';
 314     $params = [
 315       'first_name' => 'Testy',
 316       'last_name' => 'McScallion',
 317       'contact_type' => 'Individual',
 318     ];
 319
 320     $dao = CRM_Contact_BAO_Contact::add($params);
 321     $query = "SELECT contact_type, display_name FROM civicrm_contact WHERE id={$dao->id}";
 322     $toArray = [
 323       'contact_type' => 'Individual',
 324       'display_name' => 'Testy McScallion',
 325     ];
 326     $modifiedKeyArray = [];
 327     foreach ($toArray as $k => $v) {
 328       $modifiedKeyArray[sprintf($format, $k)] = $v;
 329     }
 330
 331     $dao = CRM_Core_DAO::executeQuery($query);
 332     while ($dao->fetch()) {
 333       $daoToArray = $dao->toArray();
 334       $this->checkArrayEquals($toArray, $daoToArray);
 335       $daoToArray = $dao->toArray($format);
 336       $this->checkArrayEquals($modifiedKeyArray, $daoToArray);
 337     }
 338   }
 339
 340   /**
 341    * CRM-17748: Test internal DAO options
 342    */
 343   public function testDBOptions() {
 344     $contactIDs = [];
 345     for ($i = 0; $i < 10; $i++) {
 346       $contactIDs[] = $this->individualCreate([
 347         'first_name' => 'Alan' . substr(sha1(rand()), 0, 7),
 348         'last_name' => 'Smith' . substr(sha1(rand()), 0, 4),
 349       ]);
 350     }
 351
 352     // Test option 'result_buffering'
 353     $this->_testMemoryUsageForUnbufferedQuery();
 354
 355     // cleanup
 356     foreach ($contactIDs as $contactID) {
 357       $this->callAPISuccess('Contact', 'delete', ['id' => $contactID]);
 358     }
 359   }
 360
 361   /**
 362    * Helper function to test result of buffered and unbuffered query
 363    */
 364   public function _testMemoryUsageForUnbufferedQuery() {
 365     $sql = "SELECT * FROM civicrm_contact WHERE first_name LIKE 'Alan%' AND last_name LIKE 'Smith%' ";
 366
 367     $dao = CRM_Core_DAO::executeQuery($sql);
 368     $contactsFetchedFromBufferedQuery = $dao->fetchAll();
 369
 370     $dao = CRM_Core_DAO::executeUnbufferedQuery($sql);
 371     $contactsFetchedFromUnbufferedQuery = $dao->fetchAll();
 372
 373     $this->checkArrayEquals($contactsFetchedFromBufferedQuery, $contactsFetchedFromUnbufferedQuery);
 374   }
 375
 376   /**
 377    * Test that known sql modes are present in session.
 378    */
 379   public function testSqlModePresent() {
 380     $sqlModes = CRM_Utils_SQL::getSqlModes();
 381     // assert we have strict trans
 382     $this->assertContains('STRICT_TRANS_TABLES', $sqlModes);
 383     if (CRM_Utils_SQL::supportsFullGroupBy()) {
 384       $this->assertContains('ONLY_FULL_GROUP_BY', $sqlModes);
 385     }
 386   }
 387
 388   /**
 389    * @return array
 390    */
 391   public function serializationMethods() {
 392     $constants = [];
 393     $simpleData = [
 394       NULL,
 395       ['Foo', 'Bar', '3', '4', '5'],
 396       [],
 397       ['0'],
 398     ];
 399     $complexData = [
 400       [
 401         'foo' => 'bar',
 402         'baz' => ['1', '2', '3', ['one', 'two']],
 403         '3' => '0',
 404       ],
 405     ];
 406     $daoInfo = new ReflectionClass('CRM_Core_DAO');
 407     foreach ($daoInfo->getConstants() as $constant => $val) {
 408       if ($constant == 'SERIALIZE_JSON' || $constant == 'SERIALIZE_PHP') {
 409         $constants[] = [$val, array_merge($simpleData, $complexData)];
 410       }
 411       elseif (strpos($constant, 'SERIALIZE_') === 0) {
 412         $constants[] = [$val, $simpleData];
 413       }
 414     }
 415     return $constants;
 416   }
 417
 418   public function testFetchGeneratorDao() {
 419     $this->individualCreate([], 0);
 420     $this->individualCreate([], 1);
 421     $this->individualCreate([], 2);
 422     $count = 0;
 423     $g = CRM_Core_DAO::executeQuery('SELECT contact_type FROM civicrm_contact WHERE contact_type = "Individual" LIMIT 3')
 424       ->fetchGenerator();
 425     foreach ($g as $row) {
 426       $this->assertEquals('Individual', $row->contact_type);
 427       $count++;
 428     }
 429     $this->assertEquals(3, $count);
 430   }
 431
 432   public function testFetchGeneratorArray() {
 433     $this->individualCreate([], 0);
 434     $this->individualCreate([], 1);
 435     $this->individualCreate([], 2);
 436     $count = 0;
 437     $g = CRM_Core_DAO::executeQuery('SELECT contact_type FROM civicrm_contact WHERE contact_type = "Individual" LIMIT 3')
 438       ->fetchGenerator('array');
 439     foreach ($g as $row) {
 440       $this->assertEquals('Individual', $row['contact_type']);
 441       $count++;
 442     }
 443     $this->assertEquals(3, $count);
 444   }
 445
 446   /**
 447    * @dataProvider serializationMethods
 448    */
 449   public function testFieldSerialization($method, $sampleData) {
 450     foreach ($sampleData as $value) {
 451       $serialized = CRM_Core_DAO::serializeField($value, $method);
 452       $newValue = CRM_Core_DAO::unSerializeField($serialized, $method);
 453       $this->assertEquals($value, $newValue);
 454     }
 455   }
 456
 457   /**
 458    * Test the DAO cloning method does not hit issues with freeing the result.
 459    */
 460   public function testCloneDAO() {
 461     $dao = CRM_Core_DAO::executeQuery('SELECT * FROM civicrm_domain');
 462     $i = 0;
 463     while ($dao->fetch()) {
 464       $i++;
 465       $cloned = clone($dao);
 466       unset($cloned);
 467     }
 468     $this->assertEquals(2, $i);
 469   }
 470
 471   /**
 472    * Test modifying a query in a hook.
 473    *
 474    * Test that adding a sensible string does not cause failure.
 475    *
 476    * @throws \Exception
 477    */
 478   public function testModifyQuery() {
 479     /**
 480      * @param \Civi\Core\Event\QueryEvent $e
 481      */
 482     $listener = function($e) {
 483       $e->query = '/* User :  hooked */' . $e->query;
 484     };
 485     Civi::dispatcher()->addListener('civi.db.query', $listener);
 486     CRM_Core_DAO::executeQuery('SELECT * FROM civicrm_domain');
 487
 488     Civi::dispatcher()->removeListener('civi.db.query', $listener);
 489   }
 490
 491   /**
 492    * Test modifying a query in a hook.
 493    *
 494    * Demonstrate it is modified showing the query now breaks.
 495    */
 496   public function testModifyAndBreakQuery() {
 497     /**
 498      * @param \Civi\Core\Event\QueryEvent $e
 499      */
 500     $listener = function($e) {
 501       $e->query = '/* Forgot trailing comment marker' . $e->query;
 502     };
 503     Civi::dispatcher()->addListener('civi.db.query', $listener);
 504     try {
 505       CRM_Core_DAO::executeQuery('SELECT * FROM civicrm_domain');
 506     }
 507     catch (PEAR_Exception $e) {
 508       $this->assertEquals(
 509         "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]",
 510         $e->getCause()->getUserInfo()
 511       );
 512       Civi::dispatcher()->removeListener('civi.db.query', $listener);
 513       return;
 514     }
 515     Civi::dispatcher()->removeListener('civi.db.query', $listener);
 516     $this->fail('String not altered');
 517   }
 518
 519 }