Create an SQL::AliasedExpression for the given expression and alias.
Sequel.as(:column, :alias) # "column" AS "alias"
# File lib/sequel/sql.rb, line 313 313: def as(exp, aliaz) 314: SQL::AliasedExpression.new(exp, aliaz) 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, line 326 326: def asc(arg, opts=OPTS) 327: SQL::OrderedExpression.new(arg, false, opts) 328: 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 345 345: def case(*args) # core_sql ignore 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, line 354 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, line 364 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, line 374 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, line 383 383: def char_length(arg) 384: SQL::EmulatedFunction.new(:char_length, arg) 385: end
Return a DateAdd expression, adding the negative of the interval to the date/timestamp expr.
# File lib/sequel/extensions/date_arithmetic.rb, line 37 37: def date_sub(expr, interval) 38: interval = if interval.is_a?(Hash) 39: h = {} 40: interval.each{|k,v| h[k] = -v unless v.nil?} 41: h 42: else 43: -interval 44: end 45: DateAdd.new(expr, interval) 46: 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.like(:a, 'b')) # "table"."a" LIKE 'b' ESCAPE '\'
# File lib/sequel/sql.rb, line 393 393: def deep_qualify(qualifier, expr) 394: Sequel::Qualifier.new(Sequel, 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, line 413 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, line 427 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)
# File lib/sequel/sql.rb, line 443 443: def expr(arg=(no_arg=true), &block) 444: if block_given? 445: if no_arg 446: return expr(block) 447: else 448: raise Error, 'cannot provide both an argument and a block to Sequel.expr' 449: end 450: elsif no_arg 451: raise Error, 'must provide either an argument or a block to Sequel.expr' 452: end 453: 454: case arg 455: when Symbol 456: t, c, a = Sequel.split_symbol(arg) 457: 458: arg = if t 459: SQL::QualifiedIdentifier.new(t, c) 460: else 461: SQL::Identifier.new(c) 462: end 463: 464: if a 465: arg = SQL::AliasedExpression.new(arg, a) 466: end 467: 468: arg 469: when SQL::Expression, LiteralString, SQL::Blob 470: arg 471: when Hash 472: SQL::BooleanExpression.from_value_pairs(arg, :AND) 473: when Array 474: if condition_specifier?(arg) 475: SQL::BooleanExpression.from_value_pairs(arg, :AND) 476: else 477: SQL::Wrapper.new(arg) 478: end 479: when Numeric 480: SQL::NumericExpression.new(:NOOP, arg) 481: when String 482: SQL::StringExpression.new(:NOOP, arg) 483: when TrueClass, FalseClass 484: SQL::BooleanExpression.new(:NOOP, arg) 485: when Proc 486: expr(virtual_row(&arg)) 487: else 488: SQL::Wrapper.new(arg) 489: end 490: 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 496 496: def extract(datetime_part, exp) 497: SQL::NumericExpression.new(:extract, datetime_part, exp) 498: 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 505 505: def function(name, *args) 506: SQL::Function.new(name, *args) 507: end
Return a Postgres::HStore proxy for the given hash.
# File lib/sequel/extensions/pg_hstore.rb, line 314 314: def hstore(v) 315: case v 316: when Postgres::HStore 317: v 318: when Hash 319: Postgres::HStore.new(v) 320: else 321: # May not be defined unless the pg_hstore_ops extension is used 322: hstore_op(v) 323: end 324: end
Return the object wrapped in an Postgres::HStoreOp.
# File lib/sequel/extensions/pg_hstore_ops.rb, line 308 308: def hstore_op(v) 309: case v 310: when Postgres::HStoreOp 311: v 312: else 313: Postgres::HStoreOp.new(v) 314: end 315: end
Return the argument wrapped as an SQL::Identifier.
Sequel.identifier(:a__b) # "a__b"
# File lib/sequel/sql.rb, line 512 512: def identifier(name) 513: SQL::Identifier.new(name) 514: 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 549 549: def ilike(*args) 550: SQL::StringExpression.like(*(args << {:case_insensitive=>true})) 551: 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 525 525: def join(args, joiner=nil) 526: raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array) 527: if joiner 528: args = args.zip([joiner]*args.length).flatten 529: args.pop 530: end 531: 532: return SQL::StringExpression.new(:NOOP, '') if args.empty? 533: 534: args = args.map do |a| 535: case a 536: when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass 537: a 538: else 539: a.to_s 540: end 541: end 542: SQL::StringExpression.new('||''||', *args) 543: 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 557 557: def like(*args) 558: SQL::StringExpression.like(*args) 559: 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 574 574: def lit(s, *args) # core_sql ignore 575: if args.empty? 576: if s.is_a?(LiteralString) 577: s 578: else 579: LiteralString.new(s) 580: end 581: else 582: SQL::PlaceholderLiteralString.new(s, args) 583: end 584: 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 592 592: def negate(arg) 593: if condition_specifier?(arg) 594: SQL::BooleanExpression.from_value_pairs(arg, :AND, true) 595: else 596: raise Error, 'must pass a conditions specifier to Sequel.negate' 597: end 598: 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 606 606: def or(arg) 607: if condition_specifier?(arg) 608: SQL::BooleanExpression.from_value_pairs(arg, :OR, false) 609: else 610: raise Error, 'must pass a conditions specifier to Sequel.or' 611: end 612: end
Return a Postgres::PGArray proxy for the given array and database array type.
# File lib/sequel/extensions/pg_array.rb, line 574 574: def pg_array(v, array_type=nil) 575: case v 576: when Postgres::PGArray 577: if array_type.nil? || v.array_type == array_type 578: v 579: else 580: Postgres::PGArray.new(v.to_a, array_type) 581: end 582: when Array 583: Postgres::PGArray.new(v, array_type) 584: else 585: # May not be defined unless the pg_array_ops extension is used 586: pg_array_op(v) 587: end 588: end
Return the object wrapped in an Postgres::ArrayOp.
# File lib/sequel/extensions/pg_array_ops.rb, line 275 275: def pg_array_op(v) 276: case v 277: when Postgres::ArrayOp 278: v 279: else 280: Postgres::ArrayOp.new(v) 281: end 282: end
Wrap the array or hash in a Postgres::JSONArray or Postgres::JSONHash.
# File lib/sequel/extensions/pg_json.rb, line 195 195: def pg_json(v) 196: case v 197: when Postgres::JSONArray, Postgres::JSONHash 198: v 199: when Array 200: Postgres::JSONArray.new(v) 201: when Hash 202: Postgres::JSONHash.new(v) 203: else 204: Sequel.pg_json_op(v) 205: end 206: end
Return the object wrapped in an Postgres::JSONOp.
# File lib/sequel/extensions/pg_json_ops.rb, line 227 227: def pg_json_op(v) 228: case v 229: when Postgres::JSONOp 230: v 231: else 232: Postgres::JSONOp.new(v) 233: end 234: end
Convert the object to a Postgres::PGRange.
# File lib/sequel/extensions/pg_range.rb, line 501 501: def pg_range(v, db_type=nil) 502: case v 503: when Postgres::PGRange 504: if db_type.nil? || v.db_type == db_type 505: v 506: else 507: Postgres::PGRange.new(v.begin, v.end, :exclude_begin=>v.exclude_begin?, :exclude_end=>v.exclude_end?, :db_type=>db_type) 508: end 509: when Range 510: Postgres::PGRange.from_range(v, db_type) 511: else 512: # May not be defined unless the pg_range_ops extension is used 513: pg_range_op(v) 514: end 515: end
Return the expression wrapped in the Postgres::RangeOp.
# File lib/sequel/extensions/pg_range_ops.rb, line 128 128: def pg_range_op(v) 129: case v 130: when Postgres::RangeOp 131: v 132: else 133: Postgres::RangeOp.new(v) 134: end 135: end
Wraps the expr array in an anonymous Postgres::PGRow::ArrayRow instance.
# File lib/sequel/extensions/pg_row.rb, line 578 578: def pg_row(expr) 579: case expr 580: when Array 581: Postgres::PGRow::ArrayRow.new(expr) 582: else 583: # Will only work if pg_row_ops extension is loaded 584: pg_row_op(expr) 585: end 586: end
Return a PGRowOp wrapping the given expression.
# File lib/sequel/extensions/pg_row_ops.rb, line 165 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, line 619 619: def qualify(qualifier, identifier) 620: SQL::QualifiedIdentifier.new(qualifier, identifier) 621: 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, line 631 631: def subscript(exp, *subs) 632: SQL::Subscript.new(exp, subs.flatten) 633: 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, line 640 640: def trim(arg) 641: SQL::EmulatedFunction.new(:trim, arg) 642: 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 653 653: def value_list(arg) 654: raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array) 655: SQL::ValueList.new(arg) 656: end