Dancer::Plugin::Database::Core::Handle(3pm)

1Dancer::Plugin::DatabasUes:e:rCoCroen:t:rHiabnudtleedD(a3Pn)ecrelr:D:oPcluumgeinnt:a:tDiaotnabase::Core::Handle(3)
2
3
4

NAME

6       Dancer::Plugin::Database::Core::Handle - subclassed DBI connection
7       handle
8

DESCRIPTION

10       Subclassed DBI connection handle with added convenience features
11

SYNOPSIS

13         # in your Dancer app:
14         database->quick_insert($tablename, \%data);
15
16         # Updating a record where id = 42:
17         database->quick_update($tablename, { id => 42 }, { foo => 'New value' });
18
19         # Fetching a single row quickly in scalar context
20         my $employee = database->quick_select('employees', { id => $emp_id });
21
22         # Fetching multiple rows in list context - passing an empty hashref to signify
23         # no where clause (i.e. return all rows -  so "select * from $table_name"):
24         my @all_employees = database->quick_select('employees', {});
25
26         # count number of male employees
27         my $count = database->quick_count('employees', { gender => 'male' });
28

Added features

30       A "Dancer::Plugin::Database::Handle" object is a subclassed DBI::db DBI
31       database handle, with the following added convenience methods:
32
33       quick_insert
34             database->quick_insert('mytable', { foo => 'Bar', baz => 5 });
35
36           Given a table name and a hashref of data (where keys are column
37           names, and the values are, well, the values), insert a row in the
38           table.
39
40           If you need any of the values to be interpolated straight into the
41           SQL, for instance if you need to use a function call like "NOW()"
42           or similar, then you can provide them as a scalarref:
43
44             database->quick_insert('mytable', { foo => 'Bar', timestamp => \'NOW()' });
45
46           Of course, if you do that, you must be careful to avoid SQL
47           injection attacks!
48
49       quick_update
50             database->quick_update('mytable', { id => 42 }, { foo => 'Baz' });
51
52           Given a table name, a hashref describing a where clause and a
53           hashref of changes, update a row.
54
55           As per quick_insert, if you need any of the values to be
56           interpolated straight in the SQL, for e.g. to use a function call,
57           provide a scalarref:
58
59             database->quick_update('mytable', { id => 42 }, { counter => \'counter + 1' });
60
61           Of course, if you do that, you must be careful to avoid SQL
62           injection attacks!
63
64       quick_delete
65             database->quick_delete($table, {  id => 42 });
66
67           Given a table name and a hashref to describe the rows which should
68           be deleted (the where clause - see below for further details),
69           delete them.
70
71       quick_select
72             my $row  = database->quick_select($table, { id => 42 });
73             my @rows = database->quick_select($table, { id => 42 });
74
75           Given a table name and a hashref of where clauses (see below for
76           explanation), and an optional hashref of options, returns either
77           the first matching row as a hashref if called in scalar context, or
78           a list of matching rows as hashrefs if called in list context.  The
79           third argument is a hashref of options to allow additional control,
80           as documented below.  For backwards compatibility, it can also be
81           an arrayref of column names, which acts in the same way as the
82           "columns" option.
83
84           The options you can provide are:
85
86           "columns"
87               An arrayref of column names to return, if you only want certain
88               columns returned
89
90           "order_by"
91               Specify how the results should be ordered.  This option can
92               take various values:
93
94               ·   a straight scalar or arrayref sorts by the given column(s):
95
96                       { order_by => 'foo' }           # equivalent to "ORDER BY foo"
97                       { order_by => [ qw(foo bar) ] } # equiv to "ORDER BY foo,bar"
98
99               ·   a hashref of "order =" column name>, e.g.:
100
101                       { order_by => { desc => 'foo' } } # equiv to ORDER BY foo DESC
102                       { order_by => [ { desc => 'foo' }, { asc => 'bar' } ] }
103                          # above is equiv to ORDER BY foo DESC, bar ASC
104
105           "limit"
106               Limit how many records will be returned; equivalent to e.g.
107               "LIMIT 1" in an SQL query.  If called in scalar context, an
108               implicit LIMIT 1 will be added to the query anyway, so you
109               needn't add it yourself.
110
111               An example of using options to control the results you get
112               back:
113
114                   # Get the name & phone number of the 10 highest-paid men:
115                   database->quick_select(
116                       'employees',
117                       { gender => 'male' },
118                       { order_by => 'salary', limit => 10, columns => [qw(name phone)] }
119                   );
120
121           "offset" number
122               "Offset" says to skip that many rows before beginning to return
123               rows (postgresql).
124
125               Example:
126
127                   # Get the name & phone number of the 10 highest-paid men starting from 11th:
128                   database->quick_select(
129                       'employees',
130                       { gender => 'male' },
131                       { order_by => 'salary', offset => 10, limit => 10, columns => [qw(name phone)] }
132                   );
133
134       quick_lookup
135             my $id  = database->quick_lookup($table, { email => $params->{'email'} }, 'userid' );
136
137           This is a bit of syntactic sugar when you just want to lookup a
138           specific field, such as when you're converting an email address to
139           a userid (say during a login handler.)
140
141           This call always returns a single scalar value, not a hashref of
142           the entire row (or partial row) like most of the other methods in
143           this library.
144
145           Returns undef when there's no matching row or no such field found
146           in the results.
147
148       quick_count
149             my $count = database->quick_count($table,
150                                               { email => $params->{'email'} });
151
152           This is syntactic sugar to return a count of all rows which match
153           your parameters, useful for pagination.
154
155           This call always returns a single scalar value, not a hashref of
156           the entire row (or partial row) like most of the other methods in
157           this library.
158
159       All of the convenience methods provided take care to quote table and
160       column names using DBI's "quote_identifier", and use parameterised
161       queries to avoid SQL injection attacks.  See
162       <http://www.bobby-tables.com/> for why this is important, if you're not
163       familiar with it.
164

