Index: openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.2.1d1-5.2.1d2.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.2.1d1-5.2.1d2.sql,v
diff -u -r1.1.2.1 -r1.1.2.2
--- openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.2.1d1-5.2.1d2.sql 29 Dec 2005 01:24:03 -0000 1.1.2.1
+++ openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.2.1d1-5.2.1d2.sql 4 Jan 2006 01:29:25 -0000 1.1.2.2
@@ -1810,3 +1810,490 @@
end cr_items_publish_update_tr;
/
show errors
+
+-- Refresh the attribute triggers
+
+begin
+
+ for type_rec in (select object_type,table_name from acs_object_types
+ connect by supertype = prior object_type
+ start with object_type = 'content_revision') loop
+ if table_exists(type_rec.table_name) then
+ content_type.refresh_view(type_rec.object_type);
+ content_type.refresh_trigger(type_rec.object_type);
+ content_type.refresh_view(type_rec.object_type);
+ end if;
+ end loop;
+
+end;
+/
+show errors;
+
+-- recreate content keyword package for package_id
+
+create or replace package content_keyword
+as
+
+function new (
+ --/** Creates a new keyword (also known as "subject category").
+ -- @author Karl Goldstein
+ -- @param heading The heading for the new keyword
+ -- @param description The description for the new keyword
+ -- @param parent_id The parent of this keyword, defaults to null.
+ -- @param keyword_id The id of the new keyword. A new id will be allocated if this
+ -- parameter is null
+ -- @param object_type The type for the new keyword, defaults to 'content_keyword'.
+ -- This parameter may be used by subclasses of
+ -- content_keyword to initialize the superclass.
+ -- @param creation_date As in acs_object.new
+ -- @param creation_ip As in acs_object.new
+ -- @param creation_user As in acs_object.new
+ -- @param package_id As in acs_object.new
+ -- @return The id of the newly created keyword
+ -- @see {acs_object.new}, {content_item.new}, {content_keyword.item_assign},
+ -- {content_keyword.delete}
+ --*/
+ 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',
+ package_id in acs_objects.package_id%TYPE
+) return cr_keywords.keyword_id%TYPE;
+
+procedure del (
+ --/** Deletes the specified keyword, which must be a leaf. Unassigns the
+ -- keyword from all content items. Use with caution - this
+ -- operation cannot be undone.
+ -- @author Karl Goldstein
+ -- @param keyword_id The id of the keyword to be deleted
+ -- @see {acs_object.delete}, {content_keyword.item_unassign}
+ --*/
+ keyword_id in cr_keywords.keyword_id%TYPE
+);
+
+function get_heading (
+ --/** Retrieves the heading of the content keyword
+ -- @author Karl Goldstein
+ -- @param keyword_id The keyword id
+ -- @return The heading for the specified keyword
+ -- @see {content_keyword.set_heading}, {content_keyword.get_description}
+ --*/
+ keyword_id in cr_keywords.keyword_id%TYPE
+) return varchar2;
+
+function get_description (
+ --/** Retrieves the description of the content keyword
+ -- @author Karl Goldstein
+ -- @param keyword_id The keyword id
+ -- @return The description for the specified keyword
+ -- @see {content_keyword.get_heading}, {content_keyword.set_description}
+ --*/
+ keyword_id in cr_keywords.keyword_id%TYPE
+) return varchar2;
+
+procedure set_heading (
+ --/** Sets a new heading for the keyword
+ -- @author Karl Goldstein
+ -- @param keyword_id The keyword id
+ -- @param heading The new heading
+ -- @see {content_keyword.get_heading}, {content_keyword.set_description}
+ --*/
+ keyword_id in cr_keywords.keyword_id%TYPE,
+ heading in cr_keywords.heading%TYPE
+);
+
+procedure set_description (
+ --/** Sets a new description for the keyword
+ -- @author Karl Goldstein
+ -- @param keyword_id The keyword id
+ -- @param description The new description
+ -- @see {content_keyword.set_heading}, {content_keyword.get_description}
+ --*/
+ keyword_id in cr_keywords.keyword_id%TYPE,
+ description in cr_keywords.description%TYPE
+);
+
+function is_leaf (
+ --/** Determines if the keyword has no sub-keywords associated with it
+ -- @author Karl Goldstein
+ -- @param keyword_id The keyword id
+ -- @return 't' if the keyword has no descendants, 'f' otherwise
+ -- @see {content_keyword.new}
+ --*/
+ keyword_id in cr_keywords.keyword_id%TYPE
+) return varchar2;
+
+procedure item_assign (
+ --/** Assigns this keyword to a content item, creating a relationship between them
+ -- @author Karl Goldstein
+ -- @param item_id The item to be assigned to
+ -- @param keyword_id The keyword to be assigned
+ -- @param context_id As in acs_rel.new, deprecated
+ -- @param creation_ip As in acs_rel.new, deprecated
+ -- @param creation_user As in acs_rel.new, deprecated
+ -- @see {acs_rel.new}, {content_keyword.item_unassign}
+ --*/
+ 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
+);
+
+procedure item_unassign (
+ --/** Unassigns this keyword to a content item, removing a relationship between them
+ -- @author Karl Goldstein
+ -- @param item_id The item to be unassigned from
+ -- @param keyword_id The keyword to be unassigned
+ -- @see {acs_rel.delete}, {content_keyword.item_assign}
+ --*/
+ item_id in cr_items.item_id%TYPE,
+ keyword_id in cr_keywords.keyword_id%TYPE
+);
+
+function is_assigned (
+ --/** Determines if the keyword is assigned to the item
+ -- @author Karl Goldstein
+ -- @param item_id The item id
+ -- @param keyword_id The keyword id to be checked for assignment
+ -- @param recurse Specifies if the keyword search is
+ -- recursive. May be set to one of the following
+ -- values:
+ -- - none: Not recursive. Look for an exact match.
+ -- - up: Recursive from specific to general. A search for
+ -- "attack dogs" will also match "dogs", "animals", "mammals", etc.
+ -- - down: Recursive from general to specific. A search for
+ -- "mammals" will also match "dogs", "attack dogs", "cats", "siamese cats",
+ -- etc.
+ -- @return 't' if the keyword may be matched to an item, 'f' otherwise
+ -- @see {content_keyword.item_assign}
+ --*/
+ item_id in cr_items.item_id%TYPE,
+ keyword_id in cr_keywords.keyword_id%TYPE,
+ recurse in varchar2 default 'none'
+) return varchar2;
+
+function get_path (
+ --/** Retreives a path to the keyword/subject category, with the most general
+ -- category at the root of the path
+ -- @author Karl Goldstein
+ -- @param keyword_id The keyword id
+ -- @return The path to the keyword, or null if no such keyword exists
+ -- @see {content_keyword.new}
+ --*/
+ keyword_id in cr_keywords.keyword_id%TYPE
+) return varchar2;
+
+end content_keyword;
+/
+show errors
+
+
+
+-- recreate content keyword package body for package_id
+
+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;
+
+ update acs_objects
+ set title = set_heading.heading
+ where object_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',
+ package_id in acs_objects.package_id%TYPE
+) return cr_keywords.keyword_id%TYPE
+is
+ v_id integer;
+ v_package_id acs_objects.package_id%TYPE;
+begin
+
+ if package_id is null then
+ v_package_id := acs_object.package_id(new.parent_id);
+ else
+ v_package_id := package_id;
+ end if;
+
+ v_id := acs_object.new (object_id => keyword_id,
+ context_id => parent_id,
+ object_type => object_type,
+ title => heading,
+ package_id => v_package_id,
+ 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