Index: openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.2.0d12-5.2.0d13.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.2.0d12-5.2.0d13.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.2.0d12-5.2.0d13.sql 18 Mar 2005 13:50:43 -0000 1.2 +++ openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.2.0d12-5.2.0d13.sql 20 Mar 2005 15:58:46 -0000 1.3 @@ -84,3 +84,398 @@ ); return null; end;' language 'plpgsql'; + + +create or replace function content_folder__copy (integer,integer,integer,varchar) +returns integer as ' +declare + copy__folder_id alias for $1; + copy__target_folder_id alias for $2; + copy__creation_user alias for $3; + copy__creation_ip alias for $4; -- default null + 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 + v_new_folder_id := content_folder__copy ( + copy__folder_id, + copy__target_folder_id, + copy__creation_user, + copy__creation_ip, + NULL + ); + return v_new_folder_id; +end;' language 'plpgsql'; + +create or replace function content_folder__copy (integer,integer,integer,varchar,varchar) +returns integer as ' +declare + copy__folder_id alias for $1; + copy__target_folder_id alias for $2; + copy__creation_user alias for $3; + copy__creation_ip alias for $4; -- default null + copy__name alias for $5; -- default null + 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 + + select + count(*) + into + v_valid_folders_p + from + cr_folders + where + folder_id = copy__target_folder_id + or + folder_id = copy__folder_id; + + select + parent_id + into + v_current_folder_id + from + cr_items + where + item_id = copy__folder_id; + + if copy__folder_id = content_item__get_root_folder(null) + or copy__folder_id = content_template__get_root_folder() + or copy__target_folder_id = copy__folder_id then + v_valid_folders_p := 0; + end if; + + -- get the source folder info + select + name, label, description + into + v_name, v_label, v_description + from + cr_items i, cr_folders f + where + f.folder_id = i.item_id + and + f.folder_id = copy__folder_id; + + if v_valid_folders_p = 2 then + + if content_folder__is_sub_folder(copy__folder_id, copy__target_folder_id) != ''t'' or v_current_folder_id != copy__target_folder_id or (v_name != copy__name and copy__name is not null) then + + -- create the new folder + v_new_folder_id := content_folder__new ( + coalesce (copy__name, v_name), + 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; + end if; + end if; + + return 0; +end;' language 'plpgsql'; + + +create or replace function content_revision__copy (integer,integer,integer,integer,varchar) +returns integer as ' +declare + copy__revision_id alias for $1; + copy__copy_id alias for $2; -- default null + copy__target_item_id alias for $3; -- default null + copy__creation_user alias for $4; -- default null + copy__creation_ip alias for $5; -- default null + v_copy_id cr_revisions.revision_id%TYPE; + v_target_item_id cr_items.item_id%TYPE; + type_rec record; +begin + -- use the specified item_id or the item_id of the original revision + -- if none is specified + if copy__target_item_id is null then + select item_id into v_target_item_id from cr_revisions + where revision_id = copy__revision_id; + else + v_target_item_id := copy__target_item_id; + end if; + + -- use the copy_id or generate a new copy_id if none is specified + -- the copy_id is a revision_id + if copy__copy_id is null then + select acs_object_id_seq.nextval into v_copy_id from dual; + else + v_copy_id := copy__copy_id; + end if; + + -- create the basic object + insert into acs_objects ( + object_id, + object_type, + context_id, + security_inherit_p, + creation_user, + creation_date, + creation_ip, + last_modified, + modifying_user, + modifying_ip, + title, + package_id) + select + v_copy_id as object_id, + object_type, + v_target_item_id, + security_inherit_p, + copy__creation_user as creation_user, + now() as creation_date, + copy__creation_ip as creation_ip, + now() as last_modified, + copy__creation_user as modifying_user, + copy__creation_ip as modifying_ip, + title, + package_id + from + acs_objects + where + object_id = copy__revision_id; + + -- create the basic revision (using v_target_item_id) + insert into cr_revisions + select + v_copy_id as revision_id, + v_target_item_id as item_id, + title, + description, + publish_date, + mime_type, + nls_language, + lob, + content, + content_length + from + cr_revisions + where + revision_id = copy__revision_id; + + -- iterate over the ancestor types and copy attributes + for type_rec in select ot2.object_type, tree_level(ot2.tree_sortkey) as level + from acs_object_types ot1, acs_object_types ot2, acs_objects o + where ot2.object_type <> ''acs_object'' + and ot2.object_type <> ''content_revision'' + and o.object_id = copy__revision_id + and ot1.object_type = o.object_type + and ot1.tree_sortkey between ot2.tree_sortkey and tree_right(ot2.tree_sortkey) + order by level desc + LOOP + PERFORM content_revision__copy_attributes(type_rec.object_type, + copy__revision_id, v_copy_id); + end loop; + + return v_copy_id; + +end;' language 'plpgsql'; + + + +select define_function_args('content_item__copy','item_id,target_folder_id,creation_user,creation_ip,name'); +create or replace function content_item__copy ( + integer, + integer, + integer, + varchar, + varchar +) returns integer as ' +declare + copy__item_id alias for $1; + copy__target_folder_id alias for $2; + copy__creation_user alias for $3; + copy__creation_ip alias for $4; -- default null + copy__name alias for $5; -- default null + v_current_folder_id cr_folders.folder_id%TYPE; + v_num_revisions integer; + v_name cr_items.name%TYPE; + v_content_type cr_items.content_type%TYPE; + v_locale cr_items.locale%TYPE; + v_item_id cr_items.item_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + v_is_registered boolean; + v_old_revision_id cr_revisions.revision_id%TYPE; + v_new_revision_id cr_revisions.revision_id%TYPE; + v_old_live_revision_id cr_revisions.revision_id%TYPE; + v_new_live_revision_id cr_revisions.revision_id%TYPE; + v_storage_type cr_items.storage_type%TYPE; +begin + + -- call content_folder.copy if the item is a folder + if content_folder__is_folder(copy__item_id) = ''t'' then + PERFORM content_folder__copy( + copy__item_id, + copy__target_folder_id, + copy__creation_user, + copy__creation_ip, + copy__name + ); + + -- call content_symlink.copy if the item is a symlink + else if content_symlink__is_symlink(copy__item_id) = ''t'' then + PERFORM content_symlink__copy( + copy__item_id, + copy__target_folder_id, + copy__creation_user, + copy__creation_ip, + copy__name + ); + + -- call content_extlink.copy if the item is an url + else if content_extlink__is_extlink(copy__item_id) = ''t'' then + PERFORM content_extlink__copy( + copy__item_id, + copy__target_folder_id, + copy__creation_user, + copy__creation_ip, + copy__name + ); + + -- make sure the target folder is really a folder + else if content_folder__is_folder(copy__target_folder_id) = ''t'' then + + select + parent_id + into + v_current_folder_id + from + cr_items + where + item_id = copy__item_id; + + select + content_type, name, locale, + coalesce(live_revision, latest_revision), storage_type + into + v_content_type, v_name, v_locale, v_revision_id, v_storage_type + from + cr_items + where + item_id = copy__item_id; + +-- copy to a different folder, or allow copy to the same folder +-- with a different name + + if copy__target_folder_id != v_current_folder_id or ( v_name != copy__name and copy__name is not null ) then + -- make sure the content type of the item is registered to the folder + v_is_registered := content_folder__is_registered( + copy__target_folder_id, + v_content_type, + ''f'' + ); + + if v_is_registered = ''t'' then + -- create the new content item + v_item_id := content_item__new( + coalesce (copy__name, v_name), + copy__target_folder_id, + null, + v_locale, + now(), + copy__creation_user, + null, + copy__creation_ip, + ''content_item'', + v_content_type, + null, + null, + ''text/plain'', + null, + null, + v_storage_type + ); + + select + latest_revision, live_revision into v_old_revision_id, v_old_live_revision_id + from + cr_items + where + item_id = copy__item_id; + end if; + + -- copy the latest revision (if any) to the new item + if v_old_revision_id is not null then + v_new_revision_id := content_revision__copy ( + v_old_revision_id, + null, + v_item_id, + copy__creation_user, + copy__creation_ip + ); + end if; + + -- copy the live revision (if there is one and it differs from the latest) to the new item + if v_old_live_revision_id is not null then + if v_old_live_revision_id <> v_old_revision_id then + v_new_live_revision_id := content_revision__copy ( + v_old_live_revision_id, + null, + v_item_id, + copy__creation_user, + copy__creation_ip + ); + else + v_new_live_revision_id := v_new_revision_id; + end if; + end if; + + update cr_items set live_revision = v_new_live_revision, latest_revision = v_new_revision_id where item_id = v_item_id; + + end if; + + end if; end if; end if; end if; + + return v_item_id; + +end;' language 'plpgsql';