Thread: Dealing with unique IP adresses and ranges
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? Thanks! Mark
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.Elphick@lfix.co.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.
Oliver Elphick wrote: >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) > > > Thanks for the reply. I've gone through the docs for network datatypes and functions. The operator '<<' does the test I think I'm looking for (b is contained in a). But how do I incorporate that into a constraint on the table? Thanks, Mark
Mark Fletcher <markf@wingedpig.com> writes:
> But what I want is to also be able to store an incomplete IP address,
> representing a range, say a class C block.
Are the ranges always standard ranges like /16, /18, /24, etc? If so
you should really forget about both strings and integers and use the
CIDR datatype.
Unfortunately that only solves the data-representation issue and still
leaves you with an SQL issue. You can easily test if there's overlap
between an existing entry and a proposed new one, say with
new <<= old OR new >>= old
but since this doesn't correspond to a unique-index behavior you can't
easily get the system to enforce it for you.
The only way I can think of to solve it is to create a BEFORE INSERT
trigger that does the test the hard way:
if exists(select 1 from mytab where ip >>= new.ip or ip <<= new.ip)
then raise error;
and something a tad more complex for BEFORE UPDATE (you'd want to ignore
the row you're updating). This is not going to scale nicely to huge
numbers of table entries, I fear, because of the non-indexability of
the queries; but it should work okay up to a few thousand entries.
It would be interesting to see whether a GIST operator class could be
devised that would support indexing <<= and >>= queries ... but I don't
suppose that's fit material for the NOVICE list ...
regards, tom lane
On Tue, 2002-09-03 at 08:55, Mark Fletcher wrote: > Oliver Elphick wrote: > > >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) > > > > > > > Thanks for the reply. I've gone through the docs for network datatypes > and functions. The operator '<<' does the test I think I'm looking for > (b is contained in a). But how do I incorporate that into a constraint > on the table? > Sounds like you need a trigger to look for any tuples matching b << a. > Thanks, > > > Mark > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749