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 329 def as(exp, aliaz, columns=nil) 330 SQL::AliasedExpression.new(exp, aliaz, columns) 331 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 342 def asc(arg, opts=OPTS) 343 SQL::OrderedExpression.new(arg, 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.
# 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
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
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
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
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
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
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
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
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
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
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 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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