use strict;
use warnings;
use Test::More;
use SQL::Maker;
use Test::Requires 'Tie::IxHash';
use SQL::Maker::SelectSet qw(union union_all intersect intersect_all except except_all);
sub ns {
SQL::Maker::Select->new( quote_char => q{}, name_sep => q{.}, new_line => q{ } );
}
subtest 'basic' => sub {
my $s1 = ns()
->add_from( 'table1' )
->add_select( 'id' )
->add_where( foo => 100 );
my $s2 = ns()
->add_from( 'table2' )
->add_select( 'id' )
->add_where( bar => 200 );
my $s3 = ns()
->add_from( 'table3' )
->add_select( 'id' )
->add_where( baz => 300 );
subtest 'error' => sub {
eval { union( 1, 2 ) };
ok( $@ );
eval { SQL::Maker::SelectSet->union( $s1, $s2 ) };
ok( $@ );;
eval { SQL::Maker::SelectSet->union( $s1 ) };
ok( $@ );
};
subtest 'union' => sub {
my $set = union( $s1, $s2 );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) UNION SELECT id FROM table2 WHERE (bar = ?)};
is join(', ', $set->bind), '100, 200';
$set = union( $set, $s3 );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) UNION SELECT id FROM table2 WHERE (bar = ?) UNION SELECT id FROM table3 WHERE (baz = ?)};
is join(', ', $set->bind), '100, 200, 300';
$set = union( $s3, union( $s1, $s2 ) );
is $set->as_sql, qq{SELECT id FROM table3 WHERE (baz = ?) UNION SELECT id FROM table1 WHERE (foo = ?) UNION SELECT id FROM table2 WHERE (bar = ?)};
is join(', ', $set->bind), '300, 100, 200';
$set = union_all( $s1, $s2 );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) UNION ALL SELECT id FROM table2 WHERE (bar = ?)};
is join(', ', $set->bind), '100, 200';
$set->add_order_by( 'id' );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) UNION ALL SELECT id FROM table2 WHERE (bar = ?) ORDER BY id};
is join(', ', $set->bind), '100, 200';
$set = union( union( $s3, $s1 ), $s2 );
is $set->as_sql, qq{SELECT id FROM table3 WHERE (baz = ?) UNION SELECT id FROM table1 WHERE (foo = ?) UNION SELECT id FROM table2 WHERE (bar = ?)};
is join(', ', $set->bind), '300, 100, 200';
$set = union( union( $s1, $s2 ), union( $s2, $s3) );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) UNION SELECT id FROM table2 WHERE (bar = ?) UNION SELECT id FROM table2 WHERE (bar = ?) UNION SELECT id FROM table3 WHERE (baz = ?)};
is join(', ', $set->bind), '100, 200, 200, 300';
};
subtest 'intersect' => sub {
my $set = intersect( $s1, $s2 );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) INTERSECT SELECT id FROM table2 WHERE (bar = ?)};
is join(', ', $set->bind), '100, 200';
$set = intersect( $set, $s3);
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) INTERSECT SELECT id FROM table2 WHERE (bar = ?) INTERSECT SELECT id FROM table3 WHERE (baz = ?)};
is join(', ', $set->bind), '100, 200, 300';
$set = intersect( $s3, intersect( $s1, $s2 ) );
is $set->as_sql, qq{SELECT id FROM table3 WHERE (baz = ?) INTERSECT SELECT id FROM table1 WHERE (foo = ?) INTERSECT SELECT id FROM table2 WHERE (bar = ?)};
is join(', ', $set->bind), '300, 100, 200';
$set = intersect_all( $s1, $s2 );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) INTERSECT ALL SELECT id FROM table2 WHERE (bar = ?)};
is join(', ', $set->bind), '100, 200';
$set->add_order_by( 'id' );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) INTERSECT ALL SELECT id FROM table2 WHERE (bar = ?) ORDER BY id};
is join(', ', $set->bind), '100, 200';
};
subtest 'except' => sub {
my $set = except( $s1, $s2 );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) EXCEPT SELECT id FROM table2 WHERE (bar = ?)};
is join(', ', $set->bind), '100, 200';
$set = except( $set, $s3 );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) EXCEPT SELECT id FROM table2 WHERE (bar = ?) EXCEPT SELECT id FROM table3 WHERE (baz = ?)};
is join(', ', $set->bind), '100, 200, 300';
$set = except( $s3, except( $s1, $s2 ) );
is $set->as_sql, qq{SELECT id FROM table3 WHERE (baz = ?) EXCEPT SELECT id FROM table1 WHERE (foo = ?) EXCEPT SELECT id FROM table2 WHERE (bar = ?)};
is join(', ', $set->bind), '300, 100, 200';
$set = except_all( $s1, $s2 );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) EXCEPT ALL SELECT id FROM table2 WHERE (bar = ?)};
is join(', ', $set->bind), '100, 200';
$set->add_order_by( 'id' );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) EXCEPT ALL SELECT id FROM table2 WHERE (bar = ?) ORDER BY id};
is join(', ', $set->bind), '100, 200';
};
subtest 'multiple' => sub {
my $set = intersect( except($s1, $s2), $s3 );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) EXCEPT SELECT id FROM table2 WHERE (bar = ?) INTERSECT SELECT id FROM table3 WHERE (baz = ?)};
is join(', ', $set->bind), '100, 200, 300';
$set = intersect_all( except( $s1, $s2 ), $s3 );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) EXCEPT SELECT id FROM table2 WHERE (bar = ?) INTERSECT ALL SELECT id FROM table3 WHERE (baz = ?)};
is join(', ', $set->bind), '100, 200, 300';
$set = union( except( $s1, $s2), $s3 );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) EXCEPT SELECT id FROM table2 WHERE (bar = ?) UNION SELECT id FROM table3 WHERE (baz = ?)};
is join(', ', $set->bind), '100, 200, 300';
$set = union( except_all( $s1, $s2 ), $s3 );
is $set->as_sql, qq{SELECT id FROM table1 WHERE (foo = ?) EXCEPT ALL SELECT id FROM table2 WHERE (bar = ?) UNION SELECT id FROM table3 WHERE (baz = ?)};
is join(', ', $set->bind), '100, 200, 300';
};
};
sub check_sql {
my @lines = split/\n/, $_[0];
my $sql = '';
for my $line ( @lines ) {
$line =~ s/^\s+//;
$line =~ s/^WHERE/ WHERE/;
$line =~ s/^FROM/ FROM/;
$line =~ s/^INNER/ INNER/;
$line =~ s/^EXCEPT/ EXCEPT/;
$line =~ s/^UNION/ UNION/;
$sql .= $line;
}
return $sql;
}
subtest 'complex' => sub {
my $s1 = ns ->add_from( 'member' )
->add_select('id')
->add_select('created_on')
->add_where( is_deleted => 'f' );
my $not_in = ns ->add_from('group_member')
->add_select('member_id')
->add_where( 'is_beginner' => 'f' );
my $s2 = ns ->add_from( $s1, 'm1' )
->add_select('m1.id')
->add_select('m1.created_on')
->add_where( 'm1.id' => {
'NOT IN' => \[ '(' . $not_in->as_sql . ')' , $not_in->bind ]
} );
my $s3 = ns ->add_select('mi.id')
->add_select( \do{'false'}, 'is_group' )
->add_select('mi.created_on')
->add_join(
[$s2, 'm2'] => {
table => 'member_index', alias => 'mi', type => 'inner', condition => 'mi.id = m2.id'
}
)
->add_where( 'mi.lang' => 'ja' );
is( $s3->as_sql, check_sql(<<SQL) );
SELECT mi.id, false AS is_group, mi.created_on
FROM (
SELECT m1.id, m1.created_on FROM (
SELECT id, created_on FROM member WHERE (is_deleted = ?)
) m1 WHERE (m1.id NOT IN ((SELECT member_id FROM group_member WHERE (is_beginner = ?))))
) m2 INNER JOIN member_index mi ON mi.id = m2.id WHERE (mi.lang = ?)
SQL
is join(', ', $s3->bind), 'f, f, ja';
my $s4 = ns ->add_join(
['group', 'g1'] => {
table => 'group_member', alias => 'gm1',
type => 'inner', condition => 'gm1.member_id = g1.id'
}
)
->add_join(
['group', 'g1'] => {
table => 'member', alias => 'm3',
type => 'inner', condition => 'gm1.member_id = m3.id'
}
)
->add_select( 'g1.id' )
->add_where( 'g1.type' => 'hoge' );
my $not_in2 = ns ->add_select('id')
->add_from('member')
->add_where( 'is_monger' => 't' );
my $s5 = ns ->add_select( 'g2.id' )
->add_join(
['group', 'g2'] => {
table => 'group_member', alias => 'gm2',
type => 'inner', condition => 'gm2.member_id = g2.id'
}
)
->add_where( 'gm2.member_id' => {
'NOT IN' => \[ '(' . $not_in2->as_sql . ')' , $not_in2->bind ]
} )
->add_where( 'g2.is_deleted' => 'f' );
my $set = except( $s4, $s5 );
my $s6 = ns ->add_join(
[$set, 'g'] => {
table => 'group_index', alias => 'gi',
type => 'inner', condition => 'gi.id = g.id'
}
)
->add_select( 'g.id' )
->add_select( \do{'true'}, 'is_group' )
->add_select( 'gsi.created_on' )
->add_where( 'gi.lang' => 'ja' );
is( $s6->as_sql, check_sql(<<SQL) );
SELECT g.id, true AS is_group, gsi.created_on
FROM (
SELECT g1.id FROM group g1
INNER JOIN group_member gm1 ON gm1.member_id = g1.id
INNER JOIN member m3 ON gm1.member_id = m3.id
WHERE (g1.type = ?)
EXCEPT
SELECT g2.id FROM group g2
INNER JOIN group_member gm2 ON gm2.member_id = g2.id
WHERE (gm2.member_id NOT IN (
(SELECT id FROM member WHERE (is_monger = ?))
)) AND (g2.is_deleted = ?)
) g INNER JOIN group_index gi ON gi.id = g.id WHERE (gi.lang = ?)
SQL
is join(', ', $s6->bind), 'hoge, t, f, ja';
$set = union( $s3, $s6 );
my $s7 = ns ->add_select( 'id' )
->add_select( 'is_group' )
->add_from( $set, 'list_table' )
->add_order_by( 'created_on' );
is( $s7->as_sql, check_sql(<<SQL) );
SELECT id, is_group FROM (
SELECT mi.id, false AS is_group, mi.created_on
FROM (
SELECT m1.id, m1.created_on FROM (
SELECT id, created_on FROM member WHERE (is_deleted = ?)
) m1 WHERE (m1.id NOT IN ((SELECT member_id FROM group_member WHERE (is_beginner = ?))))
) m2 INNER JOIN member_index mi ON mi.id = m2.id WHERE (mi.lang = ?)
UNION
SELECT g.id, true AS is_group, gsi.created_on
FROM (
SELECT g1.id FROM group g1
INNER JOIN group_member gm1 ON gm1.member_id = g1.id
INNER JOIN member m3 ON gm1.member_id = m3.id
WHERE (g1.type = ?)
EXCEPT
SELECT g2.id FROM group g2
INNER JOIN group_member gm2 ON gm2.member_id = g2.id
WHERE (gm2.member_id NOT IN (
(SELECT id FROM member WHERE (is_monger = ?))
)) AND (g2.is_deleted = ?)
) g INNER JOIN group_index gi ON gi.id = g.id WHERE (gi.lang = ?)
) list_table ORDER BY created_on
SQL
is join(', ', $s7->bind), 'f, f, ja, hoge, t, f, ja';
};
done_testing;
__END__