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 -r1.70 -r1.70.2.1 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 7 Jul 2011 10:46:02 -0000 1.70 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 5 Sep 2014 16:32:58 -0000 1.70.2.1 @@ -1632,282 +1632,13 @@ END; $$ LANGUAGE plpgsql stable; --- create sequence content_item_gp_session_id; --- 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, --- primary key (sid,pos) --- ); - --- create table get_path_rel_cursor ( --- sid integer, --- pos integer, --- parent_id integer, --- tree_level integer, --- primary key (sid,pos) --- ); - --- create or replace 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 i1.tree_sortkey between i2.tree_sortkey and tree_right(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 or replace 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 i1.tree_sortkey between i2.tree_sortkey and tree_right(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 or replace 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 or replace 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'; - --- -- if called with null its a noop and returns null so strict. --- create or replace 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' strict; - --- old slow version --- create or replace 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_rec record; --- v_item_id integer; --- v_rel_item_id integer; --- v_session_id integer; --- v_rel_found_p boolean; --- v_abs_found_p boolean; --- v_tmp integer; --- begin - --- -- check that the item exists --- select count(*) into v_count from cr_items where item_id = get_path__item_id; - --- if v_count = 0 then --- raise EXCEPTION ''-20000: Invalid item ID: %'', get_path__item_id; --- end if; - --- -- begin walking down the path to the item (from the repository root) - --- -- if the root folder is not null then prepare for a relative path - --- if get_path__root_folder_id is not null then - --- -- if root_folder_id is a symlink, resolve it (child items will point --- -- to the actual folder, not the symlink) - --- 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 - --- 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; - --- 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; - --- 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 - --- LOOP --- exit when NOT v_rel_found_p; --- v_path := v_path || ''../''; - --- 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; - --- -- loop over the remainder of the absolute path --- LOOP - --- 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 ''/'' --- -- loop over the absolute path - --- for v_rec in select i2.name, tree_level(i2.tree_sortkey) as tree_level --- from cr_items i1, cr_items i2 --- where i2.parent_id <> 0 --- and i1.item_id = get_path__item_id --- and i1.tree_sortkey between i2.tree_sortkey and tree_right(i2.tree_sortkey) --- order by tree_level --- LOOP --- v_path := v_path || ''/'' || v_rec.name; --- end loop; - --- end if; - --- return v_path; - --- end;' language 'plpgsql'; - - --- old define_function_args('content_item__get_path','item_id,root_folder_id') --- new -select define_function_args('content_item__get_path','item_id,root_folder_id;null'); - - - -- -- procedure content_item__get_path/2 -- + +select define_function_args('content_item__get_path','item_id,root_folder_id;null'); + CREATE OR REPLACE FUNCTION content_item__get_path( get_path__item_id integer, get_path__root_folder_id integer -- default null