From fb8a80977013a5b82f03361057e75ae71cb9d193 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Bernd=20Ble=C3=9Fmann?= Date: Tue, 25 Nov 2014 13:22:42 +0100 Subject: [PATCH] =?utf8?q?Redundante=20Upgrade-Skripte=20zum=20L=C3=B6sche?= =?utf8?q?n=20von=20CVars=20via=20Trigger=20entfernt.?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Nicht gelöscht, sondern "geleert" und ein weiteres Skript zum Löschen der Trigger und Funktionen. --- .../custom_variables_delete_via_trigger.pl | 58 +----------- .../custom_variables_delete_via_trigger_2.pl | 94 +------------------ .../remove_redundant_cvar_delete_triggers.sql | 23 +++++ 3 files changed, 27 insertions(+), 148 deletions(-) create mode 100644 sql/Pg-upgrade2/remove_redundant_cvar_delete_triggers.sql diff --git a/sql/Pg-upgrade2/custom_variables_delete_via_trigger.pl b/sql/Pg-upgrade2/custom_variables_delete_via_trigger.pl index f73a6808f..72327310e 100644 --- a/sql/Pg-upgrade2/custom_variables_delete_via_trigger.pl +++ b/sql/Pg-upgrade2/custom_variables_delete_via_trigger.pl @@ -10,62 +10,8 @@ use strict; use parent qw(SL::DBUpgrade2::Base); sub run { - my ($self) = @_; - - my @queries = ( - #Delete orphaned entries - q|DELETE FROM custom_variables WHERE sub_module = 'orderitems' - AND NOT EXISTS (SELECT id FROM orderitems WHERE orderitems.id = custom_variables.trans_id)|, - q|DELETE FROM custom_variables WHERE sub_module = 'delivery_order_items' - AND NOT EXISTS (SELECT id FROM delivery_order_items WHERE delivery_order_items.id = custom_variables.trans_id)|, - q|DELETE FROM custom_variables WHERE sub_module = 'invoice' - AND NOT EXISTS (SELECT id FROM invoice WHERE invoice.id = custom_variables.trans_id)|, - - #Create trigger - q|CREATE OR REPLACE FUNCTION orderitems_before_delete_trigger() RETURNS trigger AS $$ - BEGIN - DELETE FROM custom_variables WHERE sub_module = 'orderitems' AND trans_id = OLD.id; - - RETURN OLD; - END; - $$ LANGUAGE plpgsql|, - - q|DROP TRIGGER IF EXISTS delete_orderitems_dependencies ON orderitems|, - - q|CREATE TRIGGER delete_orderitems_dependencies - BEFORE DELETE ON orderitems - FOR EACH ROW EXECUTE PROCEDURE orderitems_before_delete_trigger()|, - - q|CREATE OR REPLACE FUNCTION delivery_order_items_before_delete_trigger() RETURNS trigger AS $$ - BEGIN - DELETE FROM custom_variables WHERE sub_module = 'delivery_order_items' AND trans_id = OLD.id; - - RETURN OLD; - END; - $$ LANGUAGE plpgsql|, - - q|DROP TRIGGER IF EXISTS delete_delivery_order_items_dependencies ON delivery_order_items|, - - q|CREATE TRIGGER delete_delivery_order_items_dependencies - BEFORE DELETE ON delivery_order_items - FOR EACH ROW EXECUTE PROCEDURE delivery_order_items_before_delete_trigger()|, - - q|CREATE OR REPLACE FUNCTION invoice_before_delete_trigger() RETURNS trigger AS $$ - BEGIN - DELETE FROM custom_variables WHERE sub_module = 'invoice' AND trans_id = OLD.id; - - RETURN OLD; - END; - $$ LANGUAGE plpgsql|, - - q|DROP TRIGGER IF EXISTS delete_invoice_dependencies ON invoice|, - - q|CREATE TRIGGER delete_invoice_dependencies - BEFORE DELETE ON invoice - FOR EACH ROW EXECUTE PROCEDURE invoice_before_delete_trigger()| - ); - - $self->db_query($_) for @queries; + # This script is intentionally empty, because there is another upgrade script + # which provides this functionality. return 1; } diff --git a/sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl b/sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl index e1e5bb5a4..7d441ed36 100644 --- a/sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl +++ b/sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl @@ -10,98 +10,8 @@ use strict; use parent qw(SL::DBUpgrade2::Base); sub run { - my ($self) = @_; - - my @queries = ( - #Delete orphaned entries - q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) - AND NOT EXISTS (SELECT id FROM customer WHERE customer.id = custom_variables.trans_id UNION SELECT id FROM vendor WHERE vendor.id = custom_variables.trans_id) - AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'CT'|, - q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) - AND NOT EXISTS (SELECT id FROM contacts WHERE contacts.cp_id = custom_variables.trans_id) - AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Contacts'|, - q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) - AND NOT EXISTS (SELECT id FROM parts WHERE parts.id = custom_variables.trans_id) - AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'IC'|, - q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) - AND NOT EXISTS (SELECT id FROM project WHERE project.id = custom_variables.trans_id) - AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Projects'|, - - #Create trigger - q|CREATE OR REPLACE FUNCTION delete_cv_custom_variables_trigger() RETURNS trigger AS $$ - BEGIN - DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) - AND trans_id = OLD.id - AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'CT'; - - RETURN OLD; - END; - $$ LANGUAGE plpgsql|, - - q|DROP TRIGGER IF EXISTS delete_cv_custom_variables ON customer|, - q|DROP TRIGGER IF EXISTS delete_cv_custom_variables ON vendor|, - - q|CREATE TRIGGER delete_cv_custom_variables - BEFORE DELETE ON customer - FOR EACH ROW EXECUTE PROCEDURE delete_cv_custom_variables_trigger()|, - q|CREATE TRIGGER delete_cv_custom_variables - BEFORE DELETE ON vendor - FOR EACH ROW EXECUTE PROCEDURE delete_cv_custom_variables_trigger()|, - - #Create trigger - q|CREATE OR REPLACE FUNCTION delete_contact_custom_variables_trigger() RETURNS trigger AS $$ - BEGIN - DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) - AND trans_id = OLD.cp_id - AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Contacts'; - - RETURN OLD; - END; - $$ LANGUAGE plpgsql|, - - q|DROP TRIGGER IF EXISTS delete_contact_custom_variables ON contacts|, - - q|CREATE TRIGGER delete_contact_custom_variables - BEFORE DELETE ON contacts - FOR EACH ROW EXECUTE PROCEDURE delete_contact_custom_variables_trigger()|, - - #Create trigger - q|CREATE OR REPLACE FUNCTION delete_part_custom_variables_trigger() RETURNS trigger AS $$ - BEGIN - DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) - AND trans_id = OLD.id - AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'IC'; - - RETURN OLD; - END; - $$ LANGUAGE plpgsql|, - - q|DROP TRIGGER IF EXISTS delete_part_custom_variables ON parts|, - - q|CREATE TRIGGER delete_part_custom_variables - BEFORE DELETE ON parts - FOR EACH ROW EXECUTE PROCEDURE delete_part_custom_variables_trigger()|, - - #Create trigger - q|CREATE OR REPLACE FUNCTION delete_project_custom_variables_trigger() RETURNS trigger AS $$ - BEGIN - DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) - AND trans_id = OLD.id - AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Projects'; - - RETURN OLD; - END; - $$ LANGUAGE plpgsql|, - - q|DROP TRIGGER IF EXISTS delete_project_custom_variables ON project|, - - q|CREATE TRIGGER delete_project_custom_variables - BEFORE DELETE ON project - FOR EACH ROW EXECUTE PROCEDURE delete_project_custom_variables_trigger()|, - - ); - - $self->db_query($_) for @queries; + # This script is intentionally empty, because there is another upgrade script + # which provides this functionality. return 1; } diff --git a/sql/Pg-upgrade2/remove_redundant_cvar_delete_triggers.sql b/sql/Pg-upgrade2/remove_redundant_cvar_delete_triggers.sql new file mode 100644 index 000000000..dfefa1c7e --- /dev/null +++ b/sql/Pg-upgrade2/remove_redundant_cvar_delete_triggers.sql @@ -0,0 +1,23 @@ +-- @tag: remove_redundant_cvar_delete_triggers +-- @description: Entfernt doppelte Trigger zum Löschen von benutzerdefinierten Variablen +-- @depends: custom_variables_delete_via_trigger custom_variables_delete_via_trigger_2 delete_cvars_on_trans_deletion +-- @encoding: utf-8 + +-- drop triggers +DROP TRIGGER IF EXISTS delete_orderitems_dependencies ON orderitems; +DROP TRIGGER IF EXISTS delete_delivery_order_items_dependencies ON delivery_order_items; +DROP TRIGGER IF EXISTS delete_invoice_dependencies ON invoice; +DROP TRIGGER IF EXISTS delete_cv_custom_variables ON customer; +DROP TRIGGER IF EXISTS delete_cv_custom_variables ON vendor; +DROP TRIGGER IF EXISTS delete_contact_custom_variables ON contacts; +DROP TRIGGER IF EXISTS delete_part_custom_variables ON parts; +DROP TRIGGER IF EXISTS delete_project_custom_variables ON project; + +-- drop functions +DROP FUNCTION IF EXISTS orderitems_before_delete_trigger(); +DROP FUNCTION IF EXISTS delivery_order_items_before_delete_trigger(); +DROP FUNCTION IF EXISTS invoice_before_delete_trigger(); +DROP FUNCTION IF EXISTS delete_cv_custom_variables_trigger(); +DROP FUNCTION IF EXISTS delete_contact_custom_variables_trigger(); +DROP FUNCTION IF EXISTS delete_part_custom_variables_trigger(); +DROP FUNCTION IF EXISTS delete_project_custom_variables_trigger(); -- 2.20.1