drop sequence class_sequence;
create sequence class_sequence;
drop sequence class_sequence_backup;
create sequence class_sequence_backup;
drop table class;
create table class (
class_id int4 NOT NULL PRIMARY KEY DEFAULT nextval('class_sequence'),
    date_updated  timestamp NOT NULL default CURRENT_TIMESTAMP,
    date_created  timestamp NOT NULL default CURRENT_TIMESTAMP,
    active int2 CHECK (active in (0,1)) DEFAULT 0,
time text not null default '' ,
title text not null default '' ,
description text not null default '' ,
users_id int4 not null default 0  REFERENCES users
       ON DELETE NO ACTION
       ON UPDATE CASCADE 
);drop table class_backup;
create table class_backup (
backup_id int4 NOT NULL UNIQUE DEFAULT nextval('class_sequence_backup'), 
    class_id int4 NOT NULL DEFAULT 0,
    date_updated  timestamp NOT NULL default CURRENT_TIMESTAMP,
    date_created  timestamp NOT NULL default CURRENT_TIMESTAMP,
    active int2 CHECK (active in (0,1)) DEFAULT 0,
    time text not null default '',
title text not null default '',
description text not null default '',
users_id int4 not null default 0, error_code text NOT NULL DEFAULT ''
);
drop view class_active;
create view class_active as select * from class
        where active = 1;
drop view class_deleted;
create view class_deleted as select * from class
        where active = 0;
drop view class_backup_ids;
create view class_backup_ids as 
           select distinct class_id from class_backup;
drop view class_purged;
create view class_purged as
   select * from class_backup where oid = ANY (
     select max(oid) from class_backup where class_id = ANY
        (
        select distinct class_id from class_backup
          where class_backup.error_code = 'purge'
           and NOT class_id = ANY (select class_id from class)
        )
        group by class_id
     )
    ;
---              Generic Functions for Perl/Postgresql version 1.0

---                       Copyright 2001, Mark Nielsen
---                            All rights reserved.
---    This Copyright notice was copied and modified from the Perl 
---    Copyright notice. 
---    This program is free software; you can redistribute it and/or modify
---    it under the terms of either:

---        a) the GNU General Public License as published by the Free
---        Software Foundation; either version 1, or (at your option) any
---        later version, or

---        b) the "Artistic License" which comes with this Kit.

---    This program is distributed in the hope that it will be useful,
---    but WITHOUT ANY WARRANTY; without even the implied warranty of
---    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See either
---    the GNU General Public License or the Artistic License for more details.

---    You should have received a copy of the Artistic License with this
---    Kit, in the file named "Artistic".  If not, I'll be glad to provide one.

---    You should also have received a copy of the GNU General Public License
---   along with this program in the file named "Copying". If not, write to the 
---   Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 
---    02111-1307, USA or visit their web page on the internet at
---    https://www.gnu.org/copyleft/gpl.html.

-- create a method to unpurge just one item.  
-- create a method to purge one item. 
--  \i /tmp/Test/sample/class.table
---------------------------------------------------------------------

drop function sql_class_insert ();
CREATE FUNCTION sql_class_insert () RETURNS int4 AS '
DECLARE
    record1 record;  oid1 int4; id int4 :=0; record_backup RECORD;
BEGIN
   insert into class (date_updated, date_created, active)
        values (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP, 1);
     -- Get the unique oid of the row just inserted. 
   GET DIAGNOSTICS oid1 = RESULT_OID;
     -- Get the class id. 
   FOR record1 IN SELECT class_id FROM class where oid = oid1
      LOOP
      id := record1.class_id;
   END LOOP;
   
     -- If id is NULL, insert failed or something is wrong.
   IF id is NULL THEN return (-1); END IF;
     -- It should also be greater than 0, otherwise something is wrong.
   IF id < 1 THEN return (-2); END IF;

      -- Now backup the data. 
    FOR record_backup IN SELECT * FROM class where class_id = id
       LOOP
       insert into class_backup (class_id, date_updated, date_created, 
           active, error_code) 
         values (id, record_backup.date_updated, record_backup.date_created,
            record_backup.active, ''insert'');
    END LOOP;

     -- Everything has passed, return id as class_id.
   return (id);
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------

drop function sql_class_delete (int4);
CREATE FUNCTION sql_class_delete (int4) RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD; 
    record_backup RECORD;
    return_int4 int4 :=0;

BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

     -- If we find the id, set active = 0. 
   FOR record1 IN SELECT class_id FROM class 
          where class_id = id
      LOOP
      update class set active=0, date_updated = CURRENT_TIMESTAMP
           where class_id = id;  
      GET DIAGNOSTICS return_int4 = ROW_COUNT;       
      id_exists := 1;
   END LOOP;
      
     -- If we did not find the id, abort and return -2.  
   IF id_exists = 0 THEN return (-2); END IF;

   FOR record_backup IN SELECT * FROM class where class_id = id
      LOOP
      insert into class_backup (class_id, date_updated, date_created,
          active , time, title, description, users_id ,error_code)
           values (record_backup.class_id, record_backup.date_updated, 
             record_backup.date_updated, record_backup.active
             , record_backup.time, record_backup.title, record_backup.description, record_backup.users_id , ''delete''
      );
   END LOOP;

     -- If id_exists == 0, Return error.
     -- It means it never existed. 
   IF id_exists = 0 THEN return (-1); END IF;

     -- We got this far, it must be true, return ROW_COUNT.   
   return (return_int4);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------

drop function sql_class_undelete (int4);
CREATE FUNCTION sql_class_undelete (int4) RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD; 
    record_backup RECORD;
    return_int4 int4 :=0;

BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

     -- If we find the id, set active = 1. 
   FOR record1 IN SELECT class_id FROM class 
          where class_id = id
      LOOP
      update class set active=1, date_updated = CURRENT_TIMESTAMP
           where class_id = id;  
      GET DIAGNOSTICS return_int4 = ROW_COUNT;       
      id_exists := 1;
   END LOOP;
      
     -- If we did not find the id, abort and return -2.  
   IF id_exists = 0 THEN return (-2); END IF;

   FOR record_backup IN SELECT * FROM class where class_id = id
      LOOP
      insert into class_backup (class_id, date_updated, date_created,
          active , time, title, description, users_id ,error_code)
           values (record_backup.class_id, record_backup.date_updated, 
             record_backup.date_updated, record_backup.active
             , record_backup.time, record_backup.title, record_backup.description, record_backup.users_id , ''undelete''
      );
   END LOOP;

     -- If id_exists == 0, Return error.
     -- It means it never existed. 
   IF id_exists = 0 THEN return (-1); END IF;

     -- We got this far, it must be true, return ROW_COUNT.   
   return (return_int4);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------
drop function sql_class_update (int4 , text, text, text, int4);
CREATE FUNCTION sql_class_update  (int4 , text, text, text, int4) 
  RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record_update RECORD; record_backup RECORD;
    return_int4 int4 :=0;
              var_2 text;
          var_3 text;
          var_4 text;
          var_5 int4;

BEGIN
             var_2 := clean_text($2);
         var_3 := clean_text($3);
         var_4 := clean_text($4);
         var_5 := clean_numeric($5);

     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

   FOR record_update IN SELECT class_id FROM class
         where class_id = id
      LOOP
      id_exists := 1;
   END LOOP;

   IF id_exists = 0 THEN return (-2); END IF;

   update class set date_updated = CURRENT_TIMESTAMP
      , time = var_2, title = var_3, description = var_4, users_id = var_5 
        where class_id = id;
   GET DIAGNOSTICS return_int4 = ROW_COUNT;

   FOR record_backup IN SELECT * FROM class where class_id = id
      LOOP
     insert into class_backup (class_id,
         date_updated, date_created, active
         , time, title, description, users_id, error_code)
       values (record_update.class_id, record_backup.date_updated,
         record_backup.date_updated, record_backup.active
         , record_backup.time, record_backup.title, record_backup.description, record_backup.users_id, ''update''
      );
   END LOOP;

     -- We got this far, it must be true, return ROW_COUNT.   
   return (return_int4);
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------

drop function sql_class_copy (int4);
CREATE FUNCTION sql_class_copy (int4) 
  RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD; record2 RECORD; record3 RECORD;    
    return_int4 int4 := 0;
    id_new int4 := 0;
    class_new int4 :=0;
BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

   FOR record1 IN SELECT class_id FROM class where class_id = id
      LOOP
      id_exists := 1;
   END LOOP;
   IF id_exists = 0 THEN return (-2); END IF;

     --- Get the new id
   FOR record1 IN SELECT sql_class_insert() as class_insert
      LOOP
      class_new := record1.class_insert;
   END LOOP;
     -- If the class_new is not greater than 0, return error.
   IF class_new < 1 THEN return -3; END IF;

   FOR record2 IN SELECT * FROM class where class_id = id
      LOOP

     FOR record1 IN SELECT sql_class_update(class_new , clean_text(record2.time), clean_text(record2.title), clean_text(record2.description), clean_text(record2.users_id))
        as class_insert
      LOOP
        -- execute some arbitrary command just to get it to pass. 
      id_exists := 1;
     END LOOP;
   END LOOP;

     -- We got this far, it must be true, return new id.   
   return (class_new);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------
