1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
|
# frozen-string-literal: true
module Sequel
module EmulateOffsetWithRowNumber
# 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.
def empty?
return super unless emulate_offset_with_row_number?
select(Dataset::EMPTY_SELECT).limit(1).single_value!.nil?
end
# 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.
def select_sql
return super unless emulate_offset_with_row_number?
offset = @opts[:offset]
order = @opts[:order]
if require_offset_order?
order ||= default_offset_order
if order.nil? || order.empty?
raise(Error, "#{db.database_type} requires an order be provided if using an offset")
end
end
columns = clone(:append_sql=>String.new, :placeholder_literal_null=>true).columns
dsa1 = dataset_alias(1)
rn = row_number_column
sql = @opts[:append_sql] || String.new
subselect_sql_append(sql, unlimited.
unordered.
select_append(Sequel.function(:ROW_NUMBER).over(:order=>order).as(rn)).
from_self(:alias=>dsa1).
select(*columns).
limit(@opts[:limit]).
where(SQL::Identifier.new(rn) > offset).
order(rn))
sql
end
# 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.
def supports_offsets_in_correlated_subqueries?
false
end
private
# Allow preparing prepared statements, since determining the prepared sql to use for
# a prepared statement requires calling prepare on that statement.
def allow_preparing_prepared_statements?
true
end
# 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.
def default_offset_order
if (cols = opts[:select])
cols.each do |c|
case c
when Symbol
return [split_alias(c).first]
when SQL::Identifier, SQL::QualifiedIdentifier
return [c]
when SQL::AliasedExpression
case c.expression
when Symbol, SQL::Identifier, SQL::QualifiedIdentifier
return [c.expression]
end
end
end
end
clone(:append_sql=>String.new).columns
end
# Whether an order is required when using offset emulation via ROW_NUMBER, true by default.
def require_offset_order?
true
end
# Whether to use ROW_NUMBER to emulate offsets
def emulate_offset_with_row_number?
@opts[:offset] && !@opts[:sql]
end
end
end
|
