Re: Dealing with unique IP adresses and ranges

On Tue, 2002-09-03 at 07:21, Mark Fletcher wrote:
> Hello,
>
> No doubt this is a simple question for those more experienced with SQL
> than I (that is, most everyone), but I'm stumped. For the table in
> question, each row represents some information about a particular IP
> address. IP addresses must be unique in regards to a particular user,
> represented by a userId. So far, easy, and the following works for me
> for this:
>
> create unique index ip_index on ip_table( ip, userId );
>
> But what I want is to also be able to store an incomplete IP address,
> representing a range, say a class C block. And when I try to insert a
> row representing a C block, if there are any rows that represent
> complete IP addresses within that C block, it should return an error
> (enforce uniqueness). And vice versa, if there's a row representing a
> class C block, and I try to insert a complete IP address within that
> block, it should return an error.
>
> An example. I insert the following rows:
>
> 1.2.3.1
> 1.2.3.2
> 1.2.3.3
>
> And I try to insert the IP address range 1.2.3, it should error out.
> btw, in my app, IP addresses are represented not as strings, but as
> ints. But if it makes things easier in the database, I can store them as
> strings.
>
> Hope this makes sense. How can I do this?

Have you thought about using the inet or cidr datatypes, rather than
string or int?

You could index on network(ip)

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And he said unto his disciples, Therefore I say unto
you, Take no thought for your life, what ye shall eat;
neither for the body, what ye shall put on. For life
is more than meat, and the body is more than clothing.
Consider the ravens, for they neither sow nor reap;
they have neither storehouse nor barn; and yet God
feeds them; how much better you are than the birds!
Consider the lilies, how they grow; they toil
not, they spin not; and yet I say unto you, that
Solomon in all his glory was not arrayed like one of
these. If then God so clothe the grass, which is to
day in the field, and tomorrow is cast into the oven;
how much more will he clothe you, O ye of little
faith? And seek not what ye shall eat, or what ye
shall drink, neither be ye of doubtful mind.
But rather seek ye the kingdom of God; and all these
things shall be added unto you."
Luke 12:22-24; 27-29; 31.