Index: openacs-4/contrib/packages/events/sql/oracle/upgrade/upgrade-0.1d2-0.1d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/events/sql/oracle/upgrade/upgrade-0.1d2-0.1d3.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/events/sql/oracle/upgrade/upgrade-0.1d2-0.1d3.sql 8 Oct 2003 15:51:12 -0000 1.1 @@ -0,0 +1,585 @@ +-- ./contrib/packages/events/sql/oracle/events-activities-package-create.sql +-- +-- The Events Package +-- +-- @author Michael Steigman (michael@steigman.net) +-- @version $Id: upgrade-0.1d2-0.1d3.sql,v 1.1 2003/10/08 15:51:12 mohanp Exp $ +-- +-- This package was orinally written by Bryan Che and Philip Greenspun +-- +-- GNU GPL v2 +-- + + +-- The Activity Package + +create or replace package events_activity +as + function new ( + activity_id in events_activities.activity_id%TYPE default null, + package_id in events_activities.package_id%TYPE, + detail_url in events_activities.detail_url%TYPE default null, + default_contact_user_id in events_activities.default_contact_user_id%TYPE default null, + 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 'f', + status_summary in acs_activities.status_summary%TYPE default null, + object_type in acs_object_types.object_type%TYPE default 'events_activity', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return events_activities.activity_id%TYPE; + + procedure del ( + activity_id in events_activities.activity_id%TYPE + ); + +end events_activity; +/ +show errors; + + + +create or replace package body events_activity +as + function new ( + activity_id in events_activities.activity_id%TYPE default null, + package_id in events_activities.package_id%TYPE, + detail_url in events_activities.detail_url%TYPE default null, + default_contact_user_id in events_activities.default_contact_user_id%TYPE default null, + 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 'f', + status_summary in acs_activities.status_summary%TYPE default null, + object_type in acs_object_types.object_type%TYPE default 'events_activity', + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return events_activities.activity_id%TYPE + is + v_activity_id events_activities.activity_id%TYPE; + begin + v_activity_id:= acs_activity.new ( + activity_id => activity_id, + name => name, + description => description, + html_p => html_p, + status_summary => status_summary, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into events_activities + (activity_id, package_id, detail_url, default_contact_user_id) + values + (v_activity_id, package_id, detail_url, default_contact_user_id); + + return v_activity_id; + end new; + + procedure del ( + activity_id in events_activities.activity_id%TYPE + ) + is + cursor v_activity_events is + select event_id from acs_events + where activity_id = del.activity_id; + begin + -- find and delete event instances + for row in v_activity_events loop + events_event.del(row.event_id); + end loop; + delete from events_def_actvty_attr_map where activity_id = del.activity_id; + delete from events_org_role_activity_map where activity_id = del.activity_id; + delete from events_activities where activity_id = del.activity_id; + acs_object.delete(activity_id); + end del; + +end events_activity; +/ +show errors; + +-- ./contrib/packages/events/sql/oracle/events-events-package-create.sql +-- +-- The Events Package +-- +-- @author Michael Steigman (michael@steigman.net) +-- @version $Id: upgrade-0.1d2-0.1d3.sql,v 1.1 2003/10/08 15:51:12 mohanp Exp $ +-- +-- This package was orinally written by Bryan Che and Philip Greenspun +-- +-- GNU GPL v2 +-- + + +-- The Event Package + +create or replace package events_event +as + procedure new ( + event_id in events_events.event_id%TYPE default null, + venue_id in events_venues.venue_id%TYPE, + display_after in events_events.display_after%TYPE default null, + max_people in events_events.max_people%TYPE default null, + reg_deadline in events_events.reg_deadline%TYPE, + available_p in events_events.available_p%TYPE default 't', + deleted_p in events_events.deleted_p%TYPE default 'f', + reg_cancellable_p in events_events.reg_cancellable_p%TYPE, + reg_needs_approval_p in events_events.reg_needs_approval_p%TYPE, + contact_user_id in events_events.contact_user_id%TYPE default null, + refreshments_note in events_events.refreshments_note%TYPE default null, + av_note in events_events.av_note%TYPE default null, + additional_note in events_events.additional_note%TYPE default null, + alternative_reg in events_events.alternative_reg%TYPE default null, + activity_id in acs_events.activity_id%TYPE + ); + +-- function new ( +-- event_id in events_events.event_id%TYPE default null, +-- venue_id in events_venues.venue_id%TYPE, +-- display_after in events_events.display_after%TYPE default null, +-- max_people in events_events.max_people%TYPE default null, +-- reg_deadline in events_events.reg_deadline%TYPE, +-- available_p in events_events.available_p%TYPE default 't', +-- deleted_p in events_events.deleted_p%TYPE default 'f', +-- reg_cancellable_p in events_events.reg_cancellable_p%TYPE, +-- reg_needs_approval_p in events_events.reg_needs_approval_p%TYPE, +-- refreshments_note in events_events.refreshments_note%TYPE default null, +-- av_note in events_events.av_note%TYPE default null, +-- additional_note in events_events.additional_note%TYPE default null, +-- alternative_reg in events_events.alternative_reg%TYPE default null, +-- activity_id in acs_events.activity_id%TYPE, +-- name in acs_events.name%TYPE default null, +-- description in acs_events.description%TYPE default null, +-- html_p in acs_events.html_p%TYPE default 'f', +-- status_summary in acs_events.status_summary%TYPE default null, +-- timespan_id in acs_events.timespan_id%TYPE, +-- recurrence_id in acs_events.recurrence_id%TYPE default null, +-- object_type in acs_objects.object_type%TYPE default 'events_event', +-- creation_date in acs_objects.creation_date%TYPE default sysdate, +-- creation_user in acs_objects.creation_user%TYPE, +-- creation_ip in acs_objects.creation_ip%TYPE, +-- context_id in acs_objects.context_id%TYPE default null +-- ) return events_events.event_id%TYPE; + + procedure del ( + event_id in events_events.event_id%TYPE + ); + +end events_event; +/ +show errors; + +create or replace package body events_event +as + procedure new ( + event_id in events_events.event_id%TYPE default null, + venue_id in events_venues.venue_id%TYPE, + display_after in events_events.display_after%TYPE default null, + max_people in events_events.max_people%TYPE default null, + reg_deadline in events_events.reg_deadline%TYPE, + available_p in events_events.available_p%TYPE default 't', + deleted_p in events_events.deleted_p%TYPE default 'f', + reg_cancellable_p in events_events.reg_cancellable_p%TYPE, + reg_needs_approval_p in events_events.reg_needs_approval_p%TYPE, + contact_user_id in events_events.contact_user_id%TYPE default null, + refreshments_note in events_events.refreshments_note%TYPE default null, + av_note in events_events.av_note%TYPE default null, + additional_note in events_events.additional_note%TYPE default null, + alternative_reg in events_events.alternative_reg%TYPE default null, + activity_id in acs_events.activity_id%TYPE + ) + is + cursor v_activity_attrs is + select attribute_id + from events_def_actvty_attr_map edaam + where edaam.activity_id = new.activity_id; + cursor v_activity_org_roles is + select role_id + from events_org_role_activity_map eoram + where eoram.activity_id = new.activity_id; + begin + + insert into events_events + (event_id, contact_user_id, venue_id, display_after, max_people, reg_deadline, available_p, + deleted_p, reg_cancellable_p, reg_needs_approval_p, refreshments_note, av_note, + additional_note, alternative_reg) + values + (event_id, contact_user_id, venue_id, display_after, max_people, reg_deadline, available_p, + deleted_p, reg_cancellable_p, reg_needs_approval_p, refreshments_note, av_note, + additional_note, alternative_reg); + + -- copy over pointers to custom fields (registration attributes) + for row in v_activity_attrs loop + insert into events_event_attr_map + (event_id, attribute_id) + values + (event_id, row.attribute_id); + end loop; + + for row in v_activity_org_roles loop + insert into events_org_role_event_map + (event_id, role_id) + values + (event_id, row.role_id); + end loop; + end new; + +-- function new ( +-- event_id in events_events.event_id%TYPE default null, +-- venue_id in events_venues.venue_id%TYPE, +-- display_after in events_events.display_after%TYPE default null, +-- max_people in events_events.max_people%TYPE default null, +-- reg_deadline in events_events.reg_deadline%TYPE, +-- available_p in events_events.available_p%TYPE default 't', +-- deleted_p in events_events.deleted_p%TYPE default 'f', +-- reg_cancellable_p in events_events.reg_cancellable_p%TYPE, +-- reg_needs_approval_p in events_events.reg_needs_approval_p%TYPE, +-- refreshments_note in events_events.refreshments_note%TYPE default null, +-- av_note in events_events.av_note%TYPE default null, +-- additional_note in events_events.additional_note%TYPE default null, +-- alternative_reg in events_events.alternative_reg%TYPE default null, +-- activity_id in acs_events.activity_id%TYPE, +-- name in acs_events.name%TYPE default null, +-- description in acs_events.description%TYPE default null, +-- html_p in acs_events.html_p%TYPE default 'f', +-- status_summary in acs_events.status_summary%TYPE default null, +-- timespan_id in acs_events.timespan_id%TYPE, +-- recurrence_id in acs_events.recurrence_id%TYPE default null, +-- object_type in acs_objects.object_type%TYPE default 'events_event', +-- creation_date in acs_objects.creation_date%TYPE default sysdate, +-- creation_user in acs_objects.creation_user%TYPE, +-- creation_ip in acs_objects.creation_ip%TYPE, +-- context_id in acs_objects.context_id%TYPE default null +-- ) return events_events.event_id%TYPE +-- is +-- v_event_id events_events.event_id%TYPE; +-- cursor v_activity_attrs is +-- select attribute_id +-- from events_def_actvty_attr_map edaam +-- where edaam.activity_id = new.activity_id; +-- cursor v_activity_org_roles is +-- select role_id +-- from events_org_role_activity_map eoram +-- where eoram.activity_id = new.activity_id; +-- begin +-- v_event_id := acs_event.new ( +-- event_id => event_id, +-- name => name, +-- description => description, +-- html_p => html_p, +-- status_summary => status_summary, +-- timespan_id => timespan_id, +-- activity_id => activity_id, +-- recurrence_id => recurrence_id, +-- object_type => object_type, +-- creation_date => creation_date, +-- creation_user => creation_user, +-- creation_ip => creation_ip, +-- context_id => context_id +-- ); + +-- insert into events_events +-- (event_id, venue_id, display_after, max_people, reg_deadline, available_p, +-- deleted_p, reg_cancellable_p, reg_needs_approval_p, refreshments_note, av_note, +-- additional_note, alternative_reg) +-- values +-- (v_event_id, venue_id, display_after, max_people, reg_deadline, available_p, +-- deleted_p, reg_cancellable_p, reg_needs_approval_p, refreshments_note, av_note, +-- additional_note, alternative_reg); + +-- -- copy over pointers to custom fields (registration attributes) +-- for row in v_activity_attrs loop +-- insert into events_event_attr_map +-- (event_id, attribute_id) +-- values +-- (v_event_id, row.attribute_id); +-- end loop; + +-- for row in v_activity_org_roles loop +-- insert into events_org_role_event_map +-- (event_id, role_id) +-- values +-- (v_event_id, row.role_id); +-- end loop; + +-- return v_event_id; +-- end new; + + procedure del ( + event_id in events_events.event_id%TYPE + ) + is + begin + delete from events_org_role_event_map where event_id = del.event_id; + delete from events_event_attr_map where event_id = del.event_id; + delete from events_events where event_id = del.event_id; + acs_object.delete(event_id); + end del; + +end events_event; +/ +show errors; + + + +-- ./contrib/packages/events/sql/oracle/events-orders-package-create.sql +-- +-- The Events Package +-- +-- @author Michael Steigman (michael@steigman.net) +-- @version $Id: upgrade-0.1d2-0.1d3.sql,v 1.1 2003/10/08 15:51:12 mohanp Exp $ +-- +-- This package was originally written by Bryan Che and Philip Greenspun +-- +-- GNU GPL v2 +-- + +-- Order Package + +create or replace package events_order +as + function new ( + order_id in events_orders.order_id%TYPE default null, + user_id in users.user_id%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return events_orders.order_id%TYPE; + + procedure del ( + order_id in events_orders.order_id%TYPE + ); + +end events_order; +/ +show errors; + + + +create or replace package body events_order +as + function new ( + order_id in events_orders.order_id%TYPE default null, + user_id in users.user_id%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return events_orders.order_id%TYPE + is + v_order_id acs_objects.object_id%TYPE; + begin + v_order_id:= acs_object.new ( + object_id => order_id, + object_type => 'events_order', + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into events_orders + (order_id, user_id) + values + (v_order_id, user_id); + + return v_order_id; + end new; + + procedure del ( + order_id in events_orders.order_id%TYPE + ) + is + begin + acs_object.delete(del.order_id); + end del; + +end events_order; +/ +show errors; + + +-- ./contrib/packages/events/sql/oracle/events-registrations-package-create.sql + +-- +-- The Events Package +-- +-- @author Michael Steigman (michael@steigman.net) +-- @version $Id: upgrade-0.1d2-0.1d3.sql,v 1.1 2003/10/08 15:51:12 mohanp Exp $ +-- +-- This package was orinally written by Bryan Che and Philip Greenspun +-- +-- GNU GPL v2 +-- + +-- The Registration Package + +create or replace package events_registration +as + function new ( + reg_id in events_registrations.reg_id%TYPE default null, + event_id in events_events.event_id%TYPE, + user_id in users.user_id%TYPE, + reg_state in events_registrations.reg_state%TYPE, + comments in events_registrations.comments%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return events_registrations.reg_id%TYPE; + + procedure del ( + reg_id in events_registrations.reg_id%TYPE + ); + +end events_registration; +/ +show errors; + + + +create or replace package body events_registration +as + function new ( + reg_id in events_registrations.reg_id%TYPE default null, + event_id in events_events.event_id%TYPE, + user_id in users.user_id%TYPE, + reg_state in events_registrations.reg_state%TYPE, + comments in events_registrations.comments%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return events_registrations.reg_id%TYPE + is + v_reg_id acs_objects.object_id%TYPE; + begin + v_reg_id:= acs_object.new ( + object_id => reg_id, + object_type => 'events_registration', + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into events_registrations + (reg_id, event_id, user_id, reg_state, comments) + values + (v_reg_id, event_id, user_id, reg_state, comments); + + return v_reg_id; + end new; + + procedure del ( + reg_id in events_registrations.reg_id%TYPE + ) + is + begin + acs_object.delete(reg_id); + end del; + +end events_registration; +/ +show errors; + +-- ./contrib/packages/events/sql/oracle/events-venues-package-create.sql + +-- +-- The Events Package +-- +-- @author Michael Steigman (michael@steigman.net) +-- @version $Id: upgrade-0.1d2-0.1d3.sql,v 1.1 2003/10/08 15:51:12 mohanp Exp $ +-- +-- This package was orinally written by Bryan Che and Philip Greenspun +-- +-- GNU GPL v2 +-- + + +-- The Venue Package + +create or replace package events_venue +as + function new ( + venue_id in events_venues.venue_id%TYPE default null, + package_id in events_venues.package_id%TYPE default null, + venue_name in events_venues.venue_name%TYPE, + address1 in events_venues.address1%TYPE default null, + address2 in events_venues.address2%TYPE default null, + city in events_venues.city%TYPE, + usps_abbrev in events_venues.usps_abbrev%TYPE default null, + postal_code in events_venues.postal_code%TYPE default null, + time_zone in events_venues.time_zone%TYPE default null, + iso in events_venues.iso%TYPE default null, + phone_number in events_venues.phone_number%TYPE default null, + fax_number in events_venues.fax_number%TYPE default null, + email in events_venues.email%TYPE default null, + needs_reserve_p in events_venues.needs_reserve_p%TYPE default 'f', + max_people in events_venues.max_people%TYPE default null, + description in events_venues.description%TYPE default null + ) return events_venues.venue_id%TYPE; + + procedure del ( + venue_id in events_venues.venue_id%TYPE + ); + +end events_venue; +/ +show errors; + +create or replace package body events_venue +as + function new ( + venue_id in events_venues.venue_id%TYPE default null, + package_id in events_venues.package_id%TYPE default null, + venue_name in events_venues.venue_name%TYPE, + address1 in events_venues.address1%TYPE default null, + address2 in events_venues.address2%TYPE default null, + city in events_venues.city%TYPE, + usps_abbrev in events_venues.usps_abbrev%TYPE default null, + postal_code in events_venues.postal_code%TYPE default null, + time_zone in events_venues.time_zone%TYPE default null, + iso in events_venues.iso%TYPE default null, + phone_number in events_venues.phone_number%TYPE default null, + fax_number in events_venues.fax_number%TYPE default null, + email in events_venues.email%TYPE default null, + needs_reserve_p in events_venues.needs_reserve_p%TYPE default 'f', + max_people in events_venues.max_people%TYPE default null, + description in events_venues.description%TYPE default null + ) return events_venues.venue_id%TYPE + is + v_venue_id events_venues.venue_id%TYPE; + begin + + insert into events_venues + (venue_id, venue_name, address1, address2, city, usps_abbrev, postal_code, iso, phone_number, + time_zone, fax_number, email, needs_reserve_p, max_people, description, package_id) + values + (venue_id, venue_name, address1, address2, city, usps_abbrev, postal_code, iso, phone_number, + time_zone, fax_number, email, needs_reserve_p, max_people, description, package_id); + + return v_venue_id; + end new; + + procedure del ( + venue_id in events_venues.venue_id%TYPE + ) + is + begin + delete from events_venues where venue_id = del.venue_id; + acs_object.delete(del.venue_id); + end del; + +end events_venue; +/ +show errors; + +