> On 16 Nov 2013, at 1:47 pm, Jon Haddad <[email protected]> wrote: > Instead of determining your table first, you should figure out what you want > to ask Cassandra.
Thanks Jon, Perhaps I should have been more clear. I need to efficiently look up the location of an IP address. >> On Nov 15, 2013, at 4:36 PM, Jacob Rhoden <[email protected]> wrote: >> >> Hi Guys, >> >> It occurs to me that someone may have done this before and be willing to >> share, or may just be interested in helping work out it. >> >> Assuming a database table where the partition key is the first component of >> a users IPv4 address, i.e. (ip=100.0.0.1, part=100) and the remaining three >> parts of the IP address become a 24bit integer. >> >> create table location( >> part int, >> start bigint, >> end bigint, >> country text, >> city text, >> primary key (part, start, end)); >> >> // range 100.0.0.0 - 100.0.0.10 >> insert into location (part, start, end, country, city) >> values(100,0,10,'AU','Melbourne’); >> >> // range 100.0.0.11 - 100.0.0.200 >> insert into location (part, start, end, country, city) >> values(100,11,200,'US','New York’); >> >> // range 100.0.0.201-100.0.0.255 >> insert into location (part, start, end, country, city) >> values(100,201,255,'UK','London'); >> >> What is the appropriate way to then query this? While the following is >> possible: >> >> select * from location where part=100 and start<=30 >> >> What I need to do, is this, which seems not allowed. What is the correct way >> to query this? >> >> select * from location where part=100 and start<=30 and end>=30 >> >> Or perhaps I’m going about this all wrong? Thanks! >
