/ 
Database-Async-0.019
River stage one • 1 direct dependent • 1 total dependent
/ Database::Async::Query

NAME

Database::Async::Query - represents a single database query

SYNOPSIS

my $query = Database::Async::Query->new(
 db => Database::Async->new(...),
);

DESCRIPTION

A query:

  • has zero or more parameters

  • accepts zero or more input rows

  • returns zero or more output rows

  • can be prepared or direct

Creating queries

Queries are initiated from the Database::Async instance; users are not expected to instantiate Database::Async::Query objects directly.

my $query = $db->query('select 1');

Passing parameters

Normally additional parameters for placeholders are passed when creating the query instance:

my $query_with_parameters = $db->query('select name from users where id = ?', $id);

For prepared statements, query parameters can be bound and passed for each execution, see "Prepared queries" for more details.

Retrieving data

Methods prefixed with row_ each provide a Ryu::Source which emits events for each row returned from the query:

$db->query(q{select * from a_table})
 ->row_hashrefs
 ->each(sub {
  say "Had ID $_->{id} with name $_->{name}"
 })->await;

Direct queries

A direct query is of the form:

$db->query(q{select 1})

or

$db->query(q{select * from some_table where id = ?}, $id)

and has all the information required to start the query.

Prepared queries

When the same query needs to be executed multiple times with different parameters, it may be worth using a prepared query. This involves sending the SQL for the query to the server so that it can parse the text and prepare a plan. Once complete, you can then send a set of parameters and have the server execute and return any results.

A prepared query involves two steps.

First, the query is created:

my $query = $db->query(q{select * from some_table where id = ?});

Next, it will need to be prepared. This will fail if the query was provided any parameters when initially constructed:

$query->prepare(
 statement => 'xyz'
);

Some engines allow a statement parameter, others will ignore it.

After a call to "prepare", the query is marked as prepared and will support the "bind" and "execute" methods. Once the query is prepared, it is traditional to bind some variables to it:

$query->bind(
 $var1, $var2, ...
);

after which it can be executed:

$query->execute(
 portal => 'abc'
);

and any results can be extracted by the usual methods such as "row_hashrefs".

Again, some engines support named portals, others will ignore the parameter.

Since passing parameters is so common, you can combine the "bind" and "execute" steps by passing an arrayref to "execute":

$query->execute([ $first_value, ... ], portal => '');

Streaming of values via Ryu::Source is also supported:

$query->execute($src, portal => '');

Note that prepared queries will continue to emit values from the row_* source(s) until the query itself is discarded. The caller is expected to keep track of any required mapping from input parameters to output rows.

A full example might look something like this:

async sub name_by_user_id {
 my ($self, $id) = @_;
 my $q = await $self->{name_by_user_id} //= do {
  # Prepare the query on first call
  my $q = $self->db->query(q{select name from "site"."user" where id = ?});
  $q->prepare(
   statement => 'name_by_user_id'
  )
 };
 my ($name) = await $q->execute([ $id ])->single;
 return $name;
}

Custom engine features

Different engines support additional features or events.

Once a query is scheduled onto an engine, it will resolve the "engine" Future instance:

my $query = $db->query('select * from some_table');
my $engine = await $query->engine;
$engine->source('notification')
 ->map('payload')
 ->say;

Cancelling queries

In cases where you want to terminate a query early, use the "cancel" method. This will ask the engine to stop query execution if already scheduled. For a query which has not yet been assigned to an engine, the "cancel" method will cancel the schedule request.

Cursors

Cursors are handled as normal SQL queries.

$db->txn(async sub {
 my ($txn) = @_;
 await $txn->query(q{declare c cursor for select id from xyz})->void;
 say while await $txn->query(q{fetch next from c})->single;
 await $txn->query(q{close c})->void;
});

in

This is a Ryu::Sink used for queries which stream data to the server.

It's buffered internally.

db

Accessor for the Database::Async instance.

sql

The SQL string that this query would be running.

bind

A list of bind parameters for this query, can be empty.

start

Schedules this query for execution.

rows

Returns a Ryu::Source which will emit the rows from this query.

Each row is a Database::Async::Row instance.

Will call "start" if required.

single

Used to retrieve data for a query that's always going to return a single row.

Defaults to all columns, provide a list of indices to select a subset:

# should yield "a", "b" and "c" as the three results
print for await $db->query(q{select 'a', 'b', 'c'})->single->as_list;

# should yield just the ID column from the first row
print for await $db->query(q{select id, * from some_table})->single('id')->as_list;

Returns a Future which will resolve to the list of items.

AUTHOR

Tom Molesworth <TEAM@cpan.org>

LICENSE

Copyright Tom Molesworth 2011-2023. Licensed under the same terms as Perl itself.