/**
* Returns all triggers on a table.
*
* @param string $table_name The name of the table.
*
* @return \array[]
*/
private function getTableTriggers($table_name)
{
AuditDataLayer::disconnect();
AuditDataLayer::connect('localhost', 'test', 'test', self::$dataSchema);
$triggers = AuditDataLayer::getTableTriggers(self::$dataSchema, $table_name);
return $triggers;
}
/**
* Object constructor.
*
* @param string $dataSchema Data schema name.
* @param string $auditSchema Audit schema name.
* @param string $tableName The table name.
* @param array[] $theAuditColumns Audit columns from config file.
* @param bool $fullOption If set append table options to rows.
*/
public function __construct($dataSchema, $auditSchema, $tableName, $theAuditColumns, $fullOption)
{
$this->auditColumns = $theAuditColumns;
$this->fullOption = $fullOption;
$this->auditTableOptions = AuditDataLayer::getTableOptions($auditSchema, $tableName);
$this->dataTableOptions = AuditDataLayer::getTableOptions($dataSchema, $tableName);
}
/**
* Connects to a MySQL instance.
*
* @param array $settings The settings from the configuration file.
*/
protected function connect($settings)
{
$host = $this->getSetting($settings, true, 'database', 'host');
$user = $this->getSetting($settings, true, 'database', 'user');
$password = $this->getSetting($settings, true, 'database', 'password');
$database = $this->getSetting($settings, true, 'database', 'data_schema');
AuditDataLayer::setIo($this->io);
AuditDataLayer::connect($host, $user, $password, $database);
}
/**
* Drops all triggers.
*/
private function dropTriggers()
{
$data_schema = $this->config['database']['data_schema'];
$triggers = AuditDataLayer::getTriggers($data_schema);
foreach ($triggers as $trigger) {
$this->io->logInfo('Dropping trigger <dbo>%s</dbo> from table <dbo>%s</dbo>', $trigger['trigger_name'], $trigger['table_name']);
AuditDataLayer::dropTrigger($data_schema, $trigger['trigger_name']);
}
}
/**
* Returns a SQL statement for creating the audit table.
*
* @return string
*/
public function buildStatement()
{
$code = new MySqlCompoundSyntaxCodeStore();
$code->append(sprintf('create table `%s`.`%s`', $this->auditSchemaName, $this->tableName));
// Create SQL for columns.
$code->append('(');
$code->append($this->getColumnDefinitions());
// Create SQL for table options.
$tableOptions = AuditDataLayer::getTableOptions($this->dataSchemaName, $this->tableName);
$code->append(sprintf(') engine=%s character set=%s collate=%s', $tableOptions['engine'], $tableOptions['character_set_name'], $tableOptions['table_collation']));
return $code->getCode();
}
/**
* Object constructor.
*
* @param string $dataSchema Data database schema.
* @param string $auditSchema Audit database schema.
* @param string $tableName Table name.
* @param array[] $auditColumns Audit columns from config file.
*/
public function __construct($dataSchema, $auditSchema, $tableName, $auditColumns)
{
$this->dataSchema = $dataSchema;
$this->auditSchema = $auditSchema;
$this->tableName = $tableName;
$this->auditColumns = $auditColumns;
$dataColumns = new TableColumnsMetadata(AuditDataLayer::getTableColumns($this->dataSchema, $this->tableName));
$auditColumns = AuditDataLayer::getTableColumns($this->auditSchema, $this->tableName);
$auditColumns = $this->addNotNull($auditColumns);
$auditColumns = new TableColumnsMetadata($auditColumns);
$this->createDiffArray($dataColumns, $auditColumns);
}
/**
* {@inheritdoc}
*/
protected function execute(InputInterface $input, OutputInterface $output)
{
$this->io = new StratumStyle($input, $output);
$this->configFileName = $input->getArgument('config file');
$this->readConfigFile();
// Create database connection with params from config file
$this->connect($this->config);
$audit = new Audit($this->config, $this->configMetadata, $this->io);
$status = $audit->main();
// Drop database connection
AuditDataLayer::disconnect();
$this->rewriteConfig();
return $status;
}
/**
* Test audit table is created correctly.
*/
public function test01()
{
$application = new Application();
$application->add(new AuditCommand());
/** @var AuditCommand $command */
$command = $application->find('audit');
$command->setRewriteConfigFile(false);
$commandTester = new CommandTester($command);
$commandTester->execute(['command' => $command->getName(), 'config file' => __DIR__ . '/config/audit.json']);
$this->assertSame(0, $commandTester->getStatusCode());
// Reconnect to DB.
AuditDataLayer::connect('localhost', 'test', 'test', self::$dataSchema);
$table1_data = AuditDataLayer::getTableOptions('test_data', 'TABLE1');
$table1_audit = AuditDataLayer::getTableOptions('test_audit', 'TABLE1');
$this->assertEquals($table1_data, $table1_audit, 'TABLE1');
$table1_data = AuditDataLayer::getTableOptions('test_data', 'TABLE2');
$table1_audit = AuditDataLayer::getTableOptions('test_audit', 'TABLE2');
$this->assertEquals($table1_data, $table1_audit, 'TABLE2');
$table1_data = AuditDataLayer::getTableOptions('test_data', 'TABLE3');
$table1_audit = AuditDataLayer::getTableOptions('test_audit', 'TABLE3');
$this->assertEquals($table1_data, $table1_audit, 'TABLE3');
}
/**
* Getting list of all tables from information_schema of database from config file.
*/
public function listOfTables()
{
$this->dataSchemaTables = AuditDataLayer::getTablesNames($this->config['database']['data_schema']);
$this->auditSchemaTables = AuditDataLayer::getTablesNames($this->config['database']['audit_schema']);
}
/**
* 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;
}
private function runAudit()
{
$application = new Application();
$application->add(new AuditCommand());
/** @var AuditCommand $command */
$command = $application->find('audit');
$command->setRewriteConfigFile(false);
$commandTester = new CommandTester($command);
$commandTester->execute(['command' => $command->getName(), 'config file' => __DIR__ . '/config/audit.json']);
// Reconnect to MySQL.
StaticDataLayer::disconnect();
StaticDataLayer::connect('localhost', 'test', 'test', self::$dataSchema);
AuditDataLayer::connect('localhost', 'test', 'test', self::$dataSchema);
}
/**
* 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');
}
}
/**
* Releases all table locks.
*/
private function unlockTables()
{
AuditDataLayer::unlockTables();
}
