From dbd67e4387f1d33ce1ffdc505fad7fdd0eee7f29 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Bernd=20Ble=C3=9Fmann?= Date: Mon, 24 Nov 2014 12:50:58 +0100 Subject: [PATCH] =?utf8?q?Upgrade-Scripte=20zum=20L=C3=B6schen=20von=20CVa?= =?utf8?q?rs=20via=20Trigger=20beschleunigt.?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit In den queries, die vor dem Installieren der Trigger die DB aufräumen, "NOT IN" durch "NOT EXISTS" ersetzt. Hintergrund-Info ist hier zu finden: http://explainextended.com/2009/09/16/not-in-vs-not-exists-vs-left-join-is-null-postgresql/ (Dank an Sven) --- .../custom_variables_delete_via_trigger.pl | 9 ++++++--- .../custom_variables_delete_via_trigger_2.pl | 8 ++++---- sql/Pg-upgrade2/delete_cvars_on_trans_deletion.sql | 14 +++++++------- 3 files changed, 17 insertions(+), 14 deletions(-) diff --git a/sql/Pg-upgrade2/custom_variables_delete_via_trigger.pl b/sql/Pg-upgrade2/custom_variables_delete_via_trigger.pl index 7eda9dea0..f73a6808f 100644 --- a/sql/Pg-upgrade2/custom_variables_delete_via_trigger.pl +++ b/sql/Pg-upgrade2/custom_variables_delete_via_trigger.pl @@ -14,9 +14,12 @@ sub run { my @queries = ( #Delete orphaned entries - q|DELETE FROM custom_variables WHERE sub_module = 'orderitems' AND trans_id NOT IN (SELECT id FROM orderitems)|, - q|DELETE FROM custom_variables WHERE sub_module = 'delivery_order_items' AND trans_id NOT IN (SELECT id FROM delivery_order_items)|, - q|DELETE FROM custom_variables WHERE sub_module = 'invoice' AND trans_id NOT IN (SELECT id FROM invoice)|, + 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 $$ 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 d97493d4f..e1e5bb5a4 100644 --- a/sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl +++ b/sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl @@ -15,16 +15,16 @@ sub run { my @queries = ( #Delete orphaned entries q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) - AND trans_id NOT IN (SELECT id FROM customer UNION SELECT id FROM vendor) + 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 trans_id NOT IN (SELECT id FROM contacts) + 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 trans_id NOT IN (SELECT id FROM parts) + 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 trans_id NOT IN (SELECT id FROM project) + 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 diff --git a/sql/Pg-upgrade2/delete_cvars_on_trans_deletion.sql b/sql/Pg-upgrade2/delete_cvars_on_trans_deletion.sql index 3e00660b3..23ae333de 100644 --- a/sql/Pg-upgrade2/delete_cvars_on_trans_deletion.sql +++ b/sql/Pg-upgrade2/delete_cvars_on_trans_deletion.sql @@ -9,39 +9,39 @@ DELETE FROM custom_variables WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC')) AND (COALESCE(sub_module, '') = '') - AND (trans_id NOT IN (SELECT id FROM parts)); + AND NOT EXISTS (SELECT id FROM parts WHERE parts.id = custom_variables.trans_id); -- 1.2. Alle CVars für Angebote/Aufträge, Lieferscheine, Rechnungen -- (sub_module gesetzt): DELETE FROM custom_variables WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC')) AND (sub_module = 'orderitems') - AND (trans_id NOT IN (SELECT id FROM orderitems)); + AND NOT EXISTS (SELECT id FROM orderitems WHERE orderitems.id = custom_variables.trans_id); DELETE FROM custom_variables WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC')) AND (sub_module = 'delivery_order_items') - AND (trans_id NOT IN (SELECT id FROM delivery_order_items)); + AND NOT EXISTS (SELECT id FROM delivery_order_items WHERE delivery_order_items.id = custom_variables.trans_id); DELETE FROM custom_variables WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC')) AND (sub_module = 'invoice') - AND (trans_id NOT IN (SELECT id FROM invoice)); + AND NOT EXISTS (SELECT id FROM invoice WHERE invoice.id = custom_variables.trans_id); -- 1.3. Alle CVars für Kunden/Lieferanten: DELETE FROM custom_variables WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'CT')) - AND (trans_id NOT IN (SELECT id FROM customer UNION SELECT id FROM vendor)); + 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); -- 1.4. Alle CVars für Ansprechpersonen: DELETE FROM custom_variables WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'Contacts')) - AND (trans_id NOT IN (SELECT cp_id FROM contacts)); + AND NOT EXISTS (SELECT cp_id FROM contacts WHERE contacts.cp_id = custom_variables.trans_id); -- 1.5. Alle CVars für Projekte: DELETE FROM custom_variables WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'Projects')) - AND (trans_id NOT IN (SELECT id FROM project)); + AND NOT EXISTS (SELECT id FROM project WHERE project.id = custom_variables.trans_id); -- 2. Triggerfunktionen erstellen, die die benutzerdefinierten -- Variablen löschen. -- 2.20.1