Re: exclusion constraint for ranges of IP

Hi,
you can do the identification of customer by ip in many ways. IMHO, first of
all, you have to put the allowed IPs into your table.
The simpler way is to write all IPs allowed, of course.
The simpler way to do range check is to have 2 columns in table,
IP-range-starts and IP-range-ends, so the SQL could be easy using every data
types.
I prefer to use string data type and not compound ip4r type because can
check using reg.exp. in SQL select, writing IPs range like
1.2.3.100-1.2.3.150 or just single 2.3.4.5.
When I did something like that, I wrote IP in this form : 001002003100 and I
use the two columns of range-starts and range-ends, so it became very easy
(and I think very performant) doing check even with large table.

The worse way is to remand check to PHP, because you have to load entire
table during select and manually select target row there.

I hope these suggests help

2011/8/22 Herouth Maoz <herouth(at)unicell(dot)co(dot)il>

>
> On 22/08/2011, at 01:19, Harald Fuchs wrote:
>
> > In article <CAF36091-203E-4C10-AA53-7D9087114D35(at)unicell(dot)co(dot)il>,
> > Herouth Maoz <herouth(at)unicell(dot)co(dot)il> writes:
> >
> >> Hi,
> >> I'm designing a new database. One of the table contains allowed IP
> ranges for a customer (Fields: customer_id, from_ip, to_ip) which is
> intended to check - if an incoming connection's originating IP number falls
> within the range, it is identified as a particular customer.
> >
> >> Naturally, I'd like to have constraints on the table that prevent
> entering of ip ranges that overlap. Is there a way to do that with exclusion
> constraints? Or do I have to define a new type for this?
> >
> > This "new type" already exists: ip4r, which can be found in pgfoundry.
> > With it you can do
> >
> > CREATE TABLE mytbl (
> > iprange ip4r NOT NULL,
> > ...,
> > CONSTRAINT range_check CHECK ((NOT overlap(iprange)))
> > );
>
>
> Thank you.
>
> I assume you can't use a CHECK constraint for between-rows constraints.
> Wouldn't this be
>
> CONSTRAINT EXCLUDE ( iprange WITH && )
>
> ?
>
> Basically, though, I'm not too happy about using compound types - that's
> why I asked if I have to. I'm not sure what my application will have to send
> and what it will receive when querying a compound type. I use PHP/ZF. I have
> just now posted a question on the pgsql-php list about this. I suspect I'll
> be getting a string which I'll have to parse, which would make the
> application more complicated to read and understand.
>
> Herouth
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

--
Simone