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 339 339: def as(exp, aliaz, columns=nil) 340: SQL::AliasedExpression.new(exp, aliaz, columns) 341: 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 352 352: def asc(arg, opts=OPTS) 353: SQL::OrderedExpression.new(arg, false, opts) 354: 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 371 371: def case(*args) # core_sql ignore 372: SQL::CaseExpression.new(*args) 373: 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 380 380: def cast(arg, sql_type) 381: SQL::Cast.new(arg, sql_type) 382: 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 390 390: def cast_numeric(arg, sql_type = nil) 391: cast(arg, sql_type || Integer).sql_number 392: 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 400 400: def cast_string(arg, sql_type = nil) 401: cast(arg, sql_type || String).sql_string 402: 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 409 409: def char_length(arg) 410: SQL::Function.new!(:char_length, [arg], :emulate=>true) 411: end
Return a DateAdd expression, adding the negative of the interval to the date/timestamp expr.
# File lib/sequel/extensions/date_arithmetic.rb, line 42 42: def date_sub(expr, interval) 43: interval = if interval.is_a?(Hash) 44: h = {} 45: interval.each{|k,v| h[k] = -v unless v.nil?} 46: h 47: else 48: -interval 49: end 50: DateAdd.new(expr, interval) 51: 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 419 419: def deep_qualify(qualifier, expr) 420: Sequel::Qualifier.new(Sequel, qualifier).transform(expr) 421: 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 439 439: def delay(&block) 440: raise(Error, "Sequel.delay requires a block") unless block 441: SQL::DelayedEvaluation.new(block) 442: 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 453 453: def desc(arg, opts=OPTS) 454: SQL::OrderedExpression.new(arg, true, opts) 455: 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)
On the Sequel module, this is aliased as #[], for easier use:
Sequel[1] - :a # SQL: (1 - a)
# File lib/sequel/sql.rb, line 473 473: def expr(arg=(no_arg=true), &block) 474: if block_given? 475: if no_arg 476: return expr(block) 477: else 478: raise Error, 'cannot provide both an argument and a block to Sequel.expr' 479: end 480: elsif no_arg 481: raise Error, 'must provide either an argument or a block to Sequel.expr' 482: end 483: 484: case arg 485: when Symbol 486: t, c, a = Sequel.split_symbol(arg) 487: 488: arg = if t 489: SQL::QualifiedIdentifier.new(t, c) 490: else 491: SQL::Identifier.new(c) 492: end 493: 494: if a 495: arg = SQL::AliasedExpression.new(arg, a) 496: end 497: 498: arg 499: when SQL::Expression, LiteralString, SQL::Blob 500: arg 501: when Hash 502: SQL::BooleanExpression.from_value_pairs(arg, :AND) 503: when Array 504: if condition_specifier?(arg) 505: SQL::BooleanExpression.from_value_pairs(arg, :AND) 506: else 507: SQL::Wrapper.new(arg) 508: end 509: when Numeric 510: SQL::NumericExpression.new(:NOOP, arg) 511: when String 512: SQL::StringExpression.new(:NOOP, arg) 513: when TrueClass, FalseClass 514: SQL::BooleanExpression.new(:NOOP, arg) 515: when Proc 516: expr(virtual_row(&arg)) 517: else 518: SQL::Wrapper.new(arg) 519: end 520: 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 526 526: def extract(datetime_part, exp) 527: SQL::NumericExpression.new(:extract, datetime_part, exp) 528: 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 535 535: def function(name, *args) 536: SQL::Function.new(name, *args) 537: 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 328 328: def hstore_op(v) 329: case v 330: when Postgres::HStoreOp 331: v 332: else 333: Postgres::HStoreOp.new(v) 334: end 335: end
Return the argument wrapped as an SQL::Identifier.
Sequel.identifier(:a__b) # "a__b"
# File lib/sequel/sql.rb, line 542 542: def identifier(name) 543: SQL::Identifier.new(name) 544: 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 579 579: def ilike(*args) 580: SQL::StringExpression.like(*(args << {:case_insensitive=>true})) 581: 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 555 555: def join(args, joiner=nil) 556: raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array) 557: if joiner 558: args = args.zip([joiner]*args.length).flatten 559: args.pop 560: end 561: 562: return SQL::StringExpression.new(:NOOP, '') if args.empty? 563: 564: args = args.map do |a| 565: case a 566: when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass 567: a 568: else 569: a.to_s 570: end 571: end 572: SQL::StringExpression.new('||''||', *args) 573: 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 587 587: def like(*args) 588: SQL::StringExpression.like(*args) 589: 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 604 604: def lit(s, *args) # core_sql ignore 605: if args.empty? 606: if s.is_a?(LiteralString) 607: s 608: else 609: LiteralString.new(s) 610: end 611: else 612: SQL::PlaceholderLiteralString.new(s, args) 613: end 614: 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 622 622: def negate(arg) 623: if condition_specifier?(arg) 624: SQL::BooleanExpression.from_value_pairs(arg, :AND, true) 625: else 626: raise Error, 'must pass a conditions specifier to Sequel.negate' 627: end 628: 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 636 636: def or(arg) 637: if condition_specifier?(arg) 638: SQL::BooleanExpression.from_value_pairs(arg, :OR, false) 639: else 640: raise Error, 'must pass a conditions specifier to Sequel.or' 641: end 642: end
Return a Postgres::PGArray proxy for the given array and database array type.
# File lib/sequel/extensions/pg_array.rb, line 551 551: def pg_array(v, array_type=nil) 552: case v 553: when Postgres::PGArray 554: if array_type.nil? || v.array_type == array_type 555: v 556: else 557: Postgres::PGArray.new(v.to_a, array_type) 558: end 559: when Array 560: Postgres::PGArray.new(v, array_type) 561: else 562: # May not be defined unless the pg_array_ops extension is used 563: pg_array_op(v) 564: end 565: end
Return the object wrapped in an Postgres::ArrayOp.
# File lib/sequel/extensions/pg_array_ops.rb, line 295 295: def pg_array_op(v) 296: case v 297: when Postgres::ArrayOp 298: v 299: else 300: Postgres::ArrayOp.new(v) 301: end 302: end
Return the expression wrapped in the Postgres::InetOp.
# File lib/sequel/extensions/pg_inet_ops.rb, line 171 171: def pg_inet_op(v) 172: case v 173: when Postgres::InetOp 174: v 175: else 176: Postgres::InetOp.new(v) 177: end 178: end
Wrap the array or hash in a Postgres::JSONArray or Postgres::JSONHash.
# File lib/sequel/extensions/pg_json.rb, line 270 270: def pg_json(v) 271: case v 272: when Postgres::JSONArray, Postgres::JSONHash 273: v 274: when Array 275: Postgres::JSONArray.new(v) 276: when Hash 277: Postgres::JSONHash.new(v) 278: when Postgres::JSONBArray 279: Postgres::JSONArray.new(v.to_a) 280: when Postgres::JSONBHash 281: Postgres::JSONHash.new(v.to_hash) 282: else 283: Sequel.pg_json_op(v) 284: end 285: end
Return the object wrapped in an Postgres::JSONOp.
# File lib/sequel/extensions/pg_json_ops.rb, line 470 470: def pg_json_op(v) 471: case v 472: when Postgres::JSONOp 473: v 474: else 475: Postgres::JSONOp.new(v) 476: end 477: end
Wrap the array or hash in a Postgres::JSONBArray or Postgres::JSONBHash.
# File lib/sequel/extensions/pg_json.rb, line 288 288: def pg_jsonb(v) 289: case v 290: when Postgres::JSONBArray, Postgres::JSONBHash 291: v 292: when Array 293: Postgres::JSONBArray.new(v) 294: when Hash 295: Postgres::JSONBHash.new(v) 296: when Postgres::JSONArray 297: Postgres::JSONBArray.new(v.to_a) 298: when Postgres::JSONHash 299: Postgres::JSONBHash.new(v.to_hash) 300: else 301: Sequel.pg_jsonb_op(v) 302: end 303: end
Return the object wrapped in an Postgres::JSONBOp.
# File lib/sequel/extensions/pg_json_ops.rb, line 480 480: def pg_jsonb_op(v) 481: case v 482: when Postgres::JSONBOp 483: v 484: else 485: Postgres::JSONBOp.new(v) 486: end 487: end
Convert the object to a Postgres::PGRange.
# File lib/sequel/extensions/pg_range.rb, line 585 585: def pg_range(v, db_type=nil) 586: case v 587: when Postgres::PGRange 588: if db_type.nil? || v.db_type == db_type 589: v 590: else 591: Postgres::PGRange.new(v.begin, v.end, :exclude_begin=>v.exclude_begin?, :exclude_end=>v.exclude_end?, :db_type=>db_type) 592: end 593: when Range 594: Postgres::PGRange.from_range(v, db_type) 595: else 596: # May not be defined unless the pg_range_ops extension is used 597: pg_range_op(v) 598: end 599: end
Return the expression wrapped in the Postgres::RangeOp.
# File lib/sequel/extensions/pg_range_ops.rb, line 133 133: def pg_range_op(v) 134: case v 135: when Postgres::RangeOp 136: v 137: else 138: Postgres::RangeOp.new(v) 139: end 140: 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 170 170: def pg_row_op(expr) 171: Postgres::PGRowOp.wrap(expr) 172: 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 649 649: def qualify(qualifier, identifier) 650: SQL::QualifiedIdentifier.new(qualifier, identifier) 651: 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 661 661: def subscript(exp, *subs) 662: SQL::Subscript.new(exp, subs.flatten) 663: 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 670 670: def trim(arg) 671: SQL::Function.new!(:trim, [arg], :emulate=>true) 672: 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 683 683: def value_list(arg) 684: raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array) 685: SQL::ValueList.new(arg) 686: end