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.
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
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
# File lib/sequel/sql.rb, line 303 def asc(arg, opts={}) SQL::OrderedExpression.new(arg, false, opts) 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.
# File lib/sequel/sql.rb, line 310 def blob(s) if s.is_a?(SQL::Blob) s else SQL::Blob.new(s) end end
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 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 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 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
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
# File lib/sequel/sql.rb, line 364 def desc(arg, opts={}) SQL::OrderedExpression.new(arg, true, opts) end
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 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
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
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
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
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
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
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
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
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
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
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
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