tests/phpunit/api/v3/LoggingTest.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4 | CiviCRM version 4.7                                                |
   5 +--------------------------------------------------------------------+
   6 | Copyright CiviCRM LLC (c) 2004-2018                                |
   7 +--------------------------------------------------------------------+
   8 | This file is a part of CiviCRM.                                    |
   9 |                                                                    |
  10 | CiviCRM is free software; you can copy, modify, and distribute it  |
  11 | under the terms of the GNU Affero General Public License           |
  12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
  13 |                                                                    |
  14 | CiviCRM is distributed in the hope that it will be useful, but     |
  15 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
  16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
  17 | See the GNU Affero General Public License for more details.        |
  18 |                                                                    |
  19 | You should have received a copy of the GNU Affero General Public   |
  20 | License and the CiviCRM Licensing Exception along                  |
  21 | with this program; if not, contact CiviCRM LLC                     |
  22 | at info[AT]civicrm[DOT]org. If you have questions about the        |
  23 | GNU Affero General Public License or the licensing of CiviCRM,     |
  24 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
  25 +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  * Test class for Logging API.
  30  *
  31  * @package CiviCRM
  32  * @group headless
  33  */
  34 class api_v3_LoggingTest extends CiviUnitTestCase {
  35
  36   /**
  37    * Sets up the fixture, for example, opens a network connection.
  38    *
  39    * This method is called before a test is executed.
  40    */
  41   protected function setUp() {
  42     $this->ensureTempColIsCleanedUp();
  43     parent::setUp();
  44   }
  45
  46   /**
  47    * Clean up log tables.
  48    */
  49   protected function tearDown() {
  50     $this->quickCleanup(array('civicrm_email', 'civicrm_address'));
  51     parent::tearDown();
  52     $this->callAPISuccess('Setting', 'create', array('logging' => FALSE));
  53     $schema = new CRM_Logging_Schema();
  54     $schema->dropAllLogTables();
  55     CRM_Core_DAO::executeQuery("DELETE FROM civicrm_setting WHERE name LIKE 'logg%'");
  56   }
  57
  58   /**
  59    * Test that logging is successfully enabled and disabled.
  60    */
  61   public function testEnableDisableLogging() {
  62     $this->assertEquals(0, $this->callAPISuccessGetValue('Setting', array('name' => 'logging')));
  63     $this->assertLoggingEnabled(FALSE);
  64
  65     $this->callAPISuccess('Setting', 'create', array('logging' => TRUE));
  66     $this->assertLoggingEnabled(TRUE);
  67     $this->checkLogTableCreated();
  68     $this->checkTriggersCreated(TRUE);
  69     // Create a contact to make sure they aren't borked.
  70     $this->individualCreate();
  71     $this->assertTrue($this->callAPISuccessGetValue('Setting', array('name' => 'logging')));
  72     $this->assertEquals(1, $this->callAPISuccessGetValue('Setting', array('name' => 'logging_all_tables_uniquid')));
  73     $this->assertEquals(
  74       date('Y-m-d'),
  75       date('Y-m-d', strtotime($this->callAPISuccessGetValue('Setting', array('name' => 'logging_uniqueid_date'))))
  76     );
  77
  78     $this->callAPISuccess('Setting', 'create', array('logging' => FALSE));
  79     $this->assertEquals(0, $this->callAPISuccessGetValue('Setting', array('name' => 'logging')));
  80     $this->assertLoggingEnabled(FALSE);
  81   }
  82
  83   /**
  84    * Test that logging is successfully enabled and disabled.
  85    */
  86   public function testEnableDisableLoggingWithTriggerHook() {
  87     $this->hookClass->setHook('civicrm_alterLogTables', array($this, 'innodbLogTableSpec'));
  88     $this->callAPISuccess('Setting', 'create', array('logging' => TRUE));
  89     $this->checkINNODBLogTableCreated();
  90     $this->checkTriggersCreated(TRUE);
  91     // Create a contact to make sure they aren't borked.
  92     $this->individualCreate();
  93     $this->callAPISuccess('Setting', 'create', array('logging' => FALSE));
  94   }
  95
  96   /**
  97    * Check responsible creation when old structure log table exists.
  98    *
  99    * When an existing table exists NEW tables will have the varchar type for log_conn_id.
 100    *
 101    * Existing tables will be unchanged, and the trigger will use log_conn_id
 102    * rather than uniqueId to be consistent across the tables.
 103    *
 104    * The settings for unique id will not be set.
 105    */
 106   public function testEnableLoggingLegacyLogTableExists() {
 107     $this->createLegacyStyleContactLogTable();
 108     $this->callAPISuccess('Setting', 'create', array('logging' => TRUE));
 109     $this->checkTriggersCreated(FALSE);
 110     $this->assertEquals(0, $this->callAPISuccessGetValue('Setting', array('name' => 'logging_all_tables_uniquid')));
 111     $this->assertEmpty($this->callAPISuccessGetValue('Setting', array('name' => 'logging_uniqueid_date')));
 112   }
 113
 114   /**
 115    * Check we can update legacy log tables using the api function.
 116    */
 117   public function testUpdateLegacyLogTable() {
 118     $this->createLegacyStyleContactLogTable();
 119     $this->callAPISuccess('Setting', 'create', array('logging' => TRUE));
 120     $this->callAPISuccess('System', 'updatelogtables', array());
 121     $this->checkLogTableCreated();
 122     $this->checkTriggersCreated(TRUE);
 123     $this->assertEquals(0, $this->callAPISuccessGetValue('Setting', array('name' => 'logging_all_tables_uniquid')));
 124     $this->assertEquals(
 125       date('Y-m-d'),
 126       date('Y-m-d', strtotime($this->callAPISuccessGetValue('Setting', array('name' => 'logging_uniqueid_date'))))
 127     );
 128   }
 129
 130   /**
 131    * Check if we can create missing log tables using api.
 132    */
 133   public function testCreateMissingLogTables() {
 134     $this->callAPISuccess('Setting', 'create', array('logging' => TRUE));
 135     CRM_Core_DAO::executeQuery("DROP TABLE log_civicrm_contact");
 136     $this->callAPISuccess('System', 'createmissinglogtables', array());
 137
 138     //Assert if log_civicrm_contact is created.
 139     $this->checkLogTableCreated();
 140   }
 141
 142   /**
 143    * Check we can update legacy log tables using the api function.
 144    */
 145   public function testUpdateLogTableHookINNODB() {
 146     $this->createLegacyStyleContactLogTable();
 147     $this->callAPISuccess('Setting', 'create', array('logging' => TRUE));
 148     $this->hookClass->setHook('civicrm_alterLogTables', array($this, 'innodbLogTableSpec'));
 149     $this->callAPISuccess('System', 'updatelogtables', array());
 150     $this->checkINNODBLogTableCreated();
 151     $this->checkTriggersCreated(TRUE);
 152     // Make sure that the absence of a hook specifying INNODB does not cause revert to archive.
 153     // Only a positive action, like specifying ARCHIVE in a hook should trigger a change back to archive.
 154     $this->hookClass->setHook('civicrm_alterLogTables', array());
 155     $schema = new CRM_Logging_Schema();
 156     $spec = $schema->getLogTableSpec();
 157     $this->assertEquals(array(), $spec['civicrm_contact']);
 158     $this->callAPISuccess('System', 'updatelogtables', array());
 159     $this->checkINNODBLogTableCreated();
 160   }
 161
 162   /**
 163    * Check that if a field is added then the trigger is updated on refresh.
 164    */
 165   public function testRebuildTriggerAfterSchemaChange() {
 166     $this->callAPISuccess('Setting', 'create', array('logging' => TRUE));
 167     $tables = array('civicrm_acl', 'civicrm_website');
 168     foreach ($tables as $table) {
 169       CRM_Core_DAO::executeQuery("ALTER TABLE $table ADD column temp_col INT(10)");
 170     }
 171
 172     $schema = new CRM_Logging_Schema();
 173     $schema->fixSchemaDifferencesForAll(TRUE);
 174
 175     foreach ($tables as $table) {
 176       $this->assertTrue($this->checkColumnExistsInTable('log_' . $table, 'temp_col'), 'log_' . $table . ' has temp_col');
 177       $dao = CRM_Core_DAO::executeQuery("SHOW TRIGGERS LIKE '{$table}'");
 178       while ($dao->fetch()) {
 179         $this->assertContains('temp_col', $dao->Statement);
 180       }
 181     }
 182     CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_acl DROP column temp_col");
 183     CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_website DROP column temp_col");
 184   }
 185
 186   /**
 187    * Use a hook to declare an INNODB engine for the contact log table.
 188    *
 189    * @param array $logTableSpec
 190    */
 191   public function innodbLogTableSpec(&$logTableSpec) {
 192     $logTableSpec['civicrm_contact'] = array(
 193       'engine' => 'InnoDB',
 194       'engine_config' => 'ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4',
 195       'indexes' => array(
 196         'index_id' => 'id',
 197         'index_log_conn_id' => 'log_conn_id',
 198         'index_log_date' => 'log_date',
 199       ),
 200     );
 201   }
 202
 203   /**
 204    * Check the log tables were created and look OK.
 205    */
 206   protected function checkLogTableCreated() {
 207     $dao = CRM_Core_DAO::executeQuery("SHOW CREATE TABLE log_civicrm_contact");
 208     $dao->fetch();
 209     $this->assertEquals('log_civicrm_contact', $dao->Table);
 210     $tableField = 'Create_Table';
 211     $this->assertContains('`log_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,', $dao->$tableField);
 212     $this->assertContains('`log_conn_id` varchar(17)', $dao->$tableField);
 213     return $dao->$tableField;
 214   }
 215
 216   /**
 217    * Check the log tables were created and reflect the INNODB hook.
 218    */
 219   protected function checkINNODBLogTableCreated() {
 220     $createTableString = $this->checkLogTableCreated();
 221     $this->assertContains('ENGINE=InnoDB', $createTableString);
 222     $this->assertContains('ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4', $createTableString);
 223     $this->assertContains('KEY `index_id` (`id`),', $createTableString);
 224   }
 225
 226   /**
 227    * Check the triggers were created and look OK.
 228    *
 229    * @param bool $unique
 230    *   Is the site configured for unique logging connection IDs per CRM-18193?
 231    */
 232   protected function checkTriggersCreated($unique) {
 233     $dao = CRM_Core_DAO::executeQuery("SHOW TRIGGERS LIKE 'civicrm_contact'");
 234     while ($dao->fetch()) {
 235       if ($dao->Timing == 'After') {
 236         if ($unique) {
 237           $this->assertContains('@uniqueID', $dao->Statement);
 238         }
 239         else {
 240           $this->assertContains('CONNECTION_ID()', $dao->Statement);
 241         }
 242       }
 243     }
 244   }
 245
 246   /**
 247    * Assert logging is enabled or disabled as per input parameter.
 248    *
 249    * @param bool $expected
 250    *   Do we expect it to be enabled.
 251    */
 252   protected function assertLoggingEnabled($expected) {
 253     $schema = new CRM_Logging_Schema();
 254     $this->assertTrue($schema->isEnabled() === $expected);
 255   }
 256
 257   /**
 258    * Create the contact log table with log_conn_id as an integer.
 259    */
 260   protected function createLegacyStyleContactLogTable() {
 261     CRM_Core_DAO::executeQuery("
 262       CREATE TABLE log_civicrm_contact
 263       (log_conn_id INT NULL, log_user_id INT NULL, log_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP)
 264       ENGINE=ARCHIVE
 265       (SELECT c.*, CURRENT_TIMESTAMP as log_date, 'Initialize' as 'log_action'
 266       FROM civicrm_contact c)
 267     ");
 268   }
 269
 270   /**
 271    * Test changes can be reverted.
 272    */
 273   public function testRevert() {
 274     $contactId = $this->individualCreate();
 275     $this->callAPISuccess('Setting', 'create', array('logging' => TRUE));
 276     CRM_Core_DAO::executeQuery("SET @uniqueID = 'woot'");
 277     $timeStamp = date('Y-m-d H:i:s');
 278     $this->callAPISuccess('Contact', 'create', array(
 279       'id' => $contactId,
 280       'first_name' => 'Dopey',
 281       'api.email.create' => array('email' => 'dopey@mail.com'))
 282     );
 283     $email = $this->callAPISuccessGetSingle('email', array('email' => 'dopey@mail.com'));
 284     $this->callAPIAndDocument('Logging', 'revert', array('log_conn_id' => 'woot', 'log_date' => $timeStamp), __FILE__, 'Revert');
 285     $this->assertEquals('Anthony', $this->callAPISuccessGetValue('contact', array('id' => $contactId, 'return' => 'first_name')));
 286     $this->callAPISuccessGetCount('Email', array('id' => $email['id']), 0);
 287   }
 288
 289   /**
 290    * Test changes can be reverted.
 291    */
 292   public function testRevertNoDate() {
 293     $contactId = $this->individualCreate();
 294     $this->callAPISuccess('Setting', 'create', array('logging' => TRUE));
 295     CRM_Core_DAO::executeQuery("SET @uniqueID = 'Wot woot'");
 296     $this->callAPISuccess('Contact', 'create', array(
 297         'id' => $contactId,
 298         'first_name' => 'Dopey',
 299         'api.email.create' => array('email' => 'dopey@mail.com'))
 300     );
 301     $email = $this->callAPISuccessGetSingle('email', array('email' => 'dopey@mail.com'));
 302     $this->callAPISuccess('Logging', 'revert', array('log_conn_id' => 'Wot woot'));
 303     $this->assertEquals('Anthony', $this->callAPISuccessGetValue('contact', array('id' => $contactId, 'return' => 'first_name')));
 304     $this->callAPISuccessGetCount('Email', array('id' => $email['id']), 0);
 305   }
 306
 307   /**
 308    * Ensure that a limited list of tables can be reverted.
 309    *
 310    * In this case ONLY civicrm_address is reverted and we check that email, contact and contribution
 311    * entities have not been.
 312    *
 313    * @throws \Exception
 314    */
 315   public function testRevertRestrictedTables() {
 316
 317     CRM_Core_DAO::executeQuery("SET @uniqueID = 'temp name'");
 318     $this->callAPISuccessGetValue('Setting', array('name' => 'logging_all_tables_uniquid'), TRUE);
 319     $this->callAPISuccess('Setting', 'create', array('logging' => TRUE));
 320
 321     $contactId = $this->individualCreate(array('address' => array(array('street_address' => '27 Cool way', 'location_type_id' => 1))));
 322     $contact = $this->callAPISuccessGetSingle('contact', array('id' => $contactId));
 323     $this->assertEquals('Anthony', $contact['first_name']);
 324     $this->assertEquals('anthony_anderson@civicrm.org', $contact['email']);
 325     $this->assertEquals('27 Cool way', $contact['street_address']);
 326
 327     sleep(1);
 328     CRM_Core_DAO::executeQuery("SET @uniqueID = 'bitty bot bot'");
 329     $this->callAPISuccess('Contact', 'create', array(
 330         'id' => $contactId,
 331         'first_name' => 'Dopey',
 332         'address' => array(array('street_address' => '25 Dorky way', 'location_type_id' => 1)),
 333         'email' => array('email' => array('email' => 'dopey@mail.com', 'location_type_id' => 1)),
 334         'api.contribution.create' => array('financial_type_id' => 'Donation', 'receive_date' => 'now', 'total_amount' => 10),
 335       )
 336     );
 337     $contact = $this->callAPISuccessGetSingle('contact', array('id' => $contactId, 'return' => array('first_name', 'email', 'modified_date', 'street_address')));
 338     $this->assertEquals('Dopey', $contact['first_name']);
 339     $this->assertEquals('dopey@mail.com', $contact['email']);
 340     $this->assertEquals('25 Dorky way', $contact['street_address']);
 341     $modifiedDate = $contact['modified_date'];
 342     // To protect against the modified date not changing due to the updates being too close together.
 343     sleep(1);
 344     $loggings = $this->callAPISuccess('Logging', 'get', array('log_conn_id' => 'bitty bot bot', 'tables' => array('civicrm_address')));
 345     $this->assertEquals('civicrm_address', $loggings['values'][0]['table'], CRM_Core_DAO::executeQuery('SELECT * FROM log_civicrm_address')->toArray());
 346     $this->assertEquals(1, $loggings['count'], CRM_Core_DAO::executeQuery('SELECT * FROM log_civicrm_address')->toArray());
 347     $this->assertEquals('27 Cool way', $loggings['values'][0]['from']);
 348     $this->assertEquals('25 Dorky way', $loggings['values'][0]['to']);
 349     $this->callAPISuccess('Logging', 'revert', array('log_conn_id' => 'bitty bot bot', 'tables' => array('civicrm_address')));
 350
 351     $contact = $this->callAPISuccessGetSingle('contact', array('id' => $contactId, 'return' => array('first_name', 'email', 'modified_date', 'street_address')));
 352     $this->assertEquals('Dopey', $contact['first_name']);
 353     $this->assertEquals('dopey@mail.com', $contact['email']);
 354     $this->assertEquals('27 Cool way', $contact['street_address']);
 355     $this->callAPISuccessGetCount('Contribution', array('contact_id' => $contactId), 1);
 356     $this->assertTrue(strtotime($modifiedDate) < strtotime($contact['modified_date']));
 357   }
 358
 359   /**
 360    * Test changes can be reverted.
 361    */
 362   public function testRevertNoDateNotUnique() {
 363     $contactId = $this->individualCreate();
 364     $this->callAPISuccess('Setting', 'create', array('logging' => TRUE));
 365     CRM_Core_DAO::executeQuery("SET @uniqueID = 'Wopity woot'");
 366     $this->callAPISuccess('Contact', 'create', array(
 367         'id' => $contactId,
 368         'first_name' => 'Dopey',
 369         'api.email.create' => array('email' => 'dopey@mail.com'))
 370     );
 371     $this->callAPISuccess('Setting', 'create', array('logging_all_tables_uniquid' => FALSE));
 372     $this->callAPISuccess('Setting', 'create', array('logging_uniqueid_date' => date('Y-m-d H:i:s', strtotime('+ 1 hour'))));
 373     $this->callAPIFailure(
 374       'Logging',
 375       'revert',
 376       array('log_conn_id' => 'Wopity woot'),
 377       'The connection date must be passed in to disambiguate this logging entry per CRM-18193'
 378     );
 379   }
 380
 381   /**
 382    * Test changes can be retrieved.
 383    */
 384   public function testGet() {
 385     $contactId = $this->individualCreate();
 386     $this->callAPISuccess('Setting', 'create', array('logging' => TRUE));
 387     CRM_Core_DAO::executeQuery("SET @uniqueID = 'wooty woot'");
 388     $timeStamp = date('Y-m-d H:i:s');
 389     $this->callAPISuccess('Contact', 'create', array(
 390         'id' => $contactId,
 391         'first_name' => 'Dopey',
 392         'last_name' => 'Dwarf',
 393         'api.email.create' => array('email' => 'dopey@mail.com'))
 394     );
 395     $this->callAPISuccessGetSingle('email', array('email' => 'dopey@mail.com'));
 396     $diffs = $this->callAPISuccess('Logging', 'get', array('log_conn_id' => 'wooty woot', 'log_date' => $timeStamp), __FUNCTION__, __FILE__);
 397     $this->assertLoggingIncludes($diffs['values'], array('to' => 'Dwarf, Dopey'));
 398     $this->assertLoggingIncludes($diffs['values'], array('to' => 'Mr. Dopey Dwarf II', 'table' => 'civicrm_contact', 'action' => 'Update', 'field' => 'display_name'));
 399     $this->assertLoggingIncludes($diffs['values'], array('to' => 'dopey@mail.com', 'table' => 'civicrm_email', 'action' => 'Insert', 'field' => 'email'));
 400   }
 401
 402   /**
 403    * Test changes can be retrieved without log_date being required.
 404    */
 405   public function testGetNoDate() {
 406     $contactId = $this->individualCreate();
 407     $this->callAPISuccess('Setting', 'create', array('logging' => TRUE));
 408     CRM_Core_DAO::executeQuery("SET @uniqueID = 'wooty wop wop'");
 409     $this->callAPISuccess('Contact', 'create', array(
 410         'id' => $contactId,
 411         'first_name' => 'Dopey',
 412         'last_name' => 'Dwarf',
 413         'api.email.create' => array('email' => 'dopey@mail.com'))
 414     );
 415     $this->callAPISuccessGetSingle('email', array('email' => 'dopey@mail.com'));
 416     $diffs = $this->callAPIAndDocument('Logging', 'get', array('log_conn_id' => 'wooty wop wop'), __FUNCTION__, __FILE__);
 417     $this->assertLoggingIncludes($diffs['values'], array('to' => 'Dwarf, Dopey'));
 418     $this->assertLoggingIncludes($diffs['values'], array('to' => 'Mr. Dopey Dwarf II', 'table' => 'civicrm_contact', 'action' => 'Update', 'field' => 'display_name'));
 419     $this->assertLoggingIncludes($diffs['values'], array('to' => 'dopey@mail.com', 'table' => 'civicrm_email', 'action' => 'Insert', 'field' => 'email'));
 420   }
 421
 422   /**
 423    * Assert the values in the $expect array in included in the logging diff.
 424    *
 425    * @param array $diffs
 426    * @param array $expect
 427    *
 428    * @return bool
 429    * @throws \CRM_Core_Exception
 430    */
 431   public function assertLoggingIncludes($diffs, $expect) {
 432     foreach ($diffs as $diff) {
 433       foreach ($expect as $expectKey => $expectValue) {
 434         if ($diff[$expectKey] != $expectValue) {
 435           continue;
 436         }
 437         return TRUE;
 438       }
 439     }
 440     throw new CRM_Core_Exception("No match found for key : $expectKey with value : $expectValue");
 441   }
 442
 443   /**
 444    * Check if the column exists in the table.
 445    *
 446    * @param string $table
 447    * @param string $column
 448    *
 449    * @return bool
 450    */
 451   protected function checkColumnExistsInTable($table, $column) {
 452     $dao = CRM_Core_DAO::executeQuery("SHOW columns FROM {$table} WHERE Field = '{$column}'");
 453     $dao->fetch(TRUE);
 454     return ($dao->N == 1);
 455   }
 456
 457   /**
 458    * Helper for when it crashes and clean up needs to be done.
 459    */
 460   protected function ensureTempColIsCleanedUp() {
 461     if ($this->checkColumnExistsInTable('civicrm_acl', 'temp_col')) {
 462       CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_acl DROP Column temp_col");
 463       CRM_Core_DAO::executeQuery("ALTER TABLE civicrm_website DROP Column temp_col");
 464     }
 465   }
 466
 467 }