-- Move old fs_simple_objects URLs to the content repository, where they
-- belong.

declare
  new_url_id     cr_extlinks.extlink_id%TYPE;
begin

    for root_folder in (select folder_id from fs_root_folders)
    loop
      for folder in (select folder_id
                     from cr_folders
                     where folder_id in (select item_id
                                         from cr_items
                                         start with item_id = root_folder.folder_id
                                         connect by prior item_id = parent_id))
      loop
  
        if content_folder.is_registered(folder.folder_id, 'content_extlink') = 'f' then
          content_folder.register_content_type(folder.folder_id, 'content_extlink');
        end if;

        if content_folder.is_registered(folder.folder_id, 'content_symlink') = 'f' then
          content_folder.register_content_type(folder.folder_id, 'content_symlink');
        end if;
  
      end loop;
    end loop;

    for fs_url in (select * from fs_urls_full)
    loop

      new_url_id := content_extlink.new (
                      url => fs_url.url,
                      label => fs_url.name,
                      description => fs_url.description,
                      parent_id => fs_url.folder_id,
                      extlink_id => null,
                      creation_date => fs_url.creation_date,
                      creation_user => fs_url.creation_user,
                      creation_ip => fs_url.creation_ip
                    );  

      update acs_objects
      set last_modified = fs_url.last_modified,
        modifying_user = fs_url.modifying_user,
        modifying_ip = fs_url.modifying_ip
      where object_id = fs_url.object_id;

      update acs_permissions
      set object_id = new_url_id
      where object_id = fs_url.object_id;

      acs_object.delete(fs_url.object_id);

    end loop;

end;
/
show errors;

drop table fs_urls;
drop table fs_simple_objects;

create or replace view fs_urls_full
as
    select cr_extlinks.extlink_id as url_id,
           cr_extlinks.url,
           cr_items.parent_id as folder_id,
           cr_extlinks.label as name,
           cr_extlinks.description,
           acs_objects.*
    from cr_extlinks,
         cr_items,
         acs_objects
    where cr_extlinks.extlink_id = cr_items.item_id
    and cr_items.item_id = acs_objects.object_id;

create or replace view fs_objects
as
    select cr_items.item_id as object_id,
      cr_items.live_revision,
      case
        when cr_items.content_type = 'content_folder' then 'folder'
        when cr_items.content_type = 'content_extlink' then 'url'
        else cr_revisions.mime_type
      end as type,
      case
        when cr_items.content_type = 'content_folder'
        then (select count(*)
              from cr_items ci
              where ci.content_type <> 'content_folder'
              connect by prior ci.item_id = ci.parent_id
              start with ci.item_id = cr_folders.folder_id)
        else cr_revisions.content_length
      end as content_size,
      case
        when cr_items.content_type = 'content_folder' then cr_folders.label
        when cr_items.content_type = 'content_extlink' then cr_extlinks.label
        else cr_items.name
      end as name,
      cr_revisions.title as file_upload_name,
      acs_objects.last_modified,
      cr_extlinks.url,
      cr_items.parent_id,
      cr_items.name as key,
      case
        when cr_items.content_type = 'content_folder' then 0
        else 1
      end as sort_key
    from cr_items, cr_extlinks, cr_folders, cr_revisions, acs_objects
    where cr_items.item_id = cr_extlinks.extlink_id(+)
      and cr_items.item_id = cr_folders.folder_id(+)
      and cr_items.item_id = acs_objects.object_id
      and cr_items.live_revision = cr_revisions.revision_id(+);

--
-- packages/file-storage/sql/file-storage-package-create.sql
--
-- @author yon (yon@openforce.net)
-- @creation-date 2002-04-03
-- @version $Id: upgrade-4.6.1-4.6.2.sql,v 1.3 2005/05/26 08:28:45 maltes Exp $
--

