Index: openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d2-0.6d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d2-0.6d3.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d2-0.6d3.sql 29 Aug 2008 15:13:49 -0000 1.2 +++ openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d2-0.6d3.sql 30 Mar 2013 13:00:30 -0000 1.3 @@ -7,52 +7,20 @@ -- -- Fix Daylight Saving Time bug when creating recurring events -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 + + +-- added +select define_function_args('acs_event__insert_instances','event_id,cutoff_date;null'); + +-- +-- procedure acs_event__insert_instances/2 +-- +CREATE OR REPLACE FUNCTION acs_event__insert_instances( + insert_instances__event_id integer, + insert_instances__cutoff_date timestamptz -- default null + +) RETURNS integer AS $$ +DECLARE event_row acs_events%ROWTYPE; recurrence_row recurrences%ROWTYPE; v_event_id acs_events.event_id%TYPE; @@ -74,7 +42,7 @@ rec_execute record; v_new_current_date timestamptz; v_offset_notice interval; -begin +BEGIN -- Get event parameters select * into event_row @@ -90,7 +58,7 @@ -- 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); + 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; @@ -127,9 +95,9 @@ -- Week has to be handled specially. -- Start with the beginning of the week containing the start date. - if v_interval_name = ''week'' + if v_interval_name = 'week' then - v_current_date := next_day(v_current_date - to_interval(7,''days''),''SUNDAY''); + 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; @@ -138,84 +106,84 @@ 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)) + 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'' + if v_interval_name = 'day' then - v_current_date := v_current_date + to_interval(v_n_intervals,''days''); + 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'' + 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 + 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'')); + 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 + 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'')); + 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 + 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 + 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'' + 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); + 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 + 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 + 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 :: timestamp) - date_trunc(''day'',v_event_date :: timestamp) -- offset + date_trunc('day',v_week_date :: timestamp) - date_trunc('day',v_event_date :: timestamp) -- offset ); v_last_date_done := v_week_date; - else if date_trunc(''day'',v_week_date) > date_trunc(''day'',v_stop_date) + else if date_trunc('day',v_week_date) > date_trunc('day',v_stop_date) then -- Gone too far exit; @@ -228,13 +196,13 @@ end loop; -- Now move to next week with repeats. - v_current_date := (v_current_date :: timestamp + to_interval(7 * v_n_intervals,''days'')) :: timestamptz; + 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 + date_trunc('day',v_current_date ::timestamp) - date_trunc('day',v_event_date ::timestamp) -- offset ); v_last_date_done := v_current_date; end if; @@ -245,19 +213,16 @@ where recurrence_id = recurrence_row.recurrence_id; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; create or replace function acs_event__recurrence_timespan_edit ( - integer, - timestamptz, - timestamptz, - boolean -) returns integer as ' + p_event_id integer, + p_start_date timestamptz, + p_end_date timestamptz, + p_edit_past_events_p boolean +) returns integer as $$ DECLARE - p_event_id alias for $1; - p_start_date alias for $2; - p_end_date alias for $3; - p_edit_past_events_p alias for $4; v_timespan RECORD; v_one_start_date timestamptz; v_one_end_date timestamptz; @@ -281,13 +246,14 @@ from acs_events where recurrence_id = (select recurrence_id from acs_events where event_id = p_event_id))) - and (p_edit_past_events_p = ''t'' or start_date >= v_one_start_date) + and (p_edit_past_events_p = 't' or start_date >= v_one_start_date) LOOP PERFORM time_interval__edit(v_timespan.interval_id, - (to_char(v_timespan.start_date,''yyyy-mm-dd'') || '' '' || to_char(p_start_date,''hh24:mi:ss'')) :: timestamptz, - (to_char(v_timespan.end_date,''yyyy-mm-dd'') || '' '' || to_char(p_end_date,''hh24:mi:ss'')) :: timestamptz); + (to_char(v_timespan.start_date,'yyyy-mm-dd') || ' ' || to_char(p_start_date,'hh24:mi:ss')) :: timestamptz, + (to_char(v_timespan.end_date,'yyyy-mm-dd') || ' ' || to_char(p_end_date,'hh24:mi:ss')) :: timestamptz); END LOOP; return p_event_id; END; -' language 'plpgsql'; + +$$ LANGUAGE plpgsql;