dydx/SQLDSL

Fork of Jay Fields' SQLDSL

= SQL DSL

SQL DSL is a library for creating SQL statements using ruby code.

by Jay[http://blog.jayfields.com] Fields[http://blog.jayfields.com]

== Download and Installation

You can download SQL DSL from here[http://rubyforge.org/projects/sqldsl] or install it with the following command.

 $ gem install sqldsl

== License
 	
You may use, copy and redistribute this library under the same terms as Ruby itself (see http://www.ruby-lang.org/en/LICENSE.txt).

== Examples

See the tests for more examples

=== Test Helper

    require 'test/unit'
    require File.dirname(__FILE__) + '/../lib/sqldsl'
    unless File.directory? File.dirname(__FILE__) + '/../vendor/mocha-0.4.0/'
      raise "mocha 4.0 is required to run the test suite. create the 'vendor' directory as a sibling of test and 'gem unpack mocha' in 'vendor'"
    end
    $:.unshift File.dirname(__FILE__) + '/../vendor/mocha-0.4.0/lib/'
    require File.dirname(__FILE__) + '/../vendor/mocha-0.4.0/lib/mocha'


=== Insert Example

    require File.dirname(__FILE__) + '/test_helper'
    
    class InsertAcceptanceTest < Test::Unit::TestCase
      def test_insert_select
        statement = Insert.into[:table1][:column1, :column2, :column3].values do
          Select[1]
        end
        assert_equal 'insert into table1 (column1, column2, column3) select 1', statement.to_sql
      end
      
      def test_insert_with_values
        statement = Insert.into[:table1][:column1, :column2, :column3].values(10, 'book', :column4)
        assert_equal "insert into table1 (column1, column2, column3) values (10, 'book', column4)", statement.to_sql
      end
    end

=== Update Example

    require File.dirname(__FILE__) + '/test_helper'
    
    class UpdateAcceptanceTest < Test::Unit::TestCase
      def test_insert_select
        statement = Update[:table1].set[:column1=>10, :column2=>'book'].where do
          not_exists(Select[1].from[:table2])
        end
        assert_equal "update table1 set column1=10, column2='book' where not exists (select 1 from table2)", statement.to_sql
      end
    end

=== Delete Example

    require File.dirname(__FILE__) + '/test_helper'
    
    class DeleteAcceptanceTest < Test::Unit::TestCase
      def test_insert_select
        statement = Delete.from[:table1].where do
          exists(Select[1].from[:table2])
        end
        assert_equal "delete from table1 where exists (select 1 from table2)", statement.to_sql
      end
    end

=== Select Example

    require File.dirname(__FILE__) + '/test_helper'
    
    class SelectAcceptanceTest < Test::Unit::TestCase
      def test_select_with_where_methods
        statement = Select[:column1, 'book', 10].from[:table1, :table2].where do
          equal :column1, 99
          not_equal :column1, 100
          less_than :column2, 'foo'
          less_than_or_equal :column3, :column4
          greater_than :column1, 0
          greater_than_or_equal :column2, 'bar'
          like :column1, 'any'
          is_not_null :column1
          is_in :column1, [1,2]
          is_not_in :column2, [3, 4]
          exists 0
          not_exists 0
        end
        expected = "select column1, 'book', 10 from table1, table2
                      where column1 = 99 and column1 <> 100 and column2 < 'foo' 
                      and column3 <= column4 and column1 > 0 and column2 >= 'bar' and column1 like 'any' 
                      and column1 is not null and column1 in (1, 2) and column2 not in (3, 4) 
                      and exists (0) and not exists (0)"
        assert_equal expected.delete("\n").squeeze(" "), statement.to_sql
      end
      
      def test_select_with_receive_any_objects_and_operators
        statement = Select[:column1, 'book', 10].from[:table1, :table2].where do
          column1 == 99
          column1 <=> 100
          column2 < 'foo'
          column3 <= column4
          column1 > 0
          column2 >= 'bar'
          column1 =~ 'any'
          column1 ^ nil
          column1 >> [1,2]
          column2 << [3, 4]
        end
        expected = "select column1, 'book', 10 from table1, table2
                      where column1 = 99 and column1 <> 100 and column2 < 'foo' 
                      and column3 <= column4 and column1 > 0 and column2 >= 'bar' and column1 like 'any'
                      and column1 is not null and column1 in (1, 2) and column2 not in (3, 4)"
        assert_equal expected.delete("\n").squeeze(" "), statement.to_sql
      end
      
      def test_select_with_receive_any_objects_and_method_calls
        statement = Select[:column1, 'book', 10].from[:table1, :table2].where do
          column1.equal 99
          column1.not_equal 100
          column2.less_than 'foo'
          column3.less_than_or_equal column4
          column1.greater_than 0
          column2.greater_than_or_equal 'bar'
          column1.like 'any'
          column1.is_not_null
          column1.is_in [1,2]
          column2.is_not_in [3, 4]
        end
        expected = "select column1, 'book', 10 from table1, table2
                      where column1 = 99 and column1 <> 100 and column2 < 'foo' 
                      and column3 <= column4 and column1 > 0 and column2 >= 'bar' and column1 like 'any'
                      and column1 is not null and column1 in (1, 2) and column2 not in (3, 4)"
        assert_equal expected.delete("\n").squeeze(" "), statement.to_sql
      end
      
      def test_add_clause
        statement = Select[:column1].from[:table1].where do
          like :column1, "any"
          add_clause "(c2 = 'foo')"
        end
        assert_equal "select column1 from table1 where column1 like 'any' and (c2 = 'foo')", statement.to_sql
      end
      
      
      def test_select_with_receive_any_objects_and_method_calls
        statement = Select[:column1, 'book', 10].from[:table1, :table2].where do
          column1.equal 0
        end.or do
          column1 > 100
        end.and do
          column2 == 15
        end
        expected = "select column1, 'book', 10 from table1, table2 where column1 = 0 or (column1 > 100) and (column2 = 15)"
        assert_equal expected, statement.to_sql
      end
      
      def test_select_with_inner_join
        expected = "select * from t1 a inner join t2 b, t3 c on a.id = b.id and b.id2 = c.id where c.attr1 = 'foo' and b.attr1 = 'foo2'"
        statement = Select.all.from[:t1.as(:a)].inner_join[:t2.as(:b), :t3.as(:c)].on do
          a.id == b.id
          b.id2 == c.id
        end.where do
          c.attr1 == 'foo'
          b.attr1 == 'foo2'
        end
        assert_equal expected, statement.to_sql
      end
      
      def test_columns_in_inner_where_are_validated_against_outer_tables
        statement = Select.all.from[:table].where do
          exists(Select.all.from[:inner_table.as(:aliased)].where do
            table.column1 == aliased.column1
          end)
        end
        assert_equal 'select * from table where exists (select * from inner_table aliased where table.column1 = aliased.column1)', statement.to_sql
      end
    
      def test_columns_in_where_are_validated_against_tables
        assert_raises ArgumentError do
          Select.all.from[:table].where do
            not_table.cat = 12
          end
        end
      end
    
      def test_columns_in_inner_where_are_validated_against_outer_and_inner_tables
        assert_raises ArgumentError do
          Select.all.from[:table].where do
            exists(Select.all.from[:inner_table].where do
              table.column1 = not_table.cat
            end)
          end
        end
      end
    end

=== Contributors
Matt[http://www.theagiledeveloper.com] Deiters[http://www.theagiledeveloper.com]

Sergio[http://spejman-on-rails.blogspot.com/] Espeja[http://spejman-on-rails.blogspot.com/]

Pat Farley (who contributed by being sqldsl's biggest skeptic)

Clint Bishop

Shane Harvie