• Files
• Commits
• Log
• Graph
• Stats

package Local::SSHTunnel;

# adapted from:
# Re: How do you use Net::OpenSSH to query mysql database via ssh tunnel
# https://www.perlmonks.org/?node_id=11106237

use Types::Standard qw(Str); # HashRef ArrayRef  Object
use Data::Printer;
use Net::OpenSSH;
use Local::DB; # path in crontab PERL5LIB
use Moo;

my $LOCAL_PORT = 3307;
my $DB_PORT    = 3306;

has remote_host => ( is => 'ro', required => 1 );
has db_name     => ( is => 'ro', isa => Str ); # optional, for dbix()

has dbix => ( is => 'lazy' );
has ssh  => ( is => 'ro', builder => sub {
    my $ssh = Net::OpenSSH->new( shift->remote_host, master_opts =>
        "-L127.0.0.1:$LOCAL_PORT:localhost:$DB_PORT" );
    die "Couldn't establish SSH connection: ". $ssh->error if $ssh->error;
    return $ssh;
  }
 );

sub _build_dbix {
	my $self = shift;
    # check db_name var has been supplied:
    $self->db_name || die "db_name variable required if calling dbix()";

    my %h = ( dbname => $self->db_name, port => $LOCAL_PORT );
	my $dbix = Local::DB->dbix(\%h); # p $dbix;
	# my $img = $dbix->select('images', 'img_name', { id => 7255 })->value; p $img;
    return $dbix;
    #return { # alternative arrangement to keep $ssh in scope
    #    dbix => $dbix,
    #    ssh  => $ssh, # need to keep this in scope for query to succeed
    #}
}

=begin
my $REMOTE_HOST = 'hmds.org.uk';
my $DB_NAME     = 'exif_data';
my $DB_USERNAME = '***';
my $DB_PASSWORD = '****';

sub new {
    my $class = shift;
    $class = ref $class || $class;

    my $ssh = Net::OpenSSH->new( $REMOTE_HOST, master_opts =>
		"-L127.0.0.1:$LOCAL_PORT:localhost:$DB_PORT" );
    die "Couldn't establish SSH connection: ". $ssh->error if $ssh->error;

    #my $dsn = "DBI:mysql:database=$DB_NAME;host=127.0.0.1;port=$LOCAL_PORT";
    #my $dbh = DBI->connect($dsn, $DB_USERNAME, $DB_PASSWORD);

    my $self = {
        dbh => $dbix,
        ssh => $ssh,
    };
    bless $self, $class;
    return $self;
}

#=begin # for DBI
sub disconnect {
    my ($self) = @_;
    $self->{dbh}->disconnect;
    $self->{ssh}->system('exit');
}

sub DESTROY {
    disconnect();
}
#=begin
sub AUTOLOAD {
    my $self= shift;

    return if $AUTOLOAD =~ /::DESTROY$/;

    $AUTOLOAD =~ s/^.*:://;
    $self->{dbh}->$AUTOLOAD(@_);
}
=cut

1;