File Coverage

lib/Dancer/Plugin/Database.pm
Criterion Covered Total %
statement 23 23 100.0
branch 2 2 100.0
condition 1 3 33.3
subroutine 9 9 100.0
pod n/a
total 35 37 94.5


line stmt bran cond sub pod time code
1             package Dancer::Plugin::Database;
2              
3 2     2   405311 use strict;
  2         4  
  2         53  
4              
5 2     2   1062 use Dancer::Plugin::Database::Core;
  2         2902  
  2         64  
6 2     2   1180 use Dancer::Plugin::Database::Core::Handle;
  2         29457  
  2         54  
7              
8 2     2   17 use Dancer ':syntax';
  2         2  
  2         11  
9 2     2   1613 use Dancer::Plugin;
  2         1967  
  2         496  
10              
11             =encoding utf8
12              
13             =head1 NAME
14              
15             Dancer::Plugin::Database - easy database connections for Dancer applications
16              
17             =cut
18              
19             our $VERSION = '2.12';
20              
21             my $settings = undef;
22              
23             sub _load_db_settings {
24 1     1   6 $settings = plugin_setting();
25 1   33     26 $settings->{charset} ||= setting('charset');
26             }
27              
28 3     3   327 sub _logger { Dancer::Logger->can( $_[0] )->( $_[1] ) }
29              
30 10     10   4866 sub _execute_hook { execute_hook(@_) }
31              
32             register database => sub {
33 54 100   54   170799 _load_db_settings() unless $settings;
34 54         300 my ($dbh, $cfg) = Dancer::Plugin::Database::Core::database( arg => $_[0],
35             logger => \&_logger,
36             hook_exec => \&_execute_hook,
37             settings => $settings );
38 54         4052 $settings = $cfg;
39 54         291 return $dbh;
40             };
41              
42             register_hook(qw(database_connected
43             database_connection_lost
44             database_connection_failed
45             database_error));
46              
47             register_plugin;
48              
49             =head1 SYNOPSIS
50              
51             use Dancer;
52             use Dancer::Plugin::Database;
53              
54             # Calling the database keyword will get you a connected database handle:
55             get '/widget/view/:id' => sub {
56             my $sth = database->prepare(
57             'select * from widgets where id = ?',
58             );
59             $sth->execute(params->{id});
60             template 'display_widget', { widget => $sth->fetchrow_hashref };
61             };
62              
63             # The handle is a Dancer::Plugin::Database::Core::Handle object, which subclasses
64             # DBI's DBI::db handle and adds a few convenience features, for example:
65             get '/insert/:name' => sub {
66             database->quick_insert('people', { name => params->{name} });
67             };
68              
69             get '/users/:id' => sub {
70             template 'display_user', {
71             person => database->quick_select('users', { id => params->{id} }),
72             };
73             };
74              
75             dance;
76              
77             Database connection details are read from your Dancer application config - see
78             below.
79              
80              
81             =head1 DESCRIPTION
82              
83             Provides an easy way to obtain a connected DBI database handle by simply calling
84             the database keyword within your L<Dancer> application
85              
86             Returns a L<Dancer::Plugin::Database::Core::Handle> object, which is a subclass of
87             L<DBI>'s C<DBI::db> connection handle object, so it does everything you'd expect
88             to do with DBI, but also adds a few convenience methods. See the documentation
89             for L<Dancer::Plugin::Database::Core::Handle> for full details of those.
90              
91             Takes care of ensuring that the database handle is still connected and valid.
92             If the handle was last asked for more than C<connection_check_threshold> seconds
93             ago, it will check that the connection is still alive, using either the
94             C<< $dbh->ping >> method if the DBD driver supports it, or performing a simple
95             no-op query against the database if not. If the connection has gone away, a new
96             connection will be obtained and returned. This avoids any problems for
97             a long-running script where the connection to the database might go away.
98              
99             Care is taken that handles are not shared across processes/threads, so this
100             should be thread-safe with no issues with transactions etc. (Thanks to Matt S
101             Trout for pointing out the previous lack of thread safety. Inspiration was
102             drawn from DBIx::Connector.)
103              
104             =head1 CONFIGURATION
105              
106             Connection details will be taken from your Dancer application config file, and
107             should be specified as, for example:
108              
109             plugins:
110             Database:
111             driver: 'mysql'
112             database: 'test'
113             host: 'localhost'
114             port: 3306
115             username: 'myusername'
116             password: 'mypassword'
117             connection_check_threshold: 10
118             dbi_params:
119             RaiseError: 1
120             AutoCommit: 1
121             on_connect_do: ["SET NAMES 'utf8'", "SET CHARACTER SET 'utf8'" ]
122             log_queries: 1
123             handle_class: 'My::Super::Sexy::Database::Handle'
124              
125             The C<connection_check_threshold> setting is optional, if not provided, it
126             will default to 30 seconds. If the database keyword was last called more than
127             this number of seconds ago, a quick check will be performed to ensure that we
128             still have a connection to the database, and will reconnect if not. This
129             handles cases where the database handle hasn't been used for a while and the
130             underlying connection has gone away.
131              
132             The C<dbi_params> setting is also optional, and if specified, should be settings
133             which can be passed to C<< DBI->connect >> as its fourth argument; see the L<DBI>
134             documentation for these.
135              
136             The optional C<on_connect_do> setting is an array of queries which should be
137             performed when a connection is established; if given, each query will be
138             performed using C<< $dbh->do >>. (If using MySQL, you might want to use this to
139             set C<SQL_MODE> to a suitable value to disable MySQL's built-in free data loss
140             'features', for example:
141              
142             on_connect_do: "SET SQL_MODE='TRADITIONAL'"
143              
144             (If you're not familiar with what I mean, I'm talking about the insane default
145             behaviour of "hmm, this bit of data won't fit the column you're trying to put it
146             in.. hmm, I know, I'll just munge it to fit, and throw a warning afterwards -
147             it's not like you're relying on me to, y'know, store what you ask me to store".
148             See L<http://effectivemysql.com/presentation/mysql-idiosyncrasies-that-bite/> for
149             just one illustration. In hindsight, I wish I'd made a sensible C<sql_mode> a
150             default setting, but I don't want to change that now.)
151              
152             The optional C<log_queries> setting enables logging of queries generated by the
153             helper functions C<quick_insert> et al in L<Dancer::Plugin::Database::Core::Handle>.
154             If you enable it, generated queries will be logged at 'debug' level. Be aware
155             that they will contain the data you're passing to/from the database, so be
156             careful not to enable this option in production, where you could inadvertently
157             log sensitive information.
158              
159             If you prefer, you can also supply a pre-crafted DSN using the C<dsn> setting;
160             in that case, it will be used as-is, and the driver/database/host settings will
161             be ignored. This may be useful if you're using some DBI driver which requires
162             a peculiar DSN.
163              
164             The optional C<handle_class> defines your own class into which database handles
165             should be blessed. This should be a subclass of
166             L<Dancer::Plugin::Database::Core::Handle> (or L<DBI::db> directly, if you just want to
167             skip the extra features).
168              
169             You will require slightly different options depending on the database engine
170             you're talking to. For instance, for SQLite, you won't need to supply
171             C<hostname>, C<port> etc, but will need to supply C<database> as the name of the
172             SQLite database file:
173              
174             plugins:
175             Database:
176             driver: SQLite
177             database: 'foo.sqlite'
178              
179             For Oracle, you may want to pass C<sid> (system ID) to identify a particular
180             database, e.g.:
181              
182             plugins:
183             Database:
184             driver: Oracle
185             host: localhost
186             sid: ABC12
187              
188             If you have any further connection parameters that need to be appended
189             to the dsn, you can put them in as a hash called dsn_extra. For
190             example, if you're running mysql on a non-standard socket, you could
191             have
192              
193             plugins:
194             Database:
195             driver: mysql
196             host: localhost
197             dsn_extra:
198             mysql_socket: /tmp/mysql_staging.sock
199              
200              
201             =head2 DEFINING MULTIPLE CONNECTIONS
202              
203             If you need to connect to multiple databases, this is easy - just list them in
204             your config under C<connections> as shown below:
205              
206             plugins:
207             Database:
208             connections:
209             foo:
210             driver: "SQLite"
211             database: "foo.sqlite"
212             bar:
213             driver: "mysql"
214             host: "localhost"
215             ....
216              
217             Then, you can call the C<database> keyword with the name of the database
218             connection you want, for example:
219              
220             my $foo_dbh = database('foo');
221             my $bar_dbh = database('bar');
222              
223              
224             =head1 RUNTIME CONFIGURATION
225              
226             You can pass a hashref to the C<database()> keyword to provide configuration
227             details to override any in the config file at runtime if desired, for instance:
228              
229             my $dbh = database({ driver => 'SQLite', database => $filename });
230              
231             (Thanks to Alan Haggai for this feature.)
232              
233             =head1 AUTOMATIC UTF-8 SUPPORT
234              
235             As of version 1.20, if your application is configured to use UTF-8 (you've
236             defined the C<charset> setting in your app config as C<UTF-8>) then support for
237             UTF-8 for the database connection will be enabled, if we know how to do so for
238             the database driver in use.
239              
240             If you do not want this behaviour, set C<auto_utf8> to a false value when
241             providing the connection details.
242              
243              
244              
245             =head1 GETTING A DATABASE HANDLE
246              
247             Calling C<database> will return a connected database handle; the first time it is
248             called, the plugin will establish a connection to the database, and return a
249             reference to the DBI object. On subsequent calls, the same DBI connection
250             object will be returned, unless it has been found to be no longer usable (the
251             connection has gone away), in which case a fresh connection will be obtained.
252              
253             If you have declared named connections as described above in 'DEFINING MULTIPLE
254             CONNECTIONS', then calling the database() keyword with the name of the
255             connection as specified in the config file will get you a database handle
256             connected with those details.
257              
258             You can also pass a hashref of settings if you wish to provide settings at
259             runtime.
260              
261              
262             =head1 CONVENIENCE FEATURES (quick_select, quick_update, quick_insert, quick_delete, quick_count)
263              
264             The handle returned by the C<database> keyword is a
265             L<Dancer::Plugin::Database::Core::Handle> object, which subclasses the C<DBI::db> DBI
266             connection handle. This means you can use it just like you'd normally use a DBI
267             handle, but extra convenience methods are provided, as documented in the POD for
268             L<Dancer::Plugin::Database::Core::Handle>.
269              
270             Examples:
271              
272             # Quickly fetch the (first) row whose ID is 42 as a hashref:
273             my $row = database->quick_select($table_name, { id => 42 });
274              
275             # Fetch all badgers as an array of hashrefs:
276             my @badgers = database->quick_select('animals', { genus => 'Mellivora' });
277              
278             # Update the row where the 'id' column is '42', setting the 'foo' column to
279             # 'Bar':
280             database->quick_update($table_name, { id => 42 }, { foo => 'Bar' });
281              
282             # Insert a new row, using a named connection (see above)
283             database('connectionname')->quick_insert($table_name, { foo => 'Bar' });
284              
285             # Delete the row with id 42:
286             database->quick_delete($table_name, { id => 42 });
287              
288             # Fetch all rows from a table (since version 1.30):
289             database->quick_select($table_name, {});
290              
291             # Retrieve a count of rows matching the criteria:
292             database->quick_count($table_name, {});
293              
294             There's more extensive documentation on these features in
295             L<Dancer::Plugin::Database::Core::Handle>, including using the C<order_by>, C<limit>,
296             C<columns> options to sort / limit results and include only specific columns.
297              
298             =head1 HOOKS
299              
300             This plugin uses Dancer's hooks support to allow you to register code that
301             should execute at given times - for example:
302              
303             hook 'database_connected' => sub {
304             my $dbh = shift;
305             # do something with the new DB handle here
306             };
307              
308             Currrently defined hook positions are:
309              
310             =over 4
311              
312             =item C<database_connected>
313              
314             Called when a new database connection has been established, after performing any
315             C<on_connect_do> statements, but before the handle is returned. Receives the
316             new database handle as a parameter, so that you can do what you need with it.
317              
318             =item C<database_connection_lost>
319              
320             Called when the plugin detects that the database connection has gone away.
321             Receives the no-longer usable handle as a parameter, in case you need to extract
322             some information from it (such as which server it was connected to).
323              
324             =item C<database_connection_failed>
325              
326             Called when an attempt to connect to the database fails. Receives a hashref of
327             connection settings as a parameter, containing the settings the plugin was using
328             to connect (as obtained from the config file).
329              
330             =item C<database_error>
331              
332             Called when a database error is raised by C<DBI>. Receives two parameters: the
333             error message being returned by DBI, and the database handle in question.
334              
335             =back
336              
337             If you need other hook positions which would be useful to you, please feel free
338             to suggest them!
339              
340              
341             =head1 AUTHOR
342              
343             David Precious, C<< <davidp@preshweb.co.uk> >>
344              
345              
346              
347             =head1 CONTRIBUTING
348              
349             This module is developed on Github at:
350              
351             L<http://github.com/bigpresh/Dancer-Plugin-Database>
352              
353             Feel free to fork the repo and submit pull requests! Also, it makes sense to
354             L<watch the repo|https://github.com/bigpresh/Dancer-Plugin-Database/toggle_watch>
355             on GitHub for updates.
356              
357             Feedback and bug reports are always appreciated. Even a quick mail to let me
358             know the module is useful to you would be very nice - it's nice to know if code
359             is being actively used.
360              
361             =head1 ACKNOWLEDGEMENTS
362              
363             Igor Bujna
364              
365             Franck Cuny
366              
367             Alan Haggai
368              
369             Christian Sánchez
370              
371             Michael Stiller
372              
373             Martin J Evans
374              
375             Carlos Sosa
376              
377             Matt S Trout
378              
379             Matthew Vickers
380              
381             Christian Walde
382              
383             Alberto Simões
384              
385             James Aitken (LoonyPandora)
386              
387             Mark Allen (mrallen1)
388              
389             Sergiy Borodych (bor)
390              
391             Mario Domgoergen (mdom)
392              
393             Andrey Inishev (inish777)
394              
395             Nick S. Knutov (knutov)
396              
397             Nicolas Franck (nicolasfranck)
398              
399             mscolly
400              
401             =head1 BUGS
402              
403             Please report any bugs or feature requests to C<bug-dancer-plugin-database at rt.cpan.org>, or through
404             the web interface at L<http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Dancer-Plugin-Database>. I will be notified, and then you'll
405             automatically be notified of progress on your bug as I make changes.
406              
407              
408              
409              
410             =head1 SUPPORT
411              
412             You can find documentation for this module with the perldoc command.
413              
414             perldoc Dancer::Plugin::Database
415              
416              
417             You can also look for information at:
418              
419             =over 4
420              
421             =item * RT: CPAN's request tracker
422              
423             L<http://rt.cpan.org/NoAuth/Bugs.html?Dist=Dancer-Plugin-Database>
424              
425             =item * AnnoCPAN: Annotated CPAN documentation
426              
427             L<http://annocpan.org/dist/Dancer-Plugin-Database>
428              
429             =item * CPAN Ratings
430              
431             L<http://cpanratings.perl.org/d/Dancer-Plugin-Database>
432              
433             =item * Search CPAN
434              
435             L<http://search.cpan.org/dist/Dancer-Plugin-Database/>
436              
437             =back
438              
439             You can find the author on IRC in the channel C<#dancer> on <irc.perl.org>.
440              
441              
442             =head1 LICENSE AND COPYRIGHT
443              
444             Copyright 2010-2015 David Precious.
445              
446             This program is free software; you can redistribute it and/or modify it
447             under the terms of either: the GNU General Public License as published
448             by the Free Software Foundation; or the Artistic License.
449              
450             See http://dev.perl.org/licenses/ for more information.
451              
452              
453             =head1 SEE ALSO
454              
455             L<Dancer>
456              
457             L<DBI>
458              
459             L<Dancer::Plugin::SimpleCRUD>
460              
461             =cut
462              
463             1; # End of Dancer::Plugin::Database