-- 
-- upgrade-4.0.1b-4.0.1.sql
--
-- Upgrades from the beta.
-- We had to rename everything from pa_* to pl_*
-- to avoid conflicts with the photo-album package.
-- This is pretty heavy-duty stuff.
--
-- There is also some code at the bottom to remap the sort_n's
-- to always start at 1 for each folder.

-- plan:
-- 1. disable object_type fk constraint
-- 2. rename the object type for each of pa_photo and pa_folder:
--    a. update these columns in acs_object_types
--        object_type
--        table_name
--    b. update acs_objects table
-- 3. enable the object_type fk constraint
-- 4. rename the pa_photo and pa_folder tables
-- 5. rename all the constraints by dropping and adding them
-- 6. drop the index rebuild job
-- 7. drop old views/triggers/plsql
-- 8. re-run all views/triggers/plsql
-- 9. add the index rebuild job

-- AND HERE WE GO...

alter table acs_objects disable constraint acs_objects_object_type_fk;

update acs_object_types
 set object_type = 'pl_photo',
     table_name = 'PL_PHOTOS'
 where object_type = 'pa_photo';

update acs_objects
 set object_type = 'pl_photo'
 where object_type = 'pa_photo';

update acs_object_types
 set object_type = 'pl_folder',
     table_name = 'PL_FOLDERS'
 where object_type = 'pa_folder';

update acs_objects
 set object_type = 'pl_folder'
 where object_type = 'pa_folder';

alter table acs_objects enable constraint acs_objects_object_type_fk;

alter table pa_photos rename to pl_photos;
alter table pa_folders rename to pl_folders;

alter table pl_photos drop constraint pa_photos_id_nn;
alter table pl_photos drop constraint pa_photos_pk;
alter table pl_photos drop constraint pa_photos_obj_fk;
alter table pl_photos drop constraint pa_photos_folder_nn;
alter table pl_photos drop constraint pa_photos_folder_fk;
alter table pl_photos drop constraint pa_photos_sort_nn;
alter table pl_photos drop constraint pa_photos_file_nn;
alter table pl_photos drop constraint pa_photos_file_xtn_nn;
alter table pl_photos drop constraint pa_photos_deleted_p_ck;
alter table pl_photos drop constraint pa_photos_folder_sort_un;
alter table pl_folders drop constraint pa_folders_id_nn;
alter table pl_folders drop constraint pa_folders_pk;
alter table pl_folders drop constraint pa_folders_obj_fk;
alter table pl_folders drop constraint pa_folders_title_nn;
alter table pl_folders drop constraint pa_folders_deleted_p_ck;

alter table pl_folders add constraint pl_folders_pk
 primary key (folder_id);
alter table pl_folders add constraint pl_folders_obj_fk
 foreign key (folder_id) references acs_objects(object_id);
alter table pl_folders add constraint pl_folders_title_nn
 check (title is not null);
alter table pl_folders add constraint pl_folders_deleted_p_ck
 check (deleted_p in ('t','f'));

alter table pl_photos add constraint pl_photos_pk
 primary key (photo_id);
alter table pl_photos add constraint pl_photos_obj_fk
 foreign key (photo_id) references acs_objects(object_id);
alter table pl_photos add constraint pl_photos_folder_nn
 check (folder_id is not null);
alter table pl_photos add constraint pl_photos_folder_fk
 foreign key (folder_id) references pl_folders;
alter table pl_photos add constraint pl_photos_sort_nn
 check (sort_n is not null);
alter table pl_photos add constraint pl_photos_file_nn
 check (client_filename is not null);
alter table pl_photos add constraint pl_photos_file_xtn_nn
 check (file_extension is not null);
alter table pl_photos add constraint pl_photos_deleted_p_ck
 check (deleted_p in ('t','f'));

declare
  cursor cur1 is 
   select job from pa_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

delete from pa_jobs;

alter table pa_jobs rename to pl_jobs;

drop procedure pa_rebuild_idx;
drop package pa_photo;
drop package pa_folder;
drop function pa_photo_date_format;
drop function pa_relative_date;
drop trigger pa_photos_ins_update_tr;
drop trigger pa_photos_post_update_tr;
drop trigger pa_folders_update_tr;
drop view pa_v_photos;
drop view pa_v_folders;

-- we use this temp table to avoid the "table is mutating" problem
-- when re-calculating the sort_n's after a photo is deleted.

create table pl_deleted_photos (
       folder_id       integer
		       constraint pl_deleted_photos_folder_nn
		       not null,
       sort_n	       integer
                       constraint pl_delete_photos_sort_n_nn
                       not null,
       swept_p         char(1) default 'f'
                       constraint pl_deleted_photos_swept_p_ck
		       check (swept_p in ('t','f'))
);

@@ photo-album-lite-views
@@ photo-album-lite-plsql
@@ photo-album-lite-triggers

alter index pa_photo_idx rename to pl_photo_idx;

create or replace procedure pl_rebuild_idx
is
begin 
  ctx_ddl.sync_index('pl_photo_idx');
end;
/
show errors;

variable pl_rebuild_idx_job number;
begin 
  dbms_job.submit(:pl_rebuild_idx_job, 
                 'pl_rebuild_idx;', 
                 interval => 'sysdate + 1/6');
  insert into pl_jobs values (:pl_rebuild_idx_job);
end;
/
show errors;
print pl_rebuild_idx_job

-- Make sort_n start at 1 for each folder so
-- that we can easily display "Photo 3 of 12".
-- Since we preserve the original order
-- we don't need to defer the unique constraint
-- on (sort_n,folder_id).
declare
 v_count integer;
 cursor cur is
  select folder_id as id
  from pl_folders;
begin
 for folder in cur loop
  v_count := 0;
  for photo in (select photo_id as id from pl_v_photos where folder_id = folder.id order by sort_n) loop
   v_count := v_count + 1;
   update pl_photos set sort_n = v_count
   where photo_id = photo.id;
  end loop;
 end loop;
end;
/
show errors