Revision fb8a8097
Von Bernd Bleßmann vor mehr als 9 Jahren hinzugefügt
| sql/Pg-upgrade2/custom_variables_delete_via_trigger.pl | ||
|---|---|---|
| 10 | 10 |
use parent qw(SL::DBUpgrade2::Base); |
| 11 | 11 |
|
| 12 | 12 |
sub run {
|
| 13 |
my ($self) = @_; |
|
| 14 |
|
|
| 15 |
my @queries = ( |
|
| 16 |
#Delete orphaned entries |
|
| 17 |
q|DELETE FROM custom_variables WHERE sub_module = 'orderitems' |
|
| 18 |
AND NOT EXISTS (SELECT id FROM orderitems WHERE orderitems.id = custom_variables.trans_id)|, |
|
| 19 |
q|DELETE FROM custom_variables WHERE sub_module = 'delivery_order_items' |
|
| 20 |
AND NOT EXISTS (SELECT id FROM delivery_order_items WHERE delivery_order_items.id = custom_variables.trans_id)|, |
|
| 21 |
q|DELETE FROM custom_variables WHERE sub_module = 'invoice' |
|
| 22 |
AND NOT EXISTS (SELECT id FROM invoice WHERE invoice.id = custom_variables.trans_id)|, |
|
| 23 |
|
|
| 24 |
#Create trigger |
|
| 25 |
q|CREATE OR REPLACE FUNCTION orderitems_before_delete_trigger() RETURNS trigger AS $$ |
|
| 26 |
BEGIN |
|
| 27 |
DELETE FROM custom_variables WHERE sub_module = 'orderitems' AND trans_id = OLD.id; |
|
| 28 |
|
|
| 29 |
RETURN OLD; |
|
| 30 |
END; |
|
| 31 |
$$ LANGUAGE plpgsql|, |
|
| 32 |
|
|
| 33 |
q|DROP TRIGGER IF EXISTS delete_orderitems_dependencies ON orderitems|, |
|
| 34 |
|
|
| 35 |
q|CREATE TRIGGER delete_orderitems_dependencies |
|
| 36 |
BEFORE DELETE ON orderitems |
|
| 37 |
FOR EACH ROW EXECUTE PROCEDURE orderitems_before_delete_trigger()|, |
|
| 38 |
|
|
| 39 |
q|CREATE OR REPLACE FUNCTION delivery_order_items_before_delete_trigger() RETURNS trigger AS $$ |
|
| 40 |
BEGIN |
|
| 41 |
DELETE FROM custom_variables WHERE sub_module = 'delivery_order_items' AND trans_id = OLD.id; |
|
| 42 |
|
|
| 43 |
RETURN OLD; |
|
| 44 |
END; |
|
| 45 |
$$ LANGUAGE plpgsql|, |
|
| 46 |
|
|
| 47 |
q|DROP TRIGGER IF EXISTS delete_delivery_order_items_dependencies ON delivery_order_items|, |
|
| 48 |
|
|
| 49 |
q|CREATE TRIGGER delete_delivery_order_items_dependencies |
|
| 50 |
BEFORE DELETE ON delivery_order_items |
|
| 51 |
FOR EACH ROW EXECUTE PROCEDURE delivery_order_items_before_delete_trigger()|, |
|
| 52 |
|
|
| 53 |
q|CREATE OR REPLACE FUNCTION invoice_before_delete_trigger() RETURNS trigger AS $$ |
|
| 54 |
BEGIN |
|
| 55 |
DELETE FROM custom_variables WHERE sub_module = 'invoice' AND trans_id = OLD.id; |
|
| 56 |
|
|
| 57 |
RETURN OLD; |
|
| 58 |
END; |
|
| 59 |
$$ LANGUAGE plpgsql|, |
|
| 60 |
|
|
| 61 |
q|DROP TRIGGER IF EXISTS delete_invoice_dependencies ON invoice|, |
|
| 62 |
|
|
| 63 |
q|CREATE TRIGGER delete_invoice_dependencies |
|
| 64 |
BEFORE DELETE ON invoice |
|
| 65 |
FOR EACH ROW EXECUTE PROCEDURE invoice_before_delete_trigger()| |
|
| 66 |
); |
|
| 67 |
|
|
| 68 |
$self->db_query($_) for @queries; |
|
| 13 |
# This script is intentionally empty, because there is another upgrade script |
|
| 14 |
# which provides this functionality. |
|
| 69 | 15 |
|
| 70 | 16 |
return 1; |
| 71 | 17 |
} |
| sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl | ||
|---|---|---|
| 10 | 10 |
use parent qw(SL::DBUpgrade2::Base); |
| 11 | 11 |
|
| 12 | 12 |
sub run {
|
| 13 |
my ($self) = @_; |
|
| 14 |
|
|
| 15 |
my @queries = ( |
|
| 16 |
#Delete orphaned entries |
|
| 17 |
q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) |
|
| 18 |
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) |
|
| 19 |
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'CT'|, |
|
| 20 |
q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) |
|
| 21 |
AND NOT EXISTS (SELECT id FROM contacts WHERE contacts.cp_id = custom_variables.trans_id) |
|
| 22 |
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Contacts'|, |
|
| 23 |
q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) |
|
| 24 |
AND NOT EXISTS (SELECT id FROM parts WHERE parts.id = custom_variables.trans_id) |
|
| 25 |
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'IC'|, |
|
| 26 |
q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) |
|
| 27 |
AND NOT EXISTS (SELECT id FROM project WHERE project.id = custom_variables.trans_id) |
|
| 28 |
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Projects'|, |
|
| 29 |
|
|
| 30 |
#Create trigger |
|
| 31 |
q|CREATE OR REPLACE FUNCTION delete_cv_custom_variables_trigger() RETURNS trigger AS $$ |
|
| 32 |
BEGIN |
|
| 33 |
DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) |
|
| 34 |
AND trans_id = OLD.id |
|
| 35 |
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'CT'; |
|
| 36 |
|
|
| 37 |
RETURN OLD; |
|
| 38 |
END; |
|
| 39 |
$$ LANGUAGE plpgsql|, |
|
| 40 |
|
|
| 41 |
q|DROP TRIGGER IF EXISTS delete_cv_custom_variables ON customer|, |
|
| 42 |
q|DROP TRIGGER IF EXISTS delete_cv_custom_variables ON vendor|, |
|
| 43 |
|
|
| 44 |
q|CREATE TRIGGER delete_cv_custom_variables |
|
| 45 |
BEFORE DELETE ON customer |
|
| 46 |
FOR EACH ROW EXECUTE PROCEDURE delete_cv_custom_variables_trigger()|, |
|
| 47 |
q|CREATE TRIGGER delete_cv_custom_variables |
|
| 48 |
BEFORE DELETE ON vendor |
|
| 49 |
FOR EACH ROW EXECUTE PROCEDURE delete_cv_custom_variables_trigger()|, |
|
| 50 |
|
|
| 51 |
#Create trigger |
|
| 52 |
q|CREATE OR REPLACE FUNCTION delete_contact_custom_variables_trigger() RETURNS trigger AS $$ |
|
| 53 |
BEGIN |
|
| 54 |
DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) |
|
| 55 |
AND trans_id = OLD.cp_id |
|
| 56 |
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Contacts'; |
|
| 57 |
|
|
| 58 |
RETURN OLD; |
|
| 59 |
END; |
|
| 60 |
$$ LANGUAGE plpgsql|, |
|
| 61 |
|
|
| 62 |
q|DROP TRIGGER IF EXISTS delete_contact_custom_variables ON contacts|, |
|
| 63 |
|
|
| 64 |
q|CREATE TRIGGER delete_contact_custom_variables |
|
| 65 |
BEFORE DELETE ON contacts |
|
| 66 |
FOR EACH ROW EXECUTE PROCEDURE delete_contact_custom_variables_trigger()|, |
|
| 67 |
|
|
| 68 |
#Create trigger |
|
| 69 |
q|CREATE OR REPLACE FUNCTION delete_part_custom_variables_trigger() RETURNS trigger AS $$ |
|
| 70 |
BEGIN |
|
| 71 |
DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) |
|
| 72 |
AND trans_id = OLD.id |
|
| 73 |
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'IC'; |
|
| 74 |
|
|
| 75 |
RETURN OLD; |
|
| 76 |
END; |
|
| 77 |
$$ LANGUAGE plpgsql|, |
|
| 78 |
|
|
| 79 |
q|DROP TRIGGER IF EXISTS delete_part_custom_variables ON parts|, |
|
| 80 |
|
|
| 81 |
q|CREATE TRIGGER delete_part_custom_variables |
|
| 82 |
BEFORE DELETE ON parts |
|
| 83 |
FOR EACH ROW EXECUTE PROCEDURE delete_part_custom_variables_trigger()|, |
|
| 84 |
|
|
| 85 |
#Create trigger |
|
| 86 |
q|CREATE OR REPLACE FUNCTION delete_project_custom_variables_trigger() RETURNS trigger AS $$ |
|
| 87 |
BEGIN |
|
| 88 |
DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) |
|
| 89 |
AND trans_id = OLD.id |
|
| 90 |
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Projects'; |
|
| 91 |
|
|
| 92 |
RETURN OLD; |
|
| 93 |
END; |
|
| 94 |
$$ LANGUAGE plpgsql|, |
|
| 95 |
|
|
| 96 |
q|DROP TRIGGER IF EXISTS delete_project_custom_variables ON project|, |
|
| 97 |
|
|
| 98 |
q|CREATE TRIGGER delete_project_custom_variables |
|
| 99 |
BEFORE DELETE ON project |
|
| 100 |
FOR EACH ROW EXECUTE PROCEDURE delete_project_custom_variables_trigger()|, |
|
| 101 |
|
|
| 102 |
); |
|
| 103 |
|
|
| 104 |
$self->db_query($_) for @queries; |
|
| 13 |
# This script is intentionally empty, because there is another upgrade script |
|
| 14 |
# which provides this functionality. |
|
| 105 | 15 |
|
| 106 | 16 |
return 1; |
| 107 | 17 |
} |
| sql/Pg-upgrade2/remove_redundant_cvar_delete_triggers.sql | ||
|---|---|---|
| 1 |
-- @tag: remove_redundant_cvar_delete_triggers |
|
| 2 |
-- @description: Entfernt doppelte Trigger zum Löschen von benutzerdefinierten Variablen |
|
| 3 |
-- @depends: custom_variables_delete_via_trigger custom_variables_delete_via_trigger_2 delete_cvars_on_trans_deletion |
|
| 4 |
-- @encoding: utf-8 |
|
| 5 |
|
|
| 6 |
-- drop triggers |
|
| 7 |
DROP TRIGGER IF EXISTS delete_orderitems_dependencies ON orderitems; |
|
| 8 |
DROP TRIGGER IF EXISTS delete_delivery_order_items_dependencies ON delivery_order_items; |
|
| 9 |
DROP TRIGGER IF EXISTS delete_invoice_dependencies ON invoice; |
|
| 10 |
DROP TRIGGER IF EXISTS delete_cv_custom_variables ON customer; |
|
| 11 |
DROP TRIGGER IF EXISTS delete_cv_custom_variables ON vendor; |
|
| 12 |
DROP TRIGGER IF EXISTS delete_contact_custom_variables ON contacts; |
|
| 13 |
DROP TRIGGER IF EXISTS delete_part_custom_variables ON parts; |
|
| 14 |
DROP TRIGGER IF EXISTS delete_project_custom_variables ON project; |
|
| 15 |
|
|
| 16 |
-- drop functions |
|
| 17 |
DROP FUNCTION IF EXISTS orderitems_before_delete_trigger(); |
|
| 18 |
DROP FUNCTION IF EXISTS delivery_order_items_before_delete_trigger(); |
|
| 19 |
DROP FUNCTION IF EXISTS invoice_before_delete_trigger(); |
|
| 20 |
DROP FUNCTION IF EXISTS delete_cv_custom_variables_trigger(); |
|
| 21 |
DROP FUNCTION IF EXISTS delete_contact_custom_variables_trigger(); |
|
| 22 |
DROP FUNCTION IF EXISTS delete_part_custom_variables_trigger(); |
|
| 23 |
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.