Module Sequel::SQL::Builders
In: lib/sequel/extensions/pg_array.rb
lib/sequel/extensions/pg_row.rb
lib/sequel/extensions/pg_array_ops.rb
lib/sequel/extensions/pg_json_ops.rb
lib/sequel/extensions/pg_inet_ops.rb
lib/sequel/extensions/pg_range_ops.rb
lib/sequel/extensions/pg_json.rb
lib/sequel/extensions/string_agg.rb
lib/sequel/extensions/date_arithmetic.rb
lib/sequel/extensions/pg_hstore_ops.rb
lib/sequel/extensions/pg_range.rb
lib/sequel/extensions/pg_row_ops.rb
lib/sequel/extensions/pg_hstore.rb
lib/sequel/sql.rb

These methods make it easier to create Sequel expressions without using the core extensions.

Methods

as   asc   blob   case   cast   cast_numeric   cast_string   char_length   date_add   date_sub   deep_qualify   delay   desc   expr   extract   function   hstore   hstore_op   identifier   ilike   join   like   lit   negate   or   pg_array   pg_array_op   pg_inet_op   pg_json   pg_json_op   pg_jsonb   pg_jsonb_op   pg_range   pg_range_op   pg_row   pg_row_op   qualify   string_agg   subscript   trim   value_list  

Public Instance methods

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")

[Source]

     # File lib/sequel/sql.rb, line 314
314:       def as(exp, aliaz, columns=nil)
315:         SQL::AliasedExpression.new(exp, aliaz, columns)
316:       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

[Source]

     # File lib/sequel/sql.rb, line 327
327:       def asc(arg, opts=OPTS)
328:         SQL::OrderedExpression.new(arg, false, opts)
329:       end

Return an SQL::Blob that holds the same data as this string. Blobs provide proper escaping of binary data. If given a blob, returns it directly.

[Source]

     # File lib/sequel/sql.rb, line 334
334:       def blob(s)
335:         if s.is_a?(SQL::Blob)
336:           s
337:         else
338:           SQL::Blob.new(s)
339:         end
340:       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

[Source]

     # File lib/sequel/sql.rb, line 346
346:       def case(*args)
347:         SQL::CaseExpression.new(*args)
348:       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))

[Source]

     # File lib/sequel/sql.rb, line 355
355:       def cast(arg, sql_type)
356:         SQL::Cast.new(arg, sql_type)
357:       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)

[Source]

     # File lib/sequel/sql.rb, line 365
365:       def cast_numeric(arg, sql_type = nil)
366:         cast(arg, sql_type || Integer).sql_number
367:       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)

[Source]

     # File lib/sequel/sql.rb, line 375
375:       def cast_string(arg, sql_type = nil)
376:         cast(arg, sql_type || String).sql_string
377:       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

[Source]

     # File lib/sequel/sql.rb, line 384
384:       def char_length(arg)
385:         SQL::Function.new!(:char_length, [arg], :emulate=>true)
386:       end

Return a DateAdd expression, adding an interval to the date/timestamp expr. Options:

:cast :Cast to the specified type instead of the default if casting

[Source]

    # File lib/sequel/extensions/date_arithmetic.rb, line 43
43:       def date_add(expr, interval, opts=OPTS)
44:         DateAdd.new(expr, interval, opts)
45:       end

Return a DateAdd expression, adding the negative of the interval to the date/timestamp expr. Options:

:cast :Cast to the specified type instead of the default if casting

[Source]

    # File lib/sequel/extensions/date_arithmetic.rb, line 51
51:       def date_sub(expr, interval, opts=OPTS)
52:         interval = if interval.is_a?(Hash)
53:           h = {}
54:           interval.each{|k,v| h[k] = -v unless v.nil?}
55:           h
56:         else
57:           -interval
58:         end
59:         DateAdd.new(expr, interval, opts)
60:       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[:a].like('b')) # "table"."a" LIKE 'b' ESCAPE '\'

[Source]

     # File lib/sequel/sql.rb, line 394
394:       def deep_qualify(qualifier, expr)
395:         Sequel::Qualifier.new(qualifier).transform(expr)
396:       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.

[Source]

     # File lib/sequel/sql.rb, line 414
414:       def delay(&block)
415:         raise(Error, "Sequel.delay requires a block") unless block
416:         SQL::DelayedEvaluation.new(block)
417:       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

[Source]

     # File lib/sequel/sql.rb, line 428
428:       def desc(arg, opts=OPTS)
429:         SQL::OrderedExpression.new(arg, true, opts)
430:       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)

[Source]

     # File lib/sequel/sql.rb, line 448
