public function test04()
{
try {
StaticDataLayer::query('delete from `TABLE1`');
} catch (DataLayerException $e) {
$this->assertContains('Unknown column', $e->getMessage());
$this->assertContains('c3', $e->getMessage());
}
}
public function test01()
{
// Run audit.
$this->runAudit();
// TABLE1 MUST exist.
$tables = $this->getAuditTables();
$this->assertNotNull(StaticDataLayer::searchInRowSet('table_name', 'TABLE1', $tables));
// TABLE1 MUST have triggers.
$triggers = $this->getTableTriggers('TABLE1');
$this->assertNotNull(StaticDataLayer::searchInRowSet('trigger_name', 'trg_t1_insert', $triggers));
$this->assertNotNull(StaticDataLayer::searchInRowSet('trigger_name', 'trg_t1_update', $triggers));
$this->assertNotNull(StaticDataLayer::searchInRowSet('trigger_name', 'trg_t1_delete', $triggers));
$actual = $this->getTableColumns(self::$auditSchema, 'TABLE1');
$expected = [];
$expected[] = ['column_name' => 'c1', 'column_type' => 'tinyint(4)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null];
$expected[] = ['column_name' => 'c2', 'column_type' => 'smallint(6)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null];
$expected[] = ['column_name' => 'c3', 'column_type' => 'mediumint(9)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null];
$expected[] = ['column_name' => 'c4', 'column_type' => 'int(11)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null];
$this->assertSame($expected, $actual);
// Test triggers.
StaticDataLayer::query('insert into `TABLE1`(c1, c2, c3, c4) values(1, 2, 3, 4)');
StaticDataLayer::query('update `TABLE1` set c1=10, c2=20, c3=30, c4=40');
StaticDataLayer::query('delete from `TABLE1`');
$rows = StaticDataLayer::executeRows(sprintf('select * from `%s`.`TABLE1` where c3 is not null', self::$auditSchema));
$this->assertSame(4, count($rows));
// Drop column c3.
StaticDataLayer::multiQuery(file_get_contents(__DIR__ . '/config/drop_column.sql'));
$this->runAudit();
// TABLE1 MUST exist.
$tables = $this->getAuditTables();
$this->assertNotNull(StaticDataLayer::searchInRowSet('table_name', 'TABLE1', $tables));
// TABLE1 MUST have triggers.
$triggers = $this->getTableTriggers('TABLE1');
$this->assertNotNull(StaticDataLayer::searchInRowSet('trigger_name', 'trg_t1_insert', $triggers));
$this->assertNotNull(StaticDataLayer::searchInRowSet('trigger_name', 'trg_t1_update', $triggers));
$this->assertNotNull(StaticDataLayer::searchInRowSet('trigger_name', 'trg_t1_delete', $triggers));
// TABLE1 must have column c3.
$actual = $this->getTableColumns(self::$auditSchema, 'TABLE1');
$expected = [];
$expected[] = ['column_name' => 'c1', 'column_type' => 'tinyint(4)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null];
$expected[] = ['column_name' => 'c2', 'column_type' => 'smallint(6)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null];
$expected[] = ['column_name' => 'c3', 'column_type' => 'mediumint(9)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null];
$expected[] = ['column_name' => 'c4', 'column_type' => 'int(11)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null];
$this->assertSame($expected, $actual);
// Test triggers.
StaticDataLayer::query('insert into `TABLE1`(c1, c2, c4) values(1, 2, 4)');
StaticDataLayer::query('update `TABLE1` set c1=10, c2=20, c4=40');
StaticDataLayer::query('delete from `TABLE1`');
// Assert we 4 rows with c3 is null.
$rows = StaticDataLayer::executeRows(sprintf('select * from `%s`.`TABLE1` where c3 is null', self::$auditSchema));
$this->assertSame(4, count($rows));
// Assert we 8 rows in total.
$rows = StaticDataLayer::executeRows(sprintf('select * from `%s`.`TABLE1`', self::$auditSchema));
$this->assertSame(8, count($rows));
}
/**
* Test delete trigger is working correctly.
*/
public function test02c()
{
StaticDataLayer::query("SET time_zone = 'Europe/Amsterdam'");
// Delete a row from AUT_COMPANY.
$sql = sprintf('
delete from `AUT_COMPANY`
where `cmp_abbr` = %s', StaticDataLayer::quoteString('SYS'));
StaticDataLayer::executeNone($sql);
// Get audit rows.
$sql = sprintf("\nselect * \nfrom `test_audit`.`AUT_COMPANY`\nwhere audit_statement = 'DELETE'");
$rows = StaticDataLayer::executeRows($sql);
// We expect 1 row.
$this->assertEquals(1, count($rows));
$row = $rows[0];
// Tests on fields.
$time = new \DateTime();
$this->assertLessThanOrEqual(date_format($time->add(new \DateInterval('PT1M')), 'Y-m-d H:i:s'), $row['audit_timestamp']);
$time = new \DateTime();
$this->assertGreaterThanOrEqual(date_format($time->sub(new \DateInterval('PT1M')), 'Y-m-d H:i:s'), $row['audit_timestamp']);
$this->assertEquals('OLD', $row['audit_type']);
$this->assertNotEmpty($row['audit_uuid']);
$this->assertEquals(3, $row['audit_rownum']);
$this->assertSame('12345', $row['audit_ses_id']);
$this->assertSame('7011', $row['audit_usr_id']);
$this->assertEquals('1', $row['cmp_id']);
$this->assertEquals('SYS', $row['cmp_abbr']);
$this->assertEquals('CMP_ID_SYS', $row['cmp_label']);
}
