--
-- $Id: photo-album-lite-drop.sql,v 1.2 2003/09/30 12:10:00 mohanp Exp $
--

prompt **
prompt ** NOTE:  This script will fail if you did not delete all photos and
prompt **        folders on your system.
prompt **
prompt ** You can recover from a failed drop attempt by running this script:
prompt **  @yourserver/packages/photo-album-lite/sql/cleanup-failed-drop.sql
prompt **  

-- remove all rows associated with deleted objects.
declare
  v_gc_installed_p integer;
  cursor cur1 is 
   select photo_id from pl_photos
   where deleted_p = 't';
  cursor cur2 is
   select folder_id from pl_folders
   where deleted_p = 't';
  v_id integer;
begin
  select count(1) into v_gc_installed_p
  from user_tables where table_name = 'GENERAL_COMMENTS';

  open cur1;
  loop
    fetch cur1 into v_id;
    exit when cur1%NOTFOUND;
    delete from acs_permissions where object_id = v_id;
    if v_gc_installed_p = 1 then
      execute immediate 'begin
      for message in (select comment_id as id from general_comments
                      where object_id = ' || v_id || ') loop
         acs_message.del(message.id);
      end loop;
end;';
    end if;
    acs_object.del (v_id);
  end loop;
  close cur1;

  open cur2;
  loop
    fetch cur2 into v_id;
    exit when cur2%NOTFOUND;
    delete from acs_permissions where object_id = v_id;
    acs_object.del (v_id);
  end loop;

end;
/
show errors

-- this will fail if you haven't deleted
-- all photos and folders.
begin
  acs_object_type.drop_type ('pl_photo');
  acs_object_type.drop_type ('pl_folder');
end;
/
show errors

drop procedure pl_rebuild_idx;

-- shut off the dbms_job
declare
  cursor cur1 is 
   select job from pl_jobs;
  v_job integer;
begin
  open cur1;
  loop
    fetch cur1 into v_job;
    exit when cur1%NOTFOUND;
    dbms_job.remove(v_job);
  end loop;
  close cur1;
end;
/
show errors

drop table pl_jobs;
drop table pl_reshuffle_queue;

drop function pl_photo_date_format;
drop function pl_relative_date;

drop view pl_v_photos;
drop view pl_v_folders;

drop table pl_photos;
drop table pl_folders;

drop package pl_photo;
drop package pl_folder;

drop sequence pl_moved_photo_sort_n_seq;