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
 
 
 
 
 
-
-