Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Deleting a branch from a tree structure

by Stegalex (Chaplain)
on Apr 22, 2003 at 23:04 UTC ( [id://252392]=perlquestion: print w/replies, xml ) Need Help??

Stegalex has asked for the wisdom of the Perl Monks concerning the following question:

I am pretty sure that this is going to entail the use of recursive subroutine calls, but I just can't get my mind around this problem.

I have a self referencing table in Oracle called "CATEGORY". A category can be a sub-category of a sub-category ad nauseum. The problem I have encountered is that Oracle's "ON DELETE CASCADE" referential integrity contstraint doesn't work on self-referencing tables (ORA-2292). So what I need to do is to build a function that will start at a node in the Category tree and delete it and all of its subcategories.

Not looking for you to write my code, just looking for a snippet or a reference to a useful pre-existing module. Thanks all.

~~~~~~~~~~~~~~~
I like chicken.

Replies are listed 'Best First'.
Re: Deleting a branch from a tree structure
by perrin (Chancellor) on Apr 22, 2003 at 23:46 UTC
    I think you'd be better off using PL/SQL or Oracle's "CONNECT BY" extensions. There's a tutorial here.
      Thanks! I knew I could do it with SQL but I am so tired that I am not thinking straight. Excellent tutorial!

      ~~~~~~~~~~~~~~~
      I like chicken.
Re: Deleting a branch from a tree structure
by demerphq (Chancellor) on Apr 22, 2003 at 23:52 UTC
    sub rec_delete { my $id=shift; foreach my $id (select id where parent=$id) { rec_delete($id); } delete($id); }

    ---
    demerphq

    <Elian> And I do take a kind of perverse pleasure in having an OO assembly language...

      This deletes referenced categories before the category that refers to them, which I think leaves open a window of brokenness, at least of referential integrity.

      It might be better to do it the other way round:

      sub rec_delete { my $id = shift; my @kids = (... select id where parent = $id ...); delete($id); rec_delete($_) for @kids; }

      Update: this is wrong - I was thinking "delete the categories this category refers to" rather than "delete the categories that refer to this category".

      Hugo

        Hmm. If the child refrences the parent, then we can't delete the parent until we remove the childs reference to it, or remove the child itself of course. So we have to do a postorder traversal of the implicit tree. A preorder traversal as you have here would try to delete the parent while the children point at it.

        Im assuming that the OP was using using the only sane way I know of to represent an nary tree in a single table form, that is using a parent pointer list representation. Now these may have been asumptions too far but... :-)


        ---
        demerphq

        <Elian> And I do take a kind of perverse pleasure in having an OO assembly language...

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://252392]
Approved by The Mad Hatter
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (5)
As of 2024-03-29 14:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found