Upgrade-Scripte zum Löschen von CVars via Trigger beschleunigt.

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)

diff --git a/sql/Pg-upgrade2/custom_variables_delete_via_trigger.pl b/sql/Pg-upgrade2/custom_variables_delete_via_trigger.pl
index 7eda9de..f73a680 100644 (file)
--- 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 d97493d..e1e5bb5 100644 (file)
--- 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 3e00660..23ae333 100644 (file)
--- 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.