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.12 -r1.13 --- openacs-4/packages/file-storage/sql/oracle/file-storage-create.sql 2 Apr 2002 07:00:53 -0000 1.12 +++ openacs-4/packages/file-storage/sql/oracle/file-storage-create.sql 4 Apr 2002 03:01:06 -0000 1.13 @@ -62,706 +62,9 @@ / show errors; -create or replace package file_storage -as +@ file-storage-package-create.sql - 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; +@ file-storage-simple-create.sql +@ file-storage-simple-package-create.sql - 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 - -- - 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_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'); - - -- 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 - -- - 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, - 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; - - 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; - - 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 - ); - 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; - 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_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; - - 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 - ); - - -- 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; - - -- We may have to delete other items here, e.g., symlinks(future feature) - 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; - -create or replace view fs_folders -as - select cr_folders.folder_id, - cr_folders.label as name, - acs_objects.last_modified, - (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, - acs_objects - where cr_folders.folder_id = cr_items.item_id - and cr_folders.folder_id = acs_objects.object_id; - -create or replace 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 or replace 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, - fs_folders.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; - - -@file-storage-simple-create.sql -@file-storage-simple-package-create.sql +@ file-storage-views-create.sql Index: openacs-4/packages/file-storage/sql/oracle/file-storage-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/oracle/file-storage-drop.sql,v diff -u -r1.7 -r1.8 --- openacs-4/packages/file-storage/sql/oracle/file-storage-drop.sql 13 Mar 2002 22:50:53 -0000 1.7 +++ openacs-4/packages/file-storage/sql/oracle/file-storage-drop.sql 4 Apr 2002 03:01:06 -0000 1.8 @@ -27,9 +27,7 @@ / show errors -drop view fs_folders_and_files; -drop view fs_files; -drop view fs_folders; +@@ file-storage-views-drop.sql drop trigger fs_package_items_delete_trig; drop trigger fs_root_folder_delete_trig; Index: openacs-4/packages/file-storage/sql/oracle/file-storage-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/oracle/file-storage-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/file-storage/sql/oracle/file-storage-package-create.sql 4 Apr 2002 03:01:06 -0000 1.1 @@ -0,0 +1,663 @@ +-- +-- packages/file-storage/sql/file-storage-package-create.sql +-- +-- @author yon (yon@openforce.net) +-- @creation-date 2002-04-03 +-- @version $Id: file-storage-package-create.sql,v 1.1 2002/04/04 03:01:06 yon 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 + -- + 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_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'); + + -- 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 + -- + 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, + 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; + + content_item.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; + + content_item.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 + ); + + content_item.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; + + content_item.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_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 + ); + + -- 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; + + -- We may have to delete other items here, e.g., symlinks(future feature) + 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; Index: openacs-4/packages/file-storage/sql/oracle/file-storage-simple-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/oracle/Attic/file-storage-simple-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/file-storage/sql/oracle/file-storage-simple-create.sql 2 Apr 2002 07:00:53 -0000 1.1 +++ openacs-4/packages/file-storage/sql/oracle/file-storage-simple-create.sql 4 Apr 2002 03:01:06 -0000 1.2 @@ -16,30 +16,23 @@ object_id integer constraint fs_simp_obj_id_fk references acs_objects(object_id) - constraint fs_simp_obj_id_pk + constraint fs_simple_objects_pk primary key, folder_id integer constraint fs_simp_folder_id_fk - references cr_folders(folder_id), - name varchar(250) not null, - description varchar(4000) ); +create index fs_so_folder_id_idx on fs_simple_objects (folder_id); create table fs_urls ( url_id integer constraint fs_url_url_id_fk references fs_simple_objects(object_id) - constraint fs_url_url_id_pk + constraint fs_urls_pk primary key, url varchar(250) not null ); - -create view fs_urls_full as -select * from fs_urls, fs_simple_objects -where url_id = object_id; - begin -- stuff for non-versioned file-storage objects acs_object_type.create_type ( @@ -63,4 +56,3 @@ end; / show errors; - Index: openacs-4/packages/file-storage/sql/oracle/file-storage-simple-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/oracle/Attic/file-storage-simple-package-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/file-storage/sql/oracle/file-storage-simple-package-create.sql 2 Apr 2002 07:00:53 -0000 1.1 +++ openacs-4/packages/file-storage/sql/oracle/file-storage-simple-package-create.sql 4 Apr 2002 03:01:06 -0000 1.2 @@ -12,146 +12,139 @@ create or replace package fs_simple_object as - function new ( - object_id in fs_simple_objects.object_id%TYPE default NULL, - object_type in acs_objects.object_type%TYPE default 'fs_simple_object', - folder_id in fs_simple_objects.folder_id%TYPE, - name in fs_simple_objects.name%TYPE, - description in fs_simple_objects.description%TYPE, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE, - context_id in acs_objects.context_id%TYPE - ) return fs_simple_objects.object_id%TYPE; + function new ( + object_id in fs_simple_objects.object_id%TYPE default NULL, + object_type in acs_objects.object_type%TYPE default 'fs_simple_object', + folder_id in fs_simple_objects.folder_id%TYPE, + name in fs_simple_objects.name%TYPE, + description in fs_simple_objects.description%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE + ) return fs_simple_objects.object_id%TYPE; - procedure delete ( - object_id in fs_simple_objects.object_id%TYPE - ); + procedure delete ( + object_id in fs_simple_objects.object_id%TYPE + ); end fs_simple_object; / show errors - - create or replace package body fs_simple_object as - function new ( - object_id in fs_simple_objects.object_id%TYPE default NULL, - object_type in acs_objects.object_type%TYPE default 'fs_simple_object', - folder_id in fs_simple_objects.folder_id%TYPE, - name in fs_simple_objects.name%TYPE, - description in fs_simple_objects.description%TYPE, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE, - context_id in acs_objects.context_id%TYPE - ) return fs_simple_objects.object_id%TYPE - is - v_object_id acs_objects.object_id%TYPE; - begin - v_object_id:= acs_object.new ( - object_id => object_id, - object_type => object_type, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - context_id => context_id - ); + function new ( + object_id in fs_simple_objects.object_id%TYPE default NULL, + object_type in acs_objects.object_type%TYPE default 'fs_simple_object', + folder_id in fs_simple_objects.folder_id%TYPE, + name in fs_simple_objects.name%TYPE, + description in fs_simple_objects.description%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE + ) return fs_simple_objects.object_id%TYPE + is + v_object_id acs_objects.object_id%TYPE; + begin + v_object_id:= acs_object.new ( + object_id => object_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); - insert into fs_simple_objects - (object_id, folder_id, name, description) values - (v_object_id, folder_id, name, description); - - return v_object_id; - end new; + insert into fs_simple_objects + (object_id, folder_id, name, description) values + (v_object_id, folder_id, name, description); - procedure delete ( - object_id in fs_simple_objects.object_id%TYPE - ) - is - begin - acs_object.delete(object_id); - end delete; + content_item.update_last_modified(fs_simple_object.new.folder_id); + return v_object_id; + end new; + + procedure delete ( + object_id in fs_simple_objects.object_id%TYPE + ) + is + begin + acs_object.delete(object_id); + end delete; + end fs_simple_object; / show errors - - - create or replace package fs_url as - function new ( - url_id in fs_urls.url_id%TYPE default NULL, - object_type in acs_objects.object_type%TYPE default 'fs_url', - url in fs_urls.url%TYPE, - folder_id in fs_simple_objects.folder_id%TYPE, - name in fs_simple_objects.name%TYPE, - description in fs_simple_objects.description%TYPE, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE, - context_id in acs_objects.context_id%TYPE - ) return fs_urls.url_id%TYPE; + function new ( + url_id in fs_urls.url_id%TYPE default NULL, + object_type in acs_objects.object_type%TYPE default 'fs_url', + url in fs_urls.url%TYPE, + folder_id in fs_simple_objects.folder_id%TYPE, + name in fs_simple_objects.name%TYPE, + description in fs_simple_objects.description%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE + ) return fs_urls.url_id%TYPE; - procedure delete ( - url_id in fs_urls.url_id%TYPE - ); + procedure delete ( + url_id in fs_urls.url_id%TYPE + ); end fs_url; / show errors - - create or replace package body fs_url as - function new ( - url_id in fs_urls.url_id%TYPE default NULL, - object_type in acs_objects.object_type%TYPE default 'fs_url', - url in fs_urls.url%TYPE, - folder_id in fs_simple_objects.folder_id%TYPE, - name in fs_simple_objects.name%TYPE, - description in fs_simple_objects.description%TYPE, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE, - creation_ip in acs_objects.creation_ip%TYPE, - context_id in acs_objects.context_id%TYPE - ) return fs_urls.url_id%TYPE - is - v_url_id fs_simple_objects.object_id%TYPE; - begin - v_url_id:= fs_simple_object.new ( - object_id => url_id, - object_type => object_type, - folder_id => folder_id, - name => name, - description => description, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - context_id => context_id - ); - - insert into fs_urls - (url_id, url) values - (v_url_id, url); + function new ( + url_id in fs_urls.url_id%TYPE default NULL, + object_type in acs_objects.object_type%TYPE default 'fs_url', + url in fs_urls.url%TYPE, + folder_id in fs_simple_objects.folder_id%TYPE, + name in fs_simple_objects.name%TYPE, + description in fs_simple_objects.description%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE + ) return fs_urls.url_id%TYPE + is + v_url_id fs_simple_objects.object_id%TYPE; + begin + v_url_id:= fs_simple_object.new ( + object_id => url_id, + object_type => object_type, + folder_id => folder_id, + name => name, + description => description, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); - return v_url_id; - end new; + insert into fs_urls + (url_id, url) values + (v_url_id, url); - procedure delete ( - url_id in fs_urls.url_id%TYPE - ) - is - begin - delete from fs_urls where url_id= fs_url.delete.url_id; + return v_url_id; + end new; - fs_simple_object.delete(url_id); - end delete; + procedure delete ( + url_id in fs_urls.url_id%TYPE + ) + is + begin + delete from fs_urls where url_id= fs_url.delete.url_id; + fs_simple_object.delete(url_id); + end delete; + end fs_url; / show errors - - Index: openacs-4/packages/file-storage/sql/oracle/file-storage-views-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/oracle/file-storage-views-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/file-storage/sql/oracle/file-storage-views-create.sql 4 Apr 2002 03:01:06 -0000 1.1 @@ -0,0 +1,98 @@ +-- +-- packages/file-storage/sql/oracle/file-storage-views-create.sql +-- +-- @author yon (yon@openforce.net) +-- @creation-date 2002-04-03 +-- @version $Id: file-storage-views-create.sql,v 1.1 2002/04/04 03:01:06 yon Exp $ +-- + +create or replace view fs_urls_full +as + select fs_urls.url_id, + fs_urls.url, + fs_simple_objects.folder_id, + fs_simple_objects.name, + fs_simple_objects.description, + acs_objects.* + from fs_urls, + fs_simple_objects, + acs_objects + where fs_urls.url_id = fs_simple_objects.object_id + and fs_simple_objects.object_id = acs_objects.object_id; + +create or replace view fs_folders +as + select cr_folders.folder_id, + cr_folders.label as name, + acs_objects.last_modified, + ((select count(*) + from cr_items ci + where ci.parent_id = cr_folders.folder_id) + + + (select count(*) + from fs_simple_objects + where fs_simple_objects.folder_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, + acs_objects + where cr_folders.folder_id = cr_items.item_id + and cr_folders.folder_id = acs_objects.object_id; + +create or replace 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 or replace view fs_objects +as + select fs_folders.folder_id as object_id, + 0 as live_revision, + 'Folder' as type, + fs_folders.content_size, + fs_folders.name, + fs_folders.last_modified, + fs_folders.url, + fs_folders.parent_id, + 0 as sort_key + from fs_folders + union + select fs_files.file_id as object_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 + union + select fs_urls_full.url_id as object_id, + 0 as live_revision, + 'URL' as type, + 0 as content_size, + fs_urls_full.name, + fs_urls_full.last_modified, + fs_urls_full.url, + fs_urls_full.folder_id as parent_id, + 1 as sort_key + from fs_urls_full; Index: openacs-4/packages/file-storage/sql/oracle/file-storage-views-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/oracle/file-storage-views-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/file-storage/sql/oracle/file-storage-views-drop.sql 4 Apr 2002 03:01:06 -0000 1.1 @@ -0,0 +1,12 @@ +-- +-- packages/file-storage/sql/oracle/file-storage-views-create.sql +-- +-- @author yon (yon@openforce.net) +-- @creation-date 2002-04-03 +-- @version $Id: file-storage-views-drop.sql,v 1.1 2002/04/04 03:01:06 yon Exp $ +-- + +drop view fs_objects; +drop view fs_files; +drop view fs_folders; +drop view fs_urls_full; 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.14 -r1.15 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql 2 Apr 2002 06:13:29 -0000 1.14 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql 4 Apr 2002 03:01:06 -0000 1.15 @@ -1,5 +1,5 @@ -- --- packages/file-storage/sql/postgresql/file-storage-create.sql +-- file-storage/sql/postgresql/file-storage-create.sql -- -- @author Kevin Scaldeferri (kevin@arsdigita.com) -- @creation-date 6 Nov 2000 @@ -55,726 +55,11 @@ '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 +\i file-storage-package-create.sql - select count(*) into v_count - from fs_root_folders - where package_id = get_root_folder__package_id; +\i file-storage-simple-create.sql +\i file-storage-simple-package-create.sql - 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, - acs_objects.last_modified, - (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, - acs_objects - where cr_folders.folder_id = cr_items.item_id - and cr_folders.folder_id = acs_objects.object_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, - fs_folders.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; +\i file-storage-views-create.sql Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-drop.sql,v diff -u -r1.7 -r1.8 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-drop.sql 13 Mar 2002 22:50:53 -0000 1.7 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-drop.sql 4 Apr 2002 03:01:06 -0000 1.8 @@ -1,5 +1,5 @@ -- --- packages/file-storage/sql/postgresql/file-storage-drop.sql +-- file-storage/sql/postgresql/file-storage-drop.sql -- -- @author Kevin Scaldeferri (kevin@arsdigita.com) -- @creation-date 6 Now 2000 @@ -37,9 +37,7 @@ select inline_0(); drop function inline_0(); -drop view fs_folders_and_files; -drop view fs_files; -drop view fs_folders; +\i file-storage-views-drop.sql; drop function fs_package_items_delete_trig(); drop trigger fs_package_items_delete_trig on fs_root_folders; @@ -56,6 +54,4 @@ 'file_storage_object', -- content_type 'f', -- drop_children_p 'f' -- drop_table_p - ); - - +); Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-package-create.sql 4 Apr 2002 03:01:06 -0000 1.1 @@ -0,0 +1,686 @@ +-- +-- 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.1 2002/04/04 03:01:06 yon Exp $ +-- + +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; + v_item_id cr_items.item_id%TYPE; +begin + + if new_file__indb_p + then + select 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) + ) + into v_item_id; + else + select 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 + ) + into v_item_id; + + end if; + + select content_item__update_last_modified(new_file__folder_id); + + return v_item_id; + +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 + select 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; + + select content_item__update_last_modified(copy_file__target_folder_id); + + 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 + + select content_item__move( + move_file__file_id, -- item_id + move_file__target_folder_id -- target_folder_id + ); + + select content_item__update_last_modified(move_file__target_folder_id); + + return 0; +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; + 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 + ); + + -- Make live the newly created revision + select 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; + + select content_item__update_last_modified(v_folder_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 Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-sc-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-sc-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-sc-create.sql 18 Dec 2001 20:54:42 -0000 1.1 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-sc-create.sql 4 Apr 2002 03:01:06 -0000 1.2 @@ -1,6 +1,6 @@ -- Implement site-wide search using OpenFTS -- --- packages/file-storage/sql/postgresql/file-storage-sc-create.sql +-- file-storage/sql/postgresql/file-storage-sc-create.sql -- -- @author Jowell Sabino (jowellsabino@netscape.net) -- Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-sc-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-sc-drop.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-sc-drop.sql 18 Dec 2001 20:54:42 -0000 1.1 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-sc-drop.sql 4 Apr 2002 03:01:06 -0000 1.2 @@ -1,6 +1,6 @@ -- Drop site-wide search using OpenFTS -- --- packages/file-storage/sql/postgresql/file-storage-sc-drop.sql +-- file-storage/sql/postgresql/file-storage-sc-drop.sql -- -- @author Jowell Sabino (jowellsabino@netscape.net) -- Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-simple-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/Attic/file-storage-simple-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-simple-create.sql 2 Apr 2002 07:00:53 -0000 1.1 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-simple-create.sql 4 Apr 2002 03:01:06 -0000 1.2 @@ -35,12 +35,6 @@ url varchar(250) not null ); - -create view fs_urls_full as -select * from fs_urls, fs_simple_objects -where url_id = object_id; - - -- stuff for non-versioned file-storage objects select acs_object_type__create_type ( 'fs_simple_object', @@ -68,4 +62,3 @@ NULL, NULL ); - Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-simple-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/Attic/file-storage-simple-package-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-simple-package-create.sql 2 Apr 2002 07:00:53 -0000 1.1 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-simple-package-create.sql 4 Apr 2002 03:01:06 -0000 1.2 @@ -116,6 +116,3 @@ return 0; END; ' language 'plpgsql'; - - - Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-views-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-views-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-views-create.sql 4 Apr 2002 03:01:06 -0000 1.1 @@ -0,0 +1,98 @@ +-- +-- file-storage/sql/postgresql/file-storage-views-create.sql +-- +-- @author Kevin Scaldeferri (kevin@arsdigita.com) +-- @creation-date 6 Nov 2000 +-- @cvs-id $Id: file-storage-views-create.sql,v 1.1 2002/04/04 03:01:06 yon Exp $ +-- + +create view fs_urls_full +as + select fs_urls.url_id, + fs_urls.url, + fs_simple_objects.folder_id, + fs_simple_objects.name, + fs_simple_objects.description, + acs_objects.* + from fs_urls, + fs_simple_objects, + acs_objects + where fs_urls.url_id = fs_simple_objects.object_id + and fs_simple_objects.object_id = acs_objects.object_id; + +create view fs_folders +as + select cr_folders.folder_id, + cr_folders.label as name, + acs_objects.last_modified, + ((select count(*) + from cr_items ci + where ci.parent_id = cr_folders.folder_id) + + + (select count(*) + from fs_simple_objects + where fs_simple_objects.folder_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, + acs_objects + where cr_folders.folder_id = cr_items.item_id + and cr_folders.folder_id = acs_objects.object_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 or replace view fs_objects +as + select fs_folders.folder_id as object_id, + 0 as live_revision, + 'Folder' as type, + fs_folders.content_size, + fs_folders.name, + fs_folders.last_modified, + fs_folders.url, + fs_folders.parent_id, + 0 as sort_key + from fs_folders + union + select fs_files.file_id as object_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 + union + select fs_urls_full.url_id as object_id, + 0 as live_revision, + 'URL' as type, + 0 as content_size, + fs_urls_full.name, + fs_urls_full.last_modified, + fs_urls_full.url, + fs_urls_full.folder_id as parent_id, + 1 as sort_key + from fs_urls_full; Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-views-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-views-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-views-drop.sql 4 Apr 2002 03:01:06 -0000 1.1 @@ -0,0 +1,12 @@ +-- +-- file-storage/sql/postresql/file-storage-views-drop.sql +-- +-- @author yon (yon@openforce.net) +-- @creation-date 2002-04-03 +-- @version $Id: file-storage-views-drop.sql,v 1.1 2002/04/04 03:01:06 yon Exp $ +-- + +drop view fs_objects; +drop view fs_files; +drop view fs_folders; +drop view fs_urls_full; Index: openacs-4/packages/file-storage/tcl/file-storage-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/tcl/file-storage-procs-oracle.xql,v diff -u -r1.11 -r1.12 --- openacs-4/packages/file-storage/tcl/file-storage-procs-oracle.xql 2 Apr 2002 07:00:53 -0000 1.11 +++ openacs-4/packages/file-storage/tcl/file-storage-procs-oracle.xql 4 Apr 2002 03:01:06 -0000 1.12 @@ -41,39 +41,21 @@ - (select fs_folders_and_files.file_id, - 'f' as url_p, - 't' as versioned_p, - fs_folders_and_files.name, - fs_folders_and_files.live_revision, - fs_folders_and_files.type, - to_char(fs_folders_and_files.last_modified, 'YYYY-MM-DD HH24:MI') as last_modified, - case when fs_folders_and_files.last_modified >= (sysdate - :n_past_days) then 1 else 0 end as new_p, - fs_folders_and_files.content_size, - decode(acs_permission.permission_p(fs_folders_and_files.file_id, :user_id, 'write'), 'f', 0, 1) as write_p, - decode(acs_permission.permission_p(fs_folders_and_files.file_id, :user_id, 'delete'), 'f', 0, 1) as delete_p, - decode(acs_permission.permission_p(fs_folders_and_files.file_id, :user_id, 'admin'), 'f', 0, 1) as admin_p - from fs_folders_and_files - where fs_folders_and_files.parent_id = :folder_id - and 't' = acs_permission.permission_p(fs_folders_and_files.file_id, :user_id, 'read')) - union - (select fs_simple_objects.object_id as file_id, - 't' as url_p, - 'f' as versioned_p, - fs_simple_objects.name, - 0 as live_revision, - 'url' as type, - NULL as last_modified, - 0 as new_p, - 0 as content_size, - decode(acs_permission.permission_p(fs_simple_objects.object_id, :user_id, 'write'), 'f', 0,1) as write_p, - decode(acs_permission.permission_p(fs_simple_objects.object_id, :user_id, 'delete'), 'f', 0, 1) as delete_p, - decode(acs_permission.permission_p(fs_simple_objects.object_id, :user_id, 'admin'), 'f', 0, 1) as admin_p - from fs_simple_objects - where folder_id= :folder_id - and 't' = acs_permission.permission_p(fs_simple_objects.object_id, :user_id, 'read')) - - order by name + select fs_objects.object_id, + fs_objects.name, + fs_objects.live_revision, + fs_objects.type, + to_char(fs_objects.last_modified, 'Month DD YYYY HH24:MI') as last_modified, + case when fs_objects.last_modified >= (sysdate - :n_past_days) then 1 else 0 end as new_p, + fs_objects.content_size, + decode(acs_permission.permission_p(fs_objects.object_id, :user_id, 'write'), 'f', 0, 1) as write_p, + decode(acs_permission.permission_p(fs_objects.object_id, :user_id, 'delete'), 'f', 0, 1) as delete_p, + decode(acs_permission.permission_p(fs_objects.object_id, :user_id, 'admin'), 'f', 0, 1) as admin_p + from fs_objects + where fs_objects.parent_id = :folder_id + and 't' = acs_permission.permission_p(fs_objects.object_id, :user_id, 'read') + order by fs_objects.sort_key, + fs_objects.name Index: openacs-4/packages/file-storage/tcl/file-storage-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/tcl/file-storage-procs-postgresql.xql,v diff -u -r1.17 -r1.18 --- openacs-4/packages/file-storage/tcl/file-storage-procs-postgresql.xql 2 Apr 2002 06:21:24 -0000 1.17 +++ openacs-4/packages/file-storage/tcl/file-storage-procs-postgresql.xql 4 Apr 2002 03:01:06 -0000 1.18 @@ -33,21 +33,21 @@ - select fs_folders_and_files.file_id, - fs_folders_and_files.name, - fs_folders_and_files.live_revision, - fs_folders_and_files.type, - to_char(fs_folders_and_files.last_modified, 'YYYY-MM-DD HH24:MI') as last_modified, - case when fs_folders_and_files.last_modified >= (sysdate - :n_past_days) then 1 else 0 end as new_p, - fs_folders_and_files.content_size, - case when acs_permission__permission_p(fs_folders_and_files, :user_id, 'write') = 'f' then 0 else 1 end as write_p, - case when acs_permission__permission_p(fs_folders_and_files, :user_id, 'delete') = 'f' then 0 else 1 end as delete_p, - case when acs_permission__permission_p(fs_folders_and_files, :user_id, 'admin') = 'f' then 0 else 1 end as admin_p - from fs_folders_and_files - where fs_folders_and_files.parent_id = :folder_id - and 't' = acs_permission__permission_p(fs_folders_and_files.file_id, :user_id, 'read') - order by fs_folders_and_files.sort_key, - fs_folders_and_files.name + select fs_objects.object_id, + fs_objects.name, + fs_objects.live_revision, + fs_objects.type, + to_char(fs_objects.last_modified, 'YYYY-MM-DD HH24:MI') as last_modified, + case when fs_objects.last_modified >= (sysdate - :n_past_days) then 1 else 0 end as new_p, + fs_objects.content_size, + case when acs_permission__permission_p(fs_objects.object_id, :user_id, 'write') = 'f' then 0 else 1 end as write_p, + case when acs_permission__permission_p(fs_objects.object_id, :user_id, 'delete') = 'f' then 0 else 1 end as delete_p, + case when acs_permission__permission_p(fs_objects.object_id, :user_id, 'admin') = 'f' then 0 else 1 end as admin_p + from fs_objects + where fs_objects.parent_id = :folder_id + and 't' = acs_permission__permission_p(fs_objects.object_id, :user_id, 'read') + order by fs_objects.sort_key, + fs_objects.name Index: openacs-4/packages/file-storage/tcl/file-storage-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/tcl/file-storage-procs.xql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/file-storage/tcl/file-storage-procs.xql 22 Feb 2002 22:12:10 -0000 1.3 +++ openacs-4/packages/file-storage/tcl/file-storage-procs.xql 4 Apr 2002 03:01:06 -0000 1.4 @@ -51,9 +51,14 @@ - select count(*) - from cr_items - where cr_items.parent_id = :folder_id + select (select count(*) + from cr_items + where cr_items.parent_id = :folder_id) + + + (select count(*) + from fs_simple_objects + where fs_simple_objects.folder_id = :folder_id) + from dual Index: openacs-4/packages/file-storage/www/folder-chunk.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/www/folder-chunk.adp,v diff -u -r1.5 -r1.6 --- openacs-4/packages/file-storage/www/folder-chunk.adp 2 Apr 2002 15:25:37 -0000 1.5 +++ openacs-4/packages/file-storage/www/folder-chunk.adp 4 Apr 2002 03:01:06 -0000 1.6 @@ -13,28 +13,44 @@ - @contents.name@ -( new ) + @contents.name@ +( new )   @contents.content_size@ items - Folder + @contents.type@ @contents.last_modified@ + - @contents.name@@contents.name@ -( new ) + @contents.name@ +( new ) - [go to | editdownload | delete] + [ edit | delete ] + +   + @contents.type@ + @contents.last_modified@ + + + + + @contents.name@ +( new ) + + + [ download | delete ] + @contents.content_size@ bytes @contents.type@ @contents.last_modified@ + Index: openacs-4/packages/file-storage/www/folder-chunk.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/www/folder-chunk.tcl,v diff -u -r1.3 -r1.4 --- openacs-4/packages/file-storage/www/folder-chunk.tcl 2 Apr 2002 15:25:37 -0000 1.3 +++ openacs-4/packages/file-storage/www/folder-chunk.tcl 4 Apr 2002 03:01:06 -0000 1.4 @@ -11,7 +11,7 @@ } if {![exists_and_not_null folder_id]} { - return + ad_return_complaint 1 "bad folder id $folder_id" ad_script_abort }