Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
Dancer::Plugin::DatabaUserCContributedaPeer::Plugin::Database::Core::Handle(3)

NAME
       Dancer::Plugin::Database::Core::Handle -	subclassed DBI connection
       handle

DESCRIPTION
       Subclassed DBI connection handle	with added convenience features

SYNOPSIS
	 # 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
       A "Dancer::Plugin::Database::Handle" object is a	subclassed DBI::db DBI
       database	handle,	with the following added convenience methods:

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

	   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.

	   If you need any of the values to be interpolated straight into the
	   SQL,	for instance if	you need to use	a function call	like "NOW()"
	   or similar, then you	can provide them as a scalarref:

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

	   Of course, if you do	that, you must be careful to avoid SQL
	   injection attacks!

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

	   Given a table name, a hashref describing a where clause and a
	   hashref of changes, update a	row.

	   As per quick_insert,	if you need any	of the values to be
	   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' });

	   Of course, if you do	that, you must be careful to avoid SQL
	   injection attacks!

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

	   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.

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

	   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
	   "columns" option.

	   The options you can provide are:

	   "columns"
	       An arrayref of column names to return, if you only want certain
	       columns returned

	   "order_by"
	       Specify how the results should be ordered.  This	option can
	       take various values:

	       o   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"

	       o   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.  If called in	scalar context,	an
	       implicit	LIMIT 1	will be	added to the query anyway, so you
	       needn't add it yourself.

	       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 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.)

	   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.

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

	   This	is syntactic sugar to return a count of	all rows which match
	   your	parameters, 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.

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:

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

AUTHOR
       David Precious "	<<davidp@preshweb.co.uk	"> >

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

SEE ALSO
       Dancer::Plugin::Database	and Dancer2::Plugin::Database

       Dancer and Dancer2

       DBI

LICENSE	AND COPYRIGHT
       Copyright 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.

perl v5.24.1			  201Dancer::Plugin::Database::Core::Handle(3)

NAME | DESCRIPTION | SYNOPSIS | Added features | WHERE clauses as hashrefs | AUTHOR | ACKNOWLEDGEMENTS | SEE ALSO | LICENSE AND COPYRIGHT

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=Dancer::Plugin::Database::Core::Handle&sektion=3&manpath=FreeBSD+12.1-RELEASE+and+Ports>

home | help