/**
* {@inheritdoc}
*
*/
public static function setUpBeforeClass()
{
parent::setUpBeforeClass();
StaticDataLayer::disconnect();
StaticDataLayer::connect('localhost', 'test', 'test', self::$dataSchema);
StaticDataLayer::executeNone('alter table `test_data`.`TABLE1` engine=innodb');
}
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));
}
/**
* Selects all unique keys from table.
*
* @param string $schemaName The name of the table schema.
* @param string $tableName The name of the table.
*
* @return \array[]
*/
public static function getTableUniqueKeys($schemaName, $tableName)
{
$sql = sprintf('
SHOW INDEX FROM %s.%s
WHERE Non_unique = 0', $schemaName, $tableName);
return self::$dl->executeRows($sql);
}
/**
* Selects all triggers in a schema
*
* @param string $schemaName The name of the table schema.
*
* @return \array[]
*/
public static function getTriggers($schemaName)
{
$sql = sprintf('
select EVENT_OBJECT_TABLE as table_name
, TRIGGER_NAME as trigger_name
from information_schema.TRIGGERS
where TRIGGER_SCHEMA = %s
order by EVENT_OBJECT_TABLE
, TRIGGER_NAME', self::$dl->quoteString($schemaName));
return self::executeRows($sql);
}
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);
return $commandTester->getStatusCode();
}
/**
* Selects info about primary keys of a table in selected schema.
*
* @param string $schemaName The name of the schema.
* @param string $tableName The name of a table.
*
* @return array[]
*/
public static function getForeignKeys($schemaName, $tableName)
{
// Getting a constraint name for foreign key.
$sql = sprintf("\n SELECT CONSTRAINT_NAME AS 'constraint_name'\n FROM information_schema.TABLE_CONSTRAINTS\n WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY'\n AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = %s\n AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = %s", self::$dl->quoteString($schemaName), self::$dl->quoteString($tableName));
$constraint_names = self::executeRows($sql);
$field_names = [];
// Getting names of columns and tables of foreign keys.
foreach ($constraint_names as $constraint_name) {
$sql = sprintf("\n SELECT COLUMN_NAME AS 'column_name',\n TABLE_NAME AS 'table_name',\n REFERENCED_TABLE_NAME AS 'ref_table_name',\n REFERENCED_COLUMN_NAME AS 'ref_column_name'\n FROM information_schema.KEY_COLUMN_USAGE\n WHERE CONSTRAINT_NAME = %s", self::$dl->quoteString($constraint_name['constraint_name']));
$table_items = self::executeRows($sql);
$table_items[0]['constraint_name'] = $constraint_name['constraint_name'];
$field_names[] = $table_items;
}
return $field_names;
}
/**
* 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[] = ';';
}
/**
* Test that the table is actually locked.
*/
public function test02()
{
$application = new Application();
$application->add(new AuditCommand());
// Start process that inserts rows into TABLE1.
$pid = pcntl_fork();
if ($pid == 0) {
// Child process.
pcntl_exec(__DIR__ . '/config/generator.php');
}
// Parent process.
sleep(2);
/** @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']);
// Tell the generator it is time to stop.
posix_kill($pid, SIGUSR1);
$status = $commandTester->getStatusCode();
$this->assertSame(0, $status, 'status code');
pcntl_waitpid($pid, $status);
$this->assertEquals(0, $status);
// Reconnect to DB.
StaticDataLayer::connect('localhost', 'test', 'test', self::$dataSchema);
// It can take some time before that all rows generated by $generator are visible by this process.
$n1 = 0;
$n2 = 0;
sleep(5);
for ($i = 0; $i < 60; $i++) {
$n1 = StaticDataLayer::executeSingleton1("select AUTO_INCREMENT - 1 \n from information_schema.TABLES\n where TABLE_SCHEMA = 'test_data'\n and TABLE_NAME = 'TABLE1'");
$n2 = StaticDataLayer::executeSingleton1('select count(*) from test_audit.TABLE1');
if (4 * $n1 == $n2) {
break;
}
sleep(3);
}
$this->assertEquals(4 * $n1, $n2, 'count');
}
/**
* 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');
}
}
/**
* 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);
}
}
foreach ($files as $file) {
if (file_exists($file)) {
require $file;
break;
}
}
declare (ticks=1);
//----------------------------------------------------------------------------------------------------------------------
function signalHandler()
{
$GLOBALS['exit'] = true;
}
//----------------------------------------------------------------------------------------------------------------------
$GLOBALS['exit'] = false;
pcntl_signal(SIGUSR1, "signalHandler");
// Set error handler.
$handler = new ErrorHandler();
$handler->register();
StaticDataLayer::connect('localhost', 'test', 'test', 'test_data');
while (true) {
if ($GLOBALS['exit']) {
break;
}
StaticDataLayer::begin();
StaticDataLayer::executeNone('insert into TABLE1(c) values(1)');
StaticDataLayer::executeNone('update TABLE1 set c = 2');
StaticDataLayer::executeNone('delete from TABLE1 where c = 2');
StaticDataLayer::commit();
}
StaticDataLayer::disconnect();
/**
* Tests for quoteString.
*
* @expectedException RuntimeException
*/
public function testQuoteString4()
{
StaticDataLayer::quoteString(new StaticDataLayer());
}
protected function tearDown()
{
StaticDataLayer::disconnect();
}
/**
* 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;
}
/**
* 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;
}
}
}
/**
* Adds the "values" part of an insert SQL statement to SQL code for a trigger.
*
* @param string $rowState The row state (i.e. OLD or NEW).
*/
private function createInsertStatementValues($rowState)
{
$values = '';
// First the values for the audit columns.
foreach ($this->auditColumns->getColumns() as $column) {
$column = $column->getProperties();
if ($values) {
$values .= ',';
}
switch (true) {
case isset($column['value_type']):
switch ($column['value_type']) {
case 'ACTION':
$values .= StaticDataLayer::quoteString($this->triggerAction);
break;
case 'STATE':
$values .= StaticDataLayer::quoteString($rowState);
break;
default:
throw new FallenException('value_type', $column['value_type']);
}
break;
case isset($column['expression']):
$values .= $column['expression'];
break;
default:
throw new RuntimeException('None of value_type and expression are set.');
}
}
// Second the values for the audit columns.
foreach ($this->tableColumns->getColumns() as $column) {
if ($values) {
$values .= ',';
}
$values .= sprintf('%s.`%s`', $rowState, $column->getProperty('column_name'));
}
$this->code->append(sprintf('values(%s);', $values));
}
/**
* Connects to the MySQL server.
*/
public static function setUpBeforeClass()
{
parent::setUpBeforeClass();
StaticDataLayer::multiQuery(file_get_contents(__DIR__ . '/config/setup.sql'));
}
/**
* Sets the SQL mode.
*
* @param string $sqlMode The SQL mode.
*/
public static function setSqlMode($sqlMode)
{
$sql = sprintf('set sql_mode = %s', self::$dl->quoteString($sqlMode));
self::executeNone($sql);
}
/**
* Test total number of rows in audit table.
*/
public function test02d()
{
// Get all audit rows.
$sql = sprintf("\nselect * \nfrom `test_audit`.`AUT_COMPANY`");
$rows = StaticDataLayer::executeRows($sql);
// We expect 4 rows: 1 insert, 2 update, and 1 delete.
$this->assertEquals(4, count($rows));
}
/**
* 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;
}
/**
* 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;
}
