-- Data model to support release scheduling of items in the content
-- repository of the ArsDigita Publishing System

-- Copyright (C) 1999-2000 ArsDigita Corporation
-- Author: Karl Goldstein (karlg@arsdigita.com)

-- $Id: content-schedule.sql,v 1.4 2001/04/02 05:35:29 danw Exp $

-- This is free software distributed under the terms of the GNU Public
-- License.  Full text of the license is available from the GNU Project:
-- http://www.fsf.org/copyleft/gpl.html

-- prompt *** Preparing for scheduled updates to live content...

create function cr_scheduled_release_tr () returns opaque as '
begin

   raise EXCEPTION ''-20000: Inserts are not allowed into cr_scheduled_release_job.'';

  return new;
end;' language 'plpgsql';

create trigger cr_scheduled_release_tr 
before insert on cr_scheduled_release_job
for each row execute procedure cr_scheduled_release_tr ();

-- show errors

-- Update the publishing status for items that are due to be released
-- or expired.
-- FIXME: job scheduling not available through postgresql.

create function cr_scheduled_release_exec () returns integer as '
declare
  exec__last_exec             timestamp;
  exec__this_exec             timestamp default now();
  exec__items_released        integer default 0;
  exec__items_expired         integer default 0;
  exec__err_num               integer;  -- sqlcode
  exec__err_msg               varchar;  -- substr(sqlerrm, 1, 500);
  item_rec                    record;
begin

    select last_exec into exec__last_exec from cr_scheduled_release_job;

    for item_rec in select 
                      p.item_id, live_revision
                    from
                      cr_release_periods p, cr_items i
                    where
                      start_when between exec__last_exec and now()
                    and
                      p.item_id = i.item_id 
    LOOP
      -- update publish status
      update cr_items
	set publish_status = ''live''
      where
	item_id = item_rec.item_id;
      exec__items_released := exec__items_released + 1;
    end loop;

    for item_rec in select 
                      p.item_id, live_revision
                    from
                      cr_release_periods p, cr_items i
                    where
                      end_when between exec__last_exec and now()
                    and
                      p.item_id = i.item_id
    LOOP
      -- update publish status
      update cr_items
	set publish_status = ''expired''
      where
	item_id = item_rec.item_id;

      exec__items_expired := exec__items_expired + 1;

    end loop;

  -- exception

  --   when others then
  --    err_num := SQLCODE;
  --    err_msg := substr(SQLERRM, 1, 500);
  -- end;

  -- keep a record of the update

  insert into cr_scheduled_release_log (
    items_released, items_expired, err_num, err_msg
  ) values (
    exec__items_released, exec__items_expired, exec__err_num, 
    coalesce(exec__err_msg,'''')
  );

  -- Reset the last time of execution to start of processing
  update cr_scheduled_release_job set last_exec = exec__this_exec;

  return 0;
end;' language 'plpgsql';



-- show errors

-- initialize the scheduled publication job
    
create function inline_1 ()
returns integer as '
declare
  v_job_id      integer;
  interval      integer default 15;
begin

  select job into v_job_id from user_jobs 
   where what = ''cr_scheduled_release_exec;'';

  if NOT FOUND then
    raise NOTICE ''
      Submitting job to process scheduled updates to live content...'';

    dbms_job__submit(
      job        =>  v_job_id, 
      what       =>  ''cr_scheduled_release_exec;'',
      next_date  =>  now(),
      interval   =>  ''now() + '' || (interval/24/60)
    ); 

    update cr_scheduled_release_job set job_id = v_job_id;
  else

    dbms_job__change(
      job        =>  v_job_id, 
      what       =>  ''cr_scheduled_release_exec;'',
      next_date  =>  now(),
      interval   =>  ''now() + '' || (interval/24/60)
    );

  end if;

  return 0;
end;' language 'plpgsql';

-- select inline_1 ();

drop function inline_1 ();


-- show errors