|
NAMEDancer::Plugin::Database::Core::Handle - subclassed DBI connection handleDESCRIPTIONSubclassed DBI connection handle with added convenience featuresSYNOPSIS# 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 featuresA "Dancer::Plugin::Database::Handle" object is a subclassed DBI::db DBI database handle, with the following added convenience methods:
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:
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. AUTHORDavid Precious " <<davidp@preshweb.co.uk "> >ACKNOWLEDGEMENTSSee "ACKNOWLEDGEMENTS" in Dancer::Plugin::DatabaseSEE ALSODancer::Plugin::Database and Dancer2::Plugin::DatabaseDancer and Dancer2 DBI LICENSE AND COPYRIGHTCopyright 2016 David Precious.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: <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. Do not use, modify, or distribute the Package, if you do not accept this license. 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. This license does not grant you the right to use any trademark, service mark, tradename, or logo of the Copyright Holder. 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. 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.
Visit the GSP FreeBSD Man Page Interface. |