From d29347220c07ab0191cf208d3775475c7b5d71ca Mon Sep 17 00:00:00 2001 From: Yorick Peterse Date: Tue, 13 Jun 2017 16:44:55 +0200 Subject: Refactor ProjectsFinder#init_collection This changes ProjectsFinder#init_collection so it no longer relies on a UNION. For example, to get starred projects of a user we used to run: SELECT projects.* FROM projects WHERE projects.pending_delete = 'f' AND ( projects.id IN ( SELECT projects.id FROM projects INNER JOIN users_star_projects ON users_star_projects.project_id = projects.id INNER JOIN project_authorizations ON projects.id = project_authorizations.project_id WHERE projects.pending_delete = 'f' AND project_authorizations.user_id = 1 AND users_star_projects.user_id = 1 UNION SELECT projects.id FROM projects INNER JOIN users_star_projects ON users_star_projects.project_id = projects.id WHERE projects.visibility_level IN (20, 10) AND users_star_projects.user_id = 1 ) ) ORDER BY projects.id DESC; With these changes the above query is turned into the following instead: SELECT projects.* FROM projects INNER JOIN users_star_projects ON users_star_projects.project_id = projects.id WHERE projects.pending_delete = 'f' AND ( EXISTS ( SELECT 1 FROM project_authorizations WHERE project_authorizations.user_id = 1 AND (project_id = projects.id) ) OR projects.visibility_level IN (20,10) ) AND users_star_projects.user_id = 1 ORDER BY projects.id DESC; This query in turn produces a better execution plan and takes less time, though the difference is only a few milliseconds (this however depends on the amount of data involved and additional conditions that may be added). --- spec/lib/gitlab/visibility_level_spec.rb | 31 +++++++++++++++++++++++++++++++ 1 file changed, 31 insertions(+) (limited to 'spec/lib/gitlab/visibility_level_spec.rb') diff --git a/spec/lib/gitlab/visibility_level_spec.rb b/spec/lib/gitlab/visibility_level_spec.rb index 3255c6f1ef7..a8f21803ec7 100644 --- a/spec/lib/gitlab/visibility_level_spec.rb +++ b/spec/lib/gitlab/visibility_level_spec.rb @@ -18,4 +18,35 @@ describe Gitlab::VisibilityLevel, lib: true do expect(described_class.level_value(100)).to eq(Gitlab::VisibilityLevel::PRIVATE) end end + + describe '.levels_for_user' do + it 'returns all levels for an admin' do + user = double(:user, admin?: true) + + expect(described_class.levels_for_user(user)). + to eq([Gitlab::VisibilityLevel::PRIVATE, + Gitlab::VisibilityLevel::INTERNAL, + Gitlab::VisibilityLevel::PUBLIC]) + end + + it 'returns INTERNAL and PUBLIC for internal users' do + user = double(:user, admin?: false, external?: false) + + expect(described_class.levels_for_user(user)). + to eq([Gitlab::VisibilityLevel::INTERNAL, + Gitlab::VisibilityLevel::PUBLIC]) + end + + it 'returns PUBLIC for external users' do + user = double(:user, admin?: false, external?: true) + + expect(described_class.levels_for_user(user)). + to eq([Gitlab::VisibilityLevel::PUBLIC]) + end + + it 'returns PUBLIC when no user is given' do + expect(described_class.levels_for_user). + to eq([Gitlab::VisibilityLevel::PUBLIC]) + end + end end -- cgit v1.2.3