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
329 def as(exp, aliaz, columns=nil)
330   SQL::AliasedExpression.new(exp, aliaz, columns)
331 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
342 def asc(arg, opts=OPTS)
343   SQL::OrderedExpression.new(arg, false, opts)
344 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
349 def blob(s)
350   if s.is_a?(SQL::Blob)
351     s
352   else
353     SQL::Blob.new(s)
354   end
355 end
case(*args) click to toggle source

Return an SQL::CaseExpression created with the given arguments. The first argument are the WHEN/THEN conditions, specified as an array or a hash. The second argument is the ELSE default value. The third optional argument is the CASE expression.

Sequel.case({a: 1}, 0) # SQL: CASE WHEN a THEN 1 ELSE 0 END
Sequel.case({a: 1}, 0, :b) # SQL: CASE b WHEN a THEN 1 ELSE 0 END
Sequel.case({{a: [2,3]} => 1}, 0) # SQL: CASE WHEN a IN (2, 3) THEN 1 ELSE 0 END
Sequel.case([[{a: [2,3]}, 1]], 0) # SQL: CASE WHEN a IN (2, 3) THEN 1 ELSE 0 END
    # File lib/sequel/sql.rb
367 def case(*args)
368   SQL::CaseExpression.new(*args)
369 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
376 def cast(arg, sql_type)
377   SQL::Cast.new(arg, sql_type)
378 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
386 def cast_numeric(arg, sql_type = nil)
387   cast(arg, sql_type || Integer).sql_number
388 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
396 def cast_string(arg, sql_type = nil)
397   cast(arg, sql_type || String).sql_string
398 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
405 def char_length(arg)
406   SQL::Function.new!(:char_length, [arg], :emulate=>true)
407 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
415 def deep_qualify(qualifier, expr)
416   Sequel::Qualifier.new(qualifier).transform(expr)
417 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
435 def delay(&block)
436   raise(Error, "Sequel.delay requires a block") unless block
437   SQL::DelayedEvaluation.new(block)
438 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
449 def desc(arg, opts=OPTS)
450   SQL::OrderedExpression.new(arg, true, opts)
451 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
469 def expr(arg=(no_arg=true), &block)
470   if block_given?
471     if no_arg
472       return expr(block)
473     else
474       raise Error, 'cannot provide both an argument and a block to Sequel.expr'
475     end
476   elsif no_arg
477     raise Error, 'must provide either an argument or a block to Sequel.expr'
478   end
479 
480   case arg
481   when Symbol
482     t, c, a = Sequel.split_symbol(arg)
483 
484     arg = if t
485       SQL::QualifiedIdentifier.new(t, c)
486     else
487       SQL::Identifier.new(c)
488     end
489 
490     if a
491       arg = SQL::AliasedExpression.new(arg, a)
492     end
493 
494     arg
495   when SQL::Expression, LiteralString, SQL::Blob
496     arg
497   when Hash
498     SQL::BooleanExpression.from_value_pairs(arg, :AND)
499   when Array
500     if condition_specifier?(arg)
501       SQL::BooleanExpression.from_value_pairs(arg, :AND)
502     else
503       SQL::Wrapper.new(arg)
504     end
505   when Numeric
506     SQL::NumericExpression.new(:NOOP, arg)
507   when String
508     SQL::StringExpression.new(:NOOP, arg)
509   when TrueClass, FalseClass
510     SQL::BooleanExpression.new(:NOOP, arg)
511   when Proc
512     expr(virtual_row(&arg))
513   else
514     SQL::Wrapper.new(arg)
515   end
516 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
522 def extract(datetime_part, exp)
523   SQL::NumericExpression.new(:extract, datetime_part, exp)
524 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
531 def function(name, *args)
532   SQL::Function.new(name, *args)
533 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
538 def identifier(name)
539   SQL::Identifier.new(name)
540 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
575 def ilike(*args)
576   SQL::StringExpression.like(*(args << {:case_insensitive=>true}))
577 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
551 def join(args, joiner=nil)
552   raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array)
553   if joiner
554     args = args.zip([joiner]*args.length).flatten
555     args.pop
556   end
557 
558   return SQL::StringExpression.new(:NOOP, '') if args.empty?
559 
560   args = args.map do |a|
561     case a
562     when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass
563       a
564     else
565       a.to_s
566     end
567   end
568   SQL::StringExpression.new(:'||', *args)
569 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
583 def like(*args)
584   SQL::StringExpression.like(*args)
585 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
600 def lit(s, *args)
601   if args.empty?
602     if s.is_a?(LiteralString)
603       s
604     else
605       LiteralString.new(s)
606     end
607   else
608     SQL::PlaceholderLiteralString.new(s, args) 
609   end
610 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
618 def negate(arg)
619   if condition_specifier?(arg)
620     SQL::BooleanExpression.from_value_pairs(arg, :AND, true)
621   else
622     raise Error, 'must pass a conditions specifier to Sequel.negate'
623   end
624 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
632 def or(arg)
633   if condition_specifier?(arg)
634     SQL::BooleanExpression.from_value_pairs(arg, :OR, false)
635   else
636     raise Error, 'must pass a conditions specifier to Sequel.or'
637   end
638 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
484 def pg_array(v, array_type=nil)
485   case v
486   when Postgres::PGArray
487     if array_type.nil? || v.array_type == array_type
488       v
489     else
490       Postgres::PGArray.new(v.to_a, array_type)
491     end
492   when Array
493     Postgres::PGArray.new(v, array_type)
494   else
495     # May not be defined unless the pg_array_ops extension is used
496     pg_array_op(v)
497   end
498 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. Also handles Postgres::JSONObject and JSONBObjects. For other objects, calls Sequel.pg_json_op (which is defined by the pg_json_ops extension).

    # File lib/sequel/extensions/pg_json.rb
