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 Symbol 394: t, c, a = Sequel.split_symbol(arg) 395: 396: arg = if t 397: SQL::QualifiedIdentifier.new(t, c) 398: else 399: SQL::Identifier.new(c) 400: end 401: 402: if a 403: arg = SQL::AliasedExpression.new(arg, a) 404: end 405: 406: arg 407: when SQL::Expression, LiteralString, SQL::Blob 408: arg 409: when Hash 410: SQL::BooleanExpression.from_value_pairs(arg, :AND) 411: when Array 412: if condition_specifier?(arg) 413: SQL::BooleanExpression.from_value_pairs(arg, :AND) 414: else 415: SQL::Wrapper.new(arg) 416: end 417: when Numeric 418: SQL::NumericExpression.new(:NOOP, arg) 419: when String 420: SQL::StringExpression.new(:NOOP, arg) 421: when TrueClass, FalseClass 422: SQL::BooleanExpression.new(:NOOP, arg) 423: when Proc 424: expr(virtual_row(&arg)) 425: else 426: SQL::Wrapper.new(arg) 427: end 428: 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 434 434: def extract(datetime_part, exp) 435: SQL::NumericExpression.new(:extract, datetime_part, exp) 436: 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 443 443: def function(name, *args) 444: SQL::Function.new(name, *args) 445: end
Return a Postgres::HStore proxy for the given hash.
# File lib/sequel/extensions/pg_hstore.rb, line 285 285: def hstore(v) 286: case v 287: when Postgres::HStore 288: v 289: when Hash 290: Postgres::HStore.new(v) 291: else 292: # May not be defined unless the pg_hstore_ops extension is used 293: hstore_op(v) 294: end 295: end
Return the object wrapped in an Postgres::HStoreOp.
# File lib/sequel/extensions/pg_hstore_ops.rb, line 270 270: def hstore_op(v) 271: case v 272: when Postgres::HStoreOp 273: v 274: else 275: Postgres::HStoreOp.new(v) 276: end 277: end
Return the argument wrapped as an SQL::Identifier.
Sequel.identifier(:a__b) # "a__b"
# File lib/sequel/sql.rb, line 450 450: def identifier(name) 451: SQL::Identifier.new(name) 452: 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 487 487: def ilike(*args) 488: SQL::StringExpression.like(*(args << {:case_insensitive=>true})) 489: 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 463 463: def join(args, joiner=nil) 464: raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array) 465: if joiner 466: args = args.zip([joiner]*args.length).flatten 467: args.pop 468: end 469: 470: return SQL::StringExpression.new(:NOOP, '') if args.empty? 471: 472: args = args.map do |a| 473: case a 474: when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass 475: a 476: else 477: a.to_s 478: end 479: end 480: SQL::StringExpression.new('||''||', *args) 481: 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 495 495: def like(*args) 496: SQL::StringExpression.like(*args) 497: 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 512 512: def lit(s, *args) # core_sql ignore 513: if args.empty? 514: if s.is_a?(LiteralString) 515: s 516: else 517: LiteralString.new(s) 518: end 519: else 520: SQL::PlaceholderLiteralString.new(s, args) 521: end 522: 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 530 530: def negate(arg) 531: if condition_specifier?(arg) 532: SQL::BooleanExpression.from_value_pairs(arg, :AND, true) 533: else 534: raise Error, 'must pass a conditions specifier to Sequel.negate' 535: end 536: 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 544 544: def or(arg) 545: if condition_specifier?(arg) 546: SQL::BooleanExpression.from_value_pairs(arg, :OR, false) 547: else 548: raise Error, 'must pass a conditions specifier to Sequel.or' 549: end 550: end
Return a Postgres::PGArray proxy for the given array and database array type.
# File lib/sequel/extensions/pg_array.rb, line 507 507: def pg_array(v, array_type=nil) 508: case v 509: when Postgres::PGArray 510: if array_type.nil? || v.array_type == array_type 511: v 512: else 513: Postgres::PGArray.new(v.to_a, array_type) 514: end 515: when Array 516: Postgres::PGArray.new(v, array_type) 517: else 518: # May not be defined unless the pg_array_ops extension is used 519: pg_array_op(v) 520: end 521: end
Return the object wrapped in an Postgres::ArrayOp.
# File lib/sequel/extensions/pg_array_ops.rb, line 230 230: def pg_array_op(v) 231: case v 232: when Postgres::ArrayOp 233: v 234: else 235: Postgres::ArrayOp.new(v) 236: end 237: end
Wrap the array or hash in a Postgres::JSONArray or Postgres::JSONHash.
# File lib/sequel/extensions/pg_json.rb, line 173 173: def pg_json(v) 174: case v 175: when Postgres::JSONArray, Postgres::JSONHash 176: v 177: when Array 178: Postgres::JSONArray.new(v) 179: when Hash 180: Postgres::JSONHash.new(v) 181: else 182: raise Error, "Sequel.pg_json requires a hash or array argument" 183: end 184: end
Convert the object to a Postgres::PGRange.
# File lib/sequel/extensions/pg_range.rb, line 484 484: def pg_range(v, db_type=nil) 485: case v 486: when Postgres::PGRange 487: if db_type.nil? || v.db_type == db_type 488: v 489: else 490: Postgres::PGRange.new(v.begin, v.end, :exclude_begin=>v.exclude_begin?, :exclude_end=>v.exclude_end?, :db_type=>db_type) 491: end 492: when Range 493: Postgres::PGRange.from_range(v, db_type) 494: else 495: # May not be defined unless the pg_range_ops extension is used 496: pg_range_op(v) 497: end 498: end
Return the expression wrapped in the Postgres::RangeOp.
# File lib/sequel/extensions/pg_range_ops.rb, line 127 127: def pg_range_op(v) 128: case v 129: when Postgres::RangeOp 130: v 131: else 132: Postgres::RangeOp.new(v) 133: end 134: end
Wraps the expr array in an anonymous Postgres::PGRow::ArrayRow instance.
# File lib/sequel/extensions/pg_row.rb, line 551 551: def pg_row(expr) 552: case expr 553: when Array 554: Postgres::PGRow::ArrayRow.new(expr) 555: else 556: # Will only work if pg_row_ops extension is loaded 557: pg_row_op(expr) 558: end 559: end
Return a PGRowOp wrapping the given expression.
# File lib/sequel/extensions/pg_row_ops.rb, line 146 146: def pg_row_op(expr) 147: Postgres::PGRowOp.wrap(expr) 148: 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 557 557: def qualify(qualifier, identifier) 558: SQL::QualifiedIdentifier.new(qualifier, identifier) 559: 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 567 567: def subscript(exp, *subs) 568: SQL::Subscript.new(exp, subs.flatten) 569: 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 580 580: def value_list(arg) 581: raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array) 582: SQL::ValueList.new(arg) 583: end