Class: SQLStatement::Select

Inherits:
Object show all
Defined in:
lib/sql/statement.rb

Overview

Creates a SELECT statement

Direct Known Subclasses

SelectCreate, SelectInsert, Update

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize ⇒ Select

Returns a new instance of Select.



281
282
283
284
285
286
287
 
# File 'lib/sql/statement.rb', line 281

def initialize
   @fields={}
   @tables={}
   @conditions=[]
   @groupby=[]
   @orderby=[]
end

Instance Attribute Details

#conditions ⇒ Object

This is an array of Function objects that specify the contents of the WHERE clause.



323
324
325
 
# File 'lib/sql/statement.rb', line 323

def conditions
  @conditions
end

#distinct ⇒ Object 



342
343
344
 
# File 'lib/sql/statement.rb', line 342

def distinct
   @distinct ||= false
end

#fields ⇒ Object

This is the list of fields or instructions to be retrieved. In a SELECT statement, this appears immediately after the SELECT keyword. In an UPDATE statement, this appears after the SET keyword. (And in other places for other kinds of queries #In an UPDATE statement, this appears after the SET keyword. (And in other places for other kinds of queries. The SQL inventors were nothing if not consistent.)

This is a hash of fields to be used in the SQL statmenet. Entries are in the form alias => underlying_expression. If you are not aliasing a field name, use the form fieldname => fieldname.



298
299
300
 
# File 'lib/sql/statement.rb', line 298

def fields
  @fields
end

#groupby ⇒ Object

Specifieds fields or expressions to group by, as an array.



326
327
328
 
# File 'lib/sql/statement.rb', line 326

def groupby
  @groupby
end

#orderby ⇒ Object

Specifieds fields or expressions to sort by, as an array.



329
330
331
 
# File 'lib/sql/statement.rb', line 329

def orderby
  @orderby
end

#tables ⇒ Object

This is the tables to include in the query (i.e. the FROM clause).

This is a hash of tables to be used in the SQL statmenet. Entries are in the form alias => underlying_expression. If you are not aliasing a table name, use the form tablename => tablename.



312
313
314
 
# File 'lib/sql/statement.rb', line 312

def tables
  @tables
end

Class Method Details

.newlists(*lists) ⇒ Object

Use this to define additional array attributes in descendant classes with all of the necessary addition, initialization and merging (<<) semantics. You still need to appropriately define allfields to make appropriate use of the lists you have added. You can only call this method once, as it redefines methods each time it is called



404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
 
# File 'lib/sql/statement.rb', line 404

def newlists *lists
	 attr_accessor *lists

	 lines=lists.collect do |listname|
	    ["@#{listname}=[]","@#{listname} += parts.#{listname}"]
	 end.transpose
	 class_eval <<-"end;"
	    def initialize *args
  super
  #{lines[0].join("\n")}
	    end
	    def << (parts)
  super
  #{lines[1].join("\n")}
  self
	    end
	 end;
end

Instance Method Details

#<<(parts) ⇒ Object

Merges the various parts of a SelectParts into the correct places in this SQL statement.



347
348
349
350
351
352
353
354
 
# File 'lib/sql/statement.rb', line 347

def << (parts)
   @fields.merge!(parts.fields)
   @tables.merge!(parts.tables)
   @conditions += parts.conditions
   @groupby += parts.groupby
   @orderby += parts.orderby
   self
end

#add_fields(newfields) ⇒ Object

Adds an array of unaliased fields to this SQL statement.



301
302
303
304
305
 
# File 'lib/sql/statement.rb', line 301

def add_fields (newfields)
   newfields.each do |x|
	 @fields[x]=x
   end
end

#add_tables(newtables) ⇒ Object

Adds an array of unaliased tables to this SQL statement.



315
316
317
318
319
 
# File 'lib/sql/statement.rb', line 315

def add_tables (newtables)
   newtables.each do |x|
	 @tables[x]=x
   end
end

#allfields ⇒ Object

If you overriding this class (or any of its subclasses) to constructing other fields from other lists, override this function, call super, construct the additional fields here, and add them to the result of super. Then return the resulting hash.



334
335
336
 
# File 'lib/sql/statement.rb', line 334

def allfields
   @fields.dup
end

#placeheld ⇒ Object 



368
369
370
 
# File 'lib/sql/statement.rb', line 368

def placeheld
   (allfields.values+@tables.values+conditions+groupby+orderby).collect{|x| x.placeheld}.flatten
end

#to_s ⇒ Object

Returns the SQL string that you would actually want to execute on the database. it may contain placeholders for actual data. Those actual data can be retrieved with the placeheld method, and used in a manner similar to

dbh.execute(s.to_s,*s.placeheld)



360
361
362
363
364
365
366
 
# File 'lib/sql/statement.rb', line 360

def to_s
   statement="SELECT #{distinct ? 'DISTINCT' : ''} #{fields_s} FROM #{tables_s}"
   v=conditions_s; statement << " WHERE "<< v if v
   v=groupby_s; statement << " GROUP BY "<< v if v
   v=orderby_s; statement << " ORDER BY "<< v if v
   return statement
end

#to_sqlpart ⇒ Object

This is useful for writing nested queries.



373
374
375
 
# File 'lib/sql/statement.rb', line 373

def to_sqlpart
   "("+to_s+")"
end