On Tue, Dec 05, 2006 at 08:21:35PM +1100, Kevin Waterson wrote:
> This one time, at band camp, "Trevor Talbot" <[EMAIL PROTECTED]> wrote:
>
>
> > I'd store the IPs in the DB in integer form as Lloyd suggested, if
> > range queries are your goal. Do conversion to and from display format
> > in the application.
>
> Yes, they are stored as INTEGER. and the conversion outside sqlite
> is simple. However, I still need to SELECT the range before displaying.
If they are stored as INTEGER then it's easy. This should find all the
hosts in some network whose name is a parameter to this query ('?'):
SELECT h.name, h.ip FROM hosts h, networks n WHERE
n.name = ? AND h.ip BETWEEN n.start AND n.end;
or
SELECT h.name, h.ip FROM hosts h, networks n WHERE
n.name = ? AND
h.ip BETWEEN n.prefix AND (n.prefix + (1 << (32 - n.prefix_length)));
or
SELECT h.name, h.ip, FROM hosts h, networks n WHERE
n.name = 'foo' AND h.ip % n.pf < (1<<(32 - n.pfl));
SQLite could probably use a bitstring type and bitstring comparison/
manipulation functions. CIDR doesn't quite go away with IPv6...
sqlite> create table networks (name, pf, pfl);
sqlite> create table hosts (name, ip);
sqlite> insert into networks values ('foo', 3232236032, 25);
sqlite> select * from networks;
foo|3232236032|25
sqlite> insert into networks values ('bar', 3232236544, 24);
sqlite> insert into networks values ('foobar', 3232236800, 24);
sqlite> insert into hosts values ('x1', 3232236042);
sqlite> insert into hosts values ('x2', 3232236043);
sqlite> insert into hosts values ('x3', 3232236554);
sqlite> insert into hosts values ('x4', 3232236821);
sqlite> SELECT h.name, ip FROM hosts h, networks n WHERE
...> n.name = 'foo' AND h.ip BETWEEN n.pf AND (n.pf + (1 << (32 - n.pfl)));
x1|3232236042
x2|3232236043
sqlite> SELECT h.name, ip FROM hosts h, networks n WHERE
...> n.name = 'bar' AND h.ip BETWEEN n.pf AND (n.pf + (1 << (32 - n.pfl)));
x3|3232236554
sqlite>
Nico
--
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
