Re: Database abstraction including data types ( $dbh->{pg_bool_tf} )

[erix]

in reply to Database abstraction including data types

Hardly a complete answer, but handy for the particular true/false problem that you mention is, on the postgres side: $dbh->{pg_bool_tf}, which lets you choose between t/f or 1/0:

$ perl -Mstrict -MDBI -E 'my$dbh=DBI->connect("dbi:Pg:");

$dbh->{pg_bool_tf} = 0; #  1/0
print $dbh->selectall_arrayref("select 1=1")->[0]->[0], "\n";

$dbh->{pg_bool_tf} = 1;  #  t/f 
print $dbh->selectall_arrayref("select 1=1")->[0]->[0], "\n"; # 
'

output: 

1
t

(See DBD::Pg $dbh attributes.)

[Anonymous Monk]

Aternatively do it all in the database:

SELECT
    CASE WHEN
        column_name
    THEN
        1
    ELSE
        0
    END AS column_name
FROM
    table
...

[Anonymous Monk]

By the way, that whole app looks like a good candidate for SQLite which could simplify the configuration and management a bit.

[mbethke]

I'm not sure how many people actually use SQLite for their Postfix installation. Postfix supports it but Postfixadmin doesn't so I suppose there has been little demand. It would probably work with mostly the same SQL as MySQL except for the DDL. But given that I'd have to support the "big ones" too as that's what most people use, I don't quite see how it would simplify things?

[mbethke]

Now that's cool, hadn't even thought that it could have been abstracted on such a low level. That may just be enough for this particular application unless I run into other incompatibilities, which looks unlikely. Thank you!