Index: openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/oracle/upgrade/upgrade-1.0a-1.5.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/oracle/upgrade/upgrade-1.0a-1.5.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/oracle/upgrade/upgrade-1.0a-1.5.sql	22 Jun 2002 04:10:00 -0000	1.1
@@ -0,0 +1,77 @@
+-- Added a simple function to attach a file to a ticket 
+-- (From Jun Yamog) Ported to Oracle by vinodk
+--
+-- @author vinod@kurup.com
+-- @created 2002-03-19
+
+create or replace function ttl__create_file (
+	p_package_id		   in apm_packages.package_id%TYPE, 
+	p_user_id			   in users.user_id%TYPE, 
+	p_name				   in cr_items.name%TYPE, 
+	p_parent_id			   in cr_items.parent_id%TYPE, 
+	p_mime_type			   in cr_mime_types.mime_type%TYPE,
+	p_file_extension	   in cr_mime_types.file_extension%TYPE
+) return cr_revisions.revision_id%TYPE
+as
+	v_item_id			cr_items.item_id%TYPE;
+	v_revision_id		cr_revisions.revision_id%TYPE;
+	v_mime_type			cr_mime_types.mime_type%TYPE;
+begin
+
+	begin
+		-- detect mime type if mime is not yet in cr_mime_type and add it
+		select mime_type into v_mime_type from cr_mime_types
+		  where  mime_type = p_mime_type;
+ 
+		exception
+		  when NO_DATA_FOUND then
+			insert into cr_mime_types 
+			(mime_type, file_extension) 
+			values 
+			(p_mime_type, p_file_extension);
+	end;
+
+	-- if the file already exists then nuke it. this become essentially write
+	select max(item_id) into v_item_id from cr_items where name = p_name;
+
+	if v_item_id is not null then
+	   content_item.delete(v_item_id);
+	end if;
+
+	-- vinodk: this needs to be rewritten to use CR API
+	-- oracle - use lob instead of file
+
+	-- create a new cr_item
+	v_item_id := acs_object.new (
+		null, 
+		'content_item', 
+		sysdate, 
+		p_user_id, 
+		null, 
+		p_package_id
+	);
+
+	insert into cr_items 
+	(item_id, parent_id, name, content_type, storage_type) 
+	values 
+	(v_item_id, p_parent_id, p_name, 'content_revision', 'lob');
+
+	-- create a new cr_revision
+	v_revision_id := acs_object.new (
+		null, 
+		'content_revision', 
+		sysdate, 
+		p_user_id, 
+		null, 
+		null
+	);
+
+	insert into cr_revisions 
+	(revision_id, item_id, mime_type) 
+	values 
+	(v_revision_id, v_item_id, p_mime_type);
+
+	return v_revision_id;
+end;
+/
+show errors;
Index: openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/postgresql/upgrade/upgrade-1.0a-1.5.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/postgresql/upgrade/upgrade-1.0a-1.5.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/postgresql/upgrade/upgrade-1.0a-1.5.sql	22 Jun 2002 04:10:00 -0000	1.1
@@ -0,0 +1,50 @@
+-- Added a simple function to attach a file to a ticket 
+-- (From Jun Yamog) Ported to Oracle by vinodk
+--
+-- @author vinod@kurup.com
+-- @created 2002-03-19
+
+create function ttl__create_file(integer, integer, varchar, integer, varchar, varchar)
+returns integer as '
+declare
+  p_package_id alias for $1;
+  p_user_id alias for $2;
+  p_name alias for $3;
+  p_parent_id alias for $4;
+  p_mime_type alias for $5;
+  p_file_extension alias for $6;
+  v_item_id integer;
+  v_revision_id integer;
+  v_mime_type varchar;
+begin
+
+-- detect mime type if mime is not yet in cr_mime_type and add it
+  select mime_type into v_mime_type from cr_mime_types
+    	where  mime_type = p_mime_type;
+ 
+  if not found then
+    insert into cr_mime_types (mime_type, file_extension) values (p_mime_type, p_file_extension);
+  end if;
+
+-- if the file already exists then nuke it. this become essentially write
+  select item_id into v_item_id from cr_items where name = p_name;
+  if found then 
+    perform content_item__delete(v_item_id);
+  end if;
+
+-- create a new cr_item
+  v_item_id := acs_object__new(null, ''content_item'', now(), p_user_id, null, p_package_id);
+  insert into cr_items (
+    item_id, parent_id, name, content_type, storage_type
+  ) values (
+    v_item_id, p_parent_id, p_name, ''content_revision'', ''file''
+  );
+
+-- create a new cr_revision
+  v_revision_id := acs_object__new(null, ''content_revision'', now(), p_user_id, null, null);
+  insert into cr_revisions (revision_id, item_id, mime_type) 
+  values (v_revision_id, v_item_id, p_mime_type);
+
+  return v_revision_id;
+end;
+' language 'plpgsql';