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

Question

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

javascriptsqlpostgresql

7

我已经阅读了大约20篇有关建模循环事件数据的选项的文章，并最终确定了一种实现方式，但在最后的细节方面仍需要一些帮助。

首先，这是我想要实现和要求的描述：

• 我正在使用Postgres 9.4

• 我将其托管在Amazon RDS上，该平台仅允许有限数量的预先审核的扩展。

显然，我的应用程序的一个重要部分基于调度。具体来说，我的系统组件允许企业客户为上门服务安排预约。这些约会可以是单次事件或重复事件。虽然这些约会通常具有开始/结束时间等特征，但每个约会还与其相关的数据有关。这些数据可能包括签到时间、签退时间、注释等内容。

对于存储重复约会，我的选择是生成每个实例，直到预定时间点，然后在数据库中生成新实例，每当这些实例运行时。我最终选择了后者，因为我认为这种实现更加简洁易于长期维护。

话虽如此，我决定使用这篇文章中的见解，将我的循环模式存储为RRULE字符串，并使用PGV8和rrule.js库进行任何计算。该文章提到了材料化视图（了解过它们，但从未使用过），但我不确定在我的情况下是否可行，因为每当重复约会发生变化或创建时，我都必须重新生成这些视图。另外，似乎我需要为每个企业创建一个材料化视图，不确定这会影响存储/性能，因为可能会有数千个企业。如果您对此有任何见解，请告诉我。

想法是拥有一个表格Appointment，它包含与约会的实际日期、时间和循环（如果适用）相关的数据。它至少应包含以下字段：

开始日期
结束日期
循环模式（RRULE）
异常
服务ID

然后，第二个表格是AppointmentData，用于存储有关预约本身的任何元数据。例如，它可能包含以下字段：

预约ID（父级外键）
注释
签到时间
签出时间
等等。

只有当服务提供商实际开始Appointment时，才会创建AppointmentData实例。

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

这是我有些困惑的领域。现在，我已经在数据库中拥有了一个能够动态计算重复日期的函数，但我有些不清楚如何将它们与单个事件"融合"并作为单个结果集返回。请注意，对于每个重复实例，我还需要返回Appointment表中的所有列，例如serviceID。

如果有任何不清楚的地方，请告诉我。

提前致谢！

- jdixon04

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- jdixon04 · Accepted Answer

对于那些想要做类似事情的人，这是我想到的方法：

首先，使用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$;

上述操作的结果是一个结果集，其中包含单次预约记录，同时还会即时生成重复预约的完整记录（这些记录只是日期）。