Notification Tech Info

From MiOS
(Difference between revisions)
Jump to: navigation, search
 
(One intermediate revision by one user not shown)
Line 1: Line 1:
The following query gets the list of outstanding notifications from the cmh_hosted database:
+
[[Category:User Instructions]]
  
select * FROM Notification_User
+
The following query gets the list of outstanding notifications from the cmh_hosted database:
LEFT JOIN Notification_Attempt ON FK_Notification_Attempt=PK_Notification_Attempt
+
JOIN Notification ON FK_Notification=PK_Notification
+
WHERE PK_Notification_Attempt IS NULL OR (Final=0 And Result>1);
+
  
NotificationType is a single character: E, S, V for Email/SMS/Voice.  Destination is the corresponding destination (email/phone number).
+
'''select * FROM Notification_User LEFT JOIN Notification_Attempt ON FK_Notification_Attempt=PK_Notification_Attempt JOIN Notification ON FK_Notification=PK_Notification WHERE PK_Notification_Attempt IS NULL OR (Final=0 And Result>1);'''
  
The script: X does this query that will find a list of all records where we need to contact the user. It should go through the result set and for each row it needs to make adds a record in Notification_Attempt and updates FK_NotificationAttempt in Notification_Users, and set Result to 0 (in progress), and do the attempted contact (ie send an email, sms, etc.). If the attempt was successful, it sets Final=1 and Result=1. If the attempt wasn't successful it sets Result to an error code (starting with 2). And it does a count on all records in Notification_Attempt where FK_Notification_User=x. If the count is >5 (meaning 5 failed attempts), set Final to 1 (meaning don't retry). At the end of the script, the script calls itself again in a loop so it can retry any failures. The script exits only when there are no more records. Your php page that receives an alert starts the script. So the script needs to touch a file or do something to prevent more than 1 instance from running while preventing a race condition.
+
NotificationType is a single character: E, S, V for Email/SMS/Voice. Destination is the corresponding destination (email/phone number).
 +
 
 +
The script: X does this query that will find a list of all records where we need to contact the user. It should go through the result set and, for each row it needs to make, adds a record in Notification_Attempt, updates FK_NotificationAttempt in Notification_Users, and sets Result to 0 ('in progress'), and attempts contact (i.e. sends an email, SMS, etc.). If the attempt was successful, it sets Final=1 and Result=1. If the attempt wasn't successful, it sets Result to an error code (starting with 2). And it does a count on all records in Notification_Attempt where FK_Notification_User=x. If the count is >5 (meaning 5 failed attempts), it sets Final to 1 (meaning don't retry). At the end of the script, the script calls itself again in a loop so it can retry any failures. The script exits only when there are no more records. Your php page that receives an alert starts the script. So the script needs to touch a file or do something to prevent more than 1 instance from running while preventing a race condition.

Latest revision as of 20:41, 20 May 2011


The following query gets the list of outstanding notifications from the cmh_hosted database:

select * FROM Notification_User LEFT JOIN Notification_Attempt ON FK_Notification_Attempt=PK_Notification_Attempt JOIN Notification ON FK_Notification=PK_Notification WHERE PK_Notification_Attempt IS NULL OR (Final=0 And Result>1);

NotificationType is a single character: E, S, V for Email/SMS/Voice. Destination is the corresponding destination (email/phone number).

The script: X does this query that will find a list of all records where we need to contact the user. It should go through the result set and, for each row it needs to make, adds a record in Notification_Attempt, updates FK_NotificationAttempt in Notification_Users, and sets Result to 0 ('in progress'), and attempts contact (i.e. sends an email, SMS, etc.). If the attempt was successful, it sets Final=1 and Result=1. If the attempt wasn't successful, it sets Result to an error code (starting with 2). And it does a count on all records in Notification_Attempt where FK_Notification_User=x. If the count is >5 (meaning 5 failed attempts), it sets Final to 1 (meaning don't retry). At the end of the script, the script calls itself again in a loop so it can retry any failures. The script exits only when there are no more records. Your php page that receives an alert starts the script. So the script needs to touch a file or do something to prevent more than 1 instance from running while preventing a race condition.

Personal tools