public function getDropTableDDL(Table $table)
{
$ret = '';
foreach ($table->getForeignKeys() as $fk) {
$ret .= "\nIF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='" . $fk->getName() . "')\n ALTER TABLE " . $this->quoteIdentifier($table->getName()) . " DROP CONSTRAINT " . $this->quoteIdentifier($fk->getName()) . ";\n";
}
self::$dropCount++;
$ret .= "\nIF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = '" . $table->getName() . "')\nBEGIN\n DECLARE @reftable_" . self::$dropCount . " nvarchar(60), @constraintname_" . self::$dropCount . " nvarchar(60)\n DECLARE refcursor CURSOR FOR\n select reftables.name tablename, cons.name constraintname\n from sysobjects tables,\n sysobjects reftables,\n sysobjects cons,\n sysreferences ref\n where tables.id = ref.rkeyid\n and cons.id = ref.constid\n and reftables.id = ref.fkeyid\n and tables.name = '" . $table->getName() . "'\n OPEN refcursor\n FETCH NEXT from refcursor into @reftable_" . self::$dropCount . ", @constraintname_" . self::$dropCount . "\n while @@FETCH_STATUS = 0\n BEGIN\n exec ('alter table '+@reftable_" . self::$dropCount . "+' drop constraint '+@constraintname_" . self::$dropCount . ")\n FETCH NEXT from refcursor into @reftable_" . self::$dropCount . ", @constraintname_" . self::$dropCount . "\n END\n CLOSE refcursor\n DEALLOCATE refcursor\n DROP TABLE " . $this->quoteIdentifier($table->getName()) . "\nEND\n";
return $ret;
}
/**
* Returns the list of other foreign keys starting on the same table.
* Used in many-to-many relationships.
*
* @return array
*/
public function getOtherFks()
{
$fks = [];
foreach ($this->parentTable->getForeignKeys() as $fk) {
if ($fk !== $this) {
$fks[] = $fk;
}
}
return $fks;
}
protected function validateTableAttributes(Table $table)
{
$reservedTableNames = array('table_name');
$tableName = strtolower($table->getName());
if (in_array($tableName, $reservedTableNames)) {
$this->errors[] = sprintf('Table "%s" uses a reserved keyword as name', $table->getName());
}
if ($table->getIsCrossRef()) {
$fkTables = array();
foreach ($table->getForeignKeys() as $fk) {
$foreignTableName = $fk->getForeignTableName();
if (isset($fkTables[$foreignTableName])) {
$this->errors[] = sprintf('Table "%s" implements an equal nest relationship for table "%s". This feature is not supported', $table->getName(), $foreignTableName);
break;
}
$fkTables[$foreignTableName] = true;
}
}
}
/**
* Get a record in the given table.
* This class is usually instantiated by Driver::getRecords(), which
* sets column values as properties.
* @param Table $table
* @param bool $exists Is this a real record, or a new one that we will probably insert later?
* @param array $data optional array of properties => values
*/
public function __construct(Table $table, $exists = true, $data = null)
{
$this->_meta['table'] = $table;
$this->_meta['exists'] = $exists;
$this->_meta['existed'] = $exists;
$this->_meta['driver'] = $table->getDriver();
if (isset($data)) {
foreach ($data as $key => $value) {
$this->{$key} = $value;
}
}
foreach ($table->getForeignKeys() as $localColumn => $foreignKey) {
if (property_exists($this, $localColumn)) {
$this->_meta['foreignRecords'][$localColumn] = $this->{$localColumn};
// unset the property, so that the magic __getter will be invoked
unset($this->{$localColumn});
} else {
$this->_meta['foreignRecords'][$localColumn] = null;
}
}
}
/**
* Returns the difference between the tables $table1 and $table2.
*
* If there are no differences this method returns the boolean false.
*
* @param Table $table1
* @param Table $table2
*
* @return bool|TableDiff
*/
public function diffTable(Table $table1, Table $table2)
{
$changes = 0;
$tableDifferences = new TableDiff($table1->getName());
$table1Columns = $table1->getColumns();
$table2Columns = $table2->getColumns();
/* See if all the fields in table 1 exist in table 2 */
foreach ($table2Columns as $columnName => $column) {
if (!$table1->hasColumn($columnName)) {
$tableDifferences->addedColumns[$columnName] = $column;
$changes++;
}
}
/* See if there are any removed fields in table 2 */
foreach ($table1Columns as $columnName => $column) {
if (!$table2->hasColumn($columnName)) {
$tableDifferences->removedColumns[$columnName] = $column;
$changes++;
}
}
foreach ($table1Columns as $columnName => $column) {
if ($table2->hasColumn($columnName)) {
$changedProperties = $this->diffColumn($column, $table2->getColumn($columnName));
if (count($changedProperties)) {
$columnDiff = new ColumnDiff($column->getName(), $table2->getColumn($columnName), $changedProperties);
$tableDifferences->changedColumns[$column->getName()] = $columnDiff;
$changes++;
}
}
}
$this->detectColumnRenamings($tableDifferences);
$table1Indexes = $table1->getIndexes();
$table2Indexes = $table2->getIndexes();
foreach ($table2Indexes as $index2Name => $index2Definition) {
foreach ($table1Indexes as $index1Name => $index1Definition) {
if ($this->diffIndex($index1Definition, $index2Definition) === false) {
unset($table1Indexes[$index1Name]);
unset($table2Indexes[$index2Name]);
} else {
if ($index1Name == $index2Name) {
$tableDifferences->changedIndexes[$index2Name] = $table2Indexes[$index2Name];
unset($table1Indexes[$index1Name]);
unset($table2Indexes[$index2Name]);
$changes++;
}
}
}
}
foreach ($table1Indexes as $index1Name => $index1Definition) {
$tableDifferences->removedIndexes[$index1Name] = $index1Definition;
$changes++;
}
foreach ($table2Indexes as $index2Name => $index2Definition) {
$tableDifferences->addedIndexes[$index2Name] = $index2Definition;
$changes++;
}
$fromFkeys = $table1->getForeignKeys();
$toFkeys = $table2->getForeignKeys();
foreach ($fromFkeys as $key1 => $constraint1) {
foreach ($toFkeys as $key2 => $constraint2) {
if ($this->diffForeignKey($constraint1, $constraint2) === false) {
unset($fromFkeys[$key1]);
unset($toFkeys[$key2]);
} else {
if (strtolower($constraint1->getName()) == strtolower($constraint2->getName())) {
$tableDifferences->changedForeignKeys[] = $constraint2;
$changes++;
unset($fromFkeys[$key1]);
unset($toFkeys[$key2]);
}
}
}
}
foreach ($fromFkeys as $key1 => $constraint1) {
$tableDifferences->removedForeignKeys[] = $constraint1;
$changes++;
}
foreach ($toFkeys as $key2 => $constraint2) {
$tableDifferences->addedForeignKeys[] = $constraint2;
$changes++;
}
return $changes ? $tableDifferences : false;
}
/**
* Builds the DDL SQL to add the foreign keys of a table.
*
* @param Table $table
* @return string
*/
public function getAddForeignKeysDDL(Table $table)
{
$ret = '';
foreach ($table->getForeignKeys() as $fk) {
$ret .= $this->getAddForeignKeyDDL($fk);
}
return $ret;
}
