view release on metacpan or  search on metacpan

lib/Dancer/Plugin/Database.pm  view on Meta::CPAN

for L<Dancer::Plugin::Database::Core::Handle> for full details of those.

Takes care of ensuring that the database handle is still connected and valid.
If the handle was last asked for more than C<connection_check_threshold> seconds
ago, it will check that the connection is still alive, using either the 
C<< $dbh->ping >> method if the DBD driver supports it, or performing a simple
no-op query against the database if not.  If the connection has gone away, a new
connection will be obtained and returned.  This avoids any problems for
a long-running script where the connection to the database might go away.

Care is taken that handles are not shared across processes/threads, so this
should be thread-safe with no issues with transactions etc.  (Thanks to Matt S
Trout for pointing out the previous lack of thread safety.  Inspiration was
drawn from DBIx::Connector.)

=head1 CONFIGURATION

Connection details will be taken from your Dancer application config file, and
should be specified as, for example: 

    plugins:
        Database:
            driver: 'mysql'
            database: 'test'
            host: 'localhost'
            port: 3306
            username: 'myusername'
            password: 'mypassword'
            connection_check_threshold: 10
            dbi_params:
                RaiseError: 1
                AutoCommit: 1
            on_connect_do: ["SET NAMES 'utf8'", "SET CHARACTER SET 'utf8'" ]
            log_queries: 1
            handle_class: 'My::Super::Sexy::Database::Handle'

The C<connection_check_threshold> setting is optional, if not provided, it
will default to 30 seconds.  If the database keyword was last called more than
this number of seconds ago, a quick check will be performed to ensure that we
still have a connection to the database, and will reconnect if not.  This
handles cases where the database handle hasn't been used for a while and the
underlying connection has gone away.

The C<dbi_params> setting is also optional, and if specified, should be settings
which can be passed to C<< DBI->connect >> as its fourth argument; see the L<DBI>
documentation for these.

The optional C<on_connect_do> setting is an array of queries which should be
performed when a connection is established; if given, each query will be
performed using C<< $dbh->do >>.  (If using MySQL, you might want to use this to
set C<SQL_MODE> to a suitable value to disable MySQL's built-in free data loss
'features', for example:

  on_connect_do: "SET SQL_MODE='TRADITIONAL'"

(If you're not familiar with what I mean, I'm talking about the insane default
behaviour of "hmm, this bit of data won't fit the column you're trying to put it
in.. hmm, I know, I'll just munge it to fit, and throw a warning afterwards -
it's not like you're relying on me to, y'know, store what you ask me to store".
See L<http://effectivemysql.com/presentation/mysql-idiosyncrasies-that-bite/> for
just one illustration.  In hindsight, I wish I'd made a sensible C<sql_mode> a
default setting, but I don't want to change that now.)

The optional C<log_queries> setting enables logging of queries generated by the
helper functions C<quick_insert> et al in L<Dancer::Plugin::Database::Core::Handle>.
If you enable it, generated queries will be logged at 'debug' level.  Be aware
that they will contain the data you're passing to/from the database, so be
careful not to enable this option in production, where you could inadvertently
log sensitive information.

If you prefer, you can also supply a pre-crafted DSN using the C<dsn> setting;
in that case, it will be used as-is, and the driver/database/host settings will 
be ignored.  This may be useful if you're using some DBI driver which requires 
a peculiar DSN.

The optional C<handle_class> defines your own class into which database handles
should be blessed.  This should be a subclass of
L<Dancer::Plugin::Database::Core::Handle> (or L<DBI::db> directly, if you just want to
skip the extra features).

You will require slightly different options depending on the database engine
you're talking to.  For instance, for SQLite, you won't need to supply
C<hostname>, C<port> etc, but will need to supply C<database> as the name of the
SQLite database file:

    plugins:
        Database:
            driver: SQLite
            database: 'foo.sqlite'

For Oracle, you may want to pass C<sid> (system ID) to identify a particular
database, e.g.:

    plugins:
        Database:
            driver: Oracle
            host: localhost
            sid: ABC12

If you have any further connection parameters that need to be appended
to the dsn, you can put them in as a hash called dsn_extra. For
example, if you're running mysql on a non-standard socket, you could
have

   plugins:
       Database:
           driver: mysql
           host: localhost
           dsn_extra:
               mysql_socket: /tmp/mysql_staging.sock


=head2 DEFINING MULTIPLE CONNECTIONS

If you need to connect to multiple databases, this is easy - just list them in
your config under C<connections> as shown below:

    plugins:
        Database:
            connections:
                foo: