module Sequel::SQL::Builders

These methods make it easier to create Sequel expressions without using the core extensions.

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
313 def as(exp, aliaz, columns=nil)
314   SQL::AliasedExpression.new(exp, aliaz, columns)
315 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
326 def asc(arg, opts=OPTS)
327   SQL::OrderedExpression.new(arg, false, opts)
328 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
333 def blob(s)
334   if s.is_a?(SQL::Blob)
335     s
336   else
337     SQL::Blob.new(s)
338   end
339 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
345 def case(*args)
346   SQL::CaseExpression.new(*args)
347 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
354 def cast(arg, sql_type)
355   SQL::Cast.new(arg, sql_type)
356 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
364 def cast_numeric(arg, sql_type = nil)
365   cast(arg, sql_type || Integer).sql_number
366 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
374 def cast_string(arg, sql_type = nil)
375   cast(arg, sql_type || String).sql_string
376 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
383 def char_length(arg)
384   SQL::Function.new!(:char_length, [arg], :emulate=>true)
385 end
date_add(expr, interval, opts=OPTS) click to toggle source

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

:cast

Cast to the specified type instead of the default if casting

   # File lib/sequel/extensions/date_arithmetic.rb
43 def date_add(expr, interval, opts=OPTS)
44   DateAdd.new(expr, interval, opts)
45 end
date_sub(expr, interval, opts=OPTS) click to toggle source

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

:cast

Cast to the specified type instead of the default if casting

   # File lib/sequel/extensions/date_arithmetic.rb
51 def date_sub(expr, interval, opts=OPTS)
52   interval = if interval.is_a?(Hash)
53     h = {}
54     interval.each{|k,v| h[k] = -v unless v.nil?}
55     h
56   else
57     -interval
58   end
59   DateAdd.new(expr, interval, opts)
60 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[:a].like('b')) # "table"."a" LIKE 'b' ESCAPE '\'
    # File lib/sequel/sql.rb
393 def deep_qualify(qualifier, expr)
394   Sequel::Qualifier.new(qualifier).transform(expr)
395 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
413 def delay(&block)
414   raise(Error, "Sequel.delay requires a block") unless block
415   SQL::DelayedEvaluation.new(block)
416 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
427 def desc(arg, opts=OPTS)
428   SQL::OrderedExpression.new(arg, true, opts)
429 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
447 def expr(arg=(no_arg=true), &block)
448   if block_given?
449     if no_arg
450       return expr(block)
451     else
452       raise Error, 'cannot provide both an argument and a block to Sequel.expr'
453     end
454   elsif no_arg
455     raise Error, 'must provide either an argument or a block to Sequel.expr'
456   end
457 
458   case arg
459   when Symbol
460     t, c, a = Sequel.split_symbol(arg)
461 
462     arg = if t
463       SQL::QualifiedIdentifier.new(t, c)
464     else
465       SQL::Identifier.new(c)
466     end
467 
468     if a
469       arg = SQL::AliasedExpression.new(arg, a)
470     end
471 
472     arg
473   when SQL::Expression, LiteralString, SQL::Blob
474     arg
475   when Hash
476     SQL::BooleanExpression.from_value_pairs(arg, :AND)
477   when Array
478     if condition_specifier?(arg)
479       SQL::BooleanExpression.from_value_pairs(arg, :AND)
480     else
481       SQL::Wrapper.new(arg)
482     end
483   when Numeric
484     SQL::NumericExpression.new(:NOOP, arg)
485   when String
486     SQL::StringExpression.new(:NOOP, arg)
487   when TrueClass, FalseClass
488     SQL::BooleanExpression.new(:NOOP, arg)
489   when Proc
490     expr(virtual_row(&arg))
491   else
492     SQL::Wrapper.new(arg)
493   end
494 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
500 def extract(datetime_part, exp)
501   SQL::NumericExpression.new(:extract, datetime_part, exp)
502 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
509 def function(name, *args)
510   SQL::Function.new(name, *args)
511 end
hstore(v) click to toggle source

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

    # File lib/sequel/extensions/pg_hstore.rb
302 def hstore(v)
303   case v
304   when Postgres::HStore
305     v
306   when Hash
307     Postgres::HStore.new(v)
308   else
309     # May not be defined unless the pg_hstore_ops extension is used
310     hstore_op(v)
311   end
312 end
hstore_op(v) click to toggle source

Return the object wrapped in an Postgres::HStoreOp.

    # File lib/sequel/extensions/pg_hstore_ops.rb
328 def hstore_op(v)
329   case v
330   when Postgres::HStoreOp
331     v
332   else
333     Postgres::HStoreOp.new(v)
334   end
335 end
identifier(name) click to toggle source

Return the argument wrapped as an SQL::Identifier.

Sequel.identifier(:a) # "a"
    # File lib/sequel/sql.rb
516 def identifier(name)
517   SQL::Identifier.new(name)
518 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
553 def ilike(*args)
554   SQL::StringExpression.like(*(args << {:case_insensitive=>true}))
555 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
529 def join(args, joiner=nil)
530   raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array)
531   if joiner
532     args = args.zip([joiner]*args.length).flatten
533     args.pop
534   end
535 
536   return SQL::StringExpression.new(:NOOP, '') if args.empty?
537 
538   args = args.map do |a|
539     case a
540     when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass
541       a
542     else
543       a.to_s
544     end
545   end
546   SQL::StringExpression.new(:'||', *args)
547 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
561 def like(*args)
562   SQL::StringExpression.like(*args)
563 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
578 def lit(s, *args)
579   if args.empty?
580     if s.is_a?(LiteralString)
581       s
582     else
583       LiteralString.new(s)
584     end
585   else
586     SQL::PlaceholderLiteralString.new(s, args) 
587   end
588 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
596 def negate(arg)
597   if condition_specifier?(arg)
598     SQL::BooleanExpression.from_value_pairs(arg, :AND, true)
599   else
600     raise Error, 'must pass a conditions specifier to Sequel.negate'
601   end
602 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
610 def or(arg)
611   if condition_specifier?(arg)
612     SQL::BooleanExpression.from_value_pairs(arg, :OR, false)
613   else
614     raise Error, 'must pass a conditions specifier to Sequel.or'
615   end
616 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
476 def pg_array(v, array_type=nil)
477   case v
478   when Postgres::PGArray
479     if array_type.nil? || v.array_type == array_type
480       v
481     else
482       Postgres::PGArray.new(v.to_a, array_type)
483     end
484   when Array
485     Postgres::PGArray.new(v, array_type)
486   else
487     # May not be defined unless the pg_array_ops extension is used
488     pg_array_op(v)
489   end
490 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
295 def pg_array_op(v)
296   case v
297   when Postgres::ArrayOp
298     v
299   else
300     Postgres::ArrayOp.new(v)
301   end
302 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
171 def pg_inet_op(v)
172   case v
173   when Postgres::InetOp
174     v
175   else
176     Postgres::InetOp.new(v)
177   end
178 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
285 def pg_json(v)
286   case v
287   when Postgres::JSONArray, Postgres::JSONHash
288     v
289   when Array
290     Postgres::JSONArray.new(v)
291   when Hash
292     Postgres::JSONHash.new(v)
293   when Postgres::JSONBArray
294     Postgres::JSONArray.new(v.to_a)
295   when Postgres::JSONBHash
296     Postgres::JSONHash.new(v.to_hash)
297   else
298     Sequel.pg_json_op(v)
299   end
300 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
470 def pg_json_op(v)
471   case v
472   when Postgres::JSONOp
473     v
474   else
475     Postgres::JSONOp.new(v)
476   end
477 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
303 def pg_jsonb(v)
304   case v
305   when Postgres::JSONBArray, Postgres::JSONBHash
306     v
307   when Array
308     Postgres::JSONBArray.new(v)
309   when Hash
310     Postgres::JSONBHash.new(v)
311   when Postgres::JSONArray
312     Postgres::JSONBArray.new(v.to_a)
313   when Postgres::JSONHash
314     Postgres::JSONBHash.new(v.to_hash)
315   else
316     Sequel.pg_jsonb_op(v)
317   end
318 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
480 def pg_jsonb_op(v)
481   case v
482   when Postgres::JSONBOp
483     v
484   else
485     Postgres::JSONBOp.new(v)
486   end
487 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
523 def pg_range(v, db_type=nil)
524   case v
525   when Postgres::PGRange
526     if db_type.nil? || v.db_type == db_type
527       v
528     else
529       Postgres::PGRange.new(v.begin, v.end, :exclude_begin=>v.exclude_begin?, :exclude_end=>v.exclude_end?, :db_type=>db_type)
530     end
531   when Range
532     Postgres::PGRange.from_range(v, db_type)
533   else
534     # May not be defined unless the pg_range_ops extension is used
535     pg_range_op(v)
536   end
537 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
132 def pg_range_op(v)
133   case v
134   when Postgres::RangeOp
135     v
136   else
137     Postgres::RangeOp.new(v)
138   end
139 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
547 def pg_row(expr)
548   case expr
549   when Array
550     Postgres::PGRow::ArrayRow.new(expr)
551   else
552     # Will only work if pg_row_ops extension is loaded
553     pg_row_op(expr)
554   end
555 end
pg_row_op(expr) click to toggle source

Return a PGRowOp wrapping the given expression.

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

Return a StringAgg expression for an aggregate string concatentation.

   # File lib/sequel/extensions/string_agg.rb
65 def string_agg(*a)
66   StringAgg.new(*a)
67 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
635 def subscript(exp, *subs)
636   SQL::Subscript.new(exp, subs.flatten)
637 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
644 def trim(arg)
645   SQL::Function.new!(:trim, [arg], :emulate=>true)
646 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
657 def value_list(arg)
658   raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array)
659   SQL::ValueList.new(arg)
660 end