/*                                                                        
** This report will generate the code necessary to create             
** a rule and procedure to write records to a history                   
** table for a table.  The history table will also be created.
**                                                                      
** The default names are:                                               
**                                                                      
**  history table name = table_name + '_h'                             
**  procedure name     = 'p_' + table_name                             
**  rule_name          = 'r_' + table_name  + '_d' or '_c'
**                                                                        
** The rule and procedure names cannot be longer than 24 characters.
** With the "r_" or "p_" in the beginning, and the "_c" or "_d"
** at the end, the maximum table name length is 20 characters.
**
** If any of these conflict with existing rules, procedures               
** or table names, please edit the created script.                        
**                                                                        
** In addition the history table has two extra columns added --    
**  edit_date_time                                                      
**  edit_usr_id                                                         
** these track the date & time as well as the user making changes to the  
** master table.                                                         
**                                                                       
** The 'WHERE' clause on the rule use ALL columns for the rule           
** firing.  This is probably inappropriate, since date/time stamps      
** (perhaps from another rule & procedure) should not trigger            
** this rule.  Edit as appropriate for your case.                                    
**                                                                     
**  Designed by:  Allan R Reid                                    
**		  Office of Surface Mining
**		  PO BOX 25065
**		  Denver, CO 90225-0065
**		  reid@osmre.gov
**
**         Date:  10/28/1999                                        
**                                                                     
*/                                                                      

.NAME bhs_and_table

.DECLARE 
   a_counter = smallint,
   b_counter = smallint,
   c_counter = smallint,
   d_counter = smallint,
   e_counter = smallint,
   f_counter = smallint,
   g_counter = smallint,
   short_name = varchar(20) not null,
   table_name = varchar(50) not null with prompt "Table Name on which to create history rule/procedure"

.SETUP
  CREATE TABLE hist_temp AS
  SELECT code='A', c.column_name, c.column_datatype, c.column_length, c.column_nulls, c.column_defaults, 
         c.column_sequence, c.key_sequence
    FROM iicolumns c, iitables t
      WHERE t.table_name = '$table_name'
        AND c.table_name = t.table_name

  UNION ALL

  SELECT code='B', c.column_name, c.column_datatype, c.column_length, c.column_nulls, c.column_defaults, 
         c.column_sequence, c.key_sequence
    FROM iicolumns c, iitables t
      WHERE t.table_name = '$table_name'
        AND c.table_name = t.table_name

  UNION ALL

  SELECT code='C', c.column_name, c.column_datatype, c.column_length, c.column_nulls, c.column_defaults, 
         c.column_sequence, c.key_sequence
    FROM iicolumns c, iitables t
      WHERE t.table_name = '$table_name'
        AND c.table_name = t.table_name

  UNION ALL

  SELECT code='D', c.column_name, c.column_datatype, c.column_length, c.column_nulls, c.column_defaults, 
         c.column_sequence, c.key_sequence
    FROM iicolumns c, iitables t
      WHERE t.table_name = '$table_name'
        AND c.table_name = t.table_name

  UNION ALL

  SELECT code='E', c.column_name, c.column_datatype, c.column_length, c.column_nulls, c.column_defaults, 
         c.column_sequence, c.key_sequence
    FROM iicolumns c, iitables t
      WHERE t.table_name = '$table_name'
        AND c.table_name = t.table_name

  UNION ALL

  SELECT code='F', c.column_name, c.column_datatype, c.column_length, c.column_nulls, c.column_defaults, 
         c.column_sequence, c.key_sequence
    FROM iicolumns c, iitables t
      WHERE t.table_name = '$table_name'
        AND c.table_name = t.table_name

  UNION ALL

  SELECT code='G', c.column_name, c.column_datatype, c.column_length, c.column_nulls, c.column_defaults, 
         c.column_sequence, c.key_sequence
    FROM iicolumns c, iitables t
      WHERE t.table_name = '$table_name'
        AND c.table_name = t.table_name;

  INSERT INTO hist_temp
  SELECT code='H', c.column_name, c.column_datatype, c.column_length, c.column_nulls, c.column_defaults, 
         c.column_sequence, c.key_sequence
    FROM iicolumns c, iitables t
      WHERE t.table_name = '$table_name'
        AND c.table_name = t.table_name

.CLEANUP
  DROP TABLE hist_temp;
  COMMIT;

.DATA hist_temp

.SORT code, column_sequence, key_sequence
.NOFORMFEEDS
.PAGELENGTH 1

