Mojo::Pg::Database::Role::MyApp

MyApp.pm

package Mojo::Pg::Database::Role::MyApp;

use v5.42;
use Mojo::Base -role, -async_await;

use Mojo::Promise;
use Crypt::PRNG 'random_string';
use List::AllUtils qw/ indexes uniq /;
use Safe::Isa '$_can';
use Tie::IxHash;

around 'query_p' => async sub ($orig, $self, @args) {
    my @indexes = indexes { $_->$_can('then') } @args;
    @args[@indexes] = map $_->[0], await Mojo::Promise->all(@args[@indexes]) if @indexes;
    $self->$orig(@args);
};

async sub exists_p ($db, @args) {
    my $abstract = $db->pg->abstract;
    splice @args, 1, 0, undef;
    my ($sql, @bind) = $abstract->select(@args);
    return !!(await $db->query_p(qq{SELECT EXISTS ($sql)}, @bind))->arrays->[0][0];
}

sub subselect($db, @args) {
    my $abstract = $db->pg->abstract;
    my ($sql, @bind) = $abstract->select(@args);
    return \["($sql)", @bind];
}

async sub binary_insert_p ($db, $table, $kv_s, @rest) {
    my %temp_kv_s = %$kv_s;
    my %translation_table;

    foreach my ($key, $value) (%temp_kv_s) {
        eval { exists $value->{type} and exists $value->{value} and %$value == 2 } or next;
        my $temp_value = random_string(101);
        $translation_table{$temp_value} = $value;
        $temp_kv_s{$key} = $temp_value;
    }

    my ($sql, @bind) = $db->pg->abstract->insert($table, \%temp_kv_s, @rest);
    $_ = exists $translation_table{$_} ? $translation_table{$_} : $_ foreach @bind;

    return $db->query_p($sql, @bind);
}

async sub insert_multi_p ($db, $table, $array_of_kv_s, @rest) {
    my $abstract = $db->pg->abstract;

    my @all_keys = sort(uniq(map {keys %$_} @$array_of_kv_s));

    my $only_start_str;
    my @all_bind;
    my @values_strings;
    foreach my $_kv (@$array_of_kv_s) {
        my $kv = { %$_kv };
        foreach my $key (@all_keys) {
            exists $kv->{$key} or $kv->{$key} = \'DEFAULT';
        }

        my @sorted_kv = map {( $_ => $kv->{$_} )} @all_keys;
        tie my %sorted_kv, 'Tie::IxHash', @sorted_kv;

        my ($sql, @bind) = $abstract->insert($table, \%sorted_kv);
        my ($keys_str, $values_str) = $sql =~ /
            ^ \s* (
                INSERT \s+ INTO \s+ \S+? \s+
                \( (?:\s*\S+\s*,)* \s* \S+ \s* \) \s+
                VALUES
            ) \s+
            ( \( .+? \) )
            \s* \z
        /xi;
        $keys_str and $values_str or die "invalid sql";
        $only_start_str //= $keys_str;
        $keys_str eq $only_start_str or die 'problem with Tie::IxHash and Mojo::Pg';
        push @values_strings, $values_str;
        push @all_bind, @bind;
    }

    my ($dummy_sql, @rest_bind) = $abstract->insert(
        'aaa',
        { a => \'NULL' },
        @rest,
    );
    my $rest_sql = $dummy_sql =~ s/^.*?\bVALUES\s+\(.*?\)//r;
    push @all_bind, @rest_bind;

    my $final_sql = "$only_start_str "
        . join(', ', @values_strings)
        . " $rest_sql";

    return $db->query_p($final_sql, @all_bind);
}

Allows for code like this:

await $db->insert_multi_p(
    'aaa',
    [
        { name => 'alex' },
        { name => 'peter' },
        { name => 'john', surname => 'travolta' },
        { name => 'mary' },
        { name => 'paul' },
    ],
    {
        on_conflict => [
            ['name'] => { count => \'aaa.count + 1' },
        ],
    },
);

await $db->insert_multi_p(
    'aaa',
    [
        { name => 'alex2' },
        { name => 'peter2' },
        { name => 'john2' },
        { name => 'mary2' },
        { name => $db->subselect('aaa', 'id', { name => 'alex' }) },
    ]
);