Index: openacs-4/packages/calendar/sql/postgresql/cal-item-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/postgresql/cal-item-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/calendar/sql/postgresql/cal-item-create.sql 24 Aug 2001 01:10:04 -0000 1.1 @@ -0,0 +1,426 @@ +-- Create the cal_item object +-- +-- @author Gary Jin (gjin@arsdigita.com) +-- @creation-date Nov 17, 2000 +-- @cvs-id $Id: cal-item-create.sql,v 1.1 2001/08/24 01:10:04 liliant Exp $ +-- + +-- ported by Lilian Tong (tong@ebt.ee.usyd.edu.au) + +---------------------------------------------------------- +-- cal_item_ojbect +---------------------------------------------------------- + +CREATE FUNCTION inline_0 () +RETURNS integer AS ' +begin + PERFORM acs_object_type__create_type ( + ''cal_item'', -- object_type + ''Calendar Item'', -- pretty_name + ''Calendar Items'', -- pretty_plural + ''acs_event'', -- supertype + ''cal_items'', -- table_name + ''cal_item_id'', -- id_column + null, -- package_name + ''f'', -- abstract_p + null, -- type_extension_table + null -- name_method + ); + return 0; +end;' LANGUAGE 'plpgsql'; + +SELECT inline_0 (); + +DROP FUNCTION inline_0 (); + +--begin + +-- acs_object_type.create_type ( +-- supertype => 'acs_event', +-- object_type => 'cal_item', +-- pretty_name => 'Calendar Item', +-- pretty_plural => 'Calendar Items', +-- table_name => 'cal_items', +-- id_column => 'cal_item_id' +-- ); + +--end; +--/ +--show errors + +CREATE FUNCTION inline_1 () +RETURNS integer AS ' +begin + PERFORM acs_attribute__create_attribute ( + ''cal_item'', -- object_type + ''on_which_calendar'', -- attribute_name + ''integer'', -- datatype + ''On Which Calendar'', -- pretty_name + ''On Which Calendars'', -- pretty_plural + null, -- table_name (default) + null, -- column_name (default) + null, -- default_value (default) + 1, -- min_n_values (default) + 1, -- max_n_values (default) + null, -- sort_order (default) + ''type_specific'', -- storage (default) + ''f'' -- static_p (default) + ); + return 0; +end;' LANGUAGE 'plpgsql'; + +SELECT inline_1 (); + +DROP FUNCTION inline_1 (); + + +--declare +-- attr_id acs_attributes.attribute_id%TYPE; +--begin +-- attr_id := acs_attribute.create_attribute ( +-- object_type => 'cal_item', +-- attribute_name => 'on_which_caledar', +-- pretty_name => 'On Which Calendar', +-- pretty_plural => 'On Which Calendars', +-- datatype => 'integer' +-- ); +--end; +--/ +--show errors + + +-- -- Each cal_item has the super_type of ACS_EVENTS +-- -- Table cal_items supplies additional information + +CREATE TABLE cal_items ( + -- primary key + cal_item_id integer + constraint cal_item_cal_item_id_fk + references acs_events + constraint news_item_news_item_id_pk + primary key, + -- a references to calendar + -- Each cal_item is owned by one calendar + on_which_calendar integer + constraint cal_item_which_cal_fk + references calendars + on delete cascade +); + +comment on table cal_items is ' + Table cal_items maps the ownership relation between + an cal_item_id to calendars. Each cal_item is owned + by a calendar +'; + +comment on column cal_items.cal_item_id is ' + Primary Key +'; + +comment on column cal_items.on_which_calendar is ' + Mapping to calendar. Each cal_item is owned + by a calendar +'; + + +------------------------------------------------------------- +-- create package cal_item +------------------------------------------------------------- + +CREATE FUNCTION cal_item__new ( + integer, -- cal_item_id cal_items.cal_item_id%TYPE + integer, -- on_which_calendar calenders.calendar_id%TYPE + varchar, -- name acs_activities.name%TYPE + varchar, -- description acs_activities.description%TYPE + integer, -- timespan_id acs_events.timespan_id%TYPE + integer, -- activity_id acs_events.activity_id%TYPE + integer, -- recurrence_id acs_events.recurrence_id%TYPE + varchar, -- object_type acs_objects.object_type%TYPE + integer, -- context_id acs_objects.context_id%TYPE + timestamp, -- createion_date acs_objects.creation_date%TYPE + integer, -- creation_user acs_objects.creation_user%TYPE + varchar -- creation_ip acs_objects.creation_ip%TYPE +) +RETURNS integer AS ' +declare + new__cal_item_id alias for $1; -- default null + new__on_which_calendar alias for $2; -- default null + new__name alias for $3; + new__description alias for $4; + new__timespan_id alias for $5; -- default null + new__activity_id alias for $6; -- default null + new__recurrence_id alias for $7; -- default null + new__object_type alias for $8; -- default "cal_item" + new__context_id alias for $9; -- default null + new__creation_date alias for $10; -- default now() + new__creation_user alias for $11; -- default null + new__creation_ip alias for $12; -- default null + v_cal_item_id cal_items.cal_item_id%TYPE; + +begin + v_cal_item_id := acs_event__new( + new__cal_item_id, -- event_id + new__name, -- name + new__description, -- description + new__timespan_id, -- timespan_id + new__activity_id, -- activity_id + new__recurrence_id, -- recurrence_id + new__object_type, -- object_type + new__creation_date, -- creation_date + new__creation_user, -- creation_user + new__creation_ip, -- creation_ip + new__context_id -- context_id + ); + + insert into cal_items + (new__cal_item_id, new__on_which_calendar) + values + (v_cal_item_id, new__on_which_calendar); + + return v_cal_item_id; + +end;' LANGUAGE 'plpgsql'; + + +------------------------------------------------------------ +-- the delete operation +------------------------------------------------------------ + +CREATE FUNCTION cal_item__delete ( + integer +) +RETURNS integer AS ' +declare + delete__cal_item_id alias for $1; +begin + -- Erase the cal_item associated with the id + delete from cal_items + where cal_item_id = delete__cal_item_id; + + -- Erase all the priviledges + delete from acs_permissions + where object_id = delete__cal_item_id; + + PERFORM acs_event__delete(delete__cal_item_id); + + return 0; + +end;' LANGUAGE 'plpgsql'; + + +------------------------------------------------------------- +-- the name function +------------------------------------------------------------- + + -- function to return the name of the cal_item +CREATE FUNCTION cal_item__name ( + integer +) +RETURNS integer AS ' +declare + name__cal_item_id alias for $1; + v_name acs_activities.name%TYPE; +begin + select name + into v_name + from acs_activities + where activity_id = + ( + select activity_id + from acs_events + where event_id = name__cal_item_id; + ); + + return v_name; + +end;' LANGUAGE 'plpgsql'; + + +--------------------------------------------------------------- +-- the on_which_calendar function +--------------------------------------------------------------- + + -- function to return the calendar that owns the cal_item +CREATE FUNCTION cal_item__on_which_calendar ( + integer +) +RETURNS integer AS ' +declare + on_which_calendar__cal_item_id alias for $1; + v_calendar_id calendars.calendar_id%TYPE; +begin + select on_which_calendar + into v_calendar_id + from cal_items + where cal_item_id = on_which_calendar__cal_item_id; + + return v_calendar_id; + +end;' LANGUAGE 'plpgsql'; + + + +--create or replace package cal_item +--as +-- function new ( +-- cal_item_id in cal_items.cal_item_id%TYPE default null, +-- on_which_calendar in calendars.calendar_id%TYPE , +-- name in acs_activities.name%TYPE default null, +-- description in acs_activities.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_objects.object_type%TYPE default 'cal_item', +-- 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 cal_items.cal_item_id%TYPE; +-- +-- delete cal_item +-- procedure delete ( +-- cal_item_id in cal_items.cal_item_id%TYPE +-- ); +-- +-- -- functions to return the name of the cal_item +-- function name ( +-- cal_item_id in cal_items.cal_item_id%TYPE +-- ) return acs_activities.name%TYPE; +-- +-- -- functions to return the calendar that owns the cal_item +-- function on_which_calendar ( +-- cal_item_id in cal_items.cal_item_id%TYPE +-- ) return calendars.calendar_id%TYPE; +-- +--end cal_item; +--/ +--show errors; +-- +-- +--create or replace package body cal_item +--as +-- function new ( +-- cal_item_id in cal_items.cal_item_id%TYPE default null, +-- on_which_calendar in calendars.calendar_id%TYPE , +-- name in acs_activities.name%TYPE default null, +-- description in acs_activities.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_objects.object_type%TYPE default 'cal_item', +-- 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 cal_items.cal_item_id%TYPE +-- +-- is +-- v_cal_item_id cal_items.cal_item_id%TYPE; +-- v_grantee_id acs_permissions.grantee_id%TYPE; +-- v_privilege acs_permissions.privilege%TYPE; +-- +-- begin +-- v_cal_item_id := acs_event.new ( +-- event_id => cal_item_id, +-- name => name, +-- description => description, +-- 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 cal_items +-- (cal_item_id, on_which_calendar) +-- values (v_cal_item_id, on_which_calendar); +-- +-- -- assign the default permission to the cal_item +-- -- by default, cal_item are going to inherit the +-- -- calendar permission that it belongs too. +-- +-- -- first find out the permissions. +-- --select grantee_id into v_grantee_id +-- --from acs_permissions +-- --where object_id = cal_item.new.on_which_calendar; +-- +-- --select privilege into v_privilege +-- --from acs_permissions +-- --where object_id = cal_item.new.on_which_calendar; +-- +-- -- now we grant the permissions +-- --acs_permission.grant_permission ( +-- -- object_id => v_cal_item_id, +-- -- grantee_id => v_grantee_id, +-- -- privilege => v_privilege +-- +-- --); +-- +-- return v_cal_item_id; +-- +-- end new; +-- +-- procedure delete ( +-- cal_item_id in cal_items.cal_item_id%TYPE +-- ) +-- is +-- +-- begin +-- -- Erase the cal_item assoicated with the id +-- delete from cal_items +-- where cal_item_id = cal_item.delete.cal_item_id; +-- +-- -- Erase all the privileges +-- delete from acs_permissions +-- where object_id = cal_item.delete.cal_item_id; +-- +-- acs_event.delete(cal_item_id); +-- end delete; +-- +-- -- functions to return the name of the cal_item +-- function name ( +-- cal_item_id in cal_items.cal_item_id%TYPE +-- ) +-- return acs_activities.name%TYPE +-- +-- is +-- v_name acs_activities.name%TYPE; +-- begin +-- select name +-- into v_name +-- from acs_activities +-- where activity_id = +-- ( +-- select activity_id +-- from acs_events +-- where event_id = cal_item.name.cal_item_id +-- ); +-- +-- return v_name; +-- end name; +-- +-- +-- -- functions to return the calendar that owns the cal_item +-- function on_which_calendar ( +-- cal_item_id in cal_items.cal_item_id%TYPE +-- ) +-- return calendars.calendar_id%TYPE +-- +-- is +-- v_calendar_id calendars.calendar_id%TYPE; +-- begin +-- select on_which_calendar +-- into v_calendar_id +-- from cal_items +-- where cal_item_id = cal_item.on_which_calendar.cal_item_id; +-- +-- return v_calendar_id; +-- end on_which_calendar; +-- +--end cal_item; +--/ +--show errors; Index: openacs-4/packages/calendar/sql/postgresql/cal-item-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/postgresql/cal-item-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/calendar/sql/postgresql/cal-item-drop.sql 24 Aug 2001 01:10:04 -0000 1.1 @@ -0,0 +1,76 @@ +-- Drop the cal_item object and all related tables, +-- views, and package +-- +-- @author Gary Jin (gjin@arsdigita.com) +-- @creation-date Nov 17, 2000 +-- @cvs-id $Id: cal-item-drop.sql,v 1.1 2001/08/24 01:10:04 liliant Exp $ +-- + +-- ported by Lilian Tong (tong@ebt.ee.usyd.edu.au) + +---------------------------------------------------------- +-- drop cal_item +---------------------------------------------------------- + +-- drop functions +drop function cal_item__new ( + integer, + integer, + varchar, + varchar, + integer, + integer, + integer, + varchar, + integer, + timestamp, + integer, + varchar +); + +drop function cal_item__delete (integer); + +drop function cal_item__name (integer); + +drop function cal_item__on_which_calendar (integer); + +--drop objects +delete from acs_objects where object_type='cal_item'; + +--drop table +drop table cal_items; + + + -- drop attributes and acs_object_type +begin; + -- drop attibutes + select acs_attribute__drop_attribute ( + 'cal_item', + 'on_which_calendar' + ); + + --drop type + select acs_object_type__drop_type( + 'cal_item', + 'f' + ); +end; + + +-- -- drop attributes and acs_object_type +--begin +-- acs_attribute.drop_attribute ('cal_item','on_which_calendar'); +-- acs_object_type.drop_type ('cal_item'); +--end; +--/ +--show errors +-- +-- +-- -- drop package +--drop package cal_item; +-- +-- +-- -- drop table +--drop table cal_items; + + Index: openacs-4/packages/calendar/sql/postgresql/cal-table-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/postgresql/cal-table-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/calendar/sql/postgresql/cal-table-create.sql 24 Aug 2001 01:10:04 -0000 1.1 @@ -0,0 +1,141 @@ +-- Creates support tables and useful views for the calendar system +-- +-- @author Gary Jin (gjin@arsdigita.com) +-- @creation-date Nov 30, 2000 +-- @cvs-id $Id: cal-table-create.sql,v 1.1 2001/08/24 01:10:04 liliant Exp $ +-- + + +------------------------------------------------------- +-- Calendar Support Tables +------------------------------------------------------- + + -- Table cal_party_prefs stores customization information + -- for each calendar. These data are unique to each party. + -- This means that each party using the same calendar can + -- have a different look to it. +create table cal_party_prefs ( + -- refers to a particular calendar Id + calendar_id integer + constraint cal_pty_prefs_cal_id_fk + references calendars + on delete cascade, + -- Party Id + party_id integer + constraint cal_pty_prefs_party_id_fk + references parties + on delete cascade, + -- default_view stores whether the user wants + -- list, month, day, week, or year as his/her default view. + default_view varchar(10) + default 'day' + constraint cal_pty_prefs_default_view_ck + check (default_view in ( + 'list', + 'day', + 'week', + 'month', + 'year' + ) + ), + -- the default number of minutes for each appointment + default_duration integer + default 60 + constraint cal_pty_prefs_default_duration + check (default_duration > 0), + -- the default starting time in daily view in military time 00 - 23 + daily_start --number(2) + numeric(2,0) + default 07 + constraint cal_pty_prefs_daily_start + check (daily_start < 24 and daily_start > -1), + -- the default ending time in daily view in military time 00 -23 + daily_end --number(2) + numeric(2,0) + default 18 + constraint cal_pty_prefs_daily_end + check (daily_end < 24 and daily_end > 0), + -- which time zone does the user belong to + time_zone integer + constraint cal_pty_prefs_time_zone_fk + references timezones + on delete cascade, + -- which day to start the week, monday or sunday + first_day_of_week varchar(9) + default 'Sunday' + constraint cal_pty_prefs_1st_day_ck + check (first_day_of_week in ( + 'Sunday', + 'Monday', + 'Tuesday', + 'Wednesday', + 'Thursday', + 'Friday', + 'Saturday' + ) + ), + -- unique constraint between calendar_id and party_id + -- this ensures that each party has only one set of + -- perferences per calendar + constraint cal_party_prefs_un unique(calendar_id, party_id) +); + + +comment on table cal_party_prefs is ' + Table cal_user_prefs would stores custom information + about each indivdual user. This would include time zone + which is the first day of the week, monday or sunday, + and the likes. +'; + +comment on column cal_party_prefs.party_id is ' + Maps to a party +'; + +comment on column cal_party_prefs.default_view is ' + default_view stores whether the user wants + list, month, day, week, or year as his/her default view. +'; + +comment on column cal_party_prefs.default_duration is ' + the default number of minutes for each appointment +'; + + +comment on column cal_party_prefs.daily_start is ' + the default start time in daily view in military time 00 - 23 + default to 07 or 7 am +'; + +comment on column cal_party_prefs.daily_end is ' + the default end time in daily view in military time 00 - 23 + default to 18 or 6 pm +'; + +--comment on column cal_party_prefs.time_zone is ' +-- The time zone that the user is in. This is useful in sending out +-- reminders and other applications +--'; + +comment on column cal_party_prefs.first_day_of_week is ' + Which day of the week will be displayed first in month and week view +'; + + + + + + + + + + + + + + + + + + + Index: openacs-4/packages/calendar/sql/postgresql/cal-table-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/postgresql/cal-table-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/calendar/sql/postgresql/cal-table-drop.sql 24 Aug 2001 01:10:04 -0000 1.1 @@ -0,0 +1,26 @@ +-- Drop the cal_item object and all related tables, +-- views, and package +-- +-- @author Gary Jin (gjin@arsdigita.com) +-- @creation-date Nov 17, 2000 +-- @cvs-id $Id: cal-table-drop.sql,v 1.1 2001/08/24 01:10:04 liliant Exp $ +-- + + +---------------------------------------------------------- +-- Drop Support Table +---------------------------------------------------------- + +DROP TABLE cal_party_prefs; + + + + + + + + + + + + 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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/calendar/sql/postgresql/calendar-create.sql 24 Aug 2001 01:10:04 -0000 1.1 @@ -0,0 +1,912 @@ +-- creates the calendar object +-- +-- @author Gary Jin (gjin@arsdigita.com) +-- @creation-date Nov 17, 2000 +-- @cvs-id $Id: calendar-create.sql,v 1.1 2001/08/24 01:10:04 liliant Exp $ +-- + +------------------------------------------------------------------ +-- calendar system permissions +------------------------------------------------------------------ + + -- creating the basic set of permissions for cal_item + -- + -- 1 create: create an new item + -- 2. read: can view the cal_item + -- 3. write: edit an existing cal_item + -- 4. delete: can delete the cal_item + -- 5. invite: can allow other parties to view or edit the cal_item +CREATE FUNCTION inline_0 () +RETURNS integer +AS 'begin + PERFORM acs_privilege__create_privilege(''cal_item_create'', ''Add an new item'', null); + PERFORM acs_privilege__create_privilege(''cal_item_read'', ''view an cal_item'', null); + PERFORM acs_privilege__create_privilege(''cal_item_write'', ''Edit an exsiting cal_item'', null); + PERFORM acs_privilege__create_privilege(''cal_item_delete'', ''Delete cal_item'', null ); + PERFORM acs_privilege__create_privilege(''cal_item_invite'', ''Allow others to view cal_item'', null); + + PERFORM acs_privilege__add_child(''create'', ''cal_item_create''); + PERFORM acs_privilege__add_child(''read'', ''cal_item_read''); + PERFORM acs_privilege__add_child(''write'', ''cal_item_write''); + + PERFORM acs_privilege__add_child(''delete'', ''cal_item_delete''); + + PERFORM acs_privilege__create_privilege(''calendar_on'', ''Implies that a calendar is selected'', null); + PERFORM acs_privilege__create_privilege(''calendar_show'', ''Show a calendar'', null); + + PERFORM acs_privilege__add_child(''read'', ''calendar_on''); + PERFORM acs_privilege__add_child(''read'', ''calendar_show''); + + PERFORM acs_privilege__create_privilege(''calendar_create'', ''Create a new calendar'', null); + PERFORM acs_privilege__create_privilege(''calendar_read'', ''View items on an exsiting calendar'', null); + PERFORM acs_privilege__create_privilege(''calendar_write'', ''Edit items of an exsiting calendar'', null); + PERFORM acs_privilege__create_privilege(''calendar_delete'',''Delete an calendar'', null); + + + PERFORM acs_privilege__add_child(''create'', ''calendar_create''); + PERFORM acs_privilege__add_child(''read'', ''calendar_read''); + PERFORM acs_privilege__add_child(''write'', ''calendar_write''); + PERFORM acs_privilege__add_child(''delete'', ''calendar_delete''); + + + PERFORM acs_privilege__add_child(''calendar_create'', ''cal_item_create''); + PERFORM acs_privilege__add_child(''calendar_read'', ''cal_item_read''); + PERFORM acs_privilege__add_child(''calendar_write'', ''cal_item_write''); + PERFORM acs_privilege__add_child(''calendar_delete'', ''cal_item_delete''); + + PERFORM acs_privilege__create_privilege(''calendar_admin'', ''calendar adminstrator'', null); + PERFORM acs_privilege__add_child(''admin'', ''calendar_admin''); + PERFORM acs_privilege__add_child(''calendar_admin'', ''calendar_read''); + PERFORM acs_privilege__add_child(''calendar_admin'', ''calendar_write''); + PERFORM acs_privilege__add_child(''calendar_admin'', ''calendar_delete''); + PERFORM acs_privilege__add_child(''calendar_admin'', ''calendar_create''); + PERFORM acs_privilege__add_child(''calendar_admin'', ''cal_item_invite''); + + return 0; + end;' +LANGUAGE 'plpgsql'; + +SELECT inline_0 (); + +drop function inline_0 (); + +---------------------------------------------------------- +-- calendar_ojbect +----------------------------------------------------------- + +CREATE FUNCTION inline_1() +RETURNS integer +AS 'declare + attr_id acs_attributes.attribute_id%TYPE; + begin + PERFORM + acs_object_type__create_type( + ''calendar'', -- object_type + ''Calendar'', -- pretty_name + ''Calendar'', -- pretty_plural + ''acs_object'', -- supertype + ''calendars'', -- table_name + ''calendar_id'',-- id_column + null, -- package_name + ''f'', -- abstract_p + null, -- type_extension_table + null -- name_method + ); + + attr_id := acs_attribute__create_attribute ( + ''calendar'', -- object_type + ''owner_id'', -- attribute_name + ''integer'', -- datatype + ''Owner'', -- pretty_name + ''Owners'', -- pretty_plural + null, -- table_name (default) + null, -- column_name (default) + null, -- default_value (default) + 1, -- min_n_values (default) + 1, -- max_n_values (default) + null, -- sort_order (default) + ''type_specific'', -- storage (default) + ''f'' -- static_p (default) + ); + + attr_id := acs_attribute__create_attribute ( + ''calendar'', -- object_type + ''private_p'', -- attribute_name + ''string'', -- datatype + ''Private Calendar'', -- pretty_name + ''Private Calendars'', -- pretty_plural + null, -- table_name (default) + null, -- column_name (default) + null, -- default_value (default) + 1, -- min_n_values (default) + 1, -- max_n_values (default) + null, -- sort_order (default) + ''type_specific'', -- storage (default) + ''f'' -- static_p (default) + ); + + attr_id := acs_attribute__create_attribute ( + ''calendar'', -- object_type + ''calendar_name'', -- attribute_name + ''string'', -- datatype + ''Calendar Name'', -- pretty_name + ''Calendar Names'', -- pretty_plural + null, -- table_name (default) + null, -- column_name (default) + null, -- default_value (default) + 1, -- min_n_values (default) + 1, -- max_n_values (default) + null, -- sort_order (default) + ''type_specific'', -- storage (default) + ''f'' -- static_p (default) + ); + return 0; + + end;' +LANGUAGE 'plpgsql'; + +SELECT inline_1(); + +DROP function inline_1(); + +--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). +create table calendars ( + -- primary key + calendar_id integer + constraint calendars_calendar_id_fk + references acs_objects + constraint calendars_calendar_id_pk + primary key, + -- the name of the calendar + calendar_name varchar(200), + -- the name of the calendar in a system should be unique + -- to avoid confusion + constraint calendar_calendar_name_un unique (calendar_name), + -- the individual or party that owns the calendar + owner_id integer + constraint calendars_calendar_owner_id_fk + references parties + on delete cascade, + -- keep track of package instances + package_id integer + constraint calendars_package_id_fk + references apm_packages(package_id) + on delete cascade, + -- whether or not the calendar is a private personal calendar or a + -- public calendar. + private_p boolean + default 'f' + constraint calendars_prviate_p_ck + check (private_p in ( + 't', + 'f' + ) + ) +); + +comment on table calendars is ' + Table calendars maps the many to many relationship betweens + calendar and its owners. +'; + +comment on column calendars.calendar_id is ' + Primary Key +'; + +comment on column calendars.calendar_name is ' + the name of the calendar. This would be unique to avoid confusion +'; + +comment on column calendars.owner_id is ' + the individual or party that owns the calendar +'; + +comment on column calendars.package_id is ' + keep track of package instances +'; + + +------------------------------------------------------------- +-- Load cal_item_object +------------------------------------------------------------- +--@@cal-item-create +\i cal-item-create.sql +------------------------------------------------------------- +-- create package calendar +------------------------------------------------------------- + +CREATE FUNCTION calendar__new ( + integer, -- calendar.calendar_id%TYPE + varchar(200), -- calendar.calendar_name%TYPE + varchar, -- acs_objects.object_type%TYPE + integer, -- calendar.owner_id%TYPE + integer, -- calendar.private_p + integer, -- calendar.package_id + integer, -- acs_objects.context_id%TYPE + timestamp, -- acs_objects.creation_date%TYPE + integer, -- acs_objects.creation_user%TYPE + varchar -- acs_objects.creation_ip%TYPE +) +RETURNS integer +AS 'declare + v_calendar_id calendars.calendar_id%TYPE; + new__calendar_id alias for $1, + new__calendar_name alias for $2, + new__object_type alias for $3, + new__owner_id alias for $4, + new__private_p alias for $5, + new__package_id alias for $6, + new__context_id alias for $7, + new__creation_date alias for $8, + new__createion_user alias for $9, + new__createion_ip alias for $10 + + begin + v_calendar_id := acs_object__new( + new__calendar_id, + new__object_type, + new__creation_date, + new__creation_user, + new__creation_ip, + new__context_id + ); + + insert into calendars + (new__calendar_id, new__calendar_name, new__owner_id, new__package_id, new__private_p) + values (v_calendar_id, new__calendar_name, new__owner_id, new__package_id, new__private_p); + + PERFORM acs_permission__grant_permission ( + v_calendar_id, + new__owner_id, + "calendar_admin" + ); + + return v_calendar_id; + end;' +LANGUAGE 'plpgsql'; + +CREATE FUNCTION calendar__delete( + integer -- calendar.calendar_id%TYPE +) +RETURNS integer +AS 'declare + delete__calendar_id alias for $1 + begin + delete from calendars + where calendar_id = delete__calendar_id; + + -- Delete all privileges associate with this calendar + + delete from acs_permissions + where object_id = delete__calendar_id; + + delete from acs_permissions + where object_id in ( + select cal_item_id + from cal_items + where on_which_calendar = delete__calendar_id; + ); + + PERFORM acs_object__delete(delete__calendar_id); + + return 0; + 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( + timestamp +) +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( + timestamp +) +RETURNS timestamp +AS 'declare + next_month__current_dates alias for $1; + v_date timestamp; + 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( + timestamp +) +RETURNS timestamp +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( + timestamp +) +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( + timestamp +) +RETURNS timestamp +AS 'declare + first_displayed_date__current_date alias for $1; + v_date timestamp; +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( + timestamp +) +RETURNS timestamp +AS 'declare + last_displayed_date__current_date alias for $1; + v_date timestamp; +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 +----------------------------------------------------------------- +-- +--@@cal-table-create +\i cal-table-create.sql Index: openacs-4/packages/calendar/sql/postgresql/calendar-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/postgresql/calendar-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/calendar/sql/postgresql/calendar-drop.sql 24 Aug 2001 01:10:04 -0000 1.1 @@ -0,0 +1,200 @@ +-- drop the calendar system +-- +-- @author Gary Jin (gjin@arsdigita.com) +-- @creation-date Nov 27, 2000 +-- $Id: calendar-drop.sql,v 1.1 2001/08/24 01:10:04 liliant Exp $ + + +------------------------------------------------ +-- Drop the Permissions +------------------------------------------------ + +delete from acs_permissions +where privilege in ( + 'cal_item_create', + 'cal_item_read', + 'cal_item_write', + 'cal_item_delete', + 'cal_item_invite' + ); + +delete from acs_privilege_hierarchy +where privilege in ( + 'cal_item_create', + 'cal_item_read', + 'cal_item_write', + 'cal_item_delete', + 'cal_item_invite' + ); + + +delete from acs_privilege_hierarchy +where child_privilege in ( + 'cal_item_create', + 'cal_item_read', + 'cal_item_write', + 'cal_item_delete', + 'cal_item_invite' + ); + + +delete from acs_privileges +where privilege in ( + 'cal_item_create', + 'cal_item_read', + 'cal_item_write', + 'cal_item_delete', + 'cal_item_invite' + ); + + + +delete from acs_permissions +where privilege in ( + 'calendar_create', + 'calendar_read', + 'calendar_write', + 'calendar_delete', + 'calendar_admin', + 'calendar_on', + 'calendar_show' + ); + +delete from acs_privilege_hierarchy +where privilege in ( + 'calendar_create', + 'calendar_read', + 'calendar_write', + 'calendar_delete', + 'calendar_admin', + 'calendar_on', + 'calendar_show' + ); + +delete from acs_privilege_hierarchy +where child_privilege in ( + 'calendar_create', + 'calendar_read', + 'calendar_write', + 'calendar_delete', + 'calendar_admin', + 'calendar_on', + 'calendar_show' + ); + + +delete from acs_privileges +where privilege in ( + 'calendar_create', + 'calendar_read', + 'calendar_write', + 'calendar_delete', + 'calendar_admin', + 'calendar_on', + 'calendar_show' + ); + + +------------------------------------------------ +-- Drop Support Tables +------------------------------------------------ +\i cal-table-drop.sql + + +------------------------------------------------ +-- drop cal_item +------------------------------------------------ +\i cal-item-drop.sql + + +------------------------------------------------ +-- Drop Calendar +------------------------------------------------ + +CREATE FUNCTION inline_0 () +RETURNS integer +AS 'begin + PERFORM acs_attribute__drop_attribute (''calendar'',''owner_id''); + PERFORM acs_attribute__drop_attribute (''calendar'',''private_p''); + PERFORM acs_object_type__drop_type (''calendar'', ''f''); + + return 0; + end;' +LANGUAGE 'plpgsql'; + +SELECT inline_0 (); + +DROP FUNCTION inline_0 (); + + +DELETE FROM acs_objects WHERE object_type='calendar'; + +DROP FUNCTION calendar__new ( + integer, -- calendar.calendar_id%TYPE + varchar, -- calendar.calendar_name%TYPE + varchar, -- acs_objects.object_type%TYPE + integer, -- calendar.owner_id%TYPE + integer, -- calendar.private_p + integer, -- calendar.package_id + integer, -- acs_objects.context_id%TYPE + timestamp, -- acs_objects.creation_date%TYPE + integer, -- acs_objects.creation_user%TYPE + varchar -- acs_objects.creation_ip%TYPE +); + +DROP FUNCTION calendar__delete( + integer +); + +DROP FUNCTION calendar__name( + integer +); + +DROP FUNCTION calendar__private_p( + integer +); + +DROP FUNCTION calendar__readable_p( + integer, + integer +); + +DROP FUNCTION calendar__show_p ( + integer, + integer +); + +DROP FUNCTION calendar__month_name( + timestamp +); + +DROP FUNCTION calendar__next_month( + timestamp +); + +DROP FUNCTION calendar__prev_month( + timestamp +); + +DROP FUNCTION calendar__num_day_in_month( + timestamp +); + +DROP FUNCTION calendar__first_displayed_date( + timestamp +); + +DROP FUNCTION calendar__last_displayed_date( + timestamp +); + + -- drop table +drop table calendars; + + + + + + + +