sqlconstructor-ruby

SQLConstructor - Ruby gem for constructing SQL queries via object-oriented interface. Currently only MySQL dialect is supported, but hopefully, IBM Informix syntax will be added, too. It is easy to add support for mostly any SQL dialect - see files lib/dialects/example-constructor.rb and lib/dialects/example-exporter.rb.

SQL SELECT, DELETE, UPDATE and INSERT clauses are supported. There's also an experimental implementation of MySQL index hints.

Column values and other data that should be escaped is passed to the methods as strings. Column and table names, aliases and everything that goes unescaped is passed as symbols.

Detailed rdoc class documentation can be found in the doc folder.

Typical usage:

sql = SQLConstructor.new
sql.select( :col1, :col2 ).from( :table ).where.eq( :col3, 16 ).and.lt( :col4, 5 )
p sql

will result in:

SELECT  col1,col2 FROM table WHERE  (col3 = 16  AND col4 < 5)

One can also construct complex queries like:

    sql = SQLConstructor.new( :tidy => true, :dialect => 'mysql' )
    inner_select1 = SQLConstructor.new( :tidy => true )
    inner_select1.select( :"MAX(h.item_id)" ).from( :item_data => :d ).
          inner_join( :call_data => :h ).on.eq( :"d.item_nm", :call_ref ).where.
          eq( :"d.item_num", :"g.item_num" ).group_by( :"h.venue_nm" ).having.eq( :"COUNT(*)", 1 )
    inner_select2 = SQLConstructor.new( :dialect => 'mysql', :tidy => true )
    inner_select2.select( :"d.item_num" ).from( :item_data => :d ).
          inner_join( :call_data => :h ).on.eq( :"d.item_nm", :call_ref ).
          group_by( :"h.venue_nm" ).having.eq( :"COUNT(*)", 1 )
    sql.update( :guest => :g ).set( :link_id => inner_select1).
        where.in( :"g.item_num", inner_select2 )
    p sql

will produce:

    UPDATE
     guest g
    SET link_id=
    (SELECT
     MAX(h.item_id)
    FROM item_data d
    INNER JOIN call_data h
    ON 
    (d.item_nm = call_ref)
    WHERE 
    (d.item_num = g.item_num)
    GROUP BY h.venue_nm
    HAVING 
    (COUNT(*) = 1)
    )
    WHERE 
    (g.item_num IN 
    (SELECT
     d.item_num
    FROM item_data d
    INNER JOIN call_data h
    ON 
    (d.item_nm = call_ref)
    GROUP BY h.venue_nm
    HAVING 
    (COUNT(*) = 1)
    ))

Queries can be modified "on the fly", which can be useful for dynamic construction:

    sql.delete.from( :datas ).where.ne( :x, "SOME TEXT" ).order_by( :y )
    p sql

    DELETE
    FROM datas
    WHERE 
    (x != 'SOME TEXT')
    ORDER BY y

    sql._remove( :order_by )
    sql._get( :from ).push( :dataf )
    p sql

    DELETE
    FROM datas,dataf
    WHERE 
    (x != 'SOME TEXT')

Methods

The list of available methods for the SQL queries:

SELECT	DELETE	INSERT	UPDATE	JOIN
select	delete	insert	update	on
from	from	into	update_more	using
where	where	values	tables	join_more
select_more	using	set	set
having	order_by	columns	where
distinct	order_by_asc	select	first
all	order_by_desc		skip
distinctrow	first
join	skip
group_by
group_by_asc
group_by_desc
order_by
order_by_asc
order_by_desc
first
skip
union
union_all
union_distinct

MySQL-specific methods:

SELECT	DELETE	INSERT	UPDATE
straight_join	low_priority	low_priority	low_priority
sql_cache	quick	delayed	ignore
sql_no_cache	ignore	high_priority	limit
high_priority	limit	quick
sql_calc_found_rows		ignore
sql_small_result		on_duplicate_key_update
sql_big_result
sql_buffer_result
limit
group_by_with_rollup
use_index
force_index
ignore_index
use_key
force_key
ignore_key
inner_join
cross_join
left_join
right_join
left_outer_join
right_outer_join
natural_join
natural_left_join
natural_right_join
natural_left_outer_join
natural_right_outer_join