/** * {@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; }