x8QG8rqV

1#!/bin/bash
2
3## purge record if resource_id is null
4mysql -e "use transformer; delete from associations where resource_id='';"
5mysql -e "use transformer; delete from associations where resource_type='OS::Cinder::VolumeAttachment';"
6mysql -e "use transformer; drop table if exists tobe_deleted_ids; create table tobe_deleted_ids (resource_id varchar(80));"
7
8## search nova related associations
9mysql -e "insert into transformer.tobe_deleted_ids select resource_id from transformer.associations, (select uuid from nova.instances where nova.instances.deleted !=0) as vm where transformer.associations.resource_id=vm.uuid"
10
11## search cinder related associations
12mysql -e "insert into transformer.tobe_deleted_ids select resource_id from transformer.associations, (select id from cinder.volumes where cinder.volumes.deleted !=0) as volume where transformer.associations.resource_id=volume.id"
13mysql -e "insert into transformer.tobe_deleted_ids select resource_id from transformer.associations, (select id from cinder.backups where cinder.backups.deleted !=0) as backup where transformer.associations.resource_id=backup.id"
14mysql -e "insert into transformer.tobe_deleted_ids select resource_id from transformer.associations, (select id from cinder.snapshots where cinder.snapshots.deleted !=0) as snapshot where transformer.associations.resource_id=snapshot.id"
15mysql -e "insert into transformer.tobe_deleted_ids select resource_id from transformer.associations, (select id from cinder.snapshots where cinder.snapshots.deleted !=0) as snapshot where transformer.associations.resource_id=snapshot.id"
16mysql -e "insert into transformer.tobe_deleted_ids select resource_id from transformer.associations, (select id from cinder.volume_attachment where cinder.volume_attachment.deleted !=0) as attachment where transformer.associations.resource_id=attachment.id"
17
18## search neutron related associations
19mysql -e "insert into transformer.tobe_deleted_ids select resource_id from transformer.associations where resource_type='neutron.loadbalancer' and resource_id not in (select id from neutron.lbaas_loadbalancers)"
20mysql -e "insert into transformer.tobe_deleted_ids select resource_id from transformer.associations where resource_type='router' and resource_id not in (select id from neutron.routers)"
21mysql -e "insert into transformer.tobe_deleted_ids select resource_id from transformer.associations where resource_type='neutron.router' and resource_id not in (select id from neutron.routers)"
22
23## search trove related associations
24mysql -e "insert into transformer.tobe_deleted_ids select resource_id from transformer.associations, (select id from trove.instances where trove.instances.deleted !=0) as instance where transformer.associations.resource_id=instance.id"
25
26# delete all need deleted associations
27mysql -e "use transformer; delete A.* from associations A inner join tobe_deleted_ids B on A.resource_id = B.resource_id; drop table tobe_deleted_ids;"