killbill-memoizeit

util: introduce trimAccount trimAccount deletes everything …

2/10/2017 7:03:35 AM

except account and payment method data.
This can be used to periodically cleanup accounts used for automated testing.

Signed-off-by: Pierre-Alexandre Meyer <pierre@mouraf.org>

Pierre-Alexandre Meyer

Commit: 2de22b5

Tree: 715a02a

Parents: 8e99ec6

Changes

util/src/main/resources/cleanAccount.sql 57(+1 -56)

util/src/main/resources/trimAccount.sql 72(+72 -0)

Details

util/src/main/resources/cleanAccount.sql 57(+1 -56)

diff --git a/util/src/main/resources/cleanAccount.sql b/util/src/main/resources/cleanAccount.sql
index 25cd821..0224d1f 100644
--- a/util/src/main/resources/cleanAccount.sql
+++ b/util/src/main/resources/cleanAccount.sql
@@ -8,68 +8,13 @@ BEGIN
 
     select record_id, tenant_record_id from accounts WHERE external_key = p_account_key into v_account_record_id, v_tenant_record_id;
 
-    DELETE FROM analytics_account_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_account_tags WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_account_transitions WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_accounts WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_bundle_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_bundle_tags WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_bundles WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_invoice_adjustments WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_invoice_credits WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_invoice_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_invoice_item_adjustments WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_invoice_items WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_invoice_payment_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_invoice_tags WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_invoices WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_notifications WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
-    DELETE FROM analytics_notifications_history WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
-    DELETE FROM analytics_payment_auths WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_payment_captures WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_payment_chargebacks WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_payment_credits WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_payment_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_payment_method_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_payment_purchases WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_payment_refunds WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_payment_tags WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_payment_voids WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_subscription_transitions WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM analytics_transaction_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    call trimAccount(p_account_key);
 
-    DELETE FROM account_email_history WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM account_emails WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
     DELETE FROM account_history WHERE target_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
     DELETE FROM accounts WHERE record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
     DELETE FROM audit_log WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM blocking_states WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM bundles WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM bus_events WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
-    DELETE FROM bus_events_history WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
-    DELETE FROM bus_ext_events WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
-    DELETE FROM bus_ext_events_history WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
-    DELETE FROM custom_field_history WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM custom_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM invoice_items WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM invoice_parent_children WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM invoice_payments WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM invoices WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM notifications WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
-    DELETE FROM notifications_history WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
-    DELETE FROM payment_attempt_history WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM payment_attempts WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM payment_history WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
     DELETE FROM payment_method_history WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
     DELETE FROM payment_methods WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM payment_transaction_history WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM payment_transactions WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM payments WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM rolled_up_usage WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM subscription_events WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM subscriptions WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM tag_history WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
-    DELETE FROM tags WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
 
     END;
 //

util/src/main/resources/trimAccount.sql 72(+72 -0)

diff --git a/util/src/main/resources/trimAccount.sql b/util/src/main/resources/trimAccount.sql
new file mode 100644
index 0000000..0b3f438
--- /dev/null
+++ b/util/src/main/resources/trimAccount.sql
@@ -0,0 +1,72 @@
+drop procedure if exists trimAccount;
+DELIMITER //
+CREATE PROCEDURE trimAccount(p_account_key varchar(36))
+BEGIN
+
+    DECLARE v_account_record_id bigint /*! unsigned */;
+    DECLARE v_tenant_record_id bigint /*! unsigned */;
+
+    select record_id, tenant_record_id from accounts WHERE external_key = p_account_key into v_account_record_id, v_tenant_record_id;
+
+    DELETE FROM analytics_account_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_account_tags WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_account_transitions WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_accounts WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_bundle_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_bundle_tags WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_bundles WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_invoice_adjustments WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_invoice_credits WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_invoice_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_invoice_item_adjustments WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_invoice_items WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_invoice_payment_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_invoice_tags WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_invoices WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_notifications WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
+    DELETE FROM analytics_notifications_history WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
+    DELETE FROM analytics_payment_auths WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_payment_captures WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_payment_chargebacks WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_payment_credits WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_payment_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_payment_method_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_payment_purchases WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_payment_refunds WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_payment_tags WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_payment_voids WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_subscription_transitions WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM analytics_transaction_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+
+    DELETE FROM account_email_history WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM account_emails WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM audit_log WHERE table_name not in ('ACCOUNT_HISTORY', 'PAYMENT_METHOD_HISTORY') and account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM blocking_states WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM bundles WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM bus_events WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
+    DELETE FROM bus_events_history WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
+    DELETE FROM bus_ext_events WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
+    DELETE FROM bus_ext_events_history WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
+    DELETE FROM custom_field_history WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM custom_fields WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM invoice_items WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM invoice_parent_children WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM invoice_payments WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM invoices WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM notifications WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
+    DELETE FROM notifications_history WHERE search_key1 = v_account_record_id and search_key2 = v_tenant_record_id;
+    DELETE FROM payment_attempt_history WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM payment_attempts WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM payment_history WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM payment_transaction_history WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM payment_transactions WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM payments WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM rolled_up_usage WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM subscription_events WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM subscriptions WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM tag_history WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+    DELETE FROM tags WHERE account_record_id = v_account_record_id and tenant_record_id = v_tenant_record_id;
+
+    END;
+//
+DELIMITER ;