On Fri, Feb 6, 2009 at 7:30 PM, Devan Goodwin <dgood...@redhat.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Fri, 6 Feb 2009 18:22:49 +0530
> Gurjeet Singh <gurjeet.si...@enterprisedb.com> 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?
>
>
Hmm.. Although that is doable, I haven't thought about that. The way I saw
it was that we'd have a IF(ORACLE) CONNECT BY ELSE IF(POSTGRES) CALL
FUNCTION END kinda code...
Thinking a bit, I don't see a point in porting this to Oracle! The calling
syntax will be different for both the databases anyway:
Oracle:
select x,y from TABLE( function() );
Postres:
select x, y from function() as f( x int, y int).
So I guess we will _have_ to keep two versions of the query at least in the
python code. The other usage in PL/SQL package is anyway going to be ported
over.
Best regards,
--
gurjeet[.sin...@enterprisedb.com
EnterpriseDB http://www.enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
_______________________________________________
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
