Aniki
view release on metacpan or search on metacpan
## INSTANCE METHODS
### `select($table_name, \%where, \%opt)`
Execute `SELECT` query by generated SQL, and returns result object.
```perl
my $result = $db->select(foo => { id => 1 }, { limit => 1 });
# stmt: SELECT FROM foo WHERE id = ? LIMIT 1
# bind: [1]
```
#### Options
There are the options of `SELECT` query.
See also [SQL::Maker](https://metacpan.org/pod/SQL::Maker#opt).
And you can use there options:
- `suppress_row_objects : Bool`
- `columns : ArrayRef[Str]`
List for retrieving columns from database.
- `prefetch : ArrayRef|HashRef`
Pre-fetch specified related rows.
See also ["RELATIONSHIP"](#relationship) section.
### `select_named($sql, \%bind, \%opt)`
### `select_by_sql($sql, \@bind, \%opt)`
Execute `SELECT` query by specified SQL, and returns result object.
```perl
my $result = $db->select_by_sql('SELECT FROM foo WHERE id = ? LIMIT 1', [1]);
# stmt: SELECT FROM foo WHERE id = ? LIMIT 1
# bind: [1]
```
#### Options
You can use there options:
- `table_name: Str`
This is table name using row/result class guessing.
Pre-fetch specified related rows.
See also ["RELATIONSHIP"](#relationship) section.
### `insert($table_name, \%values, \%opt)`
Execute `INSERT INTO` query.
```perl
$db->insert(foo => { bar => 1 });
# stmt: INSERT INTO foo (bar) VALUES (?)
# bind: [1]
```
### `insert_and_fetch_id($table_name, \%values, \%opt)`
Execute `INSERT INTO` query, and returns `last_insert_id`.
```perl
my $id = $db->insert_and_fetch_id(foo => { bar => 1 });
# stmt: INSERT INTO foo (bar) VALUES (?)
# bind: [1]
```
### `insert_and_fetch_row($table_name, \%values, \%opt)`
Execute `INSERT INTO` query, and `SELECT` it, and returns row object.
```perl
my $row = $db->insert_and_fetch_row(foo => { bar => 1 });
# stmt: INSERT INTO foo (bar) VALUES (?)
# bind: [1]
```
### `insert_and_emulate_row($table_name, \%values, \%opt)`
Execute `INSERT INTO` query, and returns row object created by `$row` and schema definition.
```perl
my $row = $db->insert_and_fetch_row(foo => { bar => 1 });
# stmt: INSERT INTO foo (bar) VALUES (?)
# bind: [1]
```
This method is faster than `insert_and_fetch_row`.
#### WARNING
If you use SQL `TRIGGER` or dynamic default value, this method don't return the correct value, maybe.
In this case, you should use `insert_and_fetch_row` instead of this method.
### `insert_on_duplicate($table_name, \%insert, \%update)`
Execute `INSERT ... ON DUPLICATE KEY UPDATE` query for MySQL.
```perl
my $row = $db->insert_on_duplicate(foo => { bar => 1 }, { bar => \'VALUE(bar) + 1' });
# stmt: INSERT INTO foo (bar) VALUES (?) ON DUPLICATE KEY UPDATE bar = VALUE(bar) + 1
# bind: [1]
```
SEE ALSO: [INSERT ... ON DUPLICATE KEY UPDATE Syntax](https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html)
### `insert_multi($table_name, \@values, \%opts)`
Execute `INSERT INTO ... (...) VALUES (...), (...), ...` query for MySQL.
Insert multiple rows at once.
```perl
my $row = $db->insert_multi(foo => [{ bar => 1 }, { bar => 2 }, { bar => 3 }]);
# stmt: INSERT INTO foo (bar) VALUES (?),(?),(?)
# bind: [1, 2, 3]
```
SEE ALSO: [INSERT Syntax](https://dev.mysql.com/doc/refman/5.6/en/insert.html)
### `update($table_name, \%set, \%where)`
Execute `UPDATE` query, and returns changed rows count.
```perl
my $count = $db->update(foo => { bar => 2 }, { id => 1 });
# stmt: UPDATE foo SET bar = ? WHERE id = ?
# bind: [2, 1]
```
### `update($row, \%set)`
Execute `UPDATE` query, and returns changed rows count.
```perl
my $row = $db->select(foo => { id => 1 }, { limit => 1 })->first;
my $count = $db->update($row => { bar => 2 });
# stmt: UPDATE foo SET bar = ? WHERE id = ?
# bind: [2, 1]
```
### `update_and_fetch_row($row, \%set)`
Execute `UPDATE` query, and `SELECT` it, and returns row object.
```perl
my $row = $db->select(foo => { id => 1 }, { limit => 1 })->first;
my $new_row = $db->update_and_fetch_row($row => { bar => 2 });
# stmt: UPDATE foo SET bar = ? WHERE id = ?
# bind: [2, 1]
```
### `update_and_emulate_row($row, \%set)`
Execute `UPDATE` query, and returns row object created by `$row` and `%set`.
```perl
my $row = $db->select(foo => { id => 1 }, { limit => 1 })->first;
my $new_row = $db->update_and_emulate_row($row => { bar => 2 });
# stmt: UPDATE foo SET bar = ? WHERE id = ?
# bind: [2, 1]
```
This method is faster than `update_and_fetch_row`.
#### WARNING
If you use SQL `TRIGGER` or `AutoCommit`, this method don't return the correct value, maybe.
In this case, you should use `update_and_fetch_row` instead of this method.
### `delete($table_name, \%where)`
Execute `DELETE` query, and returns changed rows count.
```perl
my $count = $db->delete(foo => { id => 1 });
# stmt: DELETE FROM foo WHERE id = ?
# bind: [1]
```
### `delete($row)`
Execute `DELETE` query, and returns changed rows count.
```perl
my $row = $db->select(foo => { id => 1 }, { limit => 1 })->first;
my $count = $db->delete($row);
# stmt: DELETE foo WHERE id = ?
# bind: [1]
```
## ACCESSORS
- `schema : Aniki::Schema`
- `filter : Aniki::Filter`
- `query_builder : Aniki::QueryBuilder`
- `root_row_class : Aniki::Row`
- `root_result_class : Aniki::Result`
- `connect_info : ArrayRef`
lib/Aniki.pm view on Meta::CPAN
use Aniki::QueryBuilder::Canonical;
our $VERSION = '1.06';
use SQL::Maker::SQLType qw/sql_type/;
use Class::Inspector;
use Carp qw/croak confess/;
use Try::Tiny;
use Scalar::Util qw/blessed/;
use String::CamelCase qw/camelize/;
use SQL::NamedPlaceholder qw/bind_named/;
sub _noop {}
around BUILDARGS => sub {
my $orig = shift;
my $class = shift;
my %args = (@_ == 1 && ref $_[0] eq 'HASH') ? %{$_[0]} : @_;
if (not exists $args{handler}) {
my $connect_info = delete $args{connect_info} or confess 'Attribute (connect_info) is required';
lib/Aniki.pm view on Meta::CPAN
# But, It removes `$dbh->{mysql_insertid}`.
return $self->{_context} if exists $self->{_context};
return $self->handler->dbh;
}
sub insert {
my ($self, $table_name, $row, $opt) = @_;
$row = $self->filter_on_insert($table_name, $row) unless $opt->{no_filter};
my $table = $self->schema->get_table($table_name);
$row = $self->_bind_sql_type_to_args($table, $row) if $table;
my ($sql, @bind) = $self->query_builder->insert($table_name, $row, $opt);
$self->execute($sql, @bind);
return;
}
sub filter_on_insert {
my ($self, $table_name, $row) = @_;
$row = $self->filter->apply_trigger(insert => $table_name, $row);
return $self->filter->deflate_row($table_name, $row);
}
sub update {
lib/Aniki.pm view on Meta::CPAN
$opt = $_[3];
}
croak '(Aniki#update) `set` is required for update ("SET" parameter)' unless $set && %$set;
croak '(Aniki#update) `where` condition must be a reference' unless ref $where;
$set = $self->filter_on_update($table_name, $set) unless $opt->{no_filter};
my $table = $self->schema->get_table($table_name);
if ($table) {
$set = $self->_bind_sql_type_to_args($table, $set);
$where = $self->_bind_sql_type_to_args($table, $where);
}
my ($sql, @bind) = $self->query_builder->update($table_name, $set, $where);
return $self->execute($sql, @bind)->rows;
}
sub update_and_fetch_row {
my ($self, $row, $set) = @_;
croak '(Aniki#update_and_fetch_row) condition must be a Aniki::Row object.'
unless blessed $row && $row->isa('Aniki::Row');
my $emulated_row_data = $self->_update_and_emulate_row_data($row, $set);
my $where = $self->_where_row_cond($row->table, $emulated_row_data);
lib/Aniki.pm view on Meta::CPAN
my $self = shift;
if (blessed $_[0] && $_[0]->isa('Aniki::Row')) {
return $self->delete($_[0]->table_name, $self->_where_row_cond($_[0]->table, $_[0]->row_data), @_);
}
else {
my ($table_name, $where, $opt) = @_;
croak '(Aniki#delete) `where` condition must be a reference' unless ref $where;
my $table = $self->schema->get_table($table_name);
if ($table) {
$where = $self->_bind_sql_type_to_args($table, $where);
}
my ($sql, @bind) = $self->query_builder->delete($table_name, $where, $opt);
return $self->execute($sql, @bind)->rows;
}
}
sub filter_on_update {
my ($self, $table_name, $row) = @_;
$row = $self->filter->apply_trigger(update => $table_name, $row);
return $self->filter->deflate_row($table_name, $row);
}
sub insert_and_fetch_id {
lib/Aniki.pm view on Meta::CPAN
my ($self, $table_name, $insert, $update) = @_;
if ($self->schema->database ne 'MySQL') {
Carp::croak 'Cannot use insert_on_duplicate (unsupported without MySQL)';
}
$insert = $self->filter_on_insert($table_name, $insert);
$update = $self->filter_on_update($table_name, $update);
my $table = $self->schema->get_table($table_name);
if ($table) {
$insert = $self->_bind_sql_type_to_args($table, $insert);
$update = $self->_bind_sql_type_to_args($table, $update);
}
my ($sql, @bind) = $self->query_builder->insert_on_duplicate($table_name, $insert, $update);
$self->execute($sql, @bind);
return;
}
sub insert_multi {
my ($self, $table_name, $values, $opts) = @_;
return unless @$values;
$opts = defined $opts ? {%$opts} : {};
my @values = map { $self->filter_on_insert($table_name, $_) } @$values;
if (exists $opts->{update}) {
if ($self->schema->database ne 'MySQL') {
Carp::croak 'Cannot use insert_multi with update option (unsupported without MySQL)';
}
$opts->{update} = $self->filter_on_update($table_name, $opts->{update});
}
my $table = $self->schema->get_table($table_name);
if ($table) {
$_ = $self->_bind_sql_type_to_args($table, $_) for @values;
if (exists $opts->{update}) {
$opts->{update} = $self->_bind_sql_type_to_args($table, $opts->{update});
}
}
if ($self->schema->database eq 'MySQL') {
my ($sql, @bind) = $self->query_builder->insert_multi($table_name, \@values, $opts);
$self->execute($sql, @bind);
}
else {
$self->txn(sub {
local $self->{_context} = shift;
$self->insert($table_name, $_, $opts) for @values;
});
}
return;
}
lib/Aniki.pm view on Meta::CPAN
$opt //= {};
croak '(Aniki#select) `where` condition must be a reference.' unless ref $where;
my $table = $self->schema->get_table($table_name);
my $columns = exists $opt->{columns} ? $opt->{columns}
: defined $table ? $table->field_names
: $WILDCARD_COLUMNS;
$where = $self->_bind_sql_type_to_args($table, $where) if defined $table;
local $Carp::CarpLevel = $Carp::CarpLevel + 1;
my ($sql, @bind) = $self->query_builder->select($table_name, $columns, $where, $opt);
return $self->select_by_sql($sql, \@bind, {
%$opt,
table_name => $table_name,
columns => $columns,
});
}
sub fetch_and_attach_relay_data {
my ($self, $table_name, $prefetch, $rows) = @_;
return unless @$rows;
lib/Aniki.pm view on Meta::CPAN
sub _fetch_and_attach_relay_data {
my ($self, $relationships, $rows, $key, $prefetch) = @_;
my $relationship = $relationships->get($key);
unless ($relationship) {
croak "'$key' is not defined as relationship. (maybe possible typo?)";
}
$relationship->fetcher->execute($self, $rows, $prefetch);
}
sub select_named {
my ($self, $sql, $bind, $opt) = @_;
return $self->select_by_sql(bind_named($sql, $bind), $opt);
}
sub select_by_sql {
my ($self, $sql, $bind, $opt) = @_;
$opt //= {};
local $self->{suppress_row_objects} = 1 if $opt->{suppress_row_objects};
local $self->{suppress_result_objects} = 1 if $opt->{suppress_result_objects};
my $table_name = exists $opt->{table_name} ? $opt->{table_name} : $self->_guess_table_name($sql);
my $columns = exists $opt->{columns} ? $opt->{columns} : undef;
my $prefetch = exists $opt->{prefetch} ? $opt->{prefetch} : [];
$prefetch = [$prefetch] if ref $prefetch eq 'HASH';
my $prefetch_enabled_fg = @$prefetch && !$self->suppress_row_objects && defined wantarray;
if ($prefetch_enabled_fg) {
my $txn; $txn = $self->txn_scope(caller => [caller]) unless $self->in_txn;
my $sth = $self->execute($sql, @$bind);
my $result = $self->_fetch_by_sth($sth, $table_name, $columns);
$self->fetch_and_attach_relay_data($table_name, $prefetch, $result->rows);
$txn->rollback if defined $txn; ## for read only
return $result;
}
my $sth = $self->execute($sql, @$bind);
# When the return value is never used, should not create object
# case example: use `FOR UPDATE` query for global locking
unless (defined wantarray) {
$sth->finish();
return;
}
return $self->_fetch_by_sth($sth, $table_name, $columns);
}
sub _fetch_by_sth {
my ($self, $sth, $table_name, $columns) = @_;
$columns //= $sth->{NAME};
$columns = $sth->{NAME} if $columns == $WILDCARD_COLUMNS;
my @rows;
my %row;
$sth->bind_columns(\@row{@$columns});
push @rows => {%row} while $sth->fetch;
$sth->finish;
if ($self->suppress_result_objects) {
return \@rows if $self->suppress_row_objects;
my $row_class = $self->guess_row_class($table_name);
return [
map {
$row_class->new(
lib/Aniki.pm view on Meta::CPAN
my $result_class = $self->guess_result_class($table_name);
return $result_class->new(
table_name => $table_name,
handler => $self,
row_datas => \@rows,
suppress_row_objects => $self->suppress_row_objects,
);
}
sub execute {
my ($self, $sql, @bind) = @_;
$sql = $self->handler->trace_query_set_comment($sql);
my $sth = $self->use_prepare_cached ? $self->dbh->prepare_cached($sql) : $self->dbh->prepare($sql);
$self->_bind_to_sth($sth, \@bind);
eval {
$sth->execute();
};
if ($@) {
$self->handle_error($sql, \@bind, $@);
}
return $sth;
}
sub _bind_sql_type_to_args {
my ($self, $table, $args) = @_;
my %bind_args;
for my $col (keys %{$args}) {
# if $args->{$col} is a ref, it is scalar ref or already
# sql type bined parameter. so ignored.
if (ref $args->{$col}) {
$bind_args{$col} = $args->{$col};
}
elsif (my $field = $table->get_field($col)) {
$bind_args{$col} = sql_type(\$args->{$col}, $field->sql_data_type);
}
else {
$bind_args{$col} = $args->{$col};
}
}
return \%bind_args;
}
sub _bind_to_sth {
my ($self, $sth, $bind) = @_;
for my $i (keys @$bind) {
my $v = $bind->[$i];
if (blessed $v && $v->isa('SQL::Maker::SQLType')) {
$sth->bind_param($i + 1, ${$v->value_ref}, $v->type);
} else {
$sth->bind_param($i + 1, $v);
}
}
}
has _row_class_cache => (
is => 'rw',
default => sub {
my $self = shift;
my %cache = map { $_->name => undef } $self->schema->get_tables();
return \%cache;
lib/Aniki.pm view on Meta::CPAN
sub txn { shift->handler->txn(@_) }
sub in_txn { shift->handler->in_txn(@_) }
sub txn_scope { shift->handler->txn_scope(@_) }
sub txn_begin { shift->handler->txn_begin(@_) }
sub txn_rollback { shift->handler->txn_rollback(@_) }
sub txn_commit { shift->handler->txn_commit(@_) }
# --------------------------------------------------
# error handling
sub handle_error {
my ($self, $sql, $bind, $e) = @_;
require Data::Dumper;
local $Data::Dumper::Maxdepth = 2;
$sql =~ s/\n/\n /gm;
croak sprintf $self->exception_template, $e, $sql, Data::Dumper::Dumper($bind);
}
sub exception_template {
return <<'__TRACE__';
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@ Aniki 's Exception @@@@@
Reason : %s
SQL : %s
BIND : %s
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
lib/Aniki.pm view on Meta::CPAN
=back
=head2 INSTANCE METHODS
=head3 C<select($table_name, \%where, \%opt)>
Execute C<SELECT> query by generated SQL, and returns result object.
my $result = $db->select(foo => { id => 1 }, { limit => 1 });
# stmt: SELECT FROM foo WHERE id = ? LIMIT 1
# bind: [1]
=head4 Options
There are the options of C<SELECT> query.
See also L<SQL::Maker|https://metacpan.org/pod/SQL::Maker#opt>.
And you can use there options:
=over 4
lib/Aniki.pm view on Meta::CPAN
List for retrieving columns from database.
=item C<prefetch : ArrayRef|HashRef>
Pre-fetch specified related rows.
See also L</"RELATIONSHIP"> section.
=back
=head3 C<select_named($sql, \%bind, \%opt)>
=head3 C<select_by_sql($sql, \@bind, \%opt)>
Execute C<SELECT> query by specified SQL, and returns result object.
my $result = $db->select_by_sql('SELECT FROM foo WHERE id = ? LIMIT 1', [1]);
# stmt: SELECT FROM foo WHERE id = ? LIMIT 1
# bind: [1]
=head4 Options
You can use there options:
=over 4
=item C<table_name: Str>
This is table name using row/result class guessing.
lib/Aniki.pm view on Meta::CPAN
See also L</"RELATIONSHIP"> section.
=back
=head3 C<insert($table_name, \%values, \%opt)>
Execute C<INSERT INTO> query.
$db->insert(foo => { bar => 1 });
# stmt: INSERT INTO foo (bar) VALUES (?)
# bind: [1]
=head3 C<insert_and_fetch_id($table_name, \%values, \%opt)>
Execute C<INSERT INTO> query, and returns C<last_insert_id>.
my $id = $db->insert_and_fetch_id(foo => { bar => 1 });
# stmt: INSERT INTO foo (bar) VALUES (?)
# bind: [1]
=head3 C<insert_and_fetch_row($table_name, \%values, \%opt)>
Execute C<INSERT INTO> query, and C<SELECT> it, and returns row object.
my $row = $db->insert_and_fetch_row(foo => { bar => 1 });
# stmt: INSERT INTO foo (bar) VALUES (?)
# bind: [1]
=head3 C<insert_and_emulate_row($table_name, \%values, \%opt)>
Execute C<INSERT INTO> query, and returns row object created by C<$row> and schema definition.
my $row = $db->insert_and_fetch_row(foo => { bar => 1 });
# stmt: INSERT INTO foo (bar) VALUES (?)
# bind: [1]
This method is faster than C<insert_and_fetch_row>.
=head4 WARNING
If you use SQL C<TRIGGER> or dynamic default value, this method don't return the correct value, maybe.
In this case, you should use C<insert_and_fetch_row> instead of this method.
=head3 C<insert_on_duplicate($table_name, \%insert, \%update)>
Execute C<INSERT ... ON DUPLICATE KEY UPDATE> query for MySQL.
my $row = $db->insert_on_duplicate(foo => { bar => 1 }, { bar => \'VALUE(bar) + 1' });
# stmt: INSERT INTO foo (bar) VALUES (?) ON DUPLICATE KEY UPDATE bar = VALUE(bar) + 1
# bind: [1]
SEE ALSO: L<INSERT ... ON DUPLICATE KEY UPDATE Syntax|https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html>
=head3 C<insert_multi($table_name, \@values, \%opts)>
Execute C<INSERT INTO ... (...) VALUES (...), (...), ...> query for MySQL.
Insert multiple rows at once.
my $row = $db->insert_multi(foo => [{ bar => 1 }, { bar => 2 }, { bar => 3 }]);
# stmt: INSERT INTO foo (bar) VALUES (?),(?),(?)
# bind: [1, 2, 3]
SEE ALSO: L<INSERT Syntax|https://dev.mysql.com/doc/refman/5.6/en/insert.html>
=head3 C<update($table_name, \%set, \%where)>
Execute C<UPDATE> query, and returns changed rows count.
my $count = $db->update(foo => { bar => 2 }, { id => 1 });
# stmt: UPDATE foo SET bar = ? WHERE id = ?
# bind: [2, 1]
=head3 C<update($row, \%set)>
Execute C<UPDATE> query, and returns changed rows count.
my $row = $db->select(foo => { id => 1 }, { limit => 1 })->first;
my $count = $db->update($row => { bar => 2 });
# stmt: UPDATE foo SET bar = ? WHERE id = ?
# bind: [2, 1]
=head3 C<update_and_fetch_row($row, \%set)>
Execute C<UPDATE> query, and C<SELECT> it, and returns row object.
my $row = $db->select(foo => { id => 1 }, { limit => 1 })->first;
my $new_row = $db->update_and_fetch_row($row => { bar => 2 });
# stmt: UPDATE foo SET bar = ? WHERE id = ?
# bind: [2, 1]
=head3 C<update_and_emulate_row($row, \%set)>
Execute C<UPDATE> query, and returns row object created by C<$row> and C<%set>.
my $row = $db->select(foo => { id => 1 }, { limit => 1 })->first;
my $new_row = $db->update_and_emulate_row($row => { bar => 2 });
# stmt: UPDATE foo SET bar = ? WHERE id = ?
# bind: [2, 1]
This method is faster than C<update_and_fetch_row>.
=head4 WARNING
If you use SQL C<TRIGGER> or C<AutoCommit>, this method don't return the correct value, maybe.
In this case, you should use C<update_and_fetch_row> instead of this method.
=head3 C<delete($table_name, \%where)>
Execute C<DELETE> query, and returns changed rows count.
my $count = $db->delete(foo => { id => 1 });
# stmt: DELETE FROM foo WHERE id = ?
# bind: [1]
=head3 C<delete($row)>
Execute C<DELETE> query, and returns changed rows count.
my $row = $db->select(foo => { id => 1 }, { limit => 1 })->first;
my $count = $db->delete($row);
# stmt: DELETE foo WHERE id = ?
# bind: [1]
=head2 ACCESSORS
=over 4
=item C<schema : Aniki::Schema>
=item C<filter : Aniki::Filter>
=item C<query_builder : Aniki::QueryBuilder>
lib/Aniki/Plugin/Count.pm view on Meta::CPAN
my ($self, $table, $column, $where, $opt) = @_;
$where //= {};
$column //= '*';
croak '(Aniki::Plugin::Count#count) `where` condition must be a reference.' unless ref $where;
if (ref $column) {
croak 'Do not pass HashRef/ArrayRef to second argument. Usage: $db->count($table[, $column[, $where[, $opt]]])';
}
my ($sql, @binds) = $self->query_builder->select($table, [\"COUNT($column)"], $where, $opt);
my ($count) = $self->dbh->selectrow_array($sql, undef, @binds);
return $count;
}
1;
__END__
=pod
=encoding utf-8
lib/Aniki/Plugin/SQLPager.pm view on Meta::CPAN
package Aniki::Plugin::SQLPager;
use 5.014002;
use namespace::autoclean;
use Mouse::Role;
requires qw/select_by_sql select_named/;
with qw/Aniki::Plugin::PagerInjector/;
sub select_by_sql_with_pager {
my ($self, $sql, $bind, $opt) = @_;
$opt //= {};
my $page = $opt->{page} or Carp::croak("required parameter: page");
my $rows = $opt->{rows} or Carp::croak("required parameter: rows");
my $limit = $rows + 1;
my $offset = $rows * ($page - 1);
if ($opt->{no_offset}) {
$sql .= sprintf ' LIMIT %d', $limit;
}
else {
$sql .= sprintf ' LIMIT %d OFFSET %d', $limit, $offset;
}
my $result = $self->select_by_sql($sql, $bind, $opt);
return $self->inject_pager_to_result($result => $opt);
}
sub select_named_with_pager {
my ($self, $sql, $bind, $opt) = @_;
$opt //= {};
my $page = $opt->{page} or Carp::croak("required parameter: page");
my $rows = $opt->{rows} or Carp::croak("required parameter: rows");
my $limit = $rows + 1;
my $offset = $rows * ($page - 1);
if ($opt->{no_offset}) {
$sql .= sprintf ' LIMIT %d', $limit;
}
else {
$sql .= sprintf ' LIMIT %d OFFSET %d', $limit, $offset;
}
my $result = $self->select_named($sql, $bind, $opt);
return $self->inject_pager_to_result($result => $opt);
}
1;
__END__
=pod
=for stopwords sql
lib/Aniki/Plugin/SelectJoined.pm view on Meta::CPAN
my $name_sep = $self->query_builder->name_sep;
my @columns;
for my $table (@tables) {
my $table_name = $table->name;
push @columns =>
map { "$table_name$name_sep$_" }
map { $_->name } $table->get_fields();
}
my ($sql, @bind) = $self->query_builder->join_select($base_table, $join_conditions, \@columns, $where, $opt);
return $self->select_joined_by_sql($sql, \@bind, {
table_names => \@table_names,
columns => \@columns,
%$opt,
});
}
sub select_joined_by_sql {
my ($self, $sql, $bind, $opt) = @_;
$opt //= {};
my $table_names = $opt->{table_names} or croak 'table_names is required';
my $columns = $opt->{columns} or croak 'columns is required';
my $prefetch = exists $opt->{prefetch} ? $opt->{prefetch} : {};
my $prefetch_enabled_fg = %$prefetch && !$self->suppress_row_objects;
if ($prefetch_enabled_fg) {
my $txn; $txn = $self->txn_scope unless $self->txn_manager->in_transaction;
my $sth = $self->execute($sql, @$bind);
my $result = $self->_fetch_joined_by_sth($sth, $table_names, $columns);
for my $table_name (@$table_names) {
my $rows = $result->rows($table_name);
my $prefetch = $prefetch->{$table_name};
$prefetch = [$prefetch] if ref $prefetch eq 'HASH';
$self->fetch_and_attach_relay_data($table_name, $prefetch, $rows);
}
$txn->rollback if defined $txn; ## for read only
return $result;
}
else {
my $sth = $self->execute($sql, @$bind);
return $self->_fetch_joined_by_sth($sth, $table_names, $columns);
}
}
sub _fetch_joined_by_sth {
my ($self, $sth, $table_names, $columns) = @_;
my @rows;
my %row;
$sth->bind_columns(\@row{@$columns});
push @rows => $self->_seperate_rows(\%row) while $sth->fetch;
$sth->finish;
return Aniki::Result::Collection::Joined->new(
table_names => $table_names,
handler => $self,
row_datas => \@rows,
);
}
t/query_builder/canonical.t view on Meta::CPAN
subtest select => sub {
my $expect = <<'__QUERY__';
SELECT *
FROM `foo`
WHERE (`bar` = ?) AND (`baz` = ?)
__QUERY__
chomp $expect;
my @expect = (1, 2);
my $ok = reduce { $a && $b } map {
my ($stmt, @bind) = $query_builder->select(foo => ['*'], { bar => 1, baz => 2 });
$stmt eq $expect && eq_array(\@bind, \@expect);
} 1..1000;
ok $ok, 'can get the same statement always';
};
subtest insert => sub {
my $expect = <<'__QUERY__';
INSERT INTO `foo`
(`bar`, `baz`)
VALUES (?, ?)
__QUERY__
chomp $expect;
my @expect = (1, 2);
my $ok = reduce { $a && $b } map {
my ($stmt, @bind) = $query_builder->insert(foo => { bar => 1, baz => 2 });
$stmt eq $expect && eq_array(\@bind, \@expect);
} 1..1000;
ok $ok, 'can get the same statement always';
};
subtest update => sub {
my $expect = 'UPDATE `foo` SET `bar` = ?, `foo` = ? WHERE (`bar` = ?) AND (`baz` = ?)';
my @expect = (2, 1, 1, 2);
my $ok = reduce { $a && $b } map {
my ($stmt, @bind) = $query_builder->update(foo => { foo => 1, bar => 2 }, { bar => 1, baz => 2 });
$stmt eq $expect && eq_array(\@bind, \@expect);
} 1..1000;
ok $ok, 'can get the same statement always';
};
subtest delete => sub {
my $expect = 'DELETE FROM `foo` WHERE (`bar` = ?) AND (`baz` = ?)';
my @expect = (1, 2);
my $ok = reduce { $a && $b } map {
my ($stmt, @bind) = $query_builder->delete(foo => { bar => 1, baz => 2 });
$stmt eq $expect && eq_array(\@bind, \@expect);
} 1..1000;
ok $ok, 'can get the same statement always';
};
done_testing();
( run in 1.746 second using v1.01-cache-2.11-cpan-2398b32b56e )