448:       def expr(arg=(no_arg=true), &block)
449:         if block_given?
450:           if no_arg
451:             return expr(block)
452:           else
453:             raise Error, 'cannot provide both an argument and a block to Sequel.expr'
454:           end
455:         elsif no_arg
456:           raise Error, 'must provide either an argument or a block to Sequel.expr'
457:         end
458: 
459:         case arg
460:         when Symbol
461:           t, c, a = Sequel.split_symbol(arg)
462: 
463:           arg = if t
464:             SQL::QualifiedIdentifier.new(t, c)
465:           else
466:             SQL::Identifier.new(c)
467:           end
468: 
469:           if a
470:             arg = SQL::AliasedExpression.new(arg, a)
471:           end
472: 
473:           arg
474:         when SQL::Expression, LiteralString, SQL::Blob
475:           arg
476:         when Hash
477:           SQL::BooleanExpression.from_value_pairs(arg, :AND)
478:         when Array
479:           if condition_specifier?(arg)
480:             SQL::BooleanExpression.from_value_pairs(arg, :AND)
481:           else
482:             SQL::Wrapper.new(arg)
483:           end
484:         when Numeric
485:           SQL::NumericExpression.new(:NOOP, arg)
486:         when String
487:           SQL::StringExpression.new(:NOOP, arg)
488:         when TrueClass, FalseClass
489:           SQL::BooleanExpression.new(:NOOP, arg)
490:         when Proc
491:           expr(virtual_row(&arg))
492:         else
493:           SQL::Wrapper.new(arg)
494:         end
495:       end

Extract a datetime_part (e.g. year, month) from the given expression:

  Sequel.extract(:year, :date) # extract(year FROM "date")

[Source]

     # File lib/sequel/sql.rb, line 501
501:       def extract(datetime_part, exp)
502:         SQL::NumericExpression.new(:extract, datetime_part, exp)
503:       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)

[Source]

     # File lib/sequel/sql.rb, line 510
510:       def function(name, *args)
511:         SQL::Function.new(name, *args)
512:       end

Return a Postgres::HStore proxy for the given hash.

[Source]

     # File lib/sequel/extensions/pg_hstore.rb, line 302
302:     def hstore(v)
303:       case v
304:       when Postgres::HStore
305:         v
306:       when Hash
307:         Postgres::HStore.new(v)
308:       else
309:         # May not be defined unless the pg_hstore_ops extension is used
310:         hstore_op(v)
311:       end
312:     end

Return the object wrapped in an Postgres::HStoreOp.

[Source]

     # 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) # "a"

[Source]

     # File lib/sequel/sql.rb, line 517
517:       def identifier(name)
518:         SQL::Identifier.new(name)
519:       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 '\'

[Source]

     # File lib/sequel/sql.rb, line 554
554:       def ilike(*args)
555:         SQL::StringExpression.like(*(args << {:case_insensitive=>true}))
556:       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

[Source]

     # File lib/sequel/sql.rb, line 530
530:       def join(args, joiner=nil)
531:         raise Error, 'argument to Sequel.join must be an array' unless args.is_a?(Array)
532:         if joiner
533:           args = args.zip([joiner]*args.length).flatten
534:           args.pop
535:         end
536: 
537:         return SQL::StringExpression.new(:NOOP, '') if args.empty?
538: 
539:         args = args.map do |a|
540:           case a
541:           when Symbol, ::Sequel::SQL::Expression, ::Sequel::LiteralString, TrueClass, FalseClass, NilClass
542:             a
543:           else
544:             a.to_s
545:           end
546:         end
547:         SQL::StringExpression.new('||''||', *args)
548:       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 '\'

[Source]

     # File lib/sequel/sql.rb, line 562
562:       def like(*args)
563:         SQL::StringExpression.like(*args)
564:       end

Converts a string into a Sequel::LiteralString, in order to override string literalization, e.g.:

  DB[:items].where(abc: 'def').sql #=>
    "SELECT * FROM items WHERE (abc = 'def')"

  DB[:items].where(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"

[Source]

     # File lib/sequel/sql.rb, line 579
579:       def lit(s, *args)
580:         if args.empty?
581:           if s.is_a?(LiteralString)
582:             s
583:           else
584:             LiteralString.new(s)
585:           end
586:         else
587:           SQL::PlaceholderLiteralString.new(s, args) 
588:         end
589:       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))

[Source]

     # File lib/sequel/sql.rb, line 597
597:       def negate(arg)
598:         if condition_specifier?(arg)
599:           SQL::BooleanExpression.from_value_pairs(arg, :AND, true)
600:         else
601:           raise Error, 'must pass a conditions specifier to Sequel.negate'
602:         end
603:       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))

[Source]

     # File lib/sequel/sql.rb, line 611
611:       def or(arg)
612:         if condition_specifier?(arg)
613:           SQL::BooleanExpression.from_value_pairs(arg, :OR, false)
614:         else
615:           raise Error, 'must pass a conditions specifier to Sequel.or'
616:         end
617:       end

Return a Postgres::PGArray proxy for the given array and database array type.

[Source]

     # File lib/sequel/extensions/pg_array.rb, line 476
