Dancer::Plugin::Database::KISS

KISS.pm

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< <<nick@knutov.com >> >

=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__