Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql,v diff -u -N -r1.26 -r1.27 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 25 Aug 2001 14:22:32 -0000 1.26 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 28 Aug 2001 01:07:23 -0000 1.27 @@ -1029,27 +1029,141 @@ end;' language 'plpgsql'; +create sequence content_item_gp_session_id; --- function get_path +create table get_path_cursors ( + rel_cursor_pos integer, + abs_cursor_pos integer +); + +insert into get_path_cursors values (0,0); + +create table get_path_abs_cursor ( + sid integer, + pos integer, + name text, + parent_id integer, + tree_level integer +); + +create table get_path_rel_cursor ( + sid integer, + pos integer, + parent_id integer, + tree_level integer +); + +create function content_item__create_rel_cursor(integer,integer) +returns integer as ' +declare + v_item_id alias for $1; + v_sid alias for $2; + v_rec record; + v_cur_pos integer default 0; +begin + update get_path_cursors set rel_cursor_pos = 0; + for v_rec in select i2.name, + i2.parent_id, + tree_level(i2.tree_sortkey) as tree_level + from (select * from cr_items where item_id = v_item_id) i1, + cr_items i2 + where i2.parent_id <> 0 + and i2.tree_sortkey <= i1.tree_sortkey + and i1.tree_sortkey like (i2.tree_sortkey || ''%'') + order by i2.tree_sortkey + + LOOP + insert into get_path_rel_cursor + (sid,pos,parent_id,tree_level) + values + (v_sid,v_cur_pos,v_rec.parent_id,v_rec.tree_level); + v_cur_pos := v_cur_pos + 1; + end LOOP; + + return null; +end;' language 'plpgsql'; + +create function content_item__create_abs_cursor(integer,integer) +returns integer as ' +declare + v_item_id alias for $1; + v_sid alias for $2; + v_rec record; + v_cur_pos integer default 0; +begin + update get_path_cursors set abs_cursor_pos = 0; + for v_rec in select i2.name, + i2.parent_id, + tree_level(i2.tree_sortkey) as tree_level + from (select * from cr_items where item_id = v_item_id) i1, + cr_items i2 + where i2.parent_id <> 0 + and i2.tree_sortkey <= i1.tree_sortkey + and i1.tree_sortkey like (i2.tree_sortkey || ''%'') + order by i2.tree_sortkey + + LOOP + insert into get_path_abs_cursor + (sid,pos,name,parent_id,tree_level) + values + (v_sid,v_cur_pos,v_rec.name,v_rec.parent_id,v_rec.tree_level); + v_cur_pos := v_cur_pos + 1; + end LOOP; + + return null; +end;' language 'plpgsql'; + +create function content_item__abs_cursor_next_pos() returns integer as ' +declare + v_pos integer; +begin + select abs_cursor_pos into v_pos from get_path_cursors; + update get_path_cursors set abs_cursor_pos = abs_cursor_pos + 1; + + return v_pos; +end;' language 'plpgsql'; + +create function content_item__rel_cursor_next_pos() returns integer as ' +declare + v_pos integer; +begin + select rel_cursor_pos into v_pos from get_path_cursors; + update get_path_cursors set rel_cursor_pos = rel_cursor_pos + 1; + + return v_pos; +end;' language 'plpgsql'; + +create function content_item__cleanup_cursors(integer) returns integer as ' +declare + v_sid alias for $1; +begin + delete from get_path_abs_cursor where sid = v_sid; + delete from get_path_rel_cursor where sid = v_sid; + + return null; +end;' language 'plpgsql'; + create function content_item__get_path (integer,integer) returns varchar as ' declare get_path__item_id alias for $1; get_path__root_folder_id alias for $2; -- default null v_count integer; v_name varchar; + v_saved_name varchar; v_parent_id integer default 0; v_tree_level integer; v_resolved_root_id integer; v_rel_parent_id integer default 0; v_rel_tree_level integer default 0; v_path text default ''''; - v_apath text default ''''; v_rec record; v_item_id integer; v_rel_item_id integer; - v_exit_p boolean default ''f''; - v_rel_exit_p boolean default ''f''; + v_session_id integer; + v_rel_found_p boolean; + v_abs_found_p boolean; + v_tmp integer; begin -- check that the item exists @@ -1070,110 +1184,98 @@ v_resolved_root_id := content_symlink__resolve(get_path__root_folder_id); + v_session_id := nextval(''content_item_gp_session_id''); + PERFORM content_item__create_abs_cursor(get_path__item_id, v_session_id); + PERFORM content_item__create_rel_cursor(v_resolved_root_id, v_session_id); + -- begin walking down the path to the root folder. Discard -- elements of the item path as long as they are the same as the root -- folder - for v_rec in - select i3.name as name, - i3.item_id as item_id, - i3.parent_id as parent_id, - tree_level(i3.tree_sortkey) as level, - i4.parent_id as rel_parent_id, - tree_level(i4.tree_sortkey) as rel_tree_level, - i4.item_id as rel_item_id - from (select * from cr_items where item_id = get_path__item_id) i1, - (select * from cr_items where item_id = v_resolved_root_id) i2, - cr_items i3, - cr_items i4 - where i3.tree_sortkey <= i1.tree_sortkey - and i1.tree_sortkey like (i3.tree_sortkey || ''%'') - and i4.tree_sortkey <= i2.tree_sortkey - and i2.tree_sortkey like (i4.tree_sortkey || ''%'') - and i4.parent_id <> 0 - and i3.parent_id <> 0 - and i4.item_id = i3.item_id - order by i3.tree_sortkey + while v_parent_id = v_rel_parent_id loop + v_tmp := content_item__abs_cursor_next_pos(); + select name, parent_id, tree_level + into v_name, v_parent_id, v_tree_level + from get_path_abs_cursor + where sid = v_session_id + and pos = v_tmp; - LOOP - v_name := v_rec.name; - v_parent_id := v_rec.parent_id; - v_tree_level := v_rec.level; - v_item_id := v_rec.item_id; - v_rel_parent_id := v_rec.rel_parent_id; - v_rel_tree_level := v_rec.rel_tree_level; - v_rel_item_id := v_rec.rel_item_id; - exit when v_parent_id <> v_rel_parent_id; - end LOOP; + if NOT FOUND then + v_name := v_saved_name; + v_abs_found_p := ''f''; + else + v_saved_name := v_name; + v_abs_found_p := ''t''; + end if; + v_tmp := content_item__rel_cursor_next_pos(); + select parent_id, tree_level + into v_rel_parent_id, v_rel_tree_level + from get_path_rel_cursor + where sid = v_session_id + and pos = v_tmp; - -- walk the remainder of the relative path, add a .. for each + if NOT FOUND then + v_rel_found_p := ''f''; + else + v_rel_found_p := ''t''; + end if; + + exit when NOT v_rel_found_p or NOT v_abs_found_p; + end loop; + + + -- walk the remainder of the relative path, add a ''..'' for each -- additional step - for v_rec in select - i2.parent_id, - tree_level(i2.tree_sortkey) as tree_level - from - (select * from cr_items - where item_id = v_resolved_root_id) i1, - cr_items i2, - (select * from cr_items - where item_id = v_rel_item_id) i3 - where - i2.parent_id <> 0 - and - i2.tree_sortkey <= i1.tree_sortkey - and - i2.tree_sortkey > i3.tree_sortkey - and - i1.tree_sortkey like (i2.tree_sortkey || ''%'') - order by i2.tree_sortkey LOOP + exit when NOT v_rel_found_p; v_path := v_path || ''../''; - end LOOP; + v_tmp := content_item__rel_cursor_next_pos(); + select parent_id, tree_level + into v_rel_parent_id, v_rel_tree_level + from get_path_rel_cursor + where sid = v_session_id + and pos = v_tmp; + + if NOT FOUND then + v_rel_found_p := ''f''; + else + v_rel_found_p := ''t''; + end if; + end loop; -- an item relative to itself is ''../item'' if v_resolved_root_id = get_path__item_id then v_path := ''../''; - end if; + end if; - v_path := rtrim(v_path,''/''); - -- loop over the remainder of the absolute path - - for v_rec in select - i2.name, i2.parent_id, - tree_level(i2.tree_sortkey) as tree_level - from - (select * from cr_items - where item_id = get_path__item_id) i1, - cr_items i2, - (select * from cr_items - where item_id = v_item_id) i3 - where - i2.parent_id <> 0 - and - i2.tree_sortkey <= i1.tree_sortkey - and - i2.tree_sortkey > i3.tree_sortkey - and - i1.tree_sortkey like (i2.tree_sortkey || ''%'') - order by i2.tree_sortkey - LOOP - v_path := v_path || ''/'' || v_rec.name; - end LOOP; - v_path := ltrim(v_path,''/''); - if substr(v_path,length(v_path) - 1,2) = ''..'' then - v_path := v_path || ''/''; - end if; + v_path := v_path || v_name; + v_tmp := content_item__abs_cursor_next_pos(); + select name, parent_id, tree_level + into v_name, v_parent_id, v_tree_level + from get_path_abs_cursor + where sid = v_session_id + and pos = v_tmp; + if NOT FOUND then + v_abs_found_p := ''f''; + else + v_abs_found_p := ''t''; + end if; + + exit when NOT v_abs_found_p; + v_path := v_path || ''/''; + + end LOOP; + PERFORM content_item__cleanup_cursors(v_session_id); else -- this is an absolute path so prepend a ''/'' - -- prime the pump to be consistent with relative path execution plan - -- loop over the remainder of the absolute path + -- loop over the absolute path for v_rec in select i2.name, tree_level(i2.tree_sortkey) as tree_level