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 313 def as(exp, aliaz, columns=nil) 314 SQL::AliasedExpression.new(exp, aliaz, columns) 315 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 326 def asc(arg, opts=OPTS) 327 SQL::OrderedExpression.new(arg, false, opts) 328 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 333 def blob(s) 334 if s.is_a?(SQL::Blob) 335 s 336 else 337 SQL::Blob.new(s) 338 end 339 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 345 def case(*args) 346 SQL::CaseExpression.new(*args) 347 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 354 def cast(arg, sql_type) 355 SQL::Cast.new(arg, sql_type) 356 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 364 def cast_numeric(arg, sql_type = nil) 365 cast(arg, sql_type || Integer).sql_number 366 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 374 def cast_string(arg, sql_type = nil) 375 cast(arg, sql_type || String).sql_string 376 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 383 def char_length(arg) 384 SQL::Function.new!(:char_length, [arg], :emulate=>true) 385 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 393 def deep_qualify(qualifier, expr) 394 Sequel::Qualifier.new(qualifier).transform(expr) 395 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 413 def delay(&block) 414 raise(Error, "Sequel.delay requires a block") unless block 415 SQL::DelayedEvaluation.new(block) 416 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 427 def desc(arg, opts=OPTS) 428 SQL::OrderedExpression.new(arg, true, opts) 429 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 447 def expr(arg=(no_arg=true), &block) 448 if block_given? 449 if no_arg 450 return expr(block) 451 else 452 raise Error, 'cannot provide both an argument and a block to Sequel.expr' 453 end 454 elsif no_arg 455 raise Error, 'must provide either an argument or a block to Sequel.expr' 456 end 457 458 case arg 459 when Symbol 460 t, c, a = Sequel.split_symbol(arg) 461 462 arg = if t 463 SQL::QualifiedIdentifier.new(t, c) 464 else 465 SQL::Identifier.new(c) 466 end 467 468 if a 469 arg = SQL::AliasedExpression.new(arg, a) 470 end 471 472 arg 473 when SQL::Expression, LiteralString, SQL::Blob 474 arg 475 when Hash 476 SQL::BooleanExpression.from_value_pairs(arg, :AND) 477 when Array 478 if condition_specifier?(arg) 479 SQL::BooleanExpression.from_value_pairs(arg, :AND) 480 else 481 SQL::Wrapper.new(arg) 482 end 483 when Numeric 484 SQL::NumericExpression.new(:NOOP, arg) 485 when String 486 SQL::StringExpression.new(:NOOP, arg) 487 when TrueClass, FalseClass 488 SQL::BooleanExpression.new(:NOOP, arg) 489 when Proc 490 expr(virtual_row(&arg)) 491 else 492 SQL::Wrapper.new(arg) 493 end 494 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 500 def extract(datetime_part, exp) 501 SQL::NumericExpression.new(:extract, datetime_part, exp) 502 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 509 def function(name, *args) 510 SQL::Function.new(name, *args) 511 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 516 def identifier(name) 517 SQL::Identifier.new(name) 518 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 553 def ilike(*args) 554 SQL::StringExpression.like(*(args << {:case_insensitive=>true})) 555 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 529 def join(args, joiner=nil) 530 raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array) 531 if joiner 532 args = args.zip([joiner]*args.length).flatten 533 args.pop 534 end 535 536 return SQL::StringExpression.new(:NOOP, '') if args.empty? 537 538 args = args.map do |a| 539 case a 540 when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass 541 a 542 else 543 a.to_s 544 end 545 end 546 SQL::StringExpression.new(:'||', *args) 547 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 561 def like(*args) 562 SQL::StringExpression.like(*args) 563 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 578 def lit(s, *args) 579 if args.empty? 580 if s.is_a?(LiteralString) 581 s 582 else 583 LiteralString.new(s) 584 end 585 else 586 SQL::PlaceholderLiteralString.new(s, args) 587 end 588 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 596 def negate(arg) 597 if condition_specifier?(arg) 598 SQL::BooleanExpression.from_value_pairs(arg, :AND, true) 599 else 600 raise Error, 'must pass a conditions specifier to Sequel.negate' 601 end 602 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 610 def or(arg) 611 if condition_specifier?(arg) 612 SQL::BooleanExpression.from_value_pairs(arg, :OR, false) 613 else 614 raise Error, 'must pass a conditions specifier to Sequel.or' 615 end 616 end
Return a Postgres::PGArray
proxy for the given array and database array type.
# File lib/sequel/extensions/pg_array.rb 476 def pg_array(v, array_type=nil) 477 case v 478 when Postgres::PGArray 479 if array_type.nil? || v.array_type == array_type 480 v 481 else 482 Postgres::PGArray.new(v.to_a, array_type) 483 end 484 when Array 485 Postgres::PGArray.new(v, array_type) 486 else 487 # May not be defined unless the pg_array_ops extension is used 488 pg_array_op(v) 489 end 490 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
.
# File lib/sequel/extensions/pg_json.rb 285 def pg_json(v) 286 case v 287 when Postgres::JSONArray, Postgres::JSONHash 288 v 289 when Array 290 Postgres::JSONArray.new(v) 291 when Hash 292 Postgres::JSONHash.new(v) 293 when Postgres::JSONBArray 294 Postgres::JSONArray.new(v.to_a) 295 when Postgres::JSONBHash 296 Postgres::JSONHash.new(v.to_hash) 297 else 298 Sequel.pg_json_op(v) 299 end 300 end
Return the object wrapped in an Postgres::JSONOp
.
# File lib/sequel/extensions/pg_json_ops.rb 470 def pg_json_op(v) 471 case v 472 when Postgres::JSONOp 473 v 474 else 475 Postgres::JSONOp.new(v) 476 end 477 end
Wrap the array or hash in a Postgres::JSONBArray
or Postgres::JSONBHash
.
# File lib/sequel/extensions/pg_json.rb 303 def pg_jsonb(v) 304 case v 305 when Postgres::JSONBArray, Postgres::JSONBHash 306 v 307 when Array 308 Postgres::JSONBArray.new(v) 309 when Hash 310 Postgres::JSONBHash.new(v) 311 when Postgres::JSONArray 312 Postgres::JSONBArray.new(v.to_a) 313 when Postgres::JSONHash 314 Postgres::JSONBHash.new(v.to_hash) 315 else 316 Sequel.pg_jsonb_op(v) 317 end 318 end
Return the object wrapped in an Postgres::JSONBOp
.
# File lib/sequel/extensions/pg_json_ops.rb 480 def pg_jsonb_op(v) 481 case v 482 when Postgres::JSONBOp 483 v 484 else 485 Postgres::JSONBOp.new(v) 486 end 487 end
Convert the object to a Postgres::PGRange
.
# File lib/sequel/extensions/pg_range.rb 523 def pg_range(v, db_type=nil) 524 case v 525 when Postgres::PGRange 526 if db_type.nil? || v.db_type == db_type 527 v 528 else 529 Postgres::PGRange.new(v.begin, v.end, :exclude_begin=>v.exclude_begin?, :exclude_end=>v.exclude_end?, :db_type=>db_type) 530 end 531 when Range 532 Postgres::PGRange.from_range(v, db_type) 533 else 534 # May not be defined unless the pg_range_ops extension is used 535 pg_range_op(v) 536 end 537 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 547 def pg_row(expr) 548 case expr 549 when Array 550 Postgres::PGRow::ArrayRow.new(expr) 551 else 552 # Will only work if pg_row_ops extension is loaded 553 pg_row_op(expr) 554 end 555 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 623 def qualify(qualifier, identifier) 624 SQL::QualifiedIdentifier.new(qualifier, identifier) 625 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 635 def subscript(exp, *subs) 636 SQL::Subscript.new(exp, subs.flatten) 637 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 644 def trim(arg) 645 SQL::Function.new!(:trim, [arg], :emulate=>true) 646 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 657 def value_list(arg) 658 raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array) 659 SQL::ValueList.new(arg) 660 end