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