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