module Sequel::SQL::Builders
These methods make it easier to create Sequel expressions without using the core extensions.
Public Instance Methods
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 328 def as(exp, aliaz, columns=nil) SQL::AliasedExpression.new(exp, aliaz, columns) end
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 341 def asc(arg, opts=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 348 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. 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, line 366 def case(*args) 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 375 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 385 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 395 def cast_string(arg, sql_type = nil) cast(arg, sql_type || String).sql_string 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
# File lib/sequel/sql.rb, line 404 def char_length(arg) SQL::Function.new!(:char_length, [arg], :emulate=>true) end
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, line 55 def date_sub(expr, interval, opts=OPTS) if defined?(ActiveSupport::Duration) && interval.is_a?(ActiveSupport::Duration) interval = interval.parts end parts = {} interval.each do |k,v| case v when nil # ignore when Numeric parts[k] = -v else parts[k] = Sequel::SQL::NumericExpression.new(:*, v, -1) end end DateAdd.new(expr, parts, opts) end
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, line 414 def deep_qualify(qualifier, expr) Sequel::Qualifier.new(qualifier).transform(expr) 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 > 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 434 def delay(&block) raise(Error, "Sequel.delay requires a block") unless block SQL::DelayedEvaluation.new(block) end
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 448 def desc(arg, opts=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)
On the Sequel module, this is aliased as [], for easier use:
Sequel[1] - :a # SQL: (1 - a)
# File lib/sequel/sql.rb, line 468 def expr(arg=(no_arg=true), &block) if defined?(yield) 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 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 521 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 530 def function(name, *args) SQL::Function.new(name, *args) end
Return a Postgres::HStore proxy for the given hash.
# File lib/sequel/extensions/pg_hstore.rb, line 312 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
Return the object wrapped in an Postgres::HStoreOp.
# File lib/sequel/extensions/pg_hstore_ops.rb, line 379 def hstore_op(v) case v when Postgres::HStoreOp v else Postgres::HStoreOp.new(v) end end
Return the argument wrapped as an SQL::Identifier
.
Sequel.identifier(:a) # "a"
# File lib/sequel/sql.rb, line 537 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%' ESCAPE '\'
# File lib/sequel/sql.rb, line 574 def ilike(*args) SQL::StringExpression.like(*(args << {:case_insensitive=>true})) end
Return a IsDistinctFrom expression object, using the IS DISTINCT FROM operator with the given left hand side and right hand side.
# File lib/sequel/extensions/is_distinct_from.rb, line 36 def is_distinct_from(lhs, rhs) BooleanExpression.new(:NOOP, IsDistinctFrom.new(lhs, rhs)) 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 550 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%' ESCAPE '\'
# File lib/sequel/sql.rb, line 582 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].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 599 def lit(s, *args) 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 617 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 631 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
Return a Postgres::PGArray proxy for the given array and database array type.
# File lib/sequel/extensions/pg_array.rb, line 513 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
Return the object wrapped in an Postgres::ArrayOp.
# File lib/sequel/extensions/pg_array_ops.rb, line 302 def pg_array_op(v) case v when Postgres::ArrayOp v else Postgres::ArrayOp.new(v) end end
Return the expression wrapped in the Postgres::InetOp.
# File lib/sequel/extensions/pg_inet_ops.rb, line 170 def pg_inet_op(v) case v when Postgres::InetOp v else Postgres::InetOp.new(v) end end
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, line 521 def pg_json(v) case v when Postgres::JSONObject v when Array Postgres::JSONArray.new(v) when Hash Postgres::JSONHash.new(v) when Postgres::JSONBObject v = v.__getobj__ Postgres::JSONDatabaseMethods.json_primitive_wrapper(v).new(v) else Sequel.pg_json_op(v) end end
Return the object wrapped in an Postgres::JSONOp.
# File lib/sequel/extensions/pg_json_ops.rb, line 760 def pg_json_op(v) case v when Postgres::JSONOp v else Postgres::JSONOp.new(v) end end
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, line 540 def pg_json_wrap(v) case v when *Postgres::JSON_WRAP_CLASSES Postgres::JSONDatabaseMethods.json_primitive_wrapper(v).new(v) else raise Error, "invalid value passed to Sequel.pg_json_wrap: #{v.inspect}" end end
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, line 553 def pg_jsonb(v) case v when Postgres::JSONBObject v when Array Postgres::JSONBArray.new(v) when Hash Postgres::JSONBHash.new(v) when Postgres::JSONObject v = v.__getobj__ Postgres::JSONDatabaseMethods.jsonb_primitive_wrapper(v).new(v) else Sequel.pg_jsonb_op(v) end end
Return the object wrapped in an Postgres::JSONBOp.
# File lib/sequel/extensions/pg_json_ops.rb, line 770 def pg_jsonb_op(v) case v when Postgres::JSONBOp v else Postgres::JSONBOp.new(v) end end
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, line 572 def pg_jsonb_wrap(v) case v when *Postgres::JSON_WRAP_CLASSES Postgres::JSONDatabaseMethods.jsonb_primitive_wrapper(v).new(v) else raise Error, "invalid value passed to Sequel.pg_jsonb_wrap: #{v.inspect}" end end
Convert the object to a Postgres::PGMultiRange.
# File lib/sequel/extensions/pg_multirange.rb, line 348 def pg_multirange(v, db_type) case v when Postgres::PGMultiRange if v.db_type == db_type v else Postgres::PGMultiRange.new(v, db_type) end when Array Postgres::PGMultiRange.new(v, db_type) else # May not be defined unless the pg_range_ops extension is used pg_range_op(v) end end
Convert the object to a Postgres::PGRange.
# File lib/sequel/extensions/pg_range.rb, line 519 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
Return the expression wrapped in the Postgres::RangeOp.
# File lib/sequel/extensions/pg_range_ops.rb, line 161 def pg_range_op(v) case v when Postgres::RangeOp v else Postgres::RangeOp.new(v) end end
Wraps the expr array in an anonymous Postgres::PGRow::ArrayRow instance.
# File lib/sequel/extensions/pg_row.rb, line 549 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
Return a PGRowOp wrapping the given expression.
# File lib/sequel/extensions/pg_row_ops.rb, line 188 def pg_row_op(expr) Postgres::PGRowOp.wrap(expr) 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 644 def qualify(qualifier, identifier) SQL::QualifiedIdentifier.new(qualifier, identifier) end
Skip auto parameterization for the given object when building queries.
# File lib/sequel/extensions/pg_auto_parameterize.rb, line 502 def skip_pg_auto_param(v) Postgres::AutoParameterize::SkipAutoParam.new(v) end
Return the object wrapped in an SQLite::JSONOp.
# File lib/sequel/extensions/sqlite_json_ops.rb, line 269 def sqlite_json_op(v) case v when SQLite::JSONOp v else SQLite::JSONOp.new(v) end end
Return the object wrapped in an SQLite::JSONBOp.
# File lib/sequel/extensions/sqlite_json_ops.rb, line 279 def sqlite_jsonb_op(v) case v when SQLite::JSONBOp v else SQLite::JSONBOp.new(v) end end
Return a StringAgg expression for an aggregate string concatentation.
# File lib/sequel/extensions/string_agg.rb, line 64 def string_agg(*a) StringAgg.new(*a) 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] Sequel.subscript(:array, 1..2) # array[1:2] Sequel.subscript(:array, 1...3) # array[1:2]
# File lib/sequel/sql.rb, line 656 def subscript(exp, *subs) SQL::Subscript.new(exp, subs.flatten) 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
# File lib/sequel/sql.rb, line 665 def trim(arg) SQL::Function.new!(:trim, [arg], :emulate=>true) 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))
# File lib/sequel/sql.rb, line 678 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