--
-- packages/file-storage/sql/postgresql/file-storage-create.sql
--
-- @author Kevin Scaldeferri (kevin@arsdigita.com)
-- @creation-date 6 Nov 2000
-- @cvs-id $Id: file-storage-create.sql,v 1.12.2.2 2002/03/13 19:01:52 donb Exp $
--

-- JS: I changed the way file storage uses the CR:  cr_items will store
-- JS: a file's meta-data, while cr_revisions will store specifics of a
-- JS: file's version.  Every file has at least one version.
-- JS:
-- JS: 1) The name attribute in cr_items will store the "title" of the 
-- JS:     of the file, and all its versions. 
-- JS:
-- JS: 2) The title attribute in cr_revisions  will store the filename 
-- JS: of each version, which may be different among versions of the same title.
-- JS:
-- JS: 3)   Version notes will still be stored in the description attribute. 
-- JS:
-- JS: The unfortunate result is that the use of "title" and "name" in 
-- JS: cr_revisions and cr_items, respectively, are interchanged.
-- JS:

--
-- We need to create a root folder in the content repository for 
-- each instance of file storage
--
create table fs_root_folders (
    -- ID for this package instance
    package_id  integer
                constraint fs_root_folder_package_id_fk
                references apm_packages on delete cascade
                constraint fs_root_folder_package_id_pk
                primary key,
    -- the ID of the root folder
    -- JS: I removed the on delete cascade constraint on folder_id
    -- JS: It is superfluous, and causes a lot of RI headaches
    folder_id   integer
                constraint fs_root_folder_folder_id_fk
                references cr_folders
                constraint fs_root_folder_folder_id_un
                unique
);

-- Create a subtype of content_revision so that site-wide-search can
-- distinguish file-storage items (v.s. generic content repository
-- items) in the search results
select content_type__create_type (
       'file_storage_object',    -- content_type
       'content_revision',       -- supertype. We search revision content 
                                 -- first, before item metadata
       'File Storage Object',    -- pretty_name
       'File Storage Objects',   -- pretty_plural
       'fs_root_folders',        -- table_name
       'folder_id',	         -- id_column
       'file_storage__get_title' -- name_method
       );

create function file_storage__get_root_folder (
       --
       -- Returns the root folder corresponding to a particular
       -- package instance.
       --
       integer        -- apm_packages.package_id%TYPE
)
returns integer as '  -- fs_root_folders.folder_id%TYPE 
declare
	get_root_folder__package_id  alias for $1;
        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 := file_storage__new_root_folder(get_root_folder__package_id, null, null);
        end if;

        return v_folder_id;

end;' language 'plpgsql' with (iscachable);

create function file_storage__get_package_id (
    integer                     -- cr_items.item_id%TYPE
) returns integer as '          -- fs_root_folders.package_id%TYPE
declare
    get_package_id__item_id     alias for $1;
    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 c1.item_id
          from cr_items c1,
               cr_items c2
          where c2.item_id = get_package_id__item_id
          and c1.tree_sortkey between c2.tree_sortkey and tree_right(c2.tree_sortkey)
          and c1.item_id <> get_package_id__item_id
          order by c1.tree_sortkey desc) this
    where fs_root_folders.folder_id = this.item_id;

    if NOT FOUND then
        return null;
    else
        return v_package_id;
    end if;
end;' language 'plpgsql' with (iscachable);

