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   public function testGetReferenceColumns() {
  10     // choose CRM_Core_DAO_Email as an arbitrary example
  11     $emailRefs = CRM_Core_DAO_Email::getReferenceColumns();
  12     $refsByTarget = array();
  13     foreach ($emailRefs as $refSpec) {
  14       $refsByTarget[$refSpec->getTargetTable()] = $refSpec;
  15     }
  16     $this->assertTrue(array_key_exists('civicrm_contact', $refsByTarget));
  17     $contactRef = $refsByTarget['civicrm_contact'];
  18     $this->assertEquals('contact_id', $contactRef->getReferenceKey());
  19     $this->assertEquals('id', $contactRef->getTargetKey());
  20     $this->assertEquals('CRM_Core_Reference_Basic', get_class($contactRef));
  21   }
  22
  23   public function testGetReferencesToTable() {
  24     $refs = CRM_Core_DAO::getReferencesToTable(CRM_Financial_DAO_FinancialType::getTableName());
  25     $refsBySource = array();
  26     foreach ($refs as $refSpec) {
  27       $refsBySource[$refSpec->getReferenceTable()] = $refSpec;
  28     }
  29     $this->assertTrue(array_key_exists('civicrm_entity_financial_account', $refsBySource));
  30     $genericRef = $refsBySource['civicrm_entity_financial_account'];
  31     $this->assertEquals('entity_id', $genericRef->getReferenceKey());
  32     $this->assertEquals('entity_table', $genericRef->getTypeColumn());
  33     $this->assertEquals('id', $genericRef->getTargetKey());
  34     $this->assertEquals('CRM_Core_Reference_Dynamic', get_class($genericRef));
  35   }
  36
  37   public function testFindReferences() {
  38     $params = array(
  39       'first_name' => 'Testy',
  40       'last_name' => 'McScallion',
  41       'contact_type' => 'Individual',
  42     );
  43
  44     $contact = CRM_Contact_BAO_Contact::add($params);
  45     $this->assertNotNull($contact->id);
  46
  47     $params = array(
  48       'email' => 'spam@dev.null',
  49       'contact_id' => $contact->id,
  50       'is_primary' => 0,
  51       'location_type_id' => 1,
  52     );
  53
  54     $email = CRM_Core_BAO_Email::add($params);
  55
  56     $refs = $contact->findReferences();
  57     $refsByTable = array();
  58     foreach ($refs as $refObj) {
  59       $refsByTable[$refObj->__table] = $refObj;
  60     }
  61
  62     $this->assertTrue(array_key_exists('civicrm_email', $refsByTable));
  63     $refDao = $refsByTable['civicrm_email'];
  64     $refDao->find(TRUE);
  65     $this->assertEquals($contact->id, $refDao->contact_id);
  66   }
  67
  68   /**
  69    * @return array
  70    */
  71   public function composeQueryExamples() {
  72     $cases = array();
  73     // $cases[] = array('Input-SQL', 'Input-Params', 'Expected-SQL');
  74
  75     // CASE: No params
  76     $cases[] = array(
  77       'SELECT * FROM whatever',
  78       array(),
  79       'SELECT * FROM whatever',
  80     );
  81
  82     // CASE: Integer param
  83     $cases[] = array(
  84       'SELECT * FROM whatever WHERE id = %1',
  85       array(
  86         1 => array(10, 'Integer'),
  87       ),
  88       'SELECT * FROM whatever WHERE id = 10',
  89     );
  90
  91     // CASE: String param
  92     $cases[] = array(
  93       'SELECT * FROM whatever WHERE name = %1',
  94       array(
  95         1 => array('Alice', 'String'),
  96       ),
  97       'SELECT * FROM whatever WHERE name = \'Alice\'',
  98     );
  99
 100     // CASE: Two params
 101     $cases[] = array(
 102       'SELECT * FROM whatever WHERE name = %1 AND title = %2',
 103       array(
 104         1 => array('Alice', 'String'),
 105         2 => array('Bob', 'String'),
 106       ),
 107       'SELECT * FROM whatever WHERE name = \'Alice\' AND title = \'Bob\'',
 108     );
 109
 110     // CASE: Two params with special character (%1)
 111     $cases[] = array(
 112       'SELECT * FROM whatever WHERE name = %1 AND title = %2',
 113       array(
 114         1 => array('Alice %2', 'String'),
 115         2 => array('Bob', 'String'),
 116       ),
 117       'SELECT * FROM whatever WHERE name = \'Alice %2\' AND title = \'Bob\'',
 118     );
 119
 120     // CASE: Two params with special character ($1)
 121     $cases[] = array(
 122       'SELECT * FROM whatever WHERE name = %1 AND title = %2',
 123       array(
 124         1 => array('Alice $1', 'String'),
 125         2 => array('Bob', 'String'),
 126       ),
 127       'SELECT * FROM whatever WHERE name = \'Alice $1\' AND title = \'Bob\'',
 128     );
 129
 130     return $cases;
 131   }
 132
 133   /**
 134    * @dataProvider composeQueryExamples
 135    * @param $inputSql
 136    * @param $inputParams
 137    * @param $expectSql
 138    */
 139   public function testComposeQuery($inputSql, $inputParams, $expectSql) {
 140     $actualSql = CRM_Core_DAO::composeQuery($inputSql, $inputParams);
 141     $this->assertEquals($expectSql, $actualSql);
 142   }
 143
 144   /**
 145    * CASE: Two params where the %2 is already present in the query
 146    * NOTE: This case should rightly FAIL, as using strstr in the replace mechanism will turn
 147    * the query into: SELECT * FROM whatever WHERE name = 'Alice' AND title = 'Bob' AND year LIKE ''Bob'012'
 148    * So, to avoid such ERROR, the query should be framed like:
 149    * 'SELECT * FROM whatever WHERE name = %1 AND title = %3 AND year LIKE '%2012'
 150    * $params[3] = array('Bob', 'String');
 151    * i.e. the place holder should be unique and should not contain in any other operational use in query
 152    */
 153   public function testComposeQueryFailure() {
 154     $cases[] = array(
 155       'SELECT * FROM whatever WHERE name = %1 AND title = %2 AND year LIKE \'%2012\' ',
 156       array(
 157         1 => array('Alice', 'String'),
 158         2 => array('Bob', 'String'),
 159       ),
 160       'SELECT * FROM whatever WHERE name = \'Alice\' AND title = \'Bob\' AND year LIKE \'%2012\' ',
 161     );
 162     list($inputSql, $inputParams, $expectSql) = $cases[0];
 163     $actualSql = CRM_Core_DAO::composeQuery($inputSql, $inputParams);
 164     $this->assertFalse(($expectSql == $actualSql));
 165   }
 166
 167   /**
 168    * @return array
 169    */
 170   public function sqlNameDataProvider() {
 171     return array(
 172       array('this is a long string', 30, FALSE, 'this is a long string'),
 173       array(
 174         'this is an even longer string which is exactly 60 character',
 175         60,
 176         FALSE,
 177         'this is an even longer string which is exactly 60 character',
 178       ),
 179       array(
 180         'this is an even longer string which is exactly 60 character',
 181         60,
 182         TRUE,
 183         'this is an even longer string which is exactly 60 character',
 184       ),
 185       array(
 186         'this is an even longer string which is a bit more than 60 character',
 187         60,
 188         FALSE,
 189         'this is an even longer string which is a bit more than 60 ch',
 190       ),
 191       array(
 192         'this is an even longer string which is a bit more than 60 character',
 193         60,
 194         TRUE,
 195         'this is an even longer string which is a bit more th_c1cbd519',
 196       ),
 197     );
 198   }
 199
 200   /**
 201    * @dataProvider sqlNameDataProvider
 202    * @param $inputData
 203    * @param $length
 204    * @param $makeRandom
 205    * @param $expectedResult
 206    */
 207   public function testShortenSQLName($inputData, $length, $makeRandom, $expectedResult) {
 208     $this->assertEquals($expectedResult, CRM_Core_DAO::shortenSQLName($inputData, $length, $makeRandom));
 209   }
 210
 211   public function testFindById() {
 212     $params = $this->sampleContact('Individual', 4);
 213     $existing_contact = CRM_Contact_BAO_Contact::add($params);
 214     $contact = CRM_Contact_BAO_Contact::findById($existing_contact->id);
 215     $this->assertEquals($existing_contact->id, $contact->id);
 216     $deleted_contact_id = $existing_contact->id;
 217     CRM_Contact_BAO_Contact::deleteContact($contact->id, FALSE, TRUE);
 218     $exception_thrown = FALSE;
 219     try {
 220       $deleted_contact = CRM_Contact_BAO_Contact::findById($deleted_contact_id);
 221     }
 222     catch (Exception $e) {
 223       $exception_thrown = TRUE;
 224     }
 225     $this->assertTrue($exception_thrown);
 226   }
 227
 228   /**
 229    * requireSafeDBName() method (to check valid database name)
 230    */
 231   public function testRequireSafeDBName() {
 232     $databases = array(
 233       'testdb' => TRUE,
 234       'test_db' => TRUE,
 235       'TEST_db' => TRUE,
 236       '123testdb' => TRUE,
 237       'test12db34' => TRUE,
 238       'test_12_db34' => TRUE,
 239       'test-db' => TRUE,
 240       'test;db' => FALSE,
 241       'test*&db' => FALSE,
 242       'testdb;Delete test' => FALSE,
 243       '123456' => FALSE,
 244       'test#$%^&*' => FALSE,
 245     );
 246     $testDetails = array();
 247     foreach ($databases as $database => $val) {
 248       $this->assertEquals(CRM_Core_DAO::requireSafeDBName($database), $val);
 249     }
 250   }
 251
 252   /**
 253    * Test the function designed to find myIsam tables.
 254    */
 255   public function testMyISAMCheck() {
 256     // Cleanup previous, failed tests.
 257     CRM_Core_DAO::executeQuery('DROP TABLE IF EXISTS civicrm_my_isam');
 258
 259     // A manually created MyISAM table should raise a redflag.
 260     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
 261     CRM_Core_DAO::executeQuery('CREATE TABLE civicrm_my_isam (`id` int(10) unsigned NOT NULL) ENGINE = MyISAM');
 262     $this->assertEquals(1, CRM_Core_DAO::isDBMyISAM());
 263     CRM_Core_DAO::executeQuery('DROP TABLE civicrm_my_isam');
 264
 265     // A temp table should not raise flag (static naming).
 266     $tempName = CRM_Core_DAO::createTempTableName('civicrm', FALSE);
 267     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
 268     CRM_Core_DAO::executeQuery("CREATE TABLE $tempName (`id` int(10) unsigned NOT NULL) ENGINE = MyISAM");
 269     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM()); // Ignore temp tables
 270     CRM_Core_DAO::executeQuery("DROP TABLE $tempName");
 271
 272     // A temp table should not raise flag (randomized naming).
 273     $tempName = CRM_Core_DAO::createTempTableName('civicrm', TRUE);
 274     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM());
 275     CRM_Core_DAO::executeQuery("CREATE TABLE $tempName (`id` int(10) unsigned NOT NULL) ENGINE = MyISAM");
 276     $this->assertEquals(0, CRM_Core_DAO::isDBMyISAM()); // Ignore temp tables
 277     CRM_Core_DAO::executeQuery("DROP TABLE $tempName");
 278   }
 279
 280   /**
 281    * CRM-19930: Test toArray() function with $format param
 282    */
 283   public function testDAOtoArray() {
 284     $format = 'user[%s]';
 285     $params = array(
 286       'first_name' => 'Testy',
 287       'last_name' => 'McScallion',
 288       'contact_type' => 'Individual',
 289     );
 290
 291     $dao = CRM_Contact_BAO_Contact::add($params);
 292     $query = "SELECT contact_type, display_name FROM civicrm_contact WHERE id={$dao->id}";
 293     $toArray = array(
 294       'contact_type' => 'Individual',
 295       'display_name' => 'Testy McScallion',
 296     );
 297     $modifiedKeyArray = array();
 298     foreach ($toArray as $k => $v) {
 299       $modifiedKeyArray[sprintf($format, $k)] = $v;
 300     }
 301
 302     $dao = CRM_Core_DAO::executeQuery($query);
 303     while ($dao->fetch()) {
 304       $daoToArray = $dao->toArray();
 305       $this->checkArrayEquals($toArray, $daoToArray);
 306       $daoToArray = $dao->toArray($format);
 307       $this->checkArrayEquals($modifiedKeyArray, $daoToArray);
 308     }
 309   }
 310
 311 }