Module: Sequel::SQL::Builders

Included in:
Sequel
Defined in:
lib/sequel/sql.rb

Overview

These methods are designed as replacements for the core extensions, so that Sequel is still easy to use if the core extensions are not enabled.

Instance Method Summary collapse

Instance Method Details

#as(exp, aliaz) ⇒ Object

Create an SQL::AliasedExpression for the given expression and alias.

Sequel.as(:column, :alias) # "column" AS "alias"



290
291
292
 
# File 'lib/sequel/sql.rb', line 290

def as(exp, aliaz)
  SQL::AliasedExpression.new(exp, aliaz)
end

#asc(arg, opts = {}) ⇒ Object

Order the given argument ascending. Options:

:nulls

Set to :first to use NULLS FIRST (so NULL values are ordered before other values), or :last to use NULLS LAST (so NULL values are ordered after other values).

Sequel.asc(:a) # a ASC
Sequel.asc(:b, :nulls=>:last) # b ASC NULLS LAST



303
304
305
 
# File 'lib/sequel/sql.rb', line 303

def asc(arg, opts={})
  SQL::OrderedExpression.new(arg, false, opts)
end

#blob(s) ⇒ Object

Return an SQL::Blob that holds the same data as this string. Blobs provide proper escaping of binary data. If given a blob, returns it directly.



310
311
312
313
314
315
316
 
# File 'lib/sequel/sql.rb', line 310

def blob(s)
  if s.is_a?(SQL::Blob)
    s
  else
    SQL::Blob.new(s)
  end
end

#case(*args) ⇒ Object

Return an SQL::CaseExpression created with the given arguments.

Sequel.case([[{:a=>[2,3]}, 1]], 0) # SQL: CASE WHEN a IN (2, 3) THEN 1 ELSE 0 END
Sequel.case({:a=>1}, 0, :b) # SQL: CASE b WHEN a THEN 1 ELSE 0 END



322
323
324
 
# File 'lib/sequel/sql.rb', line 322

def case(*args) # core_sql ignore
  SQL::CaseExpression.new(*args)
end

#cast(arg, sql_type) ⇒ Object

Cast the reciever to the given SQL type. You can specify a ruby class as a type, and it is handled similarly to using a database independent type in the schema methods.

Sequel.cast(:a, :integer) # CAST(a AS integer)
Sequel.cast(:a, String) # CAST(a AS varchar(255))



331
332
333
 
# File 'lib/sequel/sql.rb', line 331

def cast(arg, sql_type)
  SQL::Cast.new(arg, sql_type)
end

#cast_numeric(arg, sql_type = nil) ⇒ Object

Cast the reciever to the given SQL type (or the database’s default Integer type if none given), and return the result as a NumericExpression, so you can use the bitwise operators on the result. 

Sequel.cast_numeric(:a) # CAST(a AS integer)
Sequel.cast_numeric(:a, Float) # CAST(a AS double precision)



341
342
343
 
# File 'lib/sequel/sql.rb', line 341

def cast_numeric(arg, sql_type = nil)
  cast(arg, sql_type || Integer).sql_number
end

#cast_string(arg, sql_type = nil) ⇒ Object

Cast the reciever to the given SQL type (or the database’s default String type if none given), and return the result as a StringExpression, so you can use + directly on the result for SQL string concatenation.

Sequel.cast_string(:a) # CAST(a AS varchar(255))
Sequel.cast_string(:a, :text) # CAST(a AS text)



351
352
353
 
# File 'lib/sequel/sql.rb', line 351

def cast_string(arg, sql_type = nil)
  cast(arg, sql_type || String).sql_string
end

#desc(arg, opts = {}) ⇒ Object

Order the given argument descending. Options:

:nulls

Set to :first to use NULLS FIRST (so NULL values are ordered before other values), or :last to use NULLS LAST (so NULL values are ordered after other values).

Sequel.desc(:a) # b DESC
Sequel.desc(:b, :nulls=>:first) # b DESC NULLS FIRST



364
365
366
 
# File 'lib/sequel/sql.rb', line 364

def desc(arg, opts={})
  SQL::OrderedExpression.new(arg, true, opts)
end

#expr(arg = (no_arg=true), &block) ⇒ Object