.HEADER report

  .PRINT "/*                                                                        " .NEWLINE
  .PRINT "** A rule and procedure to write records to a history                     " .NEWLINE
  .PRINT "** table for a table. The history table will also be created.             " .NEWLINE
  .PRINT "**                                                                        " .NEWLINE
  .PRINT "** The default names are:                                                 " .NEWLINE
  .PRINT "**                                                                        " .NEWLINE
  .PRINT "**  history table name = table_name + '_h'                                " .NEWLINE
  .PRINT "**  procedure name     = 'p_' + table_name                                " .NEWLINE
  .PRINT "**  rule_name          = 'r_' + table_name                                " .NEWLINE
  .PRINT "**                                                                        " .NEWLINE
  .PRINT "** If any of these conflict with existing rules, procedures               " .NEWLINE
  .PRINT "** or table names, please edit the created script.                        " .NEWLINE
  .PRINT "**                                                                        " .NEWLINE
  .PRINT "** In addition the history table has two extra columns added --           " .NEWLINE
  .PRINT "**  edit_date_time                                                        " .NEWLINE
  .PRINT "**  edit_usr_id                                                           " .NEWLINE
  .PRINT "** these track the date & time as well as the user making changes to the  " .NEWLINE
  .PRINT "** master table.                                                          " .NEWLINE
  .PRINT "**                                                                        " .NEWLINE
  .PRINT "** The 'WHERE' clause on the rule use ALL columns for the rule            " .NEWLINE
  .PRINT "** firing.  This is probably inappropriate, since date/time stamps        " .NEWLINE
  .PRINT "** (perhaps from another rule & procedure) should not trigger             " .NEWLINE
  .PRINT "** this rule.  Edit as appropriate for your case.                         " .NEWLINE
  .PRINT "**                                                                        " .NEWLINE
  .PRINT "**  Designed by:  Allan R Reid                                            " .NEWLINE
  .PRINT "**         Date:  10/28/1999                                              " .NEWLINE
  .PRINT "**                                                                        " .NEWLINE
  .PRINT "*/                                                                        " .NEWLINE
  .NEWLINE

  .LET short_name := left($table_name,20)

  .PRINT "DROP PROCEDURE p_", trim($table_name)
  .PRINT " \\p\\g"
  .NEWLINE 
  .PRINT "DROP RULE r_", trim(short_name), "_c"
  .PRINT " \\p\\g"
  .NEWLINE 
  .PRINT "DROP RULE r_", trim(short_name), "_d"
  .PRINT " \\p\\g"
  .NEWLINE 

.HEADER code

/*
** Create a new table to be the history table
*/

  .IF code = "A" .THEN

    .NEWLINE
    .NEWLINE
    .PRINT "CREATE TABLE ", $table_name, "_h ("
    .NEWLINE


  .ELSEIF code = "B" .THEN

/*
** Close the create statement, issue access permissions to table, and begin
** modify command
*/

    .PRINT "  ,edit_date_time" .TAB 30 .PRINT "date not null with default"
    .NEWLINE
    .PRINT "  ,edit_usr_id  char(8) " .TAB 30 .PRINT "not null with default"
    .NEWLINE 
    .PRINT ")"
    .NEWLINE
    .PRINT "\\p\\g" 
    .NEWLINE
    .NEWLINE
    .PRINT "GRANT insert, select ON ",$table_name, "_h to public \\p\\g"
    .NEWLINE
    .NEWLINE
    .PRINT "MODIFY ",$table_name, "_h to BTREE on "
    .NEWLINE

  .ELSEIF code = "C" .THEN

