javascript - 使用 Javascript 和 PLV8 在 Postgresql 中计算重复事件-6ren

javascript - 使用 Javascript 和 PLV8 在 Postgresql 中计算重复事件

转载作者：塔克拉玛干更新时间：2023-11-02 21:15:10

我已经阅读了大约 20 篇关于对重复事件数据进行建模的选项的不同帖子，并最终确定了一个实现，但在最后的细节方面仍然需要一些帮助。

首先，这里描述一下我要完成的任务和要求:

我正在使用 Postgres 9.4
我将它托管在 Amazon RDS 上，它只允许有限数量的预先审查的扩展。

显然，我的应用程序的很大一部分是基于调度的。具体来说，我的系统的这个组件允许企业的客户安排上门服务的约会。这些约会可以是单次出现的，也可以是重复出现的。虽然这些约会在它们具有开始/结束时间等的意义上是典型的，但每个约会也具有与其相关联的数据。该数据可能包括入住时间、退房时间、备注等内容。

为了存储重复约会，我的选择是要么生成每个实例到预定的时间点，然后在这些实例运行时在数据库中生成新实例，要么动态计算要返回的这些实例。我最终选择了后者，因为我认为从长远来看，该实现更清晰且更易于维护。

话虽如此，使用来自 this post 的见解，我选择将我的循环模式存储为 RRULE 字符串并使用 PGV8 和 rrule.js图书馆做任何计算。那篇文章提到了物化 View 的使用(阅读过它们，但从未使用过)，但我不确定它是否适用于我的情况，因为我必须在重复约会更改或创建时重新生成这些 View 。似乎我还需要每个企业的物化 View ，并且不确定这可能会如何影响存储/性能，因为可能有 1000 家企业。如果您对此有任何见解，请告诉我。

我们的想法是创建一个表Appointment，其中包含与约会的实际日期、时间和重复(如果适用)相关的数据。它至少包含以下字段:

开始日期
结束日期
重复模式 (RRULE)
异常(exception)情况
服务编号

然后，第二个表 AppointmentData 将存储有关约会本身的任何元数据。例如，它可能包含以下字段:

预约 ID(FK 给家长)
备注
入住时间
退房时间
等等

AppointmentData 实例只会在服务提供商实际启动 Appointment 时创建。

一般来说，我只需要能够在任何给定时间检索 31 天或更短的约会(除了检索单个实例之外)。也就是说，我的想法是能够将开始日期和结束日期传递给数据库，该数据库将找到属于该范围内的所有单次约会。此外，对于包含重复模式的任何记录，我将使用我的 PLV8 函数返回属于该范围内的日期列表。 rrule.js 库有一个函数可以返回循环模式的所有日期 (rule.between(new Date(2012, 7, 1), new Date(2012, 8, 1) )))。

这是我有点磕磕绊绊的地方。现在我在数据库中有一个可以动态计算重复日期的函数，我有点不清楚如何将这些与单个事件“融合”在一起并将它们作为单个结果集返回。请注意，对于每个重复出现的实例，我还需要返回 Appointment 表中的所有列，例如 serviceID。

如果有什么不清楚的，请告诉我。

提前致谢!

最佳答案

对于那些希望做类似事情的人，这是我想出的:

首先，使用 rrule.js 从 iCAL RRule 字符串生成循环的函数:

CREATE OR REPLACE FUNCTION public.generate_recurrences(
  recurrence_pattern CHARACTER VARYING,
  start_date date,
  end_date date)
 RETURNS SETOF TEXT
 LANGUAGE plv8
 IMMUTABLE STRICT
AS $function$

  // parse the RRULE string
  var rule = RRule.fromString(recurrence_pattern);

  // return all occurrences between start date and end date
  var recurrences = rule.between(start_date, end_date);

  for(var i = 0; i < recurrences.length; i++) {
     plv8.return_next(new Date(recurrences[i]).toISOString());
  }

$function$

最后是获取任何非重复约会实例并将它们与上述函数生成的重复实例融合的函数:

CREATE OR REPLACE FUNCTION recurring_events_for(
   for_business_id INTEGER,
   range_start DATE,
   range_end   DATE
)
   RETURNS SETOF appointment
   LANGUAGE plpgsql STABLE
   AS $BODY$
DECLARE
   appointment appointment;
   recurrence  TIMESTAMPTZ;
   appointment_length INTERVAL;
BEGIN
   FOR appointment IN
       SELECT *
         FROM appointment
        WHERE business_id = for_business_id
          AND (
                  recurrence_pattern IS NOT NULL
              OR  (
                     recurrence_pattern IS NULL
                 AND scheduled_start_time BETWEEN range_start AND range_end
              )
          )
    LOOP
       IF appointment.recurrence_pattern IS NULL THEN
         RETURN NEXT appointment;
         CONTINUE;
       END IF;

       appointment_length := appointment.scheduled_end_time - appointment.scheduled_start_time;

       FOR recurrence IN
           SELECT *
             FROM generate_recurrences(
                      appointment.recurrence_pattern,
                      range_start,
                      range_end
             )
       LOOP
           EXIT WHEN recurrence::date > range_end::date;
           CONTINUE WHEN recurrence::date < range_start::date AND recurrence::date > range_end::date;
           appointment.scheduled_start_time := recurrence;
           appointment.scheduled_end_time := recurrence + appointment_length;
           RETURN NEXT appointment;
       END LOOP;
   END LOOP;
   RETURN;
END;
$BODY$;