NAME
Dancer::Plugin::Database::Handle - subclassed DBI connection handle
DESCRIPTION
Subclassed DBI connection handle with added convenience features
SYNOPSIS
# in your Dancer app:
database->quick_insert($tablename, \%data);
# Updating a record where id = 42:
database->quick_update($tablename, { id => 42 }, { foo => 'New value' });
# Fetching a single row quickly in scalar context
my $employee = database->quick_select('employees', { id => $emp_id });
# Fetching multiple rows in list context - passing an empty hashref to signify
# no where clause (i.e. return all rows - so "select * from $table_name"):
my @all_employees = database->quick_select('employees', {});Added features
A Dancer::Plugin::Database::Handle object is a subclassed DBI::db DBI database handle, with the following added convenience methods:
- quick_insert
-
database->quick_insert('mytable', { foo => 'Bar', baz => 5 });Given a table name and a hashref of data (where keys are column names, and the values are, well, the values), insert a row in the table.
- quick_update
-
database->quick_update('mytable', { id => 42 }, { foo => 'Baz' });Given a table name, a hashref describing a where clause and a hashref of changes, update a row.
- quick_delete
-
database->quick_delete($table, { id => 42 });Given a table name and a hashref to describe the rows which should be deleted (the where clause - see below for further details), delete them.
- quick_select
-
my $row = database->quick_select($table, { id => 42 }); my @rows = database->quick_select($table, { id => 42 });Given a table name and a hashref of where clauses (see below for explanation), returns either the first matching row as a hashref, if called in scalar context, or a list of matching rows as hashrefs, if called in list context.
All of the convenience methods provided take care to quote table and column names using DBI's quote_identifier, and use parameterised queries to avoid SQL injection attacks. See http://www.bobby-tables.com/ for why this is important, if you're not familiar with it.
WHERE clauses as hashrefs
quick_update, quick_delete and quick_select take a hashref of WHERE clauses. This is a hashref of field => 'value', each of which will be included in the WHERE clause used, for instance:
{ id => 42 }Will result in an SQL query which would include:
WHERE id = 42When more than one field => value pair is given, they will be ANDed together:
{ foo => 'Bar', bar => 'Baz' }Will result in:
WHERE foo = 'Bar' AND bar = 'Baz'(Actually, parameterised queries will be used, with placeholders, so SQL injection attacks will not work, but it's easier to illustrate as though the values were interpolated directly. Don't worry, they're not.)
With the same idea in mind, you can check if a value is NULL with:
{ foo => undef }This will be correctly rewritten to foo IS NULL.
You can pass an empty hashref if you want all rows, e.g.:
database->quick_select('mytable', {});... is the same as "SELECT * FROM 'mytable'"
TODO: this isn't very flexible; it would be nice to easily use other logic combinations, and other comparisons other than a straightforward equality comparison. However, supporting this abstraction without the syntax used becoming a real mess can be... awkward. Accepting a pre-written SQL 'WHERE' clause would be one option. Any thoughts on this would be appreciated!
AUTHOR
David Precious <<davidp@preshweb.co.uk > >