Wraps the given object in an appropriate Sequel wrapper. If the given object is already a Sequel object, return it directly. For condition specifiers (hashes and arrays of two pairs), true, and false, return a boolean expressions. For numeric objects, return a numeric expression. For strings, return a string expression. For procs or when the method is passed a block, evaluate it as a virtual row and wrap it appropriately. In all other cases, use a generic wrapper.

This method allows you to construct SQL expressions that are difficult to construct via other methods. For example:

Sequel.expr(1) - :a # SQL: (1 - a)



380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
 
# File 'lib/sequel/sql.rb', line 380

def expr(arg=(no_arg=true), &block)
  if block_given?
    if no_arg
      return expr(block)
    else
      raise Error, 'cannot provide both an argument and a block to Sequel.expr'
    end
  elsif no_arg
    raise Error, 'must provide either an argument or a block to Sequel.expr'
  end

  case arg
  when SQL::Expression, LiteralString, SQL::Blob
    arg
  when Hash
    SQL::BooleanExpression.from_value_pairs(arg, :AND)
  when Array
    if condition_specifier?(arg)
      SQL::BooleanExpression.from_value_pairs(arg, :AND)
    else
      SQL::Wrapper.new(arg)
    end
  when Numeric
    SQL::NumericExpression.new(:NOOP, arg)
  when String
    SQL::StringExpression.new(:NOOP, arg)
  when TrueClass, FalseClass
    SQL::BooleanExpression.new(:NOOP, arg)
  when Proc
    expr(virtual_row(&arg))
  else
    SQL::Wrapper.new(arg)
  end
end

#extract(datetime_part, exp) ⇒ Object

Extract a datetime_part (e.g. year, month) from the given expression:

Sequel.extract(:year, :date) # extract(year FROM "date")



419
420
421
 
# File 'lib/sequel/sql.rb', line 419

def extract(datetime_part, exp)
  SQL::NumericExpression.new(:extract, datetime_part, exp)
end

#function(name, *args) ⇒ Object

Returns a Sequel::SQL::Function with the function name and the given arguments.

Sequel.function(:now) # SQL: now()
Sequel.function(:substr, :a, 1) # SQL: substr(a, 1)



428
429
430
 
# File 'lib/sequel/sql.rb', line 428

def function(name, *args)
  SQL::Function.new(name, *args)
end

#identifier(name) ⇒ Object

Return the argument wrapped as an SQL::Identifier.

Sequel.identifier(:a__b) # "a__b"



435
436
437
 
# File 'lib/sequel/sql.rb', line 435

def identifier(name)
  SQL::Identifier.new(name)
end

#ilike(*args) ⇒ Object

Create a BooleanExpression case insensitive (if the database supports it) pattern match of the receiver with the given patterns. See SQL::StringExpression.like.

Sequel.ilike(:a, 'A%') # "a" ILIKE 'A%'



472
473
474
 
# File 'lib/sequel/sql.rb', line 472

def ilike(*args)
  SQL::StringExpression.like(*(args << {:case_insensitive=>true}))
end

#join(args, joiner = nil) ⇒ Object

Return a Sequel::SQL::StringExpression representing an SQL string made up of the concatenation of the given array’s elements. If an argument is passed, it is used in between each element of the array in the SQL concatenation.

Sequel.join([:a]) # SQL: a
Sequel.join([:a, :b]) # SQL: a || b
Sequel.join([:a, 'b']) # SQL: a || 'b'
Sequel.join(['a', :b], ' ') # SQL: 'a' || ' ' || b

Raises:



448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
 
# File 'lib/sequel/sql.rb', line 448

def join(args, joiner=nil)
  raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array)
  if joiner
    args = args.zip([joiner]*args.length).flatten
    args.pop
  end

  return SQL::StringExpression.new(:NOOP, '') if args.empty?

  args = args.map do |a|
    case a
    when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass
      a
    else
      a.to_s
    end
  end
  SQL::StringExpression.new(:'||', *args)
end

#like(*args) ⇒ Object

Create a SQL::BooleanExpression case sensitive (if the database supports it) pattern match of the receiver with the given patterns. See SQL::StringExpression.like.

Sequel.like(:a, 'A%') # "a" LIKE 'A%'



480
481
482
 
# File 'lib/sequel/sql.rb', line 480

def like(*args)
  SQL::StringExpression.like(*args)
end

#lit(s, *args) ⇒ Object

