Created
February 6, 2012 20:54
-
-
Save knutov/1754790 to your computer and use it in GitHub Desktop.
Dancer::Plugin::Database::KISS
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package Dancer::Plugin::Database::KISS; | |
use strict; | |
use Carp; | |
use DBI; | |
use base qw(DBI::db); | |
our $VERSION = '0.01'; | |
=head1 NAME | |
Dancer::Plugin::Database::KISS - subclassed DBI connection handle (like | |
Dancer::Plugin::Database::Handle but with different methods) | |
=head1 DESCRIPTION | |
Subclassed DBI connection handle with added convenience features | |
=head1 SYNOPSIS | |
# in your Dancer app: | |
database->sql($sql); | |
=head1 Added features | |
A C<Dancer::Plugin::Database::KISS> object is a subclassed L<DBI::db> L<DBI> | |
database handle, with the following added convenience methods: | |
=over 4 | |
=item sql | |
database->sql($sql, @parameters); | |
database->sql('SELECT * FROM users;'); | |
database->sql('SELECT * FROM users WHERE id=?;', $user_id); | |
Just do a query with optional parameters. Returns $sth. | |
=cut | |
sub sql { | |
my ($self, $sql, @params) = @_; | |
# return $self->do($sql, undef, @bind_params); | |
my $sth = $self->prepare($sql); | |
$sth->execute(@params) or die $sth->errstr."\nSQL: $sql, PARAMS: @params"; | |
return $sth; | |
} | |
=item row | |
database->row($sql, @parameters); | |
my $row = database->row('SELECT * FROM users WHERE id=?;', $user_id); | |
say $row->{id}; | |
my ($id,$name) = database->row('SELECT * FROM users WHERE id=?;', $user_id); | |
say "User's $id name: $name"; | |
Just do a [SELECT] query with optional parameters. Returns hashref or array of | |
the columns. | |
=cut | |
sub row # sql($sql,@params) | |
{ | |
my ($self, $sql, @params) = @_; | |
my $sth = $self->sql($sql, @params); | |
return $sth->fetchrow_array if wantarray; | |
return $sth->fetchrow_hashref; | |
} | |
=item select | |
database->select($sql, @parameters); | |
my $users = database->select('SELECT * FROM users'); | |
say $users->[0]->{id}; | |
Do SELECT query with optional parameters. Returns array of hashref'es of the | |
columns. Returns empty array if nothing to return. | |
=cut | |
sub select | |
{ | |
my ($self, $sql, @params) = @_; | |
my $sth = $self->sql($sql, @params); | |
return $sth->fetchall_arrayref({}) || []; | |
} | |
=item cnt | |
database->cnt($sql, @parameters); | |
my $cnt = database->cnt('SELECT count(*) FROM users WHERE status=1'); | |
say "Total $cnt user(s)"; | |
Do assuming "SELECT count(*)" query with optional parameters. Returns first | |
element of the first row (usually - count(*)). | |
=cut | |
sub cnt | |
{ | |
my ($self, $sql, @params) = @_; | |
my $sth = $self->sql($sql,@params); | |
my $row = $sth->fetchrow_arrayref; | |
$sth->finish; | |
return $row->[0]; | |
} | |
=item insert | |
database->insert($sql, @parameters); | |
my $id = database->insert( | |
'INSERT INTO users (name) VALUES (?)', 'User Name' | |
); | |
say "Id of the inserted user: $id"; | |
Do assuming INSERT query with optional parameters. Returns (if possible) id of | |
the inserted row. | |
=cut | |
sub insert | |
{ | |
my ($self, $sql, @params) = @_; | |
my $sth = $self->sql($sql,@params); | |
return $self->last_insert_id(undef,undef,undef,undef); | |
# return $sth->{mysql_insertid}; # only for MySQL | |
} | |
=head1 AUTHOR | |
Nick Knutov C< <<[email protected] >> > | |
=head1 ACKNOWLEDGEMENTS | |
See L<Dancer::Plugin::Database/ACKNOWLEDGEMENTS> | |
=head1 SEE ALSO | |
L<Dancer::Plugin::Database::Handle> | |
L<Dancer::Plugin::Database> | |
L<Dancer> | |
L<DBI> | |
=cut | |
1; | |
__END__ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment