Open-ILS/src/sql/Pg/upgrade/1241.schema.reporter_schedule_uniqueness.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('1241', :eg_version);
   4
   5 SET CONSTRAINTS ALL IMMEDIATE; -- to address "pending trigger events" error
   6
   7 -- Dedupe the table before applying the script.  Preserve the original to allow the admin to delete it manually later.
   8 CREATE TABLE reporter.schedule_original (LIKE reporter.schedule);
   9 INSERT INTO reporter.schedule_original SELECT * FROM reporter.schedule;
  10 TRUNCATE reporter.schedule;
  11 INSERT INTO reporter.schedule (SELECT DISTINCT ON (report, folder, runner, run_time) id, report, folder, runner, run_time, start_time, complete_time, email, excel_format, html_format, csv_format, chart_pie, chart_bar, chart_line, error_code, error_text FROM reporter.schedule_original);
  12 \qecho NOTE: This has created a backup of the original reporter.schedule
  13 \qecho table, named reporter.schedule_original.  Once you are sure that everything
  14 \qecho works as expected, you can delete that table by issuing the following:
  15 \qecho
  16 \qecho  'DROP TABLE reporter.schedule_original;'
  17 \qecho
  18
  19 -- Explicitly supply the name because it is referenced in clark-kent.pl
  20 CREATE UNIQUE INDEX rpt_sched_recurrence_once_idx ON reporter.schedule (report,folder,runner,run_time,COALESCE(email,''));
  21
  22 COMMIT;
  23