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 -r1.2 -r1.3 --- openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d1-0.6d2.sql 15 May 2007 20:14:15 -0000 1.2 +++ openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d1-0.6d2.sql 30 Mar 2013 13:00:30 -0000 1.3 @@ -1,50 +1,18 @@ -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; @@ -66,7 +34,7 @@ rec_execute record; v_new_current_date timestamptz; v_offset_notice interval; -begin +BEGIN -- Get event parameters select * into event_row @@ -82,7 +50,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; @@ -119,9 +87,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; @@ -130,84 +98,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) - date_trunc(''day'',v_event_date) -- offset + 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) + else if date_trunc('day',v_week_date) > date_trunc('day',v_stop_date) then -- Gone too far exit; @@ -220,13 +188,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; @@ -237,49 +205,27 @@ where recurrence_id = recurrence_row.recurrence_id; return 0; -end;' language 'plpgsql'; +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 + + +-- added +select define_function_args('time_interval__copy','interval_id,offset;0'); + +-- +-- procedure time_interval__copy/2 +-- +CREATE OR REPLACE FUNCTION time_interval__copy( + copy__interval_id integer, + copy__offset interval -- default 0 + +) RETURNS integer AS $$ +-- time_intervals.interval_id%TYPE +DECLARE interval_row time_intervals%ROWTYPE; v_foo timestamptz; -begin +BEGIN select * into interval_row from time_intervals where interval_id = copy__interval_id; @@ -289,38 +235,33 @@ (interval_row.end_date ::timestamp + copy__offset) :: timestamptz ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Allow editing only future recurrences create or replace function acs_event__recurrence_timespan_edit ( - integer, - timestamptz, - timestamptz -) returns integer as ' + p_event_id integer, + p_start_date timestamptz, + p_end_date timestamptz +) returns integer as $$ DECLARE - p_event_id alias for $1; - p_start_date alias for $2; - p_end_date alias for $3; BEGIN return acs_event__recurrence_timespan_edit ( p_event_id, p_start_date, p_end_date, - ''t''); -END;' language 'plpgsql'; + 't'); +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; @@ -335,7 +276,7 @@ where time_intervals.interval_id = timespans.interval_id and timespans.timespan_id = acs_events.timespan_id and event_id=p_event_id; -raise notice ''DAVEB RECURRENCE edit_past_events_p = % start date = %'',p_edit_past_events_p,p_start_date; +raise notice 'DAVEB RECURRENCE edit_past_events_p = % start date = %',p_edit_past_events_p,p_start_date; FOR v_timespan in select * from time_intervals @@ -345,7 +286,7 @@ 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, v_timespan.start_date + (p_start_date - v_one_start_date), @@ -354,4 +295,5 @@ return p_event_id; END; -' language 'plpgsql'; + +$$ LANGUAGE plpgsql;