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, columns=nil) click to toggle source

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

Sequel.as(:column, :alias) # "column" AS "alias"
Sequel.as(:column, :alias, [:col_alias1, :col_alias2]) # "column" AS "alias"("col_alias1", "col_alias2")
# File lib/sequel/sql.rb, line 350
def as(exp, aliaz, columns=nil)
  SQL::AliasedExpression.new(exp, aliaz, columns)
end
asc(arg, opts=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 363
def asc(arg, opts=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 370
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 382
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 391
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 401
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 411
def cast_string(arg, sql_type = nil)
  cast(arg, sql_type || String).sql_string
end
char_length(arg) click to toggle source

Return an emulated function call for getting the number of characters in the argument:

Sequel.char_length(:a) # char_length(a) -- Most databases
Sequel.char_length(:a) # length(a) -- SQLite
# File lib/sequel/sql.rb, line 420
def char_length(arg)
  SQL::Function.new!(:char_length, [arg], :emulate=>true)
end
date_add(expr, interval) click to toggle source

Return a DateAdd expression, adding an interval to the date/timestamp expr.

# File lib/sequel/extensions/date_arithmetic.rb, line 36
def date_add(expr, interval)
  DateAdd.new(expr, interval)
end
date_sub(expr, interval) click to toggle source

Return a DateAdd expression, adding the negative of the interval to the date/timestamp expr.

# File lib/sequel/extensions/date_arithmetic.rb, line 42
def date_sub(expr, interval)
  interval = if interval.is_a?(Hash)
    h = {}
    interval.each{|k,v| h[k] = -v unless v.nil?}
    h
  else
    -interval
  end
  DateAdd.new(expr, interval)
end
deep_qualify(qualifier, expr) click to toggle source

Do a deep qualification of the argument using the qualifier. This recurses into nested structures.

Sequel.deep_qualify(:table, :column) # "table"."column"
Sequel.deep_qualify(:table, Sequel.+(:column, 1)) # "table"."column" + 1
Sequel.deep_qualify(:table, Sequel.like(:a, 'b')) # "table"."a" LIKE 'b' ESCAPE '\'
# File lib/sequel/sql.rb, line 430
def deep_qualify(qualifier, expr)
  Sequel::Qualifier.new(qualifier).transform(expr)
end
delay(&block) click to toggle source

Return a delayed evaluation that uses the passed block. This is used to delay evaluations of the code to runtime. For example, with the following code:

ds = DB[:table].where{column > Time.now}

The filter is fixed to the time that where was called. Unless you are only using the dataset once immediately after creating it, that's probably not desired. If you just want to set it to the time when the query is sent to the database, you can wrap it in Sequel.delay:

ds = DB[:table].where{column > Sequel.delay{Time.now}}

Note that for dates and timestamps, you are probably better off using Sequel::CURRENT_DATE and Sequel::CURRENT_TIMESTAMP instead of this generic delayed evaluation facility.

# File lib/sequel/sql.rb, line 450
def delay(&block)
  raise(Error, "Sequel.delay requires a block") unless block
  SQL::DelayedEvaluation.new(block)
end
desc(arg, opts=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 464
def desc(arg, opts=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)

On the Sequel module, this is aliased as [], for easier use:

Sequel[1] - :a # SQL: (1 - a)
# File lib/sequel/sql.rb, line 484
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 Symbol
    t, c, a = Sequel.split_symbol(arg)

    arg = if t
      SQL::QualifiedIdentifier.new(t, c)
    else
      SQL::Identifier.new(c)
    end

    if a
      arg = SQL::AliasedExpression.new(arg, a)
    end

    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 537
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 546
def function(name, *args)
  SQL::Function.new(name, *args)
end
hstore(v) click to toggle source

Return a Postgres::HStore proxy for the given hash.

# File lib/sequel/extensions/pg_hstore.rb, line 313
def hstore(v)
  case v
  when Postgres::HStore
    v
  when Hash
    Postgres::HStore.new(v)
  else
    # May not be defined unless the pg_hstore_ops extension is used
    hstore_op(v)
  end
end
hstore_op(v) click to toggle source

Return the object wrapped in an Postgres::HStoreOp.

# File lib/sequel/extensions/pg_hstore_ops.rb, line 328
def hstore_op(v)
  case v
  when Postgres::HStoreOp
    v
  else
    Postgres::HStoreOp.new(v)
  end
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 553
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%' ESCAPE '\'
# File lib/sequel/sql.rb, line 590
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 566
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%' ESCAPE '\'
# File lib/sequel/sql.rb, line 598
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].where(:abc => 'def').sql #=>
  "SELECT * FROM items WHERE (abc = 'def')"

DB[:items].where(: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 615
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 633
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 647
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
pg_array(v, array_type=nil) click to toggle source

Return a Postgres::PGArray proxy for the given array and database array type.

# File lib/sequel/extensions/pg_array.rb, line 557
def pg_array(v, array_type=nil)
  case v
  when Postgres::PGArray
    if array_type.nil? || v.array_type == array_type
      v
    else
      Postgres::PGArray.new(v.to_a, array_type)
    end
  when Array
    Postgres::PGArray.new(v, array_type)
  else
    # May not be defined unless the pg_array_ops extension is used
    pg_array_op(v)
  end
end
pg_array_op(v) click to toggle source

Return the object wrapped in an Postgres::ArrayOp.

# File lib/sequel/extensions/pg_array_ops.rb, line 295
def pg_array_op(v)
  case v
  when Postgres::ArrayOp
    v
  else
    Postgres::ArrayOp.new(v)
  end
end
pg_inet_op(v) click to toggle source

Return the expression wrapped in the Postgres::InetOp.

# File lib/sequel/extensions/pg_inet_ops.rb, line 171
def pg_inet_op(v)
  case v
  when Postgres::InetOp
    v
  else
    Postgres::InetOp.new(v)
  end
end
pg_json(v) click to toggle source

Wrap the array or hash in a Postgres::JSONArray or Postgres::JSONHash.

# File lib/sequel/extensions/pg_json.rb, line 270
def pg_json(v)
  case v
  when Postgres::JSONArray, Postgres::JSONHash
    v
  when Array
    Postgres::JSONArray.new(v)
  when Hash
    Postgres::JSONHash.new(v)
  when Postgres::JSONBArray
    Postgres::JSONArray.new(v.to_a)
  when Postgres::JSONBHash
    Postgres::JSONHash.new(v.to_hash)
  else
    Sequel.pg_json_op(v)
  end
end
pg_json_op(v) click to toggle source

Return the object wrapped in an Postgres::JSONOp.

# File lib/sequel/extensions/pg_json_ops.rb, line 470
def pg_json_op(v)
  case v
  when Postgres::JSONOp
    v
  else
    Postgres::JSONOp.new(v)
  end
end
pg_jsonb(v) click to toggle source

Wrap the array or hash in a Postgres::JSONBArray or Postgres::JSONBHash.

# File lib/sequel/extensions/pg_json.rb, line 288
def pg_jsonb(v)
  case v
  when Postgres::JSONBArray, Postgres::JSONBHash
    v
  when Array
    Postgres::JSONBArray.new(v)
  when Hash
    Postgres::JSONBHash.new(v)
  when Postgres::JSONArray
    Postgres::JSONBArray.new(v.to_a)
  when Postgres::JSONHash
    Postgres::JSONBHash.new(v.to_hash)
  else
    Sequel.pg_jsonb_op(v)
  end
end
pg_jsonb_op(v) click to toggle source

Return the object wrapped in an Postgres::JSONBOp.

# File lib/sequel/extensions/pg_json_ops.rb, line 480
def pg_jsonb_op(v)
  case v
  when Postgres::JSONBOp
    v
  else
    Postgres::JSONBOp.new(v)
  end
end
pg_range(v, db_type=nil) click to toggle source

Convert the object to a Postgres::PGRange.

# File lib/sequel/extensions/pg_range.rb, line 591
def pg_range(v, db_type=nil)
  case v
  when Postgres::PGRange
    if db_type.nil? || v.db_type == db_type
      v
    else
      Postgres::PGRange.new(v.begin, v.end, :exclude_begin=>v.exclude_begin?, :exclude_end=>v.exclude_end?, :db_type=>db_type)
    end
  when Range
    Postgres::PGRange.from_range(v, db_type)
  else
    # May not be defined unless the pg_range_ops extension is used
    pg_range_op(v)
  end
end
pg_range_op(v) click to toggle source

Return the expression wrapped in the Postgres::RangeOp.

# File lib/sequel/extensions/pg_range_ops.rb, line 133
def pg_range_op(v)
  case v
  when Postgres::RangeOp
    v
  else
    Postgres::RangeOp.new(v)
  end
end
pg_row(expr) click to toggle source

Wraps the expr array in an anonymous Postgres::PGRow::ArrayRow instance.

# File lib/sequel/extensions/pg_row.rb, line 590
def pg_row(expr)
  case expr
  when Array
    Postgres::PGRow::ArrayRow.new(expr)
  else
    # Will only work if pg_row_ops extension is loaded
    pg_row_op(expr)
  end
end
pg_row_op(expr) click to toggle source

Return a PGRowOp wrapping the given expression.

# File lib/sequel/extensions/pg_row_ops.rb, line 170
def pg_row_op(expr)
  Postgres::PGRowOp.wrap(expr)
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 660
def qualify(qualifier, identifier)
  SQL::QualifiedIdentifier.new(qualifier, identifier)
end
string_agg(*a) click to toggle source

Return a StringAgg expression for an aggregate string concatentation.

# File lib/sequel/extensions/string_agg.rb, line 66
def string_agg(*a)
  StringAgg.new(*a)
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]
Sequel.subscript(:array, 1..2) # array[1:2]
Sequel.subscript(:array, 1...3) # array[1:2]
# File lib/sequel/sql.rb, line 672
def subscript(exp, *subs)
  SQL::Subscript.new(exp, subs.flatten)
end
trim(arg) click to toggle source

Return an emulated function call for trimming a string of spaces from both sides (similar to ruby's String#strip).

Sequel.trim(:a) # trim(a) -- Most databases
Sequel.trim(:a) # ltrim(rtrim(a)) -- Microsoft SQL Server
# File lib/sequel/sql.rb, line 681
def trim(arg)
  SQL::Function.new!(:trim, [arg], :emulate=>true)
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].where([:a, :b]=>[[1, 2], [3, 4]]) # SQL: (a, b) IN ((1, 2), (3, 4))
DB[:a].where('(a, b) IN ?', [[1, 2], [3, 4]]) # SQL: (a, b) IN ((1 = 2) AND (3 = 4))
DB[:a].where('(a, b) IN ?', Sequel.value_list([[1, 2], [3, 4]])) # SQL: (a, b) IN ((1, 2), (3, 4))
# File lib/sequel/sql.rb, line 694
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