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