Cyclos 3.6 RC1 - Clean Test Database

Functionality discussions and feature requests

Moderators: hugo, alexandre, rmvanarkel

Post Reply
duric
Posts: 219
Joined: Mon Mar 19, 2007 8:49 pm
Contact:

Cyclos 3.6 RC1 - Clean Test Database

Post by duric »

Hi all,

I can't find database cleaning script - is it available?
If not, which one is the latest version available?
duric
Posts: 219
Joined: Mon Mar 19, 2007 8:49 pm
Contact:

Post by duric »

I have tried to run Cyclos 3.5 clean up script.

Here's the result:

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`cyclos36/member_account_fee_logs`, CONSTRAINT `FK74581F9F6957A5AA` FOREIGN KEY (`account_fee_log_id`) REFERENCES `account_fee_logs` (`id`))
duric
Posts: 219
Joined: Mon Mar 19, 2007 8:49 pm
Contact:

Post by duric »

There were 3 tables: default_broker_commissions, username_change_logs , account_fee_logs requiring manual deleting of records.

Then cleanup script has been executed without problems.
hugo
Posts: 633
Joined: Sun Jun 18, 2006 1:40 pm

Post by hugo »

Thanks for letting is know and for the effort to fix it your self.
We will make sure the clean db script is up to date with the rc2 version

Regards, Hugo
duric
Posts: 219
Joined: Mon Mar 19, 2007 8:49 pm
Contact:

Post by duric »

Very thanks to all Cyclos Team :)

/*
* Cyclos 3.6 clean up script
* --------------------------
*
* WARNING!!!: Running this script over an existing Cyclos database will
* mercilessly delete all users, transactions and all related data,
* leaving only the configuration. Be careful when you use it.
* The only user that will be left is the default 'admin'.
*
* WARNING 2: After running this script, also remove the WEB-INF/indexes
* and WEB-INF/cache directories (if any) in order to prevent old data to be
* retrieved from searches.
*/

