.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.43)
.\"
.\" 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 >0, 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
.\" ========================================================================
.\"
.IX Title "Dancer::Plugin::Database::Core::Handle 3pm"
.TH Dancer::Plugin::Database::Core::Handle 3pm "2022-11-19" "perl v5.36.0" "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.
.Sp
If you need any of the values to be interpolated straight into the \s-1SQL,\s0 for
instance if you need to use a function call like \f(CW\*(C`NOW()\*(C'\fR or similar, then you
can provide them as a scalarref:
.Sp
.Vb 1
\&  database\->quick_insert(\*(Aqmytable\*(Aq, { foo => \*(AqBar\*(Aq, timestamp => \e\*(AqNOW()\*(Aq });
.Ve
.Sp
Of course, if you do that, you must be careful to avoid \s-1SQL\s0 injection attacks!
.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.
.Sp
As per quick_insert, if you need any of the values to be interpolated straight
in the \s-1SQL,\s0 for e.g. to use a function call, provide a scalarref:
.Sp
.Vb 1
\&  database\->quick_update(\*(Aqmytable\*(Aq, { id => 42 }, { counter => \e\*(Aqcounter + 1\*(Aq });
.Ve
.Sp
Of course, if you do that, you must be careful to avoid \s-1SQL\s0 injection attacks!
.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 DESC, bar ASC
.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.
.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
.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
.RE
.RS 4
.RE
.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 "'ilike'" 4
.IX Item "'ilike'"
Postgres-specific \- same as 'like', but case-insensitive.
.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
.SH "LICENSE AND COPYRIGHT"
.IX Header "LICENSE AND COPYRIGHT"
Copyright 2016 David Precious.
.PP
This program is free software; you can redistribute it and/or modify it
under the terms of the the Artistic License (2.0). You may obtain a
copy of the full license at:
.PP
<http://www.perlfoundation.org/artistic_license_2_0>
.PP
Any use, modification, and distribution of the Standard or Modified
Versions is governed by this Artistic License. By using, modifying or
distributing the Package, you accept this license. Do not use, modify,
or distribute the Package, if you do not accept this license.
.PP
If your Modified Version has been derived from a Modified Version made
by someone other than you, you are nevertheless required to ensure that
your Modified Version complies with the requirements of this license.
.PP
This license does not grant you the right to use any trademark, service
mark, tradename, or logo of the Copyright Holder.
.PP
This license includes the non-exclusive, worldwide, free-of-charge
patent license to make, have made, use, er to sell, sell, import and
otherwise transfer the Package with respect to any patent claims
licensable by the Copyright Holder that are necessarily infringed by the
Package. If you institute patent litigation (including a cross-claim or
counterclaim) against any party alleging that the Package constitutes
direct or contributory patent infringement, then this Artistic License
to you shall terminate on the date that such litigation is filed.
.PP
Disclaimer of Warranty: \s-1THE PACKAGE IS PROVIDED BY THE COPYRIGHT HOLDER
AND CONTRIBUTORS "AS IS\s0' \s-1AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES.
THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE, OR\s0 NON-INFRINGEMENT \s-1ARE DISCLAIMED TO THE EXTENT PERMITTED BY
YOUR LOCAL LAW. UNLESS REQUIRED BY LAW, NO COPYRIGHT HOLDER OR
CONTRIBUTOR WILL BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, OR
CONSEQUENTIAL DAMAGES ARISING IN ANY WAY OUT OF THE USE OF THE PACKAGE,
EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.\s0