/** * 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()); }