create function file_storage__new_root_folder (
       --
       -- Creates a new root folder
       --
       -- 
       -- A hackish function to get around the fact that we can not run
       -- code automatically when a new package instance is created.
       --
       integer,         -- apm_packages.package_id%TYPE
       varchar,         -- cr_folders.label%TYPE
       varchar          -- cr_folders.description%TYPE
)
returns integer as '	--  fs_root_folders.folder_id%TYPE
declare
	new_root_folder__package_id         alias for $1;
        new_root_folder__folder_name        alias for $2;
        new_root_folder__description        alias for $3;
        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 := new_root_folder__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 := new_root_folder__description;
        end if;

        v_folder_id := content_folder__new (
            v_package_key || ''_'' || new_root_folder__package_id, -- name
            v_folder_name, -- label
            v_description, -- description
	    null				  -- parent_id (default)
        );

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

        -- allow child items to be added
	-- JS: Note that we need to set include_subtypes to 
	-- JS: true since we created a new subtype.
        PERFORM content_folder__register_content_type(
		v_folder_id,	        -- folder_id
		''content_revision'',   -- content_types
		''t''			-- include_subtypes 
		);
        PERFORM content_folder__register_content_type(
		v_folder_id,		-- folder_id
		''content_folder'',	-- content_types
		''t''			-- include_subtypes 
		);

        -- set up default permissions
        PERFORM acs_permission__grant_permission (
		v_folder_id,                          -- object_id
		acs__magic_object_id(''the_public''), -- grantee_id 
		''read''			      -- privilege
		);

        PERFORM acs_permission__grant_permission (
		v_folder_id,			            -- object_id 
		acs__magic_object_id(''registered_users''), -- grantee_id
		''write''				    -- privilege
		);

	return v_folder_id;

end;' language 'plpgsql';
    

create function file_storage__new_file(
       -- 
       -- Create a file in CR in preparation for actual storage
       -- Wrapper for content_item__new
       --
       varchar,		-- cr_items.name%TYPE,
       integer,		-- cr_items.parent_id%TYPE,
       integer,		-- acs_objects.creation_user%TYPE,
       varchar,		-- acs_objects.creation_ip%TYPE,
       boolean		-- store in db? 
) returns integer as ' -- cr_items.item_id%TYPE
declare
	new_file__title			alias for $1;
	new_file__folder_id		alias for $2;
	new_file__user_id		alias for $3;
	new_file__creation_ip		alias for $4;
	new_file__indb_p		alias for $5;
begin

	if new_file__indb_p
	then 
	    return content_item__new (
			  new_file__title,	    -- name
			  new_file__folder_id,      -- parent_id
			  null,			    -- item_id (default)
			  null,			    -- locale (default)
			  now(),		    -- creation_date (default)
			  new_file__user_id,        -- creation_user
			  new_file__folder_id,      -- context_id
			  new_file__creation_ip,    -- creation_ip
			  ''content_item'',         -- item_subtype (default)
			  ''file_storage_object'',  -- content_type (needed by site-wide search)
			  null,			    -- title (default)
			  null,			    -- description
			  ''text/plain'',	    -- mime_type (default)
			  null,			    -- nls_language (default)
			  null			    -- data (default)
			  );
	else
	    return content_item__new (
			  new_file__title,	    -- name
			  new_file__folder_id,	    -- parent_id
			  null,			    -- item_id (default)
			  null,			    -- locale (default)
			  now(),		    -- creation_date (default)
			  new_file__user_id,	    -- creation_user
			  new_file__folder_id,	    -- context_id
			  new_file__creation_ip,    -- creation_ip
			  ''content_item'',         -- item_subtype (default)
			  ''file_storage_object'',  -- content_type (needed by site-wide search)
			  null,			    -- title (default)
			  null,			    -- description
			  ''text/plain'',	    -- mime_type (default)
			  null,			    -- nls_language (default)
			  null,			    -- text (default)
			  ''file''		    -- storage_type
			  );

	end if;

end;' language 'plpgsql';


create function file_storage__delete_file (
       --
       -- Delete a file and all its version
       -- Wrapper to content_item__delete
       --
       integer		-- cr_items.item_id%TYPE
) returns integer as '
declare
	delete_file__file_id	alias for $1;
begin

	return content_item__delete(delete_file__file_id);

end;' language 'plpgsql';


create function file_storage__rename_file (
       --
       -- Rename a file and all
       -- Wrapper to content_item__rename
       --
       integer,		-- cr_items.item_id%TYPE,
       varchar		-- cr_items.name%TYPE
) returns integer as '
declare
	rename_file__file_id	alias for $1;
	rename_file__title	alias for $2;

begin

	return content_item__rename(
	       rename_file__file_id,  -- item_id
	       rename_file__title     -- name
	       );

end;' language 'plpgsql';


