diff options
32 files changed, 514 insertions, 25 deletions
diff --git a/lib/sql_algebra.rb b/lib/sql_algebra.rb index 8389250755..5753a48d2f 100644 --- a/lib/sql_algebra.rb +++ b/lib/sql_algebra.rb @@ -28,6 +28,7 @@ require 'sql_algebra/predicates/relation_inclusion_predicate' require 'sql_algebra/predicates/match_predicate' require 'sql_algebra/extensions/range' +require 'sql_algebra/extensions/object' require 'sql_algebra/sql_builder/sql_builder_adapter' require 'sql_algebra/sql_builder/sql_builder' @@ -37,6 +38,6 @@ require 'sql_algebra/sql_builder/join_builder' require 'sql_algebra/sql_builder/inner_join_builder' require 'sql_algebra/sql_builder/left_outer_join_builder' require 'sql_algebra/sql_builder/equals_condition_builder' -require 'sql_algebra/sql_builder/column_builder' require 'sql_algebra/sql_builder/conditions_builder' - +require 'sql_algebra/sql_builder/order_builder' +require 'sql_algebra/sql_builder/selects_builder'
\ No newline at end of file diff --git a/lib/sql_algebra/predicates/binary_predicate.rb b/lib/sql_algebra/predicates/binary_predicate.rb index 3e5b9ce193..9463f162c5 100644 --- a/lib/sql_algebra/predicates/binary_predicate.rb +++ b/lib/sql_algebra/predicates/binary_predicate.rb @@ -12,7 +12,10 @@ class BinaryPredicate < Predicate def to_sql(builder = ConditionsBuilder.new) builder.call do - send(predicate_name, attribute1.to_sql(self), attribute2.to_sql(self)) + send(predicate_name) do + attribute1.to_sql(self) + attribute2.to_sql(self) + end end end end
\ No newline at end of file diff --git a/lib/sql_algebra/relations/attribute.rb b/lib/sql_algebra/relations/attribute.rb index af448286f1..85d40dfb12 100644 --- a/lib/sql_algebra/relations/attribute.rb +++ b/lib/sql_algebra/relations/attribute.rb @@ -1,8 +1,8 @@ class Attribute - attr_reader :relation, :attribute_name + attr_reader :relation, :attribute_name, :aliaz - def initialize(relation, attribute_name) - @relation, @attribute_name = relation, attribute_name + def initialize(relation, attribute_name, aliaz = nil) + @relation, @attribute_name, @aliaz = relation, attribute_name, aliaz end def eql?(other) @@ -33,7 +33,9 @@ class Attribute MatchPredicate.new(self, regexp) end - def to_sql(ignore_builder_because_i_can_only_exist_atomically = nil) - ColumnBuilder.new(relation.table, attribute_name) + def to_sql(builder = SelectsBuilder.new) + builder.call do + column relation.table, attribute_name, aliaz + end end end
\ No newline at end of file diff --git a/lib/sql_algebra/relations/inner_join_operation.rb b/lib/sql_algebra/relations/inner_join_operation.rb new file mode 100644 index 0000000000..6b5c5ce8d0 --- /dev/null +++ b/lib/sql_algebra/relations/inner_join_operation.rb @@ -0,0 +1,6 @@ +class InnerJoinOperation < JoinOperation + protected + def relation_class + InnerJoinRelation + end +end
\ No newline at end of file diff --git a/lib/sql_algebra/relations/inner_join_relation.rb b/lib/sql_algebra/relations/inner_join_relation.rb new file mode 100644 index 0000000000..1ef965a6f5 --- /dev/null +++ b/lib/sql_algebra/relations/inner_join_relation.rb @@ -0,0 +1,5 @@ +class InnerJoinRelation < JoinRelation + def join_name + :inner_join + end +end
\ No newline at end of file diff --git a/lib/sql_algebra/relations/left_outer_join_operation.rb b/lib/sql_algebra/relations/left_outer_join_operation.rb new file mode 100644 index 0000000000..fbb2a4e2ed --- /dev/null +++ b/lib/sql_algebra/relations/left_outer_join_operation.rb @@ -0,0 +1,6 @@ +class LeftOuterJoinOperation < JoinOperation + protected + def relation_class + LeftOuterJoinRelation + end +end
\ No newline at end of file diff --git a/lib/sql_algebra/relations/left_outer_join_relation.rb b/lib/sql_algebra/relations/left_outer_join_relation.rb new file mode 100644 index 0000000000..c7722c394d --- /dev/null +++ b/lib/sql_algebra/relations/left_outer_join_relation.rb @@ -0,0 +1,5 @@ +class LeftOuterJoinRelation < JoinRelation + def join_name + :left_outer_join + end +end
\ No newline at end of file diff --git a/lib/sql_algebra/relations/order_relation.rb b/lib/sql_algebra/relations/order_relation.rb index e3c29dcc27..384a007bc2 100644 --- a/lib/sql_algebra/relations/order_relation.rb +++ b/lib/sql_algebra/relations/order_relation.rb @@ -12,7 +12,9 @@ class OrderRelation < Relation def to_sql(builder = SelectBuilder.new) relation.to_sql(builder).call do attributes.each do |attribute| - order_by attribute.to_sql(self) + order_by do + attribute.to_sql(self) + end end end end diff --git a/lib/sql_algebra/relations/projection_relation.rb b/lib/sql_algebra/relations/projection_relation.rb index ca5f0bfca4..0b5d645d79 100644 --- a/lib/sql_algebra/relations/projection_relation.rb +++ b/lib/sql_algebra/relations/projection_relation.rb @@ -11,7 +11,9 @@ class ProjectionRelation < Relation def to_sql(builder = SelectBuilder.new) relation.to_sql(builder).call do - select attributes.collect { |a| a.to_sql(self) } + select do + attributes.collect { |a| a.to_sql(self) } + end end end end
\ No newline at end of file diff --git a/lib/sql_algebra/relations/selection_relation.rb b/lib/sql_algebra/relations/selection_relation.rb index cce93917c4..51461de7d2 100644 --- a/lib/sql_algebra/relations/selection_relation.rb +++ b/lib/sql_algebra/relations/selection_relation.rb @@ -17,4 +17,6 @@ class SelectionRelation < Relation end end end + + delegate :[], :to => :relation end
\ No newline at end of file diff --git a/lib/sql_algebra/relations/table_relation.rb b/lib/sql_algebra/relations/table_relation.rb index eee24e5d68..60bdfda8ee 100644 --- a/lib/sql_algebra/relations/table_relation.rb +++ b/lib/sql_algebra/relations/table_relation.rb @@ -7,7 +7,7 @@ class TableRelation < Relation def to_sql(builder = SelectBuilder.new) builder.call do - select :* + select { all } from table end end diff --git a/lib/sql_algebra/sql_builder/conditions_builder.rb b/lib/sql_algebra/sql_builder/conditions_builder.rb new file mode 100644 index 0000000000..5d42a36cec --- /dev/null +++ b/lib/sql_algebra/sql_builder/conditions_builder.rb @@ -0,0 +1,16 @@ +class ConditionsBuilder < SqlBuilder + def initialize(&block) + @conditions = [] + super(&block) + end + + def equals(&block) + @conditions << EqualsConditionBuilder.new(&block) + end + + def to_s + @conditions.join(' AND ') + end + + delegate :blank?, :to => :@conditions +end
\ No newline at end of file diff --git a/lib/sql_algebra/sql_builder/equals_condition_builder.rb b/lib/sql_algebra/sql_builder/equals_condition_builder.rb new file mode 100644 index 0000000000..016395556a --- /dev/null +++ b/lib/sql_algebra/sql_builder/equals_condition_builder.rb @@ -0,0 +1,18 @@ +class EqualsConditionBuilder < SqlBuilder + def initialize(&block) + @operands = [] + super(&block) + end + + def column(table, column, aliaz = nil) + @operands << (aliaz ? aliaz : "#{table}.#{column}") + end + + def value(value) + @operands << value + end + + def to_s + "#{@operands[0]} = #{@operands[1]}" + end +end
\ No newline at end of file diff --git a/lib/sql_algebra/sql_builder/inner_join_builder.rb b/lib/sql_algebra/sql_builder/inner_join_builder.rb new file mode 100644 index 0000000000..6aec703325 --- /dev/null +++ b/lib/sql_algebra/sql_builder/inner_join_builder.rb @@ -0,0 +1,5 @@ +class InnerJoinBuilder < JoinBuilder + def join_type + "INNER JOIN" + end +end
\ No newline at end of file diff --git a/lib/sql_algebra/sql_builder/join_builder.rb b/lib/sql_algebra/sql_builder/join_builder.rb new file mode 100644 index 0000000000..28f4437dec --- /dev/null +++ b/lib/sql_algebra/sql_builder/join_builder.rb @@ -0,0 +1,13 @@ +class JoinBuilder < SqlBuilder + def initialize(table, &block) + @table = table + @conditions = ConditionsBuilder.new + super(&block) + end + + delegate :call, :to => :@conditions + + def to_s + "#{join_type} #{@table} ON #{@conditions}" + end +end
\ No newline at end of file diff --git a/lib/sql_algebra/sql_builder/joins_builder.rb b/lib/sql_algebra/sql_builder/joins_builder.rb new file mode 100644 index 0000000000..36a92e9922 --- /dev/null +++ b/lib/sql_algebra/sql_builder/joins_builder.rb @@ -0,0 +1,18 @@ +class JoinsBuilder < SqlBuilder + def initialize(&block) + @joins = [] + super(&block) + end + + def inner_join(table, &block) + @joins << InnerJoinBuilder.new(table, &block) + end + + def left_outer_join(table, &block) + @joins << LeftOuterJoinBuilder.new(table, &block) + end + + def to_s + @joins.join(' ') + end +end
\ No newline at end of file diff --git a/lib/sql_algebra/sql_builder/left_outer_join_builder.rb b/lib/sql_algebra/sql_builder/left_outer_join_builder.rb new file mode 100644 index 0000000000..dad3f85810 --- /dev/null +++ b/lib/sql_algebra/sql_builder/left_outer_join_builder.rb @@ -0,0 +1,5 @@ +class LeftOuterJoinBuilder < JoinBuilder + def join_type + "LEFT OUTER JOIN" + end +end
\ No newline at end of file diff --git a/lib/sql_algebra/sql_builder/select_builder.rb b/lib/sql_algebra/sql_builder/select_builder.rb new file mode 100644 index 0000000000..d4eb4feb56 --- /dev/null +++ b/lib/sql_algebra/sql_builder/select_builder.rb @@ -0,0 +1,63 @@ +class SelectBuilder < SqlBuilder + def select(&block) + @selects = SelectsBuilder.new(&block) + end + + def from(table, &block) + @table = table + @joins = JoinsBuilder.new(&block) + end + delegate :inner_join, :left_outer_join, :to => :@joins + + def where(&block) + @conditions ||= ConditionsBuilder.new + @conditions.call(&block) + end + + def order_by(&block) + @orders = OrderBuilder.new(&block) + end + + def limit(i, offset = nil) + @limit = i + offset(offset) if offset + end + + def offset(i) + @offset = i + end + + def to_s + [select_clause, + from_clause, + where_clause, + order_by_clause, + limit_clause, + offset_clause].compact.join("\n") + end + + private + def select_clause + "SELECT #{@selects}" unless @selects.blank? + end + + def from_clause + "FROM #{@table} #{@joins}" unless @table.blank? + end + + def where_clause + "WHERE #{@conditions}" unless @conditions.blank? + end + + def order_by_clause + "ORDER BY #{@orders}" unless @orders.blank? + end + + def limit_clause + "LIMIT #{@limit}" unless @limit.blank? + end + + def offset_clause + "OFFSET #{@offset}" unless @offset.blank? + end +end
\ No newline at end of file diff --git a/lib/sql_algebra/sql_builder/sql_builder.rb b/lib/sql_algebra/sql_builder/sql_builder.rb new file mode 100644 index 0000000000..5cfbe578d5 --- /dev/null +++ b/lib/sql_algebra/sql_builder/sql_builder.rb @@ -0,0 +1,28 @@ +class SqlBuilder + def initialize(&block) + @callers = [] + call(&block) if block + end + + def method_missing(method, *args) + @callers.last.send(method, *args) + end + + def ==(other) + to_s == other.to_s + end + + def to_s + end + + def call(&block) + returning self do |builder| + @callers << eval("self", block.binding) + begin + instance_eval &block + ensure + @callers.pop + end + end + end +end
\ No newline at end of file diff --git a/lib/sql_algebra/sql_builder/sql_builder_adapter.rb b/lib/sql_algebra/sql_builder/sql_builder_adapter.rb new file mode 100644 index 0000000000..9bb5271f33 --- /dev/null +++ b/lib/sql_algebra/sql_builder/sql_builder_adapter.rb @@ -0,0 +1,22 @@ +class SqlBuilderAdapter + instance_methods.each { |m| undef_method m unless m =~ /^__|^instance_eval|class/ } + + def initialize(adaptee, &block) + @adaptee = adaptee + (class << self; self end).class_eval do + (adaptee.methods - instance_methods).each { |m| delegate m, :to => :@adaptee } + end + (class << self; self end).instance_exec(@adaptee, &block) + end + + def call(&block) + @caller = eval("self", block.binding) + returning self do |adapter| + instance_eval(&block) + end + end + + def method_missing(method, *args, &block) + @caller.send(method, *args, &block) + end +end
\ No newline at end of file diff --git a/spec/integration/scratch_spec.rb b/spec/integration/scratch_spec.rb new file mode 100644 index 0000000000..6426d2478d --- /dev/null +++ b/spec/integration/scratch_spec.rb @@ -0,0 +1,37 @@ +require File.join(File.dirname(__FILE__), '..', 'spec_helper') + +describe 'Relational Algebra' do + before do + User = TableRelation.new(:users) + Photo = TableRelation.new(:photos) + Camera = TableRelation.new(:cameras) + user = User.select(User[:id] == 1) + @user_photos = (user << Photo).on(user[:id] == Photo[:user_id]) + end + + it 'simulates User.has_many :photos' do + @user_photos.to_sql.should == SelectBuilder.new do + select { all } + from :users do + left_outer_join :photos do + equals { column :users, :id; column :photos, :user_id } + end + end + where do + equals { column :users, :id; value 1 } + end + end + @user_photos.to_sql.to_s.should be_like(""" + SELECT * + FROM users + LEFT OUTER JOIN photos + ON users.id = photos.user_id + WHERE + users.id = 1 + """) + end + + it 'simulating a User.has_many :cameras :through => :photos' do + user_cameras = (@user_photos << Camera).on(@user_photos[:camera_id] == Camera[:id]) + end +end
\ No newline at end of file diff --git a/spec/predicates/binary_predicate_spec.rb b/spec/predicates/binary_predicate_spec.rb index 3d9a9b3b94..a044e43a84 100644 --- a/spec/predicates/binary_predicate_spec.rb +++ b/spec/predicates/binary_predicate_spec.rb @@ -32,9 +32,12 @@ describe BinaryPredicate do end describe '#to_sql' do - it '' do + it 'manufactures correct sql' do ConcreteBinaryPredicate.new(@attribute1, @attribute2).to_sql.should == ConditionsBuilder.new do - equals 'foo.attribute_name1', 'bar.attribute_name2' + equals do + column :foo, :attribute_name1 + column :bar, :attribute_name2 + end end end end diff --git a/spec/relations/attribute_spec.rb b/spec/relations/attribute_spec.rb index 78d602abf9..5ddbaa96b5 100644 --- a/spec/relations/attribute_spec.rb +++ b/spec/relations/attribute_spec.rb @@ -59,7 +59,9 @@ describe Attribute do describe '#to_sql' do it "manufactures a column" do - Attribute.new(@relation1, :attribute_name).to_sql.should == ColumnBuilder.new(@relation1.table, :attribute_name) + Attribute.new(@relation1, :attribute_name, :alias).to_sql.should == SelectsBuilder.new do + column :foo, :attribute_name, :alias + end end end end diff --git a/spec/relations/join_relation_spec.rb b/spec/relations/join_relation_spec.rb index c6fae90ff3..a7c15fd76a 100644 --- a/spec/relations/join_relation_spec.rb +++ b/spec/relations/join_relation_spec.rb @@ -30,14 +30,20 @@ describe 'between two relations' do it 'manufactures sql joining the two tables on the predicate, merging the selects' do ConcreteJoinRelation.new(@relation1, @relation2, @predicate).to_sql.to_s.should == SelectBuilder.new do - select :* + select { all } from :foo do inner_join :bar do - equals 'foo.a', 'bar.b' + equals do + column :foo, :a + column :bar, :b + end end end where do - equals 'foo.c', 'bar.d' + equals do + column :foo, :c + column :bar, :d + end end end.to_s end diff --git a/spec/relations/order_relation_spec.rb b/spec/relations/order_relation_spec.rb index 4f7a18fc8e..8050aa981c 100644 --- a/spec/relations/order_relation_spec.rb +++ b/spec/relations/order_relation_spec.rb @@ -16,12 +16,14 @@ describe OrderRelation do end end - describe '#to_s' do + describe '#to_sql' do it "manufactures sql with an order clause" do OrderRelation.new(@relation1, @attribute1).to_sql.should == SelectBuilder.new do - select :* + select { all } from :foo - order_by 'foo.foo' + order_by do + column :foo, :foo + end end end end diff --git a/spec/relations/projection_relation_spec.rb b/spec/relations/projection_relation_spec.rb index ba5620dcde..f17f57df7b 100644 --- a/spec/relations/projection_relation_spec.rb +++ b/spec/relations/projection_relation_spec.rb @@ -19,7 +19,9 @@ describe ProjectionRelation do describe '#to_sql' do it "manufactures sql with a limited select clause" do ProjectionRelation.new(@relation1, @attribute1).to_sql.should == SelectBuilder.new do - select 'foo.foo' + select do + column :foo, :foo + end from :foo end end diff --git a/spec/relations/range_relation_spec.rb b/spec/relations/range_relation_spec.rb index fc7094c873..e6caa32e80 100644 --- a/spec/relations/range_relation_spec.rb +++ b/spec/relations/range_relation_spec.rb @@ -21,7 +21,7 @@ describe RangeRelation do range_size = @range2.last - @range2.first + 1 range_start = @range2.first RangeRelation.new(@relation1, @range2).to_sql.to_s.should == SelectBuilder.new do - select :* + select { all } from :foo limit range_size offset range_start diff --git a/spec/relations/selection_relation_spec.rb b/spec/relations/selection_relation_spec.rb index 1f8b760272..ceb771b46d 100644 --- a/spec/relations/selection_relation_spec.rb +++ b/spec/relations/selection_relation_spec.rb @@ -29,10 +29,13 @@ describe SelectionRelation do describe '#to_sql' do it "manufactures sql with where clause conditions" do SelectionRelation.new(@relation1, @predicate1).to_sql.should == SelectBuilder.new do - select :* + select { all } from :foo where do - equals 'foo.id', 'bar.foo_id' + equals do + column :foo, :id + column :bar, :foo_id + end end end end diff --git a/spec/relations/table_relation_spec.rb b/spec/relations/table_relation_spec.rb index a0647aa541..7a820782df 100644 --- a/spec/relations/table_relation_spec.rb +++ b/spec/relations/table_relation_spec.rb @@ -3,7 +3,7 @@ require File.join(File.dirname(__FILE__), '..', 'spec_helper') describe TableRelation, '#to_sql' do it "returns a simple SELECT query" do TableRelation.new(:users).to_sql.should == SelectBuilder.new do |s| - select :* + select { all } from :users end end diff --git a/spec/spec_helpers/be_like.rb b/spec/spec_helpers/be_like.rb new file mode 100644 index 0000000000..cea3f3027b --- /dev/null +++ b/spec/spec_helpers/be_like.rb @@ -0,0 +1,24 @@ +module BeLikeMatcher + class BeLike + def initialize(expected) + @expected = expected + end + + def matches?(target) + @target = target + @expected.gsub(/\s+/, ' ').strip == @target.gsub(/\s+/, ' ').strip + end + + def failure_message + "expected #{@target} to be like #{@expected}" + end + + def negative_failure_message + "expected #{@target} to be unlike #{@expected}" + end + end + + def be_like(expected) + BeLike.new(expected) + end +end
\ No newline at end of file diff --git a/spec/sql_builder/conditions_spec.rb b/spec/sql_builder/conditions_spec.rb new file mode 100644 index 0000000000..78590e2631 --- /dev/null +++ b/spec/sql_builder/conditions_spec.rb @@ -0,0 +1,18 @@ +require File.join(File.dirname(__FILE__), '..', 'spec_helper') + +describe ConditionsBuilder do + describe '#to_s' do + describe 'with aliased columns' do + it 'manufactures correct sql' do + ConditionsBuilder.new do + equals do + column(:a, :b) + column(:c, :d, :e) + end + end.to_s.should be_like(""" + a.b = e + """) + end + end + end +end
\ No newline at end of file diff --git a/spec/sql_builder/select_builder_spec.rb b/spec/sql_builder/select_builder_spec.rb new file mode 100644 index 0000000000..39597b0392 --- /dev/null +++ b/spec/sql_builder/select_builder_spec.rb @@ -0,0 +1,170 @@ +require File.join(File.dirname(__FILE__), '..', 'spec_helper') + +describe SelectBuilder do + describe '#to_s' do + describe 'with select and from clauses' do + it 'manufactures correct sql' do + SelectBuilder.new do + select do + all + end + from :users + end.to_s.should be_like(""" + SELECT * + FROM users + """) + end + end + + describe 'with specified columns and column aliases' do + it 'manufactures correct sql' do + SelectBuilder.new do + select do + column(:a, :b, :c) + column(:e, :f) + end + from :users + end.to_s.should be_like(""" + SELECT a.b AS c, e.f + FROM users + """) + end + end + + describe 'with where clause' do + it 'manufactures correct sql' do + SelectBuilder.new do + select do + all + end + from :users + where do + equals do + value :a + column :b, :c + end + end + end.to_s.should be_like(""" + SELECT * + FROM users + WHERE a = b.c + """) + end + end + + describe 'with inner join' do + it 'manufactures correct sql' do + SelectBuilder.new do + select do + all + end + from :users do + inner_join(:friendships) do + equals do + value :id + value :user_id + end + end + end + end.to_s.should be_like(""" + SELECT * + FROM users INNER JOIN friendships ON id = user_id + """) + end + end + + describe 'with order' do + it 'manufactures correct sql' do + SelectBuilder.new do + select do + all + end + from :users + order_by do + column :users, :id + column :users, :created_at, :alias + end + end.to_s.should be_like(""" + SELECT * + FROM users + ORDER BY users.id, alias + """) + end + end + + describe 'with limit and/or offset' do + it 'manufactures correct sql' do + SelectBuilder.new do + select do + all + end + from :users + limit 10 + offset 10 + end.to_s.should be_like(""" + SELECT * + FROM users + LIMIT 10 + OFFSET 10 + """) + end + end + + describe 'repeated clauses' do + describe 'with repeating joins' do + it 'manufactures correct sql' do + SelectBuilder.new do + select do + all + end + from :users do + inner_join(:friendships) do + equals do + value :id + value :user_id + end + end + end + inner_join(:pictures) do + equals do + value :id + value :user_id + end + end + end.to_s.should be_like(""" + SELECT * + FROM users INNER JOIN friendships ON id = user_id INNER JOIN pictures ON id = user_id + """) + end + end + + describe 'with repeating wheres' do + it 'manufactures correct sql' do + SelectBuilder.new do + select do + all + end + from :users + where do + equals do + value :a + value :b + end + end + where do + equals do + value :b + value :c + end + end + end.to_s.should be_like(""" + SELECT * + FROM users + WHERE a = b + AND b = c + """) + end + end + end + end +end
\ No newline at end of file |