public function getTable($table)
{
$query = 'SELECT
t.TABLE_COMMENT AS tableComment,
c.COLUMN_NAME AS columnName,
c.COLUMN_TYPE AS type,
c.COLUMN_DEFAULT AS columnDefault,
c.IS_NULLABLE AS columnNullable,
c.EXTRA AS extra,
c.COLUMN_COMMENT AS columnComment,
k.CONSTRAINT_NAME AS constraintName,
k.REFERENCED_TABLE_NAME AS referencedTable,
k.REFERENCED_COLUMN_NAME AS referencedColumn,
k.UPDATE_RULE AS onUpdate,
k.DELETE_RULE AS onDelete,
NULL AS indexName,
NULL AS indexColumn,
NULL indexNonUnique
FROM information_schema.TABLES AS t JOIN information_schema.COLUMNS AS c ON t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN
(
SELECT
ku.COLUMN_NAME,
ku.CONSTRAINT_NAME,
ku.REFERENCED_TABLE_NAME,
ku.REFERENCED_COLUMN_NAME,
r.UPDATE_RULE,
r.DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS AS r JOIN information_schema.KEY_COLUMN_USAGE AS ku
ON ku.CONSTRAINT_NAME = r.CONSTRAINT_NAME AND ku.TABLE_NAME = r.TABLE_NAME
WHERE ku.TABLE_NAME=:table AND r.CONSTRAINT_SCHEMA = :id AND ku.CONSTRAINT_SCHEMA = :id
) AS k
ON c.COLUMN_NAME = k.COLUMN_NAME
WHERE t.TABLE_SCHEMA = :id AND c.TABLE_SCHEMA = :id AND t.TABLE_NAME = :table AND c.TABLE_NAME = :table
UNION
SELECT
t.TABLE_COMMENT AS tableComment,
NULL AS columnName,
NULL AS type,
NULL AS columnDefault,
NULL AS columnNullable,
NULL AS extra,
NULL AS columnComment,
NULL AS constraintName,
NULL AS referencedTable,
NULL AS referencedColumn,
NULL AS onUpdate,
NULL AS onDelete,
st.INDEX_NAME AS indexName,
st.COLUMN_NAME AS indexColumn,
st.NON_UNIQUE AS indexNonUnique
FROM information_schema.TABLES AS t JOIN information_schema.STATISTICS AS st ON t.TABLE_SCHEMA= st.TABLE_SCHEMA AND t.TABLE_NAME = st.TABLE_NAME
WHERE t.TABLE_SCHEMA = :id AND st.TABLE_SCHEMA = :id AND t.TABLE_NAME = :table AND st.TABLE_NAME = :table';
$fields = $this->pdoDriver->executeQuery($query, array('id' => $this->pdoDriver->getID(), 'table' => $this->addPrefix($table)), true);
if (count($fields) == 0) {
return null;
}
$res = new Table($table, $fields[0]['tableComment']);
// initialize constraints
$currentForeignKeyName = null;
$currentForeignKey = null;
$currentIndexName = null;
$currentIndex = null;
foreach ($fields as $field) {
$name = $field['columnName'];
// new index
if ($field['indexName'] !== $currentIndexName) {
// add old index to table
if ($currentIndex != null) {
if ($currentIndexName === 'PRIMARY') {
$res->setPrimaryKey($currentIndex);
} else {
$res->addConstraint($currentIndex);
}
}
// create a new index
$currentIndexName = $field['indexName'];
if ($currentIndexName === 'PRIMARY') {
$currentIndex = new PrimaryKey($table);
} elseif ($currentIndexName !== null) {
if ($field['indexNonUnique'] === '0') {
$currentIndex = new Index($currentIndexName, $table, true);
} else {
$currentIndex = new Index($currentIndexName, $table, false);
}
}
}
// Index definition
if ($name === null) {
// Add field to current index
assert($currentIndex !== null);
$currentIndex->addField($field['indexColumn']);
} else {
// New foreign key
if ($field['constraintName'] !== $currentForeignKeyName) {
// Add old foreign key to table
if ($currentForeignKey !== null) {
$res->addForeignKey($currentForeignKey);
}
// Create a new foreign key
$currentForeignKeyName = $field['constraintName'];
if ($currentForeignKeyName === null) {
$currentForeignKey = null;
} else {
assert(array_key_exists($field['onDelete'], $this->onDeleteActionMapping));
$currentForeignKey = new ForeignKey($currentForeignKeyName, $table, $this->removePrefix($field['referencedTable']), array(), $this->onDeleteActionMapping[$field['onDelete']]);
}
}
// Add foreign key fields
if ($currentForeignKeyName !== null) {
assert($currentForeignKey !== null);
$currentForeignKey->addReference($name, $field['referencedColumn']);
}
$res->addField($this->buildField($name, $field['type'], $field['columnDefault'], $field['columnNullable'], $field['extra'], $field['columnComment']));
}
// Save last indices
if ($currentIndex !== null) {
$res->addConstraint($currentIndex);
}
if ($currentForeignKey !== null) {
$res->addConstraint($currentForeignKey);
}
}
return $res;
}
/**
* Tests the foreign key on delete action 'set null'.
*
* This action sets the referencing value to NULL if the referenced value is deleted.
*
* @depends testCreateTable
*
* @param DB[] $dbs the database objects to work on
*/
public function testForeignKeyOnDeleteSetNull($dbs)
{
foreach ($dbs as $db) {
/* @var $db DB */
// create field and index in referenced table ('test')
$field = new Field('intfk', new Type(Field::TYPE_INT));
$db->addField('test', $field);
$db->addIndex('test', new Index('idxxx', 'test', true, array($field->getName())));
$action = ForeignKey::ACTION_SETNULL;
// create referencing table and field
$table = new Table('testfk');
$table->addField($field);
$table->addField(new Field('id', Field::TYPE_INT));
$foreignKey = new ForeignKey('testfk_ibfk_1', 'testfk', 'test', array(array('field' => $field->getName(), 'referencedField' => $field->getName())), $action);
$table->addForeignKey($foreignKey);
$db->createTable($table);
// add a value to referenced table
$value = 42;
$db->execute('INSERT INTO :::test (intfk) VALUES(:value)', array(':::test' => 'test', ':value' => $value));
$db->execute('INSERT INTO :::testfk (id, intfk) VALUES(:id, :value)', array(':::testfk' => 'testfk', ':id' => 0, ':value' => $value));
// delete the referenced value
$db->execute('DELETE FROM :::test WHERE intfk=:value', array(':::test' => 'test', ':value' => $value));
// perform test
$countRes = $db->fetch('SELECT count(intfk) AS num FROM :::test WHERE intfk=:value', array(':::test' => 'test', ':value' => $value));
$this->assertSame(1, count($countRes));
$this->assertSame(0, (int) $countRes[0]['num']);
$countRes = $db->fetch('SELECT intfk FROM :::testfk WHERE id=:id', array(':::testfk' => 'testfk', ':id' => 0));
$this->assertSame(1, count($countRes));
$this->assertSame(null, $countRes[0]['intfk']);
// revert changes
$db->dropTable('testfk');
$db->dropField('test', $field->getName());
}
}
