Index: openacs-4/packages/calendar/calendar.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/calendar.info,v diff -u -r1.44 -r1.45 --- openacs-4/packages/calendar/calendar.info 29 Mar 2018 23:07:34 -0000 1.44 +++ openacs-4/packages/calendar/calendar.info 22 Apr 2018 18:02:52 -0000 1.45 @@ -7,7 +7,7 @@ f f - + Dirk Gomez Personal and shared event calendars. 2017-08-06 @@ -17,10 +17,10 @@ 2 #calendar.Calendar# - + - + Index: openacs-4/packages/calendar/sql/oracle/cal-item-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/oracle/cal-item-create.sql,v diff -u -r1.12 -r1.13 --- openacs-4/packages/calendar/sql/oracle/cal-item-create.sql 7 Aug 2017 23:48:05 -0000 1.12 +++ openacs-4/packages/calendar/sql/oracle/cal-item-create.sql 22 Apr 2018 18:02:52 -0000 1.13 @@ -28,7 +28,7 @@ begin attr_id := acs_attribute.create_attribute ( object_type => 'cal_item', - attribute_name => 'on_which_caledar', + attribute_name => 'on_which_calendar', pretty_name => 'On Which Calendar', pretty_plural => 'On Which Calendars', datatype => 'integer' @@ -47,8 +47,6 @@ references acs_events constraint cal_item_cal_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 @@ -75,8 +73,41 @@ '; create index cal_items_on_which_cal_idx on cal_items (on_which_calendar); - + ------------------------------------------------------------- +CREATE TABLE cal_uids ( + -- primary key + cal_uid varchar + constraint cal_uid_pk + primary key, + on_which_activity integer + constraint cal_uid_fk + not null + references acs_activities + on delete cascade, + ical_vars varchar2(4000) +); + +comment on table cal_uids is ' + Table cal_uids maps a unique (external) key to an + activity. This is needed for syncing calendars via + ical; the field uid should go into acs_activities +'; + + +comment on column cal_uids.cal_uid is ' + Primary Key +'; + +comment on column cal_uids.on_which_activity is ' + Reference to an activity, for which the key is used +'; + +comment on column cal_uids.ical_vars is ' + List with attributes and values from external ical calendar programs +'; + +------------------------------------------------------------- -- create package cal_item ------------------------------------------------------------- @@ -133,8 +164,10 @@ 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 + creation_ip in acs_objects.creation_ip%TYPE default null, + package_id in acs_objects.package_id%TYPE default null, + location in acs_event.location%TYPE default null + ) return cal_items.cal_item_id%TYPE is v_cal_item_id cal_items.cal_item_id%TYPE; @@ -155,7 +188,9 @@ creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip, - context_id => context_id + context_id => context_id, + package_id => package_id, + location => location ); insert into cal_items @@ -191,8 +226,14 @@ cal_item_id in cal_items.cal_item_id%TYPE ) is - + v_activity_id acs_events.activity_id%TYPE; + v_recurrence_id acs_events.recurrence_id%TYPE; begin + + select activity_id, recurrence_id into v_activity_id, v_recurrence_id + from acs_events + where event_id = cal_item_id; + -- Erase the cal_item associated with the id delete from cal_items where cal_item_id = cal_item.del.cal_item_id; @@ -202,6 +243,19 @@ where object_id = cal_item.del.cal_item_id; acs_event.del(cal_item_id); + + IF instances_exist_p(recurrence_id) = 'f' THEN + -- + -- There are no more events for the activity, we can clean up + -- both, the activity and - if given - the recurrence. + -- + acs_activity.del(v_activity_id); + + IF v_recurrence_id is not null THEN + recurrence.del(v_recurrence_id); + END IF; + END IF; + end del; procedure delete_all ( @@ -220,16 +274,52 @@ end delete_all; end cal_item; / -show errors; +show errors +create or replace package cal_uid +as + procedure upsert ( + p_cal_uid in cal_uids.cal_uid%TYPE, + p_activity_id in cal_uids.on_which_activity%TYPE, + p_ical_vars in cal_uids.ical_vars%TYPE + ); +end cal_uid; +/ +show errors +create or replace package body cal_uid +as + procedure upsert ( + p_cal_uid in cal_uids.cal_uid%TYPE, + p_activity_id in cal_uids.on_which_activity%TYPE, + p_ical_vars in cal_uids.ical_vars%TYPE + ) + is + BEGIN + -- + -- We might have duplicates on the activity_id and on the cal_uid, + -- both should be unique. + -- + -- Try to delete entry to avoid duplicates (might fail) + delete from cal_uids where on_which_activity = p_activity_id; + -- Insert value + insert into cal_uids + (cal_uid, on_which_activity, ical_vars) + values (p_cal_uid, p_activity_id, p_ical_vars); + exception + when dup_val_on_index then + update cal_uids + set ical_vars = p_ical_vars + where cal_uid = p_cal_uid; + END upsert; +end sec_session_property; +/ +show errors - - Index: openacs-4/packages/calendar/sql/oracle/upgrade/upgrade-2.10.0d0-2.10.0d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/oracle/upgrade/upgrade-2.10.0d0-2.10.0d1.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/calendar/sql/oracle/upgrade/upgrade-2.10.0d0-2.10.0d1.sql 22 Apr 2018 18:02:52 -0000 1.1 @@ -0,0 +1,55 @@ +--- +--- CREATE TABLE IF NOT EXISTS for Oracle ... +--- + +DECLARE count NUMBER; +BEGIN +count := 0; +SELECT COUNT(1) INTO count from user_tables WHERE table_name= 'CAL_UIDS'; +IF COL_COUNT = 0 THEN + EXECUTE IMMEDIATE ' + CREATE TABLE IF NOT EXISTS cal_uids ( + cal_uid varchar + constraint cal_uid_pk + primary key, + on_which_activity integer + constraint cal_uid_fk + not null + references acs_activities + on delete cascade, + ical_vars varchar + )'; +END IF; +END; +/ + +create or replace package body cal_uid +as + procedure upsert ( + p_cal_uid in cal_uids.cal_uid%TYPE, + p_activity_id in cal_uids.on_which_activity%TYPE, + p_ical_vars in cal_uids.ical_vars%TYPE + ) + is + BEGIN + -- + -- We might have duplicates on the activity_id and on the cal_uid, + -- both should be unique. + -- + -- Try to delete entry to avoid duplicates (might fail) + delete from cal_uids where on_which_activity = p_activity_id; + -- Insert value + insert into cal_uids + (cal_uid, on_which_activity, ical_vars) + values (p_cal_uid, p_activity_id, p_ical_vars); + + exception + when dup_val_on_index then + update cal_uids + set ical_vars = p_ical_vars + where cal_uid = p_cal_uid; + END upsert; + +end sec_session_property; +/ +show errors Index: openacs-4/packages/calendar/sql/oracle/upgrade/upgrade-2.10.0d1-2.10.0d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/oracle/upgrade/upgrade-2.10.0d1-2.10.0d2.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/calendar/sql/oracle/upgrade/upgrade-2.10.0d1-2.10.0d2.sql 22 Apr 2018 18:02:52 -0000 1.1 @@ -0,0 +1,110 @@ + +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, + html_p in acs_activities.html_p%TYPE default 'f', + status_summary in acs_activities.status_summary%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, + item_type_id in cal_items.item_type_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, + package_id in acs_objects.package_id%TYPE default null, + location in acs_event.location%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, + 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, + package_id => package_id, + location => location + ); + + insert into cal_items + (cal_item_id, on_which_calendar, item_type_id) + values (v_cal_item_id, on_which_calendar, item_type_id); + + -- 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 del ( + cal_item_id in cal_items.cal_item_id%TYPE + ) + is + + begin + -- Erase the cal_item associated with the id + delete from cal_items + where cal_item_id = cal_item.del.cal_item_id; + + -- Erase all the privileges + delete from acs_permissions + where object_id = cal_item.del.cal_item_id; + + acs_event.del(cal_item_id); + end del; + + procedure delete_all ( + recurrence_id in acs_events.recurrence_id%TYPE + ) is + v_event_id acs_events%ROWTYPE; + begin + FOR v_event_id in + (select * from acs_events + where recurrence_id = delete_all.recurrence_id) + LOOP + cal_item.del(v_event_id.event_id); + end LOOP; + + recurrence.del(recurrence_id); + end delete_all; +end cal_item; +/ +show errors; Index: openacs-4/packages/calendar/sql/oracle/upgrade/upgrade-2.10.0d2-2.10.0d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/oracle/upgrade/upgrade-2.10.0d2-2.10.0d3.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/calendar/sql/oracle/upgrade/upgrade-2.10.0d2-2.10.0d3.sql 22 Apr 2018 18:02:52 -0000 1.1 @@ -0,0 +1,128 @@ +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, + html_p in acs_activities.html_p%TYPE default 'f', + status_summary in acs_activities.status_summary%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, + item_type_id in cal_items.item_type_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, + package_id in acs_objects.package_id%TYPE default null, + location in acs_event.location%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, + 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, + package_id => package_id, + location => location + ); + + insert into cal_items + (cal_item_id, on_which_calendar, item_type_id) + values (v_cal_item_id, on_which_calendar, item_type_id); + + -- 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 del ( + cal_item_id in cal_items.cal_item_id%TYPE + ) + is + v_activity_id acs_events.activity_id%TYPE; + v_recurrence_id acs_events.recurrence_id%TYPE; + begin + + select activity_id, recurrence_id into v_activity_id, v_recurrence_id + from acs_events + where event_id = cal_item_id; + + -- Erase the cal_item associated with the id + delete from cal_items + where cal_item_id = cal_item.del.cal_item_id; + + -- Erase all the privileges + delete from acs_permissions + where object_id = cal_item.del.cal_item_id; + + acs_event.del(cal_item_id); + + IF instances_exist_p(recurrence_id) = 'f' THEN + -- + -- There are no more events for the activity, we can clean up + -- both, the activity and - if given - the recurrence. + -- + acs_activity.del(v_activity_id); + + IF v_recurrence_id is not null THEN + recurrence.del(v_recurrence_id); + END IF; + END IF; + + end del; + + procedure delete_all ( + recurrence_id in acs_events.recurrence_id%TYPE + ) is + v_event_id acs_events%ROWTYPE; + begin + FOR v_event_id in + (select * from acs_events + where recurrence_id = delete_all.recurrence_id) + LOOP + cal_item.del(v_event_id.event_id); + end LOOP; + + recurrence.del(recurrence_id); + end delete_all; +end cal_item; +/ +show errors 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 -r1.17 -r1.18 --- openacs-4/packages/calendar/sql/postgresql/cal-item-create.sql 29 Mar 2018 20:23:43 -0000 1.17 +++ openacs-4/packages/calendar/sql/postgresql/cal-item-create.sql 22 Apr 2018 18:02:52 -0000 1.18 @@ -97,18 +97,52 @@ '; create index cal_items_on_which_calendar_idx on cal_items (on_which_calendar); - + + ------------------------------------------------------------- +CREATE TABLE cal_uids ( + -- primary key + cal_uid text + constraint cal_uid_pk + primary key, + on_which_activity integer + constraint cal_uid_fk + not null + references acs_activities + on delete cascade, + ical_vars varchar +); + +comment on table cal_uids is ' + Table cal_uids maps a unique (external) key to an + activity. This is needed for syncing calendars via + ical; the field uid should go into acs_activities +'; + + +comment on column cal_uids.cal_uid is ' + Primary Key +'; + +comment on column cal_uids.on_which_activity is ' + Reference to an activity, for which the key is used +'; + +comment on column cal_uids.ical_vars is ' + List with attributes and values from external ical calendar programs +'; + + +------------------------------------------------------------- -- create package cal_item ------------------------------------------------------------- -- --- procedure cal_item__new/14 +-- procedure cal_item__new/15-16 -- -select define_function_args('cal_item__new','cal_item_id;null,on_which_calendar;null,name,description,html_p;null,status_summary;null,timespan_id;null,activity_id;null,recurrence_id;null,object_type;"cal_item",context_id;null,creation_date;now(),creation_user;null,creation_ip;null'); +select define_function_args('cal_item__new','cal_item_id;null,on_which_calendar;null,name,description,html_p;null,status_summary;null,timespan_id;null,activity_id;null,recurrence_id;null,object_type;"cal_item",context_id;null,creation_date;now(),creation_user;null,creation_ip;null,package_id;null,location;null'); - create or replace function cal_item__new( new__cal_item_id integer, -- default null new__on_which_calendar integer, -- default null @@ -122,42 +156,41 @@ new__object_type varchar, -- default "cal_item" new__context_id integer, -- default null new__creation_date timestamptz, -- default now() - new__creation_user -- creation_date acs_objects.creation_date%TYPE - integer, -- default null - new__creation_ip varchar -- default null - + new__creation_user integer, -- acs_objects.creation_date%TYPE default null + new__creation_ip varchar, -- default null + new__package_id integer, -- default null + new__location varchar default null ) returns integer AS $$ declare - v_cal_item_id cal_items.cal_item_id%TYPE; - + 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__html_p, -- html_p - new__status_summary, -- status_summary - 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 - ); + new__cal_item_id, -- event_id + new__name, -- name + new__description, -- description + new__html_p, -- html_p + new__status_summary, -- status_summary + 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 + new__package_id, -- package_id + new__location -- location + ); - insert into cal_items - (cal_item_id, on_which_calendar) - values - (v_cal_item_id, new__on_which_calendar); + insert into cal_items (cal_item_id, on_which_calendar) + values (v_cal_item_id, new__on_which_calendar); return v_cal_item_id; - end; $$ LANGUAGE plpgsql; + ------------------------------------------------------------ -- the delete operation ------------------------------------------------------------ @@ -171,17 +204,38 @@ delete__cal_item_id integer ) returns integer AS $$ declare + v_activity_id integer; + v_recurrence_id integer; begin - -- Erase the cal_item associated with the id + + select activity_id, recurrence_id into v_activity_id, v_recurrence_id + from acs_events + where event_id = delete__cal_item_id; + + -- Erase the cal_item associated with the id delete from cal_items where cal_item_id = delete__cal_item_id; - - -- Erase all the privileges - delete from acs_permissions - where object_id = delete__cal_item_id; + -- Erase all individual permissions Should be handled via CASCADE; + -- not sure, why this is here. + -- + -- delete from acs_permissions + -- where object_id = delete__cal_item_id; + PERFORM acs_event__delete(delete__cal_item_id); + IF NOT acs_event__instances_exist_p(v_recurrence_id) THEN + -- + -- There are no more events for the activity, we can clean up + -- both, the activity and - if given - the recurrence. + -- + PERFORM acs_activity__delete(v_activity_id); + + IF v_recurrence_id is not null THEN + PERFORM recurrence__delete(v_recurrence_id); + END IF; + END IF; + return 0; end; $$ LANGUAGE plpgsql; @@ -212,3 +266,45 @@ end; $$ LANGUAGE plpgsql; + + + + +select define_function_args('cal_uid__upsert','cal_uid,activity_id,ical_vars'); + +CREATE OR REPLACE FUNCTION cal_uid__upsert( + p_cal_uid text, + p_activity_id integer, + p_ical_vars text +) RETURNS void as +$$ +BEGIN + LOOP + -- + -- We might have duplicates on the activity_id and on the cal_uid, + -- both should be unique. + -- + update cal_uids + set ical_vars = p_ical_vars + where cal_uid = p_cal_uid; + IF found THEN + return; + END IF; + -- not there, so try to insert the key + -- if someone else inserts the same key concurrently, + -- we could get a unique-key failure + BEGIN + -- Try to delete entry to avoid duplicates (might fail) + delete from cal_uids where on_which_activity = p_activity_id; + -- Insert value + insert into cal_uids + (cal_uid, on_which_activity, ical_vars) + values + (p_cal_uid, p_activity_id, p_ical_vars); + RETURN; + EXCEPTION WHEN unique_violation THEN + -- Do nothing, and loop to try the UPDATE again. + END; + END LOOP; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/calendar/sql/postgresql/upgrade/upgrade-2.10.0d0-2.10.0d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/postgresql/upgrade/upgrade-2.10.0d0-2.10.0d1.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/calendar/sql/postgresql/upgrade/upgrade-2.10.0d0-2.10.0d1.sql 22 Apr 2018 18:02:52 -0000 1.1 @@ -0,0 +1,60 @@ + +CREATE TABLE IF NOT EXISTS cal_uids ( + -- primary key + cal_uid text + constraint cal_uid_pk + primary key, + on_which_activity integer + constraint cal_uid_fk + not null + references acs_activities + on delete cascade, + ical_vars text +); + +--- +--- The new ical_vars are now triples, contaning the tag name, the tag +--- parameters and the value. Previously it wer just pairs. +--- +UPDATE cal_uids SET ical_vars = NULL; + + +select define_function_args('cal_uid__upsert','cal_uid,activity_id,ical_vars'); + +CREATE OR REPLACE FUNCTION cal_uid__upsert( + p_cal_uid text, + p_activity_id integer, + p_ical_vars text +) RETURNS void as +$$ +BEGIN + LOOP + -- + -- We might have duplicates on the activity_id and on the cal_uid, + -- both should be unique. + -- + update cal_uids + set ical_vars = p_ical_vars + where cal_uid = p_cal_uid; + IF found THEN + return; + END IF; + -- not there, so try to insert the key + -- if someone else inserts the same key concurrently, + -- we could get a unique-key failure + BEGIN + -- Try to delete entry to avoid duplicates (might fail) + delete from cal_uids where on_which_activity = p_activity_id; + -- Insert value + insert into cal_uids + (cal_uid, on_which_activity, ical_vars) + values + (p_cal_uid, p_activity_id, p_ical_vars); + RETURN; + EXCEPTION WHEN unique_violation THEN + -- Do nothing, and loop to try the UPDATE again. + END; + END LOOP; +END; +$$ LANGUAGE plpgsql; + Index: openacs-4/packages/calendar/sql/postgresql/upgrade/upgrade-2.10.0d1-2.10.0d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/postgresql/upgrade/upgrade-2.10.0d1-2.10.0d2.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/calendar/sql/postgresql/upgrade/upgrade-2.10.0d1-2.10.0d2.sql 22 Apr 2018 18:02:52 -0000 1.1 @@ -0,0 +1,55 @@ +-- +-- procedure cal_item__new/15-16 +-- +select define_function_args('cal_item__new','cal_item_id;null,on_which_calendar;null,name,description,html_p;null,status_summary;null,timespan_id;null,activity_id;null,recurrence_id;null,object_type;"cal_item",context_id;null,creation_date;now(),creation_user;null,creation_ip;null,package_id;null,location;null'); + +DROP function if exists +cal_item__new(integer, integer, character varying, character varying, boolean, character varying, integer, integer, integer, character varying, integer, timestamp with time zone, integer, character varying); +DROP function if exists +cal_item__new(integer, integer, character varying, character varying, boolean, character varying, integer, integer, integer, character varying, integer, timestamp with time zone, integer, character varying, integer); + +create or replace function cal_item__new( + new__cal_item_id integer, -- default null + new__on_which_calendar integer, -- default null + new__name varchar, + new__description varchar, + new__html_p boolean, -- default null + new__status_summary varchar, -- default null + new__timespan_id integer, -- default null + new__activity_id integer, -- default null + new__recurrence_id integer, -- default null + new__object_type varchar, -- default "cal_item" + new__context_id integer, -- default null + new__creation_date timestamptz, -- default now() + new__creation_user integer, -- acs_objects.creation_date%TYPE default null + new__creation_ip varchar, -- default null + new__package_id integer, -- default null + new__location varchar default null +) returns integer AS $$ +declare + 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__html_p, -- html_p + new__status_summary, -- status_summary + 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 + new__package_id, -- package_id + new__location -- location + ); + + insert into cal_items (cal_item_id, on_which_calendar) + values (v_cal_item_id, new__on_which_calendar); + + return v_cal_item_id; +end; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/calendar/sql/postgresql/upgrade/upgrade-2.10.0d2-2.10.0d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/sql/postgresql/upgrade/upgrade-2.10.0d2-2.10.0d3.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/calendar/sql/postgresql/upgrade/upgrade-2.10.0d2-2.10.0d3.sql 22 Apr 2018 18:02:52 -0000 1.1 @@ -0,0 +1,45 @@ + +-- +-- procedure cal_item__delete/1 +-- +select define_function_args('cal_item__delete','cal_item_id'); + +create or replace function cal_item__delete( + delete__cal_item_id integer +) returns integer AS $$ +declare + v_activity_id integer; + v_recurrence_id integer; +begin + + select activity_id, recurrence_id into v_activity_id, v_recurrence_id + from acs_events + where event_id = delete__cal_item_id; + + -- Erase the cal_item associated with the id + delete from cal_items + where cal_item_id = delete__cal_item_id; + + -- Erase all individual permissions Should be handled via CASCADE; + -- not sure, why this is here. + -- + -- delete from acs_permissions + -- where object_id = delete__cal_item_id; + + PERFORM acs_event__delete(delete__cal_item_id); + + IF NOT acs_event__instances_exist_p(v_recurrence_id) THEN + -- + -- There are no more events for the activity, we can clean up + -- both, the activity and - if given - the recurrence. + -- + PERFORM acs_activity__delete(v_activity_id); + + IF v_recurrence_id is not null THEN + PERFORM recurrence__delete(v_recurrence_id); + END IF; + END IF; + + return 0; +end; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/calendar/tcl/cal-item-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/tcl/cal-item-procs-oracle.xql,v diff -u -r1.15 -r1.16 --- openacs-4/packages/calendar/tcl/cal-item-procs-oracle.xql 23 Nov 2017 08:43:16 -0000 1.15 +++ openacs-4/packages/calendar/tcl/cal-item-procs-oracle.xql 22 Apr 2018 18:02:52 -0000 1.16 @@ -61,7 +61,9 @@ item_type_id => :item_type_id, creation_user => :creation_user, creation_ip => :creation_ip, - context_id => :calendar_id + context_id => :calendar_id, + package_id => :package_id, + location => :location ); end; @@ -94,6 +96,17 @@ + + + + + begin + cal_uid.upsert(:cal_uid, :activity_id, :ical_vars); + end; + + + + Index: openacs-4/packages/calendar/tcl/cal-item-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/tcl/cal-item-procs-postgresql.xql,v diff -u -r1.23 -r1.24 --- openacs-4/packages/calendar/tcl/cal-item-procs-postgresql.xql 23 Nov 2017 08:43:16 -0000 1.23 +++ openacs-4/packages/calendar/tcl/cal-item-procs-postgresql.xql 22 Apr 2018 18:02:52 -0000 1.24 @@ -65,7 +65,9 @@ :calendar_id, now(), :creation_user, - :creation_ip + :creation_ip, + :package_id, + :location ) @@ -113,4 +115,11 @@ + + + select cal_uid__upsert(:cal_uid, :activity_id, :ical_vars) from dual + + + + Index: openacs-4/packages/calendar/tcl/cal-item-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/tcl/cal-item-procs.tcl,v diff -u -r1.28 -r1.29 --- openacs-4/packages/calendar/tcl/cal-item-procs.tcl 20 Mar 2018 07:59:11 -0000 1.28 +++ openacs-4/packages/calendar/tcl/cal-item-procs.tcl 22 Apr 2018 18:02:52 -0000 1.29 @@ -21,13 +21,7 @@ } { A sanity check that the start time is before the end time. } { - set dates_valid_p [db_string dates_valid_p_select {}] - - if {$dates_valid_p eq "1"} { - return 1 - } else { - return 0 - } + return [db_string dates_valid_p_select {}] } ad_proc -public calendar::item::new { @@ -37,12 +31,29 @@ {-description:required} {-calendar_id ""} {-item_type_id ""} + {-package_id ""} + {-location ""} + {-cal_uid ""} + {-ical_vars ""} } { + Insert a new calendar item into the database +} { + if {$package_id eq ""} { + set package_id [ad_conn package_id] + } if {[dates_valid_p -start_date $start_date -end_date $end_date]} { set creation_ip [ad_conn peeraddr] set creation_user [ad_conn user_id] set activity_id [db_exec_plsql insert_activity {} ] + + # + # In case we have a cal_uid, save it in the cal_uids table + # together with the ical_vars. + # + if {$cal_uid ne ""} { + db_dml insert_cal_uid {} + } # Convert from user timezone to system timezone if { $start_date ne $end_date } { @@ -59,7 +70,7 @@ # create the cal_item # we are leaving the name and description fields in acs_event # blank to abide by the definition that an acs_event is an acs_activity - # with added on temperoal information + # with added on temporal information # by default, the cal_item permissions # are going to be inherited from the calendar permissions @@ -88,13 +99,15 @@ ad_proc -public calendar::item::get { {-cal_item_id:required} - {-array:required} + {-array} {-normalize_time_to_utc 0} } { Get the data for a calendar item } { - upvar $array row + if {[info exists array]} { + upvar $array row + } if { [catch { set attachments_enabled_p [calendar::attachments_enabled_p] }] } { set attachments_enabled_p 0 } @@ -108,10 +121,10 @@ db_1row $query_name {} -column_array row if {$normalize_time_to_utc} { set row(start_date_ansi) [lc_time_local_to_utc $row(start_date_ansi)] - set row(end_date_ansi) [lc_time_local_to_utc $row(end_date_ansi)] + set row(end_date_ansi) [lc_time_local_to_utc $row(end_date_ansi)] } else { set row(start_date_ansi) [lc_time_system_to_conn $row(start_date_ansi)] - set row(end_date_ansi) [lc_time_system_to_conn $row(end_date_ansi)] + set row(end_date_ansi) [lc_time_system_to_conn $row(end_date_ansi)] } if { $row(start_date_ansi) eq $row(end_date_ansi) } { @@ -124,17 +137,19 @@ set row(start_time) [lc_time_fmt $row(start_date_ansi) "%X"] # Unfortunately, SQL has weekday starting at 1 = Sunday - set row(start_date) [lc_time_fmt $row(start_date_ansi) "%Y-%m-%d"] - set row(end_date) [lc_time_fmt $row(end_date_ansi) "%Y-%m-%d"] + set row(start_date) [lc_time_fmt $row(start_date_ansi) "%Y-%m-%d"] + set row(end_date) [lc_time_fmt $row(end_date_ansi) "%Y-%m-%d"] - set row(day_of_week) [expr {[lc_time_fmt $row(start_date_ansi) "%w"] + 1}] - set row(pretty_day_of_week) [lc_time_fmt $row(start_date_ansi) "%A"] - set row(day_of_month) [lc_time_fmt $row(start_date_ansi) "%d"] + set row(day_of_week) [expr {[lc_time_fmt $row(start_date_ansi) "%w"] + 1}] + set row(pretty_day_of_week) [lc_time_fmt $row(start_date_ansi) "%A"] + set row(day_of_month) [lc_time_fmt $row(start_date_ansi) "%d"] set row(pretty_short_start_date) [lc_time_fmt $row(start_date_ansi) "%x"] - set row(full_start_date) [lc_time_fmt $row(start_date_ansi) "%x"] - set row(full_end_date) [lc_time_fmt $row(end_date_ansi) "%x"] + set row(full_start_date) [lc_time_fmt $row(start_date_ansi) "%x"] + set row(full_end_date) [lc_time_fmt $row(end_date_ansi) "%x"] set row(end_time) [lc_time_fmt $row(end_date_ansi) "%X"] + + return [array get row] } ad_proc -public calendar::item::add_recurrence { @@ -169,6 +184,9 @@ {-edit_all_p 0} {-edit_past_events_p 1} {-calendar_id ""} + {-location ""} + {-cal_uid ""} + {-ical_vars ""} } { Edit the item @@ -179,6 +197,7 @@ # If the recurrence id is NULL, then we stop here and just do the normal update if {$recurrence_id ne ""} { + ns_log notice "recurrence_id $recurrence_id" calendar::item::edit_recurrence \ -event_id $cal_item_id \ -start_date $start_date \ @@ -200,7 +219,7 @@ # otherwise, keep the start and end time as 00:00 set start_date [lc_time_conn_to_system $start_date] - set end_date [lc_time_conn_to_system $end_date] + set end_date [lc_time_conn_to_system $end_date] } db_dml update_event {} @@ -210,6 +229,22 @@ db_1row get_interval_id {} db_transaction { + # + # If a cal_uid is given, update the attributes in the + # cal_uid mapping table + # + if {$cal_uid ne ""} { + # + # We have to determine the activity id for the upsert + # operation in cal_uids. + # + set activity_id [db_string select_activity_id { + select activity_id from acs_events where event_id = :cal_item_id + }] + #ns_log notice "======= cal_uid_upsert with activity_id $activity_id" + db_exec_plsql cal_uid_upsert {} + } + # call edit procedure db_exec_plsql update_interval {} @@ -226,11 +261,11 @@ } } - db_dml update_item_type_id " - update cal_items - set [join $colspecs ", "] - where cal_item_id= :cal_item_id - " + db_dml update_item_type_id [subst { + update cal_items + set [join $colspecs ", "] + where cal_item_id= :cal_item_id + }] calendar::do_notifications -mode Edited -cal_item_id $cal_item_id } Index: openacs-4/packages/calendar/tcl/cal-item-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/calendar/tcl/cal-item-procs.xql,v diff -u -r1.9 -r1.10 --- openacs-4/packages/calendar/tcl/cal-item-procs.xql 23 Nov 2017 08:43:16 -0000 1.9 +++ openacs-4/packages/calendar/tcl/cal-item-procs.xql 22 Apr 2018 18:02:52 -0000 1.10 @@ -1,11 +1,20 @@ + + + insert into cal_uids + (cal_uid, on_which_activity, ical_vars) + values + (:cal_uid, :activity_id, :ical_vars) + + + select CASE WHEN cast(:start_date as timestamp with time zone) <= cast(:end_date as timestamp with time zone) THEN 1 - ELSE -1 + ELSE 0 END from dual @@ -28,7 +37,8 @@ c.package_id as calendar_package_id, e.related_link_url, e.related_link_text, - e.redirect_to_rel_link_p + e.redirect_to_rel_link_p, + e.location from acs_events e join timespans s on (e.timespan_id = s.timespan_id) @@ -67,7 +77,8 @@ c.package_id as calendar_package_id, e.related_link_url, e.related_link_text, - e.redirect_to_rel_link_p + e.redirect_to_rel_link_p, + e.location from acs_events e join timespans s on (e.timespan_id = s.timespan_id) @@ -185,8 +196,9 @@ update acs_events set name = :name, - description = :description - where event_id= :cal_item_id + description = :description, + location = :location + where event_id = :cal_item_id