Index: openacs-4/packages/calendar/sql/postgresql/calendar-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/postgresql/calendar-create.sql,v diff -u -r1.12 -r1.13 --- openacs-4/packages/calendar/sql/postgresql/calendar-create.sql 11 Dec 2003 21:39:59 -0000 1.12 +++ openacs-4/packages/calendar/sql/postgresql/calendar-create.sql 10 Jan 2004 18:57:24 -0000 1.13 @@ -140,54 +140,8 @@ DROP function inline_0(); ---begin - -- create the calendar object - --- acs_object_type.create_type ( --- supertype => 'acs_object', --- object_type => 'calendar', --- pretty_name => 'Calendar', --- pretty_plural => 'Calendars', --- table_name => 'calendars', --- id_column => 'calendar_id' --- ); ---end; ---/ ---show errors - ---declare --- attr_id acs_attributes.attribute_id%TYPE; ---begin --- attr_id := acs_attribute.create_attribute ( --- object_type => 'calendar', --- attribute_name => 'owner_id', --- pretty_name => 'Owner', --- pretty_plural => 'Owners', --- datatype => 'integer' --- ); --- --- attr_id := acs_attribute.create_attribute ( --- object_type => 'calendar', --- attribute_name => 'private_p', --- pretty_name => 'Private Calendar', --- pretty_plural => 'Private Calendars', --- datatype => 'string' --- ); --- --- attr_id := acs_attribute.create_attribute ( --- object_type => 'calendar', --- attribute_name => 'calendar_name', --- pretty_name => 'Calendar Name', --- pretty_plural => 'Calendar Names', --- datatype => 'string' --- ); ---end; ---/ ---show errors - - - -- Calendar is a collection of events. Each calendar must - -- belong to somebody (a party). +-- Calendar is a collection of events. Each calendar must +-- belong to somebody (a party). create table calendars ( -- primary key calendar_id integer @@ -263,7 +217,6 @@ ------------------------------------------------------------- -- Load cal_item_object ------------------------------------------------------------- ---@@cal-item-create \i cal-item-create.sql ------------------------------------------------------------- -- create package calendar @@ -352,574 +305,13 @@ end;' LANGUAGE 'plpgsql'; -CREATE FUNCTION calendar__name( - integer -) -RETURNS varchar -AS 'declare - name__calendar_id alias for $1; - v_calendar_name calendars.calendar_name%TYPE; - begin - select calendar_name - into v_calendar_name - from calendars - where calendar_id = name__calendar_id; - return v_calendar_name; -end;' -LANGUAGE 'plpgsql'; - -CREATE FUNCTION calendar__private_p( - integer -) -RETURNS varchar -AS 'declare - v_private_p boolean; - private_p__calendar_id alias for $1; - begin - select private_p - into v_private_p - from calendars - where calendar_id = private_p__calendar_id; - - return v_private_p; -end;' -LANGUAGE 'plpgsql'; - -CREATE FUNCTION calendar__readable_p( - integer, - integer -) -RETURNS boolean -AS 'declare - readable_p__calendar_id alias for $1; - readable_p__party_id alias for $1; - v_readable_p boolean; - - begin - select (case count(*) - when 1 then true - else false - ) into v_readable_p - from acs_object_party_privilege_map - where party_id = readable_p__party_id - and object_id = readable_p__calendar_id - and privilege = ''calendar_read''; - - return v_readable_p; -end;' -LANGUAGE 'plpgsql'; - -CREATE FUNCTION calendar__show_p ( - integer, - integer -) -RETURNS boolean -AS 'declare - show_p__calendar_id alias for $1; - show_p__party_id alias for $2; - v_show_p boolean := ''t''; - begin - select (case count(*) - when 1 then true - else false - end) - into v_show_p - from acs_permissions - where grantee_id = show_p__party_id - and object_id = show_p__calendar_id - and privilege = ''calendar_show''; - - return v_show_p; - -end;' -LANGUAGE 'plpgsql'; - - -CREATE FUNCTION calendar__month_name( - timestamptz -) -RETURNS varchar -AS 'declare - month_name__current_date alias for $1; - v_name varchar; - begin - select to_char(month_name__current_date, ''fmMonth'') - into v_name - from dual; - - return v_name; - - end;' -LANGUAGE 'plpgsql'; - - -CREATE FUNCTION calendar__next_month( - timestamptz -) -RETURNS timestamptz -AS 'declare - next_month__current_dates alias for $1; - v_date timestamptz; - begin - --select trunc(add_months(to_date(db_sysdate), -1)) - select date_trunc(''day'', current_timestamp + cast(''1 month'' as interval)) - into v_date - from dual; - - return v_date; -end;' -LANGUAGE 'plpgsql'; - - -CREATE FUNCTION calendar__prev_month( - timestamptz -) -RETURNS timestamptz -AS 'declare - prev_month__current_date alias for $1; - v_date date; -begin --- select trunc(add_months(to_date(db_sysdate), -1)) - select date_trunc(''day'', current_timestamp - cast(''1 month'' as interval)) - into v_date - from dual; - - return v_date; -end;' -LANGUAGE 'plpgsql'; - - -CREATE FUNCTION calendar__num_day_in_month( - timestamptz -) -RETURNS integer -AS 'declare - num_day_in_month__current_date alias for $1; - v_num integer; -begin - select to_char(last_day(current_date), ''DD'') - into v_num - from dual; - - return v_num; -end;' -LANGUAGE 'plpgsql'; - - -CREATE FUNCTION calendar__first_displayed_date( - timestamptz -) -RETURNS timestamptz -AS 'declare - first_displayed_date__current_date alias for $1; - v_date timestamptz; -begin - select next_day(date_trunc(''Month'', current_date) - 7 , ''SUNDAY'') - into v_date - from dual; - - return v_date; -end;' -LANGUAGE 'plpgsql'; - - -CREATE FUNCTION calendar__last_displayed_date( - timestamptz -) -RETURNS timestamptz -AS 'declare - last_displayed_date__current_date alias for $1; - v_date timestamptz; -begin - select next_day(last_day(current_date), ''SATURDAY'') - into v_date - from dual; - - return v_date; -end;' -LANGUAGE 'plpgsql'; - - ---create or replace package calendar ---as --- function new ( --- calendar_id in acs_objects.object_id%TYPE default null, --- calendar_name in calendars.calendar_name%TYPE default null, --- object_type in acs_objects.object_type%TYPE default 'calendar', --- owner_id in calendars.owner_id%TYPE , --- private_p in calendars.private_p%TYPE default 'f', --- package_id in calendars.package_id%TYPE default null, --- context_id in acs_objects.context_id%TYPE default null, --- 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 --- --- ) return calendars.calendar_id%TYPE; --- --- procedure delete ( --- calendar_id in calendars.calendar_id%TYPE --- ); --- --- -- figures out the name of the calendar --- function name ( --- calendar_id in calendars.calendar_id%TYPE --- ) return calendars.calendar_name%TYPE; --- --- -- returns 't' if calendar is private and 'f' if its not --- function private_p ( --- calendar_id in calendars.calendar_id%TYPE --- ) return char; --- --- --- -- returns 't' if calendar is viewable by the given party --- -- this implies that the party has calendar_read permission --- -- on this calendar --- function readable_p ( --- calendar_id in calendars.calendar_id%TYPE, --- party_id in parties.party_id%TYPE --- ) return char; --- --- -- returns 't' if party wants to be able to select --- -- this calendar, and return 'f' otherwise. --- function show_p ( --- calendar_id in calendars.calendar_id%TYPE, --- party_id in parties.party_id%TYPE --- ) return char; --- --- --- ---------------------------------------------------------------- --- -- Helper functions for calendar generations: --- -- --- -- These functions are used for assist in calendar --- -- generation. Putting them in the PL/SQL level ensures that --- -- the date date will be the same, and allowing adoptation --- -- to a different language much easier and faster. --- -- --- -- current month name --- function month_name ( --- current_date date --- ) return char; --- --- -- next month --- function next_month ( --- current_date date --- ) return date; --- --- -- prev month --- function prev_month ( --- current_date date --- ) return date; --- --- -- number of days in the month --- function num_day_in_month ( --- current_date date --- ) return integer; --- --- -- first day to be displayed in a month. --- function first_displayed_date ( --- current_date date --- ) return date; --- --- -- last day to be displayed in a month. --- function last_displayed_date ( --- current_date date --- ) return date; --- ---end calendar; ---/ ---show errors; --- --- ---create or replace package body calendar ---as --- --- function new ( --- calendar_id in acs_objects.object_id%TYPE default null, --- calendar_name in calendars.calendar_name%TYPE default null, --- object_type in acs_objects.object_type%TYPE default 'calendar', --- owner_id in calendars.owner_id%TYPE , --- private_p in calendars.private_p%TYPE default 'f', --- package_id in calendars.package_id%TYPE default null, --- context_id in acs_objects.context_id%TYPE default null, --- 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 --- --- ) --- return calendars.calendar_id%TYPE --- --- is --- v_calendar_id calendars.calendar_id%TYPE; --- --- begin --- v_calendar_id := acs_object.new ( --- object_id => calendar_id, --- object_type => object_type, --- creation_date => creation_date, --- creation_user => creation_user, --- creation_ip => creation_ip, --- context_id => context_id --- ); --- --- insert into calendars --- (calendar_id, calendar_name, owner_id, package_id, private_p) --- values (v_calendar_id, calendar_name, owner_id, package_id, private_p); --- --- --- -- each calendar has three default conditions --- -- 1. all items are public --- -- 2. all items are private --- -- 3. no default conditions --- -- --- -- calendar being public implies granting permission --- -- calendar_read to the group 'the_public' and 'registered users' --- -- --- -- calendar being private implies granting permission --- -- calendar_read to the owner party/group of the party --- -- --- -- by default, we grant "calendar_admin" to --- -- the owner of the calendar --- acs_permission.grant_permission ( --- object_id => v_calendar_id, --- grantee_id => owner_id, --- privilege => 'calendar_admin' --- ); --- --- --- return v_calendar_id; --- end new; --- --- --- --- -- body for procedure delete --- procedure delete ( --- calendar_id in calendars.calendar_id%TYPE --- ) --- is --- --- begin --- -- First erase all the item relate to this calendar. --- delete from calendars --- where calendar_id = calendar.delete.calendar_id; --- --- -- Delete all privileges associate with this calendar --- delete from acs_permissions --- where object_id = calendar.delete.calendar_id; --- --- -- Delete all privilges of the cal_items that's associated --- -- with this calendar --- delete from acs_permissions --- where object_id in ( --- select cal_item_id --- from cal_items --- where on_which_calendar = calendar.delete.calendar_id --- ); --- --- --- acs_object.delete(calendar_id); --- end delete; --- --- --- --- -- figures out the name of the calendar --- function name ( --- calendar_id in calendars.calendar_id%TYPE --- ) --- return calendars.calendar_name%TYPE --- --- is --- v_calendar_name calendars.calendar_name%TYPE; --- begin --- select calendar_name --- into v_calendar_name --- from calendars --- where calendar_id = calendar.name.calendar_id; --- --- return v_calendar_name; --- end name; --- --- --- --- -- returns 't' if calendar is private and 'f' if its not --- function private_p ( --- calendar_id in calendars.calendar_id%TYPE --- ) --- return char --- --- is --- v_private_p char(1) := 't'; --- begin --- select private_p --- into v_private_p --- from calendars --- where calendar_id = calendar.private_p.calendar_id; --- --- return v_private_p; --- end private_p; --- --- --- --- -- returns 't' if calendar is viewable by the given party --- -- this implies that the party has calendar_read permission --- -- on this calendar --- function readable_p ( --- calendar_id in calendars.calendar_id%TYPE, --- party_id in parties.party_id%TYPE --- ) --- return char --- --- is --- v_readable_p char(1) := 't'; --- begin --- select decode(count(*), 1, 't', 'f') --- into v_readable_p --- from acs_object_party_privilege_map --- where party_id = calendar.readable_p.party_id --- and object_id = calendar.readable_p.calendar_id --- and privilege = 'calendar_read'; --- --- return v_readable_p; --- --- end readable_p; --- --- -- returns 't' if party wants to be able to select (calendar_show granted) --- -- this calendar, and .return 'f' otherwise. --- -- --- -- this seems to be a problem with the problem that when --- -- revoking the permissions using acs_permissions.revoke --- -- data is not removed from table acs_object_party_privilege_map. --- function show_p ( --- calendar_id in calendars.calendar_id%TYPE, --- party_id in parties.party_id%TYPE --- ) --- return char --- --- is --- v_show_p char(1) := 't'; --- begin --- select decode(count(*), 1, 't', 'f') --- into v_show_p --- from acs_permissions --- where grantee_id = calendar.show_p.party_id --- and object_id = calendar.show_p.calendar_id --- and privilege = 'calendar_show'; --- --- return v_show_p; --- --- end show_p; --- --- --- -- Helper functions for calendar generations: --- -- --- -- These functions are used for assist in calendar --- -- generation. Putting them in the PL/SQL level ensures that --- -- the date date will be the same, and allowing adoptation --- -- to a different language much easier and faster. --- -- --- -- current month name --- function month_name ( --- current_date date --- ) return char --- --- is --- name char; --- begin --- select to_char(to_date(calendar.month_name.current_date), 'fmMonth') --- into name --- from dual; --- --- return name; --- end month_name; --- --- --- -- next month --- function next_month ( --- current_date date --- ) return date --- --- is --- v_date date; --- begin --- select trunc(add_months(to_date(sysdate), -1)) --- into v_date --- from dual; --- --- return v_date; --- end next_month; --- --- --- -- prev month --- function prev_month ( --- current_date date --- ) return date --- --- is --- v_date date; --- begin --- select trunc(add_months(to_date(sysdate), -1)) --- into v_date --- from dual; --- --- return v_date; --- end prev_month; --- --- -- number of days in the month --- function num_day_in_month ( --- current_date date --- ) return integer --- --- is --- v_num integer; --- begin --- select to_char(last_day(to_date(sysdate)), 'DD') --- into v_num --- from dual; --- --- return v_num; --- end num_day_in_month; --- --- -- first day to be displayed in a month. --- function first_displayed_date ( --- current_date date --- ) return date --- --- is --- v_date date; --- begin --- select next_day(trunc(to_date(sysdate), 'Month') - 7, 'SUNDAY') --- into v_date --- from dual; --- --- return v_date; --- end first_displayed_date; --- --- -- last day to be displayed in a month. --- function last_displayed_date ( --- current_date date --- ) return date --- --- is --- v_date date; --- begin --- select next_day(last_day(to_date(sysdate)), 'SATURDAY') --- into v_date --- from dual; --- --- return v_date; --- end last_displayed_date; --- ---end calendar; ---/ ---show errors - - - ----------------------------------------------------------------- -- load related sql files ----------------------------------------------------------------- --\i cal-item-create.sql -- ---@@cal-table-create \i cal-table-create.sql + +\i calendar-notifications-init.sql \ No newline at end of file