| Server IP : 49.231.201.246 / Your IP : 216.73.216.149 Web Server : Apache/2.4.18 (Ubuntu) System : Linux 246 4.4.0-210-generic #242-Ubuntu SMP Fri Apr 16 09:57:56 UTC 2021 x86_64 User : root ( 0) PHP Version : 7.0.33-0ubuntu0.16.04.16 Disable Function : exec,passthru,shell_exec,system,proc_open,popen,pcntl_exec MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /var/www/html/egp/vendor/yiisoft/yii2/rbac/migrations/ |
Upload File : |
<?php
/**
* @link http://www.yiiframework.com/
* @copyright Copyright (c) 2008 Yii Software LLC
* @license http://www.yiiframework.com/license/
*/
use yii\base\InvalidConfigException;
use yii\db\Migration;
use yii\db\Query;
use yii\rbac\DbManager;
/**
* Fix MSSQL trigger.
*
* @see https://github.com/yiisoft/yii2/pull/17966
*
* @author Aurelien Chretien <chretien.aurelien@gmail.com>
* @since 2.0.35
*/
class m200409_110543_rbac_update_mssql_trigger extends Migration
{
/**
* @throws yii\base\InvalidConfigException
* @return DbManager
*/
protected function getAuthManager()
{
$authManager = Yii::$app->getAuthManager();
if (!$authManager instanceof DbManager) {
throw new InvalidConfigException('You should configure "authManager" component to use database before executing this migration.');
}
return $authManager;
}
protected function findForeignKeyName($table, $column, $referenceTable, $referenceColumn)
{
return (new Query())
->select(['OBJECT_NAME(fkc.constraint_object_id)'])
->from(['fkc' => 'sys.foreign_key_columns'])
->innerJoin(['c' => 'sys.columns'], 'fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id')
->innerJoin(['r' => 'sys.columns'], 'fkc.referenced_object_id = r.object_id AND fkc.referenced_column_id = r.column_id')
->andWhere('fkc.parent_object_id=OBJECT_ID(:fkc_parent_object_id)',[':fkc_parent_object_id' => $this->db->schema->getRawTableName($table)])
->andWhere('fkc.referenced_object_id=OBJECT_ID(:fkc_referenced_object_id)',[':fkc_referenced_object_id' => $this->db->schema->getRawTableName($referenceTable)])
->andWhere(['c.name' => $column])
->andWhere(['r.name' => $referenceColumn])
->scalar($this->db);
}
/**
* @return bool
*/
protected function isMSSQL()
{
return $this->db->driverName === 'mssql' || $this->db->driverName === 'sqlsrv' || $this->db->driverName === 'dblib';
}
/**
* {@inheritdoc}
*/
public function up()
{
if ($this->isMSSQL()) {
$authManager = $this->getAuthManager();
$this->db = $authManager->db;
$schema = $this->db->getSchema()->defaultSchema;
$triggerSuffix = $this->db->schema->getRawTableName($authManager->itemChildTable);
$this->execute("IF (OBJECT_ID(N'{$schema}.trigger_{$triggerSuffix}') IS NOT NULL) DROP TRIGGER {$schema}.trigger_{$triggerSuffix};");
$this->execute("IF (OBJECT_ID(N'{$schema}.trigger_auth_item_child') IS NOT NULL) DROP TRIGGER {$schema}.trigger_auth_item_child;");
$this->execute("CREATE TRIGGER {$schema}.trigger_delete_{$triggerSuffix}
ON {$schema}.{$authManager->itemTable}
INSTEAD OF DELETE
AS
BEGIN
DELETE FROM {$schema}.{$authManager->itemChildTable} WHERE parent IN (SELECT name FROM deleted) OR child IN (SELECT name FROM deleted);
DELETE FROM {$schema}.{$authManager->itemTable} WHERE name IN (SELECT name FROM deleted);
END;"
);
$foreignKey = $this->findForeignKeyName($authManager->itemChildTable, 'child', $authManager->itemTable, 'name');
$this->execute("CREATE TRIGGER {$schema}.trigger_update_{$triggerSuffix}
ON {$schema}.{$authManager->itemTable}
INSTEAD OF UPDATE
AS
DECLARE @old_name NVARCHAR(64) = (SELECT name FROM deleted)
DECLARE @new_name NVARCHAR(64) = (SELECT name FROM inserted)
BEGIN
IF @old_name <> @new_name
BEGIN
ALTER TABLE {$authManager->itemChildTable} NOCHECK CONSTRAINT {$foreignKey};
UPDATE {$authManager->itemChildTable} SET child = @new_name WHERE child = @old_name;
END
UPDATE {$authManager->itemTable}
SET name = (SELECT name FROM inserted),
type = (SELECT type FROM inserted),
description = (SELECT description FROM inserted),
rule_name = (SELECT rule_name FROM inserted),
data = (SELECT data FROM inserted),
created_at = (SELECT created_at FROM inserted),
updated_at = (SELECT updated_at FROM inserted)
WHERE name IN (SELECT name FROM deleted)
IF @old_name <> @new_name
BEGIN
ALTER TABLE {$authManager->itemChildTable} CHECK CONSTRAINT {$foreignKey};
END
END;"
);
}
}
/**
* {@inheritdoc}
*/
public function down()
{
if ($this->isMSSQL()) {
$authManager = $this->getAuthManager();
$this->db = $authManager->db;
$schema = $this->db->getSchema()->defaultSchema;
$triggerSuffix = $this->db->schema->getRawTableName($authManager->itemChildTable);
$this->execute("DROP TRIGGER {$schema}.trigger_update_{$triggerSuffix};");
$this->execute("DROP TRIGGER {$schema}.trigger_delete_{$triggerSuffix};");
$this->execute("CREATE TRIGGER {$schema}.trigger_auth_item_child
ON {$schema}.{$authManager->itemTable}
INSTEAD OF DELETE, UPDATE
AS
DECLARE @old_name VARCHAR (64) = (SELECT name FROM deleted)
DECLARE @new_name VARCHAR (64) = (SELECT name FROM inserted)
BEGIN
IF COLUMNS_UPDATED() > 0
BEGIN
IF @old_name <> @new_name
BEGIN
ALTER TABLE {$authManager->itemChildTable} NOCHECK CONSTRAINT FK__auth_item__child;
UPDATE {$authManager->itemChildTable} SET child = @new_name WHERE child = @old_name;
END
UPDATE {$authManager->itemTable}
SET name = (SELECT name FROM inserted),
type = (SELECT type FROM inserted),
description = (SELECT description FROM inserted),
rule_name = (SELECT rule_name FROM inserted),
data = (SELECT data FROM inserted),
created_at = (SELECT created_at FROM inserted),
updated_at = (SELECT updated_at FROM inserted)
WHERE name IN (SELECT name FROM deleted)
IF @old_name <> @new_name
BEGIN
ALTER TABLE {$authManager->itemChildTable} CHECK CONSTRAINT FK__auth_item__child;
END
END
ELSE
BEGIN
DELETE FROM {$schema}.{$authManager->itemChildTable} WHERE parent IN (SELECT name FROM deleted) OR child IN (SELECT name FROM deleted);
DELETE FROM {$schema}.{$authManager->itemTable} WHERE name IN (SELECT name FROM deleted);
END
END;");
}
}
}