Module | Sequel::SQL::Builders |
In: |
lib/sequel/sql.rb
|
Create an SQL::AliasedExpression for the given expression and alias.
Sequel.as(:column, :alias) # "column" AS "alias"
# File lib/sequel/sql.rb, line 291 291: def as(exp, aliaz) 292: SQL::AliasedExpression.new(exp, aliaz) 293: 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 304 304: def asc(arg, opts={}) 305: SQL::OrderedExpression.new(arg, false, opts) 306: 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, line 323 323: def case(*args) # core_sql ignore 324: SQL::CaseExpression.new(*args) 325: 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 332 332: def cast(arg, sql_type) 333: SQL::Cast.new(arg, sql_type) 334: 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 342 342: def cast_numeric(arg, sql_type = nil) 343: cast(arg, sql_type || Integer).sql_number 344: 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 352 352: def cast_string(arg, sql_type = nil) 353: cast(arg, sql_type || String).sql_string 354: 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 365 365: def desc(arg, opts={}) 366: SQL::OrderedExpression.new(arg, true, opts) 367: 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)
# File lib/sequel/sql.rb, line 381 381: def expr(arg=(no_arg=true), &block) 382: if block_given? 383: if no_arg 384: return expr(block) 385: else 386: raise Error, 'cannot provide both an argument and a block to Sequel.expr' 387: end 388: elsif no_arg 389: raise Error, 'must provide either an argument or a block to Sequel.expr' 390: end 391: 392: case arg 393: when SQL::Expression, LiteralString, SQL::Blob 394: arg 395: when Hash 396: SQL::BooleanExpression.from_value_pairs(arg, :AND) 397: when Array 398: if condition_specifier?(arg) 399: SQL::BooleanExpression.from_value_pairs(arg, :AND) 400: else 401: SQL::Wrapper.new(arg) 402: end 403: when Numeric 404: SQL::NumericExpression.new(:NOOP, arg) 405: when String 406: SQL::StringExpression.new(:NOOP, arg) 407: when TrueClass, FalseClass 408: SQL::BooleanExpression.new(:NOOP, arg) 409: when Proc 410: expr(virtual_row(&arg)) 411: else 412: SQL::Wrapper.new(arg) 413: end 414: 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 420 420: def extract(datetime_part, exp) 421: SQL::NumericExpression.new(:extract, datetime_part, exp) 422: 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 429 429: def function(name, *args) 430: SQL::Function.new(name, *args) 431: end
Return the argument wrapped as an SQL::Identifier.
Sequel.identifier(:a__b) # "a__b"
# File lib/sequel/sql.rb, line 436 436: def identifier(name) 437: SQL::Identifier.new(name) 438: 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%'
# File lib/sequel/sql.rb, line 473 473: def ilike(*args) 474: SQL::StringExpression.like(*(args << {:case_insensitive=>true})) 475: 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 449 449: def join(args, joiner=nil) 450: raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array) 451: if joiner 452: args = args.zip([joiner]*args.length).flatten 453: args.pop 454: end 455: 456: return SQL::StringExpression.new(:NOOP, '') if args.empty? 457: 458: args = args.map do |a| 459: case a 460: when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass 461: a 462: else 463: a.to_s 464: end 465: end 466: SQL::StringExpression.new('||''||', *args) 467: 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%'
# File lib/sequel/sql.rb, line 481 481: def like(*args) 482: SQL::StringExpression.like(*args) 483: end
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"
# File lib/sequel/sql.rb, line 498 498: def lit(s, *args) # core_sql ignore 499: if args.empty? 500: if s.is_a?(LiteralString) 501: s 502: else 503: LiteralString.new(s) 504: end 505: else 506: SQL::PlaceholderLiteralString.new(s, args) 507: end 508: 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 516 516: def negate(arg) 517: if condition_specifier?(arg) 518: SQL::BooleanExpression.from_value_pairs(arg, :AND, true) 519: else 520: raise Error, 'must pass a conditions specifier to Sequel.negate' 521: end 522: 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 530 530: def or(arg) 531: if condition_specifier?(arg) 532: SQL::BooleanExpression.from_value_pairs(arg, :OR, false) 533: else 534: raise Error, 'must pass a conditions specifier to Sequel.or' 535: end 536: 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 543 543: def qualify(qualifier, identifier) 544: SQL::QualifiedIdentifier.new(qualifier, identifier) 545: 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]
# File lib/sequel/sql.rb, line 553 553: def subscript(exp, *subs) 554: SQL::Subscript.new(exp, subs.flatten) 555: 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].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))
# File lib/sequel/sql.rb, line 566 566: def value_list(arg) 567: raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array) 568: SQL::ValueList.new(arg) 569: end