WHERE clauses as hashrefs

166       "quick_update", "quick_delete" and "quick_select" take a hashref of
167       WHERE clauses.  This is a hashref of field => 'value', each of which
168       will be included in the WHERE clause used, for instance:
169
170         { id => 42 }
171
172       Will result in an SQL query which would include:
173
174         WHERE id = 42
175
176       When more than one field => value pair is given, they will be ANDed
177       together:
178
179         { foo => 'Bar', bar => 'Baz' }
180
181       Will result in:
182
183         WHERE foo = 'Bar' AND bar = 'Baz'
184
185       (Actually, parameterised queries will be used, with placeholders, so
186       SQL injection attacks will not work, but it's easier to illustrate as
187       though the values were interpolated directly.  Don't worry, they're
188       not.)
189
190       With the same idea in mind, you can check if a value is NULL with:
191
192         { foo => undef }
193
194       This will be correctly rewritten to "foo IS NULL".
195
196       You can pass an empty hashref if you  want all rows, e.g.:
197
198         database->quick_select('mytable', {});
199
200       ... is the same as "SELECT * FROM 'mytable'"
201
202       If you pass in an arrayref as the value, you can get a set clause as in
203       the following example:
204
205        { foo => [ 'bar', 'baz', 'quux' ] }
206
207       ... it's the same as "WHERE foo IN ('bar', 'baz', 'quux')"
208
209       If you need additional flexibility, you can build fairly complex where
210       clauses by passing a hashref of condition operators and values as the
211       value to the column field key.
212
213       Currently recognized operators are:
214
215       'like'
216            { foo => { 'like' => '%bar%' } }
217
218           ... same as "WHERE foo LIKE '%bar%'"
219
220       'ilike'
221           Postgres-specific - same as 'like', but case-insensitive.
222
223       'gt' / 'ge'
224            'greater than' or 'greater or equal to'
225
226            { foo => { 'ge' => '42' } }
227
228           ... same as "WHERE foo >= '42'"
229
230       'lt' / 'le'
231            'less than' or 'less or equal to'
232
233            { foo => { 'lt' => '42' } }
234
235           ... same as "WHERE foo < '42'"
236
237       'eq' / 'ne' / 'is'
238            'equal' or 'not equal' or 'is'
239
240            { foo => { 'ne' => 'bar' } }
241
242           ... same as "WHERE foo != 'bar'"
243
244       You can also include a key named 'not' with a true value in the hashref
245       which will (attempt) to negate the other operator(s).
246
247        { foo => { 'like' => '%bar%', 'not' => 1 } }
248
249       ... same as "WHERE foo NOT LIKE '%bar%'"
250
251       If you use undef as the value for an operator hashref it will be
252       replaced with 'NULL' in the query.
253
254       If that's not flexible enough, you can pass in your own scalar WHERE
255       clause string BUT there's no automatic sanitation on that - if you
256       suffer from a SQL injection attack - don't blame me!  Don't forget to
257       use "quote()"/"quote_identifier()" on it then.
258

AUTHOR

260       David Precious " <<davidp@preshweb.co.uk "> >
261

ACKNOWLEDGEMENTS

263       See "ACKNOWLEDGEMENTS" in Dancer::Plugin::Database
264

LICENSE AND COPYRIGHT

273       Copyright 2016 David Precious.
274
275       This program is free software; you can redistribute it and/or modify it
276       under the terms of the the Artistic License (2.0). You may obtain a
277       copy of the full license at:
278
279       <http://www.perlfoundation.org/artistic_license_2_0>
280
281       Any use, modification, and distribution of the Standard or Modified
282       Versions is governed by this Artistic License. By using, modifying or
283       distributing the Package, you accept this license. Do not use, modify,
284       or distribute the Package, if you do not accept this license.
285
286       If your Modified Version has been derived from a Modified Version made
287       by someone other than you, you are nevertheless required to ensure that
288       your Modified Version complies with the requirements of this license.
289
290       This license does not grant you the right to use any trademark, service
291       mark, tradename, or logo of the Copyright Holder.
292
293       This license includes the non-exclusive, worldwide, free-of-charge
294       patent license to make, have made, use, er to sell, sell, import and
295       otherwise transfer the Package with respect to any patent claims
296       licensable by the Copyright Holder that are necessarily infringed by
297       the Package. If you institute patent litigation (including a cross-
298       claim or counterclaim) against any party alleging that the Package
299       constitutes direct or contributory patent infringement, then this
300       Artistic License to you shall terminate on the date that such
301       litigation is filed.
302
303       Disclaimer of Warranty: THE PACKAGE IS PROVIDED BY THE COPYRIGHT HOLDER
304       AND CONTRIBUTORS "AS IS' AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES.
305       THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
306       PURPOSE, OR NON-INFRINGEMENT ARE DISCLAIMED TO THE EXTENT PERMITTED BY
307       YOUR LOCAL LAW. UNLESS REQUIRED BY LAW, NO COPYRIGHT HOLDER OR
308       CONTRIBUTOR WILL BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, OR
309       CONSEQUENTIAL DAMAGES ARISING IN ANY WAY OUT OF THE USE OF THE PACKAGE,
310       EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
311
312
313
314perl v5.28.0                      2016D-a0n9c-e0r1::Plugin::Database::Core::Handle(3)