-- Data model to support content repository of the ArsDigita Community -- System -- Copyright (C) 1999-2000 ArsDigita Corporation -- Author: Karl Goldstein (karlg@arsdigita.com) -- $Id: content-folder.sql,v 1.55 2024/09/11 06:15:47 gustafn Exp $ -- 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_folder -- -- procedure content_folder__new/5 -- CREATE OR REPLACE FUNCTION content_folder__new( new__name varchar, new__label varchar, new__description varchar, -- default null new__parent_id integer, -- default null new__package_id integer -- default null ) RETURNS integer AS $$ -- -- content_folder__new/5 maybe obsolete, when we define proper defaults for /11 -- DECLARE BEGIN return content_folder__new(new__name, new__label, new__description, new__parent_id, null, null, now(), null, null, new__package_id ); END; $$ LANGUAGE plpgsql; -- -- procedure content_folder__new/4 -- CREATE OR REPLACE FUNCTION content_folder__new( new__name varchar, new__label varchar, new__description varchar, -- default null new__parent_id integer -- default null ) RETURNS integer AS $$ -- -- content_folder__new/4 maybe obsolete, when we define proper defaults for /11 -- DECLARE BEGIN return content_folder__new(new__name, new__label, new__description, new__parent_id, null, null, now(), null, null, 't', null ); END; $$ LANGUAGE plpgsql; -- function new -- -- procedure content_folder__new/9 -- CREATE OR REPLACE FUNCTION content_folder__new( new__name varchar, new__label varchar, new__description varchar, -- default null new__parent_id integer, -- default null new__context_id integer, -- default null new__folder_id integer, -- default null new__creation_date timestamptz, -- default now() new__creation_user integer, -- default null new__creation_ip varchar -- default null ) RETURNS integer AS $$ -- -- content_folder__new/9 maybe obsolete, when we define proper defaults for /11 -- DECLARE BEGIN return content_folder__new(new__name, new__label, new__description, new__parent_id, new__context_id, new__folder_id, new__creation_date, new__creation_user, new__creation_ip, 't', null::integer ); END; $$ LANGUAGE plpgsql; -- function new -- -- procedure content_folder__new/10 -- CREATE OR REPLACE FUNCTION content_folder__new( new__name varchar, new__label varchar, new__description varchar, -- default null new__parent_id integer, -- default null new__context_id integer, -- default null new__folder_id integer, -- default null new__creation_date timestamptz, -- default now() new__creation_user integer, -- default null new__creation_ip varchar, -- default null new__package_id integer -- default null ) RETURNS integer AS $$ -- -- content_folder__new/10 maybe obsolete, when we define proper defaults for /11 -- DECLARE v_folder_id cr_folders.folder_id%TYPE; v_context_id acs_objects.context_id%TYPE; v_package_id acs_objects.package_id%TYPE; BEGIN return content_folder__new(new__name, new__label, new__description, new__parent_id, new__context_id, new__folder_id, new__creation_date, new__creation_user, new__creation_ip, 't', new__package_id ); END; $$ LANGUAGE plpgsql; -- -- procedure content_folder__new/10 -- CREATE OR REPLACE FUNCTION content_folder__new( new__name varchar, new__label varchar, new__description varchar, -- default null new__parent_id integer, -- default null new__context_id integer, -- default null new__folder_id integer, -- default null new__creation_date timestamptz, -- default now() new__creation_user integer, -- default null new__creation_ip varchar, -- default null new__security_inherit_p boolean -- default true ) RETURNS integer AS $$ -- -- content_folder__new/10 maybe obsolete, when we define proper defaults for /11 -- DECLARE v_package_id acs_objects.package_id%TYPE; v_folder_id cr_folders.folder_id%TYPE; v_context_id acs_objects.context_id%TYPE; BEGIN return content_folder__new ( new__name, new__label, new__description, new__parent_id, new__context_id, new__folder_id, new__creation_date, new__creation_user, new__creation_ip, new__security_inherit_p, null ); END; $$ LANGUAGE plpgsql; -- function new -- accepts security_inherit_p DaveB -- old define_function_args('content_folder__new','name,label,description,parent_id,context_id,folder_id,creation_date;now,creation_user,creation_ip,security_inherit_p;t,package_id') -- new select define_function_args('content_folder__new','name,label,description;null,parent_id;null,context_id;null,folder_id;null,creation_date;now,creation_user;null,creation_ip;null,security_inherit_p;t,package_id;null'); -- -- procedure content_folder__new/11 -- CREATE OR REPLACE FUNCTION content_folder__new( new__name varchar, new__label varchar, new__description varchar, -- default null new__parent_id integer, -- default null new__context_id integer, -- default null new__folder_id integer, -- default null new__creation_date timestamptz, -- default now() -- default 'now' new__creation_user integer, -- default null new__creation_ip varchar, -- default null new__security_inherit_p boolean, -- default true -- default 't' new__package_id integer -- default null ) RETURNS integer AS $$ DECLARE v_folder_id cr_folders.folder_id%TYPE; v_context_id acs_objects.context_id%TYPE; BEGIN -- set the context_id if new__context_id is null then v_context_id := new__parent_id; else v_context_id := new__context_id; end if; -- parent_id = security_context_root means that this is a mount point if new__parent_id != -4 and content_folder__is_folder(new__parent_id) and content_folder__is_registered(new__parent_id,'content_folder','f') = 'f' then raise EXCEPTION '-20000: This folder does not allow subfolders to be created'; return null; else v_folder_id := content_item__new( new__folder_id, new__name, new__parent_id, null, new__creation_date, new__creation_user, new__context_id, new__creation_ip, 'f', 'text/plain', null, 'text', new__security_inherit_p, 'CR_FILES', 'content_folder', 'content_folder', new__package_id ); insert into cr_folders ( folder_id, label, description, package_id ) values ( v_folder_id, new__label, new__description, new__package_id ); -- set the correct object title update acs_objects set title = new__label where object_id = v_folder_id; -- inherit the attributes of the parent folder if new__parent_id is not null then insert into cr_folder_type_map select v_folder_id as folder_id, content_type from cr_folder_type_map where folder_id = new__parent_id; end if; -- update the child flag on the parent update cr_folders set has_child_folders = 't' where folder_id = new__parent_id; return v_folder_id; end if; return v_folder_id; END; $$ LANGUAGE plpgsql; -- -- procedure content_folder__new/10 -- CREATE OR REPLACE FUNCTION content_folder__new( new__name varchar, new__label varchar, new__description varchar, -- default null new__parent_id integer, -- default null new__context_id integer, -- default null new__folder_id integer, -- default null new__creation_date timestamptz, -- default now() new__creation_user integer, -- default null new__creation_ip varchar, -- default null new__security_inherit_p boolean -- default true ) RETURNS integer AS $$ -- -- content_folder__new/10 maybe obsolete, when we define proper defaults for /11 -- DECLARE BEGIN return content_folder__new(new__name, new__label, new__description, new__parent_id, new__context_id, new__folder_id, new__creation_date, new__creation_user, new__creation_ip, new__security_inherit_p, null::integer ); END; $$ LANGUAGE plpgsql; -- procedure delete select define_function_args('content_folder__del','folder_id,cascade_p;f'); -- -- procedure content_folder__del/2 -- CREATE OR REPLACE FUNCTION content_folder__del( delete__folder_id integer, p_cascade_p boolean -- default 'f' ) RETURNS integer AS $$ DECLARE v_count integer; v_child_row record; v_parent_id integer; v_path varchar; v_folder_sortkey varbit; BEGIN if p_cascade_p = 'f' then select count(*) into v_count from cr_items where parent_id = delete__folder_id; -- check if the folder contains any items if v_count > 0 then v_path := content_item__get_path(delete__folder_id, null); raise EXCEPTION '-20000: Folder ID % (%) cannot be deleted because it is not empty.', delete__folder_id, v_path; end if; else -- delete children select into v_folder_sortkey tree_sortkey from cr_items where item_id=delete__folder_id; for v_child_row in select item_id, tree_sortkey, name from cr_items where tree_sortkey between v_folder_sortkey and tree_right(v_folder_sortkey) and tree_sortkey != v_folder_sortkey order by tree_sortkey desc loop if content_folder__is_folder(v_child_row.item_id) then perform content_folder__delete(v_child_row.item_id); else perform content_item__delete(v_child_row.item_id); end if; end loop; end if; PERFORM content_folder__unregister_content_type( delete__folder_id, 'content_revision', 't' ); delete from cr_folder_type_map where folder_id = delete__folder_id; select parent_id into v_parent_id from cr_items where item_id = delete__folder_id; raise notice 'deleting folder %',delete__folder_id; PERFORM content_item__delete(delete__folder_id); -- check if any folders are left in the parent update cr_folders set has_child_folders = 'f' where folder_id = v_parent_id and not exists ( select 1 from cr_items where parent_id = v_parent_id and content_type = 'content_folder'); return 0; END; $$ LANGUAGE plpgsql; select define_function_args('content_folder__delete','folder_id,cascade_p;f'); -- -- procedure content_folder__delete/2 -- CREATE OR REPLACE FUNCTION content_folder__delete( delete__folder_id integer, p_cascade_p boolean -- default 'f' ) RETURNS integer AS $$ DECLARE BEGIN PERFORM content_folder__del(delete__folder_id,p_cascade_p); return 0; END; $$ LANGUAGE plpgsql; -- -- procedure content_folder__delete/1 -- CREATE OR REPLACE FUNCTION content_folder__delete( delete__folder_id integer ) RETURNS integer AS $$ -- -- content_folder__delete/1 maybe obsolete, when we define proper defaults for /2 -- DECLARE v_count integer; v_parent_id integer; v_path varchar; BEGIN return content_folder__del( delete__folder_id, 'f' ); END; $$ LANGUAGE plpgsql; -- procedure rename -- old define_function_args('content_folder__edit_name','folder_id,name,label,description') -- new select define_function_args('content_folder__edit_name','folder_id,name;null,label;null,description;null'); -- -- procedure content_folder__edit_name/4 -- CREATE OR REPLACE FUNCTION content_folder__edit_name( edit_name__folder_id integer, edit_name__name varchar, -- default null edit_name__label varchar, -- default null edit_name__description varchar -- default null ) RETURNS integer AS $$ DECLARE v_name_already_exists_p integer; BEGIN if edit_name__name is not null and edit_name__name != '' then PERFORM content_item__edit_name(edit_name__folder_id, edit_name__name); end if; if edit_name__label is not null and edit_name__label != '' then update acs_objects set title = edit_name__label where object_id = edit_name__folder_id; end if; if edit_name__label is not null and edit_name__label != '' and edit_name__description is not null and edit_name__description != '' then update cr_folders set label = edit_name__label, description = edit_name__description where folder_id = edit_name__folder_id; else if(edit_name__label is not null and edit_name__label != '') and (edit_name__description is null or edit_name__description = '') then update cr_folders set label = edit_name__label where folder_id = edit_name__folder_id; end if; end if; return 0; END; $$ LANGUAGE plpgsql; -- 1) make sure we are not moving the folder to an invalid location: -- a. destination folder exists -- b. folder is not the webroot (folder_id = -1) -- c. destination folder is not the same as the folder -- d. destination folder is not a subfolder -- 2) make sure subfolders are allowed in the target_folder -- 3) update the parent_id for the folder -- procedure move select define_function_args('content_folder__move','folder_id,target_folder_id,name;null'); -- -- procedure content_folder__move/3 -- CREATE OR REPLACE FUNCTION content_folder__move( move__folder_id integer, move__target_folder_id integer, move__name varchar -- default null ) RETURNS integer AS $$ DECLARE v_source_folder_id integer; v_valid_folders_p integer; BEGIN select count(*) into v_valid_folders_p from cr_folders where folder_id = move__target_folder_id or folder_id = move__folder_id; if v_valid_folders_p != 2 then raise EXCEPTION '-20000: content_folder.move - Not valid folder(s)'; end if; if move__folder_id = content_item__get_root_folder(null) or move__folder_id = content_template__get_root_folder() then raise EXCEPTION '-20000: content_folder.move - Cannot move root folder'; end if; if move__target_folder_id = move__folder_id then raise EXCEPTION '-20000: content_folder.move - Cannot move a folder to itself'; end if; if content_folder__is_sub_folder(move__folder_id, move__target_folder_id) = 't' then raise EXCEPTION '-20000: content_folder.move - Destination folder is subfolder'; end if; if content_folder__is_registered(move__target_folder_id,'content_folder','f') != 't' then raise EXCEPTION '-20000: content_folder.move - Destination folder does not allow subfolders'; end if; select parent_id into v_source_folder_id from cr_items where item_id = move__folder_id; -- update the parent_id for the folder update cr_items set parent_id = move__target_folder_id, name = coalesce ( move__name, name ) where item_id = move__folder_id; -- update the has_child_folders flags -- update the source update cr_folders set has_child_folders = 'f' where folder_id = v_source_folder_id and not exists ( select 1 from cr_items where parent_id = v_source_folder_id and content_type = 'content_folder'); -- update the destination update cr_folders set has_child_folders = 't' where folder_id = move__target_folder_id; return 0; END; $$ LANGUAGE plpgsql; -- -- procedure content_folder__move/2 -- CREATE OR REPLACE FUNCTION content_folder__move( move__folder_id integer, move__target_folder_id integer ) RETURNS integer AS $$ -- -- content_folder__move/2 maybe obsolete, when we define proper defaults for /3 -- DECLARE BEGIN perform content_folder__move ( move__folder_id, move__target_folder_id, NULL ); return null; END; $$ LANGUAGE plpgsql; -- procedure copy -- added select define_function_args('content_folder__copy','folder_id,target_folder_id,creation_user,creation_ip;null,name;null,label;null'); -- -- procedure content_folder__copy/6 -- CREATE OR REPLACE FUNCTION content_folder__copy( copy__folder_id integer, copy__target_folder_id integer, copy__creation_user integer, copy__creation_ip varchar default null, copy__name cr_items.name%TYPE default null, copy__label cr_folders.label%TYPE default null ) RETURNS integer AS $$ DECLARE v_valid_folders_p integer; v_current_folder_id cr_folders.folder_id%TYPE; v_name cr_items.name%TYPE; v_label cr_folders.label%TYPE; v_description cr_folders.description%TYPE; v_new_folder_id cr_folders.folder_id%TYPE; v_folder_contents_val record; BEGIN if copy__folder_id = content_item__get_root_folder(null) or copy__folder_id = content_template__get_root_folder() then raise EXCEPTION '-20000: content_folder.copy - Not allowed to copy root folder'; end if; select count(*) into v_valid_folders_p from cr_folders where folder_id = copy__target_folder_id or folder_id = copy__folder_id; if v_valid_folders_p != 2 then raise EXCEPTION '-20000: content_folder.copy - Invalid folder(s)'; end if; if copy__target_folder_id = copy__folder_id then raise EXCEPTION '-20000: content_folder.copy - Cannot copy folder to itself'; end if; if content_folder__is_sub_folder(copy__folder_id, copy__target_folder_id) = 't' then raise EXCEPTION '-20000: content_folder.copy - Destination folder is subfolder'; end if; if content_folder__is_registered(copy__target_folder_id,'content_folder','f') != 't' then raise EXCEPTION '-20000: content_folder.copy - Destination folder does not allow subfolders'; end if; -- get the source folder info select name, label, description, parent_id into v_name, v_label, v_description, v_current_folder_id from cr_items i, cr_folders f where f.folder_id = i.item_id and f.folder_id = copy__folder_id; -- would be better to check if the copy__name already exists in the destination folder. if v_current_folder_id = copy__target_folder_id and (v_name = copy__name or copy__name is null) then raise EXCEPTION '-20000: content_folder.copy - Destination folder is parent folder and folder already exists'; end if; -- create the new folder v_new_folder_id := content_folder__new( coalesce (copy__name, v_name), coalesce (copy__label, v_label), v_description, copy__target_folder_id, copy__target_folder_id, null, now(), copy__creation_user, copy__creation_ip, 't', null ); -- copy attributes of original folder insert into cr_folder_type_map select v_new_folder_id as folder_id, content_type from cr_folder_type_map map where folder_id = copy__folder_id and -- do not register content_type if it is already registered not exists ( select 1 from cr_folder_type_map where folder_id = v_new_folder_id and content_type = map.content_type ) ; -- for each item in the folder, copy it for v_folder_contents_val in select item_id from cr_items where parent_id = copy__folder_id LOOP PERFORM content_item__copy( v_folder_contents_val.item_id, v_new_folder_id, copy__creation_user, copy__creation_ip, null ); end loop; return 0; END; $$ LANGUAGE plpgsql; -- -- procedure content_folder__is_folder/1 -- select define_function_args('content_folder__is_folder','item_id'); CREATE OR REPLACE FUNCTION content_folder__is_folder( item_id integer ) RETURNS boolean AS $$ SELECT EXISTS ( SELECT 1 from cr_folders where folder_id = item_id ); $$ LANGUAGE sql stable strict; -- -- procedure content_folder__is_sub_folder/2 -- select define_function_args('content_folder__is_sub_folder','folder_id,target_folder_id'); CREATE OR REPLACE FUNCTION content_folder__is_sub_folder( is_sub_folder__folder_id integer, is_sub_folder__target_folder_id integer ) RETURNS boolean AS $$ WITH RECURSIVE parents AS ( select item_id, parent_id from cr_items where item_id = is_sub_folder__target_folder_id UNION ALL select cr_items.item_id, cr_items.parent_id from cr_items, parents where cr_items.item_id = parents.parent_id ) SELECT EXISTS ( SELECT 1 FROM parents WHERE parent_id = is_sub_folder__folder_id ); $$ LANGUAGE sql stable strict; -- -- procedure content_folder__is_empty/1 -- select define_function_args('content_folder__is_empty','folder_id'); CREATE OR REPLACE FUNCTION content_folder__is_empty( is_empty__folder_id integer ) RETURNS boolean AS $$ SELECT NOT EXISTS ( SELECT 1 from cr_items where parent_id = is_empty__folder_id ); $$ LANGUAGE sql stable strict; -- procedure register_content_type select define_function_args('content_folder__register_content_type','folder_id,content_type,include_subtypes;f'); -- -- procedure content_folder__register_content_type/3 -- CREATE OR REPLACE FUNCTION content_folder__register_content_type( register_content_type__folder_id integer, register_content_type__content_type varchar, register_content_type__include_subtypes boolean -- default 'f' ) RETURNS integer AS $$ DECLARE v_is_registered boolean; BEGIN if register_content_type__include_subtypes = 'f' then v_is_registered := content_folder__is_registered( register_content_type__folder_id, register_content_type__content_type, 'f' ); if v_is_registered = 'f' then insert into cr_folder_type_map ( folder_id, content_type ) values ( register_content_type__folder_id, register_content_type__content_type ); end if; else -- insert into cr_folder_type_map -- select -- register_content_type__folder_id as folder_id, -- object_type as content_type -- from -- acs_object_types -- where -- object_type <> 'acs_object' -- and -- not exists (select 1 from cr_folder_type_map -- where folder_id = register_content_type__folder_id -- and content_type = acs_object_types.object_type) -- connect by -- prior object_type = supertype -- start with -- object_type = register_content_type__content_type; insert into cr_folder_type_map select register_content_type__folder_id as folder_id, o.object_type as content_type from acs_object_types o, acs_object_types o2 where o.object_type <> 'acs_object' and not exists (select 1 from cr_folder_type_map where folder_id = register_content_type__folder_id and content_type = o.object_type) and o2.object_type = register_content_type__content_type and o.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey); end if; return 0; END; $$ LANGUAGE plpgsql; -- procedure unregister_content_type select define_function_args('content_folder__unregister_content_type','folder_id,content_type,include_subtypes;f'); -- -- procedure content_folder__unregister_content_type/3 -- CREATE OR REPLACE FUNCTION content_folder__unregister_content_type( unregister_content_type__folder_id integer, unregister_content_type__content_type varchar, unregister_content_type__include_subtypes boolean -- default 'f' ) RETURNS integer AS $$ DECLARE BEGIN if unregister_content_type__include_subtypes = 'f' then delete from cr_folder_type_map where folder_id = unregister_content_type__folder_id and content_type = unregister_content_type__content_type; else -- delete from cr_folder_type_map -- where folder_id = unregister_content_type__folder_id -- and content_type in (select object_type -- from acs_object_types -- where object_type <> 'acs_object' -- connect by prior object_type = supertype -- start with -- object_type = unregister_content_type__content_type); delete from cr_folder_type_map where folder_id = unregister_content_type__folder_id and content_type in (select o.object_type from acs_object_types o, acs_object_types o2 where o.object_type <> 'acs_object' and o2.object_type = unregister_content_type__content_type and o.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey)); end if; return 0; END; $$ LANGUAGE plpgsql; -- function is_registered select define_function_args('content_folder__is_registered','folder_id,content_type,include_subtypes;f'); -- -- procedure content_folder__is_registered/3 -- CREATE OR REPLACE FUNCTION content_folder__is_registered( is_registered__folder_id integer, is_registered__content_type varchar, is_registered__include_subtypes boolean -- default 'f' ) RETURNS boolean AS $$ DECLARE v_is_registered integer; v_subtype_val record; BEGIN if is_registered__include_subtypes = 'f' or is_registered__include_subtypes is null then select count(1) into v_is_registered from cr_folder_type_map where folder_id = is_registered__folder_id and content_type = is_registered__content_type; else -- select -- object_type -- from -- acs_object_types -- where -- object_type <> 'acs_object' -- connect by -- prior object_type = supertype -- start with -- object_type = is_registered.content_type v_is_registered := 1; for v_subtype_val in select o.object_type from acs_object_types o, acs_object_types o2 where o.object_type <> 'acs_object' and o2.object_type = is_registered__content_type and o.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey) order by o.tree_sortkey LOOP if content_folder__is_registered(is_registered__folder_id, v_subtype_val.object_type, 'f') = 'f' then v_is_registered := 0; end if; end loop; end if; if v_is_registered = 0 then return 'f'; else return 't'; end if; END; $$ LANGUAGE plpgsql stable; -- function get_label select define_function_args('content_folder__get_label','folder_id'); -- -- procedure content_folder__get_label/1 -- CREATE OR REPLACE FUNCTION content_folder__get_label( get_label__folder_id integer ) RETURNS varchar AS $$ DECLARE v_label cr_folders.label%TYPE; BEGIN select label into v_label from cr_folders where folder_id = get_label__folder_id; return v_label; END; $$ LANGUAGE plpgsql stable strict; -- function get_index_page select define_function_args('content_folder__get_index_page','folder_id'); -- -- procedure content_folder__get_index_page/1 -- CREATE OR REPLACE FUNCTION content_folder__get_index_page( get_index_page__folder_id integer ) RETURNS integer AS $$ DECLARE v_folder_id cr_folders.folder_id%TYPE; v_index_page_id cr_items.item_id%TYPE; BEGIN -- if the folder is a symlink, resolve it if content_symlink__is_symlink(get_index_page__folder_id) = 't' then v_folder_id := content_symlink__resolve(get_index_page__folder_id); else v_folder_id := get_index_page__folder_id; end if; select item_id into v_index_page_id from cr_items where parent_id = v_folder_id and name = 'index' and content_item__is_subclass( content_item__get_content_type(content_symlink__resolve(item_id)), 'content_folder') = 'f' and content_item__is_subclass( content_item__get_content_type(content_symlink__resolve(item_id)), 'content_template') = 'f'; if NOT FOUND then return null; end if; return v_index_page_id; END; $$ LANGUAGE plpgsql stable strict; -- function is_root select define_function_args('content_folder__is_root','folder_id'); -- -- procedure content_folder__is_root/1 -- CREATE OR REPLACE FUNCTION content_folder__is_root( is_root__folder_id integer ) RETURNS boolean AS $$ DECLARE v_is_root boolean; BEGIN select parent_id = -4 into v_is_root from cr_items where item_id = is_root__folder_id; return v_is_root; END; $$ LANGUAGE plpgsql; -- show errors