class SQLConstructor

Author

Vasiliy Korol (vakorol@mail.ru)

Copyright

Vasiliy Korol © 2014

License

Distributes under terms of GPLv2

This class implements methods to construct a valid SQL query. 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.

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

It 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')

Attributes

dialect[R]
exporter[RW]
obj[R]
tidy[RW]

Public Class Methods

new( params = nil ) click to toggle source

Class constructor. Accepts an optional argument with a hash of parameters :dialect and :tidy to set the SQLExporter object in @exporter, or :exporter to receive a predefined SQLExporter object.

# File lib/sqlconstructor.rb, line 108
def initialize ( params = nil )
    @dialect, @string, @obj, @tidy = nil, nil, nil, false
    if params.is_a? Hash
        @dialect  = params[ :dialect  ]
        @tidy     = params[ :tidy     ]
        @exporter = params[ :exporter ]
    end
    @exporter ||= SQLExporter.new @dialect, @tidy
    @dialect = @exporter.dialect
end

Public Instance Methods

delete() click to toggle source

Add a DELETE statement. Returns an instance of BasicDelete_ class.

# File lib/sqlconstructor.rb, line 131
def delete
    _getGenericQuery 'delete'
end
insert() click to toggle source

Add a INSERT statement Returns an instance of BasicInsert_ class.

# File lib/sqlconstructor.rb, line 139
def insert
    _getGenericQuery 'insert'
end
method_missing( method, *args ) click to toggle source

Pass all unknown methods to @obj or throw an exception if the call already originated from @obj.

# File lib/sqlconstructor.rb, line 165
def method_missing ( method, *args )
    return @obj.send( method, *args )  if @obj && @obj.child_caller != @obj  
     # raise an exception if the call is "bouncing" between self and @obj
    raise NoMethodError, ERR_UNKNOWN_METHOD + 
        ": '#{method.to_s}' from #{@obj.class.name}"
end
select( *cols ) click to toggle source

Add a SELECT statement with columns specified by *cols. Returns an instance of BasicSelect_ class.

# File lib/sqlconstructor.rb, line 123
def select ( *cols )
    _getGenericQuery 'select', *cols
end
to_s() click to toggle source

Convert object to string by calling the .export() method of the @exporter object.

# File lib/sqlconstructor.rb, line 155
    def to_s
#        return @string  if @string
        @obj.inline = self.inline
        @string = @exporter.export @obj
    end
Also aliased as: to_str
to_str()

Dirty hack to make .join work on an array of SQLConstructors

Alias for: to_s
update( *tabs ) click to toggle source

Add a UPDATE statement Returns an instance of BasicUpdate_ class.

# File lib/sqlconstructor.rb, line 147
def update ( *tabs )
    _getGenericQuery 'update', *tabs
end

Private Instance Methods

_getGenericQuery( type, *args ) click to toggle source

Returns an instance of Basic* child dialect-specific class

# File lib/sqlconstructor.rb, line 180
def _getGenericQuery ( type, *args )
    class_basic = 'Basic' + type.capitalize
    class_child = class_basic + '_' + @dialect
    begin
        @obj = self.class.const_get( class_child ).new self, *args
    rescue NameError
        @obj = self.class.const_get( class_basic ).new self, *args
    end
end