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, line 326 326: def as(exp, aliaz, columns=nil) 327: SQL::AliasedExpression.new(exp, aliaz, columns) 328: 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 339 339: def asc(arg, opts=OPTS) 340: SQL::OrderedExpression.new(arg, false, opts) 341: 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 358 358: def case(*args) # core_sql ignore 359: SQL::CaseExpression.new(*args) 360: 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 367 367: def cast(arg, sql_type) 368: SQL::Cast.new(arg, sql_type) 369: 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 377 377: def cast_numeric(arg, sql_type = nil) 378: cast(arg, sql_type || Integer).sql_number 379: 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 387 387: def cast_string(arg, sql_type = nil) 388: cast(arg, sql_type || String).sql_string 389: 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 396 396: def char_length(arg) 397: SQL::Function.new!(:char_length, [arg], :emulate=>true) 398: end
Return a DateAdd expression, adding the negative of the interval to the date/timestamp expr.
# File lib/sequel/extensions/date_arithmetic.rb, line 38 38: def date_sub(expr, interval) 39: interval = if interval.is_a?(Hash) 40: h = {} 41: interval.each{|k,v| h[k] = -v unless v.nil?} 42: h 43: else 44: -interval 45: end 46: DateAdd.new(expr, interval) 47: 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 406 406: def deep_qualify(qualifier, expr) 407: Sequel::Qualifier.new(Sequel, qualifier).transform(expr) 408: 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 426 426: def delay(&block) 427: raise(Error, "Sequel.delay requires a block") unless block 428: SQL::DelayedEvaluation.new(block) 429: 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 440 440: def desc(arg, opts=OPTS) 441: SQL::OrderedExpression.new(arg, true, opts) 442: 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 456 456: def expr(arg=(no_arg=true), &block) 457: if block_given? 458: if no_arg 459: return expr(block) 460: else 461: raise Error, 'cannot provide both an argument and a block to Sequel.expr' 462: end 463: elsif no_arg 464: raise Error, 'must provide either an argument or a block to Sequel.expr' 465: end 466: 467: case arg 468: when Symbol 469: t, c, a = Sequel.split_symbol(arg) 470: 471: arg = if t 472: SQL::QualifiedIdentifier.new(t, c) 473: else 474: SQL::Identifier.new(c) 475: end 476: 477: if a 478: arg = SQL::AliasedExpression.new(arg, a) 479: end 480: 481: arg 482: when SQL::Expression, LiteralString, SQL::Blob 483: arg 484: when Hash 485: SQL::BooleanExpression.from_value_pairs(arg, :AND) 486: when Array 487: if condition_specifier?(arg) 488: SQL::BooleanExpression.from_value_pairs(arg, :AND) 489: else 490: SQL::Wrapper.new(arg) 491: end 492: when Numeric 493: SQL::NumericExpression.new(:NOOP, arg) 494: when String 495: SQL::StringExpression.new(:NOOP, arg) 496: when TrueClass, FalseClass 497: SQL::BooleanExpression.new(:NOOP, arg) 498: when Proc 499: expr(virtual_row(&arg)) 500: else 501: SQL::Wrapper.new(arg) 502: end 503: 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 509 509: def extract(datetime_part, exp) 510: SQL::NumericExpression.new(:extract, datetime_part, exp) 511: 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 518 518: def function(name, *args) 519: SQL::Function.new(name, *args) 520: end
Return a Postgres::HStore proxy for the given hash.
# File lib/sequel/extensions/pg_hstore.rb, line 317 317: def hstore(v) 318: case v 319: when Postgres::HStore 320: v 321: when Hash 322: Postgres::HStore.new(v) 323: else 324: # May not be defined unless the pg_hstore_ops extension is used 325: hstore_op(v) 326: end 327: end
Return the object wrapped in an Postgres::HStoreOp.
# File lib/sequel/extensions/pg_hstore_ops.rb, line 316 316: def hstore_op(v) 317: case v 318: when Postgres::HStoreOp 319: v 320: else 321: Postgres::HStoreOp.new(v) 322: end 323: end
Return the argument wrapped as an SQL::Identifier.
Sequel.identifier(:a__b) # "a__b"
# File lib/sequel/sql.rb, line 525 525: def identifier(name) 526: SQL::Identifier.new(name) 527: 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, line 562 562: def ilike(*args) 563: SQL::StringExpression.like(*(args << {:case_insensitive=>true})) 564: 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 538 538: def join(args, joiner=nil) 539: raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array) 540: if joiner 541: args = args.zip([joiner]*args.length).flatten 542: args.pop 543: end 544: 545: return SQL::StringExpression.new(:NOOP, '') if args.empty? 546: 547: args = args.map do |a| 548: case a 549: when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass 550: a 551: else 552: a.to_s 553: end 554: end 555: SQL::StringExpression.new('||''||', *args) 556: 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, line 570 570: def like(*args) 571: SQL::StringExpression.like(*args) 572: 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 587 587: def lit(s, *args) # core_sql ignore 588: if args.empty? 589: if s.is_a?(LiteralString) 590: s 591: else 592: LiteralString.new(s) 593: end 594: else 595: SQL::PlaceholderLiteralString.new(s, args) 596: end 597: 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 605 605: def negate(arg) 606: if condition_specifier?(arg) 607: SQL::BooleanExpression.from_value_pairs(arg, :AND, true) 608: else 609: raise Error, 'must pass a conditions specifier to Sequel.negate' 610: end 611: 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 619 619: def or(arg) 620: if condition_specifier?(arg) 621: SQL::BooleanExpression.from_value_pairs(arg, :OR, false) 622: else 623: raise Error, 'must pass a conditions specifier to Sequel.or' 624: end 625: end
Return a Postgres::PGArray proxy for the given array and database array type.
# File lib/sequel/extensions/pg_array.rb, line 564 564: def pg_array(v, array_type=nil) 565: case v 566: when Postgres::PGArray 567: if array_type.nil? || v.array_type == array_type 568: v 569: else 570: Postgres::PGArray.new(v.to_a, array_type) 571: end 572: when Array 573: Postgres::PGArray.new(v, array_type) 574: else 575: # May not be defined unless the pg_array_ops extension is used 576: pg_array_op(v) 577: end 578: end
Return the object wrapped in an Postgres::ArrayOp.
# File lib/sequel/extensions/pg_array_ops.rb, line 291 291: def pg_array_op(v) 292: case v 293: when Postgres::ArrayOp 294: v 295: else 296: Postgres::ArrayOp.new(v) 297: end 298: end
Wrap the array or hash in a Postgres::JSONArray or Postgres::JSONHash.
# File lib/sequel/extensions/pg_json.rb, line 268 268: def pg_json(v) 269: case v 270: when Postgres::JSONArray, Postgres::JSONHash 271: v 272: when Array 273: Postgres::JSONArray.new(v) 274: when Hash 275: Postgres::JSONHash.new(v) 276: when Postgres::JSONBArray 277: Postgres::JSONArray.new(v.to_a) 278: when Postgres::JSONBHash 279: Postgres::JSONHash.new(v.to_hash) 280: else 281: Sequel.pg_json_op(v) 282: end 283: end
Return the object wrapped in an Postgres::JSONOp.
# File lib/sequel/extensions/pg_json_ops.rb, line 391 391: def pg_json_op(v) 392: case v 393: when Postgres::JSONOp 394: v 395: else 396: Postgres::JSONOp.new(v) 397: end 398: end
Wrap the array or hash in a Postgres::JSONArray or Postgres::JSONHash.
# File lib/sequel/extensions/pg_json.rb, line 286 286: def pg_jsonb(v) 287: case v 288: when Postgres::JSONBArray, Postgres::JSONBHash 289: v 290: when Array 291: Postgres::JSONBArray.new(v) 292: when Hash 293: Postgres::JSONBHash.new(v) 294: when Postgres::JSONArray 295: Postgres::JSONBArray.new(v.to_a) 296: when Postgres::JSONHash 297: Postgres::JSONBHash.new(v.to_hash) 298: else 299: Sequel.pg_json_op(v) 300: end 301: end
Return the object wrapped in an Postgres::JSONBOp.
# File lib/sequel/extensions/pg_json_ops.rb, line 401 401: def pg_jsonb_op(v) 402: case v 403: when Postgres::JSONBOp 404: v 405: else 406: Postgres::JSONBOp.new(v) 407: end 408: end
Convert the object to a Postgres::PGRange.
# File lib/sequel/extensions/pg_range.rb, line 506 506: def pg_range(v, db_type=nil) 507: case v 508: when Postgres::PGRange 509: if db_type.nil? || v.db_type == db_type 510: v 511: else 512: Postgres::PGRange.new(v.begin, v.end, :exclude_begin=>v.exclude_begin?, :exclude_end=>v.exclude_end?, :db_type=>db_type) 513: end 514: when Range 515: Postgres::PGRange.from_range(v, db_type) 516: else 517: # May not be defined unless the pg_range_ops extension is used 518: pg_range_op(v) 519: end 520: end
Return the expression wrapped in the Postgres::RangeOp.
# File lib/sequel/extensions/pg_range_ops.rb, line 129 129: def pg_range_op(v) 130: case v 131: when Postgres::RangeOp 132: v 133: else 134: Postgres::RangeOp.new(v) 135: end 136: end
Wraps the expr array in an anonymous Postgres::PGRow::ArrayRow instance.
# File lib/sequel/extensions/pg_row.rb, line 582 582: def pg_row(expr) 583: case expr 584: when Array 585: Postgres::PGRow::ArrayRow.new(expr) 586: else 587: # Will only work if pg_row_ops extension is loaded 588: pg_row_op(expr) 589: end 590: end
Return a PGRowOp wrapping the given expression.
# File lib/sequel/extensions/pg_row_ops.rb, line 166 166: def pg_row_op(expr) 167: Postgres::PGRowOp.wrap(expr) 168: 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 632 632: def qualify(qualifier, identifier) 633: SQL::QualifiedIdentifier.new(qualifier, identifier) 634: 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 644 644: def subscript(exp, *subs) 645: SQL::Subscript.new(exp, subs.flatten) 646: 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 653 653: def trim(arg) 654: SQL::Function.new!(:trim, [arg], :emulate=>true) 655: 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 666 666: def value_list(arg) 667: raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array) 668: SQL::ValueList.new(arg) 669: end