OpenACS

peterm
committed
MAIN:peterm:20030925082205
on 25 Sep 03
bug 535 - fixed variable name typo in outlook export. Made the export to outlook link more clear
openacs-4/.../oracle/plsql-packages.sql (+442)
  1 -- /packages/photo-album/sql/plsql-packages.sql
  2 --
  3 -- packages to support ACS photo ablum application
  4 --
  5 -- Copyright (C) 2000-2001 ArsDigita Corporation
  6 -- @author Tom Baginski (bags@arsdigita.com)
  7 -- @creation-date 01/08/2000
  8 --
  9 -- @cvs-id $Id$
  10 --
  11 -- This is free software distributed under the terms of the GNU Public
  12 -- License.  Full text of the license is available from the GNU Project:
  13 -- http://www.fsf.org/copyleft/gpl.html
  14
  15 -- wtem@olywa.net, 2001-09-27
  16 -- pa_image package now replaced with CR standard image package
  17
  18 create or replace package pa_photo
  19 as
  20   --/**
  21   --  creates new pa_photo
  22   --  associated images must be created by calling script
  23   --*/
  24   function new (
  25     name                in cr_items.name%TYPE,
  26     parent_id           in cr_items.parent_id%TYPE default null,
  27     item_id             in acs_objects.object_id%TYPE default null,
  28     revision_id         in acs_objects.object_id%TYPE default null,
  29     content_type        in acs_object_types.object_type%TYPE default 'pa_photo',
  30     creation_date       in acs_objects.creation_date%TYPE default sysdate,
  31     creation_user       in acs_objects.creation_user%TYPE default null,
  32     creation_ip         in acs_objects.creation_ip%TYPE default null,
  33     locale              in cr_items.locale%TYPE default null,
  34     context_id          in acs_objects.context_id%TYPE default null,
  35     title               in cr_revisions.title%TYPE default null,
  36     description         in cr_revisions.description%TYPE default null,
  37     relation_tag        in cr_child_rels.relation_tag%TYPE default null,
  38     is_live             in char default 'f',
  39     publish_date        in cr_revisions.publish_date%TYPE default sysdate,
  40     mime_type           in cr_revisions.mime_type%TYPE default null,
  41     nls_language        in cr_revisions.nls_language%TYPE default null,
  42     caption             in pa_photos.caption%TYPE default null,
  43     story               in pa_photos.story%TYPE default null,
  44     user_filename       in pa_photos.user_filename%TYPE default null
  45   ) return cr_items.item_id%TYPE;
  46
  47   --/**
  48   --  Deletes a single revision of a pa_photo
  49   --*/
  50   procedure delete_revision (
  51     revision_id         in acs_objects.object_id%TYPE
  52   );
  53
  54   --/**
  55   --  Deletes a a pa_photo and all revisions,
  56   --  Deletes associated images (which schedules binary files for deleation)
  57   --
  58   --  Be careful, cannot be undone (easily)
  59   --*/
  60   procedure delete (
  61     item_id             in acs_objects.object_id%TYPE
  62   );
  63
  64 end pa_photo;
  65 /
  66 show errors;
  67
  68 create or replace package body pa_photo
  69 as
  70   function new (
  71     name                in cr_items.name%TYPE,
  72     parent_id           in cr_items.parent_id%TYPE default null,
  73     item_id             in acs_objects.object_id%TYPE default null,
  74     revision_id         in acs_objects.object_id%TYPE default null,
  75     content_type        in acs_object_types.object_type%TYPE default 'pa_photo',
  76     creation_date       in acs_objects.creation_date%TYPE default sysdate,
  77     creation_user       in acs_objects.creation_user%TYPE default null,
  78     creation_ip         in acs_objects.creation_ip%TYPE default null,
  79     locale              in cr_items.locale%TYPE default null,
  80     context_id          in acs_objects.context_id%TYPE default null,
  81     title               in cr_revisions.title%TYPE default 'photo',
  82     description         in cr_revisions.description%TYPE default null,
  83     relation_tag        in cr_child_rels.relation_tag%TYPE default null,
  84     is_live             in char default 'f',
  85     publish_date        in cr_revisions.publish_date%TYPE default sysdate,
  86     mime_type           in cr_revisions.mime_type%TYPE default null,
  87     nls_language        in cr_revisions.nls_language%TYPE default null,
  88     caption             in pa_photos.caption%TYPE default null,
  89     story               in pa_photos.story%TYPE default null,
  90     user_filename       in pa_photos.user_filename%TYPE default null
  91   ) return cr_items.item_id%TYPE
  92   is
  93     v_item_id           cr_items.item_id%TYPE;
  94     v_revision_id       cr_revisions.revision_id%TYPE;
  95   begin
  96    
  97     v_item_id := content_item.new (
  98       name          => name,
  99       item_id       => item_id,
  100       parent_id     => parent_id,
  101       relation_tag  => relation_tag,
  102       content_type  =>  content_type,
  103       creation_date => sysdate,
  104       creation_user => creation_user,
  105       creation_ip   => creation_ip,
  106       locale        => locale,
  107       context_id    => context_id
  108     );
  109
  110     v_revision_id := content_revision.new (
  111       title => title,
  112       description   => description,
  113       item_id       => v_item_id,
  114       revision_id   => revision_id,
  115       publish_date  => publish_date,
  116       mime_type     => mime_type,
  117       nls_language  => nls_language,
  118       creation_date => sysdate,
  119       creation_user => creation_user,
  120       creation_ip   => creation_ip
  121     );
  122
  123     insert into pa_photos
  124     (pa_photo_id, caption, story, user_filename)
  125     values
  126     (v_revision_id, caption, story, user_filename);
  127
  128     -- is_live => 't' not used as part of content_item.new
  129     -- because content_item.new does not let developer specify revision_id and doesn't return revision_id,
  130     -- revision_id needed for the insert to pa_photos
  131
  132     if is_live = 't' then
  133        content_item.set_live_revision (
  134          revision_id => v_revision_id
  135     );
  136     end if;
  137
  138     return v_item_id;
  139   end new;
  140
  141   procedure delete_revision (
  142     revision_id         in acs_objects.object_id%TYPE
  143   )
  144   is
  145
  146     -- do not need to delete from the pa_photos
  147     -- the on delete cascade will take care of this
  148     -- during the content_revision.delete
  149   begin
  150     content_revision.delete (
  151       revision_id    => revision_id
  152     );
  153
  154   end delete_revision;
  155
  156   procedure delete (
  157     item_id             in acs_objects.object_id%TYPE
  158   )
  159   is
  160     cursor pa_image_cur is
  161       select
  162         child_id
  163       from
  164         cr_child_rels
  165       where
  166         parent_id = pa_photo.delete.item_id;
  167
  168   begin
  169    
  170     -- delete all the images associated with the photo
  171     for v_pa_image_val in pa_image_cur loop
  172       image.delete (
  173         item_id => v_pa_image_val.child_id
  174       );
  175     end loop;
  176
  177     -- content_item.delete takes care of all revision
  178     -- on delete cascades take care of rest
  179
  180     content_item.delete (
  181       item_id   =>  item_id
  182     );
  183
  184   end delete;
  185
  186 end pa_photo;
  187 /
  188 show errors;
  189
  190
  191 create or replace package pa_album
  192 as
  193   --/**
  194   -- Creates a new pa_album
  195   --*/
  196   function new (
  197      name           in cr_items.name%TYPE,
  198      album_id       in cr_items.item_id%TYPE default null,
  199      parent_id      in cr_items.parent_id%TYPE default null,
  200      revision_id    in cr_revisions.revision_id%TYPE default null,
  201      content_type   in acs_object_types.object_type%TYPE default 'pa_album',
  202      is_live        in char default 'f',
  203      creation_date  in acs_objects.creation_date%TYPE default sysdate,
  204      creation_user  in acs_objects.creation_user%TYPE default null,
  205      creation_ip    in acs_objects.creation_ip%TYPE default null,
  206      locale         in cr_items.locale%TYPE default null,
  207      context_id     in acs_objects.context_id%TYPE default null,
  208      relation_tag   in cr_child_rels.relation_tag%TYPE default null,
  209      publish_date   in cr_revisions.publish_date%TYPE default sysdate,
  210      mime_type      in cr_revisions.mime_type%TYPE default null,
  211      nls_language   in cr_revisions.nls_language%TYPE default null,
  212      title          in cr_revisions.title%TYPE default null,
  213      description    in cr_revisions.description%TYPE default null,
  214      story          in pa_albums.story%TYPE default null
  215   ) return cr_items.item_id%TYPE;
  216
  217   --/**
  218   -- Deletes a single revision of a pa_album
  219   --*/
  220   procedure delete_revision (
  221      revision_id    in cr_revisions.revision_id%TYPE
  222   );
  223  
  224   --/**
  225   -- Deletes a pa_album and all revisions
  226   -- Album must be empty to be deleted,
  227   -- otherwise delete throws error
  228   --*/
  229   procedure delete (
  230      album_id       in cr_items.item_id%TYPE
  231   );
  232
  233 end pa_album;
  234 /
  235 show errors;
  236
  237 create or replace package body pa_album
  238 as
  239   function new (
  240      name           in cr_items.name%TYPE,
  241      album_id       in cr_items.item_id%TYPE default null,
  242      parent_id      in cr_items.parent_id%TYPE default null,
  243      revision_id    in cr_revisions.revision_id%TYPE default null,
  244      content_type   in acs_object_types.object_type%TYPE default 'pa_album',
  245      is_live        in char default 'f',
  246      creation_date  in acs_objects.creation_date%TYPE default sysdate,
  247      creation_user  in acs_objects.creation_user%TYPE default null,
  248      creation_ip    in acs_objects.creation_ip%TYPE default null,
  249      locale         in cr_items.locale%TYPE default null,
  250      context_id     in acs_objects.context_id%TYPE default null,
  251      relation_tag   in cr_child_rels.relation_tag%TYPE default null,
  252      publish_date   in cr_revisions.publish_date%TYPE default sysdate,
  253      mime_type      in cr_revisions.mime_type%TYPE default null,
  254      nls_language   in cr_revisions.nls_language%TYPE default null,
  255      title          in cr_revisions.title%TYPE default null,
  256      description    in cr_revisions.description%TYPE default null,
  257      story          in pa_albums.story%TYPE default null
  258   ) return cr_items.item_id%TYPE
  259   is
  260     v_item_id       integer;
  261     v_revision_id   integer;
  262   begin
  263     v_item_id := content_item.new (
  264       name          => name,
  265       item_id       => album_id,
  266       parent_id     => parent_id,
  267       relation_tag  => relation_tag,
  268       content_type  => content_type,
  269       creation_date => sysdate,
  270       creation_user => creation_user,
  271       creation_ip   => creation_ip,
  272       locale        => locale,
  273       context_id    => context_id
  274     );
  275
  276     v_revision_id := content_revision.new (
  277       title         => title,
  278       description   => description,
  279       item_id       => v_item_id,
  280       revision_id   => revision_id,
  281       publish_date  => publish_date,
  282       mime_type     => mime_type,
  283       nls_language  => nls_language,
  284       creation_date => sysdate,
  285       creation_user => creation_user,
  286       creation_ip   => creation_ip
  287     );
  288
  289     insert into pa_albums (pa_album_id, story)
  290     values
  291     (v_revision_id, story);
  292
  293     -- is_live => 't' not used as part of content_item.new
  294     -- because content_item.new does not let developer specify revision_id and doesn't return revision_id,
  295     -- revision_id needed for the insert to pa_albums
  296
  297     if is_live = 't' then
  298        content_item.set_live_revision (
  299          revision_id => v_revision_id
  300     );
  301     end if;
  302
  303     return v_item_id;
  304
  305   end new;
  306
  307   procedure delete_revision (
  308     revision_id         in cr_revisions.revision_id%TYPE
  309   )
  310   is
  311   -- do not need to delete from the pa_albums
  312   -- the on delete cascade will take care of this
  313   -- during the content_revision.delete
  314   begin
  315     content_revision.delete (
  316       revision_id    => revision_id
  317     );
  318
  319   end delete_revision;
  320
  321   procedure delete (
  322      album_id       in cr_items.item_id%TYPE
  323   )
  324   is
  325     v_num_children integer;
  326   begin
  327     -- check if album is empty (no rm -r *)
  328     select count(*) into v_num_children
  329     from cr_items
  330     where parent_id = pa_album.delete.album_id;
  331
  332     if v_num_children > 0 then
  333            raise_application_error(-20000,
  334           'The specified album ' || album_id || ' still contains photos. 
  335           An album must be empty before it can be deleted.');
  336     end if;
  337    
  338     -- content_item.delete takes care of all revision
  339     -- on delete cascades take care of rest
  340
  341     content_item.delete (
  342       item_id   =>  album_id
  343     );
  344
  345   end delete;
  346
  347 end pa_album;
  348 /
  349 show errors;
  350
  351 --/**
  352 --  Package does not contain new or delete procedure because
  353 --  it contains general funcition for the photo album application
  354 --  and is not tied to a specific object.
  355 --*/
  356 create or replace package photo_album
  357 as
  358
  359     --/**
  360     -- Returns the root folder corresponding to a package instance.
  361     -- If root folder does not already exist, function returns null
  362     --
  363     -- tcl proc that calls this function from the index page
  364     -- takes care of the case that there is no root folder (new package instance)
  365     -- and creates one with appropriate permissions
  366     --*/
  367
  368     function get_root_folder (
  369         package_id in apm_packages.package_id%TYPE
  370     ) return pa_package_root_folder_map.folder_id%TYPE;
  371
  372     -- wtem@olywa.net, 2001-09-22
  373     -- wrapped up some pl-sql from tcl/photo-album-procs.tcl.pa_new_root_folder
  374     function new_root_folder (  
  375         package_id in apm_packages.package_id%TYPE
  376     ) return pa_package_root_folder_map.folder_id%TYPE;
  377    
  378 end photo_album;
  379 /
  380 show errors
  381
  382 create or replace package body photo_album
  383 as
  384
  385     function get_root_folder (
  386         package_id in apm_packages.package_id%TYPE
  387     ) return pa_package_root_folder_map.folder_id%TYPE
  388     is
  389         v_folder_id     pa_package_root_folder_map.folder_id%TYPE;
  390     begin
  391         
  392         -- this uses 0 as a magic number for
  393         -- no root folder
  394         -- in acs there will never be a folder with id 0
  395
  396         select nvl(folder_id,0) into v_folder_id
  397         from pa_package_root_folder_map
  398         where package_id = get_root_folder.package_id;
  399        
  400         if v_folder_id > 0 then
  401             return v_folder_id;
  402         else
  403             return null;
  404         end if;
  405
  406     end get_root_folder;
  407
  408     function new_root_folder (  
  409         package_id in apm_packages.package_id%TYPE
  410     ) return pa_package_root_folder_map.folder_id%TYPE
  411     is
  412         v_folder_id     pa_package_root_folder_map.folder_id%TYPE;
  413         v_package_name  apm_packages.instance_name%TYPE;
  414         v_package_key   apm_packages.package_key%TYPE;
  415     begin
  416
  417         select instance_name, package_key
  418         into v_package_name, v_package_key
  419         from apm_packages
  420         where package_id = new_root_folder.package_id;
  421
  422         v_folder_id := content_folder.new (
  423         name        => v_package_key || '_' || new_root_folder.package_id,
  424         label       => v_package_name || ' Home',
  425         description => 'Home for ' || v_package_name
  426         );
  427
  428         insert into pa_package_root_folder_map
  429         (package_id, folder_id)
  430         values
  431         (new_root_folder.package_id, v_folder_id);
  432
  433         -- allow child items to be added
  434         content_folder.register_content_type(v_folder_id,'pa_album');
  435         content_folder.register_content_type(v_folder_id,'content_folder');
  436
  437         return v_folder_id;
  438     end new_root_folder;
  439
  440 end photo_album;
  441 /
  442 show errors