GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages


Manual Reference Pages  -  DANCER2::PLUGIN::DATABASE (3)

.ds Aq ’

NAME

Dancer2::Plugin::Database - easy database connections for Dancer2 applications

CONTENTS

SYNOPSIS



    use Dancer2;
    use Dancer2::Plugin::Database;

    # Calling the database keyword will get you a connected database handle:
    get /widget/view/:id => sub {
        my $sth = database->prepare(
            select * from widgets where id = ?,
        );
        $sth->execute(params->{id});
        template display_widget, { widget => $sth->fetchrow_hashref };
    };

    # The handle is a Dancer::Plugin::Database::Core::Handle object, which subclasses
    # DBIs DBI::db handle and adds a few convenience features, for example:
    get /insert/:name => sub {
        database->quick_insert(people, { name => params->{name} });
    };

    get /users/:id => sub {
        template display_user, {
            person => database->quick_select(users, { id => params->{id} }),
        };
    };

    dance;



Database connection details are read from your Dancer2 application config - see below.

DESCRIPTION

Provides an easy way to obtain a connected DBI database handle by simply calling the database keyword within your Dancer2 application

Returns a Dancer::Plugin::Database::Core::Handle object, which is a subclass of DBI’s DBI::db connection handle object, so it does everything you’d expect to do with DBI, but also adds a few convenience methods. See the documentation for 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 connection_check_threshold seconds ago, it will check that the connection is still alive, using either the $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.)

CONFIGURATION

Connection details will be taken from your Dancer2 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 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 dbi_params setting is also optional, and if specified, should be settings which can be passed to DBI->connect as its fourth argument; see the DBI documentation for these.

The optional 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 $dbh->do. (If using MySQL, you might want to use this to set 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 <http://effectivemysql.com/presentation/mysql-idiosyncrasies-that-bite/> for just one illustration. In hindsight, I wish I’d made a sensible sql_mode a default setting, but I don’t want to change that now.)

The optional log_queries setting enables logging of queries generated by the helper functions quick_insert et al in 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 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 handle_class defines your own class into which database handles should be blessed. This should be a subclass of Dancer::Plugin::Database::Core::Handle (or 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 hostname, port etc, but will need to supply database as the name of the SQLite database file:



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



For Oracle, you may want to pass 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



    DEFINING MULTIPLE CONNECTIONS

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



    plugins:
        Database:
            connections:
                foo:
                    driver: "SQLite"
                    database: "foo.sqlite"
                bar:
                    driver: "mysql"
                    host: "localhost"
                    ....



Then, you can call the database keyword with the name of the database connection you want, for example:



    my $foo_dbh = database(foo);
    my $bar_dbh = database(bar);



RUNTIME CONFIGURATION

You can pass a hashref to the database() keyword to provide configuration details to override any in the config file at runtime if desired, for instance:



    my $dbh = database({ driver => SQLite, database => $filename });



(Thanks to Alan Haggai for this feature.)

AUTOMATIC UTF-8 SUPPORT

As of version 1.20, if your application is configured to use UTF-8 (you’ve defined the charset setting in your app config as UTF-8) then support for UTF-8 for the database connection will be enabled, if we know how to do so for the database driver in use.

If you do not want this behaviour, set auto_utf8 to a false value when providing the connection details.

GETTING A DATABASE HANDLE

Calling database will return a connected database handle; the first time it is called, the plugin will establish a connection to the database, and return a reference to the DBI object. On subsequent calls, the same DBI connection object will be returned, unless it has been found to be no longer usable (the connection has gone away), in which case a fresh connection will be obtained.

If you have declared named connections as described above in ’DEFINING MULTIPLE CONNECTIONS’, then calling the database() keyword with the name of the connection as specified in the config file will get you a database handle connected with those details.

You can also pass a hashref of settings if you wish to provide settings at runtime.

CONVENIENCE FEATURES

The handle returned by the database keyword is a Dancer::Plugin::Database::Core::Handle object, which subclasses the DBI::db DBI connection handle. This means you can use it just like you’d normally use a DBI handle, but extra convenience methods are provided.

There’s extensive documentation on these features in Dancer::Plugin::Database::Core::Handle, including using the order_by, limit, columns options to sort / limit results and include only specific columns.

HOOKS

This plugin uses Dancer2’s hooks support to allow you to register code that should execute at given times - for example:



    hook database_connected => sub {
        my $dbh = shift;
        # do something with the new DB handle here
    };



Currrently defined hook positions are:
database_connected Called when a new database connection has been established, after performing any on_connect_do statements, but before the handle is returned. Receives the new database handle as a parameter, so that you can do what you need with it.
database_connection_lost Called when the plugin detects that the database connection has gone away. Receives the no-longer usable handle as a parameter, in case you need to extract some information from it (such as which server it was connected to).
database_connection_failed Called when an attempt to connect to the database fails. Receives a hashref of connection settings as a parameter, containing the settings the plugin was using to connect (as obtained from the config file).
database_error Called when a database error is raised by DBI. Receives two parameters: the error message being returned by DBI, and the database handle in question.
If you need other hook positions which would be useful to you, please feel free to suggest them!

AUTHOR

David Precious, <davidp@preshweb.co.uk>

CONTRIBUTING

This module is developed on Github at:

<http://github.com/bigpresh/Dancer-Plugin-Database>

Feel free to fork the repo and submit pull requests! Also, it makes sense to watch the repo <https://github.com/bigpresh/Dancer-Plugin-Database/toggle_watch> on GitHub for updates.

Feedback and bug reports are always appreciated. Even a quick mail to let me know the module is useful to you would be very nice - it’s nice to know if code is being actively used.

ACKNOWLEDGEMENTS

Igor Bujna

Franck Cuny

Alan Haggai

Christian Sa\k:'\h |\n:unchez

Michael Stiller

Martin J Evans

Carlos Sosa

Matt S Trout

Matthew Vickers

Christian Walde

Alberto Simo\k:~es

James Aitken (LoonyPandora)

Mark Allen (mrallen1)

Sergiy Borodych (bor)

Mario Domgoergen (mdom)

Andrey Inishev (inish777)

Nick S. Knutov (knutov)

Nicolas Franck (nicolasfranck)

mscolly

BUGS

Please report any bugs or feature requests to bug-dancer-plugin-database at rt.cpan.org, or through the web interface at <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Dancer2-Plugin-Database>. I will be notified, and then you’ll automatically be notified of progress on your bug as I make changes.

SUPPORT

You can find documentation for this module with the perldoc command.



    perldoc Dancer2::Plugin::Database



You can also look for information at:
o RT: CPAN’s request tracker

<http://rt.cpan.org/NoAuth/Bugs.html?Dist=Dancer2-Plugin-Database>

o AnnoCPAN: Annotated CPAN documentation

<http://annocpan.org/dist/Dancer2-Plugin-Database>

o CPAN Ratings

<http://cpanratings.perl.org/d/Dancer2-Plugin-Database>

o Search CPAN

<http://search.cpan.org/dist/Dancer2-Plugin-Database/>

You can find the author on IRC in the channel #dancer on <irc.perl.org>.

LICENSE AND COPYRIGHT

Copyright 2010-2016 David Precious.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.

SEE ALSO

Dancer::Plugin::Database::Core and Dancer::Plugin::Database::Core::Handle

Dancer, Dancer2

DBI

Dancer::Plugin::SimpleCRUD

Search for    or go to Top of page |  Section 3 |  Main Index


perl v5.20.3 DANCER2::PLUGIN::DATABASE (3) 2016-03-30

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with manServer 1.07.