create function file_storage__copy_file(
       --
       -- Copy a file, but only copy the live_revision
       --
       integer,		-- cr_items.item_id%TYPE,
       integer,		-- cr_items.parent_id%TYPE,
       integer,		-- acs_objects.creation_user%TYPE,
       varchar		-- acs_objects.creation_ip%TYPE
) returns integer as '	-- cr_revisions.revision_id%TYPE 
declare
	copy_file__file_id	     alias for $1;
	copy_file__target_folder_id  alias for $2;
	copy_file__creation_user     alias for $3;
	copy_file__creation_ip	     alias for $4;
	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_id		     cr_revisions.lob%TYPE;
	v_new_lob_id		     cr_revisions.lob%TYPE;
	v_file_path		     cr_revisions.content%TYPE;
	v_new_file_id		     cr_items.item_id%TYPE;
	v_new_version_id		     cr_revisions.revision_id%TYPE;
	v_indb_p		     boolean;
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_length,
	       (case when i.storage_type = ''lob''
	             then true
		     else false
		end)
	       into v_title,v_live_revision,v_filename,v_description,v_mime_type,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 = 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(
			     v_title,		          -- title
			     copy_file__target_folder_id, -- folder_id
			     copy_file__creation_user,	  -- creation_user
			     copy_file__creation_ip,	  -- creation_ip
			     v_indb_p			  -- indb_p
			     );

	v_new_version_id := file_storage__new_version (
			     v_filename,		  -- title
			     v_description,		  -- description
			     v_mime_type,		  -- mime_type
			     v_new_file_id,		  -- item_id
			     copy_file__creation_user,	  -- creation_user
			     copy_file__creation_ip	  -- creation_ip
			     );
			     
	if v_indb_p
	then

		-- Lob to copy from
		select lob into v_lob_id
		from cr_revisions
		where revision_id = v_live_revision;

		-- New lob id
		v_new_lob_id := empty_lob();

		-- copy the blob
		PERFORM lob_copy(v_lob_id,v_new_lob_id);

		-- Update the lob id on the new version
		update cr_revisions
		set lob = v_new_lob_id,
		    content_length = v_content_length
		where revision_id = v_new_version_id;

	else

		-- For now, we simply copy the file name
		select content into v_file_path
		from cr_revisions
		where revision_id = v_live_revision;

		-- Update the file path
		update cr_revisions
		set content = v_file_path,
		    content_length = v_content_length
		where revision_id = v_new_version_id;

	end if;

	return v_new_version_id;

end;' language 'plpgsql';


create function file_storage__move_file (
       --
       -- Move a file (ans all its versions) to a different folder.
       -- Wrapper for content_item__move
       -- 
       integer,		-- cr_folders.folder_id%TYPE,
       integer		-- cr_folders.folder_id%TYPE
) returns integer as '	-- 0 for success 
declare
	move_file__file_id		alias for $1;
	move_file__target_folder_id	alias for $2;
begin

	return content_item__move(
	       move_file__file_id,		-- item_id
	       move_file__target_folder_id	-- target_folder_id
	       );

end;' language 'plpgsql';


create function file_storage__get_title (
       --
       -- Unfortunately, title in the file-storage context refers
       -- to the name attribute in cr_items, not the title attribute in 
       -- cr_revisions
       integer		-- cr_items.item_id%TYPE
) returns varchar as ' 
declare
  get_title__item_id                 alias for $1;  
  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 f
	 rom 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;' language 'plpgsql';


create function file_storage__get_content_type (
       --
       -- Wrapper for content_item__get_content_type
       integer		-- cr_items.item_id%TYPE
) returns varchar as '  -- cr_items.content_type%TYPE
declare
	get_content_type__file_id	alias for $1;
begin
	return content_item__get_content_type(
		          get_content_type__file_id
			  );

end;' language 'plpgsql';  



create function file_storage__get_folder_name (
       --
       -- Wrapper for content_folder__get_label
       integer		-- cr_folders.folder_id%TYPE
) returns varchar as '  -- cr_folders.label%TYPE 
declare
	get_folder_name__folder_id	alias for $1;
begin
	return content_folder__get_label(
		          get_folder_name__folder_id
			  );

end;' language 'plpgsql';  


