-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Fri, 6 Feb 2009 18:22:49 +0530
Gurjeet Singh <[email protected]> wrote:
> Hi All,
>
> As I mentioned in the last conference call, I could not implement
> a workaround for CONNECT BY in plpgsql; that was because of a
> limitation in the way plpgsql handles the pseudo datatype RECORD.
>
> So, I turned to implementing it as an add-on in C. But I wanted to
> actually see how much the reward would be if I did that, so I
> performed a better grepping of the sources than before and this is
> what I saw:
>
> <snip>
> $ find ./ -type f | grep -v \' | grep -v \.git | xargs -r grep -i
> "[[:space:]*]connect[[:space:]+]by"
> ./backend/server/rhnAction.py: connect by prior id =
> prerequisite
> ./java/scripts/explain_plan.pl:connect by prior id=parent_id;
> ./java/code/scripts/src/com/redhat/rhn/scripts/ExplainPlanGenerator.java:
> "connect by prior id = parent_id " +
> ./monitoring/PerlModules/NP/NOT-USED/ReleaseDB/ReleaseDB.pm:
> CONNECT BY prior component_class = macro_class
> ./schema/util/explain.sql:connect by prior id = parent_id and
> statement_id = :stmt_id
> ./schema/util/explain_plan_for_queries.py: connect by prior id
> = parent_id and statement_id = 'query_explain'
> ./schema/spacewalk/rhnsat/packages/rhn_server.pkb:
> connect by prior id = prerequisite
> ./schema/spacewalk/test-universe.satellite.sql: connect by
> prior id = prerequisite
> ./schema/spacewalk/universe.satellite.sql: connect by
> prior id = prerequisite
> </snip>
>
> (In previous "crude" grep I had encountered 125 instances!)
>
> Of these, only two were the real candidates:
> ./backend/server/rhnAction.py: connect by prior id =
> prerequisite
> ./schema/spacewalk/rhnsat/packages/rhn_server.pkb:
> connect by prior id = prerequisite
>
> and both these instances SELECT the same data: given an action_id,
> give me all the pre-requisites in order.
>
> So I have written two plpgsql functions to accomplish what these two
> instances need. Please find the code and a few examples attached.
>
> Using these functions, the queries can now be written as:
>
> ./backend/server/rhnAction.py:
>
> _query_lookup_action = rhnSQL.Statement("""
> select sa.action_id, sa.status
> from rhnServerAction sa,
> (
> select id
> from rhn_get_action_prerequisites( :action_id )
> as f( id numeric, prerequisite numeric, level
> int) /**** start with id = :action_id
> connect by prior id = prerequisite ****/
> ) a
> where sa.server_id = :server_id
> and sa.action_id = a.id
> """)
>
> Best regards,
If we modify the queries to use these functions I assume we need Oracle
implementations as well?
Cheers,
Devan
- --
Devan Goodwin <[email protected]>
Software Engineer Spacewalk / RHN Satellite
Halifax, Canada 650.567.9039x79267
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (GNU/Linux)
iEYEARECAAYFAkmMQpIACgkQAyHWaPV9my6HEACfaYg5KPAjsI6f0cd+y+QrkdpB
Ho4An0m5liOFAGdJkmpXROUOiwZk7Xb8
=WpQX
-----END PGP SIGNATURE-----
_______________________________________________
Spacewalk-devel mailing list
[email protected]
https://www.redhat.com/mailman/listinfo/spacewalk-devel
