Phil White wrote:
> On Sun, 9 Dec 2007 05:45:10 -0800
> Phil Pennock <exim-users@???> wrote:
>
>>> Which, sadly, doesn't work quite as expected:
>>> dotquad addr = 131.111.8.192
>>> decimal addr = -2089875264
>>> which means approximately 50% of my mail is not being flagged
>>> correctly.
>> 32-bit arithmetic, signed. I suspect you knew that ...
>
> Yep.
>
>>> Is there another way I can achieve the desired effect?
>> Use an embedded Perl interpreter, returning a string? I don't see
>> that you'll be using the result directly within Exim, so a string
>> should be fine, right? Math::BigInt, perhaps via Net::IP.
>>
>> A bit heavyweight though. What do you need this value in decimal for?
>
> warn
> message = X-Info: Return = \
> ${lookup mysql { SELECT value FROM system WHERE \
> ip_addr=$acl_c5 } \ {$value}{0} \
> }
>
> Database that I am trying to access stores as decimal IP, and it is
> going to be a pain to change
>
> Cheers.
> Phil
>
Not really. Aside from a very few well-documented DB-specific SQL usages, mysql
calls 'normally' used in Exim and pgsql calls are for the most part functionally
identical as submitted (preferably quoted) to the backend DB. Often even the
configure file names need no change (in EITHER direction). ELSE a pass to
substitute same, if only as a reminder: mysql -> pgsql.
The call to the DB is slightly different (I use sockets, not IP, FWIW).
Likewise not hard to move *most* tables between the two. In either direction.
PostgreSQL will install and run just about anywhere MySQL will.
But 'why bother' is that PostgreSQL *has* native datatypes for IP and CIDR
notation. Storage, handling, conversion, query - even computation.
Ex: From a PostgreSQL blacklist populated from within Exim acl's -
mail=# \d brownlist
Table "public.brownlist"
Column | Type | Modifiers
----------+-----------------------------+-----------
pg_when | timestamp without time zone |
pg_why | text |
pg_ip | cidr |
pg_host | text |
pg_where | text |
pg_notes | text |
mail=# select pg_when, pg_ip, pg_host from brownlist limit 4;
pg_when | pg_ip | pg_host
--------------------+------------------+-----------------------------------------
2006-10-07 03:05:48 | 66.88.16.251/32 | w251.z066088016.sjc-ca.dsl.cnc.net
2006-10-07 03:11:55 | 82.254.97.83/32 | lns-bzn-26-82-254-97-83.adsl.proxad.net
2006-10-07 03:25:44 | 204.127.192.84/32| rwcrmhc14.comcast.net
2006-10-07 03:40:59 | 84.109.30.233/32 | bzq-84-109-30-233.red.bezeqint.net
(4 rows)
==============
Not shown:
- pg_where is the RBL or other source confirming some of the listings.
- pg_why covers the reason-code for the acl putting it into the table.
Not a hard move to justify if you need those.
Bill
