public function test01()
{
$this->runAudit();
// TABLE1 MUST and TABLE2 MUST not exist.
$tables = $this->getAuditTables();
$this->assertNotNull(StaticDataLayer::searchInRowSet('table_name', 'TABLE1', $tables));
$this->assertNull(StaticDataLayer::searchInRowSet('table_name', 'TABLE2', $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));
// Create new table TABLE2.
StaticDataLayer::multiQuery(file_get_contents(__DIR__ . '/config/create_new_table.sql'));
$this->runAudit();
// TABLE1 and TABLE2 MUST exist.
$tables = $this->getAuditTables();
$this->assertNotNull(StaticDataLayer::searchInRowSet('table_name', 'TABLE1', $tables));
$this->assertNotNull(StaticDataLayer::searchInRowSet('table_name', 'TABLE2', $tables));
// TABLE1 and TABLE2 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));
$triggers = $this->getTableTriggers('TABLE2');
$this->assertNotNull(StaticDataLayer::searchInRowSet('trigger_name', 'trg_t2_insert', $triggers));
$this->assertNotNull(StaticDataLayer::searchInRowSet('trigger_name', 'trg_t2_update', $triggers));
$this->assertNotNull(StaticDataLayer::searchInRowSet('trigger_name', 'trg_t2_delete', $triggers));
}
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));
}
/**
* Generate body of stored procedure.
*
* @param array[] $columns Columns from table.
* @param array[] $params Params for where block.
* @param string[] $lines Stored procedure code lines.
*/
protected function bodyPart($params, $columns, &$lines)
{
$set = [];
$primaryKeys = DataLayer::getTablePrimaryKeys($this->dataSchema, $this->tableName);
$lines[] = sprintf('update %s', $this->tableName);
foreach ($columns as $column) {
$check = StaticDataLayer::searchInRowSet('Column_name', $column['column_name'], $primaryKeys);
if (!isset($check)) {
$set[] = $column;
}
}
reset($set);
$first = key($set);
$lines[] = 'set';
$lengthLastLine = 0;
foreach ($set as $key => $column) {
if ($key === $first) {
$lengthLastLine = strlen($lines[count($lines) - 1]);
$format = sprintf("%%%ds %%s = p_%%s", $lengthLastLine);
$line = sprintf($format, '', $column['column_name'], $column['column_name']);
$lines[count($lines) - 1] .= $line;
} else {
$format = sprintf("%%-%ds %%s = p_%%s", $lengthLastLine + 3);
$line = sprintf($format, ',', $column['column_name'], $column['column_name']);
$lines[] = $line;
}
}
$lines[] = 'where';
reset($params);
$first = key($params);
foreach ($params as $key => $column) {
if ($key === $first) {
$format = sprintf("%%%ds %%s = p_%%s", 1);
$line = sprintf($format, '', $column['column_name'], $column['column_name']);
$lines[count($lines) - 1] .= $line;
} else {
$format = sprintf("and%%%ds %%s = p_%%s", 3);
$line = sprintf($format, '', $column['column_name'], $column['column_name']);
$lines[] = $line;
}
}
$lines[] = ';';
}
/**
* Add not null to audit columns if it not nullable.
*
* @param array $theColumns Audit columns.
*
* @return array
*/
private function addNotNull($theColumns)
{
$modifiedColumns = [];
foreach ($theColumns as $column) {
$modifiedColumn = $column;
$auditColumn = StaticDataLayer::searchInRowSet('column_name', $modifiedColumn['column_name'], $this->auditColumns);
if (isset($auditColumn)) {
if ($modifiedColumn['is_nullable'] === 'NO') {
$modifiedColumn['column_type'] = sprintf('%s not null', $modifiedColumn['column_type']);
}
}
$modifiedColumns[] = $modifiedColumn;
}
return $modifiedColumns;
}
/**
* Processed known tables.
*
* @return int The exit status.
*/
private function knownTables()
{
$status = 0;
foreach ($this->dataSchemaTables as $table) {
if ($this->config['tables'][$table['table_name']]['audit']) {
if (isset($this->configMetadata['table_columns'][$table['table_name']])) {
$tableColumns = $this->configMetadata['table_columns'][$table['table_name']];
} else {
$tableColumns = [];
}
$configTable = new TableMetadata($table['table_name'], $this->config['database']['data_schema'], $tableColumns);
$currentTable = new AuditTable($this->io, $configTable, $this->config['database']['audit_schema'], $this->auditColumnsMetadata, $this->config['tables'][$table['table_name']]['alias'], $this->config['tables'][$table['table_name']]['skip']);
// Ensure an audit table exists.
if (StaticDataLayer::searchInRowSet('table_name', $table['table_name'], $this->auditSchemaTables) === null) {
$currentTable->createAuditTable();
}
// Drop and create audit triggers and add new columns to the audit table.
$ok = $currentTable->main($this->config['additional_sql']);
if ($ok) {
$columns = new TableColumnsMetadata(AuditDataLayer::getTableColumns($this->config['database']['data_schema'], $table['table_name']));
$this->setConfigTableColumns($table['table_name'], $columns);
} else {
$status += 1;
}
}
}
return $status;
}
/**
* Main function for asking.
*
* @param array[] $tableList All existing tables from data schema.
*/
private function startAsking($tableList)
{
$question = new Question('Please enter <note>TABLE NAME</note>: ');
$tableName = $this->helper->ask($this->input, $this->output, $question);
$key = StaticDataLayer::searchInRowSet('table_name', $tableName, $tableList);
if (!isset($key)) {
$this->io->logNote('Table \'%s\' not exist.', $tableName);
} else {
$this->askForCreateSP('INSERT', $tableName);
$this->askForCreateSP('UPDATE', $tableName);
$this->askForCreateSP('DELETE', $tableName);
$this->askForCreateSP('SELECT', $tableName);
}
}
/**
* Test update trigger is working correctly.
*/
public function test02b()
{
// Set session and user ID.
StaticDataLayer::executeNone('set @abc_g_ses_id=12345');
// The combination of my suitcase.
StaticDataLayer::executeNone('set @abc_g_usr_id=7011');
// Update a row into AUT_COMPANY.
$sql = sprintf('
update `AUT_COMPANY`
set `cmp_label` = %s
where `cmp_abbr` = %s', StaticDataLayer::quoteString('CMP_ID_SYS'), StaticDataLayer::quoteString('SYS'));
StaticDataLayer::executeNone($sql);
// Get audit rows.
$sql = sprintf("\nselect * \nfrom `test_audit`.`AUT_COMPANY`\nwhere `audit_statement` = 'UPDATE'");
StaticDataLayer::query("SET time_zone = 'Europe/Amsterdam'");
$rows = StaticDataLayer::executeRows($sql);
// We expect 2 rows.
$this->assertEquals(2, count($rows), 'row count');
// Tests on 'OLD' fields.
$row = $rows[StaticDataLayer::searchInRowSet('audit_type', 'OLD', $rows)];
$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->assertSame('2', $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('SYS', $row['cmp_label']);
// Tests on 'NEW' fields.
$row = $rows[StaticDataLayer::searchInRowSet('audit_type', 'NEW', $rows)];
$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('NEW', $row['audit_type']);
$this->assertNotEmpty($row['audit_uuid']);
$this->assertSame('2', $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']);
}
/**
* Resolves the canonical column types of the audit table columns.
*/
private function resolveCanonicalAuditColumns()
{
if (empty($this->config['audit_columns'])) {
$this->auditColumnsMetadata = new TableColumnsMetadata();
} else {
$schema = $this->config['database']['audit_schema'];
$tableName = '_TMP_' . uniqid();
AuditDataLayer::createTemporaryTable($schema, $tableName, $this->config['audit_columns']);
$columns = AuditDataLayer::getTableColumns($schema, $tableName);
AuditDataLayer::dropTemporaryTable($schema, $tableName);
foreach ($this->config['audit_columns'] as $audit_column) {
$key = StaticDataLayer::searchInRowSet('column_name', $audit_column['column_name'], $columns);
if (isset($audit_column['value_type'])) {
$columns[$key]['value_type'] = $audit_column['value_type'];
}
if (isset($audit_column['expression'])) {
$columns[$key]['expression'] = $audit_column['expression'];
}
}
$this->auditColumnsMetadata = new TableColumnsMetadata($columns, 'AuditColumnMetadata');
}
}
/**
* Generate main part with name and params.
*
* @param array[] $columns Columns from table.
* @param string|null $spType Stored procedure type {insert|update|delete|select}.
*
* @return array[]|null
*/
protected function checkUniqueKeys($columns, $spType = null)
{
$primaryKeys = DataLayer::getTablePrimaryKeys($this->dataSchema, $this->tableName);
$uniqueKeys = DataLayer::getTableUniqueKeys($this->dataSchema, $this->tableName);
$resultColumns = [];
if (!isset($spType)) {
if (count($uniqueKeys) <= 0 && count($primaryKeys) <= 0) {
return null;
} else {
return $columns;
}
}
if (count($primaryKeys) > 0) {
foreach ($columns as $column) {
$check = StaticDataLayer::searchInRowSet('Column_name', $column['column_name'], $primaryKeys);
if (isset($check)) {
$resultColumns[] = $column;
}
}
return $resultColumns;
} else {
if (count($uniqueKeys) > 0) {
reset($uniqueKeys);
$first = key($uniqueKeys);
if (count($uniqueKeys) > 1) {
$this->io->writeln(sprintf('Table <dbo>%s</dbo> has more than one unique key.', $this->tableName));
$array = [];
foreach ($uniqueKeys as $column) {
if (isset($array[$column['Key_name']])) {
$array[$column['Key_name']] .= ',';
$array[$column['Key_name']] .= $column['Column_name'];
} else {
$array[$column['Key_name']] = $column['Column_name'];
}
}
$tableArray = [];
foreach ($array as $key => $column) {
$tableArray[] = [$key, $column];
}
$table = new Table($this->output);
$table->setHeaders(['Name', 'Keys']);
$table->setRows($tableArray);
$table->render();
$question = new Question(sprintf('What unique keys use in statement?(%s): ', $uniqueKeys[$first]['Key_name']), $uniqueKeys[$first]['Key_name']);
$uniqueKeys = $this->helper->ask($this->input, $this->output, $question);
$uniqueKeys = explode(',', $array[$uniqueKeys]);
foreach ($uniqueKeys as $column) {
$resultColumns[] = ['column_name' => $column];
}
return $resultColumns;
} else {
foreach ($uniqueKeys as $column) {
$resultColumns[] = ['column_name' => $column['Column_name']];
}
return $resultColumns;
}
} else {
return null;
}
}
}
/**
* Add highlighting to columns.
*/
public function addHighlighting()
{
$styledColumns = [];
foreach ($this->rows as $key => $column) {
$styledColumn = $column;
if (is_array($column)) {
// Highlighting for data table column types and audit.
if (!empty($column['data_table_type'])) {
if (isset($column['data_table_type']) && !isset($column['audit_table_type'])) {
if (!isset($column['column_name'])) {
$styledColumns[$key - 1]['column_name'] = sprintf('<mm_column>%s</>', $styledColumns[$key - 1]['column_name']);
}
$styledColumn['column_name'] = sprintf('<mm_column>%s</>', $styledColumn['column_name']);
$styledColumn['data_table_type'] = sprintf('<mm_type>%s</>', $styledColumn['data_table_type']);
} else {
if (!isset($column['data_table_type']) && isset($column['audit_table_type'])) {
$styledColumn['audit_table_type'] = sprintf('<mm_type>%s</>', $styledColumn['audit_table_type']);
} else {
if (strcmp($column['data_table_type'], $column['audit_table_type'])) {
if (!isset($column['column_name'])) {
$styledColumns[$key - 1]['column_name'] = sprintf('<mm_column>%s</>', $styledColumns[$key - 1]['column_name']);
}
$styledColumn['column_name'] = sprintf('<mm_column>%s</>', $styledColumn['column_name']);
$styledColumn['data_table_type'] = sprintf('<mm_type>%s</>', $styledColumn['data_table_type']);
$styledColumn['audit_table_type'] = sprintf('<mm_type>%s</>', $styledColumn['audit_table_type']);
}
}
}
} else {
// Highlighting for audit table column types and audit_columns in config file.
$searchColumn = StaticDataLayer::searchInRowSet('column_name', $styledColumn['column_name'], $this->auditColumns);
if (isset($searchColumn)) {
$configType = $this->auditColumns[$searchColumn]['column_type'];
if (isset($configType) && !isset($column['audit_table_type'])) {
$styledColumn['column_name'] = sprintf('<mm_column>%s</>', $styledColumn['column_name']);
$styledColumn['config_type'] = sprintf('<mm_type>%s</>', $styledColumn['config_type']);
} else {
if (!isset($configType) && isset($column['audit_table_type'])) {
$styledColumn['audit_table_type'] = sprintf('<mm_type>%s</>', $column['audit_table_type']);
} else {
if (strcmp($configType, $column['audit_table_type'])) {
$styledColumn['column_name'] = sprintf('<mm_column>%s</>', $styledColumn['column_name']);
$styledColumn['audit_table_type'] = sprintf('<mm_type>%s</>', $column['audit_table_type']);
$styledColumn['config_type'] = sprintf('<mm_type>%s</>', $styledColumn['config_type']);
}
}
}
}
}
}
$styledColumns[] = $styledColumn;
}
$this->rows = $styledColumns;
}
