/** * Get column collection with primary key columns * * @return ColumnCollection */ public function isPrimaryKey() { $columns = new ColumnCollection(); foreach ($this->collection as $column) { if ($column->isPrimaryKey() === true) { $columns->add($column); } } return $columns; }
/** * @covers ::isPrimaryKey */ public function testIsPrimaryKey() { $columns = new ColumnCollection(); $this->assertSame(0, $columns->isPrimaryKey()->count()); $columns->add(new Column(['Field' => null, 'Type' => null, 'Collation' => null, 'Null' => null, 'Key' => null, 'Default' => null, 'Extra' => null])); $this->assertSame(0, $columns->isPrimaryKey()->count()); $columns->add(new Column(['Field' => 'foo', 'Type' => null, 'Collation' => null, 'Null' => null, 'Key' => 'PRI', 'Default' => null, 'Extra' => null])); $this->assertSame(1, $columns->isPrimaryKey()->count()); }
/** * Create table * * @param string $tableName Table name * @param \PDO $pdo Database connection * * @return Table */ public static function create($tableName, \PDO $pdo) { /** * Table */ $result = $pdo->query("SHOW TABLE STATUS LIKE '{$tableName}';"); if ($result->rowCount() !== 1) { throw new \InvalidArgumentException(sprintf('Table %s was not found', $tableName)); } $tableRow = $result->fetch(); /** * Columns */ $columns = new ColumnCollection(); foreach ($pdo->query("SHOW FULL COLUMNS FROM `{$tableName}`;") as $row) { $column = new Column($row); $columns->add($column); } /** * Indices */ $indexRows = []; foreach ($pdo->query("SHOW INDEX FROM `{$tableName}`;") as $row) { $indexRows[$row['Key_name']]['unique'] = $row['Non_unique'] === '0'; $indexRows[$row['Key_name']]['columnNames'][] = $row['Column_name']; } $indices = new IndexCollection(); foreach ($indexRows as $keyName => $indexArray) { $index = new Index($keyName, $indexArray['unique'], $indexArray['columnNames']); $indices->add($index); } /** * Initialization */ $table = new Table($tableName, $tableRow['Collation'], $columns, $indices); return $table; }
/** * Check for redundant indices on primary key column * * @param ColumnCollection $columns Table columns * @param IndexCollection $indices Table indices * * @return array Result */ public function checkRedundantIndicesOnPrimaryKey(ColumnCollection $columns, IndexCollection $indices) { $result = []; /** * Check primary key columns */ foreach ($columns->isPrimaryKey() as $column) { $colums = [$column->getField()]; /** * Check non primary key indices */ foreach ($indices->isNotPrimaryKey() as $index) { /** * Check indices with just our primary key column */ if ($index->isColumnsEqual($colums) === true) { $indexType = $index->isUnique() === true ? 'unique' : 'key'; /** * Check if index is unique */ $result[] = ['type' => 'index', 'key' => $index->getKeyName(), 'description' => sprintf('An %s index on the primary key column is redundant', $indexType)]; } } } return $result; }
/** * @covers ::checkCollationMismatchBetweenTableAndColumns */ public function testCheckCollationMismatchBetweenTableAndColumns() { $columns = new Table\ColumnCollection(); $columns->add(new Table\Column(['Field' => 'username', 'Type' => null, 'Collation' => 'utf8_danish_ci', 'Null' => null, 'Key' => null, 'Default' => null, 'Extra' => null])); $columns->add(new Table\Column(['Field' => 'first_name', 'Type' => null, 'Collation' => 'utf8_danish_ci', 'Null' => null, 'Key' => null, 'Default' => null, 'Extra' => null])); $columns->add(new Table\Column(['Field' => 'last_name', 'Type' => null, 'Collation' => 'utf8_unicode_ci', 'Null' => null, 'Key' => null, 'Default' => null, 'Extra' => null])); /** * Table */ $table = new Table(null, 'utf8_unicode_ci', new Table\ColumnCollection(), new Table\IndexCollection()); $result = $table->checkCollationMismatchBetweenTableAndColumns($columns); $expected = [['type' => 'column', 'key' => 'username', 'description' => 'Column is not using same collation as table'], ['type' => 'column', 'key' => 'first_name', 'description' => 'Column is not using same collation as table']]; $this->assertSame($expected, $result); }