4 * Class CRM_Core_DAOTest
7 class CRM_Core_DAOTest
extends CiviUnitTestCase
{
9 const ABORTED_SQL
= "_aborted_sql_";
11 public function testGetReferenceColumns() {
12 // choose CRM_Core_DAO_Email as an arbitrary example
13 $emailRefs = CRM_Core_DAO_Email
::getReferenceColumns();
15 foreach ($emailRefs as $refSpec) {
16 $refsByTarget[$refSpec->getTargetTable()] = $refSpec;
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));
25 public function testGetReferencesToTable() {
26 $refs = CRM_Core_DAO
::getReferencesToTable(CRM_Financial_DAO_FinancialType
::getTableName());
28 foreach ($refs as $refSpec) {
29 $refsBySource[$refSpec->getReferenceTable()] = $refSpec;
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));
39 public function testFindReferences() {
41 'first_name' => 'Testy',
42 'last_name' => 'McScallion',
43 'contact_type' => 'Individual',
46 $contact = CRM_Contact_BAO_Contact
::add($params);
47 $this->assertNotNull($contact->id
);
50 'email' => 'spam@dev.null',
51 'contact_id' => $contact->id
,
53 'location_type_id' => 1,
56 $email = CRM_Core_BAO_Email
::add($params);
58 $refs = $contact->findReferences();
60 foreach ($refs as $refObj) {
61 $refsByTable[$refObj->__table
] = $refObj;
64 $this->assertTrue(array_key_exists('civicrm_email', $refsByTable));
65 $refDao = $refsByTable['civicrm_email'];
67 $this->assertEquals($contact->id
, $refDao->contact_id
);
73 public function composeQueryExamples() {
75 // $cases[] = array('Input-SQL', 'Input-Params', 'Expected-SQL');
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'];
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
];
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
];
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
];
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
];
104 'SELECT * FROM whatever',
106 'SELECT * FROM whatever',
109 // CASE: Integer param
111 'SELECT * FROM whatever WHERE id = %1',
113 1 => [10, 'Integer'],
115 'SELECT * FROM whatever WHERE id = 10',
118 // CASE: String param
120 'SELECT * FROM whatever WHERE name = %1',
122 1 => ['Alice', 'String'],
124 'SELECT * FROM whatever WHERE name = \'Alice\'',
129 'SELECT * FROM whatever WHERE name = %1 AND title = %2',
131 1 => ['Alice', 'String'],
132 2 => ['Bob', 'String'],
134 'SELECT * FROM whatever WHERE name = \'Alice\' AND title = \'Bob\'',
137 // CASE: Two params with special character (%1)
139 'SELECT * FROM whatever WHERE name = %1 AND title = %2',
141 1 => ['Alice %2', 'String'],
142 2 => ['Bob', 'String'],
144 'SELECT * FROM whatever WHERE name = \'Alice %2\' AND title = \'Bob\'',
147 // CASE: Two params with special character ($1)
149 'SELECT * FROM whatever WHERE name = %1 AND title = %2',
151 1 => ['Alice $1', 'String'],
152 2 => ['Bob', 'String'],
154 'SELECT * FROM whatever WHERE name = \'Alice $1\' AND title = \'Bob\'',
161 * @dataProvider composeQueryExamples
163 * @param $inputParams
166 public function testComposeQuery($inputSql, $inputParams, $expectSql) {
167 $scope = CRM_Core_TemporaryErrorScope
::useException();
169 $actualSql = CRM_Core_DAO
::composeQuery($inputSql, $inputParams);
171 catch (Exception
$e) {
172 $actualSql = self
::ABORTED_SQL
;
174 $this->assertEquals($expectSql, $actualSql);
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
186 public function testComposeQueryFailure() {
188 'SELECT * FROM whatever WHERE name = %1 AND title = %2 AND year LIKE \'%2012\' ',
190 1 => ['Alice', 'String'],
191 2 => ['Bob', 'String'],
193 'SELECT * FROM whatever WHERE name = \'Alice\' AND title = \'Bob\' AND year LIKE \'%2012\' ',
195 list($inputSql, $inputParams, $expectSql) = $cases[0];
196 $actualSql = CRM_Core_DAO
::composeQuery($inputSql, $inputParams);
197 $this->assertFalse(($expectSql == $actualSql));
204 public function sqlNameDataProvider() {
206 ['this is a long string', 30, FALSE, 'this is a long string'],
208 'this is an even longer string which is exactly 60 character',
211 'this is an even longer string which is exactly 60 character',
214 'this is an even longer string which is exactly 60 character',
217 'this is an even longer string which is exactly 60 character',
220 'this is an even longer string which is a bit more than 60 character',
223 'this is an even longer string which is a bit more than 60 ch',
226 'this is an even longer string which is a bit more than 60 character',
229 'this is an even longer string which is a bit more th_c1cbd519',
235 * @dataProvider sqlNameDataProvider
239 * @param $expectedResult
241 public function testShortenSQLName($inputData, $length, $makeRandom, $expectedResult) {
242 $this->assertEquals($expectedResult, CRM_Core_DAO
::shortenSQLName($inputData, $length, $makeRandom));
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 $this->contactDelete($contact->id
);
252 $exception_thrown = FALSE;
254 $deleted_contact = CRM_Contact_BAO_Contact
::findById($deleted_contact_id);
256 catch (Exception
$e) {
257 $exception_thrown = TRUE;
259 $this->assertTrue($exception_thrown);
263 * requireSafeDBName() method (to check valid database name)
265 public function testRequireSafeDBName() {
271 'test12db34' => TRUE,
272 'test_12_db34' => TRUE,
276 'testdb;Delete test' => FALSE,
278 'test#$%^&*' => FALSE,
281 foreach ($databases as $database => $val) {
282 $this->assertEquals(CRM_Core_DAO
::requireSafeDBName($database), $val);
287 * Test the function designed to find myIsam tables.
289 public function testMyISAMCheck() {
290 // Cleanup previous, failed tests.
291 CRM_Core_DAO
::executeQuery('DROP TABLE IF EXISTS civicrm_my_isam');
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');
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");
310 * CRM-19930: Test toArray() function with $format param
312 public function testDAOtoArray() {
313 $format = 'user[%s]';
315 'first_name' => 'Testy',
316 'last_name' => 'McScallion',
317 'contact_type' => 'Individual',
320 $dao = CRM_Contact_BAO_Contact
::add($params);
321 $query = "SELECT contact_type, display_name FROM civicrm_contact WHERE id={$dao->id}";
323 'contact_type' => 'Individual',
324 'display_name' => 'Testy McScallion',
326 $modifiedKeyArray = [];
327 foreach ($toArray as $k => $v) {
328 $modifiedKeyArray[sprintf($format, $k)] = $v;
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);
341 * CRM-17748: Test internal DAO options
343 public function testDBOptions() {
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),
352 // Test option 'result_buffering'
353 $this->_testMemoryUsageForUnbufferedQuery();
356 foreach ($contactIDs as $contactID) {
357 $this->callAPISuccess('Contact', 'delete', ['id' => $contactID]);
362 * Helper function to test result of buffered and unbuffered query
364 public function _testMemoryUsageForUnbufferedQuery() {
365 $sql = "SELECT * FROM civicrm_contact WHERE first_name LIKE 'Alan%' AND last_name LIKE 'Smith%' ";
367 $dao = CRM_Core_DAO
::executeQuery($sql);
368 $contactsFetchedFromBufferedQuery = $dao->fetchAll();
370 $dao = CRM_Core_DAO
::executeUnbufferedQuery($sql);
371 $contactsFetchedFromUnbufferedQuery = $dao->fetchAll();
373 $this->checkArrayEquals($contactsFetchedFromBufferedQuery, $contactsFetchedFromUnbufferedQuery);
377 * Test that known sql modes are present in session.
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);
391 public function serializationMethods() {
395 ['Foo', 'Bar', '3', '4', '5'],
402 'baz' => ['1', '2', '3', ['one', 'two']],
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)];
411 elseif (strpos($constant, 'SERIALIZE_') === 0) {
412 $constants[] = [$val, $simpleData];
418 public function testFetchGeneratorDao() {
419 $this->individualCreate([], 0);
420 $this->individualCreate([], 1);
421 $this->individualCreate([], 2);
423 $g = CRM_Core_DAO
::executeQuery('SELECT contact_type FROM civicrm_contact WHERE contact_type = "Individual" LIMIT 3')
425 foreach ($g as $row) {
426 $this->assertEquals('Individual', $row->contact_type
);
429 $this->assertEquals(3, $count);
432 public function testFetchGeneratorArray() {
433 $this->individualCreate([], 0);
434 $this->individualCreate([], 1);
435 $this->individualCreate([], 2);
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']);
443 $this->assertEquals(3, $count);
447 * @dataProvider serializationMethods
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);
458 * Test the DAO cloning method does not hit issues with freeing the result.
460 public function testCloneDAO() {
461 $dao = CRM_Core_DAO
::executeQuery('SELECT * FROM civicrm_domain');
463 while ($dao->fetch()) {
465 $cloned = clone($dao);
468 $this->assertEquals(2, $i);
472 * Test modifying a query in a hook.
474 * Test that adding a sensible string does not cause failure.
478 public function testModifyQuery() {
480 * @param \Civi\Core\Event\QueryEvent $e
482 $listener = function($e) {
483 $e->query
= '/* User : hooked */' . $e->query
;
485 Civi
::dispatcher()->addListener('civi.db.query', $listener);
486 CRM_Core_DAO
::executeQuery('SELECT * FROM civicrm_domain');
488 Civi
::dispatcher()->removeListener('civi.db.query', $listener);
492 * Test modifying a query in a hook.
494 * Demonstrate it is modified showing the query now breaks.
496 public function testModifyAndBreakQuery() {
498 * @param \Civi\Core\Event\QueryEvent $e
500 $listener = function($e) {
501 $e->query
= '/* Forgot trailing comment marker' . $e->query
;
503 Civi
::dispatcher()->addListener('civi.db.query', $listener);
505 CRM_Core_DAO
::executeQuery('SELECT * FROM civicrm_domain');
507 catch (PEAR_Exception
$e) {
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()
512 Civi
::dispatcher()->removeListener('civi.db.query', $listener);
515 Civi
::dispatcher()->removeListener('civi.db.query', $listener);
516 $this->fail('String not altered');
519 public function testSupportedFields() {
520 // Hack a different db version which will trigger getSupportedFields to filter out newer fields
521 CRM_Core_BAO_Domain
::getDomain()->version
= '5.26.0';
523 $customGroupFields = CRM_Core_DAO_CustomGroup
::getSupportedFields();
524 // 'icon' was added in 5.28
525 $this->assertArrayNotHasKey('icon', $customGroupFields);
527 // Remove domain version override:
528 CRM_Core_BAO_Domain
::version(TRUE);
530 $activityFields = CRM_Activity_DAO_Activity
::getSupportedFields();
531 // Fields should be indexed by name not unique_name (which is "activity_id")
532 $this->assertEquals('id', $activityFields['id']['name']);
534 $customGroupFields = CRM_Core_DAO_CustomGroup
::getSupportedFields();
535 $this->assertArrayHasKey('icon', $customGroupFields);
537 \CRM_Core_Config
::singleton()->userPermissionClass
->permissions
= ['access CiviCRM', 'view all contacts'];
538 $contactFields = CRM_Contact_DAO_Contact
::getSupportedFields();
539 $this->assertArrayHasKey('api_key', $contactFields);
541 $permissionedContactFields = CRM_Contact_DAO_Contact
::getSupportedFields(TRUE);
542 $this->assertArrayNotHasKey('api_key', $permissionedContactFields);