drop function sql_class_purge ();
CREATE FUNCTION sql_class_purge () RETURNS int4 AS '
DECLARE
    record_backup RECORD; oid1 int4 := 0;
    return_int4 int4 :=0;
    deleted int4 := 0;
    delete_count int4 :=0;
    delete_id int4;

BEGIN 

     -- Now delete one by one. 
   FOR record_backup IN SELECT * FROM class where active = 0
      LOOP
         -- Record the id we want to delete. 
      delete_id = record_backup.class_id;

      insert into class_backup (class_id, date_updated, date_created,
          active , time, title, description, users_id ,error_code)
           values (record_backup.class_id, record_backup.date_updated, 
             record_backup.date_updated, record_backup.active
             , record_backup.time, record_backup.title, record_backup.description, record_backup.users_id , ''purge''
          );

        -- Get the unique oid of the row just inserted. 
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-2); END IF;
        -- Now delete this from the main table.   
      delete from class where class_id = delete_id;

        -- Get row count of row just deleted, should be 1. 
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- If deleted less than 1, return -3
      IF deleted < 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- We got this far, it must be true, return the number of ones we had.  
   return (delete_count);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------
drop function sql_class_purgeone (int4);
CREATE FUNCTION sql_class_purgeone (int4) RETURNS int4 AS '
DECLARE
    record_backup RECORD; oid1 int4 := 0;
    record1 RECORD;
    return_int4 int4 :=0;
    deleted int4 := 0;
    delete_count int4 :=0;
    delete_id int4;
    purged_no int4 := 0;

BEGIN

    delete_id := $1;
        -- If purged_id less than 1, return -4
    IF delete_id < 1 THEN return (-4); END IF;

   FOR record1 IN SELECT * FROM class 
      where active = 0 and class_id = delete_id 
      LOOP
      purged_no := purged_no + 1;
   END LOOP;

        -- If purged_no less than 1, return -1
   IF purged_no < 1 THEN return (-1); END IF;

     -- Now delete one by one.
   FOR record_backup IN SELECT * FROM class where class_id = delete_id
      LOOP

      insert into class_backup (class_id, date_updated, date_created,
          active , time, title, description, users_id ,error_code)
           values (record_backup.class_id, record_backup.date_updated,
             record_backup.date_updated, record_backup.active
             , record_backup.time, record_backup.title, record_backup.description, record_backup.users_id , ''purgeone''
          );

        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;
        -- Now delete this from the main table.
      delete from class where class_id = delete_id;

        -- Get row count of row just deleted, should be 1.
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- If deleted less than 1, return -3
      IF deleted < 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- We got this far, it must be true, return the number of ones we had.
   return (delete_count);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------------
drop function sql_class_unpurge ();
CREATE FUNCTION sql_class_unpurge () RETURNS int2 AS '
DECLARE
    record1 RECORD;
    record2 RECORD; 
    record_backup RECORD;
    purged_id int4 := 0;
    purge_count int4 :=0;
    timestamp1 timestamp;
    purged_no int4 := 0;
    oid1 int4 := 0;
    oid_found int4 := 0;
    highest_oid int4 := 0;

BEGIN

     -- Now get the unique ids that were purged. 
   FOR record1 IN select distinct class_id from class_backup 
       where class_backup.error_code = ''purge''
          and NOT class_id = ANY (select class_id from class)
      LOOP

      purged_id := record1.class_id;
      timestamp1 := CURRENT_TIMESTAMP;
      purged_no := purged_no + 1;
      oid_found := 0;
      highest_oid := 0;

        -- Now we have the unique id, find its latest date. 

      FOR record2 IN select max(oid) from class_backup 
          where class_id = purged_id and error_code = ''purge''
        LOOP 
          -- record we got the date and also record the highest date.
        oid_found := 1; 
        highest_oid := record2.max;
      END LOOP;
 
         -- If the oid_found is 0, return error. 
      IF oid_found = 0 THEN return (-3); END IF;

        -- Now we have the latest date, get the values and insert them. 
      FOR record_backup IN select * from class_backup 
          where oid = highest_oid
        LOOP 

      insert into class_backup (class_id, date_updated, date_created,
          active , time, title, description, users_id ,error_code)
           values (purged_id, record_backup.date_updated, 
             timestamp1, record_backup.active
             , record_backup.time, record_backup.title, record_backup.description, record_backup.users_id , ''unpurge''
          );

        -- Get the unique oid of the row just inserted. 
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-1); END IF;

      insert into class (class_id, date_updated, date_created,
          active , time, title, description, users_id)
           values (purged_id, timestamp1,
             timestamp1, record_backup.active
             , record_backup.time, record_backup.title, record_backup.description, record_backup.users_id );
        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;

      END LOOP;

   END LOOP;

     -- We got this far, it must be true, return how many were affected.  
   return (purged_no);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------
