/**
* Every rateable entity should have separate table for it's rates.
* This method will create it.
*
* @param string $rateableTableName Name of the table with entities that you gonna make rateable
* @param CDbMigration $migration
*/
public static function createTables($rateableTableName, $migration)
{
$newTable = $rateableTableName . '_rate';
$migration->createTable($newTable, array('id' => 'pk', 'user_id' => 'int(11) NOT NULL', 'target_id' => 'int(11) NOT NULL', 'created_ts' => 'timestamp NOT NULL DEFAULT "0000-00-00"', 'score' => 'tinyint NOT NULL'));
$migration->addForeignKey('fk_' . $newTable . '_user_id', $newTable, 'user_id', 'user_profile', 'user_id', 'CASCADE', 'NO ACTION');
$migration->addForeignKey('fk_' . $newTable . '_target_id', $newTable, 'target_id', $rateableTableName, 'id', 'CASCADE', 'NO ACTION');
}
/**
* Builds and executes a SQL statement for creating a new DB table.
*
* Existing table with same name will be deleted
*
* The columns in the new table should be specified as name-definition pairs (e.g. 'name'=>'string'),
* where name stands for a column name which will be properly quoted by the method, and definition
* stands for the column type which can contain an abstract DB type.
* The {@link getColumnType} method will be invoked to convert any abstract type into a physical one.
*
* If a column is specified with definition only (e.g. 'PRIMARY KEY (name, type)'), it will be directly
* inserted into the generated SQL.
*
* @param string $table the name of the table to be created. The name will be properly quoted by the method.
* @param array $columns the columns (name=>definition) in the new table.
* @param string $options additional SQL fragment that will be appended to the generated SQL.
*/
public function createTable($table, $columns, $options = null)
{
$table = $this->prefixTableName($table);
if ($this->isTableExisted($table)) {
$this->dropTable($table);
}
parent::createTable($table, $columns, $options);
}
/**
* Every commentable entity should have separate table for it's comments.
* This method will create it.
*
* @param string $commentableTableName Name of the table with entities that you gonna make commentable
* @param CDbMigration $migration
*/
public static function createTables($commentableTableName, $migration)
{
$newTableName = $commentableTableName . '_comment';
$migration->createTable($newTableName, array('id' => 'pk', 'target_id' => 'INT(11) NOT NULL', 'user_id' => 'int(11) NOT NULL', 'content' => 'TEXT NOT NULL', 'created_ts' => 'timestamp NOT NULL DEFAULT "0000-00-00"', 'last_update_ts' => 'timestamp NOT NULL DEFAULT "0000-00-00"'));
$migration->addForeignKey('fk_' . $newTableName . '_user_id', $newTableName, 'user_id', 'user_profile', 'user_id', 'CASCADE', 'NO ACTION');
$migration->addForeignKey('fk_' . $newTableName . '_target_id', $newTableName, 'target_id', $commentableTableName, 'id', 'CASCADE', 'NO ACTION');
RateableDbManagerHelper::createTables($newTableName, $migration);
}
/**
* extends the original createTable function to create PK sequence and constraint.
* @param $table string table name
* @param $columns array Array of definitions, string keys will be used as column name and values as column type; indexed strings will be used as-is in the SQL
* @param $options string optional SQL fragment to be appended in the end of the CREATE TABLE SQL
*/
public function createTable($table, $columns, $options = null, $create_trigger = true)
{
$pk = key($columns);
if (is_numeric($pk)) {
$pk = strtok($columns[0], ' ');
}
$columns[] = "CONSTRAINT PK_{$table}_ID PRIMARY KEY ({$pk})";
parent::createTable($table, $columns, $options);
$this->execute("CREATE SEQUENCE SQ_{$table} START WITH 1 INCREMENT BY 1 MINVALUE 1 NOMAXVALUE nocycle noorder");
if ($create_trigger) {
$this->createTrigger("TR_{$table}_NUM", "before insert\n\t\t\t\tON {$table}\n\t\t\t\tfor each row\n\t\t\t\twhen(new.{$pk} is null)\n\t\t\t\tbegin\n\t\t\t\t select SQ_{$table}.nextval into :new.{$pk} from dual;\n\t\t\t\tend;");
}
}
public function createTable($table, $columns)
{
parent::createTable($table, array_merge($columns, ['created_at' => 'integer null', 'updated_at' => 'integer null', 'status' => 'bool not null default true', 'key (status)']), 'ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci');
}
