From 85c6a3743abe5683c2317f1957a9f047ad2b4b8e Mon Sep 17 00:00:00 2001 From: Yorick Peterse Date: Wed, 7 Oct 2015 12:30:10 +0200 Subject: Added methods for detecting MySQL/PostgreSQL These two methods remove the need for manually going into ActiveRecord::Base.connection all over the place. --- lib/gitlab/database.rb | 11 +++++++++++ spec/lib/gitlab/database_spec.rb | 17 +++++++++++++++++ 2 files changed, 28 insertions(+) create mode 100644 lib/gitlab/database.rb create mode 100644 spec/lib/gitlab/database_spec.rb diff --git a/lib/gitlab/database.rb b/lib/gitlab/database.rb new file mode 100644 index 00000000000..741a52714ac --- /dev/null +++ b/lib/gitlab/database.rb @@ -0,0 +1,11 @@ +module Gitlab + module Database + def self.mysql? + ActiveRecord::Base.connection.adapter_name.downcase == 'mysql' + end + + def self.postgresql? + ActiveRecord::Base.connection.adapter_name.downcase == 'postgresql' + end + end +end diff --git a/spec/lib/gitlab/database_spec.rb b/spec/lib/gitlab/database_spec.rb new file mode 100644 index 00000000000..7cdebdf209a --- /dev/null +++ b/spec/lib/gitlab/database_spec.rb @@ -0,0 +1,17 @@ +require 'spec_helper' + +describe Gitlab::Database do + # These are just simple smoke tests to check if the methods work (regardless + # of what they may return). + describe '.mysql?' do + subject { described_class.mysql? } + + it { is_expected.to satisfy { |val| val == true || val == false } } + end + + describe '.postgresql?' do + subject { described_class.postgresql? } + + it { is_expected.to satisfy { |val| val == true || val == false } } + end +end -- cgit v1.2.1 From 1190d0ab3dc7a3025bf55b666f34d1a0b51a8d89 Mon Sep 17 00:00:00 2001 From: Yorick Peterse Date: Wed, 7 Oct 2015 14:03:18 +0200 Subject: Added concern for case-insensitive WHERE queries On PostgreSQL these queries use LOWER(...) to compare columns and values. For MySQL a regular WHERE is performed as MySQL is already case-insensitive. --- app/models/concerns/case_sensitivity.rb | 28 ++++ spec/models/concerns/case_sensitivity_spec.rb | 176 ++++++++++++++++++++++++++ 2 files changed, 204 insertions(+) create mode 100644 app/models/concerns/case_sensitivity.rb create mode 100644 spec/models/concerns/case_sensitivity_spec.rb diff --git a/app/models/concerns/case_sensitivity.rb b/app/models/concerns/case_sensitivity.rb new file mode 100644 index 00000000000..49d350e092b --- /dev/null +++ b/app/models/concerns/case_sensitivity.rb @@ -0,0 +1,28 @@ +# Concern for querying columns with specific case sensitivity handling. +module CaseSensitivity + extend ActiveSupport::Concern + + module ClassMethods + # Queries the given columns regardless of the casing used. + # + # Unlike other ActiveRecord methods this method only operates on a Hash. + def case_insensitive_where(params) + criteria = self + cast_lower = Gitlab::Database.postgresql? + + params.each do |key, value| + column = ActiveRecord::Base.connection.quote_table_name(key) + + if cast_lower + condition = "LOWER(#{column}) = LOWER(:value)" + else + condition = "#{column} = :value" + end + + criteria = criteria.where(condition, value: value) + end + + criteria + end + end +end diff --git a/spec/models/concerns/case_sensitivity_spec.rb b/spec/models/concerns/case_sensitivity_spec.rb new file mode 100644 index 00000000000..8b9f50aada7 --- /dev/null +++ b/spec/models/concerns/case_sensitivity_spec.rb @@ -0,0 +1,176 @@ +require 'spec_helper' + +describe CaseSensitivity do + describe '.case_insensitive_where' do + let(:connection) { ActiveRecord::Base.connection } + let(:model) { Class.new { include CaseSensitivity } } + + describe 'using PostgreSQL' do + describe 'with a single column/value pair' do + it 'returns the criteria for a column and a value' do + criteria = double(:criteria) + + expect(connection).to receive(:quote_table_name). + with(:foo). + and_return('"foo"') + + expect(model).to receive(:where). + with(%q{LOWER("foo") = LOWER(:value)}, value: 'bar'). + and_return(criteria) + + expect(model.case_insensitive_where(foo: 'bar')).to eq(criteria) + end + + it 'returns the criteria for a column with a table, and a value' do + criteria = double(:criteria) + + expect(connection).to receive(:quote_table_name). + with(:'foo.bar'). + and_return('"foo"."bar"') + + expect(model).to receive(:where). + with(%q{LOWER("foo"."bar") = LOWER(:value)}, value: 'bar'). + and_return(criteria) + + expect(model.case_insensitive_where('foo.bar': 'bar')).to eq(criteria) + end + end + + describe 'with multiple column/value pairs' do + it 'returns the criteria for a column and a value' do + initial = double(:criteria) + final = double(:criteria) + + expect(connection).to receive(:quote_table_name). + with(:foo). + and_return('"foo"') + + expect(connection).to receive(:quote_table_name). + with(:bar). + and_return('"bar"') + + expect(model).to receive(:where). + with(%q{LOWER("foo") = LOWER(:value)}, value: 'bar'). + and_return(initial) + + expect(initial).to receive(:where). + with(%q{LOWER("bar") = LOWER(:value)}, value: 'baz'). + and_return(final) + + got = model.case_insensitive_where(foo: 'bar', bar: 'baz') + + expect(got).to eq(final) + end + + it 'returns the criteria for a column with a table, and a value' do + initial = double(:criteria) + final = double(:criteria) + + expect(connection).to receive(:quote_table_name). + with(:'foo.bar'). + and_return('"foo"."bar"') + + expect(connection).to receive(:quote_table_name). + with(:'foo.baz'). + and_return('"foo"."baz"') + + expect(model).to receive(:where). + with(%q{LOWER("foo"."bar") = LOWER(:value)}, value: 'bar'). + and_return(initial) + + expect(initial).to receive(:where). + with(%q{LOWER("foo"."baz") = LOWER(:value)}, value: 'baz'). + and_return(final) + + got = model.case_insensitive_where('foo.bar': 'bar', 'foo.baz': 'baz') + + expect(got).to eq(final) + end + end + end + + describe 'using MySQL' do + describe 'with a single column/value pair' do + it 'returns the criteria for a column and a value' do + criteria = double(:criteria) + + expect(connection).to receive(:quote_table_name). + with(:foo). + and_return('`foo`') + + expect(model).to receive(:where). + with(%q{LOWER(`foo`) = LOWER(:value)}, value: 'bar'). + and_return(criteria) + + expect(model.case_insensitive_where(foo: 'bar')).to eq(criteria) + end + + it 'returns the criteria for a column with a table, and a value' do + criteria = double(:criteria) + + expect(connection).to receive(:quote_table_name). + with(:'foo.bar'). + and_return('`foo`.`bar`') + + expect(model).to receive(:where). + with(%q{LOWER(`foo`.`bar`) = LOWER(:value)}, value: 'bar'). + and_return(criteria) + + expect(model.case_insensitive_where('foo.bar': 'bar')).to eq(criteria) + end + end + + describe 'with multiple column/value pairs' do + it 'returns the criteria for a column and a value' do + initial = double(:criteria) + final = double(:criteria) + + expect(connection).to receive(:quote_table_name). + with(:foo). + and_return('`foo`') + + expect(connection).to receive(:quote_table_name). + with(:bar). + and_return('`bar`') + + expect(model).to receive(:where). + with(%q{LOWER(`foo`) = LOWER(:value)}, value: 'bar'). + and_return(initial) + + expect(initial).to receive(:where). + with(%q{LOWER(`bar`) = LOWER(:value)}, value: 'baz'). + and_return(final) + + got = model.case_insensitive_where(foo: 'bar', bar: 'baz') + + expect(got).to eq(final) + end + + it 'returns the criteria for a column with a table, and a value' do + initial = double(:criteria) + final = double(:criteria) + + expect(connection).to receive(:quote_table_name). + with(:'foo.bar'). + and_return('`foo`.`bar`') + + expect(connection).to receive(:quote_table_name). + with(:'foo.baz'). + and_return('`foo`.`baz`') + + expect(model).to receive(:where). + with(%q{LOWER(`foo`.`bar`) = LOWER(:value)}, value: 'bar'). + and_return(initial) + + expect(initial).to receive(:where). + with(%q{LOWER(`foo`.`baz`) = LOWER(:value)}, value: 'baz'). + and_return(final) + + got = model.case_insensitive_where('foo.bar': 'bar', 'foo.baz': 'baz') + + expect(got).to eq(final) + end + end + end + end +end -- cgit v1.2.1 From 03417456f0b7db408bfefd28e5b9342889b7f711 Mon Sep 17 00:00:00 2001 From: Yorick Peterse Date: Wed, 7 Oct 2015 17:37:39 +0200 Subject: Revamp finding projects by namespaces By using a JOIN we can remove the need for using 2 separate queries to find a project by its namespace. Combined with an index (only needed for PostgreSQL) this reduces the query time from ~245 ms (~520 ms for the first call) down to roughly 10 ms (~15 ms for the first call). --- app/models/concerns/case_sensitivity.rb | 2 +- app/models/project.rb | 20 ++++++---- ...20511_namespaces_projects_path_lower_indexes.rb | 17 +++++++++ db/schema.rb | 2 +- spec/benchmarks/models/project_spec.rb | 20 ++++++++++ spec/models/concerns/case_sensitivity_spec.rb | 43 ++++++++++++++-------- 6 files changed, 80 insertions(+), 24 deletions(-) create mode 100644 db/migrate/20151007120511_namespaces_projects_path_lower_indexes.rb create mode 100644 spec/benchmarks/models/project_spec.rb diff --git a/app/models/concerns/case_sensitivity.rb b/app/models/concerns/case_sensitivity.rb index 49d350e092b..fe0cea8465f 100644 --- a/app/models/concerns/case_sensitivity.rb +++ b/app/models/concerns/case_sensitivity.rb @@ -6,7 +6,7 @@ module CaseSensitivity # Queries the given columns regardless of the casing used. # # Unlike other ActiveRecord methods this method only operates on a Hash. - def case_insensitive_where(params) + def iwhere(params) criteria = self cast_lower = Gitlab::Database.postgresql? diff --git a/app/models/project.rb b/app/models/project.rb index bb47b9abb03..f75082a35d0 100644 --- a/app/models/project.rb +++ b/app/models/project.rb @@ -40,6 +40,7 @@ class Project < ActiveRecord::Base include Referable include Sortable include AfterCommitQueue + include CaseSensitivity extend Gitlab::ConfigHelper extend Enumerize @@ -235,13 +236,18 @@ class Project < ActiveRecord::Base end def find_with_namespace(id) - return nil unless id.include?('/') - - id = id.split('/') - namespace = Namespace.by_path(id.first) - return nil unless namespace - - where(namespace_id: namespace.id).where("LOWER(projects.path) = :path", path: id.second.downcase).first + namespace_path, project_path = id.split('/') + + return nil if !namespace_path || !project_path + + # Use of unscoped ensures we're not secretly adding any ORDER BYs, which + # have a negative impact on performance (and aren't needed for this + # query). + unscoped. + joins(:namespace). + iwhere('namespaces.path' => namespace_path). + iwhere('projects.path' => project_path). + take end def visibility_levels diff --git a/db/migrate/20151007120511_namespaces_projects_path_lower_indexes.rb b/db/migrate/20151007120511_namespaces_projects_path_lower_indexes.rb new file mode 100644 index 00000000000..7f6cd6d5a78 --- /dev/null +++ b/db/migrate/20151007120511_namespaces_projects_path_lower_indexes.rb @@ -0,0 +1,17 @@ +class NamespacesProjectsPathLowerIndexes < ActiveRecord::Migration + disable_ddl_transaction! + + def up + return unless Gitlab::Database.postgresql? + + execute 'CREATE INDEX CONCURRENTLY index_on_namespaces_lower_path ON namespaces (LOWER(path));' + execute 'CREATE INDEX CONCURRENTLY index_on_projects_lower_path ON projects (LOWER(path));' + end + + def down + return unless Gitlab::Database.postgresql? + + remove_index :namespaces, name: :index_on_namespaces_lower_path + remove_index :projects, name: :index_on_projects_lower_path + end +end diff --git a/db/schema.rb b/db/schema.rb index 93202f16111..c5c462c2e57 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -11,7 +11,7 @@ # # It's strongly recommended that you check this file into your version control system. -ActiveRecord::Schema.define(version: 20151005162154) do +ActiveRecord::Schema.define(version: 20151007120511) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" diff --git a/spec/benchmarks/models/project_spec.rb b/spec/benchmarks/models/project_spec.rb new file mode 100644 index 00000000000..0c6b533ac2b --- /dev/null +++ b/spec/benchmarks/models/project_spec.rb @@ -0,0 +1,20 @@ +require 'spec_helper' + +describe Project, benchmark: true do + describe '.find_with_namespace' do + let(:group) { create(:group, name: 'sisinmaru') } + let(:project) { create(:project, name: 'maru', namespace: group) } + + describe 'using a capitalized namespace' do + benchmark_subject { described_class.find_with_namespace('sisinmaru/MARU') } + + it { is_expected.to iterate_per_second(600) } + end + + describe 'using a lowercased namespace' do + benchmark_subject { described_class.find_with_namespace('sisinmaru/maru') } + + it { is_expected.to iterate_per_second(600) } + end + end +end diff --git a/spec/models/concerns/case_sensitivity_spec.rb b/spec/models/concerns/case_sensitivity_spec.rb index 8b9f50aada7..f7ed30f8198 100644 --- a/spec/models/concerns/case_sensitivity_spec.rb +++ b/spec/models/concerns/case_sensitivity_spec.rb @@ -1,11 +1,16 @@ require 'spec_helper' describe CaseSensitivity do - describe '.case_insensitive_where' do + describe '.iwhere' do let(:connection) { ActiveRecord::Base.connection } let(:model) { Class.new { include CaseSensitivity } } describe 'using PostgreSQL' do + before do + allow(Gitlab::Database).to receive(:postgresql?).and_return(true) + allow(Gitlab::Database).to receive(:mysql?).and_return(false) + end + describe 'with a single column/value pair' do it 'returns the criteria for a column and a value' do criteria = double(:criteria) @@ -18,7 +23,7 @@ describe CaseSensitivity do with(%q{LOWER("foo") = LOWER(:value)}, value: 'bar'). and_return(criteria) - expect(model.case_insensitive_where(foo: 'bar')).to eq(criteria) + expect(model.iwhere(foo: 'bar')).to eq(criteria) end it 'returns the criteria for a column with a table, and a value' do @@ -32,7 +37,7 @@ describe CaseSensitivity do with(%q{LOWER("foo"."bar") = LOWER(:value)}, value: 'bar'). and_return(criteria) - expect(model.case_insensitive_where('foo.bar': 'bar')).to eq(criteria) + expect(model.iwhere(:'foo.bar' => 'bar')).to eq(criteria) end end @@ -57,7 +62,7 @@ describe CaseSensitivity do with(%q{LOWER("bar") = LOWER(:value)}, value: 'baz'). and_return(final) - got = model.case_insensitive_where(foo: 'bar', bar: 'baz') + got = model.iwhere(foo: 'bar', bar: 'baz') expect(got).to eq(final) end @@ -82,7 +87,8 @@ describe CaseSensitivity do with(%q{LOWER("foo"."baz") = LOWER(:value)}, value: 'baz'). and_return(final) - got = model.case_insensitive_where('foo.bar': 'bar', 'foo.baz': 'baz') + got = model.iwhere(:'foo.bar' => 'bar', + :'foo.baz' => 'baz') expect(got).to eq(final) end @@ -90,6 +96,11 @@ describe CaseSensitivity do end describe 'using MySQL' do + before do + allow(Gitlab::Database).to receive(:postgresql?).and_return(false) + allow(Gitlab::Database).to receive(:mysql?).and_return(true) + end + describe 'with a single column/value pair' do it 'returns the criteria for a column and a value' do criteria = double(:criteria) @@ -99,10 +110,10 @@ describe CaseSensitivity do and_return('`foo`') expect(model).to receive(:where). - with(%q{LOWER(`foo`) = LOWER(:value)}, value: 'bar'). + with(%q{`foo` = :value}, value: 'bar'). and_return(criteria) - expect(model.case_insensitive_where(foo: 'bar')).to eq(criteria) + expect(model.iwhere(foo: 'bar')).to eq(criteria) end it 'returns the criteria for a column with a table, and a value' do @@ -113,10 +124,11 @@ describe CaseSensitivity do and_return('`foo`.`bar`') expect(model).to receive(:where). - with(%q{LOWER(`foo`.`bar`) = LOWER(:value)}, value: 'bar'). + with(%q{`foo`.`bar` = :value}, value: 'bar'). and_return(criteria) - expect(model.case_insensitive_where('foo.bar': 'bar')).to eq(criteria) + expect(model.iwhere(:'foo.bar' => 'bar')). + to eq(criteria) end end @@ -134,14 +146,14 @@ describe CaseSensitivity do and_return('`bar`') expect(model).to receive(:where). - with(%q{LOWER(`foo`) = LOWER(:value)}, value: 'bar'). + with(%q{`foo` = :value}, value: 'bar'). and_return(initial) expect(initial).to receive(:where). - with(%q{LOWER(`bar`) = LOWER(:value)}, value: 'baz'). + with(%q{`bar` = :value}, value: 'baz'). and_return(final) - got = model.case_insensitive_where(foo: 'bar', bar: 'baz') + got = model.iwhere(foo: 'bar', bar: 'baz') expect(got).to eq(final) end @@ -159,14 +171,15 @@ describe CaseSensitivity do and_return('`foo`.`baz`') expect(model).to receive(:where). - with(%q{LOWER(`foo`.`bar`) = LOWER(:value)}, value: 'bar'). + with(%q{`foo`.`bar` = :value}, value: 'bar'). and_return(initial) expect(initial).to receive(:where). - with(%q{LOWER(`foo`.`baz`) = LOWER(:value)}, value: 'baz'). + with(%q{`foo`.`baz` = :value}, value: 'baz'). and_return(final) - got = model.case_insensitive_where('foo.bar': 'bar', 'foo.baz': 'baz') + got = model.iwhere(:'foo.bar' => 'bar', + :'foo.baz' => 'baz') expect(got).to eq(final) end -- cgit v1.2.1 From c8f18fc56283859f47ab50a5e14a7b292f8e54a5 Mon Sep 17 00:00:00 2001 From: Yorick Peterse Date: Wed, 7 Oct 2015 17:42:47 +0200 Subject: Added dedicated Rake task for setting up Postgres This ensures any PostgreSQL specific schema changes (e.g. expression indexes) are created when setting up the database. --- lib/tasks/gitlab/setup.rake | 1 + lib/tasks/migrate/setup_postgresql.rake | 6 ++++++ 2 files changed, 7 insertions(+) create mode 100644 lib/tasks/migrate/setup_postgresql.rake diff --git a/lib/tasks/gitlab/setup.rake b/lib/tasks/gitlab/setup.rake index 0ac4b0fa8a3..4cbccf2ca89 100644 --- a/lib/tasks/gitlab/setup.rake +++ b/lib/tasks/gitlab/setup.rake @@ -16,6 +16,7 @@ namespace :gitlab do Rake::Task["db:setup"].invoke Rake::Task["add_limits_mysql"].invoke + Rake::Task["setup_postgresql"].invoke Rake::Task["db:seed_fu"].invoke rescue Gitlab::TaskAbortedByUserError puts "Quitting...".red diff --git a/lib/tasks/migrate/setup_postgresql.rake b/lib/tasks/migrate/setup_postgresql.rake new file mode 100644 index 00000000000..bf6894a8351 --- /dev/null +++ b/lib/tasks/migrate/setup_postgresql.rake @@ -0,0 +1,6 @@ +require Rails.root.join('db/migrate/20151007120511_namespaces_projects_path_lower_indexes') + +desc 'GitLab | Sets up PostgreSQL' +task setup_postgresql: :environment do + NamespacesProjectsPathLowerIndexes.new.up +end -- cgit v1.2.1 From 2d779f70ae51d6b23fd1e5d6b14c19762ba000cc Mon Sep 17 00:00:00 2001 From: Yorick Peterse Date: Wed, 7 Oct 2015 18:12:15 +0200 Subject: Added changelog for project namespace performance --- CHANGELOG | 1 + 1 file changed, 1 insertion(+) diff --git a/CHANGELOG b/CHANGELOG index 8ab2d9f08da..e578411c589 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -4,6 +4,7 @@ v 8.1.0 (unreleased) - Add support for creating directories from Files page (Stan Hu) - Allow removing of project without confirmation when JavaScript is disabled (Stan Hu) - Support filtering by "Any" milestone or issue and fix "No Milestone" and "No Label" filters (Stan Hu) + - Improved performance of finding projects by their namespace - Fix bug where transferring a project would result in stale commit links (Stan Hu) - Include full path of source and target branch names in New Merge Request page (Stan Hu) - Add user preference to view activities as default dashboard (Stan Hu) -- cgit v1.2.1