| 1 | package BoardStreams::DBUtil; | ||
| 2 | |||
| 3 | use Mojo::Base -strict, -signatures, -async_await; | ||
| 4 | |||
| 5 | use BoardStreams::Exceptions qw/ db_duplicate_error db_error /; | ||
| 6 | use BoardStreams::Util 'belongs_to'; | ||
| 7 | |||
| 8 | use Mojo::Promise; | ||
| 9 | use Syntax::Keyword::Try; | ||
| 10 | use Carp 'croak'; | ||
| 11 | |||
| 12 | use Exporter 'import'; | ||
| 13 | our @EXPORT = qw/ | ||
| 14 | query_throwing_exception_object_p query_throwing_exception_object | ||
| 15 | exists_p row_exists | ||
| 16 | /; | ||
| 17 | |||
| 18 | our $VERSION = "v0.0.36"; | ||
| 19 | |||
| 20 | async sub exists_p ($db, $table_name, $where = undef, $options = undef) { | ||
| 21 | my ($sql, @bind) = $db->pg->abstract->select($table_name, undef, $where, $options); | ||
| 22 | $sql = "SELECT EXISTS ($sql)"; | ||
| 23 | my $results = await $db->query_p($sql, @bind); | ||
| 24 | return $results->arrays->[0][0]; | ||
| 25 | } | ||
| 26 | |||
| 27 | sub row_exists ($db, $table_name, $where = undef, $options = undef) { | ||
| 28 | my ($sql, @bind) = $db->pg->abstract->select($table_name, undef, $where, $options); | ||
| 29 | $sql = "SELECT EXISTS ($sql)"; | ||
| 30 | return $db->query($sql, @bind)->arrays->[0][0]; | ||
| 31 | } | ||
| 32 | |||
| 33 | async sub query_throwing_exception_object_p ($db, $action, $args) { | ||
| 34 | belongs_to($action, [qw/ insert_p update_p /]) | ||
| 35 | or croak "invalid action: '$action'"; | ||
| 36 | $action =~ s/_p\z//; | ||
| 37 | |||
| 38 | my $p = Mojo::Promise->new; | ||
| 39 | |||
| 40 | $db->$action( | ||
| 41 | @$args, | ||
| 42 | sub ($_db, $err, $results) { | ||
| 43 | if ($err) { | ||
| 44 | my $sth = $results->sth; | ||
| 45 | if ($sth->err) { | ||
| 46 | # sth error codes are described here: https://www.postgresql.org/docs/14/errcodes-appendix.html | ||
| 47 | if ($sth->state eq '23505') { | ||
| 48 | # pg duplicate key error | ||
| 49 | my ($key_name) = $sth->errstr =~ /\"(.+?)\"/; | ||
| 50 | $err = db_duplicate_error $key_name; | ||
| 51 | } elsif ($sth->state !~ /^(00|01)/) { | ||
| 52 | # neither successful nor just a warning | ||
| 53 | $err = db_error undef, { | ||
| 54 | state => $sth->state, | ||
| 55 | errstr => $sth->errstr, | ||
| 56 | }; | ||
| 57 | } | ||
| 58 | } | ||
| 59 | $p->reject($err); | ||
| 60 | } else { | ||
| 61 | $p->resolve($results); | ||
| 62 | } | ||
| 63 | }, | ||
| 64 | ); | ||
| 65 | |||
| 66 | return $p; | ||
| 67 | } | ||
| 68 | |||
| 69 | sub query_throwing_exception_object ($db, $action, $args) { | ||
| 70 | belongs_to($action, [qw/ insert update /]) | ||
| 71 | or croak "invalid action: '$action'"; | ||
| 72 | |||
| 73 | my $results = do { | ||
| 74 | try { | ||
| 75 | $db->$action(@$args); | ||
| 76 | } catch ($err) { | ||
| 77 | my $dbh = $db->dbh; | ||
| 78 | if ($dbh->err) { | ||
| 79 | if ($dbh->state eq '23505') { | ||
| 80 | # pg duplicate key error | ||
| 81 | my ($key_name) = $dbh->errstr =~ /\"(.+?)\"/; | ||
| 82 | $err = db_duplicate_error $key_name; | ||
| 83 | } elsif ($dbh->state !~ /^(00|01)/) { | ||
| 84 | # neither successful nor just a warning | ||
| 85 | $err = db_error undef, { | ||
| 86 | state => $dbh->state, | ||
| 87 | errstr => $dbh->errstr, | ||
| 88 | }; | ||
| 89 | } | ||
| 90 | } | ||
| 91 | die $err; | ||
| 92 | }; | ||
| 93 | }; | ||
| 94 | |||
| 95 | return $results; | ||
| 96 | } | ||
| 97 | |||
| 98 | 1; |