Index: openacs-4/packages/file-storage/sql/oracle/file-storage-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/oracle/file-storage-create.sql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/file-storage/sql/oracle/file-storage-create.sql 4 Sep 2001 01:25:11 -0000 1.4 +++ openacs-4/packages/file-storage/sql/oracle/file-storage-create.sql 22 Sep 2001 05:37:50 -0000 1.5 @@ -6,6 +6,23 @@ -- @cvs-id $Id$ -- +-- 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: + + -- -- To enable site-wide search to distinguish CR items as File Storage items -- we create an item subtype of content_item in the ACS Object Model @@ -48,25 +65,120 @@ create or replace package file_storage as - -- - -- Returns the root folder corresponding to a particulat - -- package instance. - -- function get_root_folder ( - package_id in apm_packages.package_id%TYPE + -- + -- Returns the root folder corresponding to a particulat + -- package instance. + -- + package_id in apm_packages.package_id%TYPE ) return fs_root_folders.folder_id%TYPE; - -- - -- Creates a new root folder - -- function new_root_folder ( - package_id in apm_packages.package_id%TYPE + -- + -- Creates a new root folder + -- + package_id in apm_packages.package_id%TYPE ) 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 + -- + 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; + + function get_path ( + -- + -- Get the virtual path, but replace title with name at the end + -- Wrapper for content_item.get_path + -- + item_id in cr_items.item_id%TYPE, + root_folder_id in cr_items.parent_id%TYPE, + revision_id in cr_revisions.revision_id%TYPE + ) return varchar; + + + 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_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; + end file_storage; / show errors + + create or replace package body file_storage as @@ -96,12 +208,11 @@ end get_root_folder; - -- - -- A hackish function to get around the fact that we can't run - -- code automatically when a new package instance is created. - -- - 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 ) return fs_root_folders.folder_id%TYPE is @@ -146,11 +257,346 @@ end new_root_folder; + + function new_file ( + -- + -- Create a file in CR in preparation for actual storage + -- Wrapper for content_item.new + -- + 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 ( + 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, + item_subtype => 'file_storage_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, + item_subtype => 'file_storage_item', + storage_type => 'file' + ); + + end if; + + 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, -- item_id + name => file_storage.rename_file.title -- name + ); + + 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; + + return v_new_version_id; + + end copy_file; + + + 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; + 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); + + return v_revision_id; + + end new_version; + + + function get_path ( + -- + -- Get the virtual path, but replace title with name at the end + -- Wrapper for content_item__get_path + -- + item_id in cr_items.item_id%TYPE, + root_folder_id in cr_items.parent_id%TYPE, + revision_id in cr_revisions.revision_id%TYPE default null + ) return varchar + is + v_filename cr_revisions.title%TYPE; + v_content_type cr_items.content_type%TYPE; + v_live_revision cr_items.live_revision%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + begin + + select content_type,live_revision + into v_content_type,v_live_revision + from cr_items + where item_id = file_storage.get_path.item_id; + + if v_content_type = 'content_revision' + then + + if file_storage.get_path.revision_id is null + then + v_revision_id := v_live_revision; + else + v_revision_id := file_storage.get_path.revision_id; + end if; + + select title into v_filename + from cr_revisions + where revision_id = v_revision_id; + + return content_item.get_path( + item_id => file_storage.get_path.item_id, + root_folder_id => file_storage.get_path.root_folder_id + ) || '/../' || v_filename; + + else + + return content_item.get_path( + item_id => file_storage.get_path.item_id, + root_folder_id => file_storage.get_path.root_folder_id + ); + + end if; + + end get_path; + + + 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_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; + end file_storage; / -show errors; +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 Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql 4 Sep 2001 01:24:49 -0000 1.5 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql 22 Sep 2001 05:38:46 -0000 1.6 @@ -6,6 +6,21 @@ -- @cvs-id $Id$ -- +-- 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: -- To enable site-wide search to distinguish CR items as File Storage items -- we create an item subtype of content_item in the ACS Object Model @@ -45,18 +60,6 @@ --- file_storage API --- --- function get_root_folder ( --- package_id in apm_packages.package_id%TYPE --- ) return fs_root_folders.folder_id%TYPE; --- --- --- function new_root_folder ( --- package_id in apm_packages.package_id%TYPE --- ) return fs_root_folders.folder_id%TYPE; - - create function file_storage__get_root_folder ( -- -- Returns the root folder corresponding to a particular @@ -155,18 +158,442 @@ 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 + ''file_storage_item'', -- item_subtype (needed by site-wide search) + ''content_revision'', -- content_type (default) + 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 + ''file_storage_item'', -- item_subtype (needed by site-wide search) + ''content_revision'', -- content_type (default) + 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__get_path ( + -- + -- Get the virtual path, but replace title with name at the end + -- Wrapper for content_item__get_path + -- + integer, -- cr_items.item_id%TYPE + integer, -- cr_items.parent_id%TYPE + integer -- cr_revisions.revision_id%TYPE +) returns varchar as ' +declare + get_path__item_id alias for $1; + get_path__root_folder_id alias for $2; + get_path__revision_id alias for $3; + v_filename 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_path__item_id; + + if v_content_type = ''content_revision'' + then + select title into v_filename + from cr_revisions + where revision_id = get_path__revision_id; + + return content_item__get_path( + get_path__item_id, + get_path__root_folder_id + ) || ''/../'' || v_filename; + + else + + return content_item__get_path( + get_path__item_id, + get_path__root_folder_id + ); + + end if; + +end;' language 'plpgsql'; + +create function file_storage__get_path ( + -- + -- Get path, using the live revision for revision_id + -- + integer, -- cr_items.item_id%TYPE + integer -- cr_items.parent_id%TYPE +) returns varchar as ' +declare + get_path__item_id alias for $1; + get_path__root_folder_id alias for $2; + v_live_revision cr_items.live_revision%TYPE; +begin + + select live_revision into v_live_revision + from cr_items + where item_id = get_path__item_id; + + return file_storage__get_path( + get_path__item_id, + get_path__root_folder_id, + v_live_revision + ); + +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'; + + + -- 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). + -- We want to delete all cr_items entries, starting from the leaves all + -- the way up the root folder (old.folder_id). select item_id,content_type from cr_items where tree_sortkey like (select tree_sortkey || ''%''