diff options
author | Nick Kallen <nkallen@nick-kallens-computer-2.local> | 2008-01-16 22:55:06 -0800 |
---|---|---|
committer | Nick Kallen <nkallen@nick-kallens-computer-2.local> | 2008-01-16 22:55:06 -0800 |
commit | b47ac80a17d7a74e1b5deac0e63a72960a4da453 (patch) | |
tree | de13e27979148c8a39c396298cc789348d4acb2e | |
parent | b5a2057fcd5dfcac3682a565f2ba15281f5dcbb2 (diff) | |
download | rails-b47ac80a17d7a74e1b5deac0e63a72960a4da453.tar.gz rails-b47ac80a17d7a74e1b5deac0e63a72960a4da453.tar.bz2 rails-b47ac80a17d7a74e1b5deac0e63a72960a4da453.zip |
adding grouping functionality; added some dummy code ("Schmoin") for experimenting with aggregate joins. need to resolve the ambiguity in the #as operator between (SELECT * FROM foo AS bar) vs. (SELECT * FROM foo) AS bar
-rw-r--r-- | lib/active_relation/primitives/aggregation.rb | 4 | ||||
-rw-r--r-- | lib/active_relation/primitives/attribute.rb | 21 | ||||
-rw-r--r-- | lib/active_relation/relations.rb | 2 | ||||
-rw-r--r-- | lib/active_relation/relations/alias.rb | 9 | ||||
-rw-r--r-- | lib/active_relation/relations/compound.rb | 3 | ||||
-rw-r--r-- | lib/active_relation/relations/group.rb | 17 | ||||
-rw-r--r-- | lib/active_relation/relations/order.rb | 2 | ||||
-rw-r--r-- | lib/active_relation/relations/relation.rb | 6 | ||||
-rw-r--r-- | lib/active_relation/relations/schmoin.rb | 43 | ||||
-rw-r--r-- | spec/active_relation/primitives/aggregation_spec.rb | 15 | ||||
-rw-r--r-- | spec/active_relation/primitives/attribute_spec.rb | 6 | ||||
-rw-r--r-- | spec/active_relation/relations/group_spec.rb | 29 | ||||
-rw-r--r-- | spec/active_relation/relations/relation_spec.rb | 8 | ||||
-rw-r--r-- | spec/active_relation/relations/schmoin_spec.rb | 24 | ||||
-rw-r--r-- | spec/spec_helper.rb | 2 |
15 files changed, 169 insertions, 22 deletions
diff --git a/lib/active_relation/primitives/aggregation.rb b/lib/active_relation/primitives/aggregation.rb index 48f8946835..26348fb35e 100644 --- a/lib/active_relation/primitives/aggregation.rb +++ b/lib/active_relation/primitives/aggregation.rb @@ -6,6 +6,10 @@ module ActiveRelation @attribute, @function_sql = attribute, function_sql end + def substitute(new_relation) + Aggregation.new(attribute.substitute(new_relation), function_sql) + end + def to_sql(strategy = nil) "#{function_sql}(#{attribute.to_sql})" end diff --git a/lib/active_relation/primitives/attribute.rb b/lib/active_relation/primitives/attribute.rb index 90bbe8012b..eb167b1a66 100644 --- a/lib/active_relation/primitives/attribute.rb +++ b/lib/active_relation/primitives/attribute.rb @@ -6,18 +6,25 @@ module ActiveRelation @relation, @name, @alias = relation, name, aliaz end - def as(aliaz = nil) - Attribute.new(relation, name, aliaz) - end + module Transformations + def as(aliaz = nil) + Attribute.new(relation, name, aliaz) + end + + def substitute(new_relation) + Attribute.new(new_relation, name, @alias) + end + def qualify + self.as(qualified_name) + end + end + include Transformations + def qualified_name "#{relation.name}.#{name}" end - def qualify - self.as(qualified_name) - end - def ==(other) relation == other.relation and name == other.name and @alias == other.alias end diff --git a/lib/active_relation/relations.rb b/lib/active_relation/relations.rb index 9ae8f16524..c9b9de93b2 100644 --- a/lib/active_relation/relations.rb +++ b/lib/active_relation/relations.rb @@ -2,6 +2,8 @@ require 'active_relation/relations/relation' require 'active_relation/relations/compound' require 'active_relation/relations/table' require 'active_relation/relations/join' +require 'active_relation/relations/schmoin' +require 'active_relation/relations/group' require 'active_relation/relations/projection' require 'active_relation/relations/selection' require 'active_relation/relations/order' diff --git a/lib/active_relation/relations/alias.rb b/lib/active_relation/relations/alias.rb index 63a92ccba1..f40e51475e 100644 --- a/lib/active_relation/relations/alias.rb +++ b/lib/active_relation/relations/alias.rb @@ -8,7 +8,7 @@ module ActiveRelation end def attributes - relation.attributes.collect(&method(:substitute)) + relation.attributes.collect { |attribute| attribute.substitute(self) } end def ==(other) @@ -22,13 +22,8 @@ module ActiveRelation def attribute(name) if unaliased_attribute = relation[name] - substitute(unaliased_attribute) + unaliased_attribute.substitute(self) end end - - private - def substitute(attribute) - Attribute.new(self, attribute.name, attribute.alias) - end end end
\ No newline at end of file diff --git a/lib/active_relation/relations/compound.rb b/lib/active_relation/relations/compound.rb index d6da8eb8fa..bbc7ff1bae 100644 --- a/lib/active_relation/relations/compound.rb +++ b/lib/active_relation/relations/compound.rb @@ -2,6 +2,7 @@ module ActiveRelation class Compound < Relation attr_reader :relation - delegate :attributes, :attribute, :joins, :selects, :orders, :table_sql, :inserts, :limit, :offset, :to => :relation + delegate :attributes, :attribute, :joins, :selects, :orders, :groupings, :table_sql, :inserts, :limit, :offset, + :to => :relation end end
\ No newline at end of file diff --git a/lib/active_relation/relations/group.rb b/lib/active_relation/relations/group.rb new file mode 100644 index 0000000000..bc363970c7 --- /dev/null +++ b/lib/active_relation/relations/group.rb @@ -0,0 +1,17 @@ +module ActiveRelation + class Group < Compound + attr_reader :groupings + + def initialize(relation, *groupings) + @relation, @groupings = relation, groupings + end + + def ==(other) + relation == other.relation and groupings == other.groupings + end + + def qualify + Group.new(relation.qualify, *groupings.collect(&:qualify)) + end + end +end
\ No newline at end of file diff --git a/lib/active_relation/relations/order.rb b/lib/active_relation/relations/order.rb index d5c2a54cd1..688207f7a9 100644 --- a/lib/active_relation/relations/order.rb +++ b/lib/active_relation/relations/order.rb @@ -11,7 +11,7 @@ module ActiveRelation end def qualify - Order.new(relation.qualify, *orders.collect { |o| o.qualify }) + Order.new(relation.qualify, *orders.collect(&:qualify)) end end end
\ No newline at end of file diff --git a/lib/active_relation/relations/relation.rb b/lib/active_relation/relations/relation.rb index 663450e69c..18ba5491b1 100644 --- a/lib/active_relation/relations/relation.rb +++ b/lib/active_relation/relations/relation.rb @@ -64,6 +64,10 @@ module ActiveRelation def delete Deletion.new(self) end + + def group(*attributes) + Group.new(self, *attributes) + end JoinOperation = Struct.new(:join_sql, :relation1, :relation2) do def on(*predicates) @@ -80,6 +84,7 @@ module ActiveRelation (joins unless joins.blank?), ("WHERE #{selects.collect{|s| s.to_sql(Sql::Predicate.new)}.join("\n\tAND ")}" unless selects.blank?), ("ORDER BY #{orders.collect(&:to_sql)}" unless orders.blank?), + ("GROUP BY #{groupings.collect(&:to_sql)}" unless groupings.blank?), ("LIMIT #{limit.to_sql}" unless limit.blank?), ("OFFSET #{offset.to_sql}" unless offset.blank?) ].compact.join("\n") @@ -95,6 +100,7 @@ module ActiveRelation def selects; [] end def orders; [] end def inserts; [] end + def groupings; [] end def joins; nil end def limit; nil end def offset; nil end diff --git a/lib/active_relation/relations/schmoin.rb b/lib/active_relation/relations/schmoin.rb new file mode 100644 index 0000000000..398cfb7b28 --- /dev/null +++ b/lib/active_relation/relations/schmoin.rb @@ -0,0 +1,43 @@ +module ActiveRelation + class Schmoin < Relation + attr_reader :join_sql, :relation1, :relation2, :predicates + + def initialize(join_sql, relation1, relation2, *predicates) + @join_sql, @relation1, @relation2, @predicates = join_sql, relation1, relation2, predicates + end + + def ==(other) + predicates == other.predicates and + ((relation1 == other.relation1 and relation2 == other.relation2) or + (relation2 == other.relation1 and relation1 == other.relation2)) + end + + def qualify + Schmoin.new(join_sql, relation1.qualify, relation2.qualify, *predicates.collect(&:qualify)) + end + + protected + def joins + [relation1.joins, relation2.joins, join].compact.join(" ") + end + + def selects + relation1.send(:selects) + relation2.send(:selects) + end + + def attributes + relation1.attributes + relation2.attributes + end + + def attribute(name) + relation1[name] || relation2[name] + end + + delegate :table_sql, :to => :relation1 + + private + def join + "#{join_sql} #{relation2.send(:table_sql)} ON #{predicates.collect { |p| p.to_sql(Sql::Predicate.new) }.join(' AND ')}" + end + end +end
\ No newline at end of file diff --git a/spec/active_relation/primitives/aggregation_spec.rb b/spec/active_relation/primitives/aggregation_spec.rb index bdfa152bca..fbd846ffed 100644 --- a/spec/active_relation/primitives/aggregation_spec.rb +++ b/spec/active_relation/primitives/aggregation_spec.rb @@ -9,10 +9,17 @@ module ActiveRelation describe '==' do it 'obtains if the attribute and function sql are identical' do - @relation1[:id].sum.should == @relation1[:id].sum - @relation1[:id].sum.should_not == @relation1[:name].sum - @relation1[:id].sum.should_not == @relation1[:name].average - @relation1[:id].sum.should_not == @relation2[:id].sum + Aggregation.new(@relation1[:id], "SUM").should == Aggregation.new(@relation1[:id], "SUM") + Aggregation.new(@relation1[:id], "SUM").should_not == Aggregation.new(@relation1[:name], "SUM") + Aggregation.new(@relation1[:id], "SUM").should_not == Aggregation.new(@relation1[:name], "SUM") + Aggregation.new(@relation1[:id], "SUM").should_not == Aggregation.new(@relation2[:id], "SUM") + end + end + + describe '#substitute' do + it "distributes over the attribute" do + Aggregation.new(@relation1[:id], "SUM").substitute(@relation2). \ + should == Aggregation.new(@relation1[:id].substitute(@relation2), "SUM") end end diff --git a/spec/active_relation/primitives/attribute_spec.rb b/spec/active_relation/primitives/attribute_spec.rb index 6078da08f3..543217b9cf 100644 --- a/spec/active_relation/primitives/attribute_spec.rb +++ b/spec/active_relation/primitives/attribute_spec.rb @@ -12,6 +12,12 @@ module ActiveRelation @relation1[:id].as(:alias).should == Attribute.new(@relation1, :id, :alias) end end + + describe '#substitute' do + it "manufactures an attribute with the relation substituted" do + @relation1[:id].substitute(@relation2).should == Attribute.new(@relation2, :id) + end + end describe '#qualified_name' do it "manufactures an attribute name prefixed with the relation's name" do diff --git a/spec/active_relation/relations/group_spec.rb b/spec/active_relation/relations/group_spec.rb new file mode 100644 index 0000000000..256d7dde45 --- /dev/null +++ b/spec/active_relation/relations/group_spec.rb @@ -0,0 +1,29 @@ +require File.join(File.dirname(__FILE__), '..', '..', 'spec_helper') + +module ActiveRelation + describe Group do + before do + @relation1 = Table.new(:foo) + @relation2 = Table.new(:bar) + @attribute1 = @relation1[:id] + @attribute2 = @relation2[:id] + end + + describe '#qualify' do + it "distributes over the relation and attributes" do + Group.new(@relation1, @attribute1).qualify. \ + should == Group.new(@relation1.qualify, @attribute1.qualify) + end + end + + describe '#to_sql' do + it "manufactures sql with an order clause" do + Group.new(@relation1, @attribute1).to_sql.should be_like(""" + SELECT `foo`.`name`, `foo`.`id` + FROM `foo` + GROUP BY `foo`.`id` + """) + end + end + end +end diff --git a/spec/active_relation/relations/relation_spec.rb b/spec/active_relation/relations/relation_spec.rb index 72b7d14389..1b4ab785b7 100644 --- a/spec/active_relation/relations/relation_spec.rb +++ b/spec/active_relation/relations/relation_spec.rb @@ -82,7 +82,13 @@ module ActiveRelation describe '#order' do it "manufactures an order relation" do - @relation1.order(@attribute1, @attribute2).should be_kind_of(Order) + @relation1.order(@attribute1, @attribute2).should == Order.new(@relation1, @attribute1, @attribute2) + end + end + + describe '#group' do + it 'manufactures a group relation' do + @relation1.group(@attribute1).should == Group.new(@relation1, @attribute1) end end end diff --git a/spec/active_relation/relations/schmoin_spec.rb b/spec/active_relation/relations/schmoin_spec.rb new file mode 100644 index 0000000000..14e583afab --- /dev/null +++ b/spec/active_relation/relations/schmoin_spec.rb @@ -0,0 +1,24 @@ +require File.join(File.dirname(__FILE__), '..', '..', 'spec_helper') + +module ActiveRelation + describe Schmoin do + before do + @relation = Table.new(:users) + photos = Table.new(:photos) + @aggregate_relation = photos.project(photos[:user_id], photos[:id].count).group(photos[:user_id]).as(:photo_count) + @predicate = Equality.new(@aggregate_relation[:user_id], @relation[:id]) + end + + describe '#to_sql' do + it 'manufactures sql joining the two tables on the predicate, merging the selects' do + pending + Schmoin.new("INNER JOIN", @relation, @aggregate_relation, @predicate).to_sql.should be_like(""" + SELECT `users`.`name` + FROM `users` + INNER JOIN (SELECT `photos`.`user_id`, count(`photos`.`id`) FROM `photos`) AS `photo_count` + ON `photo_count`.`user_id` = `users`.`id` + """) + end + end + end +end
\ No newline at end of file diff --git a/spec/spec_helper.rb b/spec/spec_helper.rb index ab9a14f3bd..8f5d76370c 100644 --- a/spec/spec_helper.rb +++ b/spec/spec_helper.rb @@ -9,7 +9,7 @@ ActiveRecord::Base.configurations = { 'sql_algebra_test' => { :adapter => 'mysql', :username => 'root', - :password => '', + :password => 'password', :encoding => 'utf8', :database => 'sql_algebra_test', }, |