Could someone help me sort out some SQL please?

[TheExcession]

This is for a Stored Procedure in Oracle9i.

The idea is to select an IP address for a User based upon their membership to a Top Level IP_Pool.

Now, the Top Level IP_Pools may or may not actually contain and IP Addresses, and may of may not have multiple child pools, and so on.

The idea is to go down the tree from the Users Pool until I find a free IP Address.

The (simplified) Table defs are: (sorry about the formating)

Name Null? Type
----------------------------------------- --------
ID NOT NULL NUMBER(38)
PARENT_GROUP_ID NOT NULL NUMBER(38)


so I might have
ID=0 PARENT_GROUP_ID=0 <- top level pool
ID=1 PARENT_GROUP_ID=0 <- secondary pool
ID=2 PARENT_GROUP_ID=0 <- secondary pool
ID=3 PARENT_GROUP_ID=0 <- secondary pool

Now looking at the actuall IP Address Table

Name Null? Type
----------------------------------------- --------
IP_ADDRESS NOT NULL VARCHAR2(15)
POOL_ID NOT NULL NUMBER(38)
AVAILABLE NOT NULL NUMBER(38)


so we might have:
IP_ADDRESS 1.1.1.1 POOL_ID = 1 AVAILABLE = 0
IP_ADDRESS 1.1.1.2 POOL_ID = 1 AVAILABLE = 0
IP_ADDRESS 1.1.1.3 POOL_ID = 1 AVAILABLE = 0

IP_ADDRESS 1.1.2.1 POOL_ID = 2 AVAILABLE = 0
IP_ADDRESS 1.1.2.2 POOL_ID = 2 AVAILABLE = 1
IP_ADDRESS 1.1.2.3 POOL_ID = 2 AVAILABLE = 1

IP_ADDRESS 1.1.3.1 POOL_ID = 3 AVAILABLE = 1
IP_ADDRESS 1.1.3.2 POOL_ID = 3 AVAILABLE = 1
IP_ADDRESS 1.1.3.3 POOL_ID = 3 AVAILABLE = 1

In pseudo code

User Pool_ID =0

Are there any free Addresses in Pool_ID 0?
YES: Mark Not Avaliable - return the Address
NO: Select all child pools with PARENT_GROUP_ID=0

Loop these pools until an Availble Address is found

I think I need to use two cursors and then do some kind of nested loop.

When I just had a single parent pool to deal I was using the following SQL to locate a free address.

Now I need to extend this to handle looking into child pools.

CREATE OR REPLACE FUNCTION BGAN_getDynamicIP
(
V_APNID BGAN_IP_ADDRESSES.APN_ID%type,
V_POOLID BGAN_IP_ADDRESSES.POOL_ID%type,
V_PROFILEID BGAN_IP_ADDRESSES.PROFILE_ID%type,
V_IMSI BGAN_IP_ADDRESSES.IMSI%type,
V_NSAPI BGAN_IP_ADDRESSES.NSAPI%type
)

CURSOR DynamicCur IS SELECT IP_Address FROM BGAN_IP_ADDRESSES WHERE
POOL_ID = V_POOLID AND
APN_ID = V_APNID AND
Allocated = 0;

BEGIN

FOR TREC IN DynamicCur LOOP
IF TREC.Allocated = 0
THEN
V_IPAddress := TREC.IP_Address;

UPDATE BGAN_IP_ADDRESSES SET
Allocated = 1,
PROFILE_ID = V_PROFILEID,
IMSI = V_IMSI,
NSAPI = V_NSAPI
WHERE IP_ADDRESS = V_IPAddress;
Commit;
RETURN V_IPAddress;
END IF;
END LOOP;


END;
/
show errors;


Many thanks
Ex

Edited To Add:
I can probably impose a rule where by you cannot sub divide secondary groups into tertiary and so on, implying that there is no need to recurse down more than one level.

But it would be really sweet if someone could provide a solution that would recurse a complete tree to n levels. (But that might be asking too much!)

>> Edited by TheExcession on Wednesday 12th October 14:25

[pdV6]

Is the database the best place to do this?
Can't your application deal with it more simply?

[TheExcession]

Possibly could, but in my experience it's the calls through to the DB via the JDBC that takes up all the time. (preparing the callable statements etc)

I'd much prefer to handle it in the PL/SQL in possible.

[mr_tony]

IMHO - Best way to do this is to write a view that creates a flat table with name - ip. Put the logic in the view not the query - it's a fair bit quicker then!

(But you're probably doing this already having read the entire post now so I'll keep quiet as Cursors are certainly not my bag!)



>> Edited by mr_tony on Wednesday 12th October 16:38

[TheExcession]

mr_tony said:

IMHO - Best way to do this is to write a view that creates a flat table with name - ip. Put the logic in the view not the query - it's a fair bit quicker then!