drop function sql_class_unpurgeone (int4);
CREATE FUNCTION sql_class_unpurgeone (int4) RETURNS int2 AS '
DECLARE
    record_id int4;
    record1 RECORD;
    record2 RECORD;
    record_backup RECORD;
    return_int4 int4 :=0;
    purged_id int4 := 0;
    purge_count int4 :=0;
    timestamp1 timestamp;
    purged_no int4 := 0;
    oid1 int4 := 0;
    oid_found int4 := 0;
    highest_oid int4 := 0;

BEGIN

      purged_id := $1;
        -- If purged_id less than 1, return -1
      IF purged_id < 1 THEN return (-1); END IF;
        --- Get the current timestamp.
      timestamp1 := CURRENT_TIMESTAMP;

   FOR record1 IN select distinct class_id from class_backup
       where class_backup.error_code = ''purge''
          and NOT class_id = ANY (select class_id from class)
          and class_id = purged_id
      LOOP
      purged_no := purged_no + 1;

   END LOOP;

        -- If purged_no less than 1, return -1
   IF purged_no < 1 THEN return (-3); END IF;

        -- Now find the highest oid.  
   FOR record2 IN select max(oid) from class_backup
          where class_id = purged_id and error_code = ''purge''
        LOOP
          -- record we got the date and also record the highest date.
        oid_found := 1;
        highest_oid := record2.max;
    END LOOP;

         -- If the oid_found is 0, return error.
    IF oid_found = 0 THEN return (-4); END IF;

        -- Now get the data and restore it. 
    FOR record_backup IN select * from class_backup 
          where oid  = highest_oid
        LOOP 
        -- Insert into backup that it was unpurged. 
      insert into class_backup (class_id, date_updated, date_created,
          active , time, title, description, users_id ,error_code)
           values (purged_id, timestamp1, 
             record_backup.date_created, record_backup.active
             , record_backup.time, record_backup.title, record_backup.description, record_backup.users_id , ''unpurgeone''
          );

        -- Get the unique oid of the row just inserted. 
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-1); END IF;
        -- Insert into live table. 
      insert into class (class_id, date_updated, date_created,
          active , time, title, description, users_id)
           values (record_backup.class_id, timestamp1,
             record_backup.date_updated, record_backup.active
             , record_backup.time, record_backup.title, record_backup.description, record_backup.users_id );
        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;

      END LOOP;

     -- We got this far, it must be true, return how many were affected (1).  
   return (purged_no);
END;
' LANGUAGE 'plpgsql';

insert into class (class_id, date_updated, date_created, active)
    values (0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0);
insert into class_backup (backup_id, class_id, 
     date_updated, date_created, active, error_code)
    values (0, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, 'table creation');




drop function clean_text (text);
CREATE FUNCTION  clean_text (text) RETURNS text AS '
  my $Text = shift;
    # Get rid of whitespace in front. 
  $Text =~ s/^\\s+//;
    # Get rid of whitespace at end. 
  $Text =~ s/\\s+$//;
    # Get rid of anything not text.
  $Text =~ s/[^ a-z0-9\\/\\`\\~\\!\\@\\#\\$\\%\\^\\&\\*\\(\\)\\-\\_\\=\\+\\\\\\|\[\\{\\]\\}\\;\\:\\''\\"\\,\\<\\.\\>\\?\\t\\n]//gi;
    # Replace all multiple whitespace with one space. 
  $Text =~ s/\\s+/ /g;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up. 
select clean_text ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_alpha (text);
CREATE FUNCTION  clean_alpha (text) RETURNS text AS '
  my $Text = shift;
  $Text =~ s/[^a-z0-9_]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up. 
select clean_alpha ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (text);
CREATE FUNCTION  clean_numeric (text) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_numeric ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (int4);
CREATE FUNCTION  clean_numeric (int4) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_numeric (11111);



select sql_class_insert();
select sql_class_update(1,'2 hours', 'Introduction to Linux Part 1','This is a description of this class. Not much here so far.', 1);

select sql_class_insert();
select sql_class_update(2,'3 hours', 'Introduction to Linux Part 2','This is a description of this class. Not much here so far.', 2);



vacuum;