Index: openacs-4/packages/spam/sql/oracle/spam-packages.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/spam/sql/oracle/spam-packages.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/spam/sql/oracle/spam-packages.sql	20 Apr 2001 20:51:25 -0000	1.1
+++ openacs-4/packages/spam/sql/oracle/spam-packages.sql	4 Sep 2001 21:14:59 -0000	1.2
@@ -22,6 +22,7 @@
 	send_date     in date
     ) return acs_objects.object_id%TYPE;
 
+
     procedure edit (
         spam_id       in spam_messages.spam_id%TYPE,
         title         in acs_mail_bodies.header_subject%TYPE        default null,
@@ -31,16 +32,23 @@
  	send_date     in date
     );
 
+
     function new_content (
         context_id    in acs_objects.context_id%TYPE,
         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,
         object_type   in acs_objects.object_type%TYPE   default 'acs_content',
-	mime_type     in acs_contents.mime_type%TYPE    default 'text/plain',
-	text	      in varchar2			default null
-    ) return acs_objects.object_id%TYPE;
+	mime_type     in cr_revisions.mime_type%TYPE    default 'text/plain',
+	p_text	      in varchar2			default null,
 
+        -- TilmannS: the body_id of the spam message that this content 
+        -- item will be associated with. Added to provide unique name 
+        -- for the cr
+        p_body_id     in acs_mail_bodies.body_id%TYPE
+
+    ) return acs_objects.object_id%TYPE;
+ 
     procedure approve (
 	spam_id    in spam_messages.spam_id%TYPE
     );
@@ -72,10 +80,10 @@
     IS
 	v_link_id	acs_objects.object_id%TYPE;
 	v_body_id	acs_objects.object_id%TYPE;
-	v_content_obj   acs_mail_bodies.content_object_id%TYPE;
-	tmp_blob 	BLOB;
-	v_html_id 	acs_mail_bodies.content_object_id%TYPE;
-	v_text_id      acs_mail_bodies.content_object_id%TYPE;
+	v_content_obj   acs_mail_bodies.content_item_id%TYPE;
+	v_html_id 	acs_mail_bodies.content_item_id%TYPE;
+	v_text_id       acs_mail_bodies.content_item_id%TYPE;
+        v_dummy         integer;
     begin
 	v_body_id := acs_mail_body.new (
 		body_id => null,
@@ -87,7 +95,7 @@
 		creation_date => creation_date,
 		creation_user => creation_user,
 		creation_ip => creation_ip, 
-		object_type => object_type
+		object_type => 'acs_mail_body'
 	);
 
 	if plain_text is not null then
@@ -98,7 +106,8 @@
 			creation_ip => creation_ip, 
 			object_type => object_type,
 			mime_type => 'text/plain',
-			text => plain_text
+			p_text => plain_text,
+                        p_body_id => v_body_id
 		);
 	end if;
 
@@ -110,7 +119,8 @@
 			creation_ip => creation_ip, 
 			object_type => object_type,
 			mime_type => 'text/html',
-			text => html_text
+			p_text => html_text,
+                        p_body_id => v_body_id
 		);
 	end if;
 
@@ -129,8 +139,8 @@
 			creation_ip => creation_ip, 
 			object_type => object_type
 		);
-		acs_mail_multipart.add_content(v_content_obj, v_text_id);
-		acs_mail_multipart.add_content(v_content_obj, v_html_id);
+                v_dummy := acs_mail_multipart.add_content(v_content_obj, v_text_id);
+		v_dummy := acs_mail_multipart.add_content(v_content_obj, v_html_id);
 	elsif plain_text is not null then
 		v_content_obj := v_text_id;
 	elsif html_text is not null then
@@ -168,10 +178,13 @@
 	v_creation_ip	acs_objects.creation_ip%TYPE;
 	v_creation_date	acs_objects.creation_date%TYPE;
 	v_body_id	acs_objects.object_id%TYPE;
-	v_content_obj   acs_mail_bodies.content_object_id%TYPE;
-	tmp_blob 	BLOB;
-	v_html_id 	acs_mail_bodies.content_object_id%TYPE;
-	v_text_id      acs_mail_bodies.content_object_id%TYPE;
+	v_content_obj   acs_mail_bodies.content_item_id%TYPE;
+	v_html_id 	acs_mail_bodies.content_item_id%TYPE;
+	v_text_id       acs_mail_bodies.content_item_id%TYPE;
+        v_dummy         integer;
+
+        v_parts_row     acs_mail_multipart_parts%ROWTYPE;
+
     begin
 	select 
 		body_id, context_id, creation_user, creation_date, creation_ip 
@@ -185,34 +198,37 @@
 	and 	spam_id = edit.spam_id
 	and 	object_id = edit.spam_id;
 
+	select content_item_id into v_content_obj 
+		from acs_mail_bodies
+		where body_id = v_body_id;
+
 	update acs_mail_bodies
