Re: [sqlite] IPv{4,6} addressing extension

[Niall O'Reilly]

On 27 Feb 2012, at 10:51, Alexey Pechnikov wrote:

> You can use integer representation of IPv4 addresses as your "internal
> format" for sorting and sumilar tasks:

Thanks, Alexey.

I know that, but it's an approach which fragments the problem
which I very much want to unify: it's not common to both IP
versions, and it leaves the representation of prefixes mainly
to the application.

Best regards,
Niall O'Reilly



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] IPv{4,6} addressing extension

[Alexey Pechnikov]

You can use integer representation of IPv4 addresses as your "internal
format" for sorting and sumilar tasks:

SELECT IP2INT('0.0.0.0');
==>0
SELECT IP2INT('192.168.1.1');
==>3232235777
SELECT IP2INT('255.255.255.255');
==>4294967295

The integer value of first IP address by mask can be obtained as
SELECT NETFROM('192.168.1.1',32);
==>3232235777

And NETTO() function returns integer value of last IP address by mask.

So count of addresses calculation is simple:
SELECT NETTO('192.168.1.1/24') - NETFROM('192.168.1.1/24');
==>255


See module documentation for other functions. The home page of extension is
http://sqlite.mobigroup.ru/wiki?name=ext_inet

2012/2/27 Niall O'Reilly :
> Hello.
>
> For a current project, I need an extension to SQLite which supports
> IP addresses and routing/subnet prefixes.  Before I start building
> one, I'ld be glad to learn of any that are out there, other than
> those mentioned at either of the following URLs:
>
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg35680.html
>
> http://freebsd.ntu.edu.tw/FreeBSD/distfiles/sqlite-ext/ipv4-ext.c
>
> From what I can see, neither of these supports IPv6, nor provides
> a sortable encoding for Internet addresses and/or routes.
>
> I'm looking for the following functionality:
>
>      - feature parity between IPv4 and IPv6;
>
>      - an internal format which allows sorting a collection of
>        prefixes and addresses so that a containing prefix is
>        sorted before a more specific contained prefix, and this
>        before a contained address;
>
>      - functions to convert between display and internal formats
>        for representing IP addresses and prefixes;
>
>      - functions for extracting the bounding addresses of a
>        prefix;
>
>      - functions for testing membership (address or prefix in
>        prefix);
>
>      - functions for extracting the count of addresses covered
>        by a prefix (perhaps only for IPv4, as a 64-bit integer
>        isn't adequate for doing this with IPv6).
>
> I expect to take inspiration from the extensions cited above, as
> well as from the CPAN Net::IP module.
>
> If I'm about to re-invent the wheel, I'ld appreciate a warning.
>
>
> Best regards,
> Niall O'Reilly
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] IPv{4,6} addressing extension

[Niall O'Reilly]

Hello.

For a current project, I need an extension to SQLite which supports
IP addresses and routing/subnet prefixes.  Before I start building
one, I'ld be glad to learn of any that are out there, other than
those mentioned at either of the following URLs:

http://www.mail-archive.com/sqlite-users@sqlite.org/msg35680.html

http://freebsd.ntu.edu.tw/FreeBSD/distfiles/sqlite-ext/ipv4-ext.c

From what I can see, neither of these supports IPv6, nor provides
a sortable encoding for Internet addresses and/or routes.

I'm looking for the following functionality:

  - feature parity between IPv4 and IPv6;

  - an internal format which allows sorting a collection of
prefixes and addresses so that a containing prefix is
sorted before a more specific contained prefix, and this
before a contained address;

  - functions to convert between display and internal formats
for representing IP addresses and prefixes;

  - functions for extracting the bounding addresses of a
prefix;

  - functions for testing membership (address or prefix in
prefix);

  - functions for extracting the count of addresses covered
by a prefix (perhaps only for IPv4, as a 64-bit integer
isn't adequate for doing this with IPv6).

I expect to take inspiration from the extensions cited above, as
well as from the CPAN Net::IP module.

If I'm about to re-invent the wheel, I'ld appreciate a warning.


Best regards,
Niall O'Reilly
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users