Good stuff, I am also doing this, I spent some time on Friday and have
the first query working (QUERY_FIND_DESCENDANTS). I like the simpler
Oracle syntax, although it's nice that you also found that Oracle
supports almost a common syntax. The Postgres query I came up with is
similar to yours. Here are the two I'm using currently.
public static final String QUERY_NATIVE_FIND_DESCENDANTS_ORACLE = "" //
+ " SELECT r.id " //
+ " FROM rhq_resource r " //
+ " START WITH r.id = ? " //
+ " CONNECT BY PRIOR r.id = r.parent_resource_id ";
public static final String QUERY_NATIVE_FIND_DESCENDANTS_POSTGRES =
"" //
+ " WITH RECURSIVE childResource AS " //
+ " ( " //
+ " SELECT id FROM rhq_resource WHERE id = ? " //
non-recursive term
+ " UNION ALL " //
+ " SELECT r.id " // recursive term
+ " FROM rhq_resource AS r " //
+ " JOIN childResource AS cr " //
+ " ON (r.parent_resource_id = cr.id) " //
+ " ) " //
+ " SELECT id " //
+ " FROM childResource ";
If you post the patch I'll combine what we have...
Jay
On 2/21/2014 4:53 PM, Elias Ross wrote:
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.
_______________________________________________
rhq-devel mailing list
rhq-devel(a)lists.fedorahosted.org
https://lists.fedorahosted.org/mailman/listinfo/rhq-devel