476:     def pg_array(v, array_type=nil)
477:       case v
478:       when Postgres::PGArray
479:         if array_type.nil? || v.array_type == array_type
480:           v
481:         else
482:           Postgres::PGArray.new(v.to_a, array_type)
483:         end
484:       when Array
485:         Postgres::PGArray.new(v, array_type)
486:       else
487:         # May not be defined unless the pg_array_ops extension is used
488:         pg_array_op(v)
489:       end
490:     end

Return the object wrapped in an Postgres::ArrayOp.

[Source]

     # 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.

[Source]

     # 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.

[Source]

     # File lib/sequel/extensions/pg_json.rb, line 285
285:     def pg_json(v)
286:       case v
287:       when Postgres::JSONArray, Postgres::JSONHash
288:         v
289:       when Array
290:         Postgres::JSONArray.new(v)
291:       when Hash
292:         Postgres::JSONHash.new(v)
293:       when Postgres::JSONBArray
294:         Postgres::JSONArray.new(v.to_a)
295:       when Postgres::JSONBHash
296:         Postgres::JSONHash.new(v.to_hash)
297:       else
298:         Sequel.pg_json_op(v)
299:       end
300:     end

Return the object wrapped in an Postgres::JSONOp.

[Source]

     # 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.

[Source]

     # File lib/sequel/extensions/pg_json.rb, line 303
303:     def pg_jsonb(v)
304:       case v
305:       when Postgres::JSONBArray, Postgres::JSONBHash
306:         v
307:       when Array
308:         Postgres::JSONBArray.new(v)
309:       when Hash
310:         Postgres::JSONBHash.new(v)
311:       when Postgres::JSONArray
312:         Postgres::JSONBArray.new(v.to_a)
313:       when Postgres::JSONHash
314:         Postgres::JSONBHash.new(v.to_hash)
315:       else
316:         Sequel.pg_jsonb_op(v)
317:       end
318:     end

Return the object wrapped in an Postgres::JSONBOp.

[Source]

     # 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.

[Source]

     # File lib/sequel/extensions/pg_range.rb, line 523
523:     def pg_range(v, db_type=nil)
524:       case v
525:       when Postgres::PGRange
526:         if db_type.nil? || v.db_type == db_type
527:           v
528:         else
529:           Postgres::PGRange.new(v.begin, v.end, :exclude_begin=>v.exclude_begin?, :exclude_end=>v.exclude_end?, :db_type=>db_type)
530:         end
531:       when Range
532:         Postgres::PGRange.from_range(v, db_type)
533:       else
534:         # May not be defined unless the pg_range_ops extension is used
535:         pg_range_op(v)
536:       end
537:     end

Return the expression wrapped in the Postgres::RangeOp.

[Source]

     # File lib/sequel/extensions/pg_range_ops.rb, line 132
132:     def pg_range_op(v)
133:       case v
134:       when Postgres::RangeOp
135:         v
136:       else
137:         Postgres::RangeOp.new(v)
138:       end
139:     end

Wraps the expr array in an anonymous Postgres::PGRow::ArrayRow instance.

[Source]

     # File lib/sequel/extensions/pg_row.rb, line 547
547:     def pg_row(expr)
548:       case expr
549:       when Array
550:         Postgres::PGRow::ArrayRow.new(expr)
551:       else
552:         # Will only work if pg_row_ops extension is loaded
553:         pg_row_op(expr)
554:       end
555:     end

Return a PGRowOp wrapping the given expression.

[Source]

     # 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"

[Source]

     # File lib/sequel/sql.rb, line 624
624:       def qualify(qualifier, identifier)
625:         SQL::QualifiedIdentifier.new(qualifier, identifier)
626:       end

Return a StringAgg expression for an aggregate string concatentation.

[Source]

    # File lib/sequel/extensions/string_agg.rb, line 65
65:       def string_agg(*a)
66:         StringAgg.new(*a)
67:       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]

[Source]

     # File lib/sequel/sql.rb, line 636
636:       def subscript(exp, *subs)
637:         SQL::Subscript.new(exp, subs.flatten)
638:       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

[Source]

     # File lib/sequel/sql.rb, line 645
645:       def trim(arg)
646:         SQL::Function.new!(:trim, [arg], :emulate=>true)
647:       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].where([:a, :b]=>[[1, 2], [3, 4]]) # SQL: (a, b) IN ((1, 2), (3, 4))
  DB[:a].where('(a, b) IN ?', [[1, 2], [3, 4]]) # SQL: (a, b) IN ((1 = 2) AND (3 = 4))
  DB[:a].where('(a, b) IN ?', Sequel.value_list([[1, 2], [3, 4]])) # SQL: (a, b) IN ((1, 2), (3, 4))

[Source]

     # File lib/sequel/sql.rb, line 658
658:       def value_list(arg)
659:         raise Error, 'argument to Sequel.value_list must be an array' unless arg.is_a?(Array)
660:         SQL::ValueList.new(arg)
661:       end

[Validate]