/**
* Drops all tables in test_data and test_audit.
*/
protected static function dropAllTables()
{
$sql = "\nselect TABLE_SCHEMA as table_schema\n, TABLE_NAME as table_name\nfrom information_schema.TABLES\nwhere TABLE_SCHEMA in (%s,%s)";
$sql = sprintf($sql, StaticDataLayer::quoteString(self::$dataSchema), StaticDataLayer::quoteString(self::$auditSchema));
$tables = StaticDataLayer::executeRows($sql);
foreach ($tables as $table) {
$sql = "drop table `%s`.`%s`";
$sql = sprintf($sql, $table['table_schema'], $table['table_name']);
StaticDataLayer::executeNone($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);
}
/**
* Selects metadata of all columns of table.
*
* @param string $schemaName The name of the table schema.
* @param string $tableName The name of the table.
*
* @return \array[]
*/
public static function getTableColumns($schemaName, $tableName)
{
$sql = sprintf('
select COLUMN_NAME as column_name
, COLUMN_TYPE as column_type
, IS_NULLABLE as is_nullable
, CHARACTER_SET_NAME as character_set_name
, COLLATION_NAME as collation_name
, EXTRA as extra
from information_schema.COLUMNS
where TABLE_SCHEMA = %s
and TABLE_NAME = %s
order by ORDINAL_POSITION', self::$dl->quoteString($schemaName), self::$dl->quoteString($tableName));
return self::$dl->executeRows($sql);
}
/**
* 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;
}
/**
* 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 delete trigger is working correctly.
*/
public function test02c()
{
StaticDataLayer::query("SET time_zone = 'Europe/Amsterdam'");
// Delete a row from AUT_COMPANY.
$sql = sprintf('
delete from `AUT_COMPANY`
where `cmp_abbr` = %s', StaticDataLayer::quoteString('SYS'));
StaticDataLayer::executeNone($sql);
// Get audit rows.
$sql = sprintf("\nselect * \nfrom `test_audit`.`AUT_COMPANY`\nwhere audit_statement = 'DELETE'");
$rows = StaticDataLayer::executeRows($sql);
// We expect 1 row.
$this->assertEquals(1, count($rows));
$row = $rows[0];
// Tests on fields.
$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->assertEquals(3, $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']);
}
/**
* 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));
}
/**
* Tests for quoteString.
*
* @expectedException RuntimeException
*/
public function testQuoteString4()
{
StaticDataLayer::quoteString(new StaticDataLayer());
}
