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; }