SF.net SVN: postfixadmin:[1676] trunk/VIRTUAL_VACATION/vacation.pl

[<chr...@us...>]

Revision: 1676
          http://sourceforge.net/p/postfixadmin/code/1676
Author:   christian_boltz
Date:     2014-05-27 21:34:43 +0000 (Tue, 27 May 2014)
Log Message:
-----------
vacation.pl:
- fix two queries for postgresql
  Patch by Christoph Kuchenbuch,
  https://sourceforge.net/p/postfixadmin/bugs/315/

Modified Paths:
--------------
    trunk/VIRTUAL_VACATION/vacation.pl

Modified: trunk/VIRTUAL_VACATION/vacation.pl
===================================================================
--- trunk/VIRTUAL_VACATION/vacation.pl	2014-05-27 21:27:20 UTC (rev 1675)
+++ trunk/VIRTUAL_VACATION/vacation.pl	2014-05-27 21:34:43 UTC (rev 1676)
@@ -321,9 +321,14 @@
 sub already_notified {
     my ($to, $from) = @_;
     my $logger = get_logger();
+    my $query;
 
     # delete old notifications
-    my $query = qq{DELETE vacation_notification.* FROM vacation_notification LEFT JOIN vacation ON vacation.email = vacation_notification.on_vacation WHERE on_vacation = ? AND notified = ? AND notified_at < vacation.activefrom};
+    if ($db_type eq 'Pg') {
+        $query = qq{DELETE FROM vacation_notification USING vacation WHERE vacation.email = vacation_notification.on_vacation AND on_vacation = ? AND notified = ? AND notified_at < vacation.activefrom;};
+    } else { # mysql
+        $query = qq{DELETE vacation_notification.* FROM vacation_notification LEFT JOIN vacation ON vacation.email = vacation_notification.on_vacation WHERE on_vacation = ? AND notified = ? AND notified_at < vacation.activefrom};
+    }
     my $stm = $dbh->prepare($query);
     if (!$stm) {
         $logger->error("Could not prepare query (trying to delete old vacation notifications) :'$query' to: $to, from:$from");
@@ -353,7 +358,11 @@
         $interval = get_interval($to);
 
         if ($interval) {
-            $query = qq{SELECT NOW()-notified_at FROM vacation_notification WHERE on_vacation=? AND notified=?};
+            if ($db_type eq 'Pg') {
+                $query = qq{SELECT extract( epoch from (NOW()-notified_at))::int FROM vacation_notification WHERE on_vacation=? AND notified=?};
+            } else { # mysql
+                $query = qq{SELECT NOW()-notified_at FROM vacation_notification WHERE on_vacation=? AND notified=?};
+            }
             $stm = $dbh->prepare($query) or panic_prepare($query);
             $stm->execute($to,$from) or panic_execute($query,"on_vacation='$to', notified='$from'");
             my @row = $stm->fetchrow_array;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.