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