package TUNNEL_DB;

use DBI;
use Net::OpenSSH;

my $DB_PASSWORD= $ENV{DB_PASSWORD};
my $DB_USERNAME= $ENV{DB_USERNAME};
my $DB_DATABASE= $ENV{DB_DATABASE};
my $DB_PORT    = $ENV{DB_PORT};
my $REMOTE_HOST= $ENV{REMOTE_HOST};
my $LOCAL_PORT = $ENV{LOCAL_PORT};

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");
    $ssh->error and
    die "Couldn't establish SSH connection: ". $ssh->error;

    my $dsn = "DBI:mysql:database=$DB_DATABASE;host=127.0.0.1;port=$LOCAL_PORT";
    my $dbh = DBI->connect($dsn, $DB_USERNAME, $DB_PASSWORD);
    my $self= {
        dbh => $dbh,
        ssh => $ssh,
    };
    bless $self, $class;
    return $self;
}

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

sub DESTROY {
    disconnect();
}

sub AUTOLOAD {
    my $self= shift;

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

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

1;

##</code><code>##

#!/usr/local/bin/perl
use strict;
use warnings;
use DBI;
use TUNNEL_DB;

my $dbh= TUNNEL_DB->new();

my $sth = $dbh->prepare(
    'SELECT id,domain from domains')
    or die "prepare statement failed: $dbh->errstr()";

$sth->execute() or die "execution failed: $dbh->errstr()";

print $sth->rows . " rows found.\n";

while (my $ref = $sth->fetchrow_hashref()) {
    print "Found a row: id = $ref->{'id'}, fn = $ref->{'domain'}\n";
}

$sth->finish;

$dbh->disconnect;