.\" Automatically generated by Pod::Man 2.28 (Pod::Simple 3.28)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{
.    if \nF \{
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\"
.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
.\" Fear.  Run.  Save yourself.  No user-serviceable parts.
.    \" fudge factors for nroff and troff
.if n \{\
.    ds #H 0
.    ds #V .8m
.    ds #F .3m
.    ds #[ \f1
.    ds #] \fP
.\}
.if t \{\
.    ds #H ((1u-(\\\\n(.fu%2u))*.13m)
.    ds #V .6m
.    ds #F 0
.    ds #[ \&
.    ds #] \&
.\}
.    \" simple accents for nroff and troff
.if n \{\
.    ds ' \&
.    ds ` \&
.    ds ^ \&
.    ds , \&
.    ds ~ ~
.    ds /
.\}
.if t \{\
.    ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
.    ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
.    ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
.    ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
.    ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
.    ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
.\}
.    \" troff and (daisy-wheel) nroff accents
.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
.ds ae a\h'-(\w'a'u*4/10)'e
.ds Ae A\h'-(\w'A'u*4/10)'E
.    \" corrections for vroff
.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
.    \" for low resolution devices (crt and lpr)
.if \n(.H>23 .if \n(.V>19 \
\{\
.    ds : e
.    ds 8 ss
.    ds o a
.    ds d- d\h'-1'\(ga
.    ds D- D\h'-1'\(hy
.    ds th \o'bp'
.    ds Th \o'LP'
.    ds ae ae
.    ds Ae AE
.\}
.rm #[ #] #H #V #F C
.\" ========================================================================
.\"
.IX Title "Dancer::Plugin::Database::Core::Handle 3pm"
.TH Dancer::Plugin::Database::Core::Handle 3pm "2014-04-01" "perl v5.20.1" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
Dancer::Plugin::Database::Core::Handle \- subclassed DBI connection handle
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
Subclassed \s-1DBI\s0 connection handle with added convenience features
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 2
\&  # in your Dancer app:
\&  database\->quick_insert($tablename, \e%data);
\&
\&  # Updating a record where id = 42:
\&  database\->quick_update($tablename, { id => 42 }, { foo => \*(AqNew value\*(Aq });
\&
\&  # Fetching a single row quickly in scalar context
\&  my $employee = database\->quick_select(\*(Aqemployees\*(Aq, { 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(\*(Aqemployees\*(Aq, {});
\&
\&  # count number of male employees
\&  my $count = database\->quick_count(\*(Aqemployees\*(Aq, { gender => \*(Aqmale\*(Aq });
.Ve
.SH "Added features"
.IX Header "Added features"
A \f(CW\*(C`Dancer::Plugin::Database::Handle\*(C'\fR object is a subclassed DBI::db \s-1DBI\s0
database handle, with the following added convenience methods:
.IP "quick_insert" 4
.IX Item "quick_insert"
.Vb 1
\&  database\->quick_insert(\*(Aqmytable\*(Aq, { foo => \*(AqBar\*(Aq, baz => 5 });
.Ve
.Sp
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.
.IP "quick_update" 4
.IX Item "quick_update"
.Vb 1
\&  database\->quick_update(\*(Aqmytable\*(Aq, { id => 42 }, { foo => \*(AqBaz\*(Aq });
.Ve
.Sp
Given a table name, a hashref describing a where clause and a hashref of
changes, update a row.
.IP "quick_delete" 4
.IX Item "quick_delete"
.Vb 1
\&  database\->quick_delete($table, {  id => 42 });
.Ve
.Sp
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.
.IP "quick_select" 4
.IX Item "quick_select"
.Vb 2
\&  my $row  = database\->quick_select($table, { id => 42 });
\&  my @rows = database\->quick_select($table, { id => 42 });
.Ve
.Sp
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 \f(CW\*(C`columns\*(C'\fR option.
.Sp
The options you can provide are:
.RS 4
.ie n .IP """columns""" 4
.el .IP "\f(CWcolumns\fR" 4
.IX Item "columns"
An arrayref of column names to return, if you only want certain columns returned
.ie n .IP """order_by""" 4
.el .IP "\f(CWorder_by\fR" 4
.IX Item "order_by"
Specify how the results should be ordered.  This option can take various values:
.RS 4
.IP "\(bu" 4
a straight scalar or arrayref sorts by the given column(s):
.Sp
.Vb 2
\&    { order_by => \*(Aqfoo\*(Aq }           # equivalent to "ORDER BY foo"
\&    { order_by => [ qw(foo bar) ] } # equiv to "ORDER BY foo,bar"
.Ve
.IP "\(bu" 4
a hashref of \f(CW\*(C`order =\*(C'\fR column name>, e.g.:
.Sp
.Vb 3
\&    { order_by => { desc => \*(Aqfoo\*(Aq } } # equiv to ORDER BY foo DESC
\&    { order_by => [ { desc => \*(Aqfoo\*(Aq }, { asc => \*(Aqbar\*(Aq } ] }
\&       # above is equiv to ORDER BY foo ASC, bar DESC
.Ve
.RE
.RS 4
.RE
.ie n .IP """limit""" 4
.el .IP "\f(CWlimit\fR" 4
.IX Item "limit"
Limit how many records will be returned; equivalent to e.g. \f(CW\*(C`LIMIT 1\*(C'\fR in an \s-1SQL\s0
query.  If called in scalar context, an implicit \s-1LIMIT 1\s0 will be added to the
query anyway, so you needn't add it yourself.
.RE
.RS 4
.Sp
An example of using options to control the results you get back:
.Sp
.Vb 6
\&    # Get the name & phone number of the 10 highest\-paid men:
\&    database\->quick_select(
\&        \*(Aqemployees\*(Aq, 
\&        { gender => \*(Aqmale\*(Aq },
\&        { order_by => \*(Aqsalary\*(Aq, limit => 10, columns => [qw(name phone)] }
\&    );
.Ve
.RE
.ie n .IP """offset"" number" 4
.el .IP "\f(CWoffset\fR number" 4
.IX Item "offset number"
\&\f(CW\*(C`Offset\*(C'\fR says to skip that many rows before beginning to return rows (postgresql).
.Sp
Example:
.Sp
.Vb 6
\&    # Get the name & phone number of the 10 highest\-paid men starting from 11th:
\&    database\->quick_select(
\&        \*(Aqemployees\*(Aq, 
\&        { gender => \*(Aqmale\*(Aq },
\&        { order_by => \*(Aqsalary\*(Aq, offset => 10, limit => 10, columns => [qw(name phone)] }
\&    );
.Ve
.IP "quick_lookup" 4
.IX Item "quick_lookup"
.Vb 1
\&  my $id  = database\->quick_lookup($table, { email => $params\->{\*(Aqemail\*(Aq} }, \*(Aquserid\*(Aq );
.Ve
.Sp
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.)
.Sp
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.
.Sp
Returns undef when there's no matching row or no such field found in 
the results.
.IP "quick_count" 4
.IX Item "quick_count"
.Vb 2
\&  my $count = database\->quick_count($table,
\&                                    { email => $params\->{\*(Aqemail\*(Aq} });
.Ve
.Sp
This is syntactic sugar to return a count of all rows which match your
parameters, useful for pagination.
.Sp
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.
.PP
All of the convenience methods provided take care to quote table and column
names using \s-1DBI\s0's \f(CW\*(C`quote_identifier\*(C'\fR, and use parameterised queries to avoid
\&\s-1SQL\s0 injection attacks.  See <http://www.bobby\-tables.com/> for why this is
important, if you're not familiar with it.
.SH "WHERE clauses as hashrefs"
.IX Header "WHERE clauses as hashrefs"
\&\f(CW\*(C`quick_update\*(C'\fR, \f(CW\*(C`quick_delete\*(C'\fR and \f(CW\*(C`quick_select\*(C'\fR take a hashref of \s-1WHERE\s0
clauses.  This is a hashref of field => 'value', each of which will be
included in the \s-1WHERE\s0 clause used, for instance:
.PP
.Vb 1
\&  { id => 42 }
.Ve
.PP
Will result in an \s-1SQL\s0 query which would include:
.PP
.Vb 1
\&  WHERE id = 42
.Ve
.PP
When more than one field => value pair is given, they will be ANDed together:
.PP
.Vb 1
\&  { foo => \*(AqBar\*(Aq, bar => \*(AqBaz\*(Aq }
.Ve
.PP
Will result in:
.PP
.Vb 1
\&  WHERE foo = \*(AqBar\*(Aq AND bar = \*(AqBaz\*(Aq
.Ve
.PP
(Actually, parameterised queries will be used, with placeholders, so \s-1SQL\s0
injection attacks will not work, but it's easier to illustrate as though the
values were interpolated directly.  Don't worry, they're not.)
.PP
With the same idea in mind, you can check if a value is \s-1NULL\s0 with:
.PP
.Vb 1
\&  { foo => undef }
.Ve
.PP
This will be correctly rewritten to \f(CW\*(C`foo IS NULL\*(C'\fR.
.PP
You can pass an empty hashref if you  want all rows, e.g.:
.PP
.Vb 1
\&  database\->quick_select(\*(Aqmytable\*(Aq, {});
.Ve
.PP
\&... is the same as \f(CW"SELECT * FROM \*(Aqmytable\*(Aq"\fR
.PP
If you pass in an arrayref as the value, you can get a set clause as in the
following example:
.PP
.Vb 1
\& { foo => [ \*(Aqbar\*(Aq, \*(Aqbaz\*(Aq, \*(Aqquux\*(Aq ] }
.Ve
.PP
\&... it's the same as \f(CW\*(C`WHERE foo IN (\*(Aqbar\*(Aq, \*(Aqbaz\*(Aq, \*(Aqquux\*(Aq)\*(C'\fR
.PP
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.
.PP
Currently recognized operators are:
.IP "'like'" 4
.IX Item "'like'"
.Vb 1
\& { foo => { \*(Aqlike\*(Aq => \*(Aq%bar%\*(Aq } }
.Ve
.Sp
\&... same as \f(CW\*(C`WHERE foo LIKE \*(Aq%bar%\*(Aq\*(C'\fR
.IP "'gt' / 'ge'" 4
.IX Item "'gt' / 'ge'"
.Vb 1
\& \*(Aqgreater than\*(Aq or \*(Aqgreater or equal to\*(Aq
\&  
\& { foo => { \*(Aqge\*(Aq => \*(Aq42\*(Aq } }
.Ve
.Sp
\&... same as \f(CW\*(C`WHERE foo >= \*(Aq42\*(Aq\*(C'\fR
.IP "'lt' / 'le'" 4
.IX Item "'lt' / 'le'"
.Vb 1
\& \*(Aqless than\*(Aq or \*(Aqless or equal to\*(Aq
\&
\& { foo => { \*(Aqlt\*(Aq => \*(Aq42\*(Aq } }
.Ve
.Sp
\&... same as \f(CW\*(C`WHERE foo < \*(Aq42\*(Aq\*(C'\fR
.IP "'eq' / 'ne' / 'is'" 4
.IX Item "'eq' / 'ne' / 'is'"
.Vb 1
\& \*(Aqequal\*(Aq or \*(Aqnot equal\*(Aq or \*(Aqis\*(Aq
\&
\& { foo => { \*(Aqne\*(Aq => \*(Aqbar\*(Aq } }
.Ve
.Sp
\&... same as \f(CW\*(C`WHERE foo != \*(Aqbar\*(Aq\*(C'\fR
.PP
You can also include a key named 'not' with a true value in the hashref 
which will (attempt) to negate the other operator(s).
.PP
.Vb 1
\& { foo => { \*(Aqlike\*(Aq => \*(Aq%bar%\*(Aq, \*(Aqnot\*(Aq => 1 } }
.Ve
.PP
\&... same as \f(CW\*(C`WHERE foo NOT LIKE \*(Aq%bar%\*(Aq\*(C'\fR
.PP
If you use undef as the value for an operator hashref it will be 
replaced with '\s-1NULL\s0' in the query.
.PP
If that's not flexible enough, you can pass in your own scalar \s-1WHERE\s0 clause 
string \fB\s-1BUT\s0\fR there's no automatic sanitation on that \- if you suffer 
from a \s-1SQL\s0 injection attack \- don't blame me!
Don't forget to use \f(CW\*(C`quote()\*(C'\fR/\f(CW\*(C`quote_identifier()\*(C'\fR on it then.
.SH "AUTHOR"
.IX Header "AUTHOR"
David Precious \f(CW\*(C` <<davidp@preshweb.co.uk \*(C'\fR> >
.SH "ACKNOWLEDGEMENTS"
.IX Header "ACKNOWLEDGEMENTS"
See \*(L"\s-1ACKNOWLEDGEMENTS\*(R"\s0 in Dancer::Plugin::Database
.SH "SEE ALSO"
.IX Header "SEE ALSO"
Dancer::Plugin::Database and Dancer2::Plugin::Database
.PP
Dancer and Dancer2
.PP
\&\s-1DBI\s0