Module: Sequel::SQL::Builders
- Included in:
- Sequel
- Defined in:
- lib/sequel/sql.rb,
lib/sequel/extensions/pg_row.rb,
lib/sequel/extensions/pg_json.rb,
lib/sequel/extensions/pg_array.rb,
lib/sequel/extensions/pg_range.rb,
lib/sequel/extensions/pg_hstore.rb,
lib/sequel/extensions/pg_row_ops.rb,
lib/sequel/extensions/pg_array_ops.rb,
lib/sequel/extensions/pg_range_ops.rb,
lib/sequel/extensions/pg_hstore_ops.rb,
lib/sequel/extensions/date_arithmetic.rb
Overview
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.
Instance Method Summary collapse
-
#as(exp, aliaz) ⇒ Object
Create an SQL::AliasedExpression for the given expression and alias.
-
#asc(arg, opts = {}) ⇒ Object
Order the given argument ascending.
-
#blob(s) ⇒ Object
Return an
SQL::Blob
that holds the same data as this string. -
#case(*args) ⇒ Object
Return an
SQL::CaseExpression
created with the given arguments. -
#cast(arg, sql_type) ⇒ Object
Cast the reciever to the given SQL type.
-
#cast_numeric(arg, sql_type = nil) ⇒ Object
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. -
#cast_string(arg, sql_type = nil) ⇒ Object
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. -
#char_length(arg) ⇒ Object
Return an emulated function call for getting the number of characters in the argument:.
-
#date_add(expr, interval) ⇒ Object
Return a DateAdd expression, adding an interval to the date/timestamp expr.
-
#date_sub(expr, interval) ⇒ Object
Return a DateAdd expression, adding the negative of the interval to the date/timestamp expr.
-
#delay(&block) ⇒ Object
Return a delayed evaluation that uses the passed block.
-
#desc(arg, opts = {}) ⇒ Object
Order the given argument descending.
-
#expr(arg = (no_arg=true), &block) ⇒ Object
Wraps the given object in an appropriate Sequel wrapper.
-
#extract(datetime_part, exp) ⇒ Object
Extract a datetime_part (e.g. year, month) from the given expression:.
-
#function(name, *args) ⇒ Object
Returns a
Sequel::SQL::Function
with the function name and the given arguments. -
#hstore(v) ⇒ Object
Return a Postgres::HStore proxy for the given hash.
-
#hstore_op(v) ⇒ Object
Return the object wrapped in an Postgres::HStoreOp.
-
#identifier(name) ⇒ Object
Return the argument wrapped as an
SQL::Identifier
. -
#ilike(*args) ⇒ Object
Create a
BooleanExpression
case insensitive (if the database supports it) pattern match of the receiver with the given patterns. -
#join(args, joiner = nil) ⇒ Object
Return a
Sequel::SQL::StringExpression
representing an SQL string made up of the concatenation of the given array’s elements. -
#like(*args) ⇒ Object
Create a
SQL::BooleanExpression
case sensitive (if the database supports it) pattern match of the receiver with the given patterns. -
#lit(s, *args) ⇒ Object
Converts a string into a
Sequel::LiteralString
, in order to override string literalization, e.g.:. -
#negate(arg) ⇒ Object
Return a
Sequel::SQL::BooleanExpression
created from the condition specifier, matching none of the conditions. -
#or(arg) ⇒ Object
Return a
Sequel::SQL::BooleanExpression
created from the condition specifier, matching any of the conditions. -
#pg_array(v, array_type = nil) ⇒ Object
Return a Postgres::PGArray proxy for the given array and database array type.
-
#pg_array_op(v) ⇒ Object
Return the object wrapped in an Postgres::ArrayOp.
-
#pg_json(v) ⇒ Object
Wrap the array or hash in a Postgres::JSONArray or Postgres::JSONHash.
-
#pg_range(v, db_type = nil) ⇒ Object
Convert the object to a Postgres::PGRange.
-
#pg_range_op(v) ⇒ Object
Return the expression wrapped in the Postgres::RangeOp.
-
#pg_row(expr) ⇒ Object
Wraps the expr array in an anonymous Postgres::PGRow::ArrayRow instance.
-
#pg_row_op(expr) ⇒ Object
Return a PGRowOp wrapping the given expression.
-
#qualify(qualifier, identifier) ⇒ Object
Create a qualified identifier with the given qualifier and identifier.
-
#subscript(exp, *subs) ⇒ Object
Return an
SQL::Subscript
with the given arguments, representing an SQL array access. -
#trim(arg) ⇒ Object
Return an emulated function call for trimming a string of spaces from both sides (similar to ruby’s String#strip).
-
#value_list(arg) ⇒ Object
Return a
SQL::ValueList
created from the given array.
Instance Method Details
#as(exp, aliaz) ⇒ Object
Create an SQL::AliasedExpression for the given expression and alias.
Sequel.as(:column, :alias) # "column" AS "alias"
306 307 308 |
# File 'lib/sequel/sql.rb', line 306 def as(exp, aliaz) SQL::AliasedExpression.new(exp, aliaz) end |
#asc(arg, opts = {}) ⇒ Object
319 320 321 |
# File 'lib/sequel/sql.rb', line 319 def asc(arg, opts={}) SQL::OrderedExpression.new(arg, false, opts) end |
#blob(s) ⇒ Object
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.
326 327 328 329 330 331 332 |
# File 'lib/sequel/sql.rb', line 326 def blob(s) if s.is_a?(SQL::Blob) s else SQL::Blob.new(s) end end |
#case(*args) ⇒ Object
338 339 340 |
# File 'lib/sequel/sql.rb', line 338 def case(*args) # core_sql ignore SQL::CaseExpression.new(*args) end |
#cast(arg, sql_type) ⇒ Object
347 348 349 |
# File 'lib/sequel/sql.rb', line 347 def cast(arg, sql_type) SQL::Cast.new(arg, sql_type) end |
#cast_numeric(arg, sql_type = nil) ⇒ Object
357 358 359 |
# File 'lib/sequel/sql.rb', line 357 def cast_numeric(arg, sql_type = nil) cast(arg, sql_type || Integer).sql_number end |
#cast_string(arg, sql_type = nil) ⇒ Object
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)
367 368 369 |
# File 'lib/sequel/sql.rb', line 367 def cast_string(arg, sql_type = nil) cast(arg, sql_type || String).sql_string end |
#char_length(arg) ⇒ Object
376 377 378 |
# File 'lib/sequel/sql.rb', line 376 def char_length(arg) SQL::EmulatedFunction.new(:char_length, arg) end |
#date_add(expr, interval) ⇒ Object
Return a DateAdd expression, adding an interval to the date/timestamp expr.
31 32 33 |
# File 'lib/sequel/extensions/date_arithmetic.rb', line 31 def date_add(expr, interval) DateAdd.new(expr, interval) end |
#date_sub(expr, interval) ⇒ Object
Return a DateAdd expression, adding the negative of the interval to the date/timestamp expr.
37 38 39 40 41 42 43 44 45 46 |
# File 'lib/sequel/extensions/date_arithmetic.rb', line 37 def date_sub(expr, interval) interval = if interval.is_a?(Hash) h = {} interval.each{|k,v| h[k] = -v unless v.nil?} h else -interval end DateAdd.new(expr, interval) end |
#delay(&block) ⇒ Object
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.
396 397 398 399 |
# File 'lib/sequel/sql.rb', line 396 def delay(&block) raise(Error, "Sequel.delay requires a block") unless block SQL::DelayedEvaluation.new(block) end |
#desc(arg, opts = {}) ⇒ Object
410 411 412 |
# File 'lib/sequel/sql.rb', line 410 def desc(arg, opts={}) SQL::OrderedExpression.new(arg, true, opts) end |
#expr(arg = (no_arg=true), &block) ⇒ Object
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)
426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 |
# File 'lib/sequel/sql.rb', line 426 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 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(datetime_part, exp) ⇒ Object
Extract a datetime_part (e.g. year, month) from the given expression:
Sequel.extract(:year, :date) # extract(year FROM "date")
479 480 481 |
# File 'lib/sequel/sql.rb', line 479 def extract(datetime_part, exp) SQL::NumericExpression.new(:extract, datetime_part, exp) end |
#function(name, *args) ⇒ Object
488 489 490 |
# File 'lib/sequel/sql.rb', line 488 def function(name, *args) SQL::Function.new(name, *args) end |
#hstore(v) ⇒ Object
Return a Postgres::HStore proxy for the given hash.
307 308 309 310 311 312 313 314 315 316 317 |
# File 'lib/sequel/extensions/pg_hstore.rb', line 307 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 |
#hstore_op(v) ⇒ Object
Return the object wrapped in an Postgres::HStoreOp.
271 272 273 274 275 276 277 278 |
# File 'lib/sequel/extensions/pg_hstore_ops.rb', line 271 def hstore_op(v) case v when Postgres::HStoreOp v else Postgres::HStoreOp.new(v) end end |
#identifier(name) ⇒ Object
Return the argument wrapped as an SQL::Identifier
.
Sequel.identifier(:a__b) # "a__b"
495 496 497 |
# File 'lib/sequel/sql.rb', line 495 def identifier(name) SQL::Identifier.new(name) end |
#ilike(*args) ⇒ Object
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%'
532 533 534 |
# File 'lib/sequel/sql.rb', line 532 def ilike(*args) SQL::StringExpression.like(*(args << {:case_insensitive=>true})) end |
#join(args, joiner = nil) ⇒ Object
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
508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 |
# File 'lib/sequel/sql.rb', line 508 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 |
#like(*args) ⇒ Object
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%'
540 541 542 |
# File 'lib/sequel/sql.rb', line 540 def like(*args) SQL::StringExpression.like(*args) end |
#lit(s, *args) ⇒ Object
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"
557 558 559 560 561 562 563 564 565 566 567 |
# File 'lib/sequel/sql.rb', line 557 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 |
#negate(arg) ⇒ Object
575 576 577 578 579 580 581 |
# File 'lib/sequel/sql.rb', line 575 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 |
#or(arg) ⇒ Object
589 590 591 592 593 594 595 |
# File 'lib/sequel/sql.rb', line 589 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 |
#pg_array(v, array_type = nil) ⇒ Object
Return a Postgres::PGArray proxy for the given array and database array type.
510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 |
# File 'lib/sequel/extensions/pg_array.rb', line 510 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 |
#pg_array_op(v) ⇒ Object
Return the object wrapped in an Postgres::ArrayOp.
241 242 243 244 245 246 247 248 |
# File 'lib/sequel/extensions/pg_array_ops.rb', line 241 def pg_array_op(v) case v when Postgres::ArrayOp v else Postgres::ArrayOp.new(v) end end |
#pg_json(v) ⇒ Object
Wrap the array or hash in a Postgres::JSONArray or Postgres::JSONHash.
178 179 180 181 182 183 184 185 186 187 188 189 |
# File 'lib/sequel/extensions/pg_json.rb', line 178 def pg_json(v) case v when Postgres::JSONArray, Postgres::JSONHash v when Array Postgres::JSONArray.new(v) when Hash Postgres::JSONHash.new(v) else raise Error, "Sequel.pg_json requires a hash or array argument" end end |
#pg_range(v, db_type = nil) ⇒ Object
Convert the object to a Postgres::PGRange.
486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 |
# File 'lib/sequel/extensions/pg_range.rb', line 486 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 |
#pg_range_op(v) ⇒ Object
Return the expression wrapped in the Postgres::RangeOp.
128 129 130 131 132 133 134 135 |
# File 'lib/sequel/extensions/pg_range_ops.rb', line 128 def pg_range_op(v) case v when Postgres::RangeOp v else Postgres::RangeOp.new(v) end end |
#pg_row(expr) ⇒ Object
Wraps the expr array in an anonymous Postgres::PGRow::ArrayRow instance.
561 562 563 564 565 566 567 568 569 |
# File 'lib/sequel/extensions/pg_row.rb', line 561 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 |
#pg_row_op(expr) ⇒ Object
Return a PGRowOp wrapping the given expression.
165 166 167 |
# File 'lib/sequel/extensions/pg_row_ops.rb', line 165 def pg_row_op(expr) Postgres::PGRowOp.wrap(expr) end |
#qualify(qualifier, identifier) ⇒ Object
602 603 604 |
# File 'lib/sequel/sql.rb', line 602 def qualify(qualifier, identifier) SQL::QualifiedIdentifier.new(qualifier, identifier) end |
#subscript(exp, *subs) ⇒ Object
612 613 614 |
# File 'lib/sequel/sql.rb', line 612 def subscript(exp, *subs) SQL::Subscript.new(exp, subs.flatten) end |
#trim(arg) ⇒ Object
621 622 623 |
# File 'lib/sequel/sql.rb', line 621 def trim(arg) SQL::EmulatedFunction.new(:trim, arg) end |
#value_list(arg) ⇒ Object
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))
634 635 636 637 |
# File 'lib/sequel/sql.rb', line 634 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 |