Index: openacs-4/packages/acs-events/sql/acs-events-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/Attic/acs-events-create.sql,v diff -u -N --- openacs-4/packages/acs-events/sql/acs-events-create.sql 12 Jun 2001 03:56:20 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,984 +0,0 @@ --- 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/06/12 03:56:20 nsadmin 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/acs-events-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/Attic/acs-events-drop.sql,v diff -u -N --- openacs-4/packages/acs-events/sql/acs-events-drop.sql 12 Jun 2001 03:56:20 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,26 +0,0 @@ --- packages/acs-events/sql/acs-events-drop.sql --- --- $Id: acs-events-drop.sql,v 1.1 2001/06/12 03:56:20 nsadmin 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/activity-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/Attic/activity-create.sql,v diff -u -N --- openacs-4/packages/acs-events/sql/activity-create.sql 12 Jun 2001 03:56:20 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,284 +0,0 @@ --- 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/06/12 03:56:20 nsadmin 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/activity-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/Attic/activity-drop.sql,v diff -u -N --- openacs-4/packages/acs-events/sql/activity-drop.sql 12 Jun 2001 03:56:20 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,16 +0,0 @@ --- packages/acs-events/sql/activity-drop.sql --- --- $Id: activity-drop.sql,v 1.1 2001/06/12 03:56:20 nsadmin 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/recurrence-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/Attic/recurrence-create.sql,v diff -u -N --- openacs-4/packages/acs-events/sql/recurrence-create.sql 12 Jun 2001 03:56:20 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,206 +0,0 @@ --- packages/acs-events/sql/recurrence-create.sql --- --- Support for temporal recurrences --- --- $Id: recurrence-create.sql,v 1.1 2001/06/12 03:56:20 nsadmin 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/recurrence-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/Attic/recurrence-drop.sql,v diff -u -N --- openacs-4/packages/acs-events/sql/recurrence-drop.sql 12 Jun 2001 03:56:20 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,10 +0,0 @@ --- packages/acs-events/sql/recurrence-drop.sql --- --- $Id: recurrence-drop.sql,v 1.1 2001/06/12 03:56:20 nsadmin Exp $ - -drop package recurrence; - -drop table recurrences; -drop table recurrence_interval_types; - -drop sequence recurrence_seq; Index: openacs-4/packages/acs-events/sql/timespan-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/Attic/timespan-create.sql,v diff -u -N --- openacs-4/packages/acs-events/sql/timespan-create.sql 12 Jun 2001 03:56:20 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,782 +0,0 @@ --- 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/06/12 03:56:20 nsadmin 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/timespan-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/Attic/timespan-drop.sql,v diff -u -N --- openacs-4/packages/acs-events/sql/timespan-drop.sql 12 Jun 2001 03:56:20 -0000 1.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,12 +0,0 @@ --- packages/acs-events/sql/timespan-drop.sql --- --- $Id: timespan-drop.sql,v 1.1 2001/06/12 03:56:20 nsadmin Exp $ - -drop package timespan; -drop index timespans_idx; -drop table timespans; - -drop package time_interval; -drop table time_intervals; - -drop sequence timespan_seq;