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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
#!/usr/bin/perl
use strict;
use warnings;
use Test::More tests => 17;
use Test::Exception;
use lib qw(t/lib);
# dynamically load SQL::Abstract::Test;
eval "use SQL::Abstract::Limit::Test; 1" or die $@;
=for notes
use SQL::Abstract::Limit;
my $syntax = 'LimitOffset';
# others include: Top RowNum LimitXY Fetch RowsTo
my $sql = SQL::Abstract::Limit->new( limit => $syntax );
my($stmt, @bind) = $sql->select($table, \@fields, \%where, \@order, $limit, $offset);
=cut
use SQL::Abstract::Limit;
my @syntaxes = qw( LimitOffset LimitXY RowsTo Top RowNum GenericSubQ FetchFirst shgfh );
my @not_syntaxes = qw( Rank );
lives_ok { SQL::Abstract::Limit->new( limit => $_ ) for @syntaxes } 'survives constructor';
# query
my $table = 'TheTable';
my $fields = [ qw( requestor worker colC colH ) ];
my $where = { requestor => 'inna',
worker => ['nwiger', 'rcwe', 'sfz'],
status => { '!=', 'completed' },
};
my $order = [ qw( pay age ) ];
my $limit = 10; # 10 per page
my $offset = 70; # page 7
my $last = $offset + $limit;
my $base_sql = 'requestor, worker, colC, colH FROM TheTable WHERE ( requestor = ? AND status != ? AND ( ( worker = ? ) OR ( worker = ? ) OR ( worker = ? ) ) )';
my @expected_bind = qw/inna completed nwiger rcwe sfz/;
my $sql_ab = SQL::Abstract::Limit->new( limit_dialect => 'LimitOffset' );
my ( $stmt, @bind );
# LimitOffset
lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset) } 'select LimitOffset';
is_same_sql_bind(
$stmt, \@bind,
"SELECT $base_sql ORDER BY pay, age LIMIT $limit OFFSET $offset", \@expected_bind,
'LimitOffset SQL',
);
# LimitXY
lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'LimitXY' ) } 'select LimitXY';
is_same_sql_bind(
$stmt, \@bind,
"SELECT $base_sql ORDER BY pay, age LIMIT $offset, $limit", \@expected_bind,
'LimitXY SQL',
);
# RowsTo
lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'RowsTo' ) } 'select RowsTo';
is_same_sql_bind(
$stmt, \@bind,
"SELECT $base_sql ORDER BY pay, age ROWS $offset TO $last", \@expected_bind,
'RowsTo SQL',
);
# Top
lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'Top' ) } 'select Top';
is_same_sql_bind(
$stmt, \@bind,
"SELECT * FROM ("
. "SELECT TOP $limit * FROM ("
. "SELECT TOP $last $base_sql ORDER BY pay ASC, age ASC"
. ") AS foo ORDER BY pay DESC, age DESC"
.") AS bar ORDER BY pay ASC, age ASC", \@expected_bind,
'Top SQL',
);
# RowNum
lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'RowNum' ) } 'select RowNum';
is_same_sql_bind(
$stmt, \@bind,
"SELECT * FROM ("
. "SELECT A.*, ROWNUM r FROM ("
. "SELECT $base_sql ORDER BY pay, age"
. ") A WHERE ROWNUM < @{[$last + 1]}"
.") B WHERE r >= @{[$offset + 1]}", \@expected_bind,
'RowNum SQL',
);
# GenericSubQ
lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'GenericSubQ' ) } 'select GenericSubQ';
(my $gen_q_base_sql = $base_sql) =~ s/TheTable/TheTable X/;
is_same_sql_bind(
$stmt, \@bind,
"SELECT $gen_q_base_sql AND"
. "(SELECT COUNT(*) FROM TheTable WHERE requestor > X.requestor)"
. " BETWEEN $offset AND $last ORDER BY requestor DESC", \@expected_bind,
'GenericSubQ SQL',
);
# FetchFirst
lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'FetchFirst' ) } 'select FetchFirst';
is_same_sql_bind(
$stmt, \@bind,
"SELECT * FROM ("
. "SELECT * FROM ("
. "SELECT $base_sql ORDER BY pay ASC, age ASC FETCH FIRST $last ROWS ONLY"
. ") foo ORDER BY pay DESC, age DESC FETCH FIRST $limit ROWS ONLY"
. ") bar ORDER BY pay ASC, age ASC", \@expected_bind,
'FetchFirst SQL',
);
# Skip
lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'Skip' ) } 'select Skip';
is_same_sql_bind(
$stmt, \@bind,
"select skip $offset limit $limit $base_sql ORDER BY pay, age", \@expected_bind,
'Skip SQL',
);
|