create or replace package file_storage
as

    function get_root_folder(
        --
        -- Returns the root folder corresponding to a particular
        -- package instance.
        --
        package_id in apm_packages.package_id%TYPE
    ) return fs_root_folders.folder_id%TYPE;

    function get_package_id(
        item_id in cr_items.item_id%TYPE
    ) return fs_root_folders.package_id%TYPE;

    function new_root_folder(
        --
        -- Creates a new root folder
        --
        package_id in apm_packages.package_id%TYPE,
        folder_name in cr_folders.label%TYPE default null,
        description in cr_folders.description%TYPE default null
    ) return fs_root_folders.folder_id%TYPE;

    function new_file(
        --
        -- Create a file in CR in preparation for actual storage
        -- Wrapper for content_item.new
        --
        item_id in cr_items.item_id%TYPE default null,
        title in cr_items.name%TYPE,
        folder_id in cr_items.parent_id%TYPE,
        creation_user in acs_objects.creation_user%TYPE,
        creation_ip in acs_objects.creation_ip%TYPE,
        indb_p in char default 't'
    ) return cr_items.item_id%TYPE;

    procedure delete_file(
        --
        -- Delete a file and all its version
        -- Wrapper to content_item.delete
        --
        file_id in cr_items.item_id%TYPE
    );

    procedure rename_file(
        --
        -- Rename a file and all
        -- Wrapper to content_item__rename
        --
        file_id in cr_items.item_id%TYPE,
        title in cr_items.name%TYPE
    );

    function copy_file(
        --
        -- Copy a file, but only copy the live_revision
        --
        file_id in cr_items.item_id%TYPE,
        target_folder_id in cr_items.parent_id%TYPE,
        creation_user in acs_objects.creation_user%TYPE,
        creation_ip in acs_objects.creation_ip%TYPE
    ) return cr_revisions.revision_id%TYPE;

    procedure move_file(
        --
        -- Move a file, and all its versions, to a new folder
        --
        file_id in cr_items.item_id%TYPE,
        target_folder_id in cr_items.parent_id%TYPE
    );

    function get_title(
        --
        -- Unfortunately, title in the file-storage context refers
        -- to the name attribute in cr_items, not the title attribute in
        -- cr_revisions
        item_id in cr_items.item_id%TYPE
    ) return varchar;

    function get_parent_id(
	item_id in cr_items.item_id%TYPE
    ) return cr_items.item_id%TYPE;

    function get_content_type(
        --
        -- Wrapper for content_item. get_content_type
        --
        item_id in cr_items.item_id%TYPE
    ) return cr_items.content_type%TYPE;

    function get_folder_name(
       --
       -- Wrapper for content_folder__get_label
       --
       folder_id in cr_folders.folder_id%TYPE
    ) return cr_folders.label%TYPE;

    function new_version(
        --
        -- Create a new version of a file
        -- Wrapper for content_revision.new
        --
        filename in cr_revisions.title%TYPE,
        description in cr_revisions.description%TYPE,
        mime_type in cr_revisions.mime_type%TYPE,
        item_id in cr_items.item_id%TYPE,
        creation_user in acs_objects.creation_user%TYPE,
        creation_ip in acs_objects.creation_ip%TYPE
    ) return cr_revisions.revision_id%TYPE;

    function delete_version(
        --
        -- Delete a version of a file
        --
        file_id in cr_items.item_id%TYPE,
        version_id in cr_revisions.revision_id%TYPE
    ) return cr_items.parent_id%TYPE;

    function new_folder(
        --
        -- Create a folder
        --
        name in cr_items.name%TYPE,
        folder_name in cr_folders.label%TYPE,
        parent_id in cr_items.parent_id%TYPE,
        creation_user in acs_objects.creation_user%TYPE,
        creation_ip in acs_objects.creation_ip%TYPE
    ) return cr_folders.folder_id%TYPE;

    procedure delete_folder(
        --
        -- Delete a folder
        --
        folder_id in cr_folders.folder_id%TYPE
    );

