module Sequel::EmulateOffsetWithRowNumber

Public Instance Methods

empty?() click to toggle source

If the offset must be emulated with ROW_NUMBER, don’t remove any ordering, because it can cause invalid queries to be issued if an offset is required when ordering.

Calls superclass method
   # File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb
 8 def empty?
 9   return super unless emulate_offset_with_row_number?
10   select(Dataset::EMPTY_SELECT).limit(1).single_value!.nil?
11 end
select_sql() click to toggle source

Emulate OFFSET support with the ROW_NUMBER window function

The implementation is ugly, cloning the current dataset and modifying the clone to add a ROW_NUMBER window function (and some other things), then using the modified clone in a subselect which is selected from.

If offset is used, an order must be provided, because the use of ROW_NUMBER requires an order.

Calls superclass method
   # File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb
21 def select_sql
22   return super unless emulate_offset_with_row_number?
23 
24   offset = @opts[:offset]
25   order = @opts[:order]
26   if require_offset_order?
27     order ||= default_offset_order
28     if order.nil? || order.empty?
29       raise(Error, "#{db.database_type} requires an order be provided if using an offset")
30     end
31   end
32 
33   columns = clone(:append_sql=>String.new, :placeholder_literal_null=>true).columns
34   dsa1 = dataset_alias(1)
35   rn = row_number_column
36   sql = @opts[:append_sql] || String.new
37   subselect_sql_append(sql, unlimited.
38     unordered.
39     select_append(Sequel.function(:ROW_NUMBER).over(:order=>order).as(rn)).
40     from_self(:alias=>dsa1).
41     select(*columns).
42     limit(@opts[:limit]).
43     where(SQL::Identifier.new(rn) > offset).
44     order(rn))
45   sql
46 end
supports_offsets_in_correlated_subqueries?() click to toggle source

This does not support offsets in correlated subqueries, as it requires a query to get the columns that will be invalid if a correlated subquery is used.

   # File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb
50 def supports_offsets_in_correlated_subqueries?
51   false
52 end

Private Instance Methods

allow_preparing_prepared_statements?() click to toggle source

Allow preparing prepared statements, since determining the prepared sql to use for a prepared statement requires calling prepare on that statement.

   # File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb
58 def allow_preparing_prepared_statements?
59   true
60 end
default_offset_order() click to toggle source

The default order to use for datasets with offsets, if no order is defined. By default, orders by all of the columns in the dataset.

   # File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb
64 def default_offset_order
65   if (cols = opts[:select])
66     cols.each do |c|
67       case c
68       when Symbol
69         return [split_alias(c).first]
70       when SQL::Identifier, SQL::QualifiedIdentifier
71         return [c]
72       when SQL::AliasedExpression
73         case c.expression
74         when Symbol, SQL::Identifier, SQL::QualifiedIdentifier
75           return [c.expression]
76         end
77       end
78     end
79   end
80   clone(:append_sql=>String.new).columns
81 end
emulate_offset_with_row_number?() click to toggle source

Whether to use ROW_NUMBER to emulate offsets

   # File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb
89 def emulate_offset_with_row_number?
90   @opts[:offset] && !@opts[:sql]
91 end
require_offset_order?() click to toggle source

Whether an order is required when using offset emulation via ROW_NUMBER, true by default.

   # File lib/sequel/adapters/utils/emulate_offset_with_row_number.rb
84 def require_offset_order?
85   true
86 end