Skip to content

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) 
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

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, and JOINs

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

Merge request reports