/* Delete data*/
begin;
delete from brokering_commission_status;
delete from brokerings;
delete from contacts;
delete from reference_history;
delete from refs;
delete from ad_interests;
delete from notification_preferences;
delete from images where subclass in ('ad', 'mbr');
delete from custom_field_values where subclass <> ('admin');
delete from custom_field_values where admin_id <> 1;
delete from alerts;
delete from error_log_entry_parameters;
delete from error_log_entries;
delete from admin_alert_notification_preferences;
delete from admin_preferences_new_members;
delete from admin_preferences_message_categories;
delete from admin_preferences_transfer_types;
delete from admin_preferences_new_pending_payments;
delete from admin_preferences_guarantee_types;
delete from admin_notification_preferences;
update transfers set by_id = null, parent_id = null, transaction_fee_id = null, loan_payment_id = null, account_fee_log_id = null, fee_id = null, receiver_id = null, external_transfer_id = null, chargeback_of_id = null;
update account_fees set enabled_since = current_date where enabled_since is not null;
delete from member_account_fee_logs;
delete from invoice_payments;
delete from invoices;
delete from account_status;
delete from account_fee_charges;
delete from account_fee_logs;
delete from external_transfers;
delete from loan_payments;
delete from members_loans;
delete from payment_obligation_logs;
delete from payment_obligations;
delete from guarantee_logs;
delete from guarantees;
delete from certification_logs;
delete from certifications;
delete from loans;
delete from tickets;
delete from transfer_authorizations;
delete from transfers;
delete from scheduled_payments;
delete from accounts where subclass = 'M';
delete from operator_groups_max_amount;
delete from members_loan_groups;
delete from members_loans;
delete from loan_groups;
delete from ads;
delete from login_history;
delete from remarks;
delete from group_history_logs;
delete from password_history;
delete from username_change_logs;
delete from users where username not in ('admin');
delete from messages_to_groups;
delete from messages;
update members set member_broker_id = null, member_id = null;
update groups set member_id = null;
delete from members_channels;
delete from member_records;
delete from pending_members;
delete from transaction_fees where from_member_id is not null or to_member_id is not null;
delete from custom_field_values where field_id in (select id from custom_fields where member_id is not null);
delete from custom_field_possible_values where field_id in (select id from custom_fields where member_id is not null);
delete from custom_fields where member_id is not null;
delete from documents where member_id is not null;
delete from registration_agreement_logs;
create table clients_to_remove select id from service_clients where member_id is not null;
delete from service_client_permissions where service_client_id in (select id from clients_to_remove);
delete from service_clients_receive_payment_types where service_client_id in (select id from clients_to_remove);
delete from service_clients_do_payment_types where service_client_id in (select id from clients_to_remove);
delete from service_clients_manage_groups where service_client_id in (select id from clients_to_remove);
delete from service_clients where id in (select id from clients_to_remove);
drop table clients_to_remove;
delete from custom_field_values where field_id in (select id from custom_fields where transfer_type_id in (select id from transfer_types where fixed_destination_member_id is not null));
delete from custom_field_possible_values where field_id in (select id from custom_fields where transfer_type_id in (select id from transfer_types where fixed_destination_member_id is not null));
delete from custom_fields where transfer_type_id in (select id from transfer_types where fixed_destination_member_id is not null);
delete from transfer_types_channels where transfer_type_id in (select id from transfer_types where fixed_destination_member_id is not null);
delete from groups_chargeback_transfer_types where transfer_type_id in (select id from transfer_types where fixed_destination_member_id is not null);
delete from groups_transfer_types where transfer_type_id in (select id from transfer_types where fixed_destination_member_id is not null);
delete from groups_transfer_types_as_member where transfer_type_id in (select id from transfer_types where fixed_destination_member_id is not null);
delete from transfer_types where fixed_destination_member_id is not null;
update pos set member_pos_id = null;
delete from member_pos;
delete from pos_logs;
delete from pos;
delete from card_logs;
delete from cards;
delete from sms_logs;
delete from member_sms_status;
delete from sms_mailings_groups;
delete from sms_mailings;
delete from members where id not in (select id from users);
delete from permissions where group_id in (select id from groups where subclass = 'O');
delete from groups_transfer_types where group_id in (select id from groups where subclass = 'O');
delete from files where group_id in (select id from groups where subclass = 'O');
delete from group_operator_account_information_permissions;
delete from groups where subclass = 'O';
/* Reset the auto increment value */
alter table account_status auto_increment=1;
alter table account_fee_charges auto_increment=1;
alter table brokerings auto_increment=1;
alter table contacts auto_increment=1;
alter table reference_history auto_increment=1;
alter table refs auto_increment=1;
alter table ad_interests auto_increment=1;
alter table notification_preferences auto_increment=1;
alter table images auto_increment=1;
alter table custom_field_values auto_increment=1;
alter table custom_field_values auto_increment=1;
alter table alerts auto_increment=1;
alter table error_log_entry_parameters auto_increment=1;
alter table error_log_entries auto_increment=1;
alter table admin_alert_notification_preferences auto_increment=1;
alter table admin_preferences_message_categories auto_increment=1;
alter table admin_preferences_transfer_types auto_increment=1;
alter table admin_notification_preferences auto_increment=1;
alter table account_fee_logs auto_increment=1;
alter table invoices auto_increment=1;
alter table external_transfers auto_increment=1;
alter table payment_obligation_logs auto_increment=1;
alter table payment_obligations auto_increment=1;
alter table guarantee_logs auto_increment=1;
alter table guarantees auto_increment=1;
alter table certification_logs auto_increment=1;
alter table certifications auto_increment=1;
alter table loan_payments auto_increment=1;
alter table loans auto_increment=1;
alter table tickets auto_increment=1;
alter table transfers auto_increment=1;
alter table accounts auto_increment=1;
alter table groups auto_increment=1;
alter table operator_groups_max_amount auto_increment=1;
alter table group_history_logs auto_increment=1;
alter table members_loan_groups auto_increment=1;
alter table members_loans auto_increment=1;
alter table loan_groups auto_increment=1;
alter table ads auto_increment=1;
alter table login_history auto_increment=1;
alter table remarks auto_increment=1;
alter table messages auto_increment=1;
alter table member_pos auto_increment=1;
alter table pos_logs auto_increment=1;
alter table pos auto_increment=1;
alter table card_logs auto_increment=1;
alter table cards auto_increment=1;
alter table members auto_increment=2;
delete from account_locks;
insert into account_locks select id from accounts;
commit;
Post Reply