123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185 |
- <?php
- use yii\db\Migration;
- use yii\base\InvalidConfigException;
- class m170719_192953_create_rbac_tables extends Migration
- {/*
- public function safeUp()
- {
- }
- public function safeDown()
- {
- echo "m170719_192953_create_rbac_tables cannot be reverted.\n";
- return false;
- }*/
- /**
- * @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;
- }
- /**
- * @return bool
- */
- protected function isMSSQL()
- {
- return $this->db->driverName === 'mssql' || $this->db->driverName === 'sqlsrv' || $this->db->driverName === 'dblib';
- }
- protected function isOracle()
- {
- return $this->db->driverName === 'oci';
- }
- /**
- * @inheritdoc
- */
- public function safeUp()
- {
- $authManager = $this->getAuthManager();
- $this->db = $authManager->db;
- $tableOptions = null;
- if ($this->db->driverName === 'mysql') {
- // http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
- $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
- }
- $this->createTable($authManager->ruleTable, [
- 'name' => $this->string(64)->notNull(),
- 'data' => $this->binary(),
- 'created_at' => $this->integer(),
- 'updated_at' => $this->integer(),
- 'PRIMARY KEY ([[name]])',
- ], $tableOptions);
- $this->createTable($authManager->itemTable, [
- 'name' => $this->string(64)->notNull(),
- 'type' => $this->smallInteger()->notNull(),
- 'description' => $this->text(),
- 'rule_name' => $this->string(64),
- 'data' => $this->binary(),
- 'created_at' => $this->integer(),
- 'updated_at' => $this->integer(),
- 'PRIMARY KEY ([[name]])',
- 'FOREIGN KEY ([[rule_name]]) REFERENCES ' . $authManager->ruleTable . ' ([[name]])'.
- $this->buildFkClause('ON DELETE SET NULL', 'ON UPDATE CASCADE')
- ], $tableOptions);
- $this->createIndex('idx-auth_item-type', $authManager->itemTable, 'type');
- $this->createTable($authManager->itemChildTable, [
- 'parent' => $this->string(64)->notNull(),
- 'child' => $this->string(64)->notNull(),
- 'PRIMARY KEY ([[parent]], [[child]])',
- 'FOREIGN KEY ([[parent]]) REFERENCES ' . $authManager->itemTable . ' ([[name]])'.
- $this->buildFkClause('ON DELETE CASCADE', 'ON UPDATE CASCADE'),
- 'FOREIGN KEY ([[child]]) REFERENCES ' . $authManager->itemTable . ' ([[name]])'.
- $this->buildFkClause('ON DELETE CASCADE', 'ON UPDATE CASCADE'),
- ], $tableOptions);
- $this->createTable($authManager->assignmentTable, [
- 'item_name' => $this->string(64)->notNull(),
- 'user_id' => $this->string(64)->notNull(),
- 'created_at' => $this->integer(),
- 'PRIMARY KEY ([[item_name]], [[user_id]])',
- 'FOREIGN KEY ([[item_name]]) REFERENCES ' . $authManager->itemTable . ' ([[name]])' .
- $this->buildFkClause('ON DELETE CASCADE', 'ON UPDATE CASCADE'),
- ], $tableOptions);
- if ($this->isMSSQL()) {
- $this->execute("CREATE TRIGGER dbo.trigger_auth_item_child
- ON dbo.{$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 dbo.{$authManager->itemChildTable} WHERE parent IN (SELECT name FROM deleted) OR child IN (SELECT name FROM deleted);
- DELETE FROM dbo.{$authManager->itemTable} WHERE name IN (SELECT name FROM deleted);
- END
- END;");
- }
- }
- /**
- * @inheritdoc
- */
- public function safeDown()
- {
- $authManager = $this->getAuthManager();
- $this->db = $authManager->db;
- if ($this->isMSSQL()) {
- $this->execute('DROP TRIGGER dbo.trigger_auth_item_child;');
- }
- $this->dropTable($authManager->assignmentTable);
- $this->dropTable($authManager->itemChildTable);
- $this->dropTable($authManager->itemTable);
- $this->dropTable($authManager->ruleTable);
- }
- protected function buildFkClause($delete = '', $update = '')
- {
- if ($this->isMSSQL()) {
- return '';
- }
- if ($this->isOracle()) {
- return ' ' . $delete;
- }
- return implode(' ', ['', $delete, $update]);
- }
- /*
- // Use up()/down() to run migration code without a transaction.
- public function up()
- {
- }
- public function down()
- {
- echo "m170719_192953_create_rbac_tables cannot be reverted.\n";
- return false;
- }
- */
- }
|