• Files
• Commits
• Log
• Graph
• Stats

package Reporter::DPAE::DB;

use Moo;
with "Dancer2::Plugin::Auth::Extensible::Role::Provider";
use Dancer2::Core::Types qw/HashRef Str/;
use Reporter::Class; # provides Moo, Modern::Perl & Data::Printer::p
use namespace::clean;

has dbix => ( is => 'lazy' );

# these are passed in from config.yml (provider: DB):
has db_name => (
    is       => 'ro',
    isa      => Str,
    required => 1,
);
has db_cols=> (
    is       => 'ro',
    isa      => HashRef,
    required => 1,
);
has roles_key => (
    is      => 'ro',
    default => 'user_roles',
);
# has disable_roles => (); # causes fatal error, seems to be automatically picked up
no Moo;

our $VERSION = '0.705';

=begin
Provider class for Dancer2::Plugin::Auth::Extensible - uses DBIx::Simple and
SQL::Abstract::More for db queries; based on DPAE::Provider::Database

Expected methods: authenticate_user, get_user_details, get_user_roles

Can skip password if development env and param SKIP_PASSWORD used

How it works: DPAE authenticate_user() calls each realm in turn (hash so cannot
control the order!) and each realm provider (eg config, database, etc) uses its
own authenticate_user() to match username/password against its resource. 1st one
to get successful match wins.

native behaviour is user_details called x3 & user_roles x2 per request, but cannot
use global cache or a Moo attr as user data will persist between requests, so
adapted to use app session store for user data to save multiple db lookups

app settings = $self->plugin->app->settings
app session  = $self->plugin->app->session
=cut

use SQL::Abstract::More;
use Modern::Perl;
use DBIx::Simple; # don't need persistent dbh from Local::DB
use Data::Dumper;

use Local::Utils; # sha1 digest
use Local::DB; # for dsn, not dbix

sub app_settings { shift->plugin->app->settings }
sub session_get  { shift->plugin->app->session->read(@_) }
sub session_set  { shift->plugin->app->session->write(@_) }
sub debug        { p $_[1] if $_[0]->app_settings->{environment} =~ /^dev/ }


#===============================================================================
sub authenticate_user {
    # username & password from web form:
    my ($self, $username, $password) = @_; $self->debug('authenticating user');

    # Look up the user or return empty:
    my $user = $self->get_user_details($username) || return 0;

    # allow dev env to skip password:
    return 1 if $self->app_settings->{environment} eq 'development'
        && $ENV{SKIP_PASSWORD};

    # OK, we found a user, let match_password (from our base class) take care of
    # working out if the password is correct
    my $pwd_column_name = $self->db_cols->{users_password_column} || 'password';
	my $expected_pwd = $user->{$pwd_column_name}; # p $expected_pwd;

    # have to do our own SHA as DPAE::Provider::Base uses Crypt::SaltedHash
    # then "straightforward comparison" of strings (no longer required):
    # my $sha_digest = Local::Utils::sha1_digest($password); # p $sha_digest;
    return $self->match_password($password, $expected_pwd);
}

#===============================================================================
sub get_user_details {
    my ($self, $username) = @_; # p $username;
    return unless defined $username; $self->debug('getting user_details');

    # return user details from session if exists:
    #if ( my $user = $self->session_get('user_profile') ) { # p $user;
    #    $self->debug('returning user from session');
    #    return $user;
    #} # warn 'here';

    my $settings = $self->db_cols; # p $settings;

    my $username_col = $settings->{users_username_col} || 'username';
    my $users_table  = $settings->{users_table}        || 'users';

    # get user profile from session:
    my $user_profile = $self->session_get('user_profile'); # p $user_profile;
    # get name of roles key:
    my $user_roles_key = $self->roles_key; # p $roles_key;

    my $dbix = $self->dbix(); # warn $dbix->dbh->{Name};

    # do we have username in hilis4.users tables (if not, will not have user roles):
	my $have_user = $dbix->select( $users_table, 1,
        { $username_col => $username } )->list; # p $user;

    # add roles for use in .tt (eg IF user_profile.$roles_key.foo):
    if ($have_user) {
        my $user_roles = $self->get_user_roles($username); # p $user_roles; # arrayref
        my %roles = map { $_ => 1 } @$user_roles;
        $user_profile->{$user_roles_key} = \%roles;
    }
    else {
        $self->debug("No such hilis4.user.username $username");
        # add empty roles to prevent repeat user roles lookup
        $user_profile->{$user_roles_key} = {};
    }

    # save user profile + roles back to session:
    $self->session_set( user_profile => $user_profile );
    return $user_profile;
}

