Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-schedule.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-schedule.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/acs-content-repository/sql/postgresql/content-schedule.sql	26 Mar 2001 05:36:37 -0000	1.1
@@ -0,0 +1,160 @@
+-- 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.1 2001/03/26 05:36:37 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);
+  exec__item_rec              record;
+begin
+
+    select exec__last_exec into 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, 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';
+
+select inline_0 ();
+
+drop function inline_0 ();
+
+
+-- 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
+    dbms_output.put_line('
+      Submitting job to process scheduled updates to live content...');
+
+    dbms_job.submit(
+      job        =>  v_job_id, 
+      what       =>  'cr_scheduled_release_exec;',
+      next_date  =>  sysdate,
+      interval   =>  'sysdate + ' || (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  =>  sysdate,
+      interval   =>  'sysdate + ' || (interval/24/60)
+    );
+
+  end if;
+
+  return 0;
+end;' language 'plpgsql';
+
+select inline_1 ();
+
+drop function inline_1 ();
+
+
+-- show errors
+
+
+
+
+
+
+