module Sequel::SQL::Builders

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.

Public Instance Methods

as(exp, aliaz) click to toggle source

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

Sequel.as(:column, :alias) # "column" AS "alias"
# File lib/sequel/sql.rb, line 290
def as(exp, aliaz)
  SQL::AliasedExpression.new(exp, aliaz)
end
asc(arg, opts={}) click to toggle source

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
# File lib/sequel/sql.rb, line 303
def asc(arg, opts={})
  SQL::OrderedExpression.new(arg, false, opts)
end
blob(s) click to toggle source

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.

# 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) click to toggle source

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
# File lib/sequel/sql.rb, line 322
def case(*args) # core_sql ignore
  SQL::CaseExpression.new(*args)
end
cast(arg, sql_type) click to toggle source

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))
# 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) click to toggle source

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)
# 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) click to toggle source

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)
# 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={}) click to toggle source

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
# File lib/sequel/sql.rb, line 364
def desc(arg, opts={})
  SQL::OrderedExpression.new(arg, true, opts)
end
expr(arg=(no_arg=true), &block) click to toggle source

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)
# 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) click to toggle source

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

Sequel.extract(:year, :date) # extract(year FROM "date")
# File lib/sequel/sql.rb, line 419
def extract(datetime_part, exp)
  SQL::NumericExpression.new(:extract, datetime_part, exp)
end
function(name, *args) click to toggle source

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)
# File lib/sequel/sql.rb, line 428
def function(name, *args)
  SQL::Function.new(name, *args)
end
identifier(name) click to toggle source

Return the argument wrapped as an SQL::Identifier.

Sequel.identifier(:a__b) # "a__b"
# File lib/sequel/sql.rb, line 435
def identifier(name)
  SQL::Identifier.new(name)
end
ilike(*args) click to toggle source

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%'
# File lib/sequel/sql.rb, line 472
def ilike(*args)
  SQL::StringExpression.like(*(args << {:case_insensitive=>true}))
end
join(args, joiner=nil) click to toggle source

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
# 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) click to toggle source

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%'
# File lib/sequel/sql.rb, line 480
def like(*args)
  SQL::StringExpression.like(*args)
end
lit(s, *args) click to toggle source

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"
# 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) click to toggle source

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))
# 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) click to toggle source

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))
# 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) click to toggle source

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"
# File lib/sequel/sql.rb, line 542
def qualify(qualifier, identifier)
  SQL::QualifiedIdentifier.new(qualifier, identifier)
end
subscript(exp, *subs) click to toggle source

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]
# File lib/sequel/sql.rb, line 552
def subscript(exp, *subs)
  SQL::Subscript.new(exp, subs.flatten)
end
value_list(arg) click to toggle source

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