Revision fb8a8097
Von Bernd Bleßmann vor mehr als 10 Jahren hinzugefügt
| sql/Pg-upgrade2/custom_variables_delete_via_trigger.pl | ||
|---|---|---|
|
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;
|
||
|
}
|
||
| sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl | ||
|---|---|---|
|
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;
|
||
|
}
|
||
| sql/Pg-upgrade2/remove_redundant_cvar_delete_triggers.sql | ||
|---|---|---|
|
-- @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();
|
||
Auch abrufbar als: Unified diff
Redundante Upgrade-Skripte zum Löschen von CVars via Trigger entfernt.
Nicht gelöscht, sondern "geleert" und ein weiteres Skript zum Löschen
der Trigger und Funktionen.