X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=xt%2Fclauses.t;h=85d002db2edc594b01f0fa3181f96059ff3e203e;hb=df5d050778086f74e7e2701e5ea5eb988164f88f;hp=e723b46f446e00af8660ad7b8b7713e1e89119ec;hpb=2329b891351569b8d428a98ff397d7bf5cf2efc8;p=scpubgit%2FQ-Branch.git

diff --git a/xt/clauses.t b/xt/clauses.t
index e723b46..85d002d 100644
--- a/xt/clauses.t
+++ b/xt/clauses.t
@@ -2,9 +2,15 @@ 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 ],
@@ -296,7 +302,7 @@ is_same_sql(
     },
   ],
   into => 'license_person',
-  fields => [ qw(person_id, license_id) ],
+  fields => [ qw(person_id license_id) ],
   select => {
     _ => ['person_id', 'license_id'],
     from => ['grandfather'],
@@ -320,10 +326,158 @@ is_same_sql_bind(
       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)
+    ) 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;