Fix visibility levels of subgroups to be not higher than their parents' level
What does this MR do?
During discussion over making project visibility level match parents group visibility level it was pointed out that we should similar thing regarding groups and subgroups.
== 20191118132107 UpdateExistingSubgroupToMatchVisibilityLevelOfParent: migrating
-- execute("
SELECT id FROM
(WITH RECURSIVE base_and_descendants AS
(
(SELECT visibility_level AS strictest_parent_level,
namespaces.*
FROM namespaces
WHERE namespaces.type IN ('Group'))
UNION
(SELECT LEAST(namespaces.visibility_level, base_and_descendants.strictest_parent_level) AS strictest_parent_level,
namespaces.*
FROM namespaces, base_and_descendants WHERE namespaces.type IN ('Group')
AND namespaces.parent_id = base_and_descendants.id)
)
SELECT id, MIN(strictest_parent_level) AS strictest_parent_level
FROM base_and_descendants WHERE visibility_level > strictest_parent_level
GROUP BY id
)
as namespaces_levels WHERE strictest_parent_level = "10"
")
-> 0.0011s
== 20191118132107 UpdateExistingSubgroupToMatchVisibilityLevelOfParent: migrated (0.0012s)
- estimated number of rows: https://gitlab.slack.com/files/UL8QB0T4J/FR64UU4SW/plan.txt https://gitlab.slack.com/files/UL8QB0T4J/FRK16K4P7/plan.txt
- estimated time:
Time: 18.386 s
- planning: 2.742 ms
- execution: 18.383 s
- I/O read: 0.000 ms
- I/O write: 0.000 ms
and
Time: 1.946 min
- planning: 3.722 ms
- execution: 1.945 min
- I/O read: 1.717 min
- I/O write: 0.000 ms
Database checklist
-
Conforms to the database guides
When adding migrations:
-
Updated db/schema.rb
-
Added a down
method so the migration can be reverted -
Added the output of the migration(s) to the MR body -
Added tests for the migration in spec/migrations
if necessary (e.g. when migrating data) -
Added rollback procedure. Include either a rollback procedure or description how to rollback changes
When adding or modifying queries to improve performance:
-
Included data that shows the performance improvement, preferably in the form of a benchmark -
Included the output of EXPLAIN (ANALYZE, BUFFERS)
of the relevant queries
When adding foreign keys to existing tables:
-
Included a migration to remove orphaned rows in the source table before adding the foreign key -
Removed any instances of dependent: ...
that may no longer be necessary
When adding tables:
-
Ordered columns based on the Ordering Table Columns guidelines -
Added foreign keys to any columns pointing to data in other tables -
Added indexes for fields that are used in statements such as WHERE
,ORDER BY
,GROUP BY
, andJOIN
s
When removing columns, tables, indexes or other structures:
-
Removed these in a post-deployment migration -
Made sure the application no longer uses (or ignores) these structures
Edited by Gosia Ksionek