Dancer2::Session::DatabasePlugin - Dancer2 Session implementation for databases
use Dancer2; use Dancer2::Plugin::Database; use Dancer2::Plugin::SessionDatabase;
This class extends Dancer2::Core::Role::SessionFactory, and makes use of Dancer2::Plugin::Database for managing database connections.
The session should be set to "DatabasePlugin" in order to use this session engine in your Dancer2 Application.
session: "DatabasePlugin" engines: session: DatabasePlugin: cache: 0 # default 0, when 1 statement handles are cached connection: "foo" session_table: "SESSIONS" id_column: "SESSION_ID" data_column: "SESSION_DATA" cache_sth: 0 # default 0, when set to 1 statement handles are cached plugins: Database: connections: foo: driver: "SQLite" database: "foo.sqlite"
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.
Testing and development was done using SQLite3.
Create statement is as follows:
create table sessions (session_id varchar unique,session_data blob);
All queries are generated using sprintf statements against constatins.
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.
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).
All SQL Statements are generated based on the given configuration.
Default Query Shown:
INSERT into SESSIONS (SESSION_ID,SESSION_DATA) values (?,?)
Sprintf Template:
INSERT into %s (%s,%s) values (?,?)
UPDATE SESSIONS SET SESSION_DATA=? WHERE SESSION_ID=?
UPDATE %s SET %s=? WHERE %s=?
DELETE FROM SESSIONS WHERE SESSION_ID=?
DELETE FROM %s WHERE %s=?
SELECT SESSION_DATA FROM SESSIONS WHERE SESSION_ID=?
SELECT %s FROM %s WHERE %s=?
SELECT SESSION_ID FROM SESSIONS
Sprintf Template
SELECT %s FROM %s
UPDATE SESSIONS SET SESSION_ID=? WHERE SESSION_ID=?
This package supports the default session engine hooks along with the following addtional hooks documented in this section.
This hook is run before the session engine calls the database function from Dancer2::Plugin::Database.
hook=>'engine.session.before_db'=>sub { my ($session)=@_; };
Note: This hook is used by Dancer2::Plugin::SessionDatabase to set the database handle in the session object at runtime.
This package makes use of hooks provdied by Dancer2::Database::Plugin.
This hook is used to clear the existing database statement handle cache.
If you access sessions preforking, you will need to reset the statement handle session cache.
Example:
The following code snippit will reset the built in statement handle cache to empty.
%{$Dancer2::Session::DatabasePlugin::CACHE}=();
To release the current database session, use the following code snippet.
$Dancer2::Plugin::SessionDatabase::DBH=undef;
Your database may not support globs or glob syntax, when this is the case it is possible to set a new subrouteens in place that handle the freezing and thawing of data.
The nfreeze code reference is stored here
$Dancer2::Session::DatabasePlugin::FREEZE
The thaw code reference is stored here
$Dancer2::Session::DatabasePlugin::THAW
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
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); } };
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'; } };
This example represents how to deal with some of the strange limitations when connecting via MSSQL via DBD::Sybase with FreeTDS.
The limitations are as follows: DBD::Sybase does not support multiple open statement handls when AuttoCommit is true. DBD::Sybase doesn't handle placeholders properly, and has some issues with binary data as well.
In our session configuration we need to do the following: Disable statement handle caching and turn off the standard query generation code for the following functions: [create_update_query,create_flush_query].
engines: session: DatabasePlugin: connection: "myconnection" session_table: "SESSIONS" id_column: "SESSION_ID" data_column: "SESSION_DATA" # Disable Caching of Statement handles cache: 0 # skip internal Statment handler creation code for the following no_create: create_update_query: 1 create_flush_query: 1
Our example database has AutoCommit Disabled.
plugins: Database: connections: socmon: driver: Sybase server: SOCMON_DEV username: username password: xxx database: myconnection dbi_params: RaiseError: 1 AutoCommit: 1 FetchHashKeyName: 'NAME_lc'
MSSQL has some odd quirks when it comes to binary data, so in this case we will use varchar(max).
create table SESSIONS ( session_id varchar(32) , session_data varchar(max), l astUpdate TimeStamp, CONSTRAINT AK_session_id UNIQUE(session_id) )
Finnaly in your Dancer2 App we add the following code.
use JSON qw(to_json from_jsom); $Dancer2::Session::DatabasePlugin::FREEZE=\&to_json; $Dancer2::Session::DatabasePlugin::THAW=\&from_json; $Dancer2::Session::DatabasePlugin::HANDLE_EXECUTE=sub { my ($name,$sth,@bind)=@_; if($name eq 'create_update_query') { my ($string,$id)=@bind; $string=~ s/'/''/g; $id=~ s/'/''/g; $Dancer2::Plugin::SessionDatabase::DBH->do("update sessions set session_data='$string' where session_id='$id'"); } elsif($name eq 'create_flush_query') { my ($id,$string)=@bind; $string=~ s/'/''/g; $id=~ s/'/''/g; $Dancer2::Plugin::SessionDatabase::DBH->do("insert into sessions (session_data,session_id) values ('$string','$id')"); } else { $sth->execute(@bind); } };
Dancer2::Plugin::Database Dancer2::Session::YAML
This softare is distributed under the Perl 5 License.
Michael Shipper <AKALINUX@cpan.org>
To install Dancer2::Session::DatabasePlugin, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Dancer2::Session::DatabasePlugin
CPAN shell
perl -MCPAN -e shell install Dancer2::Session::DatabasePlugin
For more information on module installation, please visit the detailed CPAN module installation guide.