Index: openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d1-0.6d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d1-0.6d2.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d1-0.6d2.sql 28 Mar 2007 19:43:25 -0000 1.1.2.1 @@ -0,0 +1,292 @@ + +create or replace function acs_event__insert_instances ( + -- + -- This is the key procedure creating recurring events. This procedure + -- uses the interval set and recurrence information referenced by the event + -- to insert additional information to represent the recurrences. + -- Events will be added up until the earlier of recur_until and + -- cutoff_date. The procedure enforces a hard internal + -- limit of adding no more than 10,000 recurrences at once to reduce the + -- risk of demolishing the DB because of application bugs. The date of the + -- last recurrence added is marked as the db_populated_until date. + -- + -- The application is responsible for calling this function again if + -- necessary to populate to a later date. + -- + -- JS: Note that the following Oracle functions do not have any equivalent + -- JS: (at least in an obvious way) in Postgres: next_day, add_months, last_day. + -- JS: Ports of these functions are in oracle-compat-create.sql. + -- JS: + -- JS: To understand the port, it is important to keep in mind the subtle but + -- JS: important differences in the way Oracle and Postgres do date arithmetic. + -- JS: Compatibility with Oracle requires that all integers involved in date arithmetic + -- JS: be converted to Postgres day intervals, hence the typecasting. The typecasting + -- JS: function to_interval (also in oracle-compat-create.sql) is simply a convenience + -- JS: so that the code will not be littered by escaped quotes. + -- JS: + -- JS: NOTE: There seems to be some weirdness going on with recurrence + -- JS: when moving from non-DST to DST dates (email me for the gory details). + -- JS: Not sure if a Postgres bug or feature. + -- + -- @author W. Scott Meeks + -- + -- @param event_id The id of the event to recur. If the + -- event's recurrence_id is null, nothing happens. + -- @param cutoff_date Determines how far out to prepopulate the DB. + -- Default is now() plus the value of the + -- EventFutureLimit site parameter. + -- + -- @return 0 (procedure dummy) + -- + integer, -- acs_events.event_id%TYPE, + timestamptz -- default null +) +returns integer as ' +declare + insert_instances__event_id alias for $1; + insert_instances__cutoff_date alias for $2; -- default null + event_row acs_events%ROWTYPE; + recurrence_row recurrences%ROWTYPE; + v_event_id acs_events.event_id%TYPE; + v_interval_name recurrence_interval_types.interval_name%TYPE; + v_n_intervals recurrences.every_nth_interval%TYPE; + v_days_of_week recurrences.days_of_week%TYPE; + v_last_date_done timestamptz; + v_stop_date timestamptz; + v_start_date timestamptz; + v_event_date timestamptz; + v_diff integer; + v_current_date timestamptz; + v_last_day timestamptz; + v_week_date timestamptz; + v_instance_count integer; + v_days_length integer; + v_days_index integer; + v_day_num integer; + rec_execute record; + v_new_current_date timestamptz; + v_offset_notice interval; +begin + + -- Get event parameters + select * into event_row + from acs_events + where event_id = insert_instances__event_id; + + -- Get recurrence information + select * into recurrence_row + from recurrences + where recurrence_id = event_row.recurrence_id; + + + -- Set cutoff date to stop populating the DB with recurrences + -- EventFutureLimit is in years. (a parameter of the service) + if insert_instances__cutoff_date is null then + v_stop_date := add_months(now(), 12 * to_number(acs_event__get_value(''EventFutureLimit''),''99999'')::INT); + else + v_stop_date := insert_instances__cutoff_date; + end if; + + -- Events only populated until max(cutoff_date, recur_until) + -- If recur_until null, then defaults to cutoff_date + if recurrence_row.recur_until < v_stop_date then + v_stop_date := recurrence_row.recur_until; + end if; + + -- Figure out the date to start from. + -- JS: I do not understand why the date must be truncated to the midnight of the event date + select min(start_date) + into v_event_date + from acs_events_dates + where event_id = insert_instances__event_id; + + if recurrence_row.db_populated_until is null then + v_start_date := v_event_date; + else + v_start_date := recurrence_row.db_populated_until; + end if; + + v_current_date := v_start_date; + v_last_date_done := v_start_date; + v_n_intervals := recurrence_row.every_nth_interval; + + -- Case off of the interval_name to make code easier to read + select interval_name into v_interval_name + from recurrences r, + recurrence_interval_types t + where recurrence_id = recurrence_row.recurrence_id + and r.interval_type = t.interval_type; + + -- Week has to be handled specially. + -- Start with the beginning of the week containing the start date. + if v_interval_name = ''week'' + then + v_current_date := next_day(v_current_date - to_interval(7,''days''),''SUNDAY''); + v_days_of_week := recurrence_row.days_of_week; + v_days_length := char_length(v_days_of_week); + end if; + + -- Check count to prevent runaway in case of error + v_instance_count := 0; + + -- A feature: we only care about the date when populating the database for reccurrence. + while v_instance_count < 10000 and (date_trunc(''day'',v_last_date_done) <= date_trunc(''day'',v_stop_date)) + loop + v_instance_count := v_instance_count + 1; + + -- Calculate next date based on interval type + + -- Add next day, skipping every v_n_intervals + if v_interval_name = ''day'' + then + v_current_date := v_current_date + to_interval(v_n_intervals,''days''); + end if; + + -- Add a full month, skipping by v_n_intervals months + if v_interval_name = ''month_by_date'' + then + v_current_date := add_months(v_current_date, v_n_intervals); + end if; + + -- Add days so that the next date will have the same day of the week, and week of the month + if v_interval_name = ''month_by_day'' then + -- Find last day of month before correct month + v_last_day := add_months(last_day(v_current_date), v_n_intervals - 1); + -- Find correct week and go to correct day of week + v_current_date := next_day(v_last_day + + to_interval(7 * (to_number(to_char(v_current_date,''W''),''99'')::INT - 1), + ''days''), + to_char(v_current_date, ''DAY'')); + end if; + + -- Add days so that the next date will have the same day of the week on the last week of the month + if v_interval_name = ''last_of_month'' then + -- Find last day of correct month + v_last_day := last_day(add_months(v_current_date, v_n_intervals)); + -- Back up one week and find correct day of week + v_current_date := next_day(v_last_day ::timestamp - to_interval(7,''days'') :: timestamptz, to_char(v_current_date, ''DAY'')); + end if; + + -- Add a full year (12 months) + If v_interval_name = ''year'' then + v_current_date := add_months(v_current_date, 12 * v_n_intervals); + end if; + + -- Deal with custom function + if v_interval_name = ''custom'' then + + -- JS: Execute a dynamically created query on the fly... + FOR rec_execute IN + EXECUTE ''select '' || recurrence_row.custom_func + || ''('' || quote_literal(v_current_date) + || '','' || v_n_intervals || '') as current_date'' + LOOP + v_current_date := rec_execute.current_date; + END LOOP; + + end if; + + -- Check to make sure we are not going past Trunc because dates are not integral + exit when date_trunc(''day'',v_current_date) > date_trunc(''day'',v_stop_date); + + -- Have to handle week specially + if v_interval_name = ''week'' then + -- loop over days_of_week extracting each day number + -- add day number and insert + v_days_index := 1; + v_week_date := v_current_date; + while v_days_index <= v_days_length loop + v_day_num := SUBSTR(v_days_of_week, v_days_index, 1); + v_week_date := (v_current_date ::timestamp + to_interval(v_day_num,''days'')) :: timestamptz; + if date_trunc(''day'',v_week_date) > date_trunc(''day'',v_start_date) + and date_trunc(''day'',v_week_date) <= date_trunc(''day'',v_stop_date) then + -- This is where we add the event + v_event_id := acs_event__new_instance( + insert_instances__event_id, -- event_id + date_trunc(''day'',v_week_date) - date_trunc(''day'',v_event_date) -- offset + ); + v_last_date_done := v_week_date; + + else if date_trunc(''day'',v_week_date) > date_trunc(''day'',v_stop_date) + then + -- Gone too far + exit; + end if; + + end if; + + v_days_index := v_days_index + 2; + + end loop; + + -- Now move to next week with repeats. + v_current_date := (v_current_date :: timestamp + to_interval(7 * v_n_intervals,''days'')) :: timestamptz; + else + -- All other interval types + -- This is where we add the event + v_event_id := acs_event__new_instance( + insert_instances__event_id, -- event_id + date_trunc(''day'',v_current_date ::timestamp) - date_trunc(''day'',v_event_date ::timestamp) -- offset + ); + v_last_date_done := v_current_date; + end if; + end loop; + + update recurrences + set db_populated_until = v_last_date_done + where recurrence_id = recurrence_row.recurrence_id; + + return 0; +end;' language 'plpgsql'; + +create or replace function time_interval__copy( + -- + -- Creates a new copy of a time interval, offset by optional offset + -- + -- JS: We need to be careful in interpreting the copy offset. + -- JS: Oracle interprets integers as full days when doing + -- JS: date arithmetic. Thus, + -- JS: + -- JS: select sysdate()+1 from dual; + -- JS: + -- JS: will yield the next date, correct up to the second of the next day + -- JS: that the query was run. + -- JS: + -- JS: In PostgreSQL, we need to specify the type of interval when + -- JS: doing date arithmetic. if, say, an integer is used in date arithmetic, + -- JS: the results are weird. For example, + -- JS: + -- JS: select now()+1 from dual; + -- JS: + -- JS: will yield the MIDNIGHT of the next date that the query was run, i.e., + -- JS: the timestamp is typecasted as a date with a day granularity. To get the + -- JS: same effect as Oracle, we need to use explicitly typecast the integer into + -- JS: a day interval. + -- + -- @author W. Scott Meeks + -- + -- @param interval_id Interval to copy + -- @param offset Interval is offset by this date interval + -- + -- @return interval_id of the copied interval + -- + integer, -- time_intervals.interval_id%TYPE, + interval +) +returns integer as ' -- time_intervals.interval_id%TYPE +declare + copy__interval_id alias for $1; + copy__offset alias for $2; -- default 0 + interval_row time_intervals%ROWTYPE; + v_foo timestamptz; +begin + select * into interval_row + from time_intervals + where interval_id = copy__interval_id; + + return time_interval__new( + (interval_row.start_date ::timestamp + copy__offset) :: timestamptz, + (interval_row.end_date ::timestamp + copy__offset) :: timestamptz + ); + +end;' language 'plpgsql';