-- Data model to support content repository of the ArsDigita
-- Community System

-- Copyright (C) 1999-2000 ArsDigita Corporation
-- Author: Stanislav Freidin (sfreidin@arsdigita.com)

-- This is free software distributed under the terms of the GNU Public
-- License.  Full text of the license is available from the GNU Project:
-- http://www.fsf.org/copyleft/gpl.html

create or replace package body content_keyword
as

function get_heading (
  keyword_id  in cr_keywords.keyword_id%TYPE
) return varchar2
is
  v_heading varchar2(4000);
begin

  select heading into v_heading from cr_keywords
    where keyword_id = content_keyword.get_heading.keyword_id;

  return v_heading;
end get_heading;

function get_description (
  keyword_id  in cr_keywords.keyword_id%TYPE
) return varchar2
is
  v_description varchar2(4000);
begin

  select description into v_description from cr_keywords
    where keyword_id = content_keyword.get_description.keyword_id;

  return v_description;
end get_description;

procedure set_heading (
  keyword_id  in cr_keywords.keyword_id%TYPE,
  heading     in cr_keywords.heading%TYPE
)
is
begin

  update cr_keywords set 
    heading = set_heading.heading
  where
    keyword_id = set_heading.keyword_id;

end set_heading;

procedure set_description (
  keyword_id  in cr_keywords.keyword_id%TYPE,
  description in cr_keywords.description%TYPE
)
is
begin

  update cr_keywords set 
    description = set_description.description
  where
    keyword_id = set_description.keyword_id;
end set_description;

function is_leaf (
  keyword_id  in cr_keywords.keyword_id%TYPE
) return varchar2
is
  v_leaf varchar2(1);

  cursor c_leaf_cur is
    select
      'f'
    from 
      cr_keywords k
    where
      k.parent_id = is_leaf.keyword_id;

begin

  open c_leaf_cur;
  fetch c_leaf_cur into v_leaf;
  if c_leaf_cur%NOTFOUND then
    v_leaf := 't';
  end if;
  close c_leaf_cur;

  return v_leaf;
end is_leaf;

function new (
  heading       in cr_keywords.heading%TYPE,
  description   in cr_keywords.description%TYPE default null,
  parent_id     in cr_keywords.parent_id%TYPE default null,
  keyword_id    in cr_keywords.keyword_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,
  object_type   in acs_object_types.object_type%TYPE default 'content_keyword'
) return cr_keywords.keyword_id%TYPE
is
  v_id integer;
begin

  v_id := acs_object.new (object_id => keyword_id,
                          context_id => parent_id,
                          object_type => object_type,
                          creation_date => creation_date, 
                          creation_user => creation_user, 
                          creation_ip => creation_ip);
    
  insert into cr_keywords 
    (heading, description, keyword_id, parent_id)
  values
    (heading, description, v_id, parent_id);

  return v_id;
end new;

procedure del (
  keyword_id  in cr_keywords.keyword_id%TYPE
)
is
  v_item_id integer;
  cursor c_rel_cur is
    select item_id from cr_item_keyword_map 
    where keyword_id = content_keyword.del.keyword_id;
begin

  open c_rel_cur;
  loop
    fetch c_rel_cur into v_item_id;
    exit when c_rel_cur%NOTFOUND;
    item_unassign(v_item_id, content_keyword.del.keyword_id);
  end loop;
  close c_rel_cur;

  acs_object.del(keyword_id);
end del;

procedure item_assign (
  item_id       in cr_items.item_id%TYPE,
  keyword_id    in cr_keywords.keyword_id%TYPE, 
  context_id	in acs_objects.context_id%TYPE default null,
  creation_user in acs_objects.creation_user%TYPE default null,
  creation_ip   in acs_objects.creation_ip%TYPE default null
) 
is
  v_dummy integer;
begin
  
  -- Do nothing if the keyword is assigned already
  select decode(count(*),0,0,1) into v_dummy from dual 
    where exists (select 1 from cr_item_keyword_map
                   where item_id=item_assign.item_id 
                   and keyword_id=item_assign.keyword_id);

  if v_dummy > 0 then
    -- previous assignment exists 
    return;
  end if;

  insert into cr_item_keyword_map (
    item_id, keyword_id
  ) values (
    item_id, keyword_id
  );

end item_assign;

procedure item_unassign (
  item_id     in cr_items.item_id%TYPE,
  keyword_id  in cr_keywords.keyword_id%TYPE 
) is
begin

  delete from cr_item_keyword_map
    where item_id = item_unassign.item_id 
    and keyword_id = item_unassign.keyword_id;

end item_unassign;

function is_assigned (
  item_id     in cr_items.item_id%TYPE,
  keyword_id  in cr_keywords.keyword_id%TYPE,
  recurse     in varchar2 default 'none'
) return varchar2
is
  v_ret varchar2(1);
begin

  -- Look for an exact match
  if recurse = 'none' then
    declare
    begin
      select 't' into v_ret from cr_item_keyword_map
        where item_id = is_assigned.item_id
        and   keyword_id = is_assigned.keyword_id;
      return 't';
    exception when no_data_found then
      return 'f';    
    end;
  end if;

  -- Look from specific to general
  if recurse = 'up' then
    begin
      select 't' into v_ret from dual where exists (select 1 from
	(select keyword_id from cr_keywords
	   connect by parent_id = prior keyword_id
	   start with keyword_id = is_assigned.keyword_id
	 ) t, cr_item_keyword_map m
      where
	t.keyword_id = m.keyword_id
      and
	m.item_id = is_assigned.item_id);

      return 't';

    exception when no_data_found then
      return 'f';    
    end;
  end if;

  if recurse = 'down' then
    begin
      select 't' into v_ret from dual where exists ( select 1 from
	(select keyword_id from cr_keywords
	   connect by prior parent_id = keyword_id
	   start with keyword_id = is_assigned.keyword_id
	 ) t, cr_item_keyword_map m
      where
	t.keyword_id = m.keyword_id
      and
	m.item_id = is_assigned.item_id);

      return 't';

    exception when no_data_found then
      return 'f';    
    end;
  end if;  

  -- Tried none, up and down - must be an invalid parameter
  raise_application_error (-20000, 'The recurse parameter to ' || 
     'content_keyword.is_assigned should be ''none'', ''up'' or ''down''.');

end is_assigned;

function get_path (
  keyword_id in cr_keywords.keyword_id%TYPE
) return varchar2
is
  v_path     varchar2(4000) := '';
  v_is_found varchar2(1)    := 'f';
  
  cursor c_keyword_cur is 
    select 
      heading
    from (
      select 
        heading, level as tree_level
      from cr_keywords
        connect by prior parent_id = keyword_id
        start with keyword_id = get_path.keyword_id
    ) 
    order by 
      tree_level desc;

  v_heading cr_keywords.heading%TYPE;
begin

  open c_keyword_cur;
  loop
    fetch c_keyword_cur into v_heading;
    exit when c_keyword_cur%NOTFOUND;
    v_is_found := 't';
    v_path := v_path || '/' || v_heading;
  end loop;
  close c_keyword_cur;

  if v_is_found = 'f' then
    return null;
  else
    return v_path;
  end if;
end get_path;

end content_keyword;
/
show errors


-- Ensure that the context_id in acs_objects is always set to the
-- parent_id in cr_keywords

create or replace trigger cr_keywords_update_tr
after update of parent_id on cr_keywords for each row
begin
  update acs_objects set context_id = :new.parent_id
    where object_id = :new.keyword_id;
end cr_keywords_update_tr;
/
show errors