On Wed, May 13, 2015 at 02:33:02PM -0400, Greg Sabino Mullane wrote:
> On Tue, May 12, 2015 at 04:26:08PM +0100, Tim Bunce wrote:
>
> > Obviously we'd need to discuss what form that information might take.
> > The slowest but most flexible option would be to simply provide hooks:
> >
> > $dbh->{pg_type_mumble} = {
> > 12345 => {
> > perl_to_pg => sub { ... },
> > pg_to_perl => sub { ... },
> > },
> > ...
> > };
> >
> > which would be a handy fallback anyway.
> >
> > Some other ideas:
> >
> > 12345 => 54321, # treat type 12345 like type 54321
> >
> > pg_to_perl => $PG_TYPE_TREAT_AS_ARRAY, # DBD::Pg exported constant
>
> Having the user know the numeric ids of the types (both theirs and the
> system ones) seems unreasonable - so we could support it, but also allow
> data type names when we can).
Of course, so long as the names can be reliably and quickly converted to
the underlying real integer values. But that requires a db query so
the API ought to allow the mapping to be supplied so it doesn't have to
be recalculated for each connection to the same db. Part of my point is
that that kind of information could be off-loaded from DBD::pg into a
supporting module, possibly supplied with DBD::Pg.
$dbh->{pg_type_name_map} => { 'foobar' => 98765, ... };
> I think being able to handle arrays would solve 90% of the current
> problems, so some simple shortcut as you mention above would be
> good, maybe even something like:
>
> $dbh->pg_type_is_array('foobar');
which could be generalized to:
$dbh->pg_type_treatment('foobar', $numeric_id_of_array_type);
which could be further generalized to:
$dbh->pg_type_treatment('foobar', 'array');
which could be further generalized to optionally take a hash ref:
$dbh->pg_type_treatment('foobar', { hash of type handling attributes });
and then we're almost back to the hash proposal.
A db query would be needed to map 'foobar' to the corresponding id.
That query couldn't be cached, unlike my attribute API proposal above.
(You could allow typename to be treated as an integer id if it looks like
one, which would avoid the db query for each connection but that's a bit
hackinsh and is pushing complexity onto the app.)
> > I wouldn't expect this to do much as it would be implicit:
> >
> > $sth->bind_col(1, \$foo, { pg_type => $pg_type_id });
> >
> > but this might be handy to trigger appropriate serialization to postgres:
> >
> > $sth->bind_param(1, $foo, { pg_type => $pg_type_id });
>
> Yeah, that could be another approach, but I like the first (dbh) version
> a little better.
They are related. After the app has told DBD::Pg how to handle the type:
$dbh->{pg_type_mumble} = { 9876 => ...how-to-handle-that-type... };
then it can specify that a specific value is of that type:
$sth->bind_param(1, $foo, { pg_type => 9876 });
> > p.s. I'm not really familar with this part of DBD::Pg or the type
> > mechanism in libpq etc so I may be talking nonsense here :)
>
> libpq pretty much stays out of the way except to return us a numeric
> type for each returned column. We map this back to type information
> stored in some structs (see types.c). We look at this when slinging
> the data back and forth to see if we are dealing with an array. Presumably
> we could someday internalize a hash-like mapping for things like hstore
> and json (and even direct mapping to JSON someday).
>
> I still like the idea of auto-discovery by DBD::Pg, maybe just a simple
> command
> that scans all of the custom types and discovers which are arrays. Perhaps
> to supplement the above. As a one-time per session and on-demand command,
> it shouldn't be too expensive.
Arrays are just one use-case, albeit the most common one. I'm sure many
people would like JSON, for example, to "just work" using their
preferred JSON module, and some would like Geometry types to "just work"
using eg https://metacpan.org/pod/distribution/Geo-JSON/README.pod
Whatever API is added ought to enable support for these use-cases, even
if they're not supported initially. Though allowing at least callback
hooks for serialization and deserialization opens the door for other
people to add support for specific types, like geometry, themselves.
Tim.
