/** * 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); }
public function test01() { // Run audit. $this->runAudit(); // TABLE1 MUST exist. $tables = $this->getAuditTables(); $this->assertNotNull(StaticDataLayer::searchInRowSet('table_name', 'TABLE1', $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)); $actual = $this->getTableColumns(self::$auditSchema, 'TABLE1'); $expected = []; $expected[] = ['column_name' => 'c1', 'column_type' => 'tinyint(4)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null]; $expected[] = ['column_name' => 'c2', 'column_type' => 'smallint(6)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null]; $expected[] = ['column_name' => 'c3', 'column_type' => 'mediumint(9)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null]; $expected[] = ['column_name' => 'c4', 'column_type' => 'int(11)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null]; $this->assertSame($expected, $actual); // Test triggers. StaticDataLayer::query('insert into `TABLE1`(c1, c2, c3, c4) values(1, 2, 3, 4)'); StaticDataLayer::query('update `TABLE1` set c1=10, c2=20, c3=30, c4=40'); StaticDataLayer::query('delete from `TABLE1`'); $rows = StaticDataLayer::executeRows(sprintf('select * from `%s`.`TABLE1` where c3 is not null', self::$auditSchema)); $this->assertSame(4, count($rows)); // Drop column c3. StaticDataLayer::multiQuery(file_get_contents(__DIR__ . '/config/drop_column.sql')); $this->runAudit(); // TABLE1 MUST exist. $tables = $this->getAuditTables(); $this->assertNotNull(StaticDataLayer::searchInRowSet('table_name', 'TABLE1', $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)); // TABLE1 must have column c3. $actual = $this->getTableColumns(self::$auditSchema, 'TABLE1'); $expected = []; $expected[] = ['column_name' => 'c1', 'column_type' => 'tinyint(4)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null]; $expected[] = ['column_name' => 'c2', 'column_type' => 'smallint(6)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null]; $expected[] = ['column_name' => 'c3', 'column_type' => 'mediumint(9)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null]; $expected[] = ['column_name' => 'c4', 'column_type' => 'int(11)', 'is_nullable' => 'YES', 'character_set_name' => null, 'collation_name' => null]; $this->assertSame($expected, $actual); // Test triggers. StaticDataLayer::query('insert into `TABLE1`(c1, c2, c4) values(1, 2, 4)'); StaticDataLayer::query('update `TABLE1` set c1=10, c2=20, c4=40'); StaticDataLayer::query('delete from `TABLE1`'); // Assert we 4 rows with c3 is null. $rows = StaticDataLayer::executeRows(sprintf('select * from `%s`.`TABLE1` where c3 is null', self::$auditSchema)); $this->assertSame(4, count($rows)); // Assert we 8 rows in total. $rows = StaticDataLayer::executeRows(sprintf('select * from `%s`.`TABLE1`', self::$auditSchema)); $this->assertSame(8, count($rows)); }
/** * 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); } }
/** * Executes a query that returns 0 or more rows. * * @param string $query The SQL statement. * * @return \array[] */ public static function executeRows($query) { self::logQuery($query); return self::$dl->executeRows($query); }
/** * 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)); }