1#!/usr/bin/perl
2
3use strict;
4use warnings;
5
6use Test::More tests => 17;
7use Test::Exception;
8
9use lib qw(t/lib);
10
11# dynamically load SQL::Abstract::Test;
12eval "use SQL::Abstract::Limit::Test; 1" or die $@;
13
14=for notes
15
16    use SQL::Abstract::Limit;
17
18    my $syntax = 'LimitOffset';
19
20    # others include: Top RowNum LimitXY Fetch RowsTo
21
22    my $sql = SQL::Abstract::Limit->new( limit => $syntax );
23
24    my($stmt, @bind) = $sql->select($table, \@fields, \%where, \@order, $limit, $offset);
25
26=cut
27
28use SQL::Abstract::Limit;
29
30my @syntaxes = qw( LimitOffset LimitXY RowsTo Top RowNum GenericSubQ FetchFirst shgfh );
31
32my @not_syntaxes = qw( Rank );
33
34lives_ok { SQL::Abstract::Limit->new( limit => $_ ) for @syntaxes } 'survives constructor';
35
36# query
37
38my $table  = 'TheTable';
39my $fields = [ qw( requestor worker colC colH ) ];
40my $where  = { requestor => 'inna',
41               worker    => ['nwiger', 'rcwe', 'sfz'],
42               status    => { '!=', 'completed' },
43               };
44my $order = [ qw( pay age ) ];
45my $limit = 10;     # 10 per page
46my $offset = 70;    # page 7
47my $last = $offset + $limit;
48
49
50my $base_sql = 'requestor, worker, colC, colH FROM TheTable WHERE ( requestor = ? AND status != ? AND ( ( worker = ? ) OR ( worker = ? ) OR ( worker = ? ) ) )';
51
52my @expected_bind = qw/inna completed nwiger rcwe sfz/; 
53
54my $sql_ab = SQL::Abstract::Limit->new( limit_dialect => 'LimitOffset' );
55
56my ( $stmt, @bind );
57
58# LimitOffset
59lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset) } 'select LimitOffset';
60
61is_same_sql_bind(
62  $stmt, \@bind, 
63  "SELECT $base_sql ORDER BY pay, age LIMIT $limit OFFSET $offset", \@expected_bind,
64  'LimitOffset SQL',
65);
66
67# LimitXY
68lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'LimitXY' ) } 'select LimitXY';
69is_same_sql_bind(
70  $stmt, \@bind, 
71  "SELECT $base_sql ORDER BY pay, age LIMIT $offset, $limit", \@expected_bind,
72  'LimitXY SQL',
73);
74
75# RowsTo
76lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'RowsTo' ) } 'select RowsTo';
77is_same_sql_bind(
78  $stmt, \@bind, 
79  "SELECT $base_sql ORDER BY pay, age ROWS $offset TO $last", \@expected_bind,
80  'RowsTo SQL',
81);
82
83
84# Top
85lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'Top' ) } 'select Top';
86
87is_same_sql_bind(
88  $stmt, \@bind,
89  "SELECT * FROM ("
90 .  "SELECT TOP $limit * FROM ("
91 .     "SELECT TOP $last $base_sql ORDER BY pay ASC, age ASC"
92 .  ") AS foo ORDER BY pay DESC, age DESC"
93 .") AS bar ORDER BY pay ASC, age ASC", \@expected_bind,
94  'Top SQL',
95);
96
97
98
99# RowNum
100lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'RowNum' ) } 'select RowNum';
101
102is_same_sql_bind(
103  $stmt, \@bind,
104  "SELECT * FROM ("
105 .  "SELECT A.*, ROWNUM r FROM ("
106 .     "SELECT $base_sql ORDER BY pay, age"
107 .  ") A WHERE ROWNUM < @{[$last + 1]}"
108 .") B WHERE r >= @{[$offset + 1]}", \@expected_bind,
109  'RowNum SQL',
110);
111
112
113
114# GenericSubQ
115lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'GenericSubQ' ) } 'select GenericSubQ';
116(my $gen_q_base_sql = $base_sql) =~ s/TheTable/TheTable X/;
117
118is_same_sql_bind(
119  $stmt, \@bind,
120  "SELECT $gen_q_base_sql AND"
121 .  "(SELECT COUNT(*) FROM TheTable WHERE requestor > X.requestor)"
122 .  "  BETWEEN $offset AND $last ORDER BY requestor DESC", \@expected_bind,
123  'GenericSubQ SQL',
124);
125
126
127# FetchFirst
128lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'FetchFirst' ) } 'select FetchFirst';
129
130is_same_sql_bind(
131  $stmt, \@bind,
132  "SELECT * FROM ("
133 .  "SELECT * FROM ("
134 .    "SELECT $base_sql ORDER BY pay ASC, age ASC FETCH FIRST $last ROWS ONLY"
135 .    ") foo ORDER BY pay DESC, age DESC FETCH FIRST $limit ROWS ONLY"
136 .  ") bar ORDER BY pay ASC, age ASC", \@expected_bind,
137  'FetchFirst SQL',
138);
139
140# Skip
141lives_ok { ( $stmt, @bind ) = $sql_ab->select( $table, $fields, $where, $order, $limit, $offset, 'Skip' ) } 'select Skip';
142
143is_same_sql_bind(
144  $stmt, \@bind,
145  "select skip $offset limit $limit $base_sql ORDER BY pay, age", \@expected_bind,
146  'Skip SQL',
147);
148
149
150