OpenACS

maltes
committed
MAIN:maltes:20061028164555
on 28 Oct 06
Fixed the checks for email and letter to be run only when we are actually sending the message, not when we try to send one
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