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

These methods are designed as replacements for the core extensions, so that Sequel is still easy to use if the core extensions are not enabled.

Methods

as   asc   blob   case   cast   cast_numeric   cast_string   desc   expr   extract   function   hstore   hstore_op   identifier   ilike   join   like   lit   negate   or   pg_array   pg_array_op   pg_json   pg_range   pg_range_op   pg_row   pg_row_op   qualify   subscript   value_list  

Public Instance methods

Create an SQL::AliasedExpression for the given expression and alias.

  Sequel.as(:column, :alias) # "column" AS "alias"

[Source]

     # 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

[Source]

     # File lib/sequel/sql.rb, line 304
304:       def asc(arg, opts={})
305:         SQL::OrderedExpression.new(arg, false, opts)
306:       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 311
311:       def blob(s)
312:         if s.is_a?(SQL::Blob)
313:           s
314:         else
315:           SQL::Blob.new(s)
316:         end
317:       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 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))

[Source]

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

[Source]

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

[Source]

     # 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

[Source]

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

[Source]

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

[Source]

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

[Source]

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

[Source]

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

[Source]

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

[Source]

     # 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%'

[Source]

     # 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

[Source]

     # 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%'

[Source]

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

[Source]

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

[Source]

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

[Source]

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

[Source]

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

[Source]

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

[Source]

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

[Source]

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

[Source]

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

[Source]

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

[Source]

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

[Source]

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

[Source]

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

[Source]

     # 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

[Validate]