Bug 1418034

Description of problem:
Original issue was partly resolved in bz1323332 but there are still some orphaned configurations.

There are still some orphaned configuration and the number is growing when importing new resources or creating new drift definitions.

Clean installation:
orphaned configurations: 499 out of which 488 were empty configurations (not being referenced from RHQ_CONFIG_PROPERTY)

When EAP6 was imported:
orphaned configurations: 896 out of which 885 were empty configurations (not being referenced from RHQ_CONFIG_PROPERTY)

When some drift definitions were created:
orphaned configurations: 1832 out of which 1821 were empty configurations (not being referenced from RHQ_CONFIG_PROPERTY)


After that it was stable, no matter how many changes (snapshots) there were.
I also tried to remove drifts and add them again and the number was stable.

So it seems the number of orphaned empty configurations is related to number of resource types of inventoried resources and number of different drift definition types.


Version-Release number of selected component (if applicable):
JON 3.3.8

How reproducible:
Always

Steps to Reproduce:
1. install JON 3.3.8
2. count number of orphaned configuration using this query:
SELECT COUNT (DISTINCT RHQ_CONFIG.ID) FROM RHQ_CONFIG
    LEFT JOIN RHQ_CONFIG_PROPERTY ON RHQ_CONFIG.ID = RHQ_CONFIG_PROPERTY.CONFIGURATION_ID
    WHERE NOT EXISTS (
        SELECT RHQ_ALERT_NOTIFICATION.SENDER_CONFIG_ID FROM RHQ_ALERT_NOTIFICATION
        WHERE RHQ_ALERT_NOTIFICATION.SENDER_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_ALERT_NOTIFICATION.EXTRA_CONFIG_ID FROM RHQ_ALERT_NOTIFICATION
        WHERE RHQ_ALERT_NOTIFICATION.EXTRA_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_BUNDLE_DEPLOYMENT.CONFIG_ID FROM RHQ_BUNDLE_DEPLOYMENT
        WHERE RHQ_BUNDLE_DEPLOYMENT.CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_CONFIG_GROUP_UPDATE.CONFIGURATION_ID FROM RHQ_CONFIG_GROUP_UPDATE
        WHERE RHQ_CONFIG_GROUP_UPDATE.CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_CONFIG_TEMPLATE.CONFIG_ID FROM RHQ_CONFIG_TEMPLATE
        WHERE RHQ_CONFIG_TEMPLATE.CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_CONFIG_UPDATE.CONFIGURATION_ID FROM RHQ_CONFIG_UPDATE
        WHERE RHQ_CONFIG_UPDATE.CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_CONTENT_SOURCE.CONFIGURATION_ID FROM RHQ_CONTENT_SOURCE
        WHERE RHQ_CONTENT_SOURCE.CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_CREATE_RES_HIST.CONFIGURATION_ID FROM RHQ_CREATE_RES_HIST
        WHERE RHQ_CREATE_RES_HIST.CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_DASHBOARD.CONFIGURATION_ID FROM RHQ_DASHBOARD
        WHERE RHQ_DASHBOARD.CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_DASHBOARD_PORTLET.CONFIGURATION_ID FROM RHQ_DASHBOARD_PORTLET
        WHERE RHQ_DASHBOARD_PORTLET.CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_DRIFT_DEFINITION.CONFIG_ID FROM RHQ_DRIFT_DEFINITION
        WHERE RHQ_DRIFT_DEFINITION.CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_DRIFT_DEF_TEMPLATE.CONFIG_ID FROM RHQ_DRIFT_DEF_TEMPLATE
        WHERE RHQ_DRIFT_DEF_TEMPLATE.CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_INSTALLED_PKG_HIST.DEPLOYMENT_CONFIG_ID FROM RHQ_INSTALLED_PKG_HIST
        WHERE RHQ_INSTALLED_PKG_HIST.DEPLOYMENT_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_OPERATION_HISTORY.PARAMETERS_CONFIG_ID FROM RHQ_OPERATION_HISTORY
        WHERE RHQ_OPERATION_HISTORY.PARAMETERS_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_OPERATION_HISTORY.RESULTS_CONFIG_ID FROM RHQ_OPERATION_HISTORY
        WHERE RHQ_OPERATION_HISTORY.RESULTS_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_PACKAGE_VERSION.CONFIG_ID FROM RHQ_PACKAGE_VERSION
        WHERE RHQ_PACKAGE_VERSION.CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_PLUGIN.PLUGIN_CONFIG_ID FROM RHQ_PLUGIN
        WHERE RHQ_PLUGIN.PLUGIN_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_PLUGIN.JOBS_CONFIG_ID FROM RHQ_PLUGIN
        WHERE RHQ_PLUGIN.JOBS_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_RAW_CONFIG.CONFIG_ID FROM RHQ_RAW_CONFIG
        WHERE RHQ_RAW_CONFIG.CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_RESOURCE_TYPE.BUNDLE_CONFIG_ID FROM RHQ_RESOURCE_TYPE
        WHERE RHQ_RESOURCE_TYPE.BUNDLE_CONFIG_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_RESOURCE.RES_CONFIGURATION_ID FROM RHQ_RESOURCE
        WHERE RHQ_RESOURCE.RES_CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_RESOURCE.PLUGIN_CONFIGURATION_ID FROM RHQ_RESOURCE
        WHERE RHQ_RESOURCE.PLUGIN_CONFIGURATION_ID = RHQ_CONFIG.ID
    ) AND NOT EXISTS (
        SELECT RHQ_SUBJECT.CONFIGURATION_ID FROM RHQ_SUBJECT
        WHERE RHQ_SUBJECT.CONFIGURATION_ID = RHQ_CONFIG.ID
    );
3. import EAP 6
4. run the query again
5. create some drifts
6. run the query again

Actual results:
The number of orphaned configurations is growing

Expected results:
There should be no orphaned configurations

Additional info:
This bz is to finish work done by bz1323332 which added some improvements but did not resolve the issue completely.