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 -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;
