根据每个db记录的时间戳发送通知邮件

Question

我有一个数据库的"事件"(会议，生日聚会等)记录，每个事件都有一个endTime字段。在一个事件结束后，我想为每个过去的事件发送2-3封电子邮件。

现在，我每小时运行一个cron作业来查找在过去一小时内结束的所有事件，所以它看起来像:

• 在2:01pm:找到所有在1-2pm之间结束的事件，发送电子邮件
• 在3:01pm:找到所有在2-3pm之间结束的事件，发送电子邮件
• 在4:01pm:找到所有在3-4pm之间结束的事件，发送电子邮件
• at 5:01pm:…

但是，假设下午3:01的任务由于某种原因失败(Heroku崩溃了我的rest api，电子邮件服务提供商宕机等)，在下午2-3点之间结束的事件将不会收到电子邮件。

目前，如果邮件发送成功，我的db记录没有标记为notified。我应该这样做，这样我就可以改变cron脚本"找到notified=false当前时间之前的所有记录"(捕获所有以前未发送的记录)?然后你会为每一种成功发出的邮件设置一个标志吗?

或者有没有更聪明的方法来避免设置标志?

(这是地狱想出这个问题的标题和标签-欢迎建议/编辑!)

Answer 1

notified标志的问题是，它需要一个索引，只是为了使WHERE notified = FALSE ¹有效，但TRUE值留在索引中，尽管从未在查询中再次使用。

随着时间的推移，您将在索引中有一个小的FALSE部分(这是有用的)和一个大的TRUE部分(这只是浪费空间)。如果您的DBMS支持，使用某种类型的部分索引从索引中删除TRUE:

• Oracle不索引null，所以用null代替TRUE。
• 在MS SQL Server下使用过滤索引，例如:CREATE INDEX ... WHERE notified = 0。
• 在PostgreSQL下使用部分索引

或者，创建一个只有一行和一个字段的额外表:LAstrongENT²，并在成功发送一堆通知时更新该字段。这当然排除了发送无序通知，你需要小心处理部分失败，因为你不能跳过不成功的通知。

---

¹将FALSE替换为适合您的数据库的任何值

²如果您的表使用自动递增的整数作为主键，请考虑将其用于LAstrongENT而不是时间戳，以避免时间戳不唯一的边缘情况

Answer 2

CREATE TABLE events_tbl
(
    e_id    NUMBER
,   e_date  DATE
,   e_info  VARCHAR2(4000)
);
INSERT INTO events_tbl VALUES (43, '2000-01-01 01:10:00', 'event1');
INSERT INTO events_tbl VALUES (44, '2000-01-01 01:15:00', 'event2');
INSERT INTO events_tbl VALUES (45, '2000-01-01 01:20:00', 'event3');
INSERT INTO events_tbl VALUES (46, '2000-01-01 01:25:00', 'event4');
INSERT INTO events_tbl VALUES (47, '2000-01-01 02:10:00', 'event5');
INSERT INTO events_tbl VALUES (48, '2000-01-01 02:15:00', 'event6');
INSERT INTO events_tbl VALUES (49, '2000-01-01 02:20:00', 'event7');
INSERT INTO events_tbl VALUES (50, '2000-01-01 02:25:00', 'event8');
/* table that holds information about sent events */
CREATE TABLE events_sent_tbl
(
    s_e_id NUMBER
,   s_date DATE
);
INSERT INTO events_sent_tbl VALUES (43, '2000-01-01 02:01:00');
INSERT INTO events_sent_tbl VALUES (44, '2000-01-01 02:02:00');
SELECT  *
FROM    events_tbl
WHERE   TO_NUMBER(TO_CHAR(e_date, 'MMDDHH24MiSS')) BETWEEN 0101010000 AND 0101015959
AND     NOT EXISTS
        (
            SELECT  1
            FROM    events_sent_tbl
            WHERE   e_id = s_e_id
        )
;
/*
45  2000-01-01 01:20:00 event3
46  2000-01-01 01:25:00 event4
*/

处理:

• 事件发送
<
• 发送事件/gh>

…在集合。将events_tbl和events_sent_tbl读入集合。发送数据和更新集合中的信息。使用FORALL将其转储到events_sent_tbl表