Revision dbd67e43
Von Bernd Bleßmann vor fast 10 Jahren hinzugefügt
| sql/Pg-upgrade2/custom_variables_delete_via_trigger.pl | ||
|---|---|---|
| 14 | 14 |
|
| 15 | 15 |
my @queries = ( |
| 16 | 16 |
#Delete orphaned entries |
| 17 |
q|DELETE FROM custom_variables WHERE sub_module = 'orderitems' AND trans_id NOT IN (SELECT id FROM orderitems)|, |
|
| 18 |
q|DELETE FROM custom_variables WHERE sub_module = 'delivery_order_items' AND trans_id NOT IN (SELECT id FROM delivery_order_items)|, |
|
| 19 |
q|DELETE FROM custom_variables WHERE sub_module = 'invoice' AND trans_id NOT IN (SELECT id FROM invoice)|, |
|
| 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)|, |
|
| 20 | 23 |
|
| 21 | 24 |
#Create trigger |
| 22 | 25 |
q|CREATE OR REPLACE FUNCTION orderitems_before_delete_trigger() RETURNS trigger AS $$ |
| sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl | ||
|---|---|---|
| 15 | 15 |
my @queries = ( |
| 16 | 16 |
#Delete orphaned entries |
| 17 | 17 |
q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) |
| 18 |
AND trans_id NOT IN (SELECT id FROM customer UNION SELECT id FROM vendor)
|
|
| 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 | 19 |
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'CT'|, |
| 20 | 20 |
q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) |
| 21 |
AND trans_id NOT IN (SELECT id FROM contacts)
|
|
| 21 |
AND NOT EXISTS (SELECT id FROM contacts WHERE contacts.cp_id = custom_variables.trans_id)
|
|
| 22 | 22 |
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Contacts'|, |
| 23 | 23 |
q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) |
| 24 |
AND trans_id NOT IN (SELECT id FROM parts)
|
|
| 24 |
AND NOT EXISTS (SELECT id FROM parts WHERE parts.id = custom_variables.trans_id)
|
|
| 25 | 25 |
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'IC'|, |
| 26 | 26 |
q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL) |
| 27 |
AND trans_id NOT IN (SELECT id FROM project)
|
|
| 27 |
AND NOT EXISTS (SELECT id FROM project WHERE project.id = custom_variables.trans_id)
|
|
| 28 | 28 |
AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Projects'|, |
| 29 | 29 |
|
| 30 | 30 |
#Create trigger |
| sql/Pg-upgrade2/delete_cvars_on_trans_deletion.sql | ||
|---|---|---|
| 9 | 9 |
DELETE FROM custom_variables |
| 10 | 10 |
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC')) |
| 11 | 11 |
AND (COALESCE(sub_module, '') = '') |
| 12 |
AND (trans_id NOT IN (SELECT id FROM parts));
|
|
| 12 |
AND NOT EXISTS (SELECT id FROM parts WHERE parts.id = custom_variables.trans_id);
|
|
| 13 | 13 |
|
| 14 | 14 |
-- 1.2. Alle CVars für Angebote/Aufträge, Lieferscheine, Rechnungen |
| 15 | 15 |
-- (sub_module gesetzt): |
| 16 | 16 |
DELETE FROM custom_variables |
| 17 | 17 |
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC')) |
| 18 | 18 |
AND (sub_module = 'orderitems') |
| 19 |
AND (trans_id NOT IN (SELECT id FROM orderitems));
|
|
| 19 |
AND NOT EXISTS (SELECT id FROM orderitems WHERE orderitems.id = custom_variables.trans_id);
|
|
| 20 | 20 |
|
| 21 | 21 |
DELETE FROM custom_variables |
| 22 | 22 |
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC')) |
| 23 | 23 |
AND (sub_module = 'delivery_order_items') |
| 24 |
AND (trans_id NOT IN (SELECT id FROM delivery_order_items));
|
|
| 24 |
AND NOT EXISTS (SELECT id FROM delivery_order_items WHERE delivery_order_items.id = custom_variables.trans_id);
|
|
| 25 | 25 |
|
| 26 | 26 |
DELETE FROM custom_variables |
| 27 | 27 |
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC')) |
| 28 | 28 |
AND (sub_module = 'invoice') |
| 29 |
AND (trans_id NOT IN (SELECT id FROM invoice));
|
|
| 29 |
AND NOT EXISTS (SELECT id FROM invoice WHERE invoice.id = custom_variables.trans_id);
|
|
| 30 | 30 |
|
| 31 | 31 |
-- 1.3. Alle CVars für Kunden/Lieferanten: |
| 32 | 32 |
DELETE FROM custom_variables |
| 33 | 33 |
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'CT')) |
| 34 |
AND (trans_id NOT IN (SELECT id FROM customer UNION SELECT id FROM vendor));
|
|
| 34 |
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);
|
|
| 35 | 35 |
|
| 36 | 36 |
-- 1.4. Alle CVars für Ansprechpersonen: |
| 37 | 37 |
DELETE FROM custom_variables |
| 38 | 38 |
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'Contacts')) |
| 39 |
AND (trans_id NOT IN (SELECT cp_id FROM contacts));
|
|
| 39 |
AND NOT EXISTS (SELECT cp_id FROM contacts WHERE contacts.cp_id = custom_variables.trans_id);
|
|
| 40 | 40 |
|
| 41 | 41 |
-- 1.5. Alle CVars für Projekte: |
| 42 | 42 |
DELETE FROM custom_variables |
| 43 | 43 |
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'Projects')) |
| 44 |
AND (trans_id NOT IN (SELECT id FROM project));
|
|
| 44 |
AND NOT EXISTS (SELECT id FROM project WHERE project.id = custom_variables.trans_id);
|
|
| 45 | 45 |
|
| 46 | 46 |
-- 2. Triggerfunktionen erstellen, die die benutzerdefinierten |
| 47 | 47 |
-- Variablen löschen. |
Auch abrufbar als: Unified diff
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)