Skip to content

Instantly share code, notes, and snippets.

@ridget
Created June 20, 2012 01:05
Show Gist options
  • Save ridget/2957501 to your computer and use it in GitHub Desktop.
Save ridget/2957501 to your computer and use it in GitHub Desktop.
Adding an addPrimaryKey command to yii's db migrations -- supports composite primary keys
/**
* 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 dropping 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();
}
/*
* 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";
}
/**
* 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);
}
<?php
class m120619_234658_add_composite_pk extends CDbMigration
{
public function up()
{
$this->addPrimaryKey('PK_FOO','tbl_fooBar','fooId, barId');
}
public function down()
{
$this->dropPrimaryKey('PK_FOO','tbl_fooBar');
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment