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;
}