8w6QwUuY

1DROP TEMPORARY TABLE IF EXISTS organizationIds;
2DROP TEMPORARY TABLE IF EXISTS  reportExtendedIds;
3DROP TEMPORARY TABLE IF EXISTS objectIds;
4DROP TEMPORARY TABLE IF EXISTS accessTokenIds;
5DROP TEMPORARY TABLE IF EXISTS personIds;
6
7/** Get nested organization identifiers */
8CREATE TEMPORARY TABLE IF NOT EXISTS organizationIds AS (
9   SELECT s.id as id FROM organizations r
10        JOIN organizations s ON s.lft BETWEEN r.lft AND r.rgt
11        WHERE r.id IN (
12            1313 -- put here organization identifiers which you want to delete from DB
13        )
14);
15
16CREATE INDEX organizationIdsIndex ON organizationIds(id);
17-- lets cache object indexes
18CREATE TEMPORARY TABLE IF NOT EXISTS objectIds AS (
19   SELECT o.id as id FROM objects o WHERE o.organizations_id IN (SELECT * FROM organizationIds)
20);
21
22CREATE INDEX objectIdsIndex ON objectIds(id);
23-- cache person indexes
24CREATE TEMPORARY TABLE IF NOT EXISTS personIds AS (
25   SELECT p.id as id FROM persons_and_vacancies p WHERE p.organizations_id IN (SELECT * FROM organizationIds) OR p.objects_id IN (SELECT * FROM objectIds)
26);
27
28CREATE INDEX personIdsIndex ON personIds(id);
29
30-- reports indexes
31CREATE TEMPORARY TABLE IF NOT EXISTS reportExtendedIds AS (
32   SELECT r.id as id FROM report_extended r WHERE 
33        (r.organizations_id IN (SELECT * FROM organizationIds)) OR
34        (r.objects_id IN (SELECT * FROM objectIds))
35);
36
37CREATE INDEX reportExtendedIdsIndex ON reportExtendedIds(id);
38
39-- access token indexes
40CREATE TEMPORARY TABLE IF NOT EXISTS accessTokenIds AS (
41   SELECT r.id as id FROM auth_access_tokens r WHERE 
42        (r.person_id IN (SELECT * FROM personIds))
43);
44
45CREATE INDEX accessTokenIdsIndex ON accessTokenIds(id);
46
47BEGIN;
48-- remove report extended with relations
49DELETE FROM comments WHERE report_extended_id IN (SELECT * FROM reportExtendedIds);
50DELETE FROM corrections WHERE report_extended_id IN (SELECT * FROM reportExtendedIds);
51DELETE FROM report_extended_interval WHERE last_id IN (SELECT * FROM reportExtendedIds);
52DELETE FROM estimation_point WHERE report_id IN (SELECT * FROM reportExtendedIds);
53DELETE FROM report_extended WHERE id IN(SELECT * FROM reportExtendedIds);
54
55-- remove plannings
56DELETE FROM planning WHERE organizations_id IN (SELECT * FROM organizationIds) OR objects_id IN (SELECT * FROM objectIds);
57
58-- remove persons
59DELETE FROM log_actions WHERE persons_id IN (SELECT * FROM personIds);
60DELETE FROM persons_has_roles WHERE persons_id IN (SELECT * FROM personIds);
61DELETE FROM presentations_viewed_by_persons WHERE persons_id IN (SELECT * FROM personIds);
62DELETE FROM profiling_log WHERE persons_id IN (SELECT * FROM personIds);
63DELETE FROM selector WHERE persons_id IN (SELECT * FROM personIds);
64DELETE FROM persons_lock_has_objects WHERE persons_id IN (SELECT * FROM personIds);
65DELETE FROM mobile_auth WHERE person_id IN (SELECT * FROM personIds);
66DELETE FROM persons_documents WHERE persons_id IN (SELECT * FROM personIds);
67DELETE FROM workplace_information WHERE person_id IN (SELECT * FROM personIds);
68DELETE FROM rest_api_call_log WHERE auth_id IN (SELECT * FROM accessTokenIds);
69DELETE FROM client_error_log WHERE auth_id IN (SELECT * FROM accessTokenIds);
70DELETE FROM auth_access_tokens WHERE id IN (SELECT * FROM accessTokenIds);
71DELETE FROM auth_assignment WHERE userid IN (SELECT * FROM personIds);
72DELETE FROM persons_and_vacancies WHERE id IN (SELECT * FROM personIds);
73
74-- remove staff
75DELETE FROM staff WHERE organizations_id IN (SELECT * FROM organizationIds);
76
77-- remove specialities
78DELETE FROM selector WHERE specialities_id IN (SELECT id FROM specialities WHERE organizations_id IN (SELECT * FROM organizationIds));
79DELETE FROM specialities WHERE organizations_id IN (SELECT * FROM organizationIds);
80
81-- remove objects
82DELETE FROM selector WHERE objects_id IN (SELECT * FROM objectIds);
83DELETE FROM trips_schedules_has_events WHERE objects_id IN(SELECT * FROM objectIds);
84DELETE FROM cards WHERE assemblage_objects_id IN (SELECT * FROM objectIds) OR organizations_id IN (SELECT * FROM organizationIds);
85DELETE FROM objects WHERE id IN (SELECT * FROM objectIds);
86
87-- remove timesheets
88DELETE FROM organizations_has_timesheets WHERE organizations_id IN (SELECT * FROM organizationIds);
89
90-- remove schedules
91DELETE FROM schedules WHERE organizations_id IN (SELECT * FROM organizationIds);
92
93-- remove organizations 
94DELETE FROM selector WHERE organizations_id IN (SELECT * FROM organizationIds);
95DELETE FROM organizations WHERE id IN (SELECT * FROM organizationIds);
96
97ROLLBACK;
98-- COMMIT;
99DROP TEMPORARY TABLE IF EXISTS organizationIds;
100DROP TEMPORARY TABLE IF EXISTS  reportExtendedIds;
101DROP TEMPORARY TABLE IF EXISTS objectIds;
102DROP TEMPORARY TABLE IF EXISTS accessTokenIds;
103DROP TEMPORARY TABLE IF EXISTS personIds;