Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Questions about Nested Intervals by Vadim Tropashko one more time

Questions about Nested Intervals by Vadim Tropashko one more time

From: <alexzar_nospam_at_zorranlabs.com>
Date: 1 Dec 2003 20:47:04 GMT
Message-ID: <bqg9c8$qu4$1@news.netmar.com>


Hello everybody,

I would like to touch Nested Intervals by Vadim Tropashko one more time. It seems to me that it is the best implementation of storing Trees in SQL, and I want to implement few functions based on Vadim's algorithm:

  1. Insert new node under ANY other node, such as insert new node "p" under x.z.y.k.d to get the whole path to new node as x.z.y.k.d.p
  2. Move ANY node in the tree under another node, such as for the two branches x.z.y.k.d.p.k.t and x.w.f.g.i, move branch y.k.d.p.k.t under node f, to get x.w.f.y.k.d.p.k.t

For the 1), I tried to use sample code from "More pain and suffering with Tropashko's materialized path..." thread, http://dbforums.com/arch/57/2003/9/917999, and sample and formulas, http://www.scenic-route.com/program/db/lists3.htm to calculate Numerator

CREATE FUNCTION dbo.Calculate_Numerator

     ( @numer INTEGER, @child INTEGER )
RETURNS INTEGER
AS
BEGIN
     RETURN @numer * POWER ( 2, @child ) + 3 - POWER ( 2, @child ) END It seems to me that Denominator depends only of @child, so Calculate_Denominator looks this:

CREATE FUNCTION dbo.Calculate_Denominator

     ( @child INTEGER )
RETURNS INTEGER
AS
BEGIN
     RETURN POWER ( 2, @child )
END Now, I am trying to test it against the sample http://www.scenic-route.com/program/db/lists3.htm, Tropashko Nested Intervals:

Category                Path          Numer    Denom
a__                     .1            3        2
   | b__                .1.1          7        4

| e .1.1.1 15 8
| f__ .1.1.2 27 16
| k .1.1.2.1 55 32
| g .1.1.3 51 32
| c__ .1.2 11 8
| h .1.2.1 23 16
| i .1.2.2 43 32
| d .1.3 19 16

For the node a_, path .1, SELECT dbo.Calculate_Numerator ( 1, 1 ), I get 3 node a -> b, path .1.1, SELECT dbo.Calculate_Numerator ( 2, 2 ), I get 7 node a -> b -> e, path .1.1.1, SELECT dbo.Calculate_Numerator ( 3, 3 ), I get 19, but it should be 15

Could you explain what is wrong with parameters of calls, and how they should be different for .1.1.2.1, and .1.1.3 calls, for example? What should be @child and @numer for this x.z.y.k for example?

For 2) do you have any ideas about implementation?

Thanks,
Alex

Received on Mon Dec 01 2003 - 14:47:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US