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 = array();
  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 = array();
  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 = array(
  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 = array(
  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 = array();
  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 = array();
  75     // $cases[] = array('Input-SQL', 'Input-Params', 'Expected-SQL');
  76
  77     $cases[0] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('', 'String')), 'UPDATE civicrm_foo SET bar = \'\'');
  78     $cases[1] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('the text', 'String')), 'UPDATE civicrm_foo SET bar = \'the text\'');
  79     $cases[2] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array(NULL, 'String')), self::ABORTED_SQL);
  80     $cases[3] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('null', 'String')), 'UPDATE civicrm_foo SET bar = NULL');
  81
  82     $cases[3] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('', 'Float')), self::ABORTED_SQL);
  83     $cases[4] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('1.23', 'Float')), 'UPDATE civicrm_foo SET bar = 1.23');
  84     $cases[5] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array(NULL, 'Float')), self::ABORTED_SQL);
  85     $cases[6] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('null', 'Float')), self::ABORTED_SQL);
  86
  87     $cases[11] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('', 'Money')), self::ABORTED_SQL);
  88     $cases[12] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('1.23', 'Money')), 'UPDATE civicrm_foo SET bar = 1.23');
  89     $cases[13] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array(NULL, 'Money')), self::ABORTED_SQL);
  90     $cases[14] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('null', 'Money')), self::ABORTED_SQL);
  91
  92     $cases[15] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('', 'Int')), self::ABORTED_SQL);
  93     $cases[16] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('123', 'Int')), 'UPDATE civicrm_foo SET bar = 123');
  94     $cases[17] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array(NULL, 'Int')), self::ABORTED_SQL);
  95     $cases[18] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('null', 'Int')), self::ABORTED_SQL);
  96
  97     $cases[19] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('', 'Timestamp')), 'UPDATE civicrm_foo SET bar = null');
  98     $cases[20] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('20150102030405', 'Timestamp')), 'UPDATE civicrm_foo SET bar = 20150102030405');
  99     $cases[21] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array(NULL, 'Timestamp')), 'UPDATE civicrm_foo SET bar = null');
 100     $cases[22] = array('UPDATE civicrm_foo SET bar = %1', array(1 => array('null', 'Timestamp')), self::ABORTED_SQL);
 101
 102     // CASE: No params
 103     $cases[1000] = array(
 104       'SELECT * FROM whatever',
 105       array(),
 106       'SELECT * FROM whatever',
 107     );
 108
 109     // CASE: Integer param
 110     $cases[1001] = array(
 111       'SELECT * FROM whatever WHERE id = %1',
 112       array(
 113         1 => array(10, 'Integer'),
 114       ),
 115       'SELECT * FROM whatever WHERE id = 10',
 116     );
 117
 118     // CASE: String param
 119     $cases[1002] = array(
 120       'SELECT * FROM whatever WHERE name = %1',
 121       array(
 122         1 => array('Alice', 'String'),
 123       ),
 124       'SELECT * FROM whatever WHERE name = \'Alice\'',
 125     );
 126
 127     // CASE: Two params
 128     $cases[1003] = array(
 129       'SELECT * FROM whatever WHERE name = %1 AND title = %2',
 130       array(
 131         1 => array('Alice', 'String'),
 132         2 => array('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] = array(
 139       'SELECT * FROM whatever WHERE name = %1 AND title = %2',
 140       array(
 141         1 => array('Alice %2', 'String'),
 142         2 => array('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] = array(
 149       'SELECT * FROM whatever WHERE name = %1 AND title = %2',
 150       array(
 151         1 => array('Alice $1', 'String'),
 152         2 => array('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[] = array(
 188       'SELECT * FROM whatever WHERE name = %1 AND title = %2 AND year LIKE \'%2012\' ',
 189       array(
 190         1 => array('Alice', 'String'),
 191         2 => array('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 array(
 206       array('this is a long string', 30, FALSE, 'this is a long string'),
 207       array(
 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       array(
 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       array(
 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       array(
 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 = array(
 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 = array();
 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     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM()); // Ignore temp tables
 304     CRM_Core_DAO::executeQuery("DROP TABLE $tempName");
 305
 306     // A temp table should not raise flag (randomized naming).
 307     $tempName = CRM_Core_DAO::createTempTableName('civicrm', TRUE);
 308     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
 309     CRM_Core_DAO::executeQuery("CREATE TABLE $tempName (`id` int(10) unsigned NOT NULL) ENGINE = MyISAM");
 310     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM()); // Ignore temp tables
 311     CRM_Core_DAO::executeQuery("DROP TABLE $tempName");
 312   }
 313
 314   /**
 315    * CRM-19930: Test toArray() function with $format param
 316    */
 317   public function testDAOtoArray() {
 318     $format = 'user[%s]';
 319     $params = array(
 320       'first_name' => 'Testy',
 321       'last_name' => 'McScallion',
 322       'contact_type' => 'Individual',
 323     );
 324
 325     $dao = CRM_Contact_BAO_Contact::add($params);
 326     $query = "SELECT contact_type, display_name FROM civicrm_contact WHERE id={$dao->id}";
 327     $toArray = array(
 328       'contact_type' => 'Individual',
 329       'display_name' => 'Testy McScallion',
 330     );
 331     $modifiedKeyArray = array();
 332     foreach ($toArray as $k => $v) {
 333       $modifiedKeyArray[sprintf($format, $k)] = $v;
 334     }
 335
 336     $dao = CRM_Core_DAO::executeQuery($query);
 337     while ($dao->fetch()) {
 338       $daoToArray = $dao->toArray();
 339       $this->checkArrayEquals($toArray, $daoToArray);
 340       $daoToArray = $dao->toArray($format);
 341       $this->checkArrayEquals($modifiedKeyArray, $daoToArray);
 342     }
 343   }
 344
 345   /**
 346    * CRM-17748: Test internal DAO options
 347    */
 348   public function testDBOptions() {
 349     $contactIDs = array();
 350     for ($i = 0; $i < 10; $i++) {
 351       $contactIDs[] = $this->individualCreate(array(
 352         'first_name' => 'Alan' . substr(sha1(rand()), 0, 7),
 353         'last_name' => 'Smith' . substr(sha1(rand()), 0, 4),
 354       ));
 355     }
 356
 357     // Test option 'result_buffering'
 358     $this->_testMemoryUsageForUnbufferedQuery();
 359
 360     // cleanup
 361     foreach ($contactIDs as $contactID) {
 362       $this->callAPISuccess('Contact', 'delete', array('id' => $contactID));
 363     }
 364   }
 365
 366   /**
 367    * Helper function to test result of buffered and unbuffered query
 368    */
 369   public function _testMemoryUsageForUnbufferedQuery() {
 370     $sql = "SELECT * FROM civicrm_contact WHERE first_name LIKE 'Alan%' AND last_name LIKE 'Smith%' ";
 371
 372     $dao = CRM_Core_DAO::executeQuery($sql);
 373     $contactsFetchedFromBufferedQuery = $dao->fetchAll();
 374     $dao->free();
 375
 376     $dao = CRM_Core_DAO::executeUnbufferedQuery($sql);
 377     $contactsFetchedFromUnbufferedQuery = $dao->fetchAll();
 378     $dao->free();
 379
 380     $this->checkArrayEquals($contactsFetchedFromBufferedQuery, $contactsFetchedFromUnbufferedQuery);
 381   }
 382
 383 }