/**
* 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);
}
public function testAcceptedOfferIsOverridenByNewerInstruction()
{
$instruction = new Deal();
$instruction->dea_prop = $this->property->pro_id;
$instruction->dea_status = 'Completed';
$instruction->save(false);
$sql = "INSERT INTO link_client_to_instruction (dealId, clientId, capacity)\n\t\t\t\tVALUES\n\t\t\t\t(" . $instruction->dea_id . ", " . $this->owner1->cli_id . ", 'Owner'),\n\t\t\t\t(" . $instruction->dea_id . ", " . $this->owner2->cli_id . ", 'Owner')";
Yii::app()->db->createCommand($sql)->execute();
$sql = "INSERT INTO offer SET\n\t\toff_deal = '" . $instruction->dea_id . "',\n\t \toff_status = 'Accepted'";
Yii::app()->db->createCommand($sql)->execute();
$offerId = Yii::app()->db->getLastInsertID();
$sql = "INSERT INTO cli2off SET c2o_cli = '" . $this->owner3->cli_id . "', c2o_off='" . $offerId . "'";
Yii::app()->db->createCommand($sql)->execute();
$sql = "INSERT INTO cli2off SET c2o_cli = '" . $this->owner4->cli_id . "', c2o_off='" . $offerId . "'";
Yii::app()->db->createCommand($sql)->execute();
// =================================================================================
// <<< second instruction
$instruction2 = new Deal();
$instruction2->dea_prop = $this->property->pro_id;
$instruction2->dea_status = 'Available';
$instruction2->save(false);
$sql = "INSERT INTO link_client_to_instruction (dealId, clientId, capacity)\n\t\t\t\t\t\tVALUES\n\t\t\t\t\t\t(" . $instruction2->dea_id . ", " . $this->owner1->cli_id . ", 'Owner'),\n\t\t\t\t\t\t(" . $instruction2->dea_id . ", " . $this->owner2->cli_id . ", 'Owner')";
Yii::app()->db->createCommand($sql)->execute();
// second instruction >>>
// =================================================================================
$this->migration->up();
$property = Property::model()->findByPk($this->property->pro_id);
$this->assertCount(2, $property->owners);
$ownerIds = [];
foreach ($property->owners as $key => $value) {
$ownerIds[] = $value->cli_id;
}
$this->assertContains($this->owner1->cli_id, $ownerIds);
$this->assertContains($this->owner2->cli_id, $ownerIds);
}
public function insert($table, $params)
{
if (array_key_exists('disorder_id', $params)) {
$disorder = $this->dbConnection->createCommand()->select('id')->from('disorder')->where('id=:disorder_id', array(':disorder_id' => $params['disorder_id']))->queryRow();
if (!$disorder) {
unset($params['disorder_id']);
}
}
parent::insert($table, $params);
}
public function addPrimaryKey($name, $table, $columns)
{
$info = explode('.', Yii::getVersion());
if ($info[0] == 1 && $info[1] == 1 && $info[2] <= 12) {
$result = $this->execute("ALTER TABLE {$table} ADD CONSTRAINT {$name} PRIMARY KEY ({$columns})");
if ($result) {
return true;
} else {
return false;
}
} else {
return parent::addPrimaryKey($name, $table, $columns);
}
}
/**
* Executes a SQL statement. Silently. (only show sql on exception)
* This method executes the specified SQL statement using {@link dbConnection}.
* @param string $sql the SQL statement to be executed
* @param array $params input parameters (name=>value) for the SQL execution. See {@link CDbCommand::execute} for more details.
* @param boolean $verbose
*/
public function execute($sql, $params = array(), $verbose = true)
{
if ($verbose) {
parent::execute($sql, $params);
} else {
try {
echo " > execute SQL ...";
$time = microtime(true);
$this->getDbConnection()->createCommand($sql)->execute($params);
echo " done (time: " . sprintf('%.3f', microtime(true) - $time) . "s)\n";
} catch (CException $e) {
echo " failed.\n\n";
throw $e;
}
}
}
public function init()
{
$this->db = 'genesis';
parent::init();
}
public function execute($sql, $params = array())
{
return parent::execute($sql, $params);
}
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');
}
/**
* @param string $commentableTableName
* @param CDbMigration $migration
*/
public static function dropTables($rateableTableName, $migration)
{
$migration->dropTable($rateableTableName . '_rate');
}
/**
* Truncates a table, sweeping all data from it.
* @param type $table table name
* @param type $recreate_pk if the PK should be recreated - reseting the sequence value
* @param type $real_truncate if we should use a real TRUNCATE command; by default it uses DELETE, that's less performatic but doesn't cause headaches related to FKs
*/
public function truncateTable($table, $recreate_pk = true, $real_truncate = false)
{
if ($real_truncate) {
parent::truncateTable($table);
} else {
$this->delete($table);
}
if ($recreate_pk) {
$sequence = 'SQ_' . $table;
$this->execute("DROP SEQUENCE {$sequence}");
$this->execute("CREATE SEQUENCE {$sequence} START WITH 1 INCREMENT BY 1 MINVALUE 1 NOMAXVALUE nocycle noorder");
}
}
