protected function createTaggingTable()
{
$table = $this->getTable();
$database = $table->getDatabase();
$pks = $this->getTable()->getPrimaryKey();
if (count($pks) > 1) {
throw new EngineException('The Taggable behavior does not support tables with composite primary keys');
}
$taggingTableName = $this->getTaggingTableName();
if ($database->hasTable($taggingTableName)) {
$this->taggingTable = $database->getTable($taggingTableName);
} else {
$this->taggingTable = $database->addTable(array('name' => $taggingTableName, 'phpName' => $this->replaceTokens($this->getParameter('tagging_table_phpname')), 'package' => $table->getPackage(), 'schema' => $table->getSchema(), 'namespace' => '\\' . $table->getNamespace()));
// every behavior adding a table should re-execute database behaviors
// see bug 2188 http://www.propelorm.org/changeset/2188
foreach ($database->getBehaviors() as $behavior) {
$behavior->modifyDatabase();
}
}
if ($this->taggingTable->hasColumn('tag_id')) {
$tagFkColumn = $this->taggingTable->getColumn('tag_id');
} else {
$tagFkColumn = $this->taggingTable->addColumn(array('name' => 'tag_id', 'type' => PropelTypes::INTEGER, 'primaryKey' => 'true'));
}
if ($this->taggingTable->hasColumn($table->getName() . '_id')) {
$objFkColumn = $this->taggingTable->getColumn($table->getName() . '_id');
} else {
$objFkColumn = $this->taggingTable->addColumn(array('name' => $table->getName() . '_id', 'type' => PropelTypes::INTEGER, 'primaryKey' => 'true'));
}
$this->taggingTable->setIsCrossRef(true);
$fkTag = new ForeignKey();
$fkTag->setForeignTableCommonName($this->tagTable->getCommonName());
$fkTag->setForeignSchemaName($this->tagTable->getSchema());
$fkTag->setOnDelete(ForeignKey::CASCADE);
$fkTag->setOnUpdate(ForeignKey::CASCADE);
$tagColumn = $this->tagTable->getColumn('id');
$fkTag->addReference($tagFkColumn->getName(), $tagColumn->getName());
$this->taggingTable->addForeignKey($fkTag);
$fkObj = new ForeignKey();
$fkObj->setForeignTableCommonName($this->getTable()->getCommonName());
$fkObj->setForeignSchemaName($this->getTable()->getSchema());
$fkObj->setOnDelete(ForeignKey::CASCADE);
$fkObj->setOnUpdate(ForeignKey::CASCADE);
foreach ($pks as $column) {
$fkObj->addReference($objFkColumn->getName(), $column->getName());
}
$this->taggingTable->addForeignKey($fkObj);
}
/**
* Returns whether this foreign key uses a required column, or a list of
* required columns.
*
* @return boolean
*/
public function isLocalColumnsRequired()
{
foreach ($this->localColumns as $columnName) {
if (!$this->parentTable->getColumn($columnName)->isNotNull()) {
return false;
}
}
return true;
}
public function getReferrerVersionsColumn(ForeignKey $fk)
{
$fkTableName = $fk->getTable()->getName();
if ($fk->isLocalPrimaryKey()) {
$fkColumnName = $fkTableName . '_version';
} else {
$fkColumnName = $fkTableName . '_versions';
}
return $this->versionTable->getColumn($fkColumnName);
}
/**
* @param Table $old
* @param Table $new
* @return bool|string
*/
public static function computeAlter(Table $old, Table $new)
{
$oldColumnNames = array_keys($old->getColumns());
$newColumnNames = array_keys($new->getColumns());
$alterLines = [];
// Check for changed columns
$possiblyChangedColumnNames = array_intersect($oldColumnNames, $newColumnNames);
foreach ($possiblyChangedColumnNames as $possiblyChangedColumnName) {
$oldColumn = $old->getColumn($possiblyChangedColumnName);
$newColumn = $new->getColumn($possiblyChangedColumnName);
if ($oldColumn && $newColumn) {
$alter = Column::computeAlter($oldColumn, $newColumn);
if ($alter) {
$alterLines[] = $alter;
}
}
}
// check if columns have been added
$addedColumnNames = array_diff($newColumnNames, $oldColumnNames);
foreach ($addedColumnNames as $added) {
$column = $new->getColumn($added);
$query = "ADD COLUMN " . $column;
if ($column->isFirst()) {
$query .= " FIRST";
} elseif ($column->getAfter() !== false) {
$query .= " AFTER `" . $column->getAfter() . "`";
}
$alterLines[] = $query;
}
// check if columns have been removed
$removedColumnNames = array_diff($oldColumnNames, $newColumnNames);
foreach ($removedColumnNames as $removedColumnName) {
$alterLines[] = "DROP COLUMN `" . $removedColumnName . "`";
}
if (!empty($alterLines)) {
return "ALTER TABLE `" . $new->getName() . "`\n\t" . implode(",\n\t", $alterLines);
}
return false;
}
/**
* Loads the primary key for this table.
*/
protected function addPrimaryKey(Table $table, $oid, $version)
{
$stmt = $this->dbh->prepare("SELECT\n\t\t\t\t\t\t\t\t\t\tDISTINCT ON(cls.relname)\n\t\t\t\t\t\t\t\t\t\tcls.relname as idxname,\n\t\t\t\t\t\t\t\t\t\tindkey,\n\t\t\t\t\t\t\t\t\t\tindisunique\n\t\t\t\t\t\t\t\t\tFROM pg_index idx\n\t\t\t\t\t\t\t\t\t\tJOIN pg_class cls ON cls.oid=indexrelid\n\t\t\t\t\t\t\t\t\tWHERE indrelid = ? AND indisprimary\n\t\t\t\t\t\t\t\t\tORDER BY cls.relname");
$stmt->bindValue(1, $oid);
$stmt->execute();
// Loop through the returned results, grouping the same key_name together
// adding each column for that key.
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$arrColumns = explode(' ', $row['indkey']);
foreach ($arrColumns as $intColNum) {
$stmt2 = $this->dbh->prepare("SELECT a.attname\n\t\t\t\t\t\t\t\t\t\t\t\tFROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE c.oid = ? AND a.attnum = ? AND NOT a.attisdropped\n\t\t\t\t\t\t\t\t\t\t\t\tORDER BY a.attnum");
$stmt2->bindValue(1, $oid);
$stmt2->bindValue(2, $intColNum);
$stmt2->execute();
$row2 = $stmt2->fetch(PDO::FETCH_ASSOC);
$table->getColumn($row2['attname'])->setPrimaryKey(true);
}
// foreach ($arrColumns as $intColNum)
}
}
/**
* 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;
}
/**
* Loads the primary key for this table.
*/
protected function addPrimaryKey(Table $table)
{
$stmt = $this->dbh->query("SHOW KEYS FROM `" . $table->getName() . "`");
// Loop through the returned results, grouping the same key_name together
// adding each column for that key.
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// Skip any non-primary keys.
if ($row['Key_name'] !== 'PRIMARY') {
continue;
}
$name = $row["Column_name"];
$table->getColumn($name)->setPrimaryKey(true);
}
}
/**
* Loads the primary key for this table.
*
* @param Table $table The Table model class to add PK to.
*/
protected function addPrimaryKey(Table $table)
{
$stmt = $this->dbh->query("SELECT COLS.COLUMN_NAME FROM USER_CONSTRAINTS CONS, USER_CONS_COLUMNS COLS WHERE CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME AND CONS.TABLE_NAME = '" . $table->getName() . "' AND CONS.CONSTRAINT_TYPE = 'P'");
/* @var stmt PDOStatement */
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// This fixes a strange behavior by PDO. Sometimes the
// row values are inside an index 0 of an array
if (array_key_exists(0, $row)) {
$row = $row[0];
}
$table->getColumn($row['COLUMN_NAME'])->setPrimaryKey(true);
}
}
/**
* Loads the primary key for this table.
*/
protected function addPrimaryKey(Table $table)
{
$stmt = $this->dbh->query("SELECT COLUMN_NAME\n FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS\n INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ON\n INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.constraint_name\n WHERE (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY') AND\n (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = '" . $table->getName() . "')");
// Loop through the returned results, grouping the same key_name together
// adding each column for that key.
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
$name = $row[0];
$table->getColumn($name)->setPrimaryKey(true);
}
}
/**
* Load indexes for this table
*/
protected function addIndexes(Table $table)
{
$stmt = $this->dbh->query("PRAGMA index_list('" . $table->getName() . "')");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$name = $row['name'];
$index = new Index($name);
$stmt2 = $this->dbh->query("PRAGMA index_info('" . $name . "')");
while ($row2 = $stmt2->fetch(PDO::FETCH_ASSOC)) {
$colname = $row2['name'];
$index->addColumn($table->getColumn($colname));
}
$table->addIndex($index);
}
}
/**
* Loads the primary key for this table.
*/
protected function addPrimaryKey(Table $table)
{
$q = "SELECT COLUMN_NAME\n\t\t\t\t\t\tFROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS\n\t\t\t\t\t\t\t\tINNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ON\n\t\t\t\t\t\tINFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.constraint_name\n\t\t\t\t\t\tWHERE (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY') AND\n\t\t\t\t\t\t(INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = '" . $table->getName() . "')\nUNION ALL\nSELECT COLUMN_NAME\nFROM INFORMATION_SCHEMA.COLUMNS\nWHERE\n(TABLE_NAME = '" . $table->getName() . "')\nAND columnproperty(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1\n";
$stmt = $this->dbh->query($q);
// Loop through the returned results, grouping the same key_name together
// adding each column for that key.
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
$name = $row[0];
$table->getColumn($name)->setPrimaryKey(true);
}
}
/**
* Loads the primary key for this table.
*/
protected function addPrimaryKey(Table $table)
{
$stmt = $this->dbh->query("SELECT COL.COLUMN_NAME FROM USER_CONS_COLUMNS COL, USER_CONSTRAINTS CON WHERE COL.TABLE_NAME = '" . $table->getName() . "' AND COL.TABLE_NAME = CON.TABLE_NAME AND COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME AND CON.CONSTRAINT_TYPE = 'P'");
// Loop through the returned results, grouping the same key_name together
// adding each column for that key.
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$table->getColumn($row['COLUMN_NAME'])->setPrimaryKey(true);
}
}
