Module: Sequel::SQL::Builders
- Included in:
- Sequel
- Defined in:
- lib/sequel/sql.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. -
#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. -
#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. -
#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. -
#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"
290 291 292 |
# File 'lib/sequel/sql.rb', line 290 def as(exp, aliaz) SQL::AliasedExpression.new(exp, aliaz) end |
#asc(arg, opts = {}) ⇒ Object
303 304 305 |
# File 'lib/sequel/sql.rb', line 303 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.
310 311 312 313 314 315 316 |
# File 'lib/sequel/sql.rb', line 310 def blob(s) if s.is_a?(SQL::Blob) s else SQL::Blob.new(s) end end |
#case(*args) ⇒ Object
322 323 324 |
# File 'lib/sequel/sql.rb', line 322 def case(*args) # core_sql ignore SQL::CaseExpression.new(*args) end |
#cast(arg, sql_type) ⇒ Object
331 332 333 |
# File 'lib/sequel/sql.rb', line 331 def cast(arg, sql_type) SQL::Cast.new(arg, sql_type) end |
#cast_numeric(arg, sql_type = nil) ⇒ Object
341 342 343 |
# File 'lib/sequel/sql.rb', line 341 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)
351 352 353 |
# File 'lib/sequel/sql.rb', line 351 def cast_string(arg, sql_type = nil) cast(arg, sql_type || String).sql_string end |
#desc(arg, opts = {}) ⇒ Object
364 365 366 |
# File 'lib/sequel/sql.rb', line 364 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)
380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 |
# 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(datetime_part, exp) ⇒ Object
Extract a datetime_part (e.g. year, month) from the given expression:
Sequel.extract(:year, :date) # extract(year FROM "date")
419 420 421 |
# File 'lib/sequel/sql.rb', line 419 def extract(datetime_part, exp) SQL::NumericExpression.new(:extract, datetime_part, exp) end |
#function(name, *args) ⇒ Object
428 429 430 |
# File 'lib/sequel/sql.rb', line 428 def function(name, *args) SQL::Function.new(name, *args) end |
#identifier(name) ⇒ Object
Return the argument wrapped as an SQL::Identifier
.
Sequel.identifier(:a__b) # "a__b"
435 436 437 |
# File 'lib/sequel/sql.rb', line 435 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%'
472 473 474 |
# File 'lib/sequel/sql.rb', line 472 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
448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 |
# 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 |
#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%'
480 481 482 |
# File 'lib/sequel/sql.rb', line 480 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"
497 498 499 500 501 502 503 504 505 506 507 |
# 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 |
#negate(arg) ⇒ Object
515 516 517 518 519 520 521 |
# 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 |
#or(arg) ⇒ Object
529 530 531 532 533 534 535 |
# 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 |
#qualify(qualifier, identifier) ⇒ Object
542 543 544 |
# File 'lib/sequel/sql.rb', line 542 def qualify(qualifier, identifier) SQL::QualifiedIdentifier.new(qualifier, identifier) end |
#subscript(exp, *subs) ⇒ Object
552 553 554 |
# File 'lib/sequel/sql.rb', line 552 def subscript(exp, *subs) SQL::Subscript.new(exp, subs.flatten) 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))
565 566 567 568 |
# 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 |