/*
** Close modify command and start the creation of the procedure
*/

    .PRINT "  edit_date_time"
    .NEWLINE
    .NEWLINE
    .PRINT "\\p\\g"
    .NEWLINE
    .NEWLINE
    .PRINT "CREATE PROCEDURE p_", trim($table_name)
    .NEWLINE
    .PRINT "("
    .NEWLINE

  .ELSEIF code = "D" .THEN

    .PRINT ") AS DECLARE"
    .NEWLINE
    .PRINT "  err_text varchar(500) not null;"
    .NEWLINE
    .NEWLINE 
    .PRINT "BEGIN"
    .NEWLINE
    .NEWLINE 
    .PRINT "/*                                                                        " .NEWLINE
    .PRINT "** Create historical record of data BEFORE update occurred                " .NEWLINE
    .PRINT "*/                                                                        " .NEWLINE
    .NEWLINE

    .PRINT "  INSERT INTO ", trim($table_name), "_h"
    .NEWLINE
    .PRINT "    ("
    .NEWLINE
    .PRINT "      edit_date_time, edit_usr_id,"
    .NEWLINE

  .ELSEIF code = "E" .THEN

    .PRINT "     )"
    .NEWLINE
    .PRINT "  VALUES"
    .NEWLINE
    .PRINT "     ("
    .NEWLINE
    .PRINT "      date('now'), dbmsinfo('username'),"
    .NEWLINE

  .ELSEIF code = "F" .THEN

    .PRINT "     );"
    .NEWLINE 
    .NEWLINE
    .PRINT "/*                                                                        " .NEWLINE
    .PRINT "** Check for insert error -- if there, deny update on original table      " .NEWLINE
    .PRINT "*/                                                                        " .NEWLINE
    .NEWLINE 
    .NEWLINE
    .PRINT "  IF IIerrornumber <> 0 THEN"
    .NEWLINE
    .PRINT "    err_text := 'FATAL ERROR -- unable to add history record into ''", $table_name,"_h'' table'"
    .NEWLINE
    .PRINT "              +' -- update rolled back.  Please try again.';"
    .NEWLINE
    .PRINT "    raise error 9999 :err_text;"
    .NEWLINE
    .PRINT "  ENDIF;"
    .NEWLINE
    .NEWLINE 
    .PRINT "END"
    .NEWLINE
    .PRINT "\\p\\g"
    .NEWLINE 
    .NEWLINE
    .PRINT "GRANT EXECUTE ON PROCEDURE p_", trim($table_name), " to PUBLIC  \\p\\g"
    .NEWLINE 
    .NEWLINE
    .PRINT "/*                                                                        " .NEWLINE
    .PRINT "** The table, db_rules, is a table we use to track rules being entered    " .NEWLINE
    .PRINT "** into the system.  If a rule is inadvertently dropped from the system   " .NEWLINE
    .PRINT "** tables the discrepancy would show up in a comparison between this table" .NEWLINE
    .PRINT "** and the iirules.  The table has 3 columns -- rule_name, user ID and    " .NEWLINE
    .PRINT "** date/time stamp                                                        " .NEWLINE
    .PRINT "*/                                                                        " .NEWLINE
    .NEWLINE
    .NEWLINE
    .PRINT "DELETE from db_rules"
    .NEWLINE
    .PRINT "  WHERE rule_name = 'r_",short_name,"_c'"
    .NEWLINE
    .PRINT "    AND usr_id = dbmsinfo('username')"
    .NEWLINE
    .PRINT "\\p\\g"
    .NEWLINE 
    .NEWLINE
    .PRINT "/*                                                                        " .NEWLINE
    .PRINT "** Edit the RULE to change the criteria that fires the rule -- do not     " .NEWLINE
    .PRINT "** change the list of parameters passed to the procedure, or the procedure" .NEWLINE
    .PRINT "** will fail to update the history table correctly                        " .NEWLINE
    .PRINT "*/                                                                        " .NEWLINE
    .NEWLINE 
    .NEWLINE
    .PRINT "CREATE RULE r_", trim(short_name), "_c AFTER UPDATE OF ", trim($table_name)
    .NEWLINE
    .PRINT "  WHERE "

  .ELSEIF code = "G" .THEN

    .PRINT "EXECUTE PROCEDURE p_",trim($table_name)
    .NEWLINE
    .PRINT "   ("
    .NEWLINE

  .ELSEIF code = "H" .THEN

    .PRINT "   ) "
    .NEWLINE
    .PRINT "\\p\\g"
    .NEWLINE
    .NEWLINE

    .PRINT "DELETE from db_RULES"
    .NEWLINE
    .PRINT "  WHERE rule_name = 'r_",short_name,"_d'"
    .NEWLINE
    .PRINT "    AND usr_id = dbmsinfo('username')"
    .NEWLINE
    .PRINT "\\p\\g"
    .NEWLINE 
    .NEWLINE

    .PRINT "CREATE RULE r_", trim(short_name), "_d AFTER DELETE OF ", trim($table_name)
    .NEWLINE

    .PRINT "EXECUTE PROCEDURE p_",trim($table_name)
    .NEWLINE
    .PRINT "   ("
    .NEWLINE

  .ENDIF


.LET a_counter = 0
.LET b_counter = 0
.LET c_counter = 0
.LET d_counter = 0
.LET e_counter = 0
.LET f_counter = 0
.LET g_counter = 0

.DETAIL

