X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=xt%2Fclauses.t;h=85d002db2edc594b01f0fa3181f96059ff3e203e;hb=1601bb47472f6eb2ca3633935bbf3f3399e78604;hp=758a2ea1508fbe40baa89d1116695849e103a349;hpb=f9f1fdcdafdb446bede69c98a8a5b64fd9fef09e;p=scpubgit%2FQ-Branch.git diff --git a/xt/clauses.t b/xt/clauses.t index 758a2ea..85d002d 100644 --- a/xt/clauses.t +++ b/xt/clauses.t @@ -2,9 +2,20 @@ use strict; use warnings; use Test::More; use SQL::Abstract::Test import => [ qw(is_same_sql_bind is_same_sql) ]; +use SQL::Abstract; use SQL::Abstract::ExtraClauses; -my $sqlac = SQL::Abstract::ExtraClauses->new(unknown_unop_always_func => 1); +my $sqlac = SQL::Abstract->new( + unknown_unop_always_func => 1, + lazy_join_sql_parts => 1, +); + +SQL::Abstract::ExtraClauses->apply_to($sqlac); + +is_deeply( + [ $sqlac->statement_list ], + [ sort qw(select update insert delete) ], +); my ($sql, @bind) = $sqlac->select({ select => [ qw(artist.id artist.name), { -json_agg => 'cd' } ], @@ -194,7 +205,7 @@ $sql = $sqlac->select({ is_same_sql( $sql, - q{WITH (foo AS (SELECT 1)) SELECT * FROM foo}, + q{WITH foo AS (SELECT 1) SELECT * FROM foo}, ); $sql = $sqlac->update({ @@ -244,7 +255,7 @@ is_same_sql( $sql, q{ UPDATE tree_table JOIN ( - WITH RECURSIVE (tree_with_path(id, parent_id, path) AS ( + WITH RECURSIVE tree_with_path(id, parent_id, path) AS ( ( SELECT id, parent_id, CAST(id AS char(255)) AS path FROM tree_table @@ -256,7 +267,7 @@ is_same_sql( FROM tree_table AS t JOIN tree_with_path AS r ON t.parent_id = r.id ) - )) + ) SELECT * FROM tree_with_path ) AS tree ON tree.id = tree_with_path.id @@ -264,4 +275,209 @@ is_same_sql( }, ); + +($sql, @bind) = $sqlac->insert({ + with => [ + faculty => { + -select => { + _ => [qw /p.person p.email/], + from => [ person => -as => 'p' ], + where => { + 'p.person_type' => 'faculty', + 'p.person_status' => { '!=' => 'pending' }, + 'p.default_license_id' => undef, + }, + }, + }, + grandfather => { + -insert => { + into => 'license', + fields => [ qw(kind expires_on valid_from) ], + select => { + select => [\(qw('grandfather' '2017-06-30' '2016-07-01'))], + from => 'faculty', + }, + returning => 'license_id', + } + }, + ], + into => 'license_person', + fields => [ qw(person_id license_id) ], + select => { + _ => ['person_id', 'license_id'], + from => ['grandfather'], + where => { + 'a.index' => { -ident => 'b.index' }, + }, + }, +}); + +is_same_sql_bind( + $sql, \@bind, + q{ + WITH faculty AS ( + SELECT p.person, p.email FROM person AS p + WHERE ( + p.default_license_id IS NULL + AND p.person_status != ? + AND p.person_type = ? + ) + ), grandfather AS ( + INSERT INTO license (kind, expires_on, valid_from) + SELECT 'grandfather', '2017-06-30', '2016-07-01' + FROM faculty RETURNING license_id + ) INSERT INTO license_person (person_id, license_id) + SELECT person_id, license_id FROM grandfather WHERE a.index = b.index + }, + [ qw(pending faculty) ], +); + + +($sql, @bind) = $sqlac->delete({ + with => [ + instructors => { + -select => { + _ => [qw/p.person_id email default_license_id/], + from => [ + person => -as => 'p', + -join => { + to => 'license_person', + as => 'lp', + on => { 'lp.person_id' => 'p.person_id' }, + }, + -join => { + to => 'license', + as => 'l', + on => { 'l.license_id' => 'lp.license_id' }, + }, + ], + where => { + 'p.person_type' => 'faculty', + 'p.person_status' => { '!=' => 'pending' }, + 'l.kind' => 'pending', + }, + group_by => [qw/ p.person_id /], + having => { '>' => [ { -count => 'l.license_id' }, 1 ] } + }, + }, + deletable_licenses => { + -select => { + _ => [qw/lp.ctid lp.person_id lp.license_id/], + from => [ + instructors => -as => 'i', + -join => { + to => 'license_person', + as => 'lp', + on => { 'lp.person_id' => 'i.person_id' }, + }, + -join => { + to => 'license', + as => 'l', + on => { 'l.license_id' => 'lp.license_id' }, + }, + ], + where => { + 'lp.license_id' => { + '<>' => {-ident => 'i.default_license_id'} + }, + 'l.kind' => 'pending', + }, + }, + }, + ], + from => 'license_person', + where => { + ctid => { -in => + { + -select => { + _ => ['ctid'], + from => 'deletable_licenses', + } + } + } + } +}); + +is_same_sql_bind( + $sql, \@bind, + q{ + with instructors as ( + select p.person_id, email, default_license_id + from person as p + join license_person as lp on lp.person_id = p.person_id + join license as l on l.license_id = lp.license_id + where l.kind = ? + AND p.person_status != ? + AND p.person_type = ? + group by p.person_id + having COUNT(l.license_id) > ?), + deletable_licenses as ( + select lp.ctid, lp.person_id, lp.license_id + from instructors as i + join license_person as lp on lp.person_id = i.person_id + join license as l on l.license_id = lp.license_id + where l.kind = ? + and lp.license_id <> i.default_license_id + ) + delete from license_person + where ctid IN ( + (select ctid from deletable_licenses) + ) + }, + [qw( + pending pending faculty 1 pending + )] +); + +($sql, @bind) = $sqlac->update({ + _ => ['survey'], + set => { + license_id => { -ident => 'info.default_license_id' }, + }, + from => [ + -select => { + select => [qw( s.survey_id p.default_license_id p.person_id)], + from => [ + person => -as => 'p', + -join => { + to => 'class', + as => 'c', + on => { 'c.faculty_id' => 'p.person_id' }, + }, + -join => { + to => 'survey', + as => 's', + on => { 's.class_id' => 'c.class_id' }, + }, + ], + where => { 'p.institution_id' => { -value => 15031 } }, + }, + -as => 'info', + ], + where => { + 'info.survey_id' => { -ident => 'survey.survey_id' }, + } +}); + +is_same_sql_bind( + $sql, \@bind, + q{ + update survey + set license_id=info.default_license_id + from ( + select s.survey_id, p.default_license_id, p.person_id + from person AS p + join class AS c on c.faculty_id = p.person_id + join survey AS s on s.class_id = c.class_id + where p.institution_id = ? + ) AS info + where info.survey_id=survey.survey_id + }, + [qw( + 15031 + )] +); + + + done_testing;