SQL to Clean Up Unwanted Transaction Records While Keeping Certain Configurations

When a system is newly installed, the users would like to create some real records like customer data, real users, real suppliers, permission settings etc. These configurations are very often wanted, or needed after the trial run / testing period. The test transaction records are often unwanted during the full launch of the Wavelet EMP, they need to be discarded.

To perform the database clean up, see steps below:

* Please take note that we assume the database name as "wsemp", if you use a different database name, please change accordingly.

1. Identity the tables with data to be maintained.

acc_bizentity_index

acc_bizgroup_index

acc_bizunit_index

acc_branch_index

acc_pccenter_index

acc_cash_account

acc_glcode_index

cust_account_index

cust_user_index

inv_location

inv_item

inv_category

inv_category_tree

inv_bom

inv_bom_link

supp_account_index

user_index

user_role_index

user_userrole_link

user_roledomain_link

user_domain_index

user_config_registry

user_object_permissions

user_permissions

app_registry_index

2. Backup these tables

pg_dump -D -i -O -f acc_bizentity_index.sql -t acc_bizentity_index wsemp

pg_dump -D -i -O -f acc_bizgroup_index.sql -t acc_bizgroup_index wsemp

pg_dump -D -i -O -f acc_bizunit_index.sql -t acc_bizunit_index wsemp

pg_dump -D -i -O -f acc_branch_index.sql -t acc_branch_index wsemp

pg_dump -D -i -O -f acc_pccenter_index.sql -t acc_pccenter_index wsemp

pg_dump -D -i -O -f acc_cash_account.sql -t acc_cash_account wsemp

pg_dump -D -i -O -f acc_glcode_index.sql -t acc_glcode_index wsemp

pg_dump -D -i -O -f inv_location.sql -t inv_location wsemp

pg_dump -D -i -O -f cust_account_index.sql -t cust_account_index wsemp

pg_dump -D -i -O -f cust_user_index.sql -t cust_user_index wsemp

pg_dump -D -i -O -f inv_item.sql -t inv_item wsemp

pg_dump -D -i -O -f inv_category.sql -t inv_category wsemp

pg_dump -D -i -O -f inv_category_tree.sql -t inv_category_tree wsemp

pg_dump -D -i -O -f inv_bom.sql -t inv_bom wsemp

pg_dump -D -i -O -f inv_bom_link.sql -t inv_bom_link wsemp

pg_dump -D -i -O -f supp_account_index.sql -t supp_account_index wsemp

pg_dump -D -i -O -f user_index.sql -t user_index wsemp

pg_dump -D -i -O -f user_role_index.sql -t user_role_index wsemp

pg_dump -D -i -O -f user_userrole_link.sql -t user_userrole_link wsemp

pg_dump -D -i -O -f user_roledomain_link.sql -t user_roledomain_link wsemp

pg_dump -D -i -O -f user_domain_index.sql -t user_domain_index wsemp

pg_dump -D -i -O -f user_config_registry.sql -t user_config_registry wsemp

pg_dump -D -i -O -f user_object_permissions.sql -t user_object_permissions wsemp

pg_dump -D -i -O -f user_permissions.sql -t user_permissions wsemp

pg_dump -D -i -O -f app_registry_index.sql -t app_registry_index wsemp

3. Create new database

emp6/src/sql/db_install.sh wsemp

4. Populate the tables into the new database

psql -f user_role_index.sql wsemp

psql -f user_index.sql wsemp

psql -f user_userrole_link.sql wsemp

psql -f user_domain_index.sql wsemp

psql -f user_roledomain_link.sql wsemp

psql -f user_config_registry.sql wsemp

psql -f user_object_permissions.sql wsemp

psql -f user_permissions.sql wsemp

psql -f acc_bizentity_index.sql wsemp

psql -f acc_bizgroup_index.sql wsemp

psql -f acc_bizunit_index.sql wsemp

psql -f acc_pccenter_index.sql wsemp

psql -f acc_glcode_index.sql wsemp

psql -f acc_cash_account.sql wsemp

psql -f inv_location.sql wsemp

psql -f acc_branch_index.sql wsemp

psql -f supp_account_index.sql wsemp

psql -f cust_account_index.sql wsemp

psql -f cust_user_index.sql wsemp

psql -f inv_item.sql wsemp

psql -f inv_category.sql wsemp

psql -f inv_category_tree.sql wsemp

psql -f inv_bom.sql wsemp

psql -f inv_bom_link.sql wsemp

psql -f app_registry_index.sql wsemp

5. Compare the table row count between old database and new database to ensure everything is populated

select count(*) from acc_bizentity_index;

select count(*) from acc_bizgroup_index;

select count(*) from acc_bizunit_index;

select count(*) from acc_branch_index;

select count(*) from acc_pccenter_index;

select count(*) from acc_cash_account;

select count(*) from acc_glcode_index;

select count(*) from cust_account_index;

select count(*) from cust_user_index;

select count(*) from inv_location;

select count(*) from inv_item;

select count(*) from inv_category;

select count(*) from inv_category_tree;

select count(*) from inv_bom;

select count(*) from inv_bom_link;

select count(*) from supp_account_index;

select count(*) from user_index;

select count(*) from user_role_index;

select count(*) from user_userrole_link;

select count(*) from user_roledomain_link;

select count(*) from user_domain_index;

select count(*) from user_config_registry;

select count(*) from user_object_permissions;

select count(*) from user_permissions;

select count(*) from app_registry_index;