#===============================================================================
sub get_user_roles { # needs to return arrayref of roles;
    my ($self, $username) = @_; $self->debug('getting user_roles'); # p $username;

    # return roles from session if exists:
    if ( my $session = $self->session_get('user_profile') ) { # p $session;
        my $roles_key = $self->roles_key; # say $roles_key;
        if ( my $roles = $session->{$roles_key} ) { # href
            $self->debug('returning user_roles from session'); # p $roles;
             # warn Dumper [ keys %$roles ]; # caller expects arrayref:
            return [ keys %$roles ];
        }
        else {
            # warn qq!session didn't contain "$roles_key"!; # will continue to query db again
        }
    }

	my @roles = ();
	unless ( $self->disable_roles ) { # warn 'here';
		my ($sql, @bind) = $self->_get_query_params($username);
		@roles = $self->dbix->query($sql, @bind)->flat; # p \@roles;
	}
    return \@roles;
}

#===============================================================================
sub _get_query_params { # returns $sql & @bind
    my ($self, $username) = @_; # p $username;

    my $cols = $self->db_cols; # p $cols; # from config.yml db_cols
    # using views.user_permission in place of roles table:
    my $user_roles_user_id_col = $cols->{user_roles_user_id_col} || 'user_id';
#   my $user_roles_role_id_col = $cols->{user_roles_role_id_col} || 'role_id';
    my $users_username_column  = $cols->{users_username_column}  || 'username';
    my $user_roles_table       = $cols->{user_roles_table}       || 'user_role';
#   my $roles_role_id_col      = $cols->{roles_id_column}        || 'id';
    my $roles_role_col         = $cols->{roles_role_column}      || 'role';
    my $users_id_col           = $cols->{users_id_column}        || 'id';
#   my $roles_table            = $cols->{roles_table}            || 'roles';
    my $users_table            = $cols->{users_table}            || 'users';

    my $join  = "t1.${user_roles_user_id_col}=t2.${users_id_col}"; # p $join;
    my @cols  = ( "t1.${roles_role_col}" );                        # p \@cols;
    my @joins = ( "$user_roles_table|t1" => $join => "$users_table|t2" ); # p \@joins;
    my %where = ( "t2.${users_username_column}" => $username );   # p \%where;

    my @params = (
        -columns => \@cols,
        -from    => [ -join => @joins ],
        -where   => \%where,
    ); # p \@params;
    my ($sql, @bind) = SQL::Abstract::More->new->select(@params); # p [$sql, \@bind];
    return ($sql, @bind);
}

#===============================================================================
sub _build_dbix {
    my $self = shift; $self->debug('building dbix object');
    my $db   = $self->db_name; # warn $db;

=begin # use Local::DB->dbix to share db connection for test scripts & SQLite
    my @dsn  =
        $db eq 'test' # return in memory sqlite dbix object
        ? 'dbi:SQLite:dbname=:memory:'
        : Local::DB->dsn({ dbname => $db }); # p \@dsn;
    return DBIx::Simple->connect(@dsn);
=cut

    my %args = ();
    $db eq 'test' # return in memory sqlite dbix object
        ? ( $args{dsn} = 'dbi:SQLite:dbname=:memory:' )
        : ( $args{dbname} = $db, $args{log_query} = 1 ); # warn Dumper \%args;
    my $dbix = Local::DB->dbix(\%args);
    return $dbix;
}

1;