expand-expand.md

Proposal: Expanding expand functionality in Mojo::Pg

The expand method in Mojo::Pg::Results performs a really useful purpose, decoding json/jsonb values to perl structures. However, I believe it can be made more general.

The following is work that I would be happy to undertake to have implemented in Mojo::Pg core, but I wanted to check first to see if the work is

1. of value to other users of Mojo::Pg
2. not currently offered by some other means
3. not something that a current maintainer wishes to undertake

The problem

There exists many types in postgresql that currently are returned as strings by Mojo::Pg (and more rarely as numbers). The following code shows an example

$pg->db->query("SELECT '::1'::cidr")->array->[0];
   # => '::1/128'

On a more fundamental level, boolean values are changed to 0/1 values, which causes confusion when converted to JSON

# Would naively expect '{ "is_boolean": true }'
encode_json($pg->db->query("SELECT true as is_boolean")->hash);
    # => '{ "is_boolean" : 1 }'

A timestamp column is another example of a type that is stringified when extracted from a database query.

There exist perl objects that represent these types fairly accurately and using these objects instead of its string representation is preferable in many situations (the boolean example above being a very obvious one that doesn't need further elaboration.)

The solution

In some sense, the solution is already available: you just manually create the relevant object whenever you know it's in the columns returned

$pg->db->query("select id, range from subnets")->hashes->map( sub { $_->{range} = NetAddr::IP->new($_->{range}); $_; });

This is what I do for my current Mojolicious instances.

However, a much more elegant solution would be to incorporate it into the expand function

$pg->db->query('select id, range from subnets')->expand->hashes;

Possible implementation

The implementation would need to have the following properties

1. It needs to be backward compatible with existing code
2. It needs to not impact performance noticably for expand's current usage
3. It needs to be pluggable (eg IP addresses can be either NetAddr::IP or Net::IP objects)

Thus, I propose the following interface

$pg->expander( boolean => sub { shift ? JSON::PP::true : JSON::PP::false; } );
$pg->expander( cidr    => sub { NetAddr::IP->new(shift) } );
$pg->expander( json => \&MODIFIED_CURRENT_EXPAND_METHOD );
$pg->expander( 'timestamp with timezone' => sub { DateTime::Format::Pg->parse_datetime(shift) } );

# Remove expander
$pg->expander( cidr => undef );

# Use expander
ref $pg->expander('cidr')->('127.0.0.1');
    # => 'NetAddr::IP'

The default expander list would include json expansion for backwards compatibility, but would allow adding and removing expanding to your taste.

Prior art

DBIx::Class::InflateColumn is very feature-rich equivalent used for DBIx::Class. One thing that it does that I am not proposing is having a 'compactor', or 'deflate' method. Postgresql has its own automatic casting on assignment so so long as the object stringifies to something useful, it will work currently without any modification to Mojo::Pg's current codebase

# Works currently
$pg->db->query('INSERT INTO subnets (range) values (?)', NetAddr::IP->new('::/0'));

Corner cases and things to consider

Nested arrays

The following is a valid query.

$pg->db->query("SELECT array[array[array['::1', '127.0.0.1']]]::cidr[][][]")->expand->array->[0];

Arrays can be of arbitrary depth even if their type is fixed.

Similar, but not identical types

Some types are not identical, but so similar they are naturally grouped.

# These two columns end up as a perl hashref, even though they
# are different Postgresql types
$pg->db->query("SELECT '{}'::json, '{}'::jsonb")->expand->array;

One possible implementation could be

$pg->expander( [ qw(json jsonb) ] => \&MODIFIED_CURRENT_EXPAND_METHOD );

but what happens when you do the following?

# Should this now no longer expand jsonb columns?
$pg->expander( json => undef );

I don't have an answer currently for this.

Non round trip-able objects

Not really a corner case as it's just the nature of Postgresql's assignment casting. However, it probably should be documented that some intuitive types do not round-trip.

One example is the ipaddress type in the extension ip4r:

$pg->expander( ipaddress => sub { NetAddr::IP->new(shift) });

my $address = $pg->db->query("SELECT '127.0.0.1'::ipaddress")->expand->array->[0];

$pg->db->query('INSERT INTO subnets (range) values (?)', $address);
    # => DBD::Pg::st execute failed: ERROR:  invalid IP value: '127.0.0.1/32'

This could be fixed with a compactor as mentioned above, but I believe the more elegant solution would be subtyping NetAddr::IP with a new string representation.