NAME

DBIx::Squirrel::Iterator - an iterator over a statement's results

SYNOPSIS

my $it = $dbh->iterate(
    'SELECT id, name FROM artist WHERE name LIKE :pattern',
    pattern => 'A%',
    sub ($row) { $row->[1] },
);
while (my($name) = $it->next) {
    say $name;
}

# A stage can leave its signature out, and use $_
my @names = $dbh->iterate('SELECT id, name FROM artist', sub { $_->[1] })->all;

my $sth = $dbh->prepare(
    'SELECT * FROM artist WHERE id = $1',
    {ekorn_slice => {}},
);
my $artist = $sth->iterate(42)->single;

DESCRIPTION

An iterator executes a statement and hands out its rows one at a time. Each row is run through a pipeline of stages, as Ekorn::Pipeline runs them. A stage can change a row, drop it, return several values in its place, or stop the iteration with DBIx::Squirrel::Stop. A result is whatever comes out of the stages. With no stages, it's the row. A stage can ask about the result it's making with DBIx::Squirrel::Context.

An iterator is made by "iterate" in DBIx::Squirrel::db or "iterate" in DBIx::Squirrel::st. It executes the statement when its first result is asked for, and finishes the statement when the results run out.

Rows are fetched with "fetchall_arrayref" in DBI, 2 at first, the least that tells whether there's a second, then twice as many each time a fetch fills its batch, up to 64. Past 64, each fetch's cost is spread so thin that a bigger batch reads hardly any faster, and holds more of a wide row's data. "all" without stages fetches the rest at once. Each row is an array, or, with the ekorn_slice attribute, in the shape it gives:

$dbh = DBIx::Squirrel->connect($dsn, $user, $pass, {ekorn_slice => {}});
$sth = $dbh->prepare($statement, {ekorn_slice => {}});

ekorn_slice is a slice as "fetchall_arrayref" in DBI takes it: an array reference, for rows as arrays, or a hash reference, for rows as hashes. Given to connect, it's the default for each statement the handle prepares. Given to prepare, it's for that statement.

A statement handle can have only one iterator going through its results at a time.

METHODS

new

$it = DBIx::Squirrel::Iterator->new($sth, @bind_values, @stages);

Returns an iterator over the rows of $sth, a DBIx::Squirrel::st. It doesn't execute the statement. When it does, it's executed with @bind_values, in any form "execute" in DBIx::Squirrel::st takes. Each row is run through @stages, the code references or pipelines at the end of the arguments.

next

while (my($result) = $it->next) { ... }

Returns the next result, or an empty list when there are no more. A stage can return undef as a result. So read next in list context, as above, rather than testing whether what it returns is defined.

Once a stage dies, the iterator has ended. next returns an empty list on every call after, all returns nothing, and the statement is finished. "reset" runs it again, from the start.

all

@results = $it->all;
$results = $it->all;

Returns the results still to come, reading the run to its end: as a list, or, asked for one value, an array reference of them. Either way, the statement is finished, and next returns nothing after.

Without stages, the rows are the results. So all takes what's left of the batch fetched last, and fetches the rest with one fetchall_arrayref. That's nearly as quick as the DBI's own selectall_arrayref. With stages, each row goes through them in turn, which costs a little for each row. So to change every row of a large result, when nothing needs to stop early, map over all is quicker than a stage:

my @names = map { $_->[0] } $dbh->iterate('SELECT name FROM artist')->all;

Use stages when the rows should come one at a time, from next, or may stop early, or should go with the statement, as in a Sugar statement.

first

$result = $it->first;

Returns the next result, or undef if there's none, and finishes the statement. next returns nothing after, until "reset".

single

$result = $it->single;

Returns the next result, or undef if there's none, and finishes the statement. If there's another, it warns, and returns the first: for a statement that lost its LIMIT 1. next returns nothing after, until "reset".

reset

$it = $it->reset;
$it = $it->reset(@bind_values, @stages);

Executes the statement again, ready to iterate from its first row, and returns the iterator. If the statement is still active, it's finished first, and whatever the run before held goes with it. Bind values given replace those given before. Stages given are added to those given to "new". Both are kept until others are given.

A statement taking a list (see "Lists" in DBIx::Squirrel::st) is prepared again, on a new statement handle, if a list given has a different number of values from the one before. "sth" then returns the new handle.

ended

$how = $it->ended;

Returns how the last run ended, as "ended" in Ekorn::Pipeline::Iterator says: done if the rows ran out, stopped if the run was cut short, by "first", by "single" finding a second result, or by a stage that stopped, and died if a stage died. While a run is going, and before one has started, it returns nothing. "reset" starts a new run, so it returns nothing after one.

A run read with | or @{} is the iterator's last run, too (see "Operators").

column_names

@names = $it->column_names;

Returns the names of the statement's columns, as fetchrow_hashref would key them, following the FetchHashKeyName attribute. If the statement hasn't been executed, it's executed first. The names are read when it's executed, as some drivers, such as DBD::mysql, give none once the last row is fetched. A statement without columns has no names, and nor does one that couldn't be executed. A stage can ask for them through "iterator" in DBIx::Squirrel::Context.

sth

$sth = $it->sth;

Returns the statement handle. It changes if "reset" prepares a statement taking a list again.

EKORN_SOURCE

An iterator is a source, as Ekorn::Pipeline takes one: a run reading it executes the statement again, from its first row, through the iterator's stages, and passes on the statement's columns as the run's context. It's called by Ekorn::Pipeline, not by you.

Operators

An iterator overloads | and @{} as Ekorn::Pipeline's own sources do, with Ekorn::Pipeline::Operators. They're in force only where you write use Ekorn::Pipeline::Operators. Elsewhere, they croak.

use Ekorn::Pipeline::Operators;
use DBIx::Squirrel::Stages::Rows;
use Ekorn::Pipeline::Sinks qw(count_items keep_all);

my $it    = $dbh->iterate('SELECT name FROM artist', pluck);
my $names = $it | keep_all;       # an array reference
my $n     = $it | count_items;    # a number
my @names = @$it;                 # every result

| always gives one value. Perl calls an overloaded | for one value, so a sink that keeps many, such as keep_all, gives an array reference, even in a list assignment:

my @names = $it | keep_all;       # one element: the array reference
my @names = @{ $it | keep_all };  # the names

Each use is a run of its own, from the first row, through the iterator's stages, as "EKORN_SOURCE" says. So $it->[0], scalar @$it and $#$it read every row to give one row, a count or an index, and two uses run the statement twice. for (@$it) reads every row before the loop starts, where "next", in a while, gives each as it comes.

The run it starts is the iterator's. It ends a run the iterator had going, so "next" returns nothing after, and "ended" says how the | or @{} run ended, until "reset".

DIAGNOSTICS

Expected a DBIx::Squirrel statement handle

"new" wasn't given a DBIx::Squirrel::st.

Expected only one item, but there were more

A warning: "single" found a second result, and returned the first.

Expected the statement handle's other iterator to be finished

The iterator's statement handle has another iterator, with results left to hand out. Read that one to the end, or let it go out of scope, before executing this one.

SEE ALSO

DBIx::Squirrel, DBIx::Squirrel::Context, Ekorn::Pipeline, Ekorn::Pipeline::Operators

AUTHOR

Iain Campbell <cpanic@cpan.org>

LICENSE

Copyright (c) 2024-2026 by Iain Campbell.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.