Dancer...Handle(3pm)

💅 Dancer::Plugin::Database::Core::Handle 💅

okay bestie, welcome to the 3pm manual page for Dancer::Plugin::Database::Core::Handle, brought to you by User Contributed Perl Documentation. it's giving "DBI handle but make it helpful" and honestly? we're obsessed. 🫶

---

✨ NAME ✨

Dancer::Plugin::Database::Core::Handle — a subclassed DBI connection handle. that's it, that's the name, short and iconic. 💖

🦋 DESCRIPTION 🦋

It's a subclassed DBI connection handle that comes with added convenience features. so you get all the DBI goodness you already know and love, plus a little extra sparkle on top. no cap. ✨

🌸 SYNOPSIS 🌸

here's the vibe, bestie — copy these exactly, don't be freestyling:

# 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 💖

so a Dancer::Plugin::Database::Handle object is a subclassed DBI::db DBI database handle, and it comes with these extra convenience methods. periodt. 👑

🌟 quick_insert

database->quick_insert('mytable', { foo => 'Bar', baz => 5 });

give it a table name and a hashref of data (keys = column names, values = the actual values, obviously) and it inserts a row into that table. so easy.

but like — if you need any of those values to be interpolated straight into the SQL, for instance if you gotta use a function call like NOW() or something similar, then you can hand them over as a scalarref:

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

⚠️ bestie. if you do that, you must be careful to avoid SQL injection attacks! like, genuinely. this is not a drill. 😭

🌟 quick_update

database->quick_update('mytable', { id => 42 }, { foo => 'Baz' });

give it a table name, a hashref describing a where clause, and a hashref of changes — and it updates a row. chef's kiss. 💅

same deal as quick_insert: if you need any of the values 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' });

⚠️ again, for real for real: if you do that, you must be careful to avoid SQL injection attacks! 🙈

🌟 quick_delete

database->quick_delete($table, {  id => 42 });

give it a table name and a hashref describing the rows that should be deleted (that's the where clause — more on those below, keep reading bestie) and it deletes them. bye bye rows. 💀

🌟 quick_select

my $row  = database->quick_select($table, { id => 42 });
my @rows = database->quick_select($table, { id => 42 });

give it a table name and a hashref of where clauses (explanation below, don't worry), plus an optional hashref of options, and it returns:

• the first matching row as a hashref if you called it in scalar context, or
• a list of matching rows as hashrefs if you called it in list context. 🌈

that third argument is a hashref of options for extra control (documented right below). and for backwards compatibility, it can also be an arrayref of column names, which acts in the same way as the columns option. slay, legacy support. 🫶

the options you can provide are:

🌸 columns

An arrayref of column names to return, if you only want certain columns returned. picky but valid. ✨

🌸 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. and heads up: if you called it in scalar context, an implicit LIMIT 1 will be added to the query anyway, so you needn't add it yourself. one less thing, bestie. 💅

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 for when you just want to look up a specific field — like 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.

and it 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'} });

syntactic sugar that returns a count of all rows which match your parameters — super 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. (you should be familiar with it. go read it. 👀)

🔥 WHERE clauses as hashrefs 🔥

quick_update, quick_delete and quick_select take a hashref of WHERE clauses. it's a hashref of field => 'value', and each of those gets 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'll 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. chef's kiss. ✨

you can pass an empty hashref if you want all rows, e.g.:

database->quick_select('mytable', {});

... is the same as SELECT * FROM 'mytable'. give me everything, no notes. 💅

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'). iconic. 👑

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%'

'ilike'

Postgres-specific — same as 'like', but case-insensitive. 🐘

'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. fr fr.

💜 AUTHOR 💜

David Precious <<davidp@preshweb.co.uk>> — legend behaviour, honestly. 🫶

🎀 ACKNOWLEDGEMENTS 🎀

See ACKNOWLEDGEMENTS in Dancer::Plugin::Database. go look, bestie. ✨

🌈 SEE ALSO 🌈

• Dancer::Plugin::Database and Dancer2::Plugin::Database
• Dancer and Dancer2
• DBI

📜 LICENSE AND COPYRIGHT 💖

okay bestie, legal era. stay with me, it's actually short 💅

• Copyright 2016 David Precious. 🎀
• This program is free software — you can redistribute it and/or modify it under the terms of the Artistic License (2.0). You can grab the full license at:

<http://www.perlfoundation.org/artistic_license_2_0>

• 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. So like — do not use, modify, or distribute the Package if you do not accept this license. periodt. 🙅‍♀️
• 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. (yes, even if it came from your bestie's bestie's fork. it's giving chain of custody 🧚)
• This license does not grant you the right to use any trademark, service mark, tradename, or logo of the Copyright Holder. no brand-adjacent shenanigans, sorry. 🌸
• 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. (not me spotting the stray er in the original — keeping it character for character because that's the vibe 😭)
• 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. like, instantly. 🔥
• Disclaimer of Warranty: THE PACKAGE IS PROVIDED BY THE COPYRIGHT HOLDER AND CONTRIBUTORS "AS IS' AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES. THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT ARE 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.

fr fr though: all-caps legal text is not me yelling at you, that's just how warranties get disclaimed. it means no promises, no take-backs, you accepted the license when you used the Package. 💖

---

and that's the whole page, bestie — you made it to the end 🫶 this was rendered for perl v5.36.0 on 2022-11-19, and that's the Dancer...Handle(3pm) documentation, served hot. go build something iconic with it. 👑✨