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
|
package plugin::sql;
use strict;
use warnings;
use utf8;
use base qw(NanoA::Plugin);
sub init_plugin {
my ($klass, $controller) = @_;
no strict 'refs';
no warnings 'redefine';
# Usage: $app->sql_do($statement);
*{$controller . '::sql_do'} = sub {
my ($app, $sql) = @_;
my $dbh = $app->db;
$dbh->do($sql) or die $dbh->errstr;
};
# Usage: $app->sql_prepare_exec($statement, \@params);
*{$controller . '::sql_prepare_exec'} = sub {
my ($app, $sql, $params) = @_;
my $dbh = $app->db;
$params = [] unless ref $params eq 'ARRAY';
my $sth = $dbh->prepare($sql) or die $dbh->errstr;
$sth->execute(@{$params}) or die $dbh->errstr;
$sth->finish;
undef $sth;
};
# Usage: my $rows = $app->sql_select_all($statement, \@params);
# $rows : foreach my $row (@{$rows}) { $row->{field} }
*{$controller . '::sql_select_all'} = sub {
my ($app, $sql, $params) = @_;
my $dbh = $app->db;
$params = [] unless ref $params eq 'ARRAY';
my $sth = $dbh->prepare($sql) or die $dbh->errstr;
$sth->execute(@{$params}) or die $dbh->errstr;
my @res;
while (my $row = $sth->fetchrow_hashref) {
push @res, $row;
}
$sth->finish;
undef $sth;
return \@res;
};
# Usage: my ($rows, $pager) = $app->sql_select_paginate($statement, \@params, { page => num_of_page, rows => num_of_rows });
# $rows : foreach my $row (@{$rows}) { $row->{field} }
# $pager->{page} : page number
# $pager->{has_next}: has next page
# $pager->{has_prev}: has prev page
*{$controller . '::sql_select_paginate'} = sub {
my ($app, $sql, $params, $paging) = @_;
my $dbh = $app->db;
$params = [] unless ref $params eq 'ARRAY';
$sql .= ' LIMIT ? OFFSET ?';
my $sth = $dbh->prepare($sql) or die $dbh->errstr;
$sth->execute(@{$params}, $paging->{rows}+1, ($paging->{page}-1)*$paging->{rows}) or die $dbh->errstr;
my @res;
while (my $row = $sth->fetchrow_hashref) {
push @res, $row;
}
$sth->finish;
undef $sth;
my $has_next = 0;
if ( @res == $paging->{rows} + 1 ) {
pop @res;
$has_next++;
}
return (\@res, {page => $paging->{page}, has_next => $has_next, has_prev => ($paging->{page} != 1) ? 1 : 0});
};
}
# initialize myself
__PACKAGE__->init_plugin(__PACKAGE__);
1;
|