ok - don't know exactly how to implement this, but I think I understand what you are saying.

From what I've presented above, could you give me an example of creating the view and then accessing it from the stored procedure?

[boiler]

Hmmm - bearing in mind that I don't have an instance to play about with at the moment, so it is untried...
And it has been a couple of years since I have done any real work...(project manager now) And my SQL Navigator is broken so it won't cut and paste

I have amended the first cursor to accept the poolid as a variable passed into the cursor. This cursor can then be used in the second cursor (which I have defined) as it walks down the tree to check pool by pool.

It makes an assumption that the parentid pointed to in the table is where you want to start, rather than walking all the way up the tree. It would be possible to walk up the tree first if that was necessary. Also had to make an assumption for the table name holding the parent/child relationships.

I havent limited the levels that it will visit. I haven't tested it...and I will post it in the next post.

[boiler]

CREATE OR REPLACE FUNCTION BGAN_getDynamicIP
(
V_APNID BGAN_IP_ADDRESSES.APN_ID%type,
V_POOLID BGAN_IP_ADDRESSES.POOL_ID%type,
V_PROFILEID BGAN_IP_ADDRESSES.PROFILE_ID%type,
V_IMSI BGAN_IP_ADDRESSES.IMSI%type,
V_NSAPI BGAN_IP_ADDRESSES.NSAPI%type
)

/*Changed this cursor to use a varaiable, so that I can re-use it when tree walking*/
CURSOR DynamicCur(c_poolid BGAN_IP_ADDRESSES.POOL_ID%type) IS SELECT IP_Address FROM BGAN_IP_ADDRESSES WHERE
POOL_ID = c_poolid AND
APN_ID = V_APNID AND
Allocated = 0;

--New Cursor ***CHECK TABLE NAME IS CORRECT***
CURSOR ParentPool IS
SELECT ID FROM BGAN_IP_POOL
START WITH ID = l_parent_group_id
CONNECT BY PRIOR ID = PARENT_GROUP_ID
;

-- New local variable ***Check definition is correct***
l_parent_group_id BGAN_IP_POOL.PARENT_GROUP_ID%type;

BEGIN

FOR TREC IN DynamicCur(V_POOLID) LOOP
IF TREC.Allocated = 0
THEN
V_IPAddress := TREC.IP_Address;

UPDATE BGAN_IP_ADDRESSES SET
Allocated = 1,
PROFILE_ID = V_PROFILEID,
IMSI = V_IMSI,
NSAPI = V_NSAPI
WHERE IP_ADDRESS = V_IPAddress;
Commit;

RETURN V_IPAddress;

END IF;
END LOOP;

/*If we get to this point, there is no address in this pool.
Use the cursor ParentPool to walk through all the children of the parent.*/

/*First find the parent group ***Check table name****/

SELECT parent_group_id
INTO l_parent_group_id
FROM BGAN_IP_POOL
WHERE id = v_poolid;


FOR rec_parent IN ParentPool LOOP

FOR TREC IN DynamicCur(rec_parent.id) LOOP
IF TREC.Allocated = 0
THEN
V_IPAddress := TREC.IP_Address;

UPDATE BGAN_IP_ADDRESSES SET
Allocated = 1,
PROFILE_ID = V_PROFILEID,
IMSI = V_IMSI,
NSAPI = V_NSAPI
WHERE IP_ADDRESS = V_IPAddress;
Commit;

RETURN V_IPAddress;

END IF;

END LOOP;

END LOOP;


END;
/
show errors;

[boiler]

Thinking about it, you should probably pass the local variable l_parent_group_id into the tree walking cursor directly.

Hope this helps. It has certainly allowed me to forget about my work problems for a while, and it is time to go home

andy.

[TheExcession]

boiler said:

Thinking about it, you should probably pass the local variable l_parent_group_id into the tree walking cursor directly. Hope this helps. It has certainly allowed me to forget about my work problems for a while, and it is time to go home andy.

Cheers Andy,

I'll give this a try, the mysterious Connect By that I keep hearing about has finally raised its head!

Good stuff!

thanks
Ex

[boiler]

Thinking about it a little bit more , the first cursor only needs to return one row, so you could either use min, max or rownum <= 1 to ensure it only ever returns one row.

You don't really need a cursor (eg you could use select into instead), but the syntax is so much neater, and it is easier than handling a no data found exception.

Also, you shouldn't really have the return statement within the loop that is providing the implicit cursor - there may be problems with releasing memory that is being used by this cursor.

Also if the procedure does not find an ip address, you should return with a known value that you can handle. It is somewhat risky to assume that you will always be returning an ip address.

HTH
Andy.

[TheExcession]

Big to boiler for that - with a bit of tweaking it seems to be doing exactly is required.

Thanks also to those of you that emailed me with suggestions and offers to help!

Dunno where I'd be with out this place.

best
Ex