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