Last active
October 26, 2016 15:42
-
-
Save mgerasimchuk/2873964cd646ff5ea22c5bb0ab023f6b to your computer and use it in GitHub Desktop.
Remove dependent data
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| use yii\db\Migration; | |
| class m160512_055401_users_delete extends Migration | |
| { | |
| public function safeUp() | |
| { | |
| $sql = <<<SQL | |
| SET @acolinEmail = 'acollin@hartlandinternational.com'; | |
| SET @coachId = (SELECT id FROM user_account WHERE email = @acolinEmail); | |
| DROP TABLE IF EXISTS coachGroups, notCoachGroup, coachUsers, usersForDelete, | |
| activityUploadForDelete, activityForDelete, eventCodeFaildForDelete, groupForDelete, groupStudentForDelete, userProfileForDelete; | |
| CREATE TEMPORARY TABLE IF NOT EXISTS coachGroups AS (SELECT id FROM `group` WHERE `group`.ownerId = @coachId); | |
| CREATE TEMPORARY TABLE IF NOT EXISTS notCoachGroup AS (SELECT id FROM `group` WHERE `group`.ownerId != @coachId); | |
| CREATE TEMPORARY TABLE IF NOT EXISTS coachUsers AS (SELECT gs.studentId FROM group_student gs WHERE gs.groupId IN (SELECT * FROM coachGroups)); | |
| CREATE TEMPORARY TABLE IF NOT EXISTS usersForDelete AS (SELECT id FROM user_account WHERE id NOT IN (SELECT * FROM coachUsers) AND id NOT IN (SELECT @coachId UNION SELECT 1 UNION SELECT 2 UNION SELECT 95)); #1 - root, 2 - student, 95 - coach | |
| CREATE TEMPORARY TABLE IF NOT EXISTS activityUploadForDelete AS (SELECT id FROM activity_upload WHERE coachId IN (SELECT id FROM usersForDelete)); | |
| CREATE TEMPORARY TABLE IF NOT EXISTS activityForDelete AS (SELECT id FROM activity WHERE activityUploadId IN (SELECT id FROM activityUploadForDelete) OR studentId IN (SELECT id FROM usersForDelete) OR groupId IN (SELECT id FROM notCoachGroup)); | |
| CREATE TEMPORARY TABLE IF NOT EXISTS eventCodeFaildForDelete AS (SELECT id FROM event_code_failed_attempt ecfa WHERE ecfa.userId IN (SELECT id FROM usersForDelete)); | |
| CREATE TEMPORARY TABLE IF NOT EXISTS groupForDelete AS (SELECT id FROM `group` WHERE `group`.ownerId IN (SELECT id FROM usersForDelete)); | |
| CREATE TEMPORARY TABLE IF NOT EXISTS groupStudentForDelete AS (SELECT id FROM group_student WHERE studentId IN (SELECT id FROM usersForDelete)); | |
| CREATE TEMPORARY TABLE IF NOT EXISTS userProfileForDelete AS (SELECT id FROM user_profile WHERE ownerId IN (SELECT id FROM usersForDelete)); | |
| DELETE FROM event_code_failed_attempt WHERE id IN (SELECT * FROM eventCodeFaildForDelete); | |
| DELETE FROM activity WHERE id IN (SELECT * FROM activityForDelete); | |
| DELETE FROM activity_upload WHERE id IN (SELECT * FROM activityUploadForDelete); | |
| DELETE FROM group_student WHERE id IN (SELECT * FROM groupStudentForDelete); | |
| DELETE FROM event_code_failed_attempt WHERE id IN (SELECT * FROM eventCodeFaildForDelete); | |
| DELETE FROM `group` WHERE id IN (SELECT * FROM groupForDelete); | |
| DELETE FROM user_profile WHERE id IN (SELECT * FROM userProfileForDelete); | |
| DELETE FROM user_account WHERE id IN (SELECT * FROM usersForDelete); | |
| DROP TABLE IF EXISTS coachGroups, notCoachGroup, coachUsers, usersForDelete, | |
| activityUploadForDelete, activityForDelete, eventCodeFaildForDelete, groupForDelete, groupStudentForDelete, userProfileForDelete; | |
| SQL; | |
| $this->execute($sql); | |
| } | |
| public function safeDown() | |
| { | |
| return; | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Требовалось удалить тестовые данные, которые создавал пользователь(заказчик) в продакшн базе а также мы сам(разработчики, кто работал над проектом), очень много таблиц завязано между собой ключами, было принято решение оформить всю процедуру в safe миграцию(в случае ошибки изменения бы откатились, т.к. safe миграция "оборачивается" в транзакцию). Некоторые моменты можно было реализовать с использованием JOIN конструкций, но решая задачу "в лоб" потребовалось меньше времени, и для данной ситуации выбранное решение является более надежным. Решение реализовано с использованием объединений(по строкам) и виртуальных таблиц.