Converts a string into a Sequel::LiteralString, in order to override string literalization, e.g.:

DB[:items].filter(:abc => 'def').sql #=>
  "SELECT * FROM items WHERE (abc = 'def')"

DB[:items].filter(:abc => Sequel.lit('def')).sql #=>
  "SELECT * FROM items WHERE (abc = def)"

You can also provide arguments, to create a Sequel::SQL::PlaceholderLiteralString:

DB[:items].select{|o| o.count(Sequel.lit('DISTINCT ?', :a))}.sql #=>
  "SELECT count(DISTINCT a) FROM items"



497
498
499
500
501
502
503
504
505
506
507
 
# File 'lib/sequel/sql.rb', line 497

def lit(s, *args) # core_sql ignore
  if args.empty?
    if s.is_a?(LiteralString)
      s
    else
      LiteralString.new(s)
    end
  else
    SQL::PlaceholderLiteralString.new(s, args) 
  end
end

#negate(arg) ⇒ Object

Return a Sequel::SQL::BooleanExpression created from the condition specifier, matching none of the conditions.

Sequel.negate(:a=>true) # SQL: a IS NOT TRUE
Sequel.negate([[:a, true]]) # SQL: a IS NOT TRUE
Sequel.negate([[:a, 1], [:b, 2]]) # SQL: ((a != 1) AND (b != 2))



515
516
517
518
519
520
521
 
# File 'lib/sequel/sql.rb', line 515

def negate(arg)
  if condition_specifier?(arg)
    SQL::BooleanExpression.from_value_pairs(arg, :AND, true)
  else
    raise Error, 'must pass a conditions specifier to Sequel.negate'
  end
end

#or(arg) ⇒ Object

Return a Sequel::SQL::BooleanExpression created from the condition specifier, matching any of the conditions.

Sequel.or(:a=>true) # SQL: a IS TRUE
Sequel.or([[:a, true]]) # SQL: a IS TRUE
Sequel.or([[:a, 1], [:b, 2]]) # SQL: ((a = 1) OR (b = 2))



529
530
531
532
533
534
535
 
# File 'lib/sequel/sql.rb', line 529

def or(arg)
  if condition_specifier?(arg)
    SQL::BooleanExpression.from_value_pairs(arg, :OR, false)
  else
    raise Error, 'must pass a conditions specifier to Sequel.or'
  end
end

#qualify(qualifier, identifier) ⇒ Object

Create a qualified identifier with the given qualifier and identifier

Sequel.qualify(:table, :column) # "table"."column"
Sequel.qualify(:schema, :table) # "schema"."table"
Sequel.qualify(:table, :column).qualify(:schema) # "schema"."table"."column"



542
543
544
 
# File 'lib/sequel/sql.rb', line 542

def qualify(qualifier, identifier)
  SQL::QualifiedIdentifier.new(qualifier, identifier)
end

#subscript(exp, *subs) ⇒ Object

Return an SQL::Subscript with the given arguments, representing an SQL array access.

Sequel.subscript(:array, 1) # array[1]
Sequel.subscript(:array, 1, 2) # array[1, 2]
Sequel.subscript(:array, [1, 2]) # array[1, 2]



552
553
554
 
# File 'lib/sequel/sql.rb', line 552

def subscript(exp, *subs)
  SQL::Subscript.new(exp, subs.flatten)
end

#value_list(arg) ⇒ Object

Return a SQL::ValueList created from the given array. Used if the array contains all two element arrays and you want it treated as an SQL value list (IN predicate) instead of as a conditions specifier (similar to a hash). This is not necessary if you are using this array as a value in a filter, but may be necessary if you are using it as a value with placeholder SQL:

DB[:a].filter([:a, :b]=>[[1, 2], [3, 4]]) # SQL: (a, b) IN ((1, 2), (3, 4))
DB[:a].filter('(a, b) IN ?', [[1, 2], [3, 4]]) # SQL: (a, b) IN ((1 = 2) AND (3 = 4))
DB[:a].filter('(a, b) IN ?', Sequel.value_list([[1, 2], [3, 4]])) # SQL: (a, b) IN ((1, 2), (3, 4))

Raises:



565
566
567
568
 
# File 'lib/sequel/sql.rb', line 565

def value_list(arg)
  raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array)
  SQL::ValueList.new(arg)
end