/*
** Create a new history table
*/

  .IF code = "A" .THEN

    .IF a_counter > 0 .THEN

      .TAB +2  
      .PRINT ","

    .ELSE

      .TAB +3

    .ENDIF

    .PRINT trim(column_name)
    .TAB 30
    .PRINT lowercase(trim(column_datatype))

    .IF column_datatype = "FLOAT" OR column_datatype = "INTEGER" .THEN

      .PRINT varchar(column_length)

    .ELSEIF column_datatype = "MONEY" OR column_datatype = "DATE" .THEN

/*
** Do nothing - this is a place holder
*/

    .ELSE

      .PRINT "("
      .PRINT varchar(column_length), ")"

    .ENDIF
      

    .IF column_nulls = "N" .THEN

      .PRINT " not null"

    .ELSE

      .PRINT " with null"

    .ENDIF

    .IF column_defaults = "Y" .THEN

      .PRINT " with default"

    .ENDIF

    .NEWLINE
    .LET a_counter = a_counter + 1

  .ELSEIF code = "B" .THEN

    .IF key_sequence > 0 .THEN
       .PRINT "  ",trim(column_name),","
       .NEWLINE
    .ENDIF

  .ELSEIF code = "C" .THEN

    .IF b_counter > 0 .THEN

      .TAB +2  
      .PRINT ","

    .ELSE

      .TAB +3

    .ENDIF

    .PRINT trim(column_name)
    .TAB 30
    .PRINT "= "
    .PRINT lowercase(trim(column_datatype))

    .IF column_datatype = "FLOAT" OR column_datatype = "INTEGER" .THEN

      .PRINT varchar(column_length)

    .ELSEIF column_datatype = "MONEY" OR column_datatype = "DATE" .THEN

/*
** Do nothing - this is a place holder
*/

    .ELSE

      .PRINT "("
      .PRINT varchar(column_length), ")"

    .ENDIF
      

    .IF column_nulls = "N" .THEN

      .PRINT " not null"

    .ELSE

      .PRINT " with null"

    .ENDIF

    .IF column_defaults = "Y" .THEN

      .PRINT " with default"

    .ENDIF

    .NEWLINE
    .LET b_counter = b_counter + 1

  .ELSEIF code = "D" .THEN

    .IF c_counter > 0 .THEN

      .TAB +5
      .PRINT ","

    .ELSE

      .TAB +6

    .ENDIF

    .PRINT trim(column_name)
    .NEWLINE
    .LET c_counter = c_counter + 1

  .ELSEIF code = "E" .THEN

    .IF d_counter > 0 .THEN

      .TAB +5
      .PRINT ","

    .ELSE

      .TAB +6

    .ENDIF
    
    .PRINT ":",trim(column_name)
    .NEWLINE
    .LET d_counter = d_counter + 1

  .ELSEIF code = "F" .THEN

    .IF e_counter = 0 .THEN

      .PRINT "old.", trim(column_name), " <> new.",  trim(column_name)

    .ELSE

      .PRINT "     OR old.", trim(column_name), " <> new.",  trim(column_name)

    .ENDIF

    .NEWLINE
    .LET e_counter = e_counter + 1

  .ELSEIF code = "G" .THEN

    .IF f_counter > 0 .THEN

      .TAB +4
      .PRINT ","

    .ELSE

      .TAB +5

    .ENDIF

    .PRINT trim(column_name), " = old.", trim(column_name)
    .NEWLINE
    .LET f_counter = f_counter + 1

  .ELSEIF code = "H" .THEN

    .IF g_counter > 0 .THEN

      .TAB +4
      .PRINT ","

    .ELSE

      .TAB +5

    .ENDIF

    .PRINT trim(column_name), " = old.", trim(column_name)
    .NEWLINE
    .LET g_counter = g_counter + 1

  .ENDIF

.FOOTER report

  .PRINT "    ) "
  .NEWLINE
  .PRINT "\\p\\g"
  .NEWLINE
  .NEWLINE

  .PRINT "INSERT into db_rules VALUES"
  .NEWLINE
  .PRINT "  ('r_",short_name,"_c', dbmsinfo('username'), 'now')"
  .NEWLINE
  .PRINT "\\p\\g"
  .NEWLINE
  .NEWLINE
  .PRINT "INSERT into db_rules VALUES"
  .NEWLINE
  .PRINT "  ('r_",short_name,"_d', dbmsinfo('username'), 'now')"
  .NEWLINE
  .PRINT "\\p\\g"