diff options
Diffstat (limited to 'lib/gitlab/sql')
-rw-r--r-- | lib/gitlab/sql/except.rb | 22 | ||||
-rw-r--r-- | lib/gitlab/sql/intersect.rb | 23 | ||||
-rw-r--r-- | lib/gitlab/sql/set_operator.rb | 53 | ||||
-rw-r--r-- | lib/gitlab/sql/union.rb | 27 |
4 files changed, 101 insertions, 24 deletions
diff --git a/lib/gitlab/sql/except.rb b/lib/gitlab/sql/except.rb new file mode 100644 index 00000000000..82cbfa8d4ab --- /dev/null +++ b/lib/gitlab/sql/except.rb @@ -0,0 +1,22 @@ +# frozen_string_literal: true + +module Gitlab + module SQL + # Class for building SQL EXCEPT statements. + # + # ORDER BYs are dropped from the relations as the final sort order is not + # guaranteed any way. + # + # Example usage: + # + # except = Gitlab::SQL::Except.new([user.projects, user.personal_projects]) + # sql = except.to_sql + # + # Project.where("id IN (#{sql})") + class Except < SetOperator + def self.operator_keyword + 'EXCEPT' + end + end + end +end diff --git a/lib/gitlab/sql/intersect.rb b/lib/gitlab/sql/intersect.rb new file mode 100644 index 00000000000..c661db3d4c5 --- /dev/null +++ b/lib/gitlab/sql/intersect.rb @@ -0,0 +1,23 @@ +# frozen_string_literal: true + +module Gitlab + module SQL + # Class for building SQL INTERSECT statements. + # + # ORDER BYs are dropped from the relations as the final sort order is not + # guaranteed any way. + # + # Example usage: + # + # hierarchies = [group1.self_and_hierarchy, group2.self_and_hierarchy] + # intersect = Gitlab::SQL::Intersect.new(hierarchies) + # sql = intersect.to_sql + # + # Project.where("id IN (#{sql})") + class Intersect < SetOperator + def self.operator_keyword + 'INTERSECT' + end + end + end +end diff --git a/lib/gitlab/sql/set_operator.rb b/lib/gitlab/sql/set_operator.rb new file mode 100644 index 00000000000..d58a1415493 --- /dev/null +++ b/lib/gitlab/sql/set_operator.rb @@ -0,0 +1,53 @@ +# frozen_string_literal: true + +module Gitlab + module SQL + # Class for building SQL set operator statements (UNION, INTERSECT, and + # EXCEPT). + # + # ORDER BYs are dropped from the relations as the final sort order is not + # guaranteed any way. + # + # Example usage: + # + # union = Gitlab::SQL::Union.new([user.personal_projects, user.projects]) + # sql = union.to_sql + # + # Project.where("id IN (#{sql})") + class SetOperator + def initialize(relations, remove_duplicates: true) + @relations = relations + @remove_duplicates = remove_duplicates + end + + def self.operator_keyword + raise NotImplementedError + end + + def to_sql + # Some relations may include placeholders for prepared statements, these + # aren't incremented properly when joining relations together this way. + # By using "unprepared_statements" we remove the usage of placeholders + # (thus fixing this problem), at a slight performance cost. + fragments = ActiveRecord::Base.connection.unprepared_statement do + relations.map { |rel| rel.reorder(nil).to_sql }.reject(&:blank?) + end + + if fragments.any? + "(" + fragments.join(")\n#{operator_keyword_fragment}\n(") + ")" + else + 'NULL' + end + end + + # UNION [ALL] | INTERSECT [ALL] | EXCEPT [ALL] + def operator_keyword_fragment + remove_duplicates ? self.class.operator_keyword : "#{self.class.operator_keyword} ALL" + end + + private + + attr_reader :relations, :remove_duplicates + end + end +end diff --git a/lib/gitlab/sql/union.rb b/lib/gitlab/sql/union.rb index b15f2ca385a..7fb3487a5e5 100644 --- a/lib/gitlab/sql/union.rb +++ b/lib/gitlab/sql/union.rb @@ -13,30 +13,9 @@ module Gitlab # sql = union.to_sql # # Project.where("id IN (#{sql})") - class Union - def initialize(relations, remove_duplicates: true) - @relations = relations - @remove_duplicates = remove_duplicates - end - - def to_sql - # Some relations may include placeholders for prepared statements, these - # aren't incremented properly when joining relations together this way. - # By using "unprepared_statements" we remove the usage of placeholders - # (thus fixing this problem), at a slight performance cost. - fragments = ActiveRecord::Base.connection.unprepared_statement do - @relations.map { |rel| rel.reorder(nil).to_sql }.reject(&:blank?) - end - - if fragments.any? - "(" + fragments.join(")\n#{union_keyword}\n(") + ")" - else - 'NULL' - end - end - - def union_keyword - @remove_duplicates ? 'UNION' : 'UNION ALL' + class Union < SetOperator + def self.operator_keyword + 'UNION' end end end |