integration test

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