Re: Updating cidr column with network operator

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Am 22.09.2005 um 22:26 schrieb Daryl Richter:

> Axel Rau wrote:
>> Thank you for responding, Daryl,
>> Am 22.09.2005 um 16:45 schrieb Daryl Richter:
>>> Axel Rau wrote:
>>>
>>>> Hi SQLers,
>>>> I have a fk from address to network and try to update the foreign
>>>> key column to point at the network, "it belongs to":
>>>> CREATE TABLE network (
>>>> id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network
>>>> address'
>>>> )
>>>> CREATE TABLE address (
>>>> id inet PRIMARY KEY , -- 'PK of IPv4/6 host
>>>> address'
>>>> network cidr NOT NULL -- 'FK to Network table'
>>>> REFERENCES network ON DELETE CASCADE ON
>>>> UPDATE CASCADE
>>>> )
>>>> I tried (using the WHERE clause to eliminate the addresses were no
>>>> corresponding net exists):
>>>
>>>
>>> But you can't insert a row in address w/o a valid network.id?
>>> That's what the fk ensures.
>>>
>>> Perhaps you could elaborate more? Are you trying to *put* on the fk
>>> and you currently have bad data?
>> The fk requires a corresponding row in network. But my update tries
>> to reference the right network, that one where the ip address belongs
>> to.
>
> I'm still not understanding what you're trying to do, perhaps its a
> language issue. :) Let me try again.
>
> I built your schema and inserted some rows:
>
> insert into network( id ) values( '10.1' );
>
> insert into address( id, network ) values( '10.1.0.1', '10.1' );
> insert into address( id, network ) values( '10.1.0.2', '10.1' );
> insert into address( id, network ) values( '10.1.0.3', '10.1' );
>
> I then select from network:
>
> id
> -----------
> 10.1.0.0/16
>
> and from address:
>
> id network
> -------- -----------
> 10.1.0.1 10.1.0.0/16
> 10.1.0.2 10.1.0.0/16
> 10.1.0.3 10.1.0.0/16
>
> Why do you now want to update address.network? They are already
> pointing to the right network, aren't they?
Networks change during time, being diveded or aggregated or you just
enter wrong data during insert.
With the UPDATE below, I want to correct the addresses to again point
at the right net. While writing this,
I learn that because of the pk in network, UPDATEs will be difficult to
accomplish (you may need a temporary net
to park all addresses of a network to be divided, make the change in
network and use the UPDATE below to
adjust A.network. I use net '0.0.0.0/32' as 'UNKNOWN' net or for
parking.
>
> I think if you provide some sample data we can figure this out.
Yes, this a goof idea. Playing with small tables let you find quickly
the right query. Lets start over with a slightly bigger
collection of data:

insert into network( id ) values( '10.1/16' );
insert into network( id ) values( '10.2/16' );
insert into network( id ) values( '10.3/16' );

insert into address( id, network ) values( '10.1.0.1', '10.1/16' );
insert into address( id, network ) values( '10.1.0.2', '10.1/16' );
insert into address( id, network ) values( '10.1.0.3', '10.1/16' );
insert into address( id, network ) values( '10.1.0.4', '10.2/16' );
insert into address( id, network ) values( '10.1.0.5', '10.2/16' );
insert into address( id, network ) values( '10.1.0.6', '10.3/16' );

insert into address( id, network ) values( '10.200.0.6', '10.3/16'
); -- address not in network
insert into address( id, network ) values( '10.200.0.7', '10.3/16'
); -- address not in network

During the UPDATE I must avoid NULLs in A.network. This SELECT
SELECT A.id, A.network, N.id FROM address A, network N
WHERE EXISTS (SELECT A.id WHERE A.id << N.id);
does it right:

id | network | id
- ----------+-------------+-------------
10.1.0.1 | 10.1.0.0/16 | 10.1.0.0/16
10.1.0.2 | 10.1.0.0/16 | 10.1.0.0/16
10.1.0.3 | 10.1.0.0/16 | 10.1.0.0/16
10.1.0.4 | 10.2.0.0/16 | 10.1.0.0/16
10.1.0.5 | 10.2.0.0/16 | 10.1.0.0/16
10.1.0.6 | 10.3.0.0/16 | 10.1.0.0/16

NOT the UPDATE (meanwhile improved from below):

UPDATE address
SET network = (SELECT N.id WHERE A.id << N.id)
FROM address A, network N, network N1
WHERE EXISTS (SELECT A.id WHERE A.id << N1.id);
I get: 'ERROR: null value in column "network" violates not-null
constraint'.

So my problem reduces to some (basic) SQL question: "How do I avoid the
NULLs with EXISTS () or IN ()
in the WHERE clause of an UPDATE.?" Apparently in this situation the
WHERE acts not as an inner join.

The problem seems to be the N.id in the SET expression, as shows by
this SELECT:
SELECT A.id, A.network, (SELECT N.id WHERE A.id << N.id)
FROM address A, network N, network N1
WHERE EXISTS (SELECT A.id WHERE A.id << N1.id);
which gives:
id | network | ?column?
- ----------+-------------+-------------
10.1.0.1 | 10.1.0.0/16 | 10.1.0.0/16
10.1.0.1 | 10.1.0.0/16 |
10.1.0.1 | 10.1.0.0/16 |
10.1.0.2 | 10.1.0.0/16 | 10.1.0.0/16
10.1.0.2 | 10.1.0.0/16 |
10.1.0.2 | 10.1.0.0/16 |
10.1.0.3 | 10.1.0.0/16 | 10.1.0.0/16
10.1.0.3 | 10.1.0.0/16 |
10.1.0.3 | 10.1.0.0/16 |
10.1.0.4 | 10.2.0.0/16 | 10.1.0.0/16
10.1.0.4 | 10.2.0.0/16 |
10.1.0.4 | 10.2.0.0/16 |
10.1.0.5 | 10.2.0.0/16 | 10.1.0.0/16
10.1.0.5 | 10.2.0.0/16 |
10.1.0.5 | 10.2.0.0/16 |
10.1.0.6 | 10.3.0.0/16 | 10.1.0.0/16
10.1.0.6 | 10.3.0.0/16 |
10.1.0.6 | 10.3.0.0/16 |

So I'm looking forward to receive advice for suppressing the NULLs.

>>>
>>>> UPDATE address
>>>> SET network = (SELECT N.id WHERE A.id << N.id)
>>>> FROM address A, network N
>>>> WHERE A.id << N.id;
>>>
>>>
>>> This also makes no sense. For starters, << is "bitwise shift left"
>>> ...
>> I'm using 8.0.3 and there are some new operators related to inet and
>> cidr data types.
>> On page 157, I found "<<" as address/network "is contained in"
>> network.
>> Finding the net where an address belongs to works as:
>> SELECT id FROM network WHERE inet '$p_ipSource' << id;
>
> Ahh, ok. see above.
>
Axel

Axel Rau, Frankfurt, Germany +49-69-951418-0
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iQEVAwUBQzQt28Fz9+6bacTRAQI5mggAnZoX8wc3v4G/rZAfcr7BAuknSf3ENXBH
F6fzpZphXPTqBOr45wtdPxCJPaT8bgx6TfgmyRBTx6Btxmz7iAKnDou7p41cTvSZ
gllxr/BmmyzahxonPRqSI/EH10B5BqWdk+4YzwPVyElMLLdvlcq6+yz1jYv17V01
tju81zYt7gj1qwtwpqfJZy27UoO3JYRLsWsDQbXmiplH4IW2qIgIpvuya9Ewd9Eg
G58cyet+Usp6QMXTq/vAPH85k7n+XtZ4RC4AAz0sm56CnJkuULeNDYkQTA74MpCC
k2WN2R/booWQKtfptjD7ahWnjDI6II2RePLKF6yZP1+vKpdaWirTXA==
=KNmK
-----END PGP SIGNATURE-----