[Dbix-class] inflators / defaltors

[Goetz Bock]

Dear list,

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 ) ) },
});

what worked for retrieving. I will not need to update an existing field, 
but would like to query using normal IPs:

my $records = $schema->resultset('ulog')->search( 
    { 
        tcp_dport => 80, 
        ip_daddr  => "80.254.137.241",
    }, 
);

but the IP is used in the SQL:

SELECT me.raw_mac, me.oob_time_sec, me.ip_saddr, me.ip_daddr, 
    me.ip_protocol, me.ip_totlen, me.tcp_sport, me.tcp_dport, 
    me.udp_sport, me.udp_dport, me.udp_len, me.icmp_type, me.icmp_code, 
    me.icmp_echoid, me.icmp_echoseq, me.icmp_gateway, me.icmp_fragmtu 
FROM ulog me 
WHERE ( ip_daddr = ? AND tcp_dport = ? ): '80.254.137.241', '80'


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 could do all this in the script using the schema, and I probably will 
have to, unless i can figure out how to do this in the table's class.

Thanks for any pointer,
    Goetz.

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.
-- 
/"\ Goetz Bock at blacknet dot de  --  secure mobile Linux everNETting
\ /       (c) 2007 Creative Commons, Attribution-ShareAlike 2.0 de
 X   [ 1. Use descriptive subjects - 2. Edit a reply for brevity -  ]
/ \  [ 3. Reply to the list - 4. Read the archive *before* you post ]