Debian Codegraph @

README

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