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
Public Class Methods
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
Add a DELETE statement. Returns an instance of BasicDelete_ class.
# File lib/sqlconstructor.rb, line 131 def delete _getGenericQuery 'delete' end
Add a INSERT statement Returns an instance of BasicInsert_ class.
# File lib/sqlconstructor.rb, line 139 def insert _getGenericQuery 'insert' end
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
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
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
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
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