From 36f7b88e4dbafad0df9b2599e5658f0ecce128f1 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Mon, 12 Oct 2020 15:51:29 -0400 Subject: [PATCH] LP#1893463: Protect against null emails Unique indexes on nullable columns will allow multiple conceptually unique rows if the nullable columns are, in fact, NULL because NULL does not equal itself. This commit uses COALESCE to make sure that the nullable email column in the reporter.schedule table gets a value of the empty string for the purposes of the unique index. The upgrade script now also takes this into account and ignores the email column. Signed-off-by: Mike Rylander Signed-off-by: Chris Sharp --- Open-ILS/src/sql/Pg/reporter-schema.sql | 2 +- .../Pg/upgrade/1241.schema.reporter_schedule_uniqueness.sql | 4 ++-- 2 files changed, 3 insertions(+), 3 deletions(-) diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index eb07999c53..68b7cfcb5e 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -110,7 +110,7 @@ CREATE TABLE reporter.schedule ( ); CREATE INDEX rpt_sched_runner_idx ON reporter.schedule (runner); CREATE INDEX rpt_sched_folder_idx ON reporter.schedule (folder); -CREATE UNIQUE INDEX rpt_sched_recurrence_once_idx ON reporter.schedule (report,folder,runner,run_time,email); +CREATE UNIQUE INDEX rpt_sched_recurrence_once_idx ON reporter.schedule (report,folder,runner,run_time,COALESCE(email,'')); CREATE OR REPLACE VIEW reporter.simple_record AS SELECT r.id, diff --git a/Open-ILS/src/sql/Pg/upgrade/1241.schema.reporter_schedule_uniqueness.sql b/Open-ILS/src/sql/Pg/upgrade/1241.schema.reporter_schedule_uniqueness.sql index 934aa76ede..6716658aae 100644 --- a/Open-ILS/src/sql/Pg/upgrade/1241.schema.reporter_schedule_uniqueness.sql +++ b/Open-ILS/src/sql/Pg/upgrade/1241.schema.reporter_schedule_uniqueness.sql @@ -8,7 +8,7 @@ SET CONSTRAINTS ALL IMMEDIATE; -- to address "pending trigger events" error CREATE TABLE reporter.schedule_original (LIKE reporter.schedule); INSERT INTO reporter.schedule_original SELECT * FROM reporter.schedule; TRUNCATE reporter.schedule; -INSERT INTO reporter.schedule (SELECT DISTINCT ON (report, folder, runner, run_time, email) 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); +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); \qecho NOTE: This has created a backup of the original reporter.schedule \qecho table, named reporter.schedule_original. Once you are sure that everything \qecho works as expected, you can delete that table by issuing the following: @@ -17,7 +17,7 @@ INSERT INTO reporter.schedule (SELECT DISTINCT ON (report, folder, runner, run_t \qecho -- Explicitly supply the name because it is referenced in clark-kent.pl -CREATE UNIQUE INDEX rpt_sched_recurrence_once_idx ON reporter.schedule (report,folder,runner,run_time,email); +CREATE UNIQUE INDEX rpt_sched_recurrence_once_idx ON reporter.schedule (report,folder,runner,run_time,COALESCE(email,'')); COMMIT; -- 2.43.2