Index: openacs-4/packages/notifications/tcl/sweep-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/notifications/tcl/Attic/sweep-procs-postgresql.xql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/notifications/tcl/sweep-procs-postgresql.xql	20 Sep 2002 21:19:00 -0000	1.3
+++ openacs-4/packages/notifications/tcl/sweep-procs-postgresql.xql	16 Jan 2003 13:53:10 -0000	1.4
@@ -5,42 +5,33 @@
 
     <fullquery name="notification::sweep::cleanup_notifications.select_notification_ids">
         <querytext>
-            select notification_id
-            from notifications
-            where now() - notif_date > 2
-              and not exists (select notifications.notification_id
-                              from notifications
-                                  inner join notification_requests
-                                      on (
-                                               notifications.type_id = notification_requests.type_id
-                                           and notifications.object_id = notification_requests.object_id
-                                      )
-                                  left outer join notification_user_map
-                                      on (notifications.notification_id = notification_user_map.notification_id)
-                              where notification_user_map.sent_date is null)
+           select notification_id
+           from notifications
+           except
+           select distinct notification_id
+           from notifications inner join notification_requests using (type_id, object_id)
+             left outer join notification_user_map using (notification_id, user_id)
+           where sent_date is null
         </querytext>
     </fullquery>
 
     <fullquery name="notification::sweep::sweep_notifications.select_notifications">
         <querytext>
-            select notifications.notification_id,
-                   notifications.notif_subject,
-                   notifications.notif_text,
-                   notifications.notif_html,
-                   notification_requests.user_id,
-                   notification_requests.type_id,
-                   notification_requests.delivery_method_id,
-                   notifications.response_id
-            from notifications
-                     inner join notification_requests
-                         on (
-                                  notifications.type_id = notification_requests.type_id
-                              and notifications.object_id = notification_requests.object_id
-                         )
-                     left outer join notification_user_map
-                         on (notifications.notification_id = notification_user_map.notification_id)
-            where notification_requests.interval_id = :interval_id
-            and notification_user_map.sent_date is null
+            select notification_id,
+                   notif_subject,
+                   notif_text,
+                   notif_html,
+                   user_id,
+                   type_id,
+                   delivery_method_id,
+                   response_id
+            from notifications inner join notification_requests using (type_id, object_id)
+              inner join acs_objects on (notification_requests.request_id = acs_objects.object_id)
+              left outer join notification_user_map using (notification_id, user_id)
+            where sent_date is null
+              and creation_date <= notif_date
+              and interval_id = :interval_id
+            order by user_id, type_id
         </querytext>
     </fullquery>