On Fri, Feb 21, 2014 at 12:09 PM, Jay Shaughnessy <jshaughn(a)redhat.com> wrote:
I'm going to look into making these changes...
I updated the bug, but I already finished my changes, tested on Oracle
(more than 10x speed improvement), and I have a patch to give, it just
needs to be approved by Apple.
Spoiler:
Not to give too much away, but I actually figured out how to do this
on both systems and have the query almost the same.
For Oracle:
+ + "WITH children(resource_id) AS (" // note no RECURSIVE keyword
+ + "SELECT id FROM rhq_resource WHERE parent_resource_id =
:resourceId "
+ + "UNION ALL "
+ + "SELECT r.id FROM children c, rhq_resource r WHERE
r.parent_resource_id = c.resource_id) "
+ + "SELECT resource_id FROM children"
For PostGres:
+ + "WITH RECURSIVE children(resource_id) AS ("
+ + "SELECT id FROM rhq_resource WHERE parent_resource_id =
:resourceId "
+ + "UNION ALL "
+ + "SELECT r.id FROM children c, rhq_resource r WHERE
r.parent_resource_id = c.resource_id) "
+ + "SELECT resource_id FROM children"
The one unfixed query I'm missing is for matching resource name and
type, used when an alert is triggered, causing an operation to fire.
You can add additional columns (like name and type) for these queries,
but they aren't used in all contexts. I suppose ideally you would just
query the entire resource itself, but then each native query has to
include every single column name for rhq_resource, which I wasn't
interested in doing.