end file_storage;
/
show errors

create or replace package body file_storage
as

    function get_root_folder(
        package_id in apm_packages.package_id%TYPE
    ) return fs_root_folders.folder_id%TYPE
    is
        v_folder_id             fs_root_folders.folder_id%TYPE;
        v_count                 integer;
    begin
        select count(*)
        into v_count
        from fs_root_folders
        where package_id = get_root_folder.package_id;

        if v_count > 0 then
            select folder_id
            into v_folder_id
            from fs_root_folders
            where package_id = get_root_folder.package_id;
        else
            -- must be a new instance.  Gotta create a new root folder
            v_folder_id := new_root_folder(package_id);
        end if;

        return v_folder_id;
    end get_root_folder;

    function get_package_id(
        item_id in cr_items.item_id%TYPE
    ) return fs_root_folders.package_id%TYPE
    is
        v_package_id            fs_root_folders.package_id%TYPE;
    begin
        select fs_root_folders.package_id
        into v_package_id
        from fs_root_folders,
             (select cr_items.item_id
              from cr_items
              connect by prior cr_items.parent_id = cr_items.item_id
              start with cr_items.item_id = get_package_id.item_id) this
        where fs_root_folders.folder_id = this.item_id;

        return v_package_id;

    exception when NO_DATA_FOUND then
        return null;
    end get_package_id;

    function new_root_folder(
        --
        -- A hackish function to get around the fact that we can't run
        -- code automatically when a new package instance is created.
        --
        package_id in apm_packages.package_id%TYPE,
        folder_name in cr_folders.label%TYPE default null,
        description in cr_folders.description%TYPE default null
    ) return fs_root_folders.folder_id%TYPE
    is
        v_folder_id             fs_root_folders.folder_id%TYPE;
        v_package_name          apm_packages.instance_name%TYPE;
        v_package_key           apm_packages.package_key%TYPE;
        v_folder_name           cr_folders.label%TYPE;
        v_description           cr_folders.description%TYPE;
    begin
        select instance_name, package_key
        into v_package_name, v_package_key
        from apm_packages
        where package_id = new_root_folder.package_id;

        if new_root_folder.folder_name is null
        then
            v_folder_name := v_package_name || ' Root Folder';
        else
            v_folder_name := folder_name;
        end if;

        if new_root_folder.description is null
        then
            v_description := 'Root folder for the file-storage system. All other folders in file storage are subfolders of this one.';
        else
            v_description := description;
        end if;

        v_folder_id := content_folder.new(
            name => v_package_key || '_' || package_id,
            label => v_folder_name,
            description => v_description
        );

        insert
        into fs_root_folders
        (package_id, folder_id)
        values
        (package_id, v_folder_id);

        -- allow child items to be added
        content_folder.register_content_type(v_folder_id,'content_revision','t');
        content_folder.register_content_type(v_folder_id,'content_folder','t');
        content_folder.register_content_type(v_folder_id,'content_extlink','t');

        -- set up default permissions
        acs_permission.grant_permission(
            object_id => v_folder_id,
            grantee_id => acs.magic_object_id('the_public'),
            privilege => 'read'
        );

        acs_permission.grant_permission(
            object_id => v_folder_id,
            grantee_id => acs.magic_object_id('registered_users'),
            privilege => 'write'
        );

        return v_folder_id;
    end new_root_folder;

    function new_file(
        --
        -- Create a file in CR in preparation for actual storage
        -- Wrapper for content_item.new
        --
        item_id in cr_items.item_id%TYPE default null,
        title in cr_items.name%TYPE,
        folder_id in cr_items.parent_id%TYPE,
        creation_user in acs_objects.creation_user%TYPE,
        creation_ip in acs_objects.creation_ip%TYPE,
        indb_p in char default 't'
    ) return cr_items.item_id%TYPE
    is
        v_item_id               cr_items.item_id%TYPE;
    begin
        if new_file.indb_p = 't'
        then
            v_item_id := content_item.new(
                item_id => new_file.item_id,
                name => new_file.title,
                parent_id => new_file.folder_id,
                creation_user => new_file.creation_user,
                context_id => new_file.folder_id,
                creation_ip => new_file.creation_ip,
                content_type => 'file_storage_object',
                item_subtype => 'content_item'
            );
        else
            v_item_id := content_item.new(
                name => new_file.title,
                parent_id => new_file.folder_id,
                creation_user => new_file.creation_user,
                context_id => new_file.folder_id,
                creation_ip => new_file.creation_ip,
                content_type => 'file_storage_object',
                item_subtype => 'content_item',
                storage_type => 'file'
            );
        end if;

        acs_object.update_last_modified(file_storage.new_file.folder_id);

        return v_item_id;
    end new_file;

    procedure delete_file(
        --
        -- Delete a file and all its version
        -- Wrapper to content_item__delete
        --
        file_id in cr_items.item_id%TYPE
    )
    is
    begin
        content_item.delete(item_id => file_storage.delete_file.file_id);
    end delete_file;

    procedure rename_file(
        --
        -- Rename a file and all
        -- Wrapper to content_item__rename
        --
        file_id in cr_items.item_id%TYPE,
        title in cr_items.name%TYPE
    )
    is
    begin
        content_item.rename(
            item_id => file_storage.rename_file.file_id,
            name => file_storage.rename_file.title
        );
    end rename_file;

    function copy_file(
        --
        -- Copy a file, but only copy the live_revision
        --
        file_id in cr_items.item_id%TYPE,
        target_folder_id in cr_items.parent_id%TYPE,
        creation_user in acs_objects.creation_user%TYPE,
        creation_ip in acs_objects.creation_ip%TYPE
    ) return cr_revisions.revision_id%TYPE
    is
        v_title                 cr_items.name%TYPE;
        v_live_revision         cr_items.live_revision%TYPE;
        v_filename              cr_revisions.title%TYPE;
        v_description           cr_revisions.description%TYPE;
        v_mime_type             cr_revisions.mime_type%TYPE;
        v_content_length        cr_revisions.content_length%TYPE;
        v_lob                   cr_revisions.content%TYPE;
        v_file_path             cr_revisions.filename%TYPE;
        v_new_file_id           cr_items.item_id%TYPE;
        v_new_version_id        cr_revisions.revision_id%TYPE;
        v_indb_p                char;
    begin
        -- We copy only the title from the file being copied, and attributes of the
        -- live revision
        select i.name, i.live_revision, r.title, r.description,
               r.mime_type, r.content, r.filename, r.content_length,
               decode(i.storage_type,'lob','t','f')
        into v_title, v_live_revision, v_filename, v_description,
             v_mime_type, v_lob, v_file_path, v_content_length,
             v_indb_p
        from cr_items i, cr_revisions r
        where r.item_id = i.item_id
        and r.revision_id = i.live_revision
        and i.item_id = file_storage.copy_file.file_id;

        -- We should probably use the copy functions of CR
        -- when we optimize this function
        v_new_file_id := file_storage.new_file(
            title => v_title,
            folder_id => file_storage.copy_file.target_folder_id,
            creation_user => file_storage.copy_file.creation_user,
            creation_ip => file_storage.copy_file.creation_ip,
            indb_p => v_indb_p
        );

        v_new_version_id := file_storage.new_version(
            filename => v_filename,
            description => v_description,
            mime_type => v_mime_type,
            item_id => v_new_file_id,
            creation_user => file_storage.copy_file.creation_user,
            creation_ip => file_storage.copy_file.creation_ip
        );

        -- Oracle is easier, since lobs are true lobs
        -- For now, we simply copy the file name
        update cr_revisions
        set filename = v_file_path,
            content = v_lob,
            content_length = v_content_length
        where revision_id = v_new_version_id;

        acs_object.update_last_modified(file_storage.copy_file.target_folder_id);

        return v_new_version_id;
    end copy_file;

    procedure move_file(
        --
        -- Move a file, and all its versions, to a new folder
        --
        file_id in cr_items.item_id%TYPE,
        target_folder_id in cr_items.parent_id%TYPE
    )
    is
    begin
        content_item.move(
            item_id => file_storage.move_file.file_id,
            target_folder_id => file_storage.move_file.target_folder_id
        );

        acs_object.update_last_modified(file_storage.move_file.target_folder_id);

    end;

    function new_version(
        --
        -- Create a new version of a file
        -- Wrapper for content_revision.new
        --
        filename in cr_revisions.title%TYPE,
        description in cr_revisions.description%TYPE,
        mime_type in cr_revisions.mime_type%TYPE,
        item_id in cr_items.item_id%TYPE,
        creation_user in acs_objects.creation_user%TYPE,
        creation_ip in acs_objects.creation_ip%TYPE
    ) return cr_revisions.revision_id%TYPE
    is
        v_revision_id           cr_revisions.revision_id%TYPE;
        v_folder_id             cr_items.parent_id%TYPE;
    begin
        -- Create a revision
        v_revision_id := content_revision.new(
            title => new_version.filename,
            description => new_version.description,
            mime_type => new_version.mime_type,
            item_id => new_version.item_id,
            creation_user => new_version.creation_user,
            creation_ip => new_version.creation_ip
        );

        -- Make live the newly created revision
        content_item.set_live_revision(revision_id => v_revision_id);

        select cr_items.parent_id
        into v_folder_id
        from cr_items
        where cr_items.item_id = file_storage.new_version.item_id;

        acs_object.update_last_modified(v_folder_id);

        return v_revision_id;

        exception when NO_DATA_FOUND then
            return v_revision_id;
    end new_version;

    function get_title(
        --
        -- Unfortunately, title in the file-storage context refers
        -- to the name attribute in cr_items, not the title attribute in
        -- cr_revisions
        item_id in cr_items.item_id%TYPE
    ) return varchar
    is
        v_title                 cr_items.name%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;
        else if v_content_type = 'content_symlink'
            then
                select label into v_title
                from cr_symlinks
                where symlink_id = get_title.item_id;
            else
                select name into v_title
                from cr_items
                where item_id = get_title.item_id;
            end if;
        end if;

        return v_title;
    end get_title;

    function get_parent_id(
        item_id in cr_items.item_id%TYPE
    ) return cr_items.item_id%TYPE
    is
        v_parent_id             cr_items.item_id%TYPE;
    begin
        select parent_id
        into v_parent_id
        from cr_items
        where item_id = get_parent_id.item_id;

	return v_parent_id;
    end get_parent_id;

    function get_content_type(
        --
        -- Wrapper for content_item. 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
        v_content_type := content_item.get_content_type(
            item_id => file_storage.get_content_type.item_id
        );

        return v_content_type;
    end get_content_type;

    function get_folder_name(
        --
        -- Wrapper for content_folder.get_label
        --
        folder_id in cr_folders.folder_id%TYPE
    ) return cr_folders.label%TYPE
    is
       v_folder_name            cr_folders.label%TYPE;
    begin
        v_folder_name := content_folder.get_label(
            folder_id => file_storage.get_folder_name.folder_id
        );

        return v_folder_name;
    end get_folder_name;

    function delete_version(
        --
        -- Delete a version of a file
        --
        file_id in cr_items.item_id%TYPE,
        version_id in cr_revisions.revision_id%TYPE
    ) return cr_items.parent_id%TYPE
    is
        v_parent_id             cr_items.parent_id%TYPE;
    begin
        if file_storage.delete_version.version_id = content_item.get_live_revision(file_storage.delete_version.file_id)
        then
            content_revision.delete(file_storage.delete_version.version_id);
            content_item.set_live_revision(
                content_item.get_latest_revision(file_storage.delete_version.file_id)
            );
        else
            content_revision.delete(file_storage.delete_version.version_id);
        end if;

        -- If the live revision is null, we have deleted the last version above
        select decode(live_revision, null, parent_id, 0)
        into v_parent_id
        from cr_items
        where item_id = file_storage.delete_version.file_id;

        -- Unfortunately, due to PostgreSQL behavior with regards referential integrity,
        -- we cannot delete the content_item entry if there are no more revisions.
        return v_parent_id;
    end delete_version;

    function new_folder(
        --
        -- Create a folder
        --
        name in cr_items.name%TYPE,
        folder_name in cr_folders.label%TYPE,
        parent_id in cr_items.parent_id%TYPE,
        creation_user in acs_objects.creation_user%TYPE,
        creation_ip in acs_objects.creation_ip%TYPE
    ) return cr_folders.folder_id%TYPE
    is
        v_folder_id             cr_folders.folder_id%TYPE;
    begin
        -- Create a new folder
        v_folder_id := content_folder.new(
            name => file_storage.new_folder.name,
            label => file_storage.new_folder.folder_name,
            parent_id => file_storage.new_folder.parent_id,
            creation_user => file_storage.new_folder.creation_user,
            creation_ip => file_storage.new_folder.creation_ip
        );

        -- register the standard content types
        content_folder.register_content_type(
            v_folder_id,                -- folder_id
            'content_revision',        -- content_type
            't'                        -- include_subtypes
        );

        content_folder.register_content_type(
            v_folder_id,                -- folder_id
            'content_folder',        -- content_type
            't'                        -- include_subtypes
        );

        content_folder.register_content_type(
            v_folder_id,                -- folder_id
            'content_extlink',        -- content_type
            't'                        -- include_subtypes
        );

        -- Give the creator admin privileges on the folder
        acs_permission.grant_permission(
            v_folder_id,                             -- object_id
            file_storage.new_folder.creation_user,  -- grantee_id
            'admin'                                     -- privilege
        );

        return v_folder_id;
    end new_folder;

    procedure delete_folder(
        --
        -- Delete a folder
        --
        folder_id in cr_folders.folder_id%TYPE
    )
    is
    begin
        content_folder.delete(
            folder_id => file_storage.delete_folder.folder_id
        );
    end delete_folder;

end file_storage;
/
show errors;

-- JS: BEFORE DELETE TRIGGER to clean up CR
create or replace trigger fs_package_items_delete_trig
before delete on fs_root_folders
for each row
declare
    cursor v_cursor is
        select item_id,content_type
        from cr_items
        where item_id != :old.folder_id
        connect by parent_id = prior item_id
        start with item_id = :old.folder_id
        order by level desc;
begin
    for v_rec in v_cursor
    loop
        -- We delete the item. On delete cascade should take care
        -- of deletion of revisions.
        if v_rec.content_type = 'file_storage_object'
        then
            content_item.delete(v_rec.item_id);
        end if;

        -- Instead of doing an if-else, we make sure we are deleting a folder.
        if v_rec.content_type = 'content_folder'
        then
            content_folder.delete(v_rec.item_id);
        end if;

        -- Instead of doing an if-else, we make sure we are deleting a folder.
        if v_rec.content_type = 'content_symlink'
        then
            content_symlink.delete(v_rec.item_id);
        end if;

        -- Instead of doing an if-else, we make sure we are deleting a folder.
        if v_rec.content_type = 'content_extlink'
        then
            content_extlink.delete(v_rec.item_id);
        end if;

    end loop;
end;
/
show errors;

-- JS: AFTER DELETE TRIGGER to clean up last entry in CR
create or replace trigger fs_root_folder_delete_trig
after delete on fs_root_folders
for each row
begin
    content_folder.delete(:old.folder_id);
end;
/
show errors;