/**
* Adds a unique constraint to the table to enforce uniqueness of the slug_column
*
* @param Table $table
*/
protected function addUniqueConstraint(Table $table)
{
$unique = new Unique($this->getColumnForParameter('slug_column'));
$unique->setName($table->getCommonName() . '_slug');
$unique->addColumn($table->getColumn($this->getParameter('slug_column')));
if ($this->getParameter('scope_column')) {
$unique->addColumn($table->getColumn($this->getParameter('scope_column')));
}
$table->addUnique($unique);
}
protected function addClosureColumn($name, Table $ct_table, Column $column)
{
$table = $this->getTable();
$id_fieldname = $column->getName();
$domain = $column->getDomain();
if (!$ct_table->hasColumn($name)) {
$column = new Column($name);
$column->setDomain($domain);
$column->setPrimaryKey(true);
$ct_table->addColumn($column);
} else {
$column = $ct_table->getColumn($name);
}
$ct_tablename_normalized = str_replace('_', '', $ct_table->getName());
$fk_name = $ct_tablename_normalized . '_' . $name . '_fk';
if (!$ct_table->getColumnForeignKeys($name)) {
$column_fk = new ForeignKey($fk_name);
$column_fk->addReference($name, $table->getColumn($id_fieldname)->getName());
$column_fk->setForeignTableCommonName($table->getName());
$column_fk->setOnUpdate('cascade');
$column_fk->setOnDelete('restrict');
$ct_table->addForeignKey($column_fk);
}
$column_idx_name = $fk_name . '_idx';
if (!$ct_table->hasIndex($column_idx_name)) {
$column_idx = new Index($column_idx_name);
$column_idx->addColumn(['name' => $column->getName()]);
$ct_table->addIndex($column_idx);
}
}
/**
* Returns whether this foreign key uses at least one required local column.
*
* @return boolean
*/
public function isAtLeastOneLocalColumnRequired()
{
foreach ($this->localColumns as $columnName) {
if ($this->parentTable->getColumn($columnName)->isNotNull()) {
return true;
}
}
return false;
}
/**
* Unfortunately, SQLite does not support composite pks where one is AUTOINCREMENT,
* so we have to flag both as NOT NULL and create a UNIQUE constraint.
*
* @param Table $table
*/
public function normalizeTable(Table $table)
{
if (count($pks = $table->getPrimaryKey()) > 1 && $table->hasAutoIncrementPrimaryKey()) {
foreach ($pks as $pk) {
//no pk can be NULL, as usual
$pk->setNotNull(true);
//in SQLite the column with the AUTOINCREMENT MUST be a primary key, too.
if (!$pk->isAutoIncrement()) {
//for all other sub keys we remove it, since we create a UNIQUE constraint over all primary keys.
$pk->setPrimaryKey(false);
}
}
//search if there is already a UNIQUE constraint over the primary keys
$pkUniqueExist = false;
foreach ($table->getUnices() as $unique) {
$allPk = false;
foreach ($unique->getColumns() as $columnName) {
$allPk &= $table->getColumn($columnName)->isPrimaryKey();
}
if ($allPk) {
//there's already a unique constraint with the composite pk
$pkUniqueExist = true;
break;
}
}
//there is none, let's create it
if (!$pkUniqueExist) {
$unique = new Unique();
foreach ($pks as $pk) {
$unique->addColumn($pk);
}
$table->addUnique($unique);
}
}
parent::normalizeTable($table);
}
/**
* 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);
}
}
/**
* 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'];
$internalName = $name;
if (0 === strpos($name, 'sqlite_autoindex')) {
$internalName = '';
}
$index = $row['unique'] ? new Unique($internalName) : new Index($internalName);
$stmt2 = $this->dbh->query("PRAGMA index_info('" . $name . "')");
while ($row2 = $stmt2->fetch(\PDO::FETCH_ASSOC)) {
$colname = $row2['name'];
$index->addColumn($table->getColumn($colname));
}
if (1 === count($table->getPrimaryKey()) && 1 === count($index->getColumns())) {
// exclude the primary unique index, since it's autogenerated by sqlite
if ($table->getPrimaryKey()[0]->getName() === $index->getColumns()[0]) {
continue;
}
}
if ($index instanceof Unique) {
$table->addUnique($index);
} else {
$table->addIndex($index);
}
}
}
public function testCantGetColumn()
{
$table = new Table('books');
$this->assertFalse($table->hasColumn('FOO', true));
$this->assertNull($table->getColumn('FOO'));
$this->assertNull($table->getColumnByPhpName('Foo'));
}
/**
* Loads the primary key for this table.
*/
protected function addPrimaryKey(Table $table)
{
$dataFetcher = $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.
foreach ($dataFetcher as $row) {
$name = $this->cleanDelimitedIdentifiers($row[0]);
$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'");
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 (isset($row[0])) {
$row = $row[0];
}
$table->getColumn($row['COLUMN_NAME'])->setPrimaryKey(true);
}
}
/**
* Loads the primary key for this table.
*/
protected function addPrimaryKey(Table $table, $oid)
{
$stmt = $this->dbh->prepare("SELECT\n DISTINCT ON(cls.relname)\n cls.relname as idxname,\n indkey,\n indisunique\n FROM pg_index idx\n JOIN pg_class cls ON cls.oid=indexrelid\n WHERE indrelid = ? AND indisprimary\n ORDER 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 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid\n WHERE c.oid = ? AND a.attnum = ? AND NOT a.attisdropped\n ORDER BY a.attnum");
$stmt2->bindValue(1, $oid);
$stmt2->bindValue(2, $intColNum);
$stmt2->execute();
$row2 = $stmt2->fetch(\PDO::FETCH_ASSOC);
if (!$table->getColumn($row2['attname'])) {
continue;
}
$table->getColumn($row2['attname'])->setPrimaryKey(true);
}
}
}
/**
* Adds a relation from logTable to origin table.
*
* @param Table $logTable
*/
protected function addForeignKey(Table $logTable)
{
$table = $this->getTable();
if ($table->getForeignKeysReferencingTable($table->getName())) {
//if already a foreignKey exist to origin table then don't add a second.
return;
}
// create the foreign key
$fk = new ForeignKey();
$fk->setForeignTableCommonName($table->getCommonName());
$fk->setForeignSchemaName($table->getSchema());
$fk->setPhpName('Origin');
$fk->setOnDelete('CASCADE');
$fk->setOnUpdate('CASCADE');
foreach ($table->getPrimaryKey() as $column) {
$fk->addReference($logTable->getColumn($column->getName()), $column);
}
$logTable->addForeignKey($fk);
}
/**
* 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.
*/
protected function addPrimaryKey(Table $table)
{
$stmt = $this->dbh->query(sprintf('SHOW KEYS FROM %s', $this->getPlatform()->doQuoting($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 ('PRIMARY' !== $row['Key_name']) {
continue;
}
$name = $row['Column_name'];
$table->getColumn($name)->setPrimaryKey(true);
}
}
/**
* Returns a column object using a name stored in the behavior parameters.
* Useful for table behaviors.
*
* @param string $name
* @return Column
*/
public function getColumnForParameter($name)
{
return $this->table->getColumn($this->getParameter($name));
}
/**
* 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);
}
}
