1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230
|
package Mojo::SQLite::Results;
use Mojo::Base -base;
use Mojo::Collection;
use Mojo::JSON 'from_json';
use Mojo::Util 'tablify';
our $VERSION = '3.009';
has [qw(db sth)];
sub new {
my $self = shift->SUPER::new(@_);
($self->{sth}{private_mojo_refcount} //= 0)++;
return $self;
}
sub DESTROY {
my $self = shift;
return() unless my $sth = $self->{sth};
$sth->finish unless --$sth->{private_mojo_refcount};
}
sub array { ($_[0]->_expand($_[0]->sth->fetchrow_arrayref))[0] }
sub arrays { _collect($_[0]->_expand(@{$_[0]->sth->fetchall_arrayref})) }
sub columns { shift->sth->{NAME} }
sub expand {
my ($self, %expands) = @_;
for my $type (keys %expands) {
my @cols = ref $expands{$type} eq 'ARRAY' ? @{$expands{$type}} : $expands{$type};
++$self->{expand}{$type}{$_} for @cols;
}
return $self;
}
sub finish { shift->sth->finish }
sub hash { ($_[0]->_expand($_[0]->sth->fetchrow_hashref))[0] }
sub hashes { _collect($_[0]->_expand(@{$_[0]->sth->fetchall_arrayref({})})) }
sub last_insert_id { shift->{last_insert_id} // 0 }
sub rows { shift->sth->rows }
sub text { tablify shift->arrays }
sub _collect { Mojo::Collection->new(@_) }
sub _expand {
my ($self, @rows) = @_;
return @rows unless $self->{expand} and $rows[0];
if (ref $rows[0] eq 'HASH') {
my @json_names = keys %{$self->{expand}{json}};
for my $r (@rows) { $r->{$_} = from_json $r->{$_} for grep { $r->{$_} } @json_names }
} else {
my $cols = $self->columns;
my @json_idxs = grep { $self->{expand}{json}{$cols->[$_]} } 0..$#$cols;
for my $r (@rows) { $r->[$_] = from_json $r->[$_] for grep { $r->[$_] } @json_idxs }
}
return @rows;
}
1;
=head1 NAME
Mojo::SQLite::Results - Results
=head1 SYNOPSIS
use Mojo::SQLite::Results;
my $results = Mojo::SQLite::Results->new(sth => $sth);
$results->hashes->map(sub { $_->{foo} })->shuffle->join("\n")->say;
=head1 DESCRIPTION
L<Mojo::SQLite::Results> is a container for L<DBD::SQLite> statement handles
used by L<Mojo::SQLite::Database>.
=head1 ATTRIBUTES
L<Mojo::SQLite::Results> implements the following attributes.
=head2 db
my $db = $results->db;
$results = $results->db(Mojo::SQLite::Database->new);
L<Mojo::SQLite::Database> object these results belong to.
=head2 sth
my $sth = $results->sth;
$results = $results->sth($sth);
L<DBD::SQLite> statement handle results are fetched from.
=head1 METHODS
L<Mojo::SQLite::Results> inherits all methods from L<Mojo::Base> and implements
the following new ones.
=head2 new
my $results = Mojo::SQLite::Results->new;
my $results = Mojo::SQLite::Results->new(sth => $sth);
my $results = Mojo::SQLite::Results->new({sth => $sth});
Construct a new L<Mojo::SQLite::Results> object.
=head2 array
my $array = $results->array;
Fetch next row from L</"sth"> and return it as an array reference. Note that
L</"finish"> needs to be called if you are not fetching all the possible rows.
# Process one row at a time
while (my $next = $results->array) {
say $next->[3];
}
=head2 arrays
my $collection = $results->arrays;
Fetch all rows from L</"sth"> and return them as a L<Mojo::Collection> object
containing array references.
# Process all rows at once
say $results->arrays->reduce(sub { $a + $b->[3] }, 0);
=head2 columns
my $columns = $results->columns;
Return column names as an array reference.
# Names of all columns
say for @{$results->columns};
=head2 expand
$results = $results->expand(json => 'some_json');
$results = $results->expand(json => ['some_json','other_json']);
Decode specified fields from a particular format to Perl values for all rows.
Currently only the C<json> text format is recognized. The names must exactly
match the column names as returned by L</"columns">; it is recommended to use
explicit aliases in the query for consistent column names.
# Expand JSON
$results->expand(json => 'json_field')->hashes->map(sub { $_->{foo}{bar} })->join("\n")->say;
=head2 finish
$results->finish;
Indicate that you are finished with L</"sth"> and will not be fetching all the
remaining rows.
=head2 hash
my $hash = $results->hash;
Fetch next row from L</"sth"> and return it as a hash reference. Note that
L</"finish"> needs to be called if you are not fetching all the possible rows.
# Process one row at a time
while (my $next = $results->hash) {
say $next->{money};
}
=head2 hashes
my $collection = $results->hashes;
Fetch all rows from L</"sth"> and return them as a L<Mojo::Collection> object
containing hash references.
# Process all rows at once
say $results->hashes->reduce(sub { $a + $b->{money} }, 0);
=head2 last_insert_id
my $id = $results->last_insert_id;
Returns the L<rowid|https://www.sqlite.org/c3ref/last_insert_rowid.html> of the
most recent successful C<INSERT>.
=head2 rows
my $num = $results->rows;
Number of rows. Note that for C<SELECT> statements, this count will not be
accurate until all rows have been fetched.
=head2 text
my $text = $results->text;
Fetch all rows from L</"sth"> and turn them into a table with
L<Mojo::Util/"tablify">.
=head1 BUGS
Report any issues on the public bugtracker.
=head1 AUTHOR
Dan Book, C<dbook@cpan.org>
=head1 COPYRIGHT AND LICENSE
Copyright 2015, Dan Book.
This library is free software; you may redistribute it and/or modify it under
the terms of the Artistic License version 2.0.
=head1 SEE ALSO
L<Mojo::SQLite>
|