Index: openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.1.1-5.1.2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/upgrade/Attic/upgrade-5.1.1-5.1.2.sql,v diff -u -N --- openacs-4/packages/acs-content-repository/sql/oracle/upgrade/upgrade-5.1.1-5.1.2.sql 1 Jun 2004 23:24:45 -0000 1.1.2.1 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,3084 +0,0 @@ - -create or replace package content_item -as - ---/** ---Content items store the overview of the content published on a ---website. The actual content is stored in content revisions. It is ---implemented this way so that there can be mulitple versions of the ---actual content while the main idea remains constant. For example: If ---there is a review for the movie "Terminator," there will exist a ---content item by the name "terminator" with all the right parameters ---(supertype, parent, etc), there will also exist at least one content ---revision pointing to this item with the actual review content. ---@see {content_revision}, {content_folder} ---*/ - -c_root_folder_id constant integer := -100; - -function get_root_folder ( - item_id in cr_items.item_id%TYPE default null -) return cr_folders.folder_id%TYPE; - -function new ( - --/** Creates a new content item. If the data, title or text - -- parameters are specified, also creates a revision for the item. - -- @author Karl Goldstein - -- @param name The name for the item, must be URL-encoded. - -- If an item with this name already exists under the specified - -- parent item, an error is thrown - -- @param parent_id The parent of this item, defaults to null - -- @param item_id The id of the new item. A new id will be allocated if this - -- parameter is null - -- @param locale The locale for this item, for use with Intermedia search - -- @param item_subtype The type of the new item, defaults to 'content_item' - -- This parameter is used to support inheritance, so that - -- subclasses of content_item can call this function - -- to initialize the parent class - -- @param content_type The content type for the item, defaults to - -- 'content_revision'. Only objects of this type - -- may be used as revisions for the item - -- @param title The user-readable title for the item, defaults to the item's - -- name - -- @param description A short description for the item (4000 characters maximum) - -- @param mime_type The file type of the item, defaults to 'text/plain' - -- @param nls_language The language for the item, used for Intermedia search - -- @param text The text content of the new revision, 4000 charcters maximum. - -- Cannot be specified simultaneously with the data - -- parameter - -- @param data The blob content of the new revision. Cannot be specified - -- simultaneously with the text parameter - -- @param relation_tag If a parent-child relationship is registered - -- for these content types, use this tag to - -- describe the parent-child relationship. Defaults - -- to 'parent content type'-'child content type' - -- @param is_live If 't', the new revision will become live - -- @param context_id Security context id, as in acs_object.new - -- If null, defaults to parent_id, and copies permissions - -- from the parent into the current item - -- @param storage_type in ('lob','file'). Indicates how content is to be stored. - -- 'file' content is stored externally in the file system. - -- @param others As in acs_object.new - -- @return The id of the newly created item - -- @see {acs_object.new} - --*/ - name in cr_items.name%TYPE, - parent_id in cr_items.parent_id%TYPE default null, - item_id in acs_objects.object_id%TYPE default null, - locale in cr_items.locale%TYPE default null, - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - context_id in acs_objects.context_id%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - item_subtype in acs_object_types.object_type%TYPE - default 'content_item', - content_type in acs_object_types.object_type%TYPE - default 'content_revision', - title in cr_revisions.title%TYPE default null, - description in cr_revisions.description%TYPE default null, - mime_type in cr_revisions.mime_type%TYPE default 'text/plain', - nls_language in cr_revisions.nls_language%TYPE default null, - text in varchar2 default null, - data in cr_revisions.content%TYPE default null, - relation_tag in cr_child_rels.relation_tag%TYPE default null, - is_live in char default 'f', - storage_type in cr_items.storage_type%TYPE default 'lob' -) return cr_items.item_id%TYPE; - - -function is_published ( - --/** Determins whether an item is published or not. - -- @author Michael Pih - -- @param item_id The item ID - -- @return 't' if the item is published, 'f' otherwise - --*/ - item_id in cr_items.item_id%TYPE -) return char; - - -function is_publishable ( - --/** Determines if an item is publishable. Publishable items must - -- meet the following criteria: - -- 1) for each child type, the item has n children, min_n < n < max_n - -- 2) for each relation type, the item has n relations, min_n < n < max_n - -- 3) any 'publishing_wf' workflows are finished - -- @author Michael Pih - -- @param item_id The item ID - -- @return 't' if the item is publishable in it's present state, - -- Otherwise, returns 'f' - --*/ - item_id in cr_items.item_id%TYPE -) return char; - - - -function is_valid_child ( - --/** Determines if an item would be a valid child of another item by - -- checking if the parent allows children of the would-be child's - -- content type and if the parent already has n_max children of - -- that content type. - -- @author Michael Pih - -- @param item_id The item ID of the potential parent - -- @param content_type The content type of the potential child item - -- @return 't' if the item would be a valid child, 'f' otherwise - --*/ - - item_id in cr_items.item_id%TYPE, - content_type in acs_object_types.object_type%TYPE, - relation_tag in cr_child_rels.relation_tag%TYPE default null -) return char; - -procedure del ( - --/** Deletes the specified content item, along with any revisions, symlinks, - -- workflows, associated templates, associated keywords, - -- child and item relationships for the item. Use with caution - this - -- operation cannot be undone. - -- @author Karl Goldstein - -- @param item_id The id of the item to delete - -- @see {acs_object.delete} - --*/ - item_id in cr_items.item_id%TYPE -); - -procedure edit_name ( - --/** Renames the item. If an item with the specified name already exists - -- under this item's parent, an error is thrown - -- @author Karl Goldstein - -- @param item_id The id of the item to rename - -- @param name The new name for the item, must be URL-encoded - -- @see {content_item.new} - --*/ - item_id in cr_items.item_id%TYPE, - name in cr_items.name%TYPE -); - -function get_id ( - --/** Takes in a path, such as "/tv/programs/star_trek/episode_203" - -- and returns the id of the item with this path. Note: URLs are abstract (no - -- extensions are allowed in content item names and extensions are stripped when - -- looking up content items) - -- @author Karl Goldstein - -- @param item_path The path to be resolved - -- @param root_folder_id Starts path resolution from this folder. Defaults to - -- the root of the sitemap - -- @param resolve_index Boolean flag indicating whether to return the - -- id of the index page for folders (if one - -- exists). Defaults to 'f'. - -- @return The id of the item with the given path, or null if no such item exists - -- @see {content_item.get_path} - --*/ - item_path in varchar2, - root_folder_id in cr_items.item_id%TYPE default c_root_folder_id, - resolve_index in char default 'f' -) return cr_items.item_id%TYPE; - -function get_path ( - --/** Retrieves the full path to an item, in the form of - -- "/tv/programs/star_trek/episode_203" - -- @author Karl Goldstein - -- @param item_id The item for which the path is to be retrieved - -- @param root_folder_id Starts path resolution from this folder. - -- Defaults to the root of the sitemap - -- @return The path to the item - -- @see {content_item.get_id}, {content_item.write_to_file} - --*/ - item_id in cr_items.item_id%TYPE, - root_folder_id in cr_items.item_id%TYPE default null -) return varchar2; - -function get_virtual_path ( - --/** Retrieves the virtual path to an item, in the form of - -- "/tv/programs/star_trek/episode_203" - -- @author Michael Pih - -- @param item_id The item for which the path is to be retrieved - -- @param root_folder_id Starts path resolution from this folder. - -- Defaults to the root of the sitemap - -- @return The virtual path to the item - -- @see {content_item.get_id}, {content_item.write_to_file}, {content_item.get_path} - --*/ - item_id in cr_items.item_id%TYPE, - root_folder_id in cr_items.item_id%TYPE default c_root_folder_id -) return varchar2; - -procedure write_to_file ( - --/** Writes the content of the live revision of this item to a file, - -- creating all the neccessary directories in the process - -- @author Karl Goldstein - -- @param item_id The item to be written to a file - -- @param root_path The path in the filesystem to which the root of the - -- sitemap corresponds - -- @see {content_item.get_path} - --*/ - item_id in cr_items.item_id%TYPE, - root_path in varchar2 -); - -procedure register_template ( - --/** Registers a template which will be used to render this item. - -- @author Karl Goldstein - -- @param item_id The item for which the template will be registered - -- @param template_id The template to be registered - -- @param use_context The context in which the template is appropriate, such - -- as 'admin' or 'public' - -- @see {content_type.register_template}, {content_item.unregister_template}, - -- {content_item.get_template} - --*/ - item_id in cr_items.item_id%TYPE, - template_id in cr_templates.template_id%TYPE, - use_context in cr_item_template_map.use_context%TYPE -); - -procedure unregister_template ( - --/** Unregisters a template which will be used to render this item. - -- @author Karl Goldstein - -- @param item_id The item for which the template will be unregistered - -- @param template_id The template to be registered - -- @param use_context The context in which the template is appropriate, such - -- as 'admin' or 'public' - -- @see {content_type.register_template}, {content_item.register_template}, - -- {content_item.get_template} - --*/ - item_id in cr_items.item_id%TYPE, - template_id in cr_templates.template_id%TYPE default null, - use_context in cr_item_template_map.use_context%TYPE default null -); - -function get_template ( - --/** Retrieves the template which should be used to render this item. If no template - -- is registered to specifically render the item in the given context, the - -- default template for the item's type is returned. - -- @author Karl Goldstein - -- @param item_id The item for which the template will be unregistered - -- @param use_context The context in the item is to be rendered, such - -- as 'admin' or 'public' - -- @return The id of the registered template, or null if no template could be - -- found - -- @see {content_type.register_template}, {content_item.register_template}, - --*/ - item_id in cr_items.item_id%TYPE, - use_context in cr_item_template_map.use_context%TYPE -) return cr_templates.template_id%TYPE; - -function get_live_revision ( - --/** Retrieves the id of the live revision for the item - -- @param item_id The item for which the live revision is to be retrieved - -- @return The id of the live revision for this item, or null if no live revision - -- exists - -- @see {content_item.set_live_revision}, {content_item.get_latest_revision} - --*/ - item_id in cr_items.item_id%TYPE -) return cr_revisions.revision_id%TYPE; - -procedure set_live_revision ( - --/** Make the specified revision the live revision for the item - -- @author Karl Goldstein - -- @param revision_id The id of the revision which is to become live - -- for its corresponding item - -- @see {content_item.get_live_revision} - --*/ - revision_id in cr_revisions.revision_id%TYPE, - publish_status in cr_items.publish_status%TYPE default 'ready' -); - - -procedure unset_live_revision ( - --/** Set the live revision to null for the item - -- @author Michael Pih - -- @param item_id The id of the item for which to unset the live revision - -- @see {content_item.set_live_revision} - item_id in cr_items.item_id%TYPE -); - -procedure set_release_period ( - --/** Sets the release period for the item. This information may be - -- used by applications to update the publishing status of items - -- at periodic intervals. - -- @author Karl Goldstein - -- @param item_id The id the item. - -- @param start_when The time and date when the item should be released. - -- @param end_when The time and date when the item should be expired. - --*/ - item_id in cr_items.item_id%TYPE, - start_when date default null, - end_when date default null -); - - -function get_revision_count ( - --/** Return the total count of revisions for this item - -- @author Karl Goldstein - -- @param item_id The id the item - -- @return The number of revisions for this item - -- @see {content_revision.new} - --*/ - item_id in cr_items.item_id%TYPE -) return number; - --- Return the object type of this item -function get_content_type ( - --/** Retrieve the content type of this item. Only objects of this type may be - -- used as revisions for the item. - -- @author Karl Goldstein - -- @param item_id The item for which the content type is to be retrieved - -- @return The content type of the item - --*/ - item_id in cr_items.item_id%TYPE -) return cr_items.content_type%TYPE; - -function get_context ( - --/** Retrieve the parent of the given item - -- @author Karl Goldstein - -- @param item_id The item for which the parent is to be retrieved - -- @return The id of the parent for this item - --*/ - item_id in cr_items.item_id%TYPE -) return acs_objects.context_id%TYPE; - -procedure move ( - --/** Move the specified item to a different folder. If the target folder does - -- not exist, or if the folder already contains an item with the same name - -- as the given item, an error will be thrown. - -- @author Karl Goldstein - -- @param item_id The item to be moved - -- @param target_folder_id The new folder for the item - -- @see {content_item.new}, {content_folder.new}, {content_item.copy} - --*/ - item_id in cr_items.item_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - name in cr_items.name%TYPE default null -); - -procedure copy ( - --/** Copies the item to a new location, creating an identical item with - -- an identical latest revision (if any). If the target folder does - -- not exist, or if the folder already contains an item with the same name - -- as the given item, an error will be thrown. - -- @author Karl Goldstein, Michael Pih - -- @param item_id The item to be copied - -- @param target_folder_id The folder where the item is to be copied - -- @param creation_user The user_id of the creator - -- @param creation_ip The IP address of the creator - -- @see {content_item.new}, {content_folder.new}, {content_item.move} - --*/ - item_id in cr_items.item_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE default null, - name in cr_items.name%TYPE default null -); - -function copy2 ( - --/** Copies the item to a new location, creating an identical item with - -- an identical latest revision (if any). If the target folder does - -- not exist, or if the folder already contains an item with the same name - -- as the given item, an error will be thrown. - -- @author Karl Goldstein, Michael Pih - -- @param item_id The item to be copied - -- @param target_folder_id The folder where the item is to be copied - -- @param creation_user The user_id of the creator - -- @param creation_ip The IP address of the creator - -- @return The item ID of the new copy. - -- @see {content_item.new}, {content_folder.new}, {content_item.move} - --*/ - item_id in cr_items.item_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE default null, - name in cr_items.name%TYPE default null -) return cr_items.item_id%TYPE; - --- get the latest revision for an item -function get_latest_revision ( - --/** Retrieves the id of the latest revision for the item (as opposed to the live - -- revision) - -- @author Karl Goldstein - -- @param item_id The item for which the latest revision is to be retrieved - -- @return The id of the latest revision for this item, or null if no revisions - -- exist - -- @see {content_item.get_live_revision} - --*/ - item_id in cr_items.item_id%TYPE -) return cr_revisions.revision_id%TYPE; - - -function get_best_revision ( - --/** Retrieves the id of the live revision for the item if one exists, - -- otherwise retrieves the id of the latest revision if one exists. - -- revision) - -- @author Michael Pih - -- @param item_id The item for which the revision is to be retrieved - -- @return The id of the live or latest revision for this item, - -- or null if no revisions exist - -- @see {content_item.get_live_revision}, {content_item.get_latest_revision} - --*/ - item_id in cr_items.item_id%TYPE -) return cr_revisions.revision_id%TYPE; - -function get_title ( - --/** Retrieves the title for the item, using either the latest or the live revision. - -- If the specified item is in fact a folder, return the folder's label. - -- In addition, this function will automatically resolve symlinks. - -- @author Karl Goldstein - -- @param item_id The item for which the title is to be retrieved - -- @param is_live If 't', use the live revision to get the title. Otherwise, - -- use the latest revision. The default is 'f' - -- @return The title of the item - -- @see {content_item.get_live_revision}, {content_item.get_latest_revision}, - -- {content_symlink.resolve} - --*/ - item_id in cr_items.item_id%TYPE, - is_live in char default 'f' -) return cr_revisions.title%TYPE; - -function get_publish_date ( - --/** Retrieves the publish date for the item - -- @author Karl Goldstein - -- @param item_id The item for which the publish date is to be retrieved - -- @param is_live If 't', use the live revision for the item. Otherwise, use - -- the latest revision. The default is 'f' - -- @return The publish date for the item, or null if the item has no revisions - -- @see {content_item.get_live_revision}, {content_item.get_latest_revision}, - --*/ - item_id in cr_items.item_id%TYPE, - is_live in char default 'f' -) return cr_revisions.publish_date%TYPE; - -function is_subclass ( - --/** Determines if one type is a subclass of another. A class is always a subclass of - -- itself. - -- @author Karl Goldstein - -- @param object_type The child class - -- @param supertype The superclass - -- @return 't' if the child class is a subclass of the superclass, 'f' otherwise - -- @see {acs_object_type.create_type} - --*/ - object_type in acs_object_types.object_type%TYPE, - supertype in acs_object_types.supertype%TYPE -) return char; - -function relate ( - --/** Relates two content items - -- @author Karl Goldstein - -- @param item_id The item id - -- @param object_id The item id of the related object - -- @param relation_tag A tag to help identify the relation type, - -- defaults to 'generic' - -- @param order_n The order of this object among other objects - -- of the same relation type, defaults to null. - -- @param relation_type The object type of the relation, defaults to - -- 'cr_item_rel' - --*/ - item_id in cr_items.item_id%TYPE, - object_id in acs_objects.object_id%TYPE, - relation_tag in cr_type_relations.relation_tag%TYPE default 'generic', - order_n in cr_item_rels.order_n%TYPE default null, - relation_type in acs_object_types.object_type%TYPE default 'cr_item_rel' -) return cr_item_rels.rel_id%TYPE; - - -procedure unrelate ( - --/** Delete the item relationship between two items - -- @author Michael Pih - -- @param rel_id The relationship id - -- @see {content_item.relate} - --*/ - rel_id in cr_item_rels.rel_id%TYPE -); - -function is_index_page ( - --/** Determine if the item is an index page for the specified folder. - -- The item is an index page for the folder if it exists in the - -- folder and its item name is "index". - -- @author Karl Goldstein - -- @param item_id The item id - -- @param folder_id The folder id - -- @return 't' if the item is an index page for the specified - -- folder, 'f' otherwise - -- @see {content_folder.get_index_page} - --*/ - item_id in cr_items.item_id%TYPE, - folder_id in cr_folders.folder_id%TYPE -) return varchar2; - - -function get_parent_folder ( - --/** Get the parent folder. - -- @author Michael Pih - -- @param item_id The item id - -- @return the folder_id of the parent folder, null otherwise - --*/ - item_id in cr_items.item_id%TYPE -) return cr_folders.folder_id%TYPE; - -end content_item; -/ -show errors - -create or replace package content_folder -as - -function new ( - --/** Create a new folder - -- @author Karl Goldstein - -- @param label The label for the folder - -- @param description A short description of the folder, 4000 characters maximum - -- @param parent_id The parent of the folder - -- @param folder_id The id of the new folder. A new id will be allocated by default - -- @param context_id The context id. The parent id will be used as the default context - -- @param creation_date As in acs_object.new - -- @param creation_ip As in acs_object.new - -- @param creation_user As in acs_object.new - -- @return The id of the newly created folder - -- @see {acs_object.new}, {content_item.new} - --*/ - name in cr_items.name%TYPE, - label in cr_folders.label%TYPE, - description in cr_folders.description%TYPE default null, - parent_id in cr_items.parent_id%TYPE default null, - context_id in acs_objects.context_id%TYPE default null, - folder_id in cr_folders.folder_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 -) return cr_folders.folder_id%TYPE; - -procedure del ( - --/** Delete a folder. An error is thrown if the folder is not empty - -- @author Karl Goldstein - -- @param folder_id The id of the folder to delete - -- @see {acs_object.delete}, {content_item.delete} - --*/ - folder_id in cr_folders.folder_id%TYPE, - cascade_p in char default 'f' -); - -procedure edit_name ( - --/** Change the name, label and/or description of the folder - -- @author Karl Goldstein - -- @param folder_id The id of the folder to modify - -- @param name The new name for the folder. An error will be thrown if - -- an item with this name already exists under this folder's - -- parent. If this parameter is null, the old name will be preserved - -- @param label The new label for the folder. The old label will be preserved if - -- this parameter is null - -- @param label The new description for the folder. The old description - -- will be preserved if this parameter is null - -- @see {content_folder.new} - --*/ - folder_id in cr_folders.folder_id%TYPE, - name in cr_items.name%TYPE default null, - label in cr_folders.label%TYPE default null, - description in cr_folders.description%TYPE default null -); - -procedure move ( - --/** Recursively move the folder and all items in into a new location. - -- An error is thrown if either of the parameters is not a folder. - -- The root folder of the sitemap and the root folder of the - -- templates cannot be moved. - -- @author Karl Goldstein - -- @param folder_id The id of the folder to move - -- @param target_folder_id The destination folder - -- @see {content_folder.new}, {content_folder.copy} - --*/ - folder_id in cr_folders.folder_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - name in cr_items.name%TYPE default null -); - -procedure copy ( - --/** Recursively copy the folder and all items in into a new location. - -- An error is thrown if either of the parameters is not a folder. - -- The root folder of the sitemap and the root folder of the - -- templates cannot be copied - -- @author Karl Goldstein - -- @param folder_id The id of the folder to copy - -- @param target_folder_id The destination folder - -- @param creation_user The id of the creation user - -- @param creation_ip The IP address of the creation user (defaults to null) - -- @see {content_folder.new}, {content_folder.copy} - --*/ - folder_id in cr_folders.folder_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE default null, - name in cr_items.name%TYPE default null -); - -function is_folder ( - --/** Determine if the item is a folder - -- @author Karl Goldstein - -- @param item_id The item id - -- @return 't' if the item is a folder, 'f' otherwise - -- @see {content_folder.new}, {content_folder.is_sub_folder} - --*/ - item_id in cr_items.item_id%TYPE -) return char; - -function is_sub_folder ( - --/** Determine if the item target_folder_id is a subfolder of - -- the item folder_id - -- @author Karl Goldstein - -- @param folder_id The superfolder id - -- @param target_folder_id The subfolder id - -- @return 't' if the item target_folder_id is a subfolder of - -- the item folder_id, 'f' otherwise - -- @see {content_folder.is_folder} - --*/ - folder_id in cr_folders.folder_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE -) return char; - -function is_empty ( - --/** Determine if the folder is empty - -- @author Karl Goldstein - -- @param folder_id The folder id - -- @return 't' if the folder contains no subfolders or items, 'f' otherwise - -- @see {content_folder.is_folder} - --*/ - folder_id in cr_folders.folder_id%TYPE -) return varchar2; - -function is_root ( - --/** Determine whether the folder is a root (has a parent_id of 0) - -- @author Karl Goldstein - -- @param folder_id The folder ID - -- @return 't' if the folder is a root or 'f' otherwise - --*/ - folder_id in cr_folders.folder_id%TYPE -) return char; - -procedure register_content_type ( - --/** Register a content type to the folder, if it is not already registered. - -- Only items of the registered type(s) may be added to the folder. - -- @author Karl Goldstein - -- @param folder_id The folder id - -- @param content_type The content type to be registered - -- @see {content_folder.unregister_content_type}, - -- {content_folder.is_registered} - --*/ - folder_id in cr_folders.folder_id%TYPE, - content_type in cr_folder_type_map.content_type%TYPE, - include_subtypes in varchar2 default 'f' -); - -procedure unregister_content_type ( - --/** Unregister a content type from the folder, if it has been registered. - -- Only items of the registered type(s) may be added to the folder. - -- If the folder already contains items of the type to be unregistered, the - -- items remain in the folder. - -- @author Karl Goldstein - -- @param folder_id The folder id - -- @param content_type The content type to be unregistered - -- @param include_subtypes If 't', all subtypes of content_type will be - -- unregistered as well - -- @see {content_folder.register_content_type}, {content_folder.is_registered} - --*/ - folder_id in cr_folders.folder_id%TYPE, - content_type in cr_folder_type_map.content_type%TYPE, - include_subtypes in varchar2 default 'f' -); - --- change this to is_type_registered -function is_registered ( - --/** Determines if a content type is registered to the folder - -- Only items of the registered type(s) may be added to the folder. - -- @author Karl Goldstein - -- @param folder_id The folder id - -- @param content_type The content type to be checked - -- @param include_subtypes If 't', all subtypes of the content_type - -- will be checked, returning 't' if all of them are registered. If 'f', - -- only an exact match with content_type will be - -- performed. - -- @return 't' if the type is registered to this folder, 'f' otherwise - -- @see {content_folder.register_content_type}, {content_folder.unregister_content_type}, - --*/ - folder_id in cr_folders.folder_id%TYPE, - content_type in cr_folder_type_map.content_type%TYPE, - include_subtypes in varchar2 default 'f' -) return varchar2; - - -function get_label ( - --/** Returns the label for the folder. This function is the default name method - -- for the folder object. - -- @author Karl Goldstein - -- @param folder_id The folder id - -- @return The folder's label - -- @see {acs_object_type.create_type}, the docs for the name_method parameter - --*/ - folder_id in cr_folders.folder_id%TYPE -) return cr_folders.label%TYPE; - - -function get_index_page ( - --/** Returns the item ID of the index page of the folder, null otherwise - -- @author Michael Pih - -- @param folder_id The folder id - -- @return The item ID of the index page - --*/ - folder_id in cr_folders.folder_id%TYPE -) return cr_items.item_id%TYPE; - - - -end content_folder; -/ -show errors - -create or replace package body content_item -as - -function get_root_folder ( - item_id in cr_items.item_id%TYPE default null -) return cr_folders.folder_id%TYPE is - - v_folder_id cr_folders.folder_id%TYPE; - -begin - - if item_id is NULL then - - v_folder_id := c_root_folder_id; - - else - - select - item_id into v_folder_id - from - cr_items - where - parent_id = 0 - connect by - prior parent_id = item_id - start with - item_id = get_root_folder.item_id; - - end if; - - return v_folder_id; - -exception - when NO_DATA_FOUND then - raise_application_error(-20000, - 'Could not find a root folder for item ID ' || item_id || '. ' || - 'Either the item does not exist or its parent value is corrupted.'); -end get_root_folder; - -function new ( - name in cr_items.name%TYPE, - parent_id in cr_items.parent_id%TYPE default null, - item_id in acs_objects.object_id%TYPE default null, - locale in cr_items.locale%TYPE default null, - creation_date in acs_objects.creation_date%TYPE - default sysdate, - creation_user in acs_objects.creation_user%TYPE - default null, - context_id in acs_objects.context_id%TYPE - default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - item_subtype in acs_object_types.object_type%TYPE - default 'content_item', - content_type in acs_object_types.object_type%TYPE - default 'content_revision', - title in cr_revisions.title%TYPE default null, - description in cr_revisions.description%TYPE default null, - mime_type in cr_revisions.mime_type%TYPE default 'text/plain', - nls_language in cr_revisions.nls_language%TYPE default null, - text in varchar2 default null, - data in cr_revisions.content%TYPE default null, - relation_tag in cr_child_rels.relation_tag%TYPE default null, - is_live in char default 'f', - storage_type in cr_items.storage_type%TYPE default 'lob' -) return cr_items.item_id%TYPE -is - v_parent_id cr_items.parent_id%TYPE; - v_parent_type acs_objects.object_type%TYPE; - v_item_id cr_items.item_id%TYPE; - v_revision_id cr_revisions.revision_id%TYPE; - v_title cr_revisions.title%TYPE; - v_rel_id acs_objects.object_id%TYPE; - v_rel_tag cr_child_rels.relation_tag%TYPE; - v_context_id acs_objects.context_id%TYPE; - v_storage_type cr_items.storage_type%TYPE; -begin - - -- if content_item.is_subclass(item_subtype,'content_item') = 'f' then - -- raise_application_error(-20000, 'The object_type ' || item_subtype || - -- ' does not inherit from content_item.'); - -- end if; - - -- place the item in the context of the pages folder if no - -- context specified - - if storage_type = 'text' then - v_storage_type := 'lob'; - else - v_storage_type := storage_type; - end if; - - if parent_id is null then - v_parent_id := c_root_folder_id; - else - v_parent_id := parent_id; - end if; - - -- Determine context_id - if context_id is null then - v_context_id := v_parent_id; - else - v_context_id := context_id; - end if; - - if v_parent_id = 0 or - content_folder.is_folder(v_parent_id) = 't' then - - if v_parent_id ^= 0 and - content_folder.is_registered( - v_parent_id, content_item.new.content_type, 'f') = 'f' then - - raise_application_error(-20000, - 'This item''s content type ' || content_item.new.content_type || - ' is not registered to this folder ' || v_parent_id); - - end if; - - elsif v_parent_id ^= 0 then - - begin - - -- Figure out the relation_tag to use - if content_item.new.relation_tag is null then - v_rel_tag := content_item.get_content_type(v_parent_id) - || '-' || content_item.new.content_type; - else - v_rel_tag := content_item.new.relation_tag; - end if; - - select object_type into v_parent_type from acs_objects - where object_id = v_parent_id; - - if is_subclass(v_parent_type, 'content_item') = 't' and - is_valid_child(v_parent_id, content_item.new.content_type, v_rel_tag) = 'f' then - - raise_application_error(-20000, - 'This item''s content type ' || content_item.new.content_type || - ' is not allowed in this container ' || v_parent_id); - - end if; - - exception when NO_DATA_FOUND then - - raise_application_error(-20000, - 'Invalid parent ID ' || v_parent_id || - ' specified in content_item.new'); - - end; - - end if; - - -- Create the object - - v_item_id := acs_object.new( - object_id => content_item.new.item_id, - object_type => content_item.new.item_subtype, - context_id => v_context_id, - creation_date => content_item.new.creation_date, - creation_user => content_item.new.creation_user, - creation_ip => content_item.new.creation_ip - ); - - -- Turn off security inheritance if there is no security context - --if context_id is null then - -- update acs_objects set security_inherit_p = 'f' - -- where object_id = v_item_id; - --end if; - - insert into cr_items ( - item_id, name, content_type, parent_id, storage_type - ) values ( - v_item_id, content_item.new.name, - content_item.new.content_type, v_parent_id, v_storage_type - ); - - -- if the parent is not a folder, insert into cr_child_rels - -- We checked above before creating the object that it is a valid rel - if v_parent_id ^= 0 and - content_folder.is_folder(v_parent_id) = 'f' then - - v_rel_id := acs_object.new( - object_type => 'cr_item_child_rel', - context_id => v_parent_id - ); - - insert into cr_child_rels ( - rel_id, parent_id, child_id, relation_tag, order_n - ) values ( - v_rel_id, v_parent_id, v_item_id, v_rel_tag, v_item_id - ); - - end if; - - -- use the name of the item if no title is supplied - if content_item.new.title is null then - v_title := content_item.new.name; - else - v_title := content_item.new.title; - end if; - - -- create the revision if data or title or text is not null - -- note that the caller could theoretically specify both text - -- and data, in which case the text is ignored. - - if content_item.new.data is not null then - - v_revision_id := content_revision.new( - item_id => v_item_id, - title => v_title, - description => content_item.new.description, - data => content_item.new.data, - mime_type => content_item.new.mime_type, - creation_date => content_item.new.creation_date, - creation_user => content_item.new.creation_user, - creation_ip => content_item.new.creation_ip, - nls_language => content_item.new.nls_language - ); - - elsif content_item.new.title is not null or - content_item.new.text is not null then - - v_revision_id := content_revision.new( - item_id => v_item_id, - title => v_title, - description => content_item.new.description, - text => content_item.new.text, - mime_type => content_item.new.mime_type, - creation_date => content_item.new.creation_date, - creation_user => content_item.new.creation_user, - creation_ip => content_item.new.creation_ip - ); - - end if; - - -- make the revision live if is_live is 't' - if content_item.new.is_live = 't' then - content_item.set_live_revision(v_revision_id); - end if; - - -- Have the new item inherit the permission of the parent item - -- if no security context was specified - --if parent_id is not null and context_id is null then - -- content_permission.inherit_permissions ( - -- parent_id, v_item_id, creation_user - -- ); - --end if; - - return v_item_id; -end new; - -function is_published ( - item_id in cr_items.item_id%TYPE -) return char -is - v_is_published char(1); -begin - - select - 't' into v_is_published - from - cr_items - where - live_revision is not null - and - publish_status = 'live' - and - item_id = is_published.item_id; - - return v_is_published; - exception - when NO_DATA_FOUND then - return 'f'; -end is_published; - -function is_publishable ( - item_id in cr_items.item_id%TYPE -) return char -is - v_child_count integer; - v_rel_count integer; - v_template_id cr_templates.template_id%TYPE; - - -- get the child types registered to this content type - cursor c_child_types is - select - child_type, min_n, max_n - from - cr_type_children - where - parent_type = content_item.get_content_type( is_publishable.item_id ); - - -- get the relation types registered to this content type - cursor c_rel_types is - select - target_type, min_n, max_n - from - cr_type_relations - where - content_type = content_item.get_content_type( is_publishable.item_id ); - - -- get the publishing workflows associated with this content item - -- there should only be 1 if CMS exists, otherwise 0 - -- cursor c_pub_wf is - -- select - -- case_id, state - -- from - -- wf_cases - -- where - -- workflow_key = 'publishing_wf' - -- and - -- object_id = is_publishable.item_id; - -begin - - -- validate children - -- make sure the # of children of each type fall between min_n and max_n - for v_child_type in c_child_types loop - select - count(rel_id) into v_child_count - from - cr_child_rels - where - parent_id = is_publishable.item_id - and - content_item.get_content_type( child_id ) = v_child_type.child_type; - - -- make sure # of children is in range - if v_child_type.min_n is not null - and v_child_count < v_child_type.min_n then - return 'f'; - end if; - if v_child_type.max_n is not null - and v_child_count > v_child_type.max_n then - return 'f'; - end if; - - end loop; - - - -- validate relations - -- make sure the # of ext links of each type fall between min_n and max_n - for v_rel_type in c_rel_types loop - select - count(rel_id) into v_rel_count - from - cr_item_rels i, acs_objects o - where - i.related_object_id = o.object_id - and - i.item_id = is_publishable.item_id - and - nvl(content_item.get_content_type(o.object_id),o.object_type) = v_rel_type.target_type; - - -- make sure # of object relations is in range - if v_rel_type.min_n is not null - and v_rel_count < v_rel_type.min_n then - return 'f'; - end if; - if v_rel_type.max_n is not null - and v_rel_count > v_rel_type.max_n then - return 'f'; - end if; - end loop; - - -- validate publishing workflows - -- make sure any 'publishing_wf' associated with this item are finished - -- KG: logic is wrong here. Only the latest workflow matters, and even - -- that is a little problematic because more than one workflow may be - -- open on an item. In addition, this should be moved to CMS. - - -- Removed this as having workflow stuff in the CR is just plain wrong. - -- DanW, Aug 25th, 2001. - - -- for v_pub_wf in c_pub_wf loop - -- if v_pub_wf.state ^= 'finished' then - -- return 'f'; - -- end if; - -- end loop; - - return 't'; - exception - when NO_DATA_FOUND then - return 'f'; -end is_publishable; - -function is_valid_child ( - item_id in cr_items.item_id%TYPE, - content_type in acs_object_types.object_type%TYPE, - relation_tag in cr_child_rels.relation_tag%TYPE default null -) return char -is - v_is_valid_child char(1); - v_max_children cr_type_children.max_n%TYPE; - v_n_children integer; -begin - - v_is_valid_child := 'f'; - - -- first check if content_type is a registered child_type - begin - select - sum(max_n) into v_max_children - from - cr_type_children - where - parent_type = content_item.get_content_type( is_valid_child.item_id ) - and - child_type = is_valid_child.content_type - and - (is_valid_child.relation_tag is null - or is_valid_child.relation_tag = relation_tag); - - exception - when NO_DATA_FOUND then - return 'f'; - end; - - -- if the max is null then infinite number is allowed - if v_max_children is null then - return 't'; - end if; - - -- next check if there are already max_n children of that content type - select - count(rel_id) into v_n_children - from - cr_child_rels - where - parent_id = is_valid_child.item_id - and - content_item.get_content_type( child_id ) = is_valid_child.content_type - and - (is_valid_child.relation_tag is null - or is_valid_child.relation_tag = relation_tag); - - if v_n_children < v_max_children then - v_is_valid_child := 't'; - end if; - - return v_is_valid_child; - exception - when NO_DATA_FOUND then - return 'f'; -end is_valid_child; - -/* delete a content item - 1) delete all associated workflows - 2) delete all symlinks associated with this object - 3) delete any revisions for this item - 4) unregister template relations - 5) delete all permissions associated with this item - 6) delete keyword associations - 7) delete all associated comments */ -procedure del ( - item_id in cr_items.item_id%TYPE -) is - --- cursor c_wf_cases_cur is --- select --- case_id --- from --- wf_cases --- where --- object_id = item_id; - - cursor c_symlink_cur is - select - symlink_id - from - cr_symlinks - where - target_id = content_item.del.item_id; - - cursor c_revision_cur is - select - revision_id - from - cr_revisions - where - item_id = content_item.del.item_id; - - cursor c_rel_cur is - select - rel_id - from - cr_item_rels - where - item_id = content_item.del.item_id - or - related_object_id = content_item.del.item_id; - - cursor c_child_cur is - select - rel_id - from - cr_child_rels - where - child_id = content_item.del.item_id; - - cursor c_parent_cur is - select - rel_id, child_id - from - cr_child_rels - where - parent_id = content_item.del.item_id; - - -- this is strictly for debugging - -- cursor c_error_cur is - -- select - -- object_id, object_type - -- from - -- acs_objects - -- where - -- context_id = content_item.delete.item_id; - -begin - - -- Removed this as having workflow stuff in the CR is just plain wrong. - -- DanW, Aug 25th, 2001. - - -- dbms_output.put_line('Deleting associated workflows...'); - -- 1) delete all workflow cases associated with this item - -- for v_wf_cases_val in c_wf_cases_cur loop - -- workflow_case.delete(v_wf_cases_val.case_id); - -- end loop; - - dbms_output.put_line('Deleting symlinks...'); - -- 2) delete all symlinks to this item - for v_symlink_val in c_symlink_cur loop - content_symlink.del(v_symlink_val.symlink_id); - end loop; - - dbms_output.put_line('Unscheduling item...'); - delete from cr_release_periods - where item_id = content_item.del.item_id; - - dbms_output.put_line('Deleting associated revisions...'); - -- 3) delete all revisions of this item - delete from cr_item_publish_audit - where item_id = content_item.del.item_id; - for v_revision_val in c_revision_cur loop - content_revision.del(v_revision_val.revision_id); - end loop; - - dbms_output.put_line('Deleting associated item templates...'); - -- 4) unregister all templates to this item - delete from cr_item_template_map - where item_id = content_item.del.item_id; - - dbms_output.put_line('Deleting item relationships...'); - -- Delete all relations on this item - for v_rel_val in c_rel_cur loop - acs_rel.del(v_rel_val.rel_id); - end loop; - - dbms_output.put_line('Deleting child relationships...'); - for v_rel_val in c_child_cur loop - acs_rel.del(v_rel_val.rel_id); - end loop; - - dbms_output.put_line('Deleting parent relationships...'); - for v_rel_val in c_parent_cur loop - acs_rel.del(v_rel_val.rel_id); - content_item.del(v_rel_val.child_id); - end loop; - - dbms_output.put_line('Deleting associated permissions...'); - -- 5) delete associated permissions - delete from acs_permissions - where object_id = content_item.del.item_id; - - dbms_output.put_line('Deleting keyword associations...'); - -- 6) delete keyword associations - delete from cr_item_keyword_map - where item_id = content_item.del.item_id; - - dbms_output.put_line('Deleting associated comments...'); - -- 7) delete associated comments - journal_entry.delete_for_object( content_item.del.item_id ); - - -- context_id debugging loop - --for v_error_val in c_error_cur loop - -- dbms_output.put_line('ID=' || v_error_val.object_id || ' TYPE=' - -- || v_error_val.object_type); - --end loop; - - dbms_output.put_line('Deleting content item...'); - acs_object.del(content_item.del.item_id); - -end del; - - -procedure edit_name ( - item_id in cr_items.item_id%TYPE, - name in cr_items.name%TYPE -) is - cursor exists_cur is - select - item_id - from - cr_items - where - cr_items.name = content_item.edit_name.name - and - parent_id = (select - parent_id - from - cr_items - where - cr_items.item_id = content_item.edit_name.item_id); - - exists_id integer; -begin - - open exists_cur; - fetch exists_cur into exists_id; - - if exists_cur%NOTFOUND then - close exists_cur; - update cr_items - set cr_items.name = content_item.edit_name.name - where cr_items.item_id = content_item.edit_name.item_id; - else - close exists_cur; - if exists_id <> item_id then - raise_application_error(-20000, - 'An item with the name ' || name || - ' already exists in this directory.'); - end if; - end if; - -end edit_name; - -function get_id ( - item_path in varchar2, - root_folder_id in cr_items.item_id%TYPE default c_root_folder_id, - resolve_index in char default 'f' -) return cr_items.item_id%TYPE is - - v_item_path varchar2(4000); - v_root_folder_id cr_items.item_id%TYPE; - parent_id integer; - child_id integer; - start_pos integer := 1; - end_pos integer; - counter integer := 0; - item_name varchar2(200); - -begin - - v_root_folder_id := nvl(root_folder_id, c_root_folder_id); - - -- If the request path is the root, then just return the root folder - if item_path = '/' then - return v_root_folder_id; - end if; - - -- Remove leading, trailing spaces, leading slashes - v_item_path := rtrim(ltrim(trim(item_path), '/'), '/'); - - parent_id := v_root_folder_id; - - -- if parent_id is a symlink, resolve it - parent_id := content_symlink.resolve(parent_id); - - loop - - end_pos := instr(v_item_path, '/', start_pos); - - if end_pos = 0 then - item_name := substr(v_item_path, start_pos); - else - item_name := substr(v_item_path, start_pos, end_pos - start_pos); - end if; - - select - item_id into child_id - from - cr_items - where - parent_id = get_id.parent_id - and - name = item_name; - - exit when end_pos = 0; - - parent_id := child_id; - - -- if parent_id is a symlink, resolve it - parent_id := content_symlink.resolve(parent_id); - - start_pos := end_pos + 1; - - end loop; - - if get_id.resolve_index = 't' then - - -- if the item is a folder and has an index page, then return - - if content_folder.is_folder( child_id ) = 't' and - content_folder.get_index_page( child_id ) is not null then - - child_id := content_folder.get_index_page( child_id ); - - end if; - - end if; - - return child_id; - -exception - when NO_DATA_FOUND then - return null; -end get_id; - -function get_path ( - item_id in cr_items.item_id%TYPE, - root_folder_id in cr_items.item_id%TYPE default null -) return varchar2 -is - - cursor c_abs_cur is - select - name, parent_id, level as tree_level - from - cr_items - where - parent_id <> 0 - connect by - prior parent_id = item_id - start with - item_id = get_path.item_id - order by - tree_level desc; - - v_count integer; - v_name varchar2(400); - v_parent_id integer := 0; - v_tree_level integer; - - v_resolved_root_id integer; - - cursor c_rel_cur is - select - parent_id, level as tree_level - from - cr_items - where - parent_id <> 0 - connect by - prior parent_id = item_id - start with - item_id = v_resolved_root_id - order by - tree_level desc; - - v_rel_parent_id integer := 0; - v_rel_tree_level integer := 0; - - v_path varchar2(4000) := ''; - -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_application_error(-20000, 'Invalid item ID: ' || item_id); - end if; - - -- begin walking down the path to the item (from the repository root) - open c_abs_cur; - - -- if the root folder is not null then prepare for a relative path - - if 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(root_folder_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 - - open c_rel_cur; - - while v_parent_id = v_rel_parent_id loop - fetch c_abs_cur into v_name, v_parent_id, v_tree_level; - fetch c_rel_cur into v_rel_parent_id, v_rel_tree_level; - exit when c_abs_cur%NOTFOUND or c_rel_cur%NOTFOUND; - end loop; - - -- walk the remainder of the relative path, add a '..' for each - -- additional step - - loop - exit when c_rel_cur%NOTFOUND; - v_path := v_path || '../'; - fetch c_rel_cur into v_rel_parent_id, v_rel_tree_level; - end loop; - close c_rel_cur; - - -- an item relative to itself is '../item' - if v_resolved_root_id = item_id then - v_path := '../'; - end if; - - else - - -- this is an absolute path so prepend a '/' - v_path := '/'; - - -- prime the pump to be consistent with relative path execution plan - fetch c_abs_cur into v_name, v_parent_id, v_tree_level; - - end if; - - -- loop over the remainder of the absolute path - - loop - - v_path := v_path || v_name; - - fetch c_abs_cur into v_name, v_parent_id, v_tree_level; - - exit when c_abs_cur%NOTFOUND; - - v_path := v_path || '/'; - - end loop; - close c_abs_cur; - - return v_path; - -end get_path; - - -function get_virtual_path ( - item_id in cr_items.item_id%TYPE, - root_folder_id in cr_items.item_id%TYPE default c_root_folder_id -) return varchar2 -is - v_path varchar2(4000); - v_item_id cr_items.item_id%TYPE; - v_is_folder char(1); - v_index cr_items.item_id%TYPE; -begin - - -- first resolve the item - v_item_id := content_symlink.resolve( get_virtual_path.item_id ); - - v_is_folder := content_folder.is_folder( v_item_id ); - v_index := content_folder.get_index_page( v_item_id ); - - -- if the folder has an index page - if v_is_folder = 't' and v_index is not null then - v_path := content_item.get_path( content_symlink.resolve( v_index )); - else - v_path := content_item.get_path( v_item_id ); - end if; - - return v_path; - exception - when NO_DATA_FOUND then - return null; -end get_virtual_path; - - - -procedure write_to_file ( - item_id in cr_items.item_id%TYPE, - root_path in varchar2 -)is - - blob_loc cr_revisions.content%TYPE; - v_revision cr_items.live_revision%TYPE; - -begin - - v_revision := get_live_revision(item_id); - select content into blob_loc from cr_revisions - where revision_id = v_revision; - - blob_to_file(root_path || get_path(item_id), blob_loc); - -exception when no_data_found then - - raise_application_error(-20000, 'No live revision for content item' || - item_id || ' in content_item.write_to_file.'); - -end write_to_file; - -procedure register_template ( - item_id in cr_items.item_id%TYPE, - template_id in cr_templates.template_id%TYPE, - use_context in cr_item_template_map.use_context%TYPE -) is - -begin - - -- register template if it is not already registered - insert into cr_item_template_map ( - template_id, item_id, use_context - ) select - register_template.template_id, - register_template.item_id, - register_template.use_context - from - dual - where - not exists ( select 1 - from - cr_item_template_map - where - item_id = register_template.item_id - and - template_id = register_template.template_id - and - use_context = register_template.use_context ); - -end register_template; - -procedure unregister_template ( - item_id in cr_items.item_id%TYPE, - template_id in cr_templates.template_id%TYPE default null, - use_context in cr_item_template_map.use_context%TYPE default null -) is - -begin - - if use_context is null and template_id is null then - - delete from cr_item_template_map - where item_id = unregister_template.item_id; - - elsif use_context is null then - - delete from cr_item_template_map - where template_id = unregister_template.template_id - and item_id = unregister_template.item_id; - - elsif template_id is null then - - delete from cr_item_template_map - where item_id = unregister_template.item_id - and use_context = unregister_template.use_context; - - else - - delete from cr_item_template_map - where template_id = unregister_template.template_id - and item_id = unregister_template.item_id - and use_context = unregister_template.use_context; - - end if; - -end unregister_template; - -function get_template ( - item_id in cr_items.item_id%TYPE, - use_context in cr_item_template_map.use_context%TYPE -) return cr_templates.template_id%TYPE is - - v_template_id cr_templates.template_id%TYPE; - v_content_type cr_items.content_type%TYPE; - - cursor item_cur is - select - template_id - from - cr_item_template_map - where - item_id = get_template.item_id - and - use_context = get_template.use_context; - -begin - - -- look for a template assigned specifically to this item - open item_cur; - fetch item_cur into v_template_id; - - -- otherwise get the default for the content type - if item_cur%NOTFOUND then - select - m.template_id - into - v_template_id - from - cr_items i, cr_type_template_map m - where - i.item_id = get_template.item_id - and - i.content_type = m.content_type - and - m.use_context = get_template.use_context - and - m.is_default = 't'; - end if; - close item_cur; - - return v_template_id; - -exception - when NO_DATA_FOUND then - if item_cur%ISOPEN then - close item_cur; - end if; - return null; -end get_template; - --- Return the object type of this item - -function get_content_type ( - item_id in cr_items.item_id%TYPE -) return cr_items.content_type%TYPE is - v_content_type cr_items.content_type%TYPE; -begin - - select - content_type into v_content_type - from - cr_items - where - item_id = get_content_type.item_id; - - return v_content_type; -exception - when NO_DATA_FOUND then - return null; -end get_content_type; - -function get_live_revision ( - item_id in cr_items.item_id%TYPE -) return cr_revisions.revision_id%TYPE is - - v_revision_id acs_objects.object_id%TYPE; - -begin - - select - live_revision into v_revision_id - from - cr_items - where - item_id = get_live_revision.item_id; - - return v_revision_id; - -exception - when NO_DATA_FOUND then - return null; -end get_live_revision; - -procedure set_live_revision ( - revision_id in cr_revisions.revision_id%TYPE, - publish_status in cr_items.publish_status%TYPE default 'ready' -) is -begin - - update - cr_items - set - live_revision = set_live_revision.revision_id, - publish_status = set_live_revision.publish_status - where - item_id = (select - item_id - from - cr_revisions - where - revision_id = set_live_revision.revision_id); - - update - cr_revisions - set - publish_date = sysdate - where - revision_id = set_live_revision.revision_id; - -end set_live_revision; - - -procedure unset_live_revision ( - item_id in cr_items.item_id%TYPE -) is -begin - - update - cr_items - set - live_revision = NULL - where - item_id = unset_live_revision.item_id; - - -- if an items publish status is "live", change it to "ready" - update - cr_items - set - publish_status = 'production' - where - publish_status = 'live' - and - item_id = unset_live_revision.item_id; - - -end unset_live_revision; - - -procedure set_release_period ( - item_id in cr_items.item_id%TYPE, - start_when date default null, - end_when date default null -) is - - v_count integer; - -begin - - select decode(count(*),0,0,1) into v_count from cr_release_periods - where item_id = set_release_period.item_id; - - if v_count = 0 then - - insert into cr_release_periods ( - item_id, start_when, end_when - ) values ( - item_id, start_when, end_when - ); - - else - - update cr_release_periods - set start_when = set_release_period.start_when, - end_when = set_release_period.end_when - where - item_id = set_release_period.item_id; - - end if; - -end set_release_period; - - -function get_revision_count ( - item_id in cr_items.item_id%TYPE -) return number is - - v_count integer; - -begin - - select - count(*) into v_count - from - cr_revisions - where - item_id = get_revision_count.item_id; - - return v_count; - -end get_revision_count; - -function get_context ( - item_id in cr_items.item_id%TYPE -) return acs_objects.context_id%TYPE is - - v_context_id acs_objects.context_id%TYPE; - -begin - - select - context_id - into - v_context_id - from - acs_objects - where - object_id = get_context.item_id; - - return v_context_id; - -exception when no_data_found then - - raise_application_error(-20000, 'Content item ' || item_id || - ' does not exist in content_item.get_context'); - - -end get_context; - --- 1) make sure we are not moving the item to an invalid location: --- that is, the destination folder exists and is a valid folder --- 2) make sure the content type of the content item is registered --- to the target folder --- 3) update the parent_id for the item -procedure move ( - item_id in cr_items.item_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - name in cr_items.name%TYPE default null -) is -begin - - if content_folder.is_folder(item_id) = 't' then - content_folder.move(item_id, target_folder_id, name); - elsif content_folder.is_folder(target_folder_id) = 't' then - - - if content_folder.is_registered( move.target_folder_id, - get_content_type( move.item_id )) = 't' and - content_folder.is_registered( move.target_folder_id, - get_content_type( content_symlink.resolve( move.item_id)),'f') = 't' - then - - -- update the parent_id for the item - update cr_items - set parent_id = move.target_folder_id, - name = nvl (move.name, cr_items.name) - where item_id = move.item_id; - end if; - - end if; -end move; - -procedure copy ( - item_id in cr_items.item_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE default null, - name in cr_items.name%TYPE default null -) is - - copy_id cr_items.item_id%TYPE; - -begin - - copy_id := copy2(item_id, target_folder_id, creation_user, creation_ip, name); - -end copy; - --- copy a content item to a target folder --- 1) make sure we are not copying the item to an invalid location: --- that is, the destination folder exists, is a valid folder, --- and is not the current folder --- 2) make sure the content type of the content item is registered --- with the current folder --- 3) create a new item with no revisions in the target folder --- 4) copy the latest revision from the original item to the new item (if any) - -function copy2 ( - item_id in cr_items.item_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE default null, - name in cr_items.name%TYPE default null -) return cr_items.item_id%TYPE is - 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 char(1); - v_old_revision_id cr_revisions.revision_id%TYPE; - v_new_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(copy2.item_id) = 't' then - content_folder.copy( - folder_id => copy2.item_id, - target_folder_id => copy2.target_folder_id, - creation_user => copy2.creation_user, - creation_ip => copy2.creation_ip, - name => copy2.name - ); - -- call content_symlink.copy if the item is a symlink - elsif content_symlink.is_symlink(copy2.item_id) = 't' then - content_symlink.copy( - symlink_id => copy2.item_id, - target_folder_id => copy2.target_folder_id, - creation_user => copy2.creation_user, - creation_ip => copy2.creation_ip, - name => copy2.name - ); - -- call content_extlink.copy if the item is a extlink - elsif content_extlink.is_extlink(copy2.item_id) = 't' then - content_extlink.copy( - extlink_id => copy2.item_id, - target_folder_id => copy2.target_folder_id, - creation_user => copy2.creation_user, - creation_ip => copy2.creation_ip, - name => copy2.name - ); - -- call content_extlink.copy if the item is a extlink - elsif content_extlink.is_extlink(copy2.item_id) = 't' then - content_extlink.copy( - extlink_id => copy2.item_id, - target_folder_id => copy2.target_folder_id, - creation_user => copy2.creation_user, - creation_ip => copy2.creation_ip - ); - -- make sure the target folder is really a folder - elsif content_folder.is_folder(copy2.target_folder_id) = 't' then - - select - parent_id - into - v_current_folder_id - from - cr_items - where - item_id = copy2.item_id; - - select - content_type, name , locale, - nvl(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 = copy2.item_id; - - -- can't copy to the same folder unless name is different - if copy2.target_folder_id ^= v_current_folder_id or (v_name != copy2.name and copy2.name is not null) then - - if copy2.name is not null then - v_name := copy2.name; - end if; - -- make sure the content type of the item is registered to the folder - v_is_registered := content_folder.is_registered( - folder_id => copy2.target_folder_id, - content_type => v_content_type, - include_subtypes => 'f' - ); - - if v_is_registered = 't' then - -- create the new content item - v_item_id := content_item.new( - parent_id => copy2.target_folder_id, - name => v_name, - locale => v_locale, - content_type => v_content_type, - creation_user => copy2.creation_user, - creation_ip => copy2.creation_ip, - storage_type => v_storage_type - ); - - -- get the latest revision of the old item - select - latest_revision into v_old_revision_id - from - cr_items - where - item_id = copy2.item_id; - - -- 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 ( - revision_id => v_old_revision_id, - target_item_id => v_item_id, - creation_user => copy2.creation_user, - creation_ip => copy2.creation_ip - ); - end if; - end if; - - - end if; - end if; - - return v_item_id; - -end copy2; - --- get the latest revision for an item -function get_latest_revision ( - item_id in cr_items.item_id%TYPE -) return cr_revisions.revision_id%TYPE is - v_revision_id integer; - - cursor c_revision_cur is - select - r.revision_id - from - cr_revisions r, acs_objects o - where - r.revision_id = o.object_id - and - r.item_id = get_latest_revision.item_id - order by - o.creation_date desc; -begin - - if item_id is null then - return null; - end if; - - open c_revision_cur; - fetch c_revision_cur into v_revision_id; - if c_revision_cur%NOTFOUND then - close c_revision_cur; - return null; - end if; - close c_revision_cur; - return v_revision_id; - -exception - when NO_DATA_FOUND then - if c_revision_cur%ISOPEN then - close c_revision_cur; - end if; - return null; -end get_latest_revision; - - - -function get_best_revision ( - item_id in cr_items.item_id%TYPE -) return cr_revisions.revision_id%TYPE -is - v_revision_id cr_revisions.revision_id%TYPE; -begin - - select - NVL (live_revision, latest_revision ) - into - v_revision_id - from - cr_items - where - item_id = get_best_revision.item_id; - - return v_revision_id; -exception - when NO_DATA_FOUND then - return null; -end get_best_revision; - - - -function get_title ( - item_id in cr_items.item_id%TYPE, - is_live in char default 'f' -) return cr_revisions.title%TYPE is - - v_title cr_revisions.title%TYPE; - v_content_type cr_items.content_type%TYPE; - -begin - - select content_type into v_content_type from cr_items - where item_id = get_title.item_id; - - if v_content_type = 'content_folder' then - select label into v_title from cr_folders - where folder_id = get_title.item_id; - elsif v_content_type = 'content_symlink' then - select label into v_title from cr_symlinks - where symlink_id = get_title.item_id; - elsif v_content_type = 'content_extlink' then - select label into v_title from cr_extlinks - where extlink_id = get_title.item_id; - else - if is_live ^= 'f' then - select - title into v_title - from - cr_revisions r, cr_items i - where - i.item_id = get_title.item_id - and - r.revision_id = i.live_revision; - else - select - title into v_title - from - cr_revisions r, cr_items i - where - i.item_id = get_title.item_id - and - r.revision_id = i.latest_revision; - end if; - end if; - - return v_title; - -end get_title; - -function get_publish_date ( - item_id in cr_items.item_id%TYPE, - is_live in char default 'f' -) return cr_revisions.publish_date%TYPE -is - v_revision_id cr_revisions.revision_id%TYPE; - v_publish_date cr_revisions.publish_date%TYPE; -begin - - if is_live ^= 'f' then - select - publish_date into v_publish_date - from - cr_revisions r, cr_items i - where - i.item_id = get_publish_date.item_id - and - r.revision_id = i.live_revision; - else - select - publish_date into v_publish_date - from - cr_revisions r, cr_items i - where - i.item_id = get_publish_date.item_id - and - r.revision_id = i.latest_revision; - end if; - - return v_publish_date; - -exception when no_data_found then - return null; -end get_publish_date; - -function is_subclass ( - object_type in acs_object_types.object_type%TYPE, - supertype in acs_object_types.supertype%TYPE -) return char is - - v_subclass_p char; - - cursor c_inherit_cur is - select - object_type - from - acs_object_types - connect by - prior object_type = supertype - start with - object_type = is_subclass.supertype; - -begin - - v_subclass_p := 'f'; - - for v_inherit_val in c_inherit_cur loop - if v_inherit_val.object_type = is_subclass.object_type then - v_subclass_p := 't'; - end if; - end loop; - - return v_subclass_p; - -end is_subclass; - -function relate ( - item_id in cr_items.item_id%TYPE, - object_id in acs_objects.object_id%TYPE, - relation_tag in cr_type_relations.relation_tag%TYPE default 'generic', - order_n in cr_item_rels.order_n%TYPE default null, - relation_type in acs_object_types.object_type%TYPE default 'cr_item_rel' -) return cr_item_rels.rel_id%TYPE -is - v_content_type cr_items.content_type%TYPE; - v_object_type acs_objects.object_type%TYPE; - v_is_valid integer; - v_rel_id integer; - v_exists integer; - v_order_n cr_item_rels.order_n%TYPE; -begin - - -- check the relationship is valid - v_content_type := content_item.get_content_type ( relate.item_id ); - v_object_type := content_item.get_content_type ( relate.object_id ); - - select - decode( count(1),0,0,1) into v_is_valid - from - cr_type_relations - where - content_item.is_subclass( v_object_type, target_type ) = 't' - and - content_item.is_subclass( v_content_type, content_type ) = 't'; - - if v_is_valid = 0 then - raise_application_error(-20000, - 'There is no registered relation type matching this item relation.'); - end if; - - if relate.item_id ^= relate.object_id then - -- check that these two items are not related already - --dbms_output.put_line( 'checking if the items are already related...'); - begin - select - rel_id, 1 as v_exists into v_rel_id, v_exists - from - cr_item_rels - where - item_id = relate.item_id - and - related_object_id = relate.object_id - and - relation_tag = relate.relation_tag; - exception when no_data_found then - v_exists := 0; - end; - - - -- if order_n is null, use rel_id (the order the item was related) - if relate.order_n is null then - v_order_n := v_rel_id; - else - v_order_n := relate.order_n; - end if; - - - -- if relationship does not exist, create it - if v_exists <> 1 then - --dbms_output.put_line( 'creating new relationship...'); - v_rel_id := acs_object.new( - object_type => relation_type, - context_id => item_id - ); - insert into cr_item_rels ( - rel_id, item_id, related_object_id, order_n, relation_tag - ) values ( - v_rel_id, item_id, object_id, v_order_n, relation_tag - ); - - -- if relationship already exists, update it - else - --dbms_output.put_line( 'updating existing relationship...'); - update cr_item_rels set - relation_tag = relate.relation_tag, - order_n = v_order_n - where - rel_id = v_rel_id; - end if; - - end if; - return v_rel_id; -end relate; - - -procedure unrelate ( - rel_id in cr_item_rels.rel_id%TYPE -) is -begin - - -- delete the relation object - acs_rel.del( unrelate.rel_id ); - - -- delete the row from the cr_item_rels table - delete from cr_item_rels where rel_id = unrelate.rel_id; - -end unrelate; - -function is_index_page ( - item_id in cr_items.item_id%TYPE, - folder_id in cr_folders.folder_id%TYPE -) return varchar2 -is -begin - if content_folder.get_index_page(folder_id) = item_id then - return 't'; - else - return 'f'; - end if; -end is_index_page; - - - -function get_parent_folder ( - item_id in cr_items.item_id%TYPE -) return cr_folders.folder_id%TYPE -is - v_folder_id cr_folders.folder_id%TYPE; - v_parent_folder_p char(1); -begin - v_parent_folder_p := 'f'; - v_folder_id := get_parent_folder.item_id; - - while v_parent_folder_p = 'f' and v_folder_id is not null loop - select - parent_id, content_folder.is_folder( parent_id ) - into - v_folder_id, v_parent_folder_p - from - cr_items - where - item_id = v_folder_id; - - end loop; - - return v_folder_id; - -end get_parent_folder; - -end content_item; -/ -show errors - -create or replace package body content_folder -as - -function new ( - name in cr_items.name%TYPE, - label in cr_folders.label%TYPE, - description in cr_folders.description%TYPE default null, - parent_id in cr_items.parent_id%TYPE default null, - context_id in acs_objects.context_id%TYPE default null, - folder_id in cr_folders.folder_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 -) return cr_folders.folder_id%TYPE is - v_folder_id cr_folders.folder_id%TYPE; - v_context_id acs_objects.context_id%TYPE; -begin - - -- set the context_id - if content_folder.new.context_id is null then - v_context_id := content_folder.new.parent_id; - else - v_context_id := content_folder.new.context_id; - end if; - - -- parent_id = 0 means that this is a mount point - if parent_id ^= 0 and - content_folder.is_registered(parent_id,'content_folder') = 'f' then - - raise_application_error(-20000, - 'This folder does not allow subfolders to be created'); - else - - v_folder_id := content_item.new( - item_id => folder_id, - name => name, - item_subtype => 'content_folder', - content_type => 'content_folder', - context_id => v_context_id, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - parent_id => parent_id - ); - - insert into cr_folders ( - folder_id, label, description - ) values ( - v_folder_id, label, description - ); - - -- inherit the attributes of the parent folder - if content_folder.new.parent_id is not null then - - insert into cr_folder_type_map ( - folder_id, content_type - ) select - v_folder_id, content_type - from - cr_folder_type_map - where - folder_id = content_folder.new.parent_id; - end if; - - -- update the child flag on the parent - update cr_folders set has_child_folders = 't' - where folder_id = content_folder.new.parent_id; - - return v_folder_id; - end if; - -end new; - -procedure del ( - folder_id in cr_folders.folder_id%TYPE, - cascade_p in char default 'f' -) is - - v_count integer; - v_parent_id cr_items.parent_id%TYPE; - v_child_item_id cr_items.item_id%TYPE; - - cursor c_folder_children_cur is - select - item_id - from - cr_items - connect by - prior item_id=parent_id - start with parent_id = del.folder_id; - -begin - - -- check if the folder contains any items - - select count(*) into v_count from cr_items where parent_id = folder_id; - - if v_count > 0 and content_folder.del.cascade_p='f' then - raise_application_error(-20000, - 'Folder ID ' || folder_id || ' (' || content_item.get_path(folder_id) || - ') cannot be deleted because it is not empty.'); - else - open c_folder_children_cur; - - loop - - fetch c_folder_children_cur into v_child_item_id; - exit when c_folder_children_cur%NOTFOUND; - if is_folder(v_child_item_id) = 't' then - content_folder.del(v_child_item_id,'t'); - else - - content_item.del(v_child_item_id); - end if; - end loop; - close c_folder_children_cur; - end if; - - content_folder.unregister_content_type( - folder_id => content_folder.del.folder_id, - content_type => 'content_revision', - include_subtypes => 't' ); - - delete from cr_folder_type_map - where folder_id = content_folder.del.folder_id; - - select parent_id into v_parent_id from cr_items - where item_id = content_folder.del.folder_id; - - content_item.del(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'); - -end del; - --- renames a folder, making sure the new name is not already in use -procedure edit_name ( - folder_id in cr_folders.folder_id%TYPE, - name in cr_items.name%TYPE default null, - label in cr_folders.label%TYPE default null, - description in cr_folders.description%TYPE default null -) is - v_name_already_exists_p integer := 0; -begin - - if name is not null then - content_item.edit_name(folder_id, name); - end if; - - if label is not null and description is not null then - - update cr_folders - set cr_folders.label = content_folder.edit_name.label, - cr_folders.description = content_folder.edit_name.description - where cr_folders.folder_id = content_folder.edit_name.folder_id; - - elsif label is not null and description is null then - - update cr_folders - set cr_folders.label = content_folder.edit_name.label - where cr_folders.folder_id = content_folder.edit_name.folder_id; - - end if; - -end edit_name; - - --- 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 ( - folder_id in cr_folders.folder_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - name in cr_items.name%TYPE default null -) is - v_source_folder_id integer; - v_valid_folders_p integer := 0; -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_application_error(-20000, - 'content_folder.move - Not valid folder(s)'); - end if; - - if folder_id = content_item.get_root_folder or - folder_id = content_template.get_root_folder then - raise_application_error( -20000, - 'content_folder.move - Cannot move root folder'); - end if; - - if target_folder_id = folder_id then - raise_application_error(-20000, - 'content_folder.move - Cannot move a folder to itself'); - end if; - - if is_sub_folder(folder_id, target_folder_id) = 't' then - raise_application_error(-20000, - 'content_folder.move - Destination folder is subfolder'); - end if; - - if is_registered(target_folder_id,'content_folder') ^= 't' then - raise_application_error(-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=nvl(move.name, cr_items.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 = target_folder_id; - -end move; - --- * make sure that subfolders are allowed in this folder --- * creates new folder in the target folder with the same attributes --- as the old one --- * copies all contents of folder to the new one -procedure copy ( - folder_id in cr_folders.folder_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE default null, - name in cr_items.name%TYPE default null -) is - v_valid_folders_p integer := 0; - 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; - - -- cursor: items in the folder - cursor c_folder_contents_cur is - select - item_id - from - cr_items - where - parent_id = copy.folder_id; - -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 folder_id = content_item.get_root_folder or folder_id = content_template.get_root_folder or target_folder_id = folder_id then - v_valid_folders_p := 0; - end if; - - if v_valid_folders_p = 2 then - - -- 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 is_sub_folder(folder_id, 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 - if copy.name is not null then - v_name := copy.name; - end if; - -- create the new folder - v_new_folder_id := content_folder.new( - parent_id => copy.target_folder_id, - name => nvl(copy.name,v_name), - label => v_label, - description => v_description, - creation_user => copy.creation_user, - creation_ip => copy.creation_ip - ); - - -- copy attributes of original folder - insert into cr_folder_type_map ( - folder_id, content_type - ) select - v_new_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 c_folder_contents_cur loop - - content_item.copy( - item_id => v_folder_contents_val.item_id, - target_folder_id => v_new_folder_id, - creation_user => copy.creation_user, - creation_ip => copy.creation_ip - ); - - end loop; - - end if; - end if; -end copy; - - - - - --- returns 1 if the item_id passed in is a folder -function is_folder ( - item_id in cr_items.item_id%TYPE -) return char is - - v_folder_p varchar2(1) := 'f'; - -begin - - select 't' into v_folder_p from cr_folders - where folder_id = item_id; - - return v_folder_p; - -exception - when NO_DATA_FOUND then - return 'f'; - -end is_folder; - --- target_folder_id is the possible sub folder -function is_sub_folder ( - folder_id in cr_folders.folder_id%TYPE, - target_folder_id in cr_folders.folder_id%TYPE -) return char -is - cursor c_tree_cur is - select - parent_id - from - cr_items - connect by - prior parent_id = item_id - start with - item_id = target_folder_id; - - v_parent_id integer := 0; - v_sub_folder_p char := 'f'; - -begin - - if folder_id = content_item.get_root_folder or - folder_id = content_template.get_root_folder then - v_sub_folder_p := 't'; - end if; - - -- Get the parents - open c_tree_cur; - while v_parent_id <> folder_id loop - fetch c_tree_cur into v_parent_id; - exit when c_tree_cur%NOTFOUND; - end loop; - close c_tree_cur; - - if v_parent_id ^= 0 then - v_sub_folder_p := 't'; - end if; - - return v_sub_folder_p; - -end is_sub_folder; - -function is_empty ( - folder_id in cr_folders.folder_id%TYPE -) return varchar2 -is - v_return varchar2(1); -begin - - select - decode( count(*), 0, 't', 'f' ) into v_return - from - cr_items - where - parent_id = is_empty.folder_id; - - return v_return; -end is_empty; - - -procedure register_content_type ( - folder_id in cr_folders.folder_id%TYPE, - content_type in cr_folder_type_map.content_type%TYPE, - include_subtypes in varchar2 default 'f' -) is - - v_is_registered varchar2(100); - -begin - - if register_content_type.include_subtypes = 'f' then - - v_is_registered := is_registered( - folder_id => register_content_type.folder_id, - content_type => register_content_type.content_type, - include_subtypes => '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 ( - folder_id, content_type - ) select - register_content_type.folder_id, object_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; - - end if; - -end register_content_type; - -procedure unregister_content_type ( - folder_id in cr_folders.folder_id%TYPE, - content_type in cr_folder_type_map.content_type%TYPE, - include_subtypes in varchar2 default 'f' -) is -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); - - end if; - -end unregister_content_type; - - - - -function is_registered ( - folder_id in cr_folders.folder_id%TYPE, - content_type in cr_folder_type_map.content_type%TYPE, - include_subtypes in varchar2 default 'f' -) return varchar2 -is - v_is_registered integer; - cursor c_subtype_cur is - 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; - -begin - - if is_registered.include_subtypes = 'f' 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 - - v_is_registered := 1; - for v_subtype_val in c_subtype_cur loop - if 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 is_registered; - -function get_label ( - folder_id in cr_folders.folder_id%TYPE -) return cr_folders.label%TYPE -is - 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 get_label; - - -function get_index_page ( - folder_id in cr_folders.folder_id%TYPE -) return cr_items.item_id%TYPE -is - 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'; - - return v_index_page_id; - -exception when no_data_found then - return null; -end get_index_page; - -function is_root ( - folder_id in cr_folders.folder_id%TYPE -) return char is - v_is_root char(1); -begin - - select decode(parent_id, 0, 't', 'f') into v_is_root - from cr_items where item_id = is_root.folder_id; - - return v_is_root; -end is_root; - -end content_folder; -/ -show errors -