module Sequel::SQL::Builders

  1. lib/sequel/sql.rb

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

Public Instance methods

as(exp, aliaz, columns=nil)

Create an SQL::AliasedExpression for the given expression and alias., :alias) # "column" AS "alias", :alias, [:col_alias1, :col_alias2]) # "column" AS "alias"("col_alias1", "col_alias2")
[show source]
    # File lib/sequel/sql.rb
329 def as(exp, aliaz, columns=nil)
330, aliaz, columns)
331 end
asc(arg, opts=OPTS)

Order the given argument ascending. Options:


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
[show source]
    # File lib/sequel/sql.rb
342 def asc(arg, opts=OPTS)
343, false, opts)
344 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.

[show source]
    # File lib/sequel/sql.rb
349 def blob(s)
350   if s.is_a?(SQL::Blob)
351     s
352   else
354   end
355 end

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.{a: 1}, 0) # SQL: CASE WHEN a THEN 1 ELSE 0 END{a: 1}, 0, :b) # SQL: CASE b WHEN a THEN 1 ELSE 0 END{{a: [2,3]} => 1}, 0) # SQL: CASE WHEN a IN (2, 3) THEN 1 ELSE 0 END[[{a: [2,3]}, 1]], 0) # SQL: CASE WHEN a IN (2, 3) THEN 1 ELSE 0 END
[show source]
    # File lib/sequel/sql.rb
367 def case(*args)
369 end
cast(arg, sql_type)

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))
[show source]
    # File lib/sequel/sql.rb
376 def cast(arg, sql_type)
377, sql_type)
378 end
cast_numeric(arg, sql_type = nil)

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)
[show source]
    # 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)

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)
[show source]
    # File lib/sequel/sql.rb
396 def cast_string(arg, sql_type = nil)
397   cast(arg, sql_type || String).sql_string
398 end

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
[show source]
    # File lib/sequel/sql.rb
405 def char_length(arg)
406!(:char_length, [arg], :emulate=>true)
407 end
deep_qualify(qualifier, expr)

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 '\'
[show source]
    # File lib/sequel/sql.rb
415 def deep_qualify(qualifier, expr)
417 end

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

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{}}

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.

[show source]
    # File lib/sequel/sql.rb
435 def delay(&block)
436   raise(Error, "Sequel.delay requires a block") unless block
438 end
desc(arg, opts=OPTS)

Order the given argument descending. Options:


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
[show source]
    # File lib/sequel/sql.rb
449 def desc(arg, opts=OPTS)
450, true, opts)
451 end
expr(arg=(no_arg=true), &block)

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)
[show source]
    # File lib/sequel/sql.rb
469 def expr(arg=(no_arg=true), &block)
470   if defined?(yield)
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
480   case arg
481   when Symbol
482     t, c, a = Sequel.split_symbol(arg)
484     arg = if t
485, c)
486     else
488     end
490     if a
491       arg =, a)
492     end
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
504     end
505   when Numeric
506, arg)
507   when String
508, arg)
509   when TrueClass, FalseClass
510, arg)
511   when Proc
512     expr(virtual_row(&arg))
513   else
515   end
516 end
extract(datetime_part, exp)

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

Sequel.extract(:year, :date) # extract(year FROM "date")
[show source]
    # File lib/sequel/sql.rb
522 def extract(datetime_part, exp)
523, datetime_part, exp)
524 end
function(name, *args)

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)
[show source]
    # File lib/sequel/sql.rb
531 def function(name, *args)
532, *args)
533 end

Return the argument wrapped as an SQL::Identifier.

Sequel.identifier(:a) # "a"
[show source]
    # File lib/sequel/sql.rb
538 def identifier(name)
540 end

Create a BooleanExpression case insensitive (if the database supports it) pattern match of the receiver with the given patterns. See

Sequel.ilike(:a, 'A%') # "a" ILIKE 'A%' ESCAPE '\'
[show source]
    # File lib/sequel/sql.rb
575 def ilike(*args)
576*(args << {:case_insensitive=>true}))
577 end
join(args, joiner=nil)

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
[show source]
    # 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 =[joiner]*args.length).flatten
555     args.pop
556   end
558   return, '') if args.empty?
560   args = 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'||', *args)
569 end

Create a SQL::BooleanExpression case sensitive (if the database supports it) pattern match of the receiver with the given patterns. See, 'A%') # "a" LIKE 'A%' ESCAPE '\'
[show source]
    # File lib/sequel/sql.rb
583 def like(*args)
585 end
lit(s, *args)

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"
[show source]
    # File lib/sequel/sql.rb
600 def lit(s, *args)
601   if args.empty?
602     if s.is_a?(LiteralString)
603       s
604     else
606     end
607   else
608, args) 
609   end
610 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))
[show source]
    # 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

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))
[show source]
    # 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
qualify(qualifier, identifier)

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"
[show source]
    # File lib/sequel/sql.rb
645 def qualify(qualifier, identifier)
646, identifier)
647 end
subscript(exp, *subs)

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]
[show source]
    # File lib/sequel/sql.rb
657 def subscript(exp, *subs)
658, subs.flatten)
659 end

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
[show source]
    # File lib/sequel/sql.rb
666 def trim(arg)
667!(:trim, [arg], :emulate=>true)
668 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].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))
[show source]
    # 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)
682 end