Merge branch 'feature/precalculate-authorized-projects' into 'master'

Precalculate authorized projects in database

## What does this MR do?
It caches user's authorized projects in database instead of using multiple unions, which should simplify and speed-up things since this operation (getting authorized projects) is used a lot.

## Are there points in the code the reviewer needs to double check?
Did we miss a scenario where we need to refresh the list of projects?

## Does this MR meet the acceptance criteria?

- [x] [CHANGELOG](https://gitlab.com/gitlab-org/gitlab-ce/blob/master/CHANGELOG) entry added
- [ ] ~~[Documentation created/updated](https://gitlab.com/gitlab-org/gitlab-ce/blob/master/doc/development/doc_styleguide.md)~~
- [ ] ~~API support added~~
- Tests
  - [x] Added for this feature/bug
  - [x] All builds are passing
- [x] Conform by the [merge request performance guides](http://docs.gitlab.com/ce/development/merge_request_performance_guidelines.html)
- [x] Conform by the [style guides](https://gitlab.com/gitlab-org/gitlab-ce/blob/master/CONTRIBUTING.md#style-guides)
- [x] Branch has no merge conflicts with `master` (if it does - rebase it please)
- [x] [Squashed related commits together](https://git-scm.com/book/en/Git-Tools-Rewriting-History#Squashing-Commits)

## What are the relevant issue numbers?
#23150 

See merge request !6839

7 files changed, 102 insertions, 47 deletions

diff --git a/app/models/concerns/select_for_project_authorization.rb b/app/models/concerns/select_for_project_authorization.rb
new file mode 100644
index 00000000000..50a1d7fc3e1
--- /dev/null
+++ b/app/models/concerns/select_for_project_authorization.rb
@@ -0,0 +1,9 @@
+module SelectForProjectAuthorization
+  extend ActiveSupport::Concern
+
+  module ClassMethods
+    def select_for_project_authorization
+      select("members.user_id, projects.id AS project_id, members.access_level")
+    end
+  end
+end
diff --git a/app/models/group.rb b/app/models/group.rb
index d9e90cd256a..73b0f1c6572 100644
--- a/app/models/group.rb
+++ b/app/models/group.rb
@@ -5,6 +5,7 @@ class Group < Namespace
   include Gitlab::VisibilityLevel
   include AccessRequestable
   include Referable
+  include SelectForProjectAuthorization
 
   has_many :group_members, -> { where(requested_at: nil) }, dependent: :destroy, as: :source
   alias_method :members, :group_members
@@ -61,6 +62,14 @@ class Group < Namespace
     def visible_to_user(user)
       where(id: user.authorized_groups.select(:id).reorder(nil))
     end
+
+    def select_for_project_authorization
+      if current_scope.joins_values.include?(:shared_projects)
+        select("members.user_id, projects.id AS project_id, project_group_links.group_access")
+      else
+        super
+      end
+    end
   end
 
   def to_reference(_from_project = nil)
@@ -176,4 +185,8 @@ class Group < Namespace
   def system_hook_service
     SystemHooksService.new
   end
+
+  def refresh_members_authorized_projects
+    UserProjectAccessChangedService.new(users.pluck(:id)).execute
+  end
 end
diff --git a/app/models/member.rb b/app/models/member.rb
index b89ba8ecbb8..7be2665bf48 100644
--- a/app/models/member.rb
+++ b/app/models/member.rb
@@ -113,6 +113,8 @@ class Member < ActiveRecord::Base
         member.save
       end
 
+      UserProjectAccessChangedService.new(user.id).execute if user.is_a?(User)
+
       member
     end
 
@@ -239,6 +241,7 @@ class Member < ActiveRecord::Base
   end
 
   def post_create_hook
+    UserProjectAccessChangedService.new(user.id).execute
     system_hook_service.execute_hooks_for(self, :create)
   end
 
@@ -247,9 +250,19 @@ class Member < ActiveRecord::Base
   end
 
   def post_destroy_hook
+    refresh_member_authorized_projects
     system_hook_service.execute_hooks_for(self, :destroy)
   end
 
+  def refresh_member_authorized_projects
+    # If user/source is being destroyed, project access are gonna be destroyed eventually
+    # because of DB foreign keys, so we shouldn't bother with refreshing after each
+    # member is destroyed through association
+    return if destroyed_by_association.present?
+
+    UserProjectAccessChangedService.new(user_id).execute
+  end
+
   def after_accept_invite
     post_create_hook
   end
diff --git a/app/models/project.rb b/app/models/project.rb
index 4aedc91dc34..9b14a3d9303 100644
--- a/app/models/project.rb
+++ b/app/models/project.rb
@@ -13,6 +13,7 @@ class Project < ActiveRecord::Base
   include CaseSensitivity
   include TokenAuthenticatable
   include ProjectFeaturesCompatibility
+  include SelectForProjectAuthorization
 
   extend Gitlab::ConfigHelper
 
@@ -1289,16 +1290,10 @@ class Project < ActiveRecord::Base
 
   # Checks if `user` is authorized for this project, with at least the
   # `min_access_level` (if given).
-  #
-  # If you change the logic of this method, please also update `User#authorized_projects`
   def authorized_for_user?(user, min_access_level = nil)
     return false unless user
 
-    return true if personal? && namespace_id == user.namespace_id
-
-    authorized_for_user_by_group?(user, min_access_level) ||
-      authorized_for_user_by_members?(user, min_access_level) ||
-      authorized_for_user_by_shared_projects?(user, min_access_level)
+    user.authorized_project?(self, min_access_level)
   end
 
   def append_or_update_attribute(name, value)
@@ -1350,30 +1345,6 @@ class Project < ActiveRecord::Base
       current_application_settings.default_branch_protection == Gitlab::Access::PROTECTION_DEV_CAN_MERGE
   end
 
-  def authorized_for_user_by_group?(user, min_access_level)
-    member = user.group_members.find_by(source_id: group)
-
-    member && (!min_access_level || member.access_level >= min_access_level)
-  end
-
-  def authorized_for_user_by_members?(user, min_access_level)
-    member = members.find_by(user_id: user)
-
-    member && (!min_access_level || member.access_level >= min_access_level)
-  end
-
-  def authorized_for_user_by_shared_projects?(user, min_access_level)
-    shared_projects = user.group_members.joins(group: :shared_projects).
-      where(project_group_links: { project_id: self })
-
-    if min_access_level
-      members_scope = { access_level: Gitlab::Access.values.select { |access| access >= min_access_level } }
-      shared_projects = shared_projects.where(members: members_scope)
-    end
-
-    shared_projects.any?
-  end
-
   # Similar to the normal callbacks that hook into the life cycle of an
   # Active Record object, you can also define callbacks that get triggered
   # when you add an object to an association collection. If any of these
diff --git a/app/models/project_authorization.rb b/app/models/project_authorization.rb
new file mode 100644
index 00000000000..a00d43773d9
--- /dev/null
+++ b/app/models/project_authorization.rb
@@ -0,0 +1,8 @@
+class ProjectAuthorization < ActiveRecord::Base
+  belongs_to :user
+  belongs_to :project
+
+  validates :project, presence: true
+  validates :access_level, inclusion: { in: Gitlab::Access.all_values }, presence: true
+  validates :user, uniqueness: { scope: [:project, :access_level] }, presence: true
+end
diff --git a/app/models/project_group_link.rb b/app/models/project_group_link.rb
index db46def11eb..6149c35cc61 100644
--- a/app/models/project_group_link.rb
+++ b/app/models/project_group_link.rb
@@ -16,6 +16,9 @@ class ProjectGroupLink < ActiveRecord::Base
   validates :group_access, inclusion: { in: Gitlab::Access.values }, presence: true
   validate :different_group
 
+  after_create :refresh_group_members_authorized_projects
+  after_destroy :refresh_group_members_authorized_projects
+
   def self.access_options
     Gitlab::Access.options
   end
@@ -35,4 +38,8 @@ class ProjectGroupLink < ActiveRecord::Base
       errors.add(:base, "Project cannot be shared with the project it is in.")
     end
   end
+
+  def refresh_group_members_authorized_projects
+    group.refresh_members_authorized_projects
+  end
 end
diff --git a/app/models/user.rb b/app/models/user.rb
index 5a2b232c4ed..c405321127b 100644
--- a/app/models/user.rb
+++ b/app/models/user.rb
@@ -72,6 +72,8 @@ class User < ActiveRecord::Base
   has_many :created_projects,         foreign_key: :creator_id, class_name: 'Project'
   has_many :users_star_projects, dependent: :destroy
   has_many :starred_projects, through: :users_star_projects, source: :project
+  has_many :project_authorizations, dependent: :destroy
+  has_many :authorized_projects, through: :project_authorizations, source: :project
 
   has_many :snippets,                 dependent: :destroy, foreign_key: :author_id
   has_many :issues,                   dependent: :destroy, foreign_key: :author_id
@@ -438,11 +440,44 @@ class User < ActiveRecord::Base
     Group.where("namespaces.id IN (#{union.to_sql})")
   end
 
-  # Returns projects user is authorized to access.
-  #
-  # If you change the logic of this method, please also update `Project#authorized_for_user`
+  def refresh_authorized_projects
+    loop do
+      begin
+        Gitlab::Database.serialized_transaction do
+          project_authorizations.delete_all
+
+          # project_authorizations_union can return multiple records for the same project/user with
+          # different access_level so we take row with the maximum access_level
+          project_authorizations.connection.execute <<-SQL
+            INSERT INTO project_authorizations (user_id, project_id, access_level)
+            SELECT user_id, project_id, MAX(access_level) AS access_level
+            FROM (#{project_authorizations_union.to_sql}) sub
+            GROUP BY user_id, project_id
+          SQL
+
+          update_column(:authorized_projects_populated, true) unless authorized_projects_populated
+        end
+
+        break
+      # In the event of a concurrent modification Rails raises StatementInvalid.
+      # In this case we want to keep retrying until the transaction succeeds
+      rescue ActiveRecord::StatementInvalid
+      end
+    end
+  end
+
   def authorized_projects(min_access_level = nil)
-    Project.where("projects.id IN (#{projects_union(min_access_level).to_sql})")
+    refresh_authorized_projects unless authorized_projects_populated
+
+    # We're overriding an association, so explicitly call super with no arguments or it would be passed as `force_reload` to the association
+    projects = super()
+    projects = projects.where('project_authorizations.access_level >= ?', min_access_level) if min_access_level
+
+    projects
+  end
+
+  def authorized_project?(project, min_access_level = nil)
+    authorized_projects(min_access_level).exists?({ id: project.id })
   end
 
   # Returns the projects this user has reporter (or greater) access to, limited
@@ -456,8 +491,9 @@ class User < ActiveRecord::Base
   end
 
   def viewable_starred_projects
-    starred_projects.where("projects.visibility_level IN (?) OR projects.id IN (#{projects_union.to_sql})",
-                           [Project::PUBLIC, Project::INTERNAL])
+    starred_projects.where("projects.visibility_level IN (?) OR projects.id IN (?)",
+                           [Project::PUBLIC, Project::INTERNAL],
+                           authorized_projects.select(:project_id))
   end
 
   def owned_projects
@@ -887,16 +923,14 @@ class User < ActiveRecord::Base
 
   private
 
-  def projects_union(min_access_level = nil)
-    relations = [personal_projects.select(:id),
-                 groups_projects.select(:id),
-                 projects.select(:id),
-                 groups.joins(:shared_projects).select(:project_id)]
-
-    if min_access_level
-      scope = { access_level: Gitlab::Access.all_values.select { |access| access >= min_access_level } }
-      relations = [relations.shift] + relations.map { |relation| relation.where(members: scope) }
-    end
+  # Returns a union query of projects that the user is authorized to access
+  def project_authorizations_union
+    relations = [
+      personal_projects.select("#{id} AS user_id, projects.id AS project_id, #{Gitlab::Access::OWNER} AS access_level"),
+      groups_projects.select_for_project_authorization,
+      projects.select_for_project_authorization,
+      groups.joins(:shared_projects).select_for_project_authorization
+    ]
 
     Gitlab::SQL::Union.new(relations)
   end