Index: openacs-4/packages/acs-events/sql/oracle/acs-events-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/oracle/acs-events-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/oracle/acs-events-create.sql 13 Jul 2001 02:00:30 -0000 1.1 @@ -0,0 +1,984 @@ +-- packages/acs-events/sql/acs-events-create.sql +-- +-- @author smeeks@arsdigita.com +-- @creation-date 2000-11-16 +-- @cvs-id $Id: acs-events-create.sql,v 1.1 2001/07/13 02:00:30 jowells Exp $ + +-- Create the objects and packages for the ACS Events service + +@@activity-create +@@timespan-create +@@recurrence-create + +-- Sequence for event tables that are not subclasses of acs_object +create sequence acs_events_seq start with 1; + +-- The event object +begin + acs_object_type.create_type ( + supertype => 'acs_object', + object_type => 'acs_event', + pretty_name => 'ACS Event', + pretty_plural => 'ACS Events', + table_name => 'ACS_EVENTS', + id_column => 'EVENT_ID' + ); +end; +/ +show errors + +-- Event attributes +declare + attr_id acs_attributes.attribute_id%TYPE; +begin + attr_id := acs_attribute.create_attribute ( + object_type => 'acs_event', + attribute_name => 'timespan_id', + pretty_name => 'Timespan', + pretty_plural => 'Timespans', + datatype => 'integer' + ); + attr_id := acs_attribute.create_attribute ( + object_type => 'acs_event', + attribute_name => 'activity_id', + pretty_name => 'Activity', + pretty_plural => 'Activities', + datatype => 'integer' + ); + attr_id := acs_attribute.create_attribute ( + object_type => 'acs_event', + attribute_name => 'recurrence_id', + pretty_name => 'Recurrence', + pretty_plural => 'Recurrences', + datatype => 'integer' + ); + attr_id := acs_attribute.create_attribute ( + object_type => 'acs_event', + attribute_name => 'name', + pretty_name => 'Name', + pretty_plural => 'Names', + datatype => 'string' + ); + attr_id := acs_attribute.create_attribute ( + object_type => 'acs_event', + attribute_name => 'description', + pretty_name => 'Description', + pretty_plural => 'Descriptions', + datatype => 'string' + ); + attr_id := acs_attribute.create_attribute ( + object_type => 'acs_event', + attribute_name => 'html_p', + pretty_name => 'HTML?', + pretty_plural => '', + datatype => 'string' + ); + attr_id := acs_attribute.create_attribute ( + object_type => 'acs_event', + attribute_name => 'related_link_url', + pretty_name => 'Related Link URL', + pretty_plural => 'Related Link URLs ', + datatype => 'string' + ); + attr_id := acs_attribute.create_attribute ( + object_type => 'acs_event', + attribute_name => 'related_link_text', + pretty_name => 'Related Link Text', + pretty_plural => 'Related Link Texts', + datatype => 'string' + ); + attr_id := acs_attribute.create_attribute ( + object_type => 'acs_event', + attribute_name => 'redirect_to_rel_link_p', + pretty_name => 'Redirect to Related Link?', + pretty_plural => '', + datatype => 'string' + ); +end; +/ +show errors + +-- Events table + +create table acs_events ( + event_id integer + constraint acs_events_fk references acs_objects(object_id) on delete cascade + constraint acs_events_pk primary key, + -- + -- Need additional columns for attributes not inherited from activity, e.g. + -- activity.name = "Bootcamp" and event.name = "December Bootcamp" + -- + -- The Event API supports methods to retrieve name/description from + -- either the event (if defined) or the underlying activity (if not defined) + -- + -- acs_event.get_name() + -- acs_event.get_description() + -- acs_event.get_html_p() + -- + name varchar2(255), + description varchar2(4000), + -- is the event description written in html + html_p char(1) + constraint acs_events_html_p_ck check(html_p in ('t','f')), + -- + -- The following three columns encapsulate the remaining attributes of an Event: + -- the activity that takes place during the event, its timespan (a collection of time + -- intervals during which the event occurs), and an optional recurrence specification + -- that identifies how events repeat in time. + -- + activity_id integer + constraint acs_events_activity_id_fk + references acs_activities on delete set null, + -- Can't reference timespans since it doesn't have a primary key + -- When referencing, need to call timespan.exists_p in case timespan + -- was deleted out from under event. + timespan_id integer, + recurrence_id integer + constraint acs_events_recurrence_id_fk + references recurrences, + -- a link which points to a page related to the event + -- this could be either additional detail or a substitution + -- for the link in some application view, e.g. drill-down from + -- calendar. + -- + related_link_url varchar(4000), + related_link_text varchar(4000), + -- + -- Interpretation of this column is application dependent, but it is + -- suggested that if this is 't', then related_link_{url|text} should be + -- used as the link in summary views in an application. Otherwise, + -- related_link_{url|text} should be available in a detail view + -- of the event. + -- + redirect_to_rel_link_p char(1) + constraint acs_events_rdrct2rel_lnk_p_ck + check(redirect_to_rel_link_p in ('t','f')) +); + +-- This is important to prevent locking on update of master table. +-- See http://www.arsdigita.com/bboard/q-and-a-fetch-msg.tcl?msg_id=000KOh + +create index acs_events_activity_id_ids on acs_events(activity_id); + +-- This is useful for looking up instances of an event + +create index acs_events_recurrence_id_idx on acs_events(recurrence_id); + +comment on table acs_events is ' + A relationship between a time span and an activity. +'; + +comment on column acs_events.name is ' + The name of the event. +'; + +comment on column acs_events.description is ' + The description of the event. +'; + +comment on column acs_events.timespan_id is ' + The time span associated with this event. +'; + +comment on column acs_events.activity_id is ' + The activity associated with this event. +'; + +comment on column acs_events.recurrence_id is ' + A description of how this event recurs. If null, then this event does + not recur. +'; + +-- A table to create associations between events and parties + +create table acs_event_party_map ( + event_id integer + constraint acs_evnt_party_map_evnt_id_fk + references acs_events on delete cascade, + party_id integer + constraint acs_evnt_party_map_party_id_fk + references parties on delete cascade, + constraint acs_evnt_party_map_pk primary key(event_id, party_id) +); + +comment on table acs_event_party_map is ' + Maps a many-to-many relationship between events and parties. +'; + +-- ACS Event Views + +-- This view makes the temporal information easier to access + +create or replace view acs_events_dates as +select e.*, + start_date, + end_date +from acs_events e, + timespans s, + time_intervals t +where e.timespan_id = s.timespan_id +and s.interval_id = t.interval_id; + +comment on table acs_events_dates is ' + This view produces a separate row for each time interval in the timespan + associated with an event. +'; + +-- This view provides an alternative to the get_name and get_description +-- functions + +create or replace view acs_events_activities as +select event_id, + nvl(e.name, a.name) as name, + nvl(e.description, a.description) as description, + nvl(e.html_p, a.html_p) as html_p, + e.activity_id, + timespan_id, + recurrence_id +from acs_events e, + acs_activities a +where e.activity_id = a.activity_id; + +comment on table acs_events_activities is ' + This view pulls the event name and description from the underlying + activity if necessary. +'; + +-- These views should make it easier to find recurrences that +-- need to be populated further, e.g. +-- +-- select recurrence_id +-- from partially_populated_events p, acs_event_party_map m +-- where db_populated_until < :current_date +-- and p.event_id = m.event_id +-- and party_id = :party_id +-- group by recurrence_id +-- +create or replace view partially_populated_event_ids as +select min(event_id) as event_id, + db_populated_until +from acs_events e, + recurrences r +where e.recurrence_id = r.recurrence_id +and (recur_until > db_populated_until or recur_until is null) +group by r.recurrence_id, db_populated_until; + +comment on table partially_populated_event_ids is ' + This view returns the first event_id and db_populated_until column + for any recurrences that have not been completely populated. +'; + +create or replace view partially_populated_events as +select e.event_id, + timespan_id, + activity_id, + recurrence_id, + db_populated_until +from acs_events e, + partially_populated_event_ids p +where e.event_id = p.event_id; + +comment on table partially_populated_events is ' + This view returns information about recurring events that have not been + completely populated (such as indefinitely recurring events.) +'; + + +-- ACS Event API +-- +-- Quick reference for the API supported for the Event object. Note that every procedure +-- takes event_id as the first argument, we're just leave it out for compactness. +-- +-- new (...) +-- delete () +-- +-- get_name () +-- get_description () +-- +-- timespan_set (timespan_id) +-- activity_set (activity_id) +-- +-- party_map (party_id) +-- party_unmap (party_id) +-- +-- insert_instances (cutoff_date) +-- +-- delete_all () +-- delete_all (recurrence_id) +-- +-- shift (start_offset, end_offset) +-- shift_all (start_offset, end_offset) +-- shift_all (recurrence_id, start_offset, end_offset) +-- +-- recurs_p () + + +create or replace package acs_event +as + function new ( + -- Creates a new event (20.10.10) + -- @author W. Scott Meeks + -- @param event_id optional id to use for new event + -- @param name optional Name of the new event + -- @param description optional Description of the new event + -- @param timespan_id optional initial time interval set + -- @param activity_id optional initial activity + -- @param recurrence_id optional id of recurrence information + -- @param object_type 'acs_event' + -- @param creation_date default sysdate + -- @param creation_user acs_object param + -- @param creation_ip acs_object param + -- @param context_id acs_object param + -- @return The id of the new event. + -- + event_id in acs_events.event_id%TYPE default null, + name in acs_events.name%TYPE default null, + description in acs_events.description%TYPE default null, + timespan_id in acs_events.timespan_id%TYPE default null, + activity_id in acs_events.activity_id%TYPE default null, + recurrence_id in acs_events.recurrence_id%TYPE default null, + object_type in acs_object_types.object_type%TYPE default 'acs_event', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + context_id in acs_objects.context_id%TYPE default null + ) return acs_events.event_id%TYPE; + + procedure delete ( + -- Deletes an event (20.10.40) + -- Also deletes party mappings (via on delete cascade). + -- If this is the last instance of a recurring event, the recurrence + -- info is deleted as well + -- @author W. Scott Meeks + -- @param event_id id of event to delete + -- + event_id in acs_events.event_id%TYPE + ); + + procedure delete_all ( + -- Deletes all instances of an event. + -- @author W. Scott Meeks + -- @param event_id All events with the same recurrence_id as this one will be deleted. + -- + event_id in acs_events.event_id%TYPE + ); + + procedure delete_all_recurrences ( + -- Deletes all instances of an event. + -- @author W. Scott Meeks + -- @param recurrence_id All events with this recurrence_id will be deleted. + -- + recurrence_id in recurrences.recurrence_id%TYPE default null + ); + + function get_name ( + -- Returns the name or the name of the activity associated with the event if + -- name is null. + -- @author W. Scott Meeks + -- @param event_id id of event to get name for + -- + event_id in acs_events.event_id%TYPE + ) return acs_events.name%TYPE; + + function get_description ( + -- Returns the description or the description of the activity associated + -- with the event if description is null. + -- @author W. Scott Meeks + -- @param event_id id of event to get description for + -- + event_id in acs_events.event_id%TYPE + ) return acs_events.description%TYPE; + + function get_html_p ( + -- Returns html_p or html_p of the activity associated with the event if + -- html_p is null. + -- @author W. Scott Meeks + -- @param event_id id of event to get html_p for + -- + event_id in acs_events.event_id%TYPE + ) return acs_events.html_p%TYPE; + + procedure timespan_set ( + -- Sets the time span for an event (20.10.15) + -- @author W. Scott Meeks + -- @param event_id id of event to update + -- @param timespan_id new time interval set + -- + event_id in acs_events.event_id%TYPE, + timespan_id in timespans.timespan_id%TYPE + ); + + procedure activity_set ( + -- Sets the activity for an event (20.10.20) + -- @author W. Scott Meeks + -- @param event_id id of event to update + -- @param activity_id new activity + -- + event_id in acs_events.event_id%TYPE, + activity_id in acs_activities.activity_id%TYPE + ); + + procedure party_map ( + -- Adds a party mapping to an event (20.10.30) + -- @author W. Scott Meeks + -- @param event_id event to add mapping to + -- @param party_id party to add mapping for + -- + event_id in acs_events.event_id%TYPE, + party_id in parties.party_id%TYPE + ); + + procedure party_unmap ( + -- Deletes a party mapping from an event (20.10.30) + -- @author W. Scott Meeks + -- @param event_id id of event to delete mapping from + -- @param party_id id of party to delete mapping for + -- + event_id in acs_events.event_id%TYPE, + party_id in parties.party_id%TYPE + ); + + function recurs_p ( + -- Returns 't' if event recurs, 'f' otherwise (20.50.40) + -- @author W. Scott Meeks + -- @param event_id id of event to check + -- @return 't' or 'f' + -- + event_id in acs_events.event_id%TYPE + ) return char; + + function instances_exist_p ( + -- Returns 't' if events with the given recurrence_id exist, 'f' otherwise + -- @author W. Scott Meeks + -- @param recurrence_id id of recurrence to check + -- @return 't' or 'f' + -- + recurrence_id in acs_events.recurrence_id%TYPE + ) return char; + + procedure 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. + -- + -- @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 optional If provided, determines how far out to + -- prepopulate the DB. If not provided, then + -- defaults to sysdate plus the value of the + -- EventFutureLimit site parameter. + event_id in acs_events.event_id%TYPE, + cutoff_date in date default null + ); + + procedure shift ( + -- Shifts the timespan of an event by the given offsets. + -- @author W. Scott Meeks + -- @param event_id Event to shift. + -- @param start_offset optional If provided, adds this number to the + -- start_dates of the timespan of the event. + -- No effect on any null start_date. + -- @param end_offset optional If provided, adds this number to the + -- end_dates of the timespan of the event. + -- No effect on any null end_date. + -- + event_id in acs_events.event_id%TYPE default null, + start_offset in number default 0, + end_offset in number default 0 + ); + + procedure shift_all ( + -- Shifts the timespan of all instances of a recurring event + -- by the given offsets. + -- @author W. Scott Meeks + -- @param event_id All events with the same + -- recurrence_id as this one will be shifted. + -- @param start_offset optional If provided, adds this number to the + -- start_dates of the timespan of the event + -- instances. No effect on any null start_date. + -- @param end_offset optional If provided, adds this number to the + -- end_dates of the timespan of the event + -- instances. No effect on any null end_date. + -- + event_id in acs_events.event_id%TYPE default null, + start_offset in number default 0, + end_offset in number default 0 + ); + + procedure shift_all ( + -- Same as above but invoked using recurrence Id + recurrence_id in recurrences.recurrence_id%TYPE default null, + start_offset in number default 0, + end_offset in number default 0 + ); + +end acs_event; +/ +show errors + +create or replace package body acs_event +as + function new ( + event_id in acs_events.event_id%TYPE default null, + name in acs_events.name%TYPE default null, + description in acs_events.description%TYPE default null, + timespan_id in acs_events.timespan_id%TYPE default null, + activity_id in acs_events.activity_id%TYPE default null, + recurrence_id in acs_events.recurrence_id%TYPE default null, + object_type in acs_object_types.object_type%TYPE default 'acs_event', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + context_id in acs_objects.context_id%TYPE default null + ) return acs_events.event_id%TYPE + is + new_event_id acs_events.event_id%TYPE; + begin + new_event_id := acs_object.new( + object_id => event_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into acs_events + (event_id, name, description, activity_id, timespan_id, recurrence_id) + values + (new_event_id, name, description, activity_id, timespan_id, recurrence_id); + + return new_event_id; + end new; + + procedure delete ( + event_id in acs_events.event_id%TYPE + ) + is + recurrence_id acs_events.recurrence_id%TYPE; + begin + select recurrence_id into recurrence_id + from acs_events + where event_id = acs_event.delete.event_id; + + -- acs_events and acs_event_party_map deleted via on delete cascade + acs_object.delete(event_id); + + -- Check for no more instances and delete recurrence if exists + if instances_exist_p(recurrence_id) = 'f' then + recurrence.delete(recurrence_id); + end if; + end delete; + + procedure delete_all ( + event_id in acs_events.event_id%TYPE + ) + is + recurrence_id acs_events.recurrence_id%TYPE; + begin + + select recurrence_id into recurrence_id + from acs_events + where event_id = delete_all.event_id; + + delete_all_recurrences(recurrence_id); + end delete_all; + + procedure delete_all_recurrences ( + recurrence_id in recurrences.recurrence_id%TYPE default null + ) + is + cursor event_id_cursor is + select event_id + from acs_events + where recurrence_id = delete_all_recurrences.recurrence_id; + event_id event_id_cursor%ROWTYPE; + begin + if recurrence_id is not null then + for event_id in event_id_cursor loop + acs_event.delete(event_id.event_id); + end loop; + end if; + end delete_all_recurrences; + + -- Equivalent functionality to get_name and get_description provided by + -- acs_event_activity view + + function get_name ( + event_id in acs_events.event_id%TYPE + ) return acs_events.name%TYPE + is + name acs_events.name%TYPE; + begin + select nvl(e.name, a.name) into name + from acs_events e, + acs_activities a + where event_id = get_name.event_id + and e.activity_id = a.activity_id(+); + + return name; + end get_name; + + function get_description ( + event_id in acs_events.event_id%TYPE + ) return acs_events.description%TYPE + is + description acs_events.description%TYPE; + begin + select nvl(e.description, a.description) into description + from acs_events e, acs_activities a + where event_id = get_description.event_id + and e.activity_id = a.activity_id(+); + + return description; + end get_description; + + function get_html_p ( + event_id in acs_events.event_id%TYPE + ) return acs_events.html_p%TYPE + is + html_p acs_events.html_p%TYPE; + begin + select nvl(e.html_p, a.html_p) into html_p + from acs_events e, acs_activities a + where event_id = get_html_p.event_id + and e.activity_id = a.activity_id(+); + + return html_p; + end get_html_p; + + procedure timespan_set ( + event_id in acs_events.event_id%TYPE, + timespan_id in timespans.timespan_id%TYPE + ) + is + begin + update acs_events + set timespan_id = timespan_set.timespan_id + where event_id = timespan_set.event_id; + end timespan_set; + + procedure activity_set ( + event_id in acs_events.event_id%TYPE, + activity_id in acs_activities.activity_id%TYPE + ) + as + begin + update acs_events + set activity_id = activity_set.activity_id + where event_id = activity_set.event_id; + end activity_set; + + procedure party_map ( + event_id in acs_events.event_id%TYPE, + party_id in parties.party_id%TYPE + ) + is + begin + insert into acs_event_party_map + (event_id, party_id) + values + (event_id, party_id); + end party_map; + + procedure party_unmap ( + event_id in acs_events.event_id%TYPE, + party_id in parties.party_id%TYPE + ) + is + begin + delete from acs_event_party_map + where event_id = party_unmap.event_id + and party_id = party_unmap.party_id; + end party_unmap; + + function recurs_p ( + event_id in acs_events.event_id%TYPE + ) return char + is + result char; + begin + select decode(recurrence_id, null, 'f', 't') into result + from acs_events + where event_id = recurs_p.event_id; + + return result; + end recurs_p; + + function instances_exist_p ( + recurrence_id in acs_events.recurrence_id%TYPE + ) return char + is + result char; + begin + -- Only need to check if any rows exist. + select count(*) into result + from dual + where exists (select recurrence_id + from acs_events + where recurrence_id = instances_exist_p.recurrence_id); + + if result = 0 then + return 'f'; + else + return 't'; + end if; + end instances_exist_p; + + -- This function is used internally by insert_instances + function get_value ( + parameter_name in apm_parameters.parameter_name%TYPE + ) return apm_parameter_values.attr_value%TYPE + is + package_id apm_packages.package_id%TYPE; + begin + select package_id into package_id + from apm_packages + where package_key = 'acs-events'; + + return apm.get_value(package_id, parameter_name); + end get_value; + + -- This function is used internally by insert_instances + function new_instance ( + event_id in acs_events.event_id%TYPE, + date_offset in integer + ) return acs_events.event_id%TYPE + is + event acs_events%ROWTYPE; + object acs_objects%ROWTYPE; + new_event_id acs_events.event_id%TYPE; + new_timespan_id acs_events.timespan_id%TYPE; + begin + select * into event + from acs_events + where event_id = new_instance.event_id; + + select * into object + from acs_objects + where object_id = event_id; + + new_timespan_id := timespan.copy(event.timespan_id, date_offset); + + new_event_id := new( + name => event.name, + description => event.description, + timespan_id => new_timespan_id, + activity_id => event.activity_id, + recurrence_id => event.recurrence_id, + creation_user => object.creation_user, + creation_ip => object.creation_ip, + context_id => object.context_id + ); + + return new_event_id; + end new_instance; + + procedure insert_instances ( + event_id in acs_events.event_id%TYPE, + cutoff_date in date default null + ) + is + event acs_events%ROWTYPE; + recurrence recurrences%ROWTYPE; + new_event_id acs_events.event_id%TYPE; + interval_name recurrence_interval_types.interval_name%TYPE; + n_intervals recurrence.every_nth_interval%TYPE; + days_of_week recurrence.days_of_week%TYPE; + last_date_done date; + stop_date date; + start_date date; + event_date date; + diff integer; + current_date date; + v_last_day date; + week_date date; + instance_count integer; + days_length integer; + days_index integer; + day_num integer; + begin + select * into event + from acs_events + where event_id = insert_instances.event_id; + + select * into recurrence + from recurrences + where recurrence_id = event.recurrence_id; + + -- Set cutoff date + -- EventFutureLimit is in years. + if cutoff_date is null then + stop_date := add_months(sysdate, 12 * get_value('EventFutureLimit')); + else + stop_date := cutoff_date; + end if; + + -- Events only populated until max(cutoff_date, recur_until) + -- If recur_until null, then defaults to cutoff_date + if recurrence.recur_until < stop_date then + stop_date := recurrence.recur_until; + end if; + + -- Figure out the date to start from + select trunc(min(start_date)) + into event_date + from acs_events_dates + where event_id = insert_instances.event_id; + + if recurrence.db_populated_until is null then + start_date := event_date; + else + start_date := recurrence.db_populated_until; + end if; + + current_date := start_date; + last_date_done := start_date; + n_intervals := recurrence.every_nth_interval; + + -- Case off of the interval_name to make code easier to read + select interval_name into interval_name + from recurrences r, + recurrence_interval_types t + where recurrence_id = recurrence.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 interval_name = 'week' then + current_date := NEXT_DAY(current_date - 7, 'SUNDAY'); + days_of_week := recurrence.days_of_week; + days_length := LENGTH(days_of_week); + end if; + + -- Check count to prevent runaway in case of error + instance_count := 0; + while instance_count < 10000 and (trunc(last_date_done) <= trunc(stop_date)) + loop + instance_count := instance_count + 1; + + -- Calculate next date based on interval type + if interval_name = 'day' then + current_date := current_date + n_intervals; + elsif interval_name = 'month_by_date' then + current_date := ADD_MONTHS(current_date, n_intervals); + elsif interval_name = 'month_by_day' then + -- Find last day of month before correct month + v_last_day := ADD_MONTHS(LAST_DAY(current_date), n_intervals - 1); + -- Find correct week and go to correct day of week + current_date := NEXT_DAY(v_last_day + (7 * (to_char(current_date, 'W') - 1)), + to_char(current_date, 'DAY')); + elsif interval_name = 'last_of_month' then + -- Find last day of correct month + v_last_day := LAST_DAY(ADD_MONTHS(current_date, n_intervals)); + -- Back up one week and find correct day of week + current_date := NEXT_DAY(v_last_day - 7, to_char(current_date, 'DAY')); + elsif interval_name = 'year' then + current_date := ADD_MONTHS(current_date, 12 * n_intervals); + -- Deal with custom function + elsif interval_name = 'custom' then + execute immediate 'current_date := ' || + recurrence.custom_func || '(' || current_date || ', ' || n_intervals || ');'; + end if; + + -- Check to make sure we're not going past Trunc because dates aren't integral + exit when trunc(current_date) > trunc(stop_date); + + -- Have to handle week specially + if interval_name = 'week' then + -- loop over days_of_week extracting each day number + -- add day number and insert + days_index := 1; + week_date := current_date; + while days_index <= days_length loop + day_num := SUBSTR(days_of_week, days_index, 1); + week_date := current_date + day_num; + if trunc(week_date) > trunc(start_date) and trunc(week_date) <= trunc(stop_date) then + -- This is where we add the event + new_event_id := new_instance( + event_id, + trunc(week_date) - trunc(event_date) + ); + last_date_done := week_date; + elsif trunc(week_date) > trunc(stop_date) then + -- Gone too far + exit; + end if; + days_index := days_index + 2; + end loop; + + -- Now move to next week with repeats. + current_date := current_date + 7 * n_intervals; + else + -- All other interval types + -- This is where we add the event + new_event_id := new_instance( + event_id, + trunc(current_date) - trunc(event_date) + ); + last_date_done := current_date; + end if; + end loop; + + update recurrences + set db_populated_until = last_date_done + where recurrence_id = recurrence.recurrence_id; + + end insert_instances; + + + procedure shift ( + event_id in acs_events.event_id%TYPE default null, + start_offset in number default 0, + end_offset in number default 0 + ) + is + begin + update acs_events_dates + set start_date = start_date + start_offset, + end_date = end_date + end_offset + where event_id = shift.event_id; + end shift; + + procedure shift_all ( + event_id in acs_events.event_id%TYPE default null, + start_offset in number default 0, + end_offset in number default 0 + ) + is + begin + update acs_events_dates + set start_date = start_date + start_offset, + end_date = end_date + end_offset + where recurrence_id = (select recurrence_id + from acs_events + where event_id = shift_all.event_id); + end shift_all; + + procedure shift_all ( + recurrence_id in recurrences.recurrence_id%TYPE default null, + start_offset in number default 0, + end_offset in number default 0 + ) + is + begin + update acs_events_dates + set start_date = start_date + start_offset, + end_date = end_date + end_offset + where recurrence_id = shift_all.recurrence_id; + end shift_all; + +end acs_event; +/ +show errors + Index: openacs-4/packages/acs-events/sql/oracle/acs-events-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/oracle/acs-events-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/oracle/acs-events-drop.sql 13 Jul 2001 02:00:30 -0000 1.1 @@ -0,0 +1,26 @@ +-- packages/acs-events/sql/acs-events-drop.sql +-- +-- $Id: acs-events-drop.sql,v 1.1 2001/07/13 02:00:30 jowells Exp $ + +drop package acs_event; + +drop view partially_populated_events; +drop view partially_populated_event_ids; +drop view acs_events_activities; +drop view acs_events_dates; + +drop table acs_event_party_map; +drop index acs_events_recurrence_id_idx; +drop table acs_events; + +begin + acs_object_type.drop_type ('acs_event'); +end; +/ +show errors + +drop sequence acs_events_seq; + +@@recurrence-drop +@@timespan-drop +@@activity-drop \ No newline at end of file Index: openacs-4/packages/acs-events/sql/oracle/activity-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/oracle/activity-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/oracle/activity-create.sql 13 Jul 2001 02:00:30 -0000 1.1 @@ -0,0 +1,284 @@ +-- packages/acs-events/sql/activity-create.sql +-- +-- @author W. Scott Meeks +-- @author Gary Jin (gjin@arsdigita.com) +-- $Id: activity-create.sql,v 1.1 2001/07/13 02:00:30 jowells Exp $ +-- +-- The activity object + +begin + acs_object_type.create_type ( + supertype => 'acs_object', + object_type => 'acs_activity', + pretty_name => 'Activity', + pretty_plural => 'Activities', + table_name => 'ACS_ACTIVITIES', + id_column => 'ACTIVITY_ID' + ); +end; +/ +show errors + +declare + attr_id acs_attributes.attribute_id%TYPE; +begin + attr_id := acs_attribute.create_attribute ( + object_type => 'acs_activity', + attribute_name => 'name', + pretty_name => 'Name', + pretty_plural => 'Names', + datatype => 'string' + ); + + attr_id := acs_attribute.create_attribute ( + object_type => 'acs_activity', + attribute_name => 'description', + pretty_name => 'Description', + pretty_plural => 'Descriptions', + datatype => 'string' + ); + + attr_id := acs_attribute.create_attribute ( + object_type => 'acs_activity', + attribute_name => 'html_p', + pretty_name => 'HTML?', + pretty_plural => 'HTML?', + datatype => 'string' + ); +end; +/ +show errors + +-- The activities table + +create table acs_activities ( + activity_id integer + constraint acs_activities_fk + references acs_objects(object_id) + on delete cascade + constraint acs_activities_pk + primary key, + name varchar2(255) not null, + description varchar2(4000), + -- is the activity description written in html? + html_p char(1) + constraint acs_activities_html_p_ck + check(html_p in ('t','f')) +); + +comment on table acs_activities is ' + Represents what happens during an event +'; + +create table acs_activity_object_map ( + activity_id integer + constraint acs_act_obj_mp_activity_id_fk + references acs_activities on delete cascade, + object_id integer + constraint acs_act_obj_mp_object_id_fk + references acs_objects(object_id) on delete cascade, + constraint acs_act_obj_mp_pk + primary key(activity_id, object_id) +); + +comment on table acs_activity_object_map is ' + Maps between an activity and multiple ACS objects. +'; + +create or replace package acs_activity +as + function new ( + -- Create a new activity + -- @author W. Scott Meeks + -- @param activity_id optional id to use for new activity + -- @param name Name of the activity + -- @param description optional description of the activity + -- @param object_type 'acs_activity' + -- @param creation_date default sysdate + -- @param creation_user acs_object param + -- @param creation_ip acs_object param + -- @param context_id acs_object param + -- @return The id of the new activity. + -- + activity_id in acs_activities.activity_id%TYPE default null, + name in acs_activities.name%TYPE, + description in acs_activities.description%TYPE default null, + html_p in acs_activities.html_p%TYPE default 'f', + object_type in acs_object_types.object_type%TYPE default 'acs_activity', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + context_id in acs_objects.context_id%TYPE default null + ) return acs_activities.activity_id%TYPE; + + function name ( + -- name method + -- @author gjin@arsdigita.com + -- @param activity_id + -- + activity_id in acs_activities.activity_id%TYPE + + ) return acs_activities.name%TYPE; + + procedure delete ( + -- Deletes an activity + -- @author W. Scott Meeks + -- @param activity_id id of activity to delete + activity_id in acs_activities.activity_id%TYPE + ); + + + -- NOTE: can't use update + + procedure edit ( + -- Update the name or description of an activity + -- @author W. Scott Meeks + -- @param activity_id activity to update + -- @param name optional New name for this activity + -- @param description optional New description for this activity + -- @param html_p optional New value of html_p for this activity + activity_id in acs_activities.activity_id%TYPE, + name in acs_activities.name%TYPE default null, + description in acs_activities.description%TYPE default null, + html_p in acs_activities.html_p%TYPE default null + ); + + procedure object_map ( + -- Adds an object mapping to an activity + -- @author W. Scott Meeks + -- @param activity_id id of activity to add mapping to + -- @param object_id id of object to add mapping for + -- + activity_id in acs_activities.activity_id%TYPE, + object_id in acs_objects.object_id%TYPE + ); + + procedure object_unmap ( + -- Deletes an object mapping from an activity + -- @author W. Scott Meeks + -- @param activity_id activity to delete mapping from + -- @param object_id object to delete mapping for + -- + activity_id in acs_activities.activity_id%TYPE, + object_id in acs_objects.object_id%TYPE + ); + +end acs_activity; +/ +show errors + +create or replace package body acs_activity +as + function new ( + activity_id in acs_activities.activity_id%TYPE default null, + name in acs_activities.name%TYPE, + description in acs_activities.description%TYPE default null, + html_p in acs_activities.html_p%TYPE default 'f', + object_type in acs_object_types.object_type%TYPE default 'acs_activity', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + context_id in acs_objects.context_id%TYPE default null + ) return acs_activities.activity_id%TYPE + is + new_activity_id acs_activities.activity_id%TYPE; + begin + new_activity_id := acs_object.new( + object_id => activity_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into acs_activities + (activity_id, name, description) + values + (new_activity_id, name, description); + + return new_activity_id; + end new; + + + function name ( + -- name method + -- @author gjin@arsdigita.com + -- @param activity_id + -- + activity_id in acs_activities.activity_id%TYPE + + ) return acs_activities.name%TYPE + + is + new_activity_name acs_activities.name%TYPE; + + begin + select name + into new_activity_name + from acs_activities + where activity_id = name.activity_id; + + return new_activity_name; + end; + + + procedure delete ( + activity_id in acs_activities.activity_id%TYPE + ) + is + begin + -- Cascade will cause delete from acs_activities + -- and acs_activity_object_map + + acs_object.delete(activity_id); + end delete; + + -- NOTE: can't use update + + procedure edit ( + activity_id in acs_activities.activity_id%TYPE, + name in acs_activities.name%TYPE default null, + description in acs_activities.description%TYPE default null, + html_p in acs_activities.html_p%TYPE default null + ) + is + begin + update acs_activities + set name = nvl(edit.name, name), + description = nvl(edit.description, description), + html_p = nvl(edit.html_p, html_p) + where activity_id = edit.activity_id; + end edit; + + procedure object_map ( + activity_id in acs_activities.activity_id%TYPE, + object_id in acs_objects.object_id%TYPE + ) + is + begin + insert into acs_activity_object_map + (activity_id, object_id) + values + (activity_id, object_id); + end object_map; + + procedure object_unmap ( + activity_id in acs_activities.activity_id%TYPE, + object_id in acs_objects.object_id%TYPE + ) + is + begin + delete from acs_activity_object_map + where activity_id = object_unmap.activity_id + and object_id = object_unmap.object_id; + end object_unmap; + +end acs_activity; +/ +show errors + + + + + Index: openacs-4/packages/acs-events/sql/oracle/activity-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/oracle/activity-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/oracle/activity-drop.sql 13 Jul 2001 02:00:30 -0000 1.1 @@ -0,0 +1,16 @@ +-- packages/acs-events/sql/activity-drop.sql +-- +-- $Id: activity-drop.sql,v 1.1 2001/07/13 02:00:30 jowells Exp $ + +drop package acs_activity; +drop table acs_activity_object_map; +drop table acs_activities; + +begin + acs_object_type.drop_type ('acs_activity'); +end; +/ +show errors + + + Index: openacs-4/packages/acs-events/sql/oracle/recurrence-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/oracle/recurrence-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/oracle/recurrence-create.sql 13 Jul 2001 02:00:30 -0000 1.1 @@ -0,0 +1,206 @@ +-- packages/acs-events/sql/recurrence-create.sql +-- +-- Support for temporal recurrences +-- +-- $Id: recurrence-create.sql,v 1.1 2001/07/13 02:00:30 jowells Exp $ + +-- These columns describe how an event recurs. The are modeled on the Palm DateBook. +-- The interval_type 'custom' indicates that the PL/SQL function referenced in +-- custom_func should be used to generate the recurrences. + +-- Sequence for recurrence tables + +create sequence recurrence_seq start with 1; + +create table recurrence_interval_types ( + interval_type integer + constraint recurrence_interval_type_pk primary key, + interval_name varchar2(50) not null + constraint rit_interval_name_un unique +); + +set feedback off; + +insert into recurrence_interval_types values (1,'day'); +insert into recurrence_interval_types values (2,'week'); +insert into recurrence_interval_types values (3,'month_by_date'); +insert into recurrence_interval_types values (4,'month_by_day'); +insert into recurrence_interval_types values (5,'last_of_month'); +insert into recurrence_interval_types values (6,'year'); +insert into recurrence_interval_types values (7,'custom'); + +set feedback on; + +create table recurrences ( + recurrence_id integer + constraint recurrences_pk primary key, + -- + -- Indicate the interval type for recurrence (see above) + -- + interval_type constraint recurs_interval_type_fk + references recurrence_interval_types not null, + -- + -- Indicates how many of the given intervals between recurrences. + -- Must be a positive number! + -- + every_nth_interval integer + constraint recurs_every_nth_interval_ck + check(every_nth_interval > 0), + -- + -- If recurring on a weekly basis (interval_type = 'week') + -- indicates which days of the week the event recurs on. + -- This is represented as a space separated list of numbers + -- corresponding to days of the week, where 0 corresponds to + -- Sunday, 1 to Monday, and so on. Null indicates no days are set. + -- So for example, '1' indicates recur on Mondays, '3 5' indicates + -- recur on Wednesday and Friday. + -- + days_of_week varchar2(20), + -- + -- Indicates when this event should stop recurring. Null indicates + -- recur indefinitely. + -- + recur_until date, + -- + -- Recurring events can be only partially populated if fully populating + -- the events would require inserting too many instances. This + -- column indicates up to what date this event has recurred. This + -- allows further instances to be added if the user attempts to view + -- a date beyond db_populated_until. If recur_until is not null, + -- then this column will always be prior to or the same as recur_until. + -- This column will be null until some recurrences have been added. + -- + db_populated_until date, + -- + -- This column holds the name of a PL/SQL function that will be called + -- to generate dates of recurrences if interval_type is 'custom' + -- + custom_func varchar2(255) +); + +-- This is important to prevent locking on update of master table. +-- See http://www.arsdigita.com/bboard/q-and-a-fetch-msg.tcl?msg_id=000KOh +create index recurrences_interval_type_idx on recurrences(interval_type); + +comment on table recurrences is ' + Desribes how an event recurs. +'; + +comment on column recurrences.interval_type is ' + One of day, week, month_by_date, month_by_day, last_of_month, year, custom. +'; + +comment on column recurrences.every_nth_interval is ' + Indicates how many of the given intervals between recurrences. +'; + +comment on column recurrences.days_of_week is ' + For weekly recurrences, stores which days of the week the event recurs on. +'; + +comment on column recurrences.recur_until is ' + Indicates when this event should stop recurring. Null indicates + recur indefinitely. +'; + +comment on column recurrences.db_populated_until is ' + Indicates the date of the last recurrence added. Used to determine if more + recurrences need to be added. +'; + +comment on column recurrences.custom_func is ' + Stores the name of a PL/SQL function that can be called to generate dates + for special recurrences. +'; + +-- Recurrence API +-- +-- Currently supports only new and delete methods. +-- + +create or replace package recurrence +as + function new ( + -- Creates a new recurrence + -- @author W. Scott Meeks + -- @param interval_type Sets interval_type of new recurrence + -- @param every_nth_interval Sets every_nth_interval of new recurrence + -- @param days_of_week optional If provided, sets days_of_week + -- of new recurrence + -- @param recur_until optional If provided, sets recur_until + -- of new recurrence + -- @param custom_func optional If provided, set name of + -- custom recurrence function + -- @return id of new recurrence + -- + interval_type in recurrence_interval_types.interval_name%TYPE, + every_nth_interval in recurrences.every_nth_interval%TYPE, + days_of_week in recurrences.days_of_week%TYPE default null, + recur_until in recurrences.recur_until%TYPE default null, + custom_func in recurrences.custom_func%TYPE default null + ) return recurrences.recurrence_id%TYPE; + + procedure delete ( + -- Deletes the recurrence + -- @author W. Scott Meeks + -- @param recurrence_id id of recurrence to delete + -- + recurrence_id in recurrences.recurrence_id%TYPE + ); + +end recurrence; +/ +show errors + +create or replace package body recurrence +as + function new ( + interval_type in recurrence_interval_types.interval_name%TYPE, + every_nth_interval in recurrences.every_nth_interval%TYPE, + days_of_week in recurrences.days_of_week%TYPE default null, + recur_until in recurrences.recur_until%TYPE default null, + custom_func in recurrences.custom_func%TYPE default null + ) return recurrences.recurrence_id%TYPE + is + recurrence_id recurrences.recurrence_id%TYPE; + interval_type_id recurrence_interval_types.interval_type%TYPE; + begin + select recurrence_seq.nextval into recurrence_id from dual; + + select interval_type + into interval_type_id + from recurrence_interval_types + where interval_name = new.interval_type; + + insert into recurrences + (recurrence_id, + interval_type, + every_nth_interval, + days_of_week, + recur_until, + custom_func) + values + (recurrence_id, + interval_type_id, + every_nth_interval, + days_of_week, + recur_until, + custom_func); + + return recurrence_id; + end new; + + + -- Note: this will fail if there are any events_with this recurrence + procedure delete ( + recurrence_id in recurrences.recurrence_id%TYPE + ) + is + begin + delete from recurrences + where recurrence_id = recurrence.delete.recurrence_id; + end delete; + +end recurrence; +/ +show errors Index: openacs-4/packages/acs-events/sql/oracle/recurrence-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/oracle/recurrence-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/oracle/recurrence-drop.sql 13 Jul 2001 02:00:30 -0000 1.1 @@ -0,0 +1,10 @@ +-- packages/acs-events/sql/recurrence-drop.sql +-- +-- $Id: recurrence-drop.sql,v 1.1 2001/07/13 02:00:30 jowells Exp $ + +drop package recurrence; + +drop table recurrences; +drop table recurrence_interval_types; + +drop sequence recurrence_seq; Index: openacs-4/packages/acs-events/sql/oracle/timespan-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/oracle/timespan-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/oracle/timespan-create.sql 13 Jul 2001 02:00:30 -0000 1.1 @@ -0,0 +1,782 @@ +-- packages/acs-events/sql/timespan-create.sql +-- +-- This script defines the data models for both time_interval and timespan. +-- +-- API: +-- +-- new (start_date, end_date) +-- delete () +-- +-- edit (start_date, end_date) +-- +-- shift (start_offset, end_offset) +-- +-- overlaps_p (interval_id) +-- overlaps_p (start_date, end_date) +-- +-- $Id: timespan-create.sql,v 1.1 2001/07/13 02:00:30 jowells Exp $ + +-- Table for storing time intervals. Note that time intervals can be open on +-- either end. This is represented by a null value for start_date or end_date. +-- Applications can determine how to interpret null values. However, this is +-- the default interpretation used by the overlaps_p functions. A null value +-- for start_date is treated as extending to the beginning of time. A null +-- value for end_date is treated as extending to the end of time. The net effect +-- is that an interval with an open start overlaps any interval whose start +-- is before the end of the interval with the open start. Likewise, an interval +-- with an open end overlaps any interval whose end is after the start of the +-- interval with the open end. + +-- Sequence for timespan tables +create sequence timespan_seq start with 1; + +create table time_intervals ( + interval_id integer + constraint time_intervals_pk + primary key, + start_date date, + end_date date, + constraint time_interval_date_order_ck + check(start_date <= end_date) +); + +comment on table time_intervals is ' + A time interval is represented by two points in time. +'; + +create or replace package time_interval +as + function new ( + -- Creates a new time interval + -- @author W. Scott Meeks + -- @param start_date optional Sets this as start_date of new interval + -- @param end_date optional Sets this as end_date of new interval + -- @return id of new time interval + -- + start_date in time_intervals.start_date%TYPE default null, + end_date in time_intervals.end_date%TYPE default null + ) return time_intervals.interval_id%TYPE; + + procedure delete ( + -- Deletes the given time interval + -- @author W. Scott Meeks + -- @param interval_id id of the interval to delete + -- + interval_id in time_intervals.interval_id%TYPE + ); + + -- NOTE: update is reserved and cannot be used for PL/SQL procedure names + + procedure edit ( + -- Updates the start_date or end_date of an interval + -- @author W. Scott Meeks + -- @param interval_id id of the interval to update + -- @param start_date optional If provided, sets this as the new + -- start_date of the interval. + -- @param end_date optional If provided, sets this as the new + -- start_date of the interval. + -- + interval_id in time_intervals.interval_id%TYPE, + start_date in time_intervals.start_date%TYPE default null, + end_date in time_intervals.end_date%TYPE default null + ); + + procedure shift ( + -- Updates the start_date or end_date of an interval based on offsets of + -- fractional days. + -- @author W. Scott Meeks + -- @param interval_id The interval to update. + -- @param start_offset optional If provided, adds this number to the + -- start_date of the interval. No effect if + -- start_date is null. + -- @param end_offset optional If provided, adds this number to the + -- end_date of the interval. No effect if + -- end_date is null. + -- + interval_id in time_intervals.interval_id%TYPE, + start_offset in number default 0, + end_offset in number default 0 + ); + + function overlaps_p ( + -- Returns 't' if the two intervals overlap, 'f' otherwise. + -- @author W. Scott Meeks + -- @param interval_1_id + -- @param interval_2_id + -- @return 't' or 'f' + -- + interval_1_id in time_intervals.interval_id%TYPE, + interval_2_id in time_intervals.interval_id%TYPE + ) return char; + + function overlaps_p ( + -- Returns 't if the interval bounded by the given start_date or + -- end_date overlaps the given interval, 'f' otherwise. + -- @author W. Scott Meeks + -- @param start_date optional If provided, see if it overlaps + -- the interval. + -- @param end_date optional If provided, see if it overlaps + -- the interval. + -- @return 't' or 'f' + -- + interval_id in time_intervals.interval_id%TYPE, + start_date in time_intervals.start_date%TYPE default null, + end_date in time_intervals.end_date%TYPE default null + ) return char; + + function overlaps_p ( + start_1 in time_intervals.start_date%TYPE, + end_1 in time_intervals.end_date%TYPE, + start_2 in time_intervals.start_date%TYPE, + end_2 in time_intervals.end_date%TYPE + ) return char; + + function eq ( + -- Checks if two intervals are equivalent + -- @author W. Scott Meeks + -- @param interval_1_id First interval + -- @param interval_2_id Second interval + -- @return boolean + -- + interval_1_id in time_intervals.interval_id%TYPE, + interval_2_id in time_intervals.interval_id%TYPE + ) return boolean; + + function copy ( + -- Creates a new copy of a time interval, offset by optional offset + -- @author W. Scott Meeks + -- @param interval_id Interval to copy + -- @param offset optional If provided, interval is + -- offset by this number of days. + -- @return interval_id + -- + interval_id in time_intervals.interval_id%TYPE, + offset in integer default 0 + ) return time_intervals.interval_id%TYPE; + +end time_interval; +/ +show errors + +create or replace package body time_interval +as + function new ( + start_date in time_intervals.start_date%TYPE default null, + end_date in time_intervals.end_date%TYPE default null + ) return time_intervals.interval_id%TYPE + is + interval_id time_intervals.interval_id%TYPE; + begin + select timespan_seq.nextval into interval_id from dual; + + insert into time_intervals + (interval_id, start_date, end_date) + values + (interval_id, start_date, end_date); + + return interval_id; + end new; + + procedure delete ( + interval_id in time_intervals.interval_id%TYPE + ) + is + begin + delete time_intervals + where interval_id = time_interval.delete.interval_id; + end delete; + + procedure edit ( + interval_id in time_intervals.interval_id%TYPE, + start_date in time_intervals.start_date%TYPE default null, + end_date in time_intervals.end_date%TYPE default null + ) + is + begin + -- Null for start_date or end_date means don't change. + if start_date is not null and end_date is not null then + update time_intervals + set start_date = edit.start_date, + end_date = edit.end_date + where interval_id = edit.interval_id; + elsif start_date is not null then + update time_intervals + set start_date = edit.start_date + where interval_id = edit.interval_id; + elsif end_date is not null then + update time_intervals + set end_date = edit.end_date + where interval_id = edit.interval_id; + end if; + end edit; + + procedure shift ( + interval_id in time_intervals.interval_id%TYPE, + start_offset in number default 0, + end_offset in number default 0 + ) + is + begin + update time_intervals + set start_date = start_date + start_offset, + end_date = end_date + end_offset + where interval_id = shift.interval_id; + end shift; + + function overlaps_p ( + interval_1_id in time_intervals.interval_id%TYPE, + interval_2_id in time_intervals.interval_id%TYPE + ) return char + is + start_1 date; + start_2 date; + end_1 date; + end_2 date; + begin + -- Pull out the start and end dates and call the main overlaps_p. + select start_date, + end_date + into start_1, + end_1 + from time_intervals + where interval_id = interval_1_id; + + select start_date, + end_date + into start_2, + end_2 + from time_intervals + where interval_id = interval_2_id; + + return overlaps_p(start_1, end_1, start_2, end_2); + end overlaps_p; + + function overlaps_p ( + interval_id in time_intervals.interval_id%TYPE, + start_date in time_intervals.start_date%TYPE default null, + end_date in time_intervals.end_date%TYPE default null + ) return char + is + interval_start time_intervals.start_date%TYPE; + interval_end time_intervals.end_date%TYPE; + begin + -- Pull out the start and end date and call the main overlaps_p. + select start_date, + end_date + into interval_start, + interval_end + from time_intervals + where interval_id = overlaps_p.interval_id; + + return overlaps_p(interval_start, interval_end, start_date, end_date); + end overlaps_p; + + function overlaps_p ( + start_1 in time_intervals.start_date%TYPE, + end_1 in time_intervals.end_date%TYPE, + start_2 in time_intervals.start_date%TYPE, + end_2 in time_intervals.end_date%TYPE + ) return char + is + begin + if start_1 is null then + -- No overlap if 2nd interval starts after 1st ends + if end_1 < start_2 then + return 'f'; + else + return 't'; + end if; + elsif start_2 is null then + -- No overlap if 2nd interval ends before 1st starts + if end_2 < start_1 then + return 'f'; + else + return 't'; + end if; + -- Okay, both start dates are not null + elsif start_1 <= start_2 then + -- 1st starts before 2nd + if end_1 < start_2 then + -- No overlap if 1st ends before 2nd starts + return 'f'; + else + -- No overlap or at least one null + return 't'; + end if; + else + -- 1st starts after 2nd + if end_2 < start_1 then + -- No overlap if 2nd ends before 1st starts + return 'f'; + else + -- No overlap or at least one null + return 't'; + end if; + end if; + end overlaps_p; + + function eq ( + -- Checks if two intervals are equivalent + interval_1_id in time_intervals.interval_id%TYPE, + interval_2_id in time_intervals.interval_id%TYPE + ) return boolean + is + interval_1 time_intervals%ROWTYPE; + interval_2 time_intervals%ROWTYPE; + begin + select * into interval_1 + from time_intervals + where interval_id = interval_1_id; + + select * into interval_2 + from time_intervals + where interval_id = interval_2_id; + + if interval_1.start_date = interval_2.start_date and + interval_1.end_date = interval_2.end_date then + return true; + else + return false; + end if; + end eq; + + function copy ( + interval_id in time_intervals.interval_id%TYPE, + offset in integer default 0 + ) return time_intervals.interval_id%TYPE + is + interval time_intervals%ROWTYPE; + begin + select * into interval + from time_intervals + where interval_id = copy.interval_id; + + return new(interval.start_date + offset, interval.end_date + offset); + end copy; + +end time_interval; +/ +show errors + +-- Create the timespans table. + +create table timespans ( + -- Can't be primary key because of the one to many relationship with + -- interval_id, but we can declare it not null and index it. + timespan_id integer not null, + interval_id integer + constraint tm_ntrvl_sts_interval_id_fk + references time_intervals on delete cascade +); + +create index timespans_idx on timespans(timespan_id); + +-- This is important to prevent locking on update of master table. +-- See http://www.arsdigita.com/bboard/q-and-a-fetch-msg.tcl?msg_id=000KOh +create index timespans_interval_id_idx on timespans(interval_id); + +comment on table timespans is ' + Establishes a relationship between timespan_id and multiple time + intervals. Represents a range of moments at which an event can occur. +'; + +-- TimeSpan API +-- +-- Quick reference for the API supported for timespans. All procedures take timespan_id +-- as the first argument (not shown explicitly): +-- +-- new (interval_id) +-- new (start_date, end_date) +-- delete () +-- +-- Methods to join additional time intervals with an existing timespan: +-- +-- join (timespan_id) +-- join_interval (interval_id) +-- join (start_date, end_date) +-- +-- interval_delete (interval_id) +-- interval_list () +-- +-- Tests for overlap: +-- +-- overlaps_p (timespan_id) +-- overlaps_p (interval_id) +-- overlaps_p (start_date, end_date) +-- +-- Info: +-- +-- exists_p () +-- multi_interval_p () + +create or replace package timespan +as + function new ( + -- Creates a new timespan (20.20.10) + -- given a time_interval + -- Copies the interval so the caller is responsible for deleting it + interval_id in time_intervals.interval_id%TYPE default null + ) return timespans.timespan_id%TYPE; + + function new ( + -- Creates a new timespan (20.20.10) + -- given a start_date and end_date + start_date in time_intervals.start_date%TYPE default null, + end_date in time_intervals.end_date%TYPE default null + ) return timespans.timespan_id%TYPE; + + procedure delete ( + -- Deletes the timespan and any contained intervals + -- @author W. Scott Meeks + -- @param timespan_id id of timespan to delete + timespan_id in timespans.timespan_id%TYPE + ); + + -- Join a new timespan or time interval to an existing timespan + + procedure join ( + -- timespan_1_id is modified, timespan_2_id is not + timespan_1_id in timespans.timespan_id%TYPE, + timespan_2_id in timespans.timespan_id%TYPE + ); + + -- Unfortunately, Oracle can't distinguish the signature of this function + -- with the previous because the args have the same underlying types + -- + procedure join_interval ( + -- interval is copied to the timespan + timespan_id in timespans.timespan_id%TYPE, + interval_id in time_intervals.interval_id%TYPE, + copy_p in boolean default true + ); + + procedure join ( + timespan_id in timespans.timespan_id%TYPE, + start_date in time_intervals.start_date%TYPE default null, + end_date in time_intervals.end_date%TYPE default null + ); + + + procedure interval_delete ( + -- Deletes an interval from the given timespan + -- @author W. Scott Meeks + -- @param timespan_id timespan to delete from + -- @param interval_id delete this interval from the set + -- + timespan_id in timespans.timespan_id%TYPE, + interval_id in time_intervals.interval_id%TYPE + ); + + -- Information + + function exists_p ( + -- If its contained intervals are all deleted, then a timespan will + -- automatically be deleted. This checks a timespan_id to make sure it's + -- still valid. + -- @author W. Scott Meeks + -- @param timespan_id id of timespan to check + -- @return 't' or 'f' + timespan_id in timespans.timespan_id%TYPE + ) return char; + + function multi_interval_p ( + -- Returns 't' if timespan contains more than one interval, + -- 'f' otherwise ( + -- @author W. Scott Meeks + -- @param timespan_id id of set to check + -- @return 't' or 'f' + timespan_id in timespans.timespan_id%TYPE + ) return char; + + + function overlaps_p ( + -- Checks to see if a given interval overlaps any of the intervals + -- in the given timespan. + timespan_1_id in timespans.timespan_id%TYPE, + timespan_2_id in timespans.timespan_id%TYPE + ) return char; + + -- Unfortunately, Oracle can't distinguish the signature of this function + -- with the previous because the args have the same underlying types + -- + function overlaps_interval_p ( + timespan_id in timespans.timespan_id%TYPE, + interval_id in time_intervals.interval_id%TYPE + ) return char; + + function overlaps_p ( + timespan_id in timespans.timespan_id%TYPE, + start_date in time_intervals.start_date%TYPE default null, + end_date in time_intervals.end_date%TYPE default null + ) return char; + + function copy ( + -- Creates a new copy of a timespan, offset by optional offset + -- @author W. Scott Meeks + -- @param timespan_id Timespan to copy + -- @param offset optional If provided, all dates in timespan + -- are offset by this number of days. + -- @return timespan_id + -- + timespan_id in timespans.timespan_id%TYPE, + offset in integer default 0 + ) return timespans.timespan_id%TYPE; + +end timespan; +/ +show errors + +create or replace package body timespan +as + function new ( + interval_id in time_intervals.interval_id%TYPE + ) return timespans.timespan_id%TYPE + is + timespan_id timespans.timespan_id%TYPE; + new_interval_id time_intervals.interval_id%TYPE; + begin + select timespan_seq.nextval into timespan_id from dual; + + new_interval_id := time_interval.copy(interval_id); + + insert into timespans + (timespan_id, interval_id) + values + (timespan_id, new_interval_id); + + return timespan_id; + end new; + + function new ( + start_date in time_intervals.start_date%TYPE default null, + end_date in time_intervals.end_date%TYPE default null + ) return timespans.timespan_id%TYPE + is + begin + return new(time_interval.new(start_date, end_date)); + end new; + + procedure delete ( + timespan_id in timespans.timespan_id%TYPE + ) + is + begin + -- Delete intervals, corresponding timespan entries deleted by + -- cascading constraints + delete from time_intervals + where interval_id in (select interval_id + from timespans + where timespan_id = timespan.delete.timespan_id); + end delete; + + -- + -- Join a new timespan or time interval to an existing timespan + -- + procedure join ( + timespan_1_id in timespans.timespan_id%TYPE, + timespan_2_id in timespans.timespan_id%TYPE + ) + is + cursor timespan_cursor is + select * + from timespans + where timespan_id = timespan_2_id; + timespan_val timespan_cursor%ROWTYPE; + begin + -- Loop over intervals in 2nd timespan, join with 1st. + for timespan_val in timespan_cursor + loop + join_interval(timespan_1_id, timespan_val.interval_id); + end loop; + end join; + + -- Optional argument to copy interval + procedure join_interval ( + timespan_id in timespans.timespan_id%TYPE, + interval_id in time_intervals.interval_id%TYPE, + copy_p in boolean default true + ) + is + new_interval_id time_intervals.interval_id%TYPE; + begin + if copy_p then + new_interval_id := time_interval.copy(interval_id); + else + new_interval_id := interval_id; + end if; + + insert into timespans + (timespan_id, interval_id) + values + (timespan_id, new_interval_id); + end join_interval; + + procedure join ( + timespan_id in timespans.timespan_id%TYPE, + start_date in time_intervals.start_date%TYPE default null, + end_date in time_intervals.end_date%TYPE default null + ) + is + begin + join_interval( + timespan_id => timespan_id, + interval_id => time_interval.new(start_date, end_date), + copy_p => false + ); + end join; + + procedure interval_delete ( + timespan_id in timespans.timespan_id%TYPE, + interval_id in time_intervals.interval_id%TYPE + ) + is + begin + delete from timespans + where timespan_id = interval_delete.timespan_id + and interval_id = interval_delete.interval_id; + end interval_delete; + + -- Information + + function exists_p ( + timespan_id in timespans.timespan_id%TYPE + ) return char + is + result integer; + begin + -- Only need to check if any rows exist. + select count(*) + into result + from dual + where exists (select timespan_id + from timespans + where timespan_id = exists_p.timespan_id); + if result = 0 then + return 'f'; + else + return 't'; + end if; + end exists_p; + + function multi_interval_p ( + timespan_id in timespans.timespan_id%TYPE + ) return char + is + result char; + begin + -- 'f' if 0 or 1 intervals, 't' otherwise + select decode(count(timespan_id), 0, 'f', 1, 'f', 't') + into result + from timespans + where timespan_id = multi_interval_p.timespan_id; + + return result; + end multi_interval_p; + + + function overlaps_p ( + -- Checks to see if any intervals in a timespan overlap any of the intervals + -- in the second timespan. + timespan_1_id in timespans.timespan_id%TYPE, + timespan_2_id in timespans.timespan_id%TYPE + ) return char + is + result char; + cursor timespan_cursor is + select * + from timespans + where timespan_id = timespan_2_id; + timespan_val timespan_cursor%ROWTYPE; + begin + -- Loop over 2nd timespan, checking each interval against 1st + for timespan_val in timespan_cursor + loop + result := overlaps_interval_p + (timespan_1_id, + timespan_val.interval_id + ); + if result = 't' then + return 't'; + end if; + end loop; + return 'f'; + end overlaps_p; + + function overlaps_interval_p ( + timespan_id in timespans.timespan_id%TYPE, + interval_id in time_intervals.interval_id%TYPE default null + ) return char + is + start_date date; + end_date date; + begin + select start_date, end_date + into start_date, end_date + from time_intervals + where interval_id = overlaps_interval_p.interval_id; + + return overlaps_p(timespan_id, start_date, end_date); + end overlaps_interval_p; + + function overlaps_p ( + timespan_id in timespans.timespan_id%TYPE, + start_date in time_intervals.start_date%TYPE default null, + end_date in time_intervals.end_date%TYPE default null + ) return char + is + result char; + cursor timespan_cursor is + select * + from timespans + where timespan_id = overlaps_p.timespan_id; + timespan_val timespan_cursor%ROWTYPE; + begin + -- Loop over each interval in timespan, checking against dates. + for timespan_val in timespan_cursor + loop + result := time_interval.overlaps_p( + timespan_val.interval_id, + start_date, + end_date + ); + + if result = 't' then + return 't'; + end if; + end loop; + return 'f'; + end overlaps_p; + + function copy ( + timespan_id in timespans.timespan_id%TYPE, + offset in integer default 0 + ) return timespans.timespan_id%TYPE + is + cursor timespan_cursor is + select * + from timespans + where timespan_id = copy.timespan_id; + timespan_val timespan_cursor%ROWTYPE; + new_interval_id timespans.interval_id%TYPE; + new_timespan_id timespans.timespan_id%TYPE; + begin + new_timespan_id := null; + + -- Loop over each interval in timespan, creating a new copy + for timespan_val in timespan_cursor + loop + new_interval_id := time_interval.copy(timespan_val.interval_id, offset); + + if new_timespan_id is null then + new_timespan_id := new(new_interval_id); + else + join_interval(new_timespan_id, new_interval_id); + end if; + end loop; + return new_timespan_id; + end copy; + +end timespan; +/ +show errors + Index: openacs-4/packages/acs-events/sql/oracle/timespan-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/oracle/timespan-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/oracle/timespan-drop.sql 13 Jul 2001 02:00:30 -0000 1.1 @@ -0,0 +1,12 @@ +-- packages/acs-events/sql/timespan-drop.sql +-- +-- $Id: timespan-drop.sql,v 1.1 2001/07/13 02:00:30 jowells Exp $ + +drop package timespan; +drop index timespans_idx; +drop table timespans; + +drop package time_interval; +drop table time_intervals; + +drop sequence timespan_seq;