create function file_storage__new_version (
       --
       -- Create a new version of a file
       -- Wrapper for content_revision__new
       --
       varchar,		-- cr_revisions.title%TYPE,
       varchar,		-- cr_revisions.description%TYPE,
       varchar,		-- cr_revisions.mime_type%TYPE,
       integer,		-- cr_items.item_id%TYPE,
       integer,		-- acs_objects.creation_user%TYPE,
       varchar		-- acs_objects.creation_ip%TYPE
) returns integer as '	-- cr_revisions.revision_id 
declare
	new_version__filename		alias for $1;
	new_version__description	alias for $2;
	new_version__mime_type		alias for $3;
	new_version__item_id		alias for $4;
	new_version__creation_user	alias for $5;
	new_version__creation_ip	alias for $6;
	v_revision_id			cr_revisions.revision_id%TYPE;
begin
	-- Create a revision
    	v_revision_id := content_revision__new (
			  new_version__filename,	-- title
			  new_version__description,	-- description
			  now(),			-- publish_date
			  new_version__mime_type,	-- mime_type
			  null,				-- nls_language
			  null,				-- data (default)
			  new_version__item_id,		-- item_id
			  null,				-- revision_id
			  now(),			-- creation_date
			  new_version__creation_user,	-- creation_user
			  new_version__creation_ip	-- creation_ip
			  );

	-- Make live the newly created revision
    	PERFORM content_item__set_live_revision(v_revision_id);

	return v_revision_id;

end;' language 'plpgsql';


create function file_storage__delete_version (
       --
       -- Delete a version of a file
       --
       integer,	-- cr_items.item_id%TYPE,
       integer	-- cr_revisions.revision_id%TYPE
) returns integer as ' -- cr_items.parent_id%TYPE
declare
	delete_version__file_id		alias for $1;
	delete_version__version_id	alias for $2;
	v_parent_id			cr_items.parent_id%TYPE;
	v_deleted_last_version_p	boolean;
begin

        if delete_version__version_id = content_item__get_live_revision(delete_version__file_id) 
	then
            PERFORM content_revision__delete(delete_version__version_id);
            PERFORM content_item__set_live_revision(
			content_item__get_latest_revision(delete_version__file_id)
			);
        else
            PERFORM content_revision__delete(delete_version__version_id);
        end if;

	-- If the live revision is null, we have deleted the last version above
        select (case when live_revision is null
                     then parent_id
                     else 0
                end)
	  into v_parent_id 
        from cr_items
        where item_id = 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;' language 'plpgsql';


create function file_storage__new_folder(
       --
       -- Create a folder
       --
       varchar,		-- cr_items.name%TYPE,
       varchar,		-- cr_folders.label%TYPE,
       integer,		-- cr_items.parent_id%TYPE,
       integer,		-- acs_objects.creation_user%TYPE,       
       varchar		-- acs_objects.creation_ip%TYPE       
) returns integer as '	-- cr_folders.folder_id%TYPE
declare
	new_folder__name	      alias for $1;
	new_folder__folder_name	      alias for $2;
	new_folder__parent_id	      alias for $3;
	new_folder__creation_user     alias for $4;
	new_folder__creation_ip	      alias for $5;
	v_folder_id		      cr_folders.folder_id%TYPE;
begin

	-- Create a new folder
	v_folder_id := content_folder__new (
			    new_folder__name,		-- name
			    new_folder__folder_name,	-- label
			    null,			-- description
			    new_folder__parent_id,	-- parent_id
			    null,			-- context_id (default)
			    null,			-- folder_id (default)
			    now(),			-- creation_date
			    new_folder__creation_user,	-- creation_user
			    new_folder__creation_ip	-- creation_ip
			    );

	-- register the standard content types
	-- JS: Note that we need to set include_subtypes 
	-- JS: to true since we created a new subtype.
	PERFORM content_folder__register_content_type(
			v_folder_id,		-- folder_id
			''content_revision'',	-- content_type
			''t'');			-- include_subtypes (default)

	PERFORM content_folder__register_content_type(
			v_folder_id,		-- folder_id
			''content_folder'',	-- content_type
			''t''			-- include_subtypes (default)
			);			

	-- Give the creator admin privileges on the folder
	PERFORM acs_permission__grant_permission (
		     v_folder_id,	         -- object_id
		     new_folder__creation_user,  -- grantee_id
		     ''admin''			 -- privilege
		     );

	return v_folder_id;

end;' language 'plpgsql';


create function file_storage__delete_folder(
       --
       -- Delete a folder
       --
       integer		-- cr_folders.folder_id%TYPE
) returns integer as '	-- 0 for success
declare
	delete_folder__folder_id	alias for $1; 
begin

	return content_folder__delete(
		    delete_folder__folder_id  -- folder_id
		    );

end;' language 'plpgsql';


-- JS: BEFORE DELETE TRIGGER to clean up CR entries (except root folder)
create function fs_package_items_delete_trig () returns opaque as '
declare

	v_rec	record;
begin

	for v_rec in
	
		-- We want to delete all cr_items entries, starting from the leaves all
		--  the way up the root folder (old.folder_id).
		select c1.item_id, c1.content_type
		from cr_items c1, cr_items c2
                where c2.item_id = old.folder_id
		  and c1.tree_sortkey between c2.tree_sortkey and tree_right(c2.tree_sortkey)
		  and c1.item_id <> old.folder_id
                order by c1.tree_sortkey desc
	loop


		-- We delete the item. On delete cascade should take care
		-- of deletion of revisions.
		if v_rec.content_type = ''file_storage_object''
		then
		    raise notice ''Deleting item_id = %'',v_rec.item_id;
		    PERFORM 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
		    raise notice ''Deleting folder_id = %'',v_rec.item_id;
		    PERFORM content_folder__delete(v_rec.item_id);
		end if;

		-- We may have to delete other items here, e.g., symlinks (future feature)

	end loop;

	-- We need to return something for the trigger to be activated
	return old;

end;' language 'plpgsql';

create trigger fs_package_items_delete_trig before delete
on fs_root_folders for each row 
execute procedure fs_package_items_delete_trig ();


-- JS: AFTER DELETE TRIGGER to clean up last CR entry
create function fs_root_folder_delete_trig () returns opaque as '
begin
	PERFORM content_folder__delete(old.folder_id);
	return null;

end;' language 'plpgsql';

create trigger fs_root_folder_delete_trig after delete
on fs_root_folders for each row 
execute procedure fs_root_folder_delete_trig ();


-- Comment out to disable site-wide search  interface
\i file-storage-sc-create.sql

create view fs_folders
as
    select cr_folders.folder_id,
           cr_folders.label as name,
           (select count(*)
            from cr_items ci
            where ci.parent_id = cr_folders.folder_id) as content_size,
           (select site_node__url(site_nodes.node_id)
            from site_nodes
            where site_nodes.object_id = file_storage__get_package_id(cr_items.item_id)) as url,
           cr_items.parent_id
    from cr_folders,
         cr_items
    where cr_folders.folder_id = cr_items.item_id;

create view fs_files
as
    select cr_revisions.item_id as file_id,
           cr_revisions.revision_id as live_revision,
           cr_revisions.mime_type as type,
           cr_revisions.content_length as content_size,
           cr_items.name,
           acs_objects.last_modified,
           (select site_node__url(site_nodes.node_id)
            from site_nodes
            where site_nodes.object_id = file_storage__get_package_id(cr_items.item_id)) as url,
           cr_items.parent_id
    from cr_revisions,
         cr_items,
         acs_objects
    where cr_revisions.revision_id = cr_items.live_revision
    and cr_revisions.item_id = cr_items.item_id
    and cr_items.content_type = 'file_storage_object'
    and cr_revisions.revision_id = acs_objects.object_id;

create view fs_folders_and_files
as
    select fs_folders.folder_id as file_id,
           0 as live_revision,
           'Folder' as type,
           fs_folders.content_size,
           fs_folders.name,
           to_date(null, 'YYYY-MM-DD HH24:MI') as last_modified,
           fs_folders.url,
           fs_folders.parent_id,
           0 as sort_key
    from fs_folders
    union
    select fs_files.file_id,
           fs_files.live_revision,
           fs_files.type,
           fs_files.content_size,
           fs_files.name,
           fs_files.last_modified,
           fs_files.url,
           fs_files.parent_id,
           1 as sort_key
    from fs_files;