Index: openacs-4/packages/cronjob/sql/oracle/cronjob-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/cronjob/sql/oracle/cronjob-create.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/cronjob/sql/oracle/cronjob-create.sql	4 Nov 2001 18:07:08 -0000	1.1
@@ -0,0 +1,300 @@
+--
+-- Cronjob in sql
+-- @author tom jackson <tom@zmbh.com>
+-- @creation-date 22 Sept 2001
+-- @cvs-id $Id: cronjob-create.sql,v 1.1 2001/11/04 18:07:08 tomj Exp $
+--
+
+--drop table cronjobs;
+create table cronjobs (
+	cronjob_id integer not null
+   constraint cj_cronjob_id_fk references acs_objects(object_id)
+   constraint cj_cronjob_id_pk primary key,
+  user_id integer not null
+   constraint cj_user_id_fk references users,
+	description varchar2(100) not null,
+  approved_p char(1) default 'f' not null
+   constraint cj_approved_p_ck check (approved_p in ('f','t')),
+  disabled_p char(1) default 'f' not null
+   constraint cj_disabled_p_ck check (disabled_p in ('f','t')),
+  minute char(2)  default '0' not null,
+  hr  char(2)  default '0' not null,
+  mon char(2)  default '0' not null,
+  day char(2)  default '0' not null,
+  dayofweek char(2)  default '0' not null,
+  run_sql varchar2(4000),
+  run_tcl varchar2(4000),
+  email   varchar2(255)
+);
+
+begin 
+    acs_object_type.create_type ( 
+  supertype => 'acs_object', 
+  object_type => 'cronjob', 
+  pretty_name => 'CronJob', 
+  pretty_plural => 'CronJobs', 
+  table_name => 'cronjobs',  
+  id_column => 'cronjob_id' 
+    ); 
+end; 
+/ 
+show errors; 
+
+
+
+
+create or replace package cronjob
+as
+    function cronjob_p (
+      cronjob_id  in cronjobs.cronjob_id%TYPE
+    ) return char;
+
+    function new ( 
+      cronjob_id  in cronjobs.cronjob_id%TYPE default null,
+      user_id  in cronjobs.user_id%TYPE,
+      description  in cronjobs.description%TYPE,
+      approved_p  in cronjobs.approved_p%TYPE default 'f',
+      disabled_p  in cronjobs.disabled_p%TYPE,
+      minute  in cronjobs.minute%TYPE default '0',
+      hr  in cronjobs.hr%TYPE default '0',
+      mon  in cronjobs.mon%TYPE default '0',
+      day  in cronjobs.day%TYPE default '0',
+      dayofweek  in cronjobs.dayofweek%TYPE default '0',
+      run_sql  in cronjobs.run_sql%TYPE default null,
+      run_tcl  in cronjobs.run_tcl%TYPE default null,
+      email  in cronjobs.email%TYPE default null,
+      object_type  in acs_objects.object_type%TYPE default 'cronjob',
+      creation_date  in acs_objects.creation_date%TYPE default sysdate,
+      creation_user  in acs_objects.creation_user%TYPE default null,
+      creation_ip  in acs_objects.creation_ip%TYPE default null,
+      context_id  in acs_objects.context_id%TYPE default null
+    ) return cronjobs.cronjob_id%TYPE;
+
+    procedure delete (
+      cronjob_id  in cronjobs.cronjob_id%TYPE
+    );
+    procedure set_attrs ( 
+      cronjob_id in cronjobs.cronjob_id%TYPE,
+      user_id in cronjobs.user_id%TYPE default null,
+      description in cronjobs.description%TYPE default null,
+      approved_p in cronjobs.approved_p%TYPE default null,
+      disabled_p in cronjobs.disabled_p%TYPE default null,
+      minute in cronjobs.minute%TYPE default null,
+      hr in cronjobs.hr%TYPE default null,
+      mon in cronjobs.mon%TYPE default null,
+      day in cronjobs.day%TYPE default null,
+      dayofweek in cronjobs.dayofweek%TYPE default null,
+      run_sql in cronjobs.run_sql%TYPE default null,
+      run_tcl in cronjobs.run_tcl%TYPE default null,
+      email in cronjobs.email%TYPE default null
+    );
+    procedure reset_attr ( 
+      cronjob_id in cronjobs.cronjob_id%TYPE,
+      column_name in varchar
+    );
+end cronjob;
+/
+show errors;
+
+create or replace package body cronjob
+as
+    function cronjob_p (
+      cronjob_id  in cronjobs.cronjob_id%TYPE
+    ) return char
+    is
+    -- declare vars here
+      v_check_cronjob_id integer;
+    begin
+      select count(cronjob_id) into v_check_cronjob_id
+          from cronjobs
+          where cronjob_id = cronjob_p.cronjob_id;
+      if v_check_cronjob_id = 1 then
+          return 't';
+      else
+          return 'f';
+      end if;
+    end cronjob_p;
+
+    -- Context ID May need adjustment to reflect security/access model.
+    function new ( 
+      cronjob_id  in cronjobs.cronjob_id%TYPE default null,
+      user_id  in cronjobs.user_id%TYPE,
+      description  in cronjobs.description%TYPE,
+      approved_p  in cronjobs.approved_p%TYPE default 'f',
+      disabled_p  in cronjobs.disabled_p%TYPE,
+      minute  in cronjobs.minute%TYPE default '0',
+      hr  in cronjobs.hr%TYPE default '0',
+      mon  in cronjobs.mon%TYPE default '0',
+      day  in cronjobs.day%TYPE default '0',
+      dayofweek  in cronjobs.dayofweek%TYPE default '0',
+      run_sql  in cronjobs.run_sql%TYPE default null,
+      run_tcl  in cronjobs.run_tcl%TYPE default null,
+      email  in cronjobs.email%TYPE default null,
+      object_type  in acs_objects.object_type%TYPE default 'cronjob',
+      creation_date  in acs_objects.creation_date%TYPE default sysdate,
+      creation_user  in acs_objects.creation_user%TYPE default null,
+      creation_ip  in acs_objects.creation_ip%TYPE default null,
+      context_id  in acs_objects.context_id%TYPE default null
+    ) return cronjobs.cronjob_id%TYPE
+    is
+      v_cronjob_id integer;
+    begin
+      v_cronjob_id := acs_object.new (
+      object_id => cronjob_id,
+      object_type => object_type,
+      creation_date => creation_date,
+      creation_user => creation_user,
+      creation_ip => creation_ip,
+      context_id => context_id
+    );
+
+    insert into cronjobs
+      ( cronjob_id, user_id, description, approved_p, disabled_p, minute, hr, mon, day, dayofweek, run_sql, run_tcl, email )
+    values
+      ( v_cronjob_id, user_id, description, approved_p, disabled_p, minute, hr, mon, day, dayofweek, run_sql, run_tcl, email );
+
+    return v_cronjob_id;
+  end new;
+
+  procedure delete (
+    cronjob_id  in cronjobs.cronjob_id%TYPE
+  )
+  is
+  begin
+    if cronjob_p(cronjob.delete.cronjob_id) = 'f' then
+       return;
+    end if;
+
+    delete from cronjobs
+    where cronjob_id = cronjob.delete.cronjob_id;
+
+    acs_object.delete(cronjob_id);
+  end delete;
+
+  procedure set_attrs ( 
+     cronjob_id in cronjobs.cronjob_id%TYPE,
+     user_id in cronjobs.user_id%TYPE default null,
+     description in cronjobs.description%TYPE default null,
+     approved_p in cronjobs.approved_p%TYPE default null,
+     disabled_p in cronjobs.disabled_p%TYPE default null,
+     minute in cronjobs.minute%TYPE default null,
+     hr in cronjobs.hr%TYPE default null,
+     mon in cronjobs.mon%TYPE default null,
+     day in cronjobs.day%TYPE default null,
+     dayofweek in cronjobs.dayofweek%TYPE default null,
+     run_sql in cronjobs.run_sql%TYPE default null,
+     run_tcl in cronjobs.run_tcl%TYPE default null,
+     email in cronjobs.email%TYPE default null
+  )
+  is
+    -- declared vars here
+  begin
+    if cronjob_p(cronjob.set_attrs.cronjob_id) = 'f' then
+      return;
+    end if;
+
+  -- modify parts that are not null
+
+    if user_id is not null then
+        update cronjobs set user_id = set_attrs.user_id
+          where cronjob_id = set_attrs.cronjob_id;
+    end if;
+    if description is not null then
+        update cronjobs set description = set_attrs.description
+          where cronjob_id = set_attrs.cronjob_id;
+    end if;
+    if approved_p is not null then
+        update cronjobs set approved_p = set_attrs.approved_p
+          where cronjob_id = set_attrs.cronjob_id;
+    end if;
+    if disabled_p is not null then
+        update cronjobs set disabled_p = set_attrs.disabled_p
+          where cronjob_id = set_attrs.cronjob_id;
+    end if;
+    if minute is not null then
+        update cronjobs set minute = set_attrs.minute
+          where cronjob_id = set_attrs.cronjob_id;
+    end if;
+    if hr is not null then
+        update cronjobs set hr = set_attrs.hr
+          where cronjob_id = set_attrs.cronjob_id;
+    end if;
+    if mon is not null then
+        update cronjobs set mon = set_attrs.mon
+          where cronjob_id = set_attrs.cronjob_id;
+    end if;
+    if day is not null then
+        update cronjobs set day = set_attrs.day
+          where cronjob_id = set_attrs.cronjob_id;
+    end if;
+    if dayofweek is not null then
+        update cronjobs set dayofweek = set_attrs.dayofweek
+          where cronjob_id = set_attrs.cronjob_id;
+    end if;
+    if run_sql is not null then
+        update cronjobs set run_sql = set_attrs.run_sql
+          where cronjob_id = set_attrs.cronjob_id;
+    end if;
+    if run_tcl is not null then
+        update cronjobs set run_tcl = set_attrs.run_tcl
+          where cronjob_id = set_attrs.cronjob_id;
+    end if;
+    if email is not null then
+        update cronjobs set email = set_attrs.email
+          where cronjob_id = set_attrs.cronjob_id;
+    end if;
+
+  end set_attrs;
+  procedure reset_attr ( 
+    cronjob_id in cronjobs.cronjob_id%TYPE,
+    column_name in varchar
+  )
+  is 
+    -- declared vars here
+  begin
+    if cronjob_p(cronjob.reset_attr.cronjob_id) = 'f' then
+      return;
+    end if;
+  
+    if column_name = 'approved_p' then
+       update cronjobs set approved_p = 'f'
+       where cronjob_id = reset_attr.cronjob_id;
+    end if;
+    if column_name = 'minute' then
+       update cronjobs set minute = '0'
+       where cronjob_id = reset_attr.cronjob_id;
+    end if;
+    if column_name = 'hr' then
+       update cronjobs set hr = '0'
+       where cronjob_id = reset_attr.cronjob_id;
+    end if;
+    if column_name = 'mon' then
+       update cronjobs set mon = '0'
+       where cronjob_id = reset_attr.cronjob_id;
+    end if;
+    if column_name = 'day' then
+       update cronjobs set day = '0'
+       where cronjob_id = reset_attr.cronjob_id;
+    end if;
+    if column_name = 'dayofweek' then
+       update cronjobs set dayofweek = '0'
+       where cronjob_id = reset_attr.cronjob_id;
+    end if;
+    if column_name = 'run_sql' then
+       update cronjobs set run_sql = NULL
+       where cronjob_id = reset_attr.cronjob_id;
+    end if;
+    if column_name = 'run_tcl' then
+       update cronjobs set run_tcl = NULL
+       where cronjob_id = reset_attr.cronjob_id;
+    end if;
+    if column_name = 'email' then
+       update cronjobs set email = NULL
+       where cronjob_id = reset_attr.cronjob_id;
+    end if;
+
+  end reset_attr;
+
+end cronjob;
+/
+show errors;
Index: openacs-4/packages/cronjob/sql/postgresql/cronjob-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/cronjob/sql/postgresql/cronjob-create.sql,v
diff -u
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/cronjob/sql/postgresql/cronjob-create.sql	4 Nov 2001 18:07:08 -0000	1.1
@@ -0,0 +1,273 @@
+--
+-- Cronjob in sql
+-- @author tom jackson <tom@zmbh.com>
+-- @creation-date 22 Sept 2001
+-- @cvs-id $Id: cronjob-create.sql,v 1.1 2001/11/04 18:07:08 tomj Exp $
+--
+
+-- You might comment out this line
+drop table cronjobs;
+
+create table cronjobs (
+	cronjob_id integer not null
+   constraint cj_cronjob_id_fk references acs_objects(object_id)
+   constraint cj_cronjob_id_pk primary key,
+  user_id integer not null
+   constraint cj_user_id_fk references users,
+	description varchar(100) not null,
+  approved_p char(1) default 'f' not null
+   constraint cj_approved_p_ck check (approved_p in ('f','t')),
+  disabled_p char(1) default 'f' not null
+   constraint cj_disabled_p_ck check (disabled_p in ('f','t')),
+  minute char(2)  default '0' not null,
+  hr  char(2)  default '0' not null,
+  mon char(2)  default '0' not null,
+  day char(2)  default '0' not null,
+  dayofweek char(2)  default '0' not null,
+  run_sql text,
+  run_tcl text,
+  email   varchar(255)
+);
+ 
+create function inline_0 () 
+returns integer as '  
+begin 
+    PERFORM acs_object_type__create_type (  
+  ''cronjob'', -- object_type  
+  ''CronJob'', -- pretty_name 
+  ''CronJobs'', -- pretty_plural 
+  ''acs_object'',   -- supertype 
+  ''cronjobs'', -- table_name 
+  ''cronjob_id'', -- id_column 
+  null, -- package_name 
+  ''f'', -- abstract_p 
+  null, -- type_extension_table 
+  null -- name_method 
+  ); 
+
+     return 0;  
+end;' language 'plpgsql'; 
+
+select inline_0 (); 
+ 
+drop function inline_0 ();
+
+drop function  cronjob__cronjob_p (integer);
+
+create function cronjob__cronjob_p (integer)
+returns boolean
+as '
+declare
+ p_cronjob_id alias for $1;
+ v_check_cronjob_id integer;
+begin
+ select count(cronjob_id) into v_check_cronjob_id
+  from cronjobs
+  where cronjob_id = p_cronjob_id;
+ if v_check_cronjob_id = 1 
+ then 
+   return true;
+ else 
+   return false;
+ end if;
+end;' language 'plpgsql';
+
+drop function cronjob__new (integer, integer, varchar, char, char, char(2), char(2), char(2), char(2), char(2), text, text, varchar, integer, varchar, integer);
+
+create function cronjob__new (integer, integer, varchar, char, char, char(2), char(2), char(2), char(2), char(2), text, text, varchar, integer, varchar, integer)
+returns integer as '
+declare 
+  p_cronjob_id  alias for $1; -- default null 
+  p_user_id  alias for $2; -- default null 
+  p_description  alias for $3; -- default null 
+  p_approved_p  alias for $4; -- default ''f'' 
+  p_disabled_p  alias for $5; -- default ''f''
+  p_minute  alias for $6; -- default ''0'' 
+  p_hr  alias for $7; -- default ''0'' 
+  p_mon  alias for $8; -- default ''0'' 
+  p_day  alias for $9; -- default ''0'' 
+  p_dayofweek  alias for $10; -- default ''0'' 
+  p_run_sql  alias for $11; -- default null 
+  p_run_tcl  alias for $12; -- default null 
+  p_email  alias for $13; -- default null
+  p_creation_user  alias for $14; -- default null 
+  p_creation_ip  alias for $15; -- default null 
+  p_context_id  alias for $16; -- default null
+  v_cronjob_id cronjobs.cronjob_id%TYPE;
+  v_object_type  acs_objects.object_type%TYPE; -- default ''cronjob'' 
+  v_creation_date  acs_objects.creation_date%TYPE; -- default now() 
+begin
+  v_cronjob_id := acs_object__new ( 
+    p_cronjob_id, 
+    ''cronjob'',
+    now(),
+    p_creation_user,
+    p_creation_ip,
+    p_context_id
+  );  
+
+  insert into cronjobs
+    (cronjob_id, user_id, description, approved_p, disabled_p, minute, hr, mon, day, dayofweek, run_sql, run_tcl, email)
+  values
+    (v_cronjob_id, p_user_id, p_description, p_approved_p, p_disabled_p, p_minute, p_hr, p_mon, p_day, p_dayofweek, p_run_sql, p_run_tcl, p_email);
+
+  return v_cronjob_id;
+
+end;' language 'plpgsql';
+
+drop function cronjob__delete (integer);
+
+create function cronjob__delete (integer)
+returns integer as '
+declare
+ p_cronjob_id    alias for $1;
+ v_return integer := 0; 
+begin
+   if not cronjob__cronjob_p(p_cronjob_id)
+   then
+    return v_return;
+   end if;
+   delete from acs_permissions
+     where object_id = p_cronjob_id;
+
+   delete from cronjobs
+     where cronjob_id = p_cronjob_id;
+
+   raise NOTICE ''Deleting cronjob...'';
+
+   return v_return;
+
+end;' language 'plpgsql';
+
+drop function cronjob__set_attrs (integer, integer, varchar, char, char, char(2), char(2), char(2), char(2), char(2), text, text, varchar);
+
+create function cronjob__set_attrs (integer, integer, varchar, char, char, char(2), char(2), char(2), char(2), char(2), text, text, varchar)
+returns integer 
+as '
+declare 
+  p_cronjob_id alias for $1;
+  p_user_id alias for $2; -- default null
+  p_description alias for $3; -- default null
+  p_approved_p alias for $4; -- default null
+  p_disabled_p alias for $5; -- default null
+  p_minute alias for $6; -- default null
+  p_hr alias for $7; -- default null
+  p_mon alias for $8; -- default null
+  p_day alias for $9; -- default null
+  p_dayofweek alias for $10; -- default null
+  p_run_sql alias for $11; -- default null
+  p_run_tcl alias for $12; -- default null
+  p_email alias for $13; -- default null
+  v_return integer := 0;
+begin
+  if p_user_id is not null
+  then
+   update cronjobs set user_id = p_user_id 
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_description is not null
+  then
+   update cronjobs set description = p_description 
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_approved_p is not null
+  then
+   update cronjobs set approved_p = p_approved_p 
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_disabled_p is not null
+  then
+   update cronjobs set disabled_p = p_disabled_p 
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_minute is not null
+  then
+   update cronjobs set minute = p_minute 
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_hr is not null
+  then
+   update cronjobs set hr = p_hr 
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_mon is not null
+  then
+   update cronjobs set mon = p_mon 
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_day is not null
+  then
+   update cronjobs set day = p_day 
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_dayofweek is not null
+  then
+   update cronjobs set dayofweek = p_dayofweek 
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_run_sql is not null
+  then
+   update cronjobs set run_sql = p_run_sql 
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_run_tcl is not null
+  then
+   update cronjobs set run_tcl = p_run_tcl 
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_email is not null
+  then
+   update cronjobs set email = p_email 
+    where cronjob_id = p_cronjob_id;
+  end if;
+  return v_return;
+end;' language 'plpgsql';
+
+drop function cronjob__reset_attr (integer,varchar);
+
+create function cronjob__reset_attr (integer,varchar)
+returns integer 
+as '
+declare 
+  p_cronjob_id alias for $1;
+  p_column_name  alias for $2;
+  v_return integer := 0;
+begin
+  if p_column_name = ''approved_p''
+  then
+   update cronjobs set approved_p = ''f''
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_column_name = ''disabled_p''
+  then
+   update cronjobs set disabled_p = ''f''
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_column_name = ''minute''
+  then
+   update cronjobs set minute = ''0''
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_column_name = ''hr''
+  then
+   update cronjobs set hr = ''0''
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_column_name = ''mon''
+  then
+   update cronjobs set mon = ''0''
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_column_name = ''day''
+  then
+   update cronjobs set day = ''0''
+    where cronjob_id = p_cronjob_id;
+  end if;
+  if p_column_name = ''dayofweek''
+  then
+   update cronjobs set dayofweek = ''0''
+    where cronjob_id = p_cronjob_id;
+  end if;
+  return v_return;
+end;' language 'plpgsql';
+