yii2

1
<?php
2
/**
3
 * @link https://www.yiiframework.com/
4
 * @copyright Copyright (c) 2008 Yii Software LLC
5
 * @license https://www.yiiframework.com/license/
6
 */
7

8
use yii\base\InvalidConfigException;
9
use yii\db\Migration;
10
use yii\db\Query;
11
use yii\rbac\DbManager;
12

13
/**
14
 * Fix MSSQL trigger.
15
 *
16
 * @see https://github.com/yiisoft/yii2/pull/17966
17
 *
18
 * @author Aurelien Chretien <chretien.aurelien@gmail.com>
19
 * @since 2.0.35
20
 */
21
class m200409_110543_rbac_update_mssql_trigger extends Migration
22
{
23
    /**
24
     * @throws yii\base\InvalidConfigException
25
     * @return DbManager
26
     */
27
    protected function getAuthManager()
28
    {
29
        $authManager = Yii::$app->getAuthManager();
30
        if (!$authManager instanceof DbManager) {
31
            throw new InvalidConfigException('You should configure "authManager" component to use database before executing this migration.');
32
        }
33

34
        return $authManager;
35
    }
36

37
    protected function findForeignKeyName($table, $column, $referenceTable, $referenceColumn)
38
    {
39
        return (new Query())
40
            ->select(['OBJECT_NAME(fkc.constraint_object_id)'])
41
            ->from(['fkc' => 'sys.foreign_key_columns'])
42
            ->innerJoin(['c' => 'sys.columns'], 'fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id')
43
            ->innerJoin(['r' => 'sys.columns'], 'fkc.referenced_object_id = r.object_id AND fkc.referenced_column_id = r.column_id')
44
            ->andWhere('fkc.parent_object_id=OBJECT_ID(:fkc_parent_object_id)',[':fkc_parent_object_id' => $this->db->schema->getRawTableName($table)])
45
            ->andWhere('fkc.referenced_object_id=OBJECT_ID(:fkc_referenced_object_id)',[':fkc_referenced_object_id' => $this->db->schema->getRawTableName($referenceTable)])
46
            ->andWhere(['c.name' => $column])
47
            ->andWhere(['r.name' => $referenceColumn])
48
            ->scalar($this->db);
49
    }
50

51
    /**
52
     * @return bool
53
     */
54
    protected function isMSSQL()
55
    {
56
        return $this->db->driverName === 'mssql' || $this->db->driverName === 'sqlsrv' || $this->db->driverName === 'dblib';
57
    }
58

59
    /**
60
     * {@inheritdoc}
61
     */
62
    public function up()
63
    {
64
        if ($this->isMSSQL()) {
65
            $authManager = $this->getAuthManager();
66
            $this->db = $authManager->db;
67
            $schema = $this->db->getSchema()->defaultSchema;
68
            $triggerSuffix = $this->db->schema->getRawTableName($authManager->itemChildTable);
69

70
            $this->execute("IF (OBJECT_ID(N'{$schema}.trigger_{$triggerSuffix}') IS NOT NULL) DROP TRIGGER {$schema}.trigger_{$triggerSuffix};");
71
            $this->execute("IF (OBJECT_ID(N'{$schema}.trigger_auth_item_child') IS NOT NULL) DROP TRIGGER {$schema}.trigger_auth_item_child;");
72

73
            $this->execute("CREATE TRIGGER {$schema}.trigger_delete_{$triggerSuffix}
74
            ON {$schema}.{$authManager->itemTable}
75
            INSTEAD OF DELETE
76
            AS
77
            BEGIN
78
                  DELETE FROM {$schema}.{$authManager->itemChildTable} WHERE parent IN (SELECT name FROM deleted) OR child IN (SELECT name FROM deleted);
79
                  DELETE FROM {$schema}.{$authManager->itemTable} WHERE name IN (SELECT name FROM deleted);
80
            END;"
81
            );
82

83
            $foreignKey = $this->findForeignKeyName($authManager->itemChildTable, 'child', $authManager->itemTable, 'name');
84
            $this->execute("CREATE TRIGGER {$schema}.trigger_update_{$triggerSuffix}
85
            ON {$schema}.{$authManager->itemTable}
86
            INSTEAD OF UPDATE
87
            AS
88
                DECLARE @old_name NVARCHAR(64) = (SELECT name FROM deleted)
89
                DECLARE @new_name NVARCHAR(64) = (SELECT name FROM inserted)
90
            BEGIN
91
                IF @old_name <> @new_name
92
                BEGIN
93
                    ALTER TABLE {$authManager->itemChildTable} NOCHECK CONSTRAINT {$foreignKey};
94
                    UPDATE {$authManager->itemChildTable} SET child = @new_name WHERE child = @old_name;
95
                END
96
            UPDATE {$authManager->itemTable}
97
            SET name = (SELECT name FROM inserted),
98
            type = (SELECT type FROM inserted),
99
            description = (SELECT description FROM inserted),
100
            rule_name = (SELECT rule_name FROM inserted),
101
            data = (SELECT data FROM inserted),
102
            created_at = (SELECT created_at FROM inserted),
103
            updated_at = (SELECT updated_at FROM inserted)
104
            WHERE name IN (SELECT name FROM deleted)
105
            IF @old_name <> @new_name
106
                BEGIN
107
                    ALTER TABLE {$authManager->itemChildTable} CHECK CONSTRAINT {$foreignKey};
108
                END
109
            END;"
110
            );
111
        }
112
    }
113

114
    /**
115
     * {@inheritdoc}
116
     */
117
    public function down()
118
    {
119
        if ($this->isMSSQL()) {
120
            $authManager = $this->getAuthManager();
121
            $this->db = $authManager->db;
122
            $schema = $this->db->getSchema()->defaultSchema;
123
            $triggerSuffix = $this->db->schema->getRawTableName($authManager->itemChildTable);
124

125
            $this->execute("DROP TRIGGER {$schema}.trigger_update_{$triggerSuffix};");
126
            $this->execute("DROP TRIGGER {$schema}.trigger_delete_{$triggerSuffix};");
127

128
            $this->execute("CREATE TRIGGER {$schema}.trigger_auth_item_child
129
            ON {$schema}.{$authManager->itemTable}
130
            INSTEAD OF DELETE, UPDATE
131
            AS
132
            DECLARE @old_name VARCHAR (64) = (SELECT name FROM deleted)
133
            DECLARE @new_name VARCHAR (64) = (SELECT name FROM inserted)
134
            BEGIN
135
            IF COLUMNS_UPDATED() > 0
136
                BEGIN
137
                    IF @old_name <> @new_name
138
                    BEGIN
139
                        ALTER TABLE {$authManager->itemChildTable} NOCHECK CONSTRAINT FK__auth_item__child;
140
                        UPDATE {$authManager->itemChildTable} SET child = @new_name WHERE child = @old_name;
141
                    END
142
                UPDATE {$authManager->itemTable}
143
                SET name = (SELECT name FROM inserted),
144
                type = (SELECT type FROM inserted),
145
                description = (SELECT description FROM inserted),
146
                rule_name = (SELECT rule_name FROM inserted),
147
                data = (SELECT data FROM inserted),
148
                created_at = (SELECT created_at FROM inserted),
149
                updated_at = (SELECT updated_at FROM inserted)
150
                WHERE name IN (SELECT name FROM deleted)
151
                IF @old_name <> @new_name
152
                    BEGIN
153
                        ALTER TABLE {$authManager->itemChildTable} CHECK CONSTRAINT FK__auth_item__child;
154
                    END
155
                END
156
                ELSE
157
                    BEGIN
158
                        DELETE FROM {$schema}.{$authManager->itemChildTable} WHERE parent IN (SELECT name FROM deleted) OR child IN (SELECT name FROM deleted);
159
                        DELETE FROM {$schema}.{$authManager->itemTable} WHERE name IN (SELECT name FROM deleted);
160
                    END
161
            END;");
162
        }
163
    }
164
}
165