524 def pg_json(v)
525   case v
526   when Postgres::JSONObject
527     v
528   when Array
529     Postgres::JSONArray.new(v)
530   when Hash
531     Postgres::JSONHash.new(v)
532   when Postgres::JSONBObject
533     v = v.__getobj__
534     Postgres::JSONDatabaseMethods.json_primitive_wrapper(v).new(v)
535   else
536     Sequel.pg_json_op(v)
537   end
538 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
594 def pg_json_op(v)
595   case v
596   when Postgres::JSONOp
597     v
598   else
599     Postgres::JSONOp.new(v)
600   end
601 end
pg_json_wrap(v) click to toggle source

Wraps Ruby array, hash, string, integer, float, true, false, and nil values with the appropriate JSON wrapper. Raises an exception for other types.

    # File lib/sequel/extensions/pg_json.rb
543 def pg_json_wrap(v)
544   case v
545   when *Postgres::JSON_WRAP_CLASSES
546     Postgres::JSONDatabaseMethods.json_primitive_wrapper(v).new(v)
547   else
548     raise Error, "invalid value passed to Sequel.pg_json_wrap: #{v.inspect}"
549   end
550 end
pg_jsonb(v) click to toggle source

Wrap the array or hash in a Postgres::JSONBArray or Postgres::JSONBHash. Also handles Postgres::JSONObject and JSONBObjects. For other objects, calls Sequel.pg_json_op (which is defined by the pg_json_ops extension).

    # File lib/sequel/extensions/pg_json.rb
556 def pg_jsonb(v)
557   case v
558   when Postgres::JSONBObject
559     v
560   when Array
561     Postgres::JSONBArray.new(v)
562   when Hash
563     Postgres::JSONBHash.new(v)
564   when Postgres::JSONObject
565     v = v.__getobj__
566     Postgres::JSONDatabaseMethods.jsonb_primitive_wrapper(v).new(v)
567   else
568     Sequel.pg_jsonb_op(v)
569   end
570 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
604 def pg_jsonb_op(v)
605   case v
606   when Postgres::JSONBOp
607     v
608   else
609     Postgres::JSONBOp.new(v)
610   end
611 end
pg_jsonb_wrap(v) click to toggle source

Wraps Ruby array, hash, string, integer, float, true, false, and nil values with the appropriate JSONB wrapper. Raises an exception for other types.

    # File lib/sequel/extensions/pg_json.rb
575 def pg_jsonb_wrap(v)
576   case v
577   when *Postgres::JSON_WRAP_CLASSES
578     Postgres::JSONDatabaseMethods.jsonb_primitive_wrapper(v).new(v)
579   else
580     raise Error, "invalid value passed to Sequel.pg_jsonb_wrap: #{v.inspect}"
581   end
582 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
535 def pg_range(v, db_type=nil)
536   case v
537   when Postgres::PGRange
538     if db_type.nil? || v.db_type == db_type
539       v
540     else
541       Postgres::PGRange.new(v.begin, v.end, :exclude_begin=>v.exclude_begin?, :exclude_end=>v.exclude_end?, :db_type=>db_type)
542     end
543   when Range
544     Postgres::PGRange.from_range(v, db_type)
545   else
546     # May not be defined unless the pg_range_ops extension is used
547     pg_range_op(v)
548   end
549 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
549 def pg_row(expr)
550   case expr
551   when Array
552     Postgres::PGRow::ArrayRow.new(expr)
553   else
554     # Will only work if pg_row_ops extension is loaded
555     pg_row_op(expr)
556   end
557 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
645 def qualify(qualifier, identifier)
646   SQL::QualifiedIdentifier.new(qualifier, identifier)
647 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
657 def subscript(exp, *subs)
658   SQL::Subscript.new(exp, subs.flatten)
659 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
666 def trim(arg)
667   SQL::Function.new!(:trim, [arg], :emulate=>true)
668 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
679 def value_list(arg)
680   raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array)
681   SQL::ValueList.new(arg)
682 end