-		set content_object_id = null,
+		set content_item_id = null,
 		header_subject = edit.title
 	where body_id = v_body_id;
 
 	-- now we have the body id of the spam message.
 	-- nuke any content associated with it.
-	select content_object_id into v_content_obj 
-		from acs_mail_bodies
-		where body_id = v_body_id;
+
 	if acs_mail_multipart.multipart_p(v_content_obj) = 't' then
 		-- we have a multipart
 		-- and thus have to nuke the components.
-		select content_id into v_text_id 
-			from acs_contents, acs_mail_multipart_parts
-			where multipart_id = v_content_obj
-			and content_id = content_object_id
-			and mime_type = 'text/plain';
-		select content_id into v_html_id 
-			from acs_contents, acs_mail_multipart_parts
-			where multipart_id = v_content_obj
-			and content_id = content_object_id
-			and mime_type = 'text/html';
-		acs_content.delete(v_text_id);
-		acs_content.delete(v_html_id);
-		acs_mail_multipart.delete(v_content_obj);
+
+                for v_parts_row in (select content_item_id from acs_mail_multipart_parts where multipart_id=v_content_obj) loop
+
+                        -- cannot delete content_item as long as 
+                        -- acs_mail_multipart_parts references it, so 
+                        -- we set it null here. There's propably a more
+                        -- elegant way to do this though. (TilmannS)
+                        update acs_mail_multipart_parts set content_item_id=null where multipart_id=v_content_obj;
+
+                        content_item.delete(v_parts_row.content_item_id);
+                end loop;
+
+     		acs_mail_multipart.delete(v_content_obj);
+
 	else 
-		acs_content.delete(v_content_obj);
+		content_item.delete(v_content_obj);
 	end if;
 
 	if plain_text is not null then
@@ -222,7 +238,8 @@
 			creation_user => v_creation_user,
 			creation_ip => v_creation_ip, 
 			mime_type => 'text/plain',
-			text => plain_text
+			p_text => plain_text,
+                        p_body_id => v_body_id
 		);
 	end if;
 
@@ -233,7 +250,8 @@
 			creation_user => v_creation_user,
 			creation_ip => v_creation_ip, 
 			mime_type => 'text/html',
-			text => html_text
+			p_text => html_text,
+                        p_body_id => v_body_id
 		);
 	end if;
 
@@ -251,17 +269,15 @@
 			creation_user => v_creation_user,
 			creation_ip => v_creation_ip
 		);
-		acs_mail_multipart.add_content(v_content_obj, v_text_id);
-		acs_mail_multipart.add_content(v_content_obj, v_html_id);
+		v_dummy := acs_mail_multipart.add_content(v_content_obj, v_text_id);
+		v_dummy := acs_mail_multipart.add_content(v_content_obj, v_html_id);
 	elsif plain_text is not null then
 		v_content_obj := v_text_id;
 	elsif html_text is not null then
 		v_content_obj := v_html_id;
 	end if;
 	acs_mail_body.set_content_object(v_body_id, v_content_obj);
-
 	
-	
 	update spam_messages	
 		set sql_query = edit.sql_query,
 		    send_date = edit.send_date
@@ -275,27 +291,31 @@
         creation_user in acs_objects.creation_user%TYPE default null,
         creation_ip   in acs_objects.creation_ip%TYPE   default null,
         object_type   in acs_objects.object_type%TYPE   default null,
-	mime_type     in acs_contents.mime_type%TYPE    default 'text/plain',
-	text	      in varchar2			default null
+	mime_type     in cr_revisions.mime_type%TYPE    default 'text/plain',
+	p_text	      in varchar2			default null,
+        p_body_id     in acs_mail_bodies.body_id%TYPE
+
     ) return acs_objects.object_id%TYPE
     is 
-	tmp_blob BLOB;
-	v_id integer;
+	v_id            integer;
+        v_rev_id        integer;
     begin
-	v_id := acs_mail_gc_object.new (
-		context_id => context_id,
-		creation_date => creation_date,
-		creation_user => creation_user,
-		creation_ip => creation_ip
+	v_id := content_item.new (
+       		name => ('spam_message body_id-' || p_body_id || ' ' || mime_type),
+                creation_date   => creation_date,
+                creation_user   => creation_user,
+                creation_ip     => creation_ip,
+		mime_type       => mime_type,
+                text            => p_text,
+                storage_type    => 'text'
 	);
-	v_id := acs_content.new (
-		content_id => v_id,
-		mime_type => mime_type
-	);
-	select content into tmp_blob from acs_contents 
-		where content_id = v_id;
-	string_to_blob(text, tmp_blob);
+
+        -- set the new revision live
+        v_rev_id := content_item.get_latest_revision(v_id);
+        content_item.set_live_revision(v_rev_id);
+
 	return v_id;
+
     end new_content;
 
    procedure approve(spam_id IN spam_messages.spam_id%TYPE)
