Module Sequel::SQL::Builders
In: lib/sequel/sql.rb

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.

Methods

as   asc   blob   case   cast   cast_numeric   cast_string   desc   expr   extract   function   identifier   ilike   join   like   lit   negate   or   qualify   subscript   value_list  

Public Instance methods

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

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

[Source]

     # File lib/sequel/sql.rb, line 291
291:       def as(exp, aliaz)
292:         SQL::AliasedExpression.new(exp, aliaz)
293:       end

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

[Source]

     # File lib/sequel/sql.rb, line 304
304:       def asc(arg, opts={})
305:         SQL::OrderedExpression.new(arg, false, opts)
306:       end

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.

[Source]

     # File lib/sequel/sql.rb, line 311
311:       def blob(s)
312:         if s.is_a?(SQL::Blob)
313:           s
314:         else
315:           SQL::Blob.new(s)
316:         end
317:       end

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

[Source]

     # File lib/sequel/sql.rb, line 323
323:       def case(*args) # core_sql ignore
324:         SQL::CaseExpression.new(*args)
325:       end

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

[Source]

     # File lib/sequel/sql.rb, line 332
332:       def cast(arg, sql_type)
333:         SQL::Cast.new(arg, sql_type)
334:       end

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)

[Source]

     # File lib/sequel/sql.rb, line 342
342:       def cast_numeric(arg, sql_type = nil)
343:         cast(arg, sql_type || Integer).sql_number
344:       end

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)

[Source]

     # File lib/sequel/sql.rb, line 352
352:       def cast_string(arg, sql_type = nil)
353:         cast(arg, sql_type || String).sql_string
354:       end

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

[Source]

     # File lib/sequel/sql.rb, line 365
365:       def desc(arg, opts={})
366:         SQL::OrderedExpression.new(arg, true, opts)
367:       end

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)

[Source]

     # File lib/sequel/sql.rb, line 381
381:       def expr(arg=(no_arg=true), &block)
382:         if block_given?
383:           if no_arg
384:             return expr(block)
385:           else
386:             raise Error, 'cannot provide both an argument and a block to Sequel.expr'
387:           end
388:         elsif no_arg
389:           raise Error, 'must provide either an argument or a block to Sequel.expr'
390:         end
391: 
392:         case arg
393:         when SQL::Expression, LiteralString, SQL::Blob
394:           arg
395:         when Hash
396:           SQL::BooleanExpression.from_value_pairs(arg, :AND)
397:         when Array
398:           if condition_specifier?(arg)
399:             SQL::BooleanExpression.from_value_pairs(arg, :AND)
400:           else
401:             SQL::Wrapper.new(arg)
402:           end
403:         when Numeric
404:           SQL::NumericExpression.new(:NOOP, arg)
405:         when String
406:           SQL::StringExpression.new(:NOOP, arg)
407:         when TrueClass, FalseClass
408:           SQL::BooleanExpression.new(:NOOP, arg)
409:         when Proc
410:           expr(virtual_row(&arg))
411:         else
412:           SQL::Wrapper.new(arg)
413:         end
414:       end

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

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

[Source]

     # File lib/sequel/sql.rb, line 420
420:       def extract(datetime_part, exp)
421:         SQL::NumericExpression.new(:extract, datetime_part, exp)
422:       end

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)

[Source]

     # File lib/sequel/sql.rb, line 429
429:       def function(name, *args)
430:         SQL::Function.new(name, *args)
431:       end

Return the argument wrapped as an SQL::Identifier.

  Sequel.identifier(:a__b) # "a__b"

[Source]

     # File lib/sequel/sql.rb, line 436
436:       def identifier(name)
437:         SQL::Identifier.new(name)
438:       end

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

[Source]

     # File lib/sequel/sql.rb, line 473
473:       def ilike(*args)
474:         SQL::StringExpression.like(*(args << {:case_insensitive=>true}))
475:       end

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

[Source]

     # File lib/sequel/sql.rb, line 449
449:       def join(args, joiner=nil)
450:         raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array)
451:         if joiner
452:           args = args.zip([joiner]*args.length).flatten
453:           args.pop
454:         end
455: 
456:         return SQL::StringExpression.new(:NOOP, '') if args.empty?
457: 
458:         args = args.map do |a|
459:           case a
460:           when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass
461:             a
462:           else
463:             a.to_s
464:           end
465:         end
466:         SQL::StringExpression.new('||''||', *args)
467:       end

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

[Source]

     # File lib/sequel/sql.rb, line 481
481:       def like(*args)
482:         SQL::StringExpression.like(*args)
483:       end

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"

[Source]

     # File lib/sequel/sql.rb, line 498
498:       def lit(s, *args) # core_sql ignore
499:         if args.empty?
500:           if s.is_a?(LiteralString)
501:             s
502:           else
503:             LiteralString.new(s)
504:           end
505:         else
506:           SQL::PlaceholderLiteralString.new(s, args) 
507:         end
508:       end

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

[Source]

     # File lib/sequel/sql.rb, line 516
516:       def negate(arg)
517:         if condition_specifier?(arg)
518:           SQL::BooleanExpression.from_value_pairs(arg, :AND, true)
519:         else
520:           raise Error, 'must pass a conditions specifier to Sequel.negate'
521:         end
522:       end

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

[Source]

     # File lib/sequel/sql.rb, line 530
530:       def or(arg)
531:         if condition_specifier?(arg)
532:           SQL::BooleanExpression.from_value_pairs(arg, :OR, false)
533:         else
534:           raise Error, 'must pass a conditions specifier to Sequel.or'
535:         end
536:       end

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"

[Source]

     # File lib/sequel/sql.rb, line 543
543:       def qualify(qualifier, identifier)
544:         SQL::QualifiedIdentifier.new(qualifier, identifier)
545:       end

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]

[Source]

     # File lib/sequel/sql.rb, line 553
553:       def subscript(exp, *subs)
554:         SQL::Subscript.new(exp, subs.flatten)
555:       end

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

[Source]

     # File lib/sequel/sql.rb, line 566
566:       def value_list(arg)
567:         raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array)
568:         SQL::ValueList.new(arg)
569:       end

[Validate]