/**
* Get index collection with non primary key indices
*
* @return IndexCollection
*/
public function isNotPrimaryKey()
{
$indices = new IndexCollection();
foreach ($this->collection as $index) {
if ($index->isPrimaryKey() === false) {
$indices->add($index);
}
}
return $indices;
}
/**
* @covers ::isNotPrimaryKey
*/
public function testIsNotPrimaryKey()
{
$indices = new IndexCollection();
$this->assertSame(0, $indices->isNotPrimaryKey()->count());
$indices->add(new Index('PRIMARY', null, null));
$this->assertSame(0, $indices->isNotPrimaryKey()->count());
$indices->add(new Index(null, null, null));
$this->assertSame(1, $indices->isNotPrimaryKey()->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 ::checkRedundantIndicesOnPrimaryKey
*/
public function testCheckRedundantIndicesOnPrimaryKey()
{
/**
* Column
*/
$column = new Table\Column(['Field' => 'id', 'Type' => 'int(10) unsigned', 'Collation' => '', 'Null' => 'NO', 'Key' => 'PRI', 'Default' => '', 'Extra' => 'auto_increment']);
$columns = new Table\ColumnCollection();
$columns->add($column);
/**
* Indicies
*/
$indices = new Table\IndexCollection();
$indices->add(new Table\Index('PRIMARY', true, ['id']));
$indices->add(new Table\Index('unique', true, ['id']));
$indices->add(new Table\Index('key', false, ['id']));
/**
* Table
*/
$table = new Table(null, null, new Table\ColumnCollection(), new Table\IndexCollection());
$result = $table->checkRedundantIndicesOnPrimaryKey($columns, $indices);
$expected = [['type' => 'index', 'key' => 'unique', 'description' => 'An unique index on the primary key column is redundant'], ['type' => 'index', 'key' => 'key', 'description' => 'An key index on the primary key column is redundant']];
$this->assertSame($expected, $result);
}
