NAME

Dancer2::Session::DatabasePlugin - Dancer2 Session implementation for databases

SYNOPSIS

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

DESCRIPTION

This class extends Dancer2::Core::Role::SessionFactory, and makes use of Dancer2::Plugin::Database for managing database connections.

CONFIGURATION

The session should be set to "DatabasePlugin" in order to use this session engine in your Dancer2 Application.

  session: "DatabasePlugin"

  engines:
    session:
      DatabasePlugin:
        connection: "foo"
        session_table: "SESSIONS"
        id_column:     "SESSION_ID"
        data_column:   "SESSION_DATA"

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

Expected Schema

The code was developed to use a table with 2 columns: SESSION_ID, SESSION_DATA, additional columns will not impact the code. No records are deleted unless the session destroy is called, so cleanup is something that may need to be done over time.

The sql statements are generated based on the configuration options, session_table, id_column, and data_column.

Example Schema

Testing and development was done using SQLite3.

Create statement is as follows:

  create table sessions (session_id varchar unique,session_data blob);

How Queries are generated

All queries are generated using sprintf statements against constatins.

Column SESSION_ID

This column must have constraint defining the values as unique. The id is a string representing the current session, internals from Dancer2::Core::Session seems to return a 32 byte long string. It is highly recommended this column be indexed.

Column SESSION_DATA

This field is expected to be a BLOB or binary data type, although a large text field should work. The data being written to this column is generated by using Storable::nfreeze($ref).

SQL Statements

All SQL Statements are generated based on the given configuration.

Insert

Default Query Shown:

  INSERT into SESSIONS (SESSION_ID,SESSION_DATA) values (?,?) 

Sprintf Template:

  INSERT into %s (%s,%s) values (?,?)

Update Existing session

Default Query Shown:

  UPDATE SESSIONS SET SESSION_DATA=? WHERE SESSION_ID=?

Sprintf Template:

  UPDATE %s SET %s=? WHERE %s=?

Delete

Default Query Shown:

  DELETE FROM SESSIONS WHERE SESSION_ID=?

Sprintf Template:

  DELETE FROM %s WHERE %s=?

SELECT Current Session

Default Query Shown:

  SELECT SESSION_DATA FROM SESSIONS WHERE SESSION_ID=?

Sprintf Template:

  SELECT %s FROM %s WHERE %s=?

SELECT All Session Keys

Default Query Shown:

  SELECT SESSION_ID FROM SESSIONS

Sprintf Template

  SELECT %s FROM %s

Rename Session

Default Query Shown:

  UPDATE SESSIONS SET SESSION_ID=? WHERE SESSION_ID=?

Sprintf Template:

  UPDATE %s SET %s=? WHERE %s=?

Dancer2::Plugin::Database hooks

This package makes use of hooks provdied by Dancer2::Database::Plugin.

"database_connection_lost"

This hook is used to clear the existing database statement handle cache.

"database_error"

This hook is used to clear the existing database statement handle cache.

Notes

Database Acces Pre-Fork

If you access sessions preforking, you will need to reset the statement handle session cache.

Example:

  %{$Dancer2::Session::DatabasePlugin::CACHE}=();

Specal Examples

Oracle in general

Oracle has some odd quirks, here is an example configuration that may help solve more than a few problems.

  Database:
    connections:
      myoracledb:
        driver: "Oracle:(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = my.oracle.server.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=ORACLE.SERVICE.COM)))"
        username: OracleUser
        password: 'xxxxxxx'
        dbi_params:
           RaiseError: 1
           AutoCommit: 1
           FetchHashKeyName: 'NAME_uc'
           LongReadLen: 1000000

The manual bind example ( Oracle and the like )

Some databases require manual binds for blob. Here is an example of how to do this for Oracle.

  use DBD::Oracle qw(:ora_types);
  use Dancer2;
  use Dancer2::Plugin::Database;
  use Dancer2::Plugin::SessionDatabase;

  $Dancer2::Session::DatabasePlugin::HANDLE_EXECUTE=sub {
    my ($name,$sth,@bind)=@_;
    if($name eq 'create_update_query') {
      my ($string,$id)=@bind;
      $sth->bind_param(1,$string,{ora_type => ORA_BLOB });
      $sth->bind_param(2,$id,{ora_type => ORA_VARCHAR2});
      $sth->execute();
    } elsif($name eq 'create_flush_query') {
      my ($id,$string)=@bind;
      $sth->bind_param(1,$id,{ora_type => ORA_VARCHAR2});
      $sth->bind_param(2,$string,{ora_type => ORA_BLOB });
      $sth->execute();
    } else {
      $sth->execute(@bind);
    }
  };

Completly Changing an SQL statement

Sometimes you may want to replace the query created with something entierly new. To do this you will need to set $HANDLE_SQL_STRING function refrerence.

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

  $Dancer2::Session::DatabasePlugin::HANDLE_SQL_STRING=sub {
    my ($name)=@_;
    if($name eq 'query_to_alter') {
      $_[1]='some new sql statement';
    }
  };

See Also

Dancer2::Plugin::Database Dancer2::Session::YAML

LICENSE

This softare is distributed under the Perl 5 License.

AUTHOR

Michael Shipper <AKALINUX@cpan.org>

cpanm Dancer2::Session::DatabasePlugin

perl -MCPAN -e shell
install Dancer2::Session::DatabasePlugin