--
-- file-storage/sql/postgresql/file-storage-package-create.sql
--
-- @author Kevin Scaldeferri (kevin@arsdigita.com)
-- @creation-date 6 Nov 2000
-- @cvs-id $Id: file-storage-package-create.sql,v 1.30.2.1 2019/08/09 20:25:08 gustafn Exp $
--
-- added
select define_function_args('file_storage__get_root_folder','package_id');
--
-- procedure file_storage__get_root_folder/1
--
--
-- Returns the root folder corresponding to a particular
-- package instance.
--
CREATE OR REPLACE FUNCTION file_storage__get_root_folder(
get_root_folder__package_id integer
)
RETURNS integer AS $$
DECLARE
v_folder_id fs_root_folders.folder_id%TYPE;
BEGIN
select folder_id into v_folder_id
from fs_root_folders
where package_id = get_root_folder__package_id;
return v_folder_id;
END;
$$ LANGUAGE plpgsql stable;
-- added
select define_function_args('file_storage__get_package_id','item_id');
--
-- procedure file_storage__get_package_id/1
--
CREATE OR REPLACE FUNCTION file_storage__get_package_id(
get_package_id__item_id integer
)
RETURNS integer AS $$
DECLARE
v_package_id fs_root_folders.package_id%TYPE;
v_tree_sortkey cr_items.tree_sortkey%TYPE;
BEGIN
select fs_root_folders.package_id
into v_package_id
from fs_root_folders,
(select cr_items.item_id
from (select tree_ancestor_keys(cr_items_get_tree_sortkey(get_package_id__item_id)) as tree_sortkey) parents,
cr_items
where cr_items.tree_sortkey = parents.tree_sortkey) 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 stable;
-- added
select define_function_args('file_storage__new_root_folder','package_id,folder_name,url,description');
--
-- procedure file_storage__new_root_folder/4
--
--
-- 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.
--
CREATE OR REPLACE FUNCTION file_storage__new_root_folder(
new_root_folder__package_id integer,
new_root_folder__folder_name varchar,
new_root_folder__url varchar,
new_root_folder__description varchar
)
RETURNS integer AS $$
DECLARE
v_folder_id fs_root_folders.folder_id%TYPE;
BEGIN
v_folder_id := content_folder__new (
new_root_folder__url, -- name
new_root_folder__folder_name, -- label
new_root_folder__description, -- description
null, -- parent_id (default)
new_root_folder__package_id, --context_id
null, --folder_id
null, --creation_date
null, --creation_user
null, --creation_ip
new_root_folder__package_id --package_id
);
insert into fs_root_folders (package_id, folder_id)
values (new_root_folder__package_id, v_folder_id);
--
-- Register the needed content types
--
-- GN: Maybe, when someone decides to really implement the half-cooked
-- "image" content type, it should go in here as well.
PERFORM content_folder__register_content_type(
v_folder_id, -- folder_id
'file_storage_object', -- content_types
'f'); -- include_subtypes
PERFORM content_folder__register_content_type(
v_folder_id, -- folder_id
'content_folder', -- content_types
't'); -- include_subtypes
PERFORM content_folder__register_content_type(
v_folder_id, -- folder_id
'content_extlink', -- content_types
't'); -- include_subtypes
-- PERFORM content_folder__register_content_type(
-- v_folder_id, -- folder_id
-- 'content_symlink', -- content_types
-- 't'); -- include_subtypes
-- );
return v_folder_id;
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('file_storage__new_file','name,folder_id,user_id,creation_ip,indb_p,item_id,package_id');
--
-- procedure file_storage__new_file/7
--
--
-- Create a file in CR in preparation for actual storage
-- Wrapper for content_item__new
--
-- DRB: I added this version to allow one to predefine item_id, among other things to
-- make it easier to use with ad_form
CREATE OR REPLACE FUNCTION file_storage__new_file(
new_file__name varchar,
new_file__folder_id integer,
new_file__user_id integer,
new_file__creation_ip varchar,
new_file__indb_p boolean,
new_file__item_id integer,
new_file__package_id integer
)
RETURNS integer AS $$
DECLARE
v_item_id integer;
BEGIN
if new_file__indb_p
then
v_item_id := content_item__new (
new_file__name, -- name
new_file__folder_id, -- parent_id
new_file__item_id, -- 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)
new_file__package_id -- package_id
);
else
v_item_id := content_item__new (
new_file__name, -- name
new_file__folder_id, -- parent_id
new_file__item_id, -- 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
new_file__package_id -- package_id
);
end if;
perform acs_object__update_last_modified(new_file__folder_id,new_file__user_id,new_file__creation_ip);
return v_item_id;
END;
$$ LANGUAGE plpgsql;
--
-- procedure file_storage__new_file/6
--
CREATE OR REPLACE FUNCTION file_storage__new_file(
new_file__name varchar,
new_file__folder_id integer,
new_file__user_id integer,
new_file__creation_ip varchar,
new_file__indb_p boolean,
new_file__package_id integer
)
RETURNS integer AS $$
DECLARE
BEGIN
return file_storage__new_file(
new_file__name, -- name
new_file__folder_id, -- parent_id
new_file__user_id, -- creation_user
new_file__creation_ip, -- creation_ip
new_file__indb_p, -- storage_type
null, -- item_id
new_file__package_id -- package_id
);
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('file_storage__delete_file','file_id');
--
-- procedure file_storage__delete_file/1
--
--
-- Delete a file and all its version
-- Wrapper to content_item__delete
--
CREATE OR REPLACE FUNCTION file_storage__delete_file(
delete_file__file_id integer
)
RETURNS integer AS $$
DECLARE
BEGIN
return content_item__delete(delete_file__file_id);
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('file_storage__rename_file','file_id,name');
--
-- procedure file_storage__rename_file/2
--
--
-- Rename a file and all
-- Wrapper to content_item__edit_name
--
CREATE OR REPLACE FUNCTION file_storage__rename_file(
rename_file__file_id integer,
rename_file__name varchar
)
RETURNS integer AS $$
DECLARE
BEGIN
return content_item__edit_name(
rename_file__file_id, -- item_id
rename_file__name -- name
);
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('file_storage__copy_file','file_id,target_folder_id,creation_user,creation_ip,name;null,title;null');
--
-- procedure file_storage__copy_file/6
--
--
-- Copy a file, but only copy the live_revision
--
CREATE OR REPLACE FUNCTION file_storage__copy_file(
copy_file__file_id integer,
copy_file__target_folder_id integer,
copy_file__creation_user integer,
copy_file__creation_ip varchar,
copy_file__name cr_items.name%TYPE default null,
copy_file__title cr_revisions.title%TYPE default null
)
RETURNS integer AS $$
DECLARE
v_name 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;
v_isurl boolean;
v_content_type cr_items.content_type%TYPE;
v_package_id apm_packages.package_id%TYPE;
BEGIN
v_isurl:= false;
select content_type into v_content_type from cr_items where item_id = copy_file__file_id;
if v_content_type = 'content_extlink'
then
v_isurl:= true;
end if;
-- We copy only the title from the file being copied, and attributes of the live revision
if v_isurl = false
then
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_name,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;
select package_id into v_package_id from acs_objects where object_id = copy_file__file_id;
v_name := coalesce(copy_file__name, v_name);
v_filename := coalesce(copy_file__title, v_filename);
v_new_file_id := file_storage__new_file(
v_name, -- name
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_package_id -- package_id
);
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;
perform acs_object__update_last_modified(copy_file__target_folder_id,copy_file__creation_user,copy_file__creation_ip);
return v_new_version_id;
else
perform content_extlink__copy (copy_file__file_id, copy_file__target_folder_id, copy_file__creation_user,copy_file__creation_ip,v_name);
return 0;
end if;
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('file_storage__move_file','file_id,target_folder_id,creation_user,creation_ip');
--
-- procedure file_storage__move_file/4
--
--
-- Move a file (and all its versions) to a different folder.
-- Wrapper for content_item__move
--
CREATE OR REPLACE FUNCTION file_storage__move_file(
move_file__file_id integer,
move_file__target_folder_id integer,
move_file__creation_user integer,
move_file__creation_ip varchar
)
RETURNS integer AS $$
DECLARE
BEGIN
perform content_item__move(
move_file__file_id, -- item_id
move_file__target_folder_id -- target_folder_id
);
perform acs_object__update_last_modified(move_file__target_folder_id,move_file__creation_user,move_file__creation_ip);
return 0;
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('file_storage__get_title','item_id');
--
-- procedure file_storage__get_title/1
--
CREATE OR REPLACE FUNCTION file_storage__get_title(
get_title__item_id integer
)
RETURNS varchar AS $$
DECLARE
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;
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 title into v_title
from cr_revisions, cr_items
where revision_id=live_revision
and cr_items.item_id=get_title__item_id;
end if;
end if;
return v_title;
END;
$$ LANGUAGE plpgsql;
select define_function_args('file_storage__get_parent_id','item_id');
CREATE OR REPLACE FUNCTION file_storage__get_parent_id (
get_parent_id__item_id integer
)
RETURNS integer AS $$
declare
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;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('file_storage__get_content_type','file_id');
--
-- procedure file_storage__get_content_type/1
--
--
-- Wrapper for content_item__get_content_type
--
CREATE OR REPLACE FUNCTION file_storage__get_content_type(
get_content_type__file_id integer
)
RETURNS varchar AS $$
DECLARE
BEGIN
return content_item__get_content_type(
get_content_type__file_id
);
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('file_storage__get_folder_name','folder_id');
--
-- procedure file_storage__get_folder_name/1
--
CREATE OR REPLACE FUNCTION file_storage__get_folder_name(
get_folder_name__folder_id integer
)
RETURNS varchar AS $$
DECLARE
BEGIN
return content_folder__get_label(get_folder_name__folder_id);
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('file_storage__new_version','filename,description,mime_type,item_id,creation_user,creation_ip');
--
-- procedure file_storage__new_version/6
--
--
-- Create a new version of a file
-- Wrapper for content_revision__new
--
CREATE OR REPLACE FUNCTION file_storage__new_version(
new_version__filename varchar,
new_version__description varchar,
new_version__mime_type varchar,
new_version__item_id integer,
new_version__creation_user integer,
new_version__creation_ip varchar
)
RETURNS integer AS $$
DECLARE
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 (
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
null, -- content_length
null -- package_id
);
-- Make live the newly created revision
perform content_item__set_live_revision(v_revision_id);
select cr_items.parent_id
into v_folder_id
from cr_items
where cr_items.item_id = new_version__item_id;
perform acs_object__update_last_modified(v_folder_id,new_version__creation_user,new_version__creation_ip);
perform acs_object__update_last_modified(new_version__item_id,new_version__creation_user,new_version__creation_ip);
return v_revision_id;
END;
$$ LANGUAGE plpgsql;
--
-- procedure file_storage__delete_version/2
--
select define_function_args('file_storage__delete_version','file_id,version_id');
--
-- Delete a version of a file
--
CREATE OR REPLACE FUNCTION file_storage__delete_version(
delete_version__file_id integer,
delete_version__version_id integer
)
RETURNS integer AS $$
DECLARE
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;
--
-- procedure file_storage__new_folder/5
--
select define_function_args('file_storage__new_folder','name,folder_name,parent_id,creation_user,creation_ip');
CREATE OR REPLACE FUNCTION file_storage__new_folder(
new_folder__name varchar,
new_folder__folder_name varchar,
new_folder__parent_id integer,
new_folder__creation_user integer,
new_folder__creation_ip varchar
)
RETURNS integer AS $$
DECLARE
v_folder_id cr_folders.folder_id%TYPE;
v_package_id acs_objects.package_id%TYPE;
BEGIN
v_package_id := file_storage__get_package_id(new_folder__parent_id);
-- 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
v_package_id -- package_id
);
--
-- Register the needed content types
--
-- maybe, when someone decides to really implement the half-cooked
-- "image" content type, it should go in here as well
PERFORM content_folder__register_content_type(
v_folder_id, -- folder_id
'file_storage_object', -- 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)
PERFORM content_folder__register_content_type(
v_folder_id, -- folder_id
'content_extlink', -- content_types
't'); -- include_subtypes
-- PERFORM content_folder__register_content_type(
-- v_folder_id, -- folder_id
-- 'content_symlink', -- content_types
-- 't'); -- include_subtypes
-- 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;
-- added
--
-- procedure file_storage__delete_folder/1
--
--
-- Delete a folder
--
CREATE OR REPLACE FUNCTION file_storage__delete_folder(
delete_folder__folder_id integer
)
RETURNS integer AS $$
DECLARE
BEGIN
return file_storage__delete_folder(
delete_folder__folder_id, -- folder_id
'f');
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('file_storage__delete_folder','folder_id,cascade_p');
--
-- procedure file_storage__delete_folder/2
--
--
-- Delete a folder
--
CREATE OR REPLACE FUNCTION file_storage__delete_folder(
delete_folder__folder_id integer,
delete_folder__cascade_p boolean
)
RETURNS integer AS $$
DECLARE
BEGIN
return content_folder__delete(
delete_folder__folder_id, -- folder_id
delete_folder__cascade_p
);
END;
$$ LANGUAGE plpgsql;
-- JS: BEFORE DELETE TRIGGER to clean up CR entries (except root folder)
--
-- procedure fs_package_items_delete_trig/0
--
CREATE OR REPLACE FUNCTION fs_package_items_delete_trig (
)
RETURNS trigger 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
-- DRB: Why can't we just use object delete here?
-- 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;
-- Instead of doing an if-else, we make sure we are deleting a folder.
if v_rec.content_type = 'content_symlink'
then
raise notice 'Deleting symlink_id = %',v_rec.item_id;
PERFORM 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
raise notice 'Deleting folder_id = %',v_rec.item_id;
PERFORM content_extlink__delete(v_rec.item_id);
end if;
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 OR REPLACE FUNCTION fs_root_folder_delete_trig () RETURNS trigger 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
OpenACS – The Toolkit for Online Communities : ACS API Browser : File Storage 5.10.0d1 : file-storage-package-create.sql
Begin main content
