Notification Tech Info

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.