Class: SQLConstructor

Inherits:
SQLObject show all
Defined in:
lib/sqlconstructor.rb

Overview

Author

Vasiliy Korol ([email protected])

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

Defined Under Namespace

Classes: BasicDelete, BasicDelete_mysql, BasicInsert, BasicInsert_mysql, BasicJoin, BasicJoin_mysql, BasicSelect, BasicSelect_example, BasicSelect_mysql, BasicUnion, BasicUpdate, BasicUpdate_mysql, GenericQuery, QAttr

Instance Attribute Summary collapse

Attributes inherited from SQLObject

#alias, #inline, #name, #separator

Instance Method Summary collapse

Methods inherited from SQLObject

#_name, #_string, #_string=, get

Constructor Details

#initialize(params = nil) ⇒ SQLConstructor

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.



108
109
110
111
112
113
114
115
116
117
 
# 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

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(method, *args) ⇒ Object

Pass all unknown methods to @obj or throw an exception if the call

already originated from @obj.

Raises:

  • (NoMethodError) 


165
166
167
168
169
170
 
# 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

Instance Attribute Details

#dialect ⇒ Object (readonly)

Returns the value of attribute dialect.



98
99
100
 
# File 'lib/sqlconstructor.rb', line 98

def dialect
  @dialect
end

#exporter ⇒ Object

Returns the value of attribute exporter.



97
98
99
 
# File 'lib/sqlconstructor.rb', line 97

def exporter
  @exporter
end

#obj ⇒ Object (readonly)

Returns the value of attribute obj.



98
99
100
 
# File 'lib/sqlconstructor.rb', line 98

def obj
  @obj
end

#tidy ⇒ Object

Returns the value of attribute tidy.



97
98
99
 
# File 'lib/sqlconstructor.rb', line 97

def tidy
  @tidy
end

Instance Method Details

#delete ⇒ Object

Add a DELETE statement.

Returns an instance of BasicDelete_[%dialect%] class.



131
132
133
 
# File 'lib/sqlconstructor.rb', line 131

def delete
    _getGenericQuery 'delete'
end

#insert ⇒ Object

Add a INSERT statement

Returns an instance of BasicInsert_[%dialect%] class.



139
140
141
 
# File 'lib/sqlconstructor.rb', line 139

def insert
    _getGenericQuery 'insert'
end

#select(*cols) ⇒ Object

Add a SELECT statement with columns specified by *cols.

Returns an instance of BasicSelect_[%dialect%] class.



123
124
125
 
# File 'lib/sqlconstructor.rb', line 123

def select ( *cols )
    _getGenericQuery 'select', *cols
end

#to_s ⇒ Object

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

the @exporter object.



155
156
157
158
159
 
# File 'lib/sqlconstructor.rb', line 155

def to_s
#        return @string  if @string
    @obj.inline = self.inline
    @string = @exporter.export @obj
end

#to_str ⇒ Object

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



101
 
# File 'lib/sqlconstructor.rb', line 101

alias :to_str :to_s

#update(*tabs) ⇒ Object

Add a UPDATE statement

Returns an instance of BasicUpdate_[%dialect%] class.



147
148
149
 
# File 'lib/sqlconstructor.rb', line 147

def update ( *tabs )
    _getGenericQuery 'update', *tabs
end