Hi All,
Was finding issues trying to set up a composite key with a migration using mssql so added a couple of commands to CdBMigration so I could go ahead and set up the migrations. Can’t link currently, so have embedded code below:
CdBMigration.php
/*
* Modifies exisiting column to become PK -- for composite PK's
* @param string $name name of the constraint to add
* @param string $table name of the table to add primary key to
* @param string $columns name of the column to utilise as primary key. If there are multiple columns, separate them with commas.
*/
public function addPrimaryKey($name,$table,$columns)
{
echo " > alter table $table add constraint $name primary key ($columns) ...";
$time=microtime(true);
$this->getDbConnection()->createCommand()->addPrimaryKey($name,$table,$columns);
echo " done (time: ".sprintf('%.3f', microtime(true)-$time)."s)\n";
}
/*
* Modifies exisiting column to become PK -- for composite PK's
* @param string $name name of the constraint to add
* @param string $table name of the table to add primary key to
* @param string $column name of the column to utilise as primary key.
*/
public function dropPrimaryKey($name,$table)
{
echo " > alter table $table drop constraint $name primary key $column ...";
$time=microtime(true);
$this->getDbConnection()->createCommand()->dropPrimaryKey($name,$table);
echo " done (time: ".sprintf('%.3f', microtime(true)-$time)."s)\n";
}
CDbCommand.php
/**
* Builds a SQL statement for creating a primary key constraint.
* @param string $name the name of the primary key to be created. The name will be properly quoted by the method.
* @param string $table the table who will be inheriting the primary key. The name will be properly quoted by the method.
* @param string $column the column where the primary key will be effected. The name will be properly quoted by the method.
* @return integer number of rows affected by the execution.
* @since 1.1.6
*/
public function addPrimaryKey($name,$table,$columns)
{
return $this->setText($this->getConnection()->getSchema()->addPrimaryKey($name,$table,$columns))->execute();
}
/**
* Builds a SQL statement for creating a primary key constraint.
* @param string $name the name of the primary key to be dropped. The name will be properly quoted by the method.
* @param string $table the table that owns the primary key. The name will be properly quoted by the method.
* @return integer number of rows affected by the execution.
* @since 1.1.6
*/
public function dropPrimaryKey($name,$table)
{
return $this->setText($this->getConnection()->getSchema()->dropPrimaryKey($name,$table))->execute();
}
CDbSchema.php
/**
* Builds a SQL statement for adding a primary key constraint to an existing table.
* The method will properly quote the table and column names.
* @param string $name the name of the primary key constraint.
* @param string $table the table that the primary key constraint will be added to.
* @param string $columns the name of the column to that the constraint will be added on.
* @return string the SQL statement for adding a primary key constraint to an existing table.
* @since 1.1.6
*/
public function addPrimaryKey($name,$table,$columns)
{
$columns=preg_split('/\s*,\s*/',$columns,-1,PREG_SPLIT_NO_EMPTY);
foreach($columns as $i=>$col)
$columns[$i]=$this->quoteColumnName($col);
return 'ALTER TABLE ' . $this->quoteTableName($table) . ' ADD CONSTRAINT '
. $this->quoteColumnName($name) . ' PRIMARY KEY ('
. implode(', ', $columns). ' )';
}
/**
* Builds a SQL statement for removing a primary key constraint to an existing table.
* The method will properly quote the table and column names.
* @param string $name the name of the primary key constraint to be removed.
* @param string $table the table that the primary key constraint will be removed from.
* @return string the SQL statement for adding a primary key constraint to an existing table.
* @since 1.1.6
*/
public function dropPrimaryKey($name,$table)
{
return 'ALTER TABLE ' . $this->quoteTableName($table) . ' DROP CONSTRAINT '
. $this->quoteColumnName($name);
}