Perl Modules vs plugins

[c0ncrete]

A Perl module is sort of like a plugin, but is usable from anywhere you use Perl.

I suggest that you check out PPM for ActiveState's Perl distribution or CPAN for others (Strawberry, etc) if you have no idea what is happening here.

Understanding of OOP and namespace basics would be helpful as well.

This would go in <PERL_DIR>/site/lib/EQEmu/Database.pm
Part of this was shamelessly ganked from the existing plugin::LoadMysql().
You will need to have SQL::Statement installed for it to function.
You should already have everything else if you have a current install.

package EQEmu::Database;

use strict;
use warnings::register;
use Carp qw(confess);

use JSON;

use DBI;
use DBD::mysql;
use SQL::Statement;

sub new
{
    my $c = shift;
    my $p = length(@_) == 1 && ref $_[0] ? shift : {@_};
    $c->_initDone($p);
    return bless $p, $c;
}

sub _initDone
{
    my ($s, $p) = @_;
    my $c = ref $s || $s;
    foreach my $k (keys %{$p}) {
        next if $k =~ /^_/;
        warnings::warn("unhandled attribute [$k => $p->{$k}] in $c");
    }
}

# TODO: accept full path to config file as parameter
sub _loadConfig
{
	my ($self, $filepath) = @_;
	local $Carp::CarpLevel = $Carp::CarpLevel + 1;

	my $json = new JSON();

	# attempt to open and read entire file
	my $contents;
	open (my $fh, '<', $filepath)
	  or confess("cannot open config file [$filepath]"); {
		local $/;
		$contents = <$fh>;
	} close($fh);

	# decode the contents of the config file,
	# and only keep what we really need to
	my $config = $json->decode($contents)->{server}{database};

	$self->{_user} = $config->{username};
	$self->{_pass} = $config->{password};
	
	# construct and store DSN
	$self->{_dsn} = "DBI:mysql:$config->{db}:$config->{host}:3306";
}

# returns validated database handle
sub _getHandle
{
	my ($self) = @_;
	local $Carp::CarpLevel = $Carp::CarpLevel + 1;
	
	my $dbh = $self->{_dbh};
	unless ($dbh && (ref $dbh) =~ /^DBI::db$/) {
		warnings::warn("invalid database handle [".(ref $dbh)."]");
		return;
	};
	return $dbh;
}

# parses and validates SQL statement passed as string
# returns 0 or 1
sub _validateStatement
{
	my ($self, $inString) = @_;
	local $Carp::CarpLevel = $Carp::CarpLevel + 1;
	
	# get or create a parser (can be reused)
	my $parser;
	unless ($parser = $self->{_parser}) {
		$self->{_parser} = SQL::Parser->new();
		$parser = $self->{_parser};
	}
	
	# parse SQL statement for validation
	my $statement = SQL::Statement->new($inString, $parser);
	my $command   = $statement->command();
	
	# limit command type to SELECT for now
	unless ($command =~ /SELECT/) {
		warnings::warn("commands of type [".$command."] not allowed");
		return;
	}
	
	return 1
}

# connects database handle
sub Connect
{
	my ($self) = @_;
	local $Carp::CarpLevel = $Carp::CarpLevel + 1;

	warnings::warn("already connected... aborting attempt")
	  if $self->{_connected};

	$self->_loadConfig("F:\\EQEmu\\eqemu_config.json");
	
	my $dbh = DBI->connect(
		$self->{_dsn},
		$self->{_user},
		$self->{_pass}
	);
	unless ($dbh && (ref $dbh) =~ /^DBI::db$/) {
		confess("unable to create connection handle");
		return;
	}
	$self->{_dbh} = $dbh;
	$self->{_connected} = 1;

	return 1;
}

# verifies connection
sub Connected
{
	my ($self) = @_;
	local $Carp::CarpLevel = $Carp::CarpLevel + 1;
	
	return 0 unless $self->{_connected};
	
	my $dbh = $self->_getHandle
	  or confess("no database handle found");

	unless ($dbh->ping) {
		warnings::warn("unresponsive connection [ping]");
		$self->{_connected} = 0;
		$self->{_dbh} = undef;
		return;
	};
	
	return 1;
}

# returns an empty array reference on failure or empty result set
# otherwise, returns a reference to an array of hash references
sub Query {
	
	my ($self, $input) = @_;
	
	# validate SQL statement from input
	unless ($self->_validateStatement($input)) {
		warnings::warn("invalid SQL statement [".$input."]");
		return ();
	};
	
	# verify connection and get a database connection handle
	$self->Connect unless $self->Connected;
	return () unless my $dbh = $self->_getHandle;
	
	# basic DBI/DBD::MySQL stuff
	my $sth = $dbh->prepare($input);
	unless ($sth && (ref $sth) =~ /^DBI::st$/) {
		warnings::warn("invalid transaction handle [".(ref $sth)."]");
		return ();
	};
	$sth->execute;
	unless ($sth->rows) {
		warnings::warn("transaction returned no rows");
		return ();
	}
	
	return $sth->fetchall_arrayref({});
};

1;

This is an example of usage in a script.
I ran it outside the emulator for testing.

Note that I only called subroutines that did not begin with a _.
This is intentional as the idea behind a module (or plugin) is to simplify things for "high level" coding.

use EQEmu::Database;
use Data::Dump 'dump';

my $database = new EQEmu::Database();
   $database->Connect();

# the statement to execute
my $sql = "SELECT * FROM bot_data";

#where we will be storing everything
my $rows;

# execute the above statement if connected
if ($database->Connected()) {
	$rows = $database->Query($sql);
}

# dump all the data we got back
dump $rows;

# only print out specific information
my ($row, $key);
map {
	$row = $_;
	map {
		$key = $_;
		plugin::Debug("$key => $row->{$key}");
	} qw(bot_id name last_name race class owner_id);
} @$rows;

# access a specific field from the second row returned
plugin::Debug($$rows[1]->{last_spawn}); # = 1520152044

# emulates behavior of plugin::Debug() for testing
sub plugin::Debug {
	print "DEBUG: ".shift."\n";
}