Dancer::Plugin::Database::Core::Handle(3) 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', {});
# count number of male employees
my $count = database->quick_count('employees', { gender => 'male' });

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.

If you need any of the values to be interpolated straight into the SQL, for instance if you need to use a function call like "NOW()" or similar, then you can provide them as a scalarref: 

  database->quick_insert('mytable', { foo => 'Bar', timestamp => \'NOW()' });

Of course, if you do that, you must be careful to avoid SQL injection attacks!

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.

As per quick_insert, if you need any of the values to be interpolated straight in the SQL, for e.g. to use a function call, provide a scalarref: 

  database->quick_update('mytable', { id => 42 }, { counter => \'counter + 1' });

Of course, if you do that, you must be careful to avoid SQL injection attacks!

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), and an optional hashref of options, 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. The third argument is a hashref of options to allow additional control, as documented below. For backwards compatibility, it can also be an arrayref of column names, which acts in the same way as the "columns" option.

The options you can provide are:

"columns"
An arrayref of column names to return, if you only want certain columns returned
"order_by"
Specify how the results should be ordered. This option can take various values:
  • a straight scalar or arrayref sorts by the given column(s): 

        { order_by => 'foo' }           # equivalent to "ORDER BY foo"
    { order_by => [ qw(foo bar) ] } # equiv to "ORDER BY foo,bar"
  • a hashref of "order =" column name>, e.g.: 

        { order_by => { desc => 'foo' } } # equiv to ORDER BY foo DESC
    { order_by => [ { desc => 'foo' }, { asc => 'bar' } ] }
       # above is equiv to ORDER BY foo DESC, bar ASC
"limit"
Limit how many records will be returned; equivalent to e.g. "LIMIT 1" in an SQL query. If called in scalar context, an implicit LIMIT 1 will be added to the query anyway, so you needn't add it yourself.

An example of using options to control the results you get back: 

    # Get the name & phone number of the 10 highest-paid men:
    database->quick_select(
        'employees', 
        { gender => 'male' },
        { order_by => 'salary', limit => 10, columns => [qw(name phone)] }
    );
"offset" number
"Offset" says to skip that many rows before beginning to return rows (postgresql).

Example: 

    # Get the name & phone number of the 10 highest-paid men starting from 11th:
    database->quick_select(
        'employees', 
        { gender => 'male' },
        { order_by => 'salary', offset => 10, limit => 10, columns => [qw(name phone)] }
    );
quick_lookup
  my $id  = database->quick_lookup($table, { email => $params->{'email'} }, 'userid' );

This is a bit of syntactic sugar when you just want to lookup a specific field, such as when you're converting an email address to a userid (say during a login handler.)

This call always returns a single scalar value, not a hashref of the entire row (or partial row) like most of the other methods in this library.

Returns undef when there's no matching row or no such field found in the results.

quick_count
  my $count = database->quick_count($table,
                                    { email => $params->{'email'} });

This is syntactic sugar to return a count of all rows which match your parameters, useful for pagination.

This call always returns a single scalar value, not a hashref of the entire row (or partial row) like most of the other methods in this library.

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

When 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'"

If you pass in an arrayref as the value, you can get a set clause as in the following example: 

 { foo => [ 'bar', 'baz', 'quux' ] }

... it's the same as "WHERE foo IN ('bar', 'baz', 'quux')"

If you need additional flexibility, you can build fairly complex where clauses by passing a hashref of condition operators and values as the value to the column field key.

Currently recognized operators are:

'like'
 { foo => { 'like' => '%bar%' } }

... same as "WHERE foo LIKE '%bar%'"

'gt' / 'ge'
 'greater than' or 'greater or equal to'
  
 { foo => { 'ge' => '42' } }

... same as "WHERE foo >= '42'"

'lt' / 'le'
 'less than' or 'less or equal to'
 { foo => { 'lt' => '42' } }

... same as "WHERE foo < '42'"

'eq' / 'ne' / 'is'
 'equal' or 'not equal' or 'is'
 { foo => { 'ne' => 'bar' } }

... same as "WHERE foo != 'bar'"

You can also include a key named 'not' with a true value in the hashref which will (attempt) to negate the other operator(s). 

 { foo => { 'like' => '%bar%', 'not' => 1 } }

... same as "WHERE foo NOT LIKE '%bar%'"

If you use undef as the value for an operator hashref it will be replaced with 'NULL' in the query.

If that's not flexible enough, you can pass in your own scalar WHERE clause string BUT there's no automatic sanitation on that - if you suffer from a SQL injection attack - don't blame me! Don't forget to use "quote()"/"quote_identifier()" on it then.

AUTHOR

David Precious " <<[email protected] "> >

ACKNOWLEDGEMENTS

See ``ACKNOWLEDGEMENTS'' in Dancer::Plugin::Database

LAST SEARCHED