[Dbix-class] inflators / defaltors

[Bernhard Graf]

On Monday 26 March 2007 22:56, Goetz Bock wrote:

> I would like to write an DBIx::Class tool for the sqlite3 database
> generated by ulogd-sqlite3.
>
> The basic setup is very simple, but there are some stumbling blocks:
> - IPs are stored as integer (should have been unsigned int, but my
>   sqlite returns signed (negative) integers ).
> - time is stored as an integer, too.
>
> I thought I could handle the IPs with an in-/de-flator:
>
> __PACKAGE__->inflate_column( 'ip_daddr' => {
>     inflate => sub { inet_ntoa( pack( 'N', shift ) ) },
>     deflate => sub { unpack( 'N', inet_aton( shift ) ) },
> });

Inflators work with objects only.

> I've not even tried with the timestamp (oob_time_sec) and would
> ultimately like to do queries like:
>
>     ->search( { ip_daddr => "80.254.137.0/24" } );
>
> what would be required to be converted into
>
> SELECT ...
> FROM ...
> WHERE ( ip_daddr > ? AND ip_daddr < ? ): '1358858496', '1358858751';

I'm not sure how well sqlite can optimize a query like this.
Also this looks like you want to store a row for every possible IP 
address... Maybe it would be smarter (in respect of memory consumption 
and database speed) to store IP networks as entity of base IP and mask 
(length) where at least the base IP should be indexed. But that depends 
on what you want to achieve and is not DBIC-related anyway.


> BTW: I've tried to use Net::IP but could not get my head arround it,
>      maybe almost 17h of up i should stop now and get some rest.

Using this (or another like NetAddr::IP) is the right approach for DBIC 
inflators.

HTH
-- 
Bernhard Graf