#!/usr/bin/perl
#< Apply ratings to objects, and manipulate ratings
# Bugs: -s only works >0 - so to add a rating of -2, specify -s 1, then invoke
# three times with -d option to decrement rating
use warnings;
use strict;
use DBI;
use Getopt::Long;
our $O_CHECK = 0;
our $O_DEC = 0;
our $O_INC = 0;
our $O_SPEC = 0;
our $O_REMOVE = 0;
our $O_HELP = 0;
our $O_VERBOSE = 0;
our $O_OBJECT = "";
our $O_REPORT = "";
our $E_SUCCESS = 0;
our $E_ERROR = 0;
our $MAX_SPEC = 5;
our $MIN_SPEC = -5;
our $DB_NAME = "testdb";
our $DB_USER = "testuser";
our $DB_PASS = "l3tm3in";
our $DB_HOST = "localhost";
my ($dbh,$sth);
sub display_help {
my $thisprog = $0;
$thisprog =~ s#^.*/##;
if ( $O_VERBOSE > 0 ) {
printf( "--[ Verbose: thisprog ]--> %s\n", $thisprog );
}
printf( "Usage: %s [-hv] [-g <opts>] [-c|-d|-i|-r||-s <rating>] -o <object>\n", $thisprog );
printf( " -c Check rating\n" );
printf( " -d Decrement rating\n" );
printf( " -g Generate report\n" );
printf( " -h Print this help message\n" );
printf( " -i Increment rating\n" );
printf( " -o Object to rate\n" );
printf( " -r Remove rating\n" );
printf( " -s Specify rating explicitly ( > 0 only )\n" );
printf( " -v Run in verbose mode\n" );
}
sub process_arguments {
GetOptions ( 'check' => \$O_CHECK,
'decrement' => \$O_DEC,
'help' => \$O_HELP,
'increment' => \$O_INC,
'object=s' => \$O_OBJECT,
'remove' => \$O_REMOVE,
'specify=i' => \$O_SPEC,
'verbose+' => \$O_VERBOSE,
'generate=s' => \$O_REPORT ) or $O_HELP++;
if ( $O_HELP > 0 ) {
display_help();
exit $E_SUCCESS;
}
my $total_arg_check = $O_CHECK + $O_DEC + $O_INC + $O_REMOVE;
$total_arg_check++ if ( $O_SPEC != 0 && $O_REPORT ne "" );
$total_arg_check++ if ( ( $total_arg_check >= 1 && $O_SPEC != 0 ) ||
( $total_arg_check >= 1 && $O_REPORT ne "" ) );
if ( $total_arg_check > 1 ) {
printf( STDERR "Error: Exactly one of -c, -d, -g, -i, -r or -s must be specified\n" );
exit $E_ERROR;
}
if ( $O_OBJECT eq "" && $O_REPORT eq "" ) {
printf( STDERR "Error: Object (-o) must be specified\n" );
exit $E_ERROR;
}
if ( $O_SPEC > $MAX_SPEC || $O_SPEC < $MIN_SPEC ) {
printf( STDERR "Error: Rating must be >= %s && <= %s\n", $MIN_SPEC, $MAX_SPEC );
exit $E_ERROR;
}
}
sub db_connect {
if ( $O_VERBOSE > 0 ) {
printf( "--[ Verbose ]--> Attempting to connect to database\n" );
}
$dbh = DBI->connect("DBI:mysql:$DB_NAME:$DB_HOST", $DB_USER, $DB_PASS ) ||
die( "Cannot connect to database: $DBI::errstr\n" );
if ( $O_VERBOSE > 0 ) {
printf( "--[ Verbose ]--> Database connection successful\n" );
}
}
sub db_disconnect {
if ( $O_VERBOSE > 0 ) {
printf( "--[ Verbose ]--> Attempting to disconnect from database\n" );
}
$dbh->disconnect;
if ( $O_VERBOSE > 0 ) {
printf( "--[ Verbose ]--> Database disconnection successful\n" );
}
}
sub check_rating {
$sth = $dbh->prepare("SELECT * FROM ratings WHERE object = ?");
$sth->execute($O_OBJECT);
my @data = $sth->fetchrow_array();
if ( scalar( @data == 0 ) ) {
printf( "Object %s is not rated\n", $O_OBJECT );
} else {
$sth->execute($O_OBJECT);
while ( @data = $sth->fetchrow_array() ) {
printf( "%-30s%2s\n", $data[0], $data[1] );
}
}
$sth->finish();
}
sub increment_rating {
$sth = $dbh->prepare("SELECT * FROM ratings WHERE object = ?");
$sth->execute($O_OBJECT);
my @data = $sth->fetchrow_array();
if ( scalar( @data == 0 ) ) {
printf( "Object %s is not rated - use -s to specify a rating first\n", $O_OBJECT );
} else {
my $current_rating = $data[1];
if ( $current_rating >= $MAX_SPEC ) {
printf( "Object %s already has maximum rating of %s\n", $O_OBJECT, $MAX_SPEC );
} else {
$sth = $dbh->prepare("UPDATE ratings SET rating = ? WHERE object = ?");
$sth->execute(++$current_rating,$O_OBJECT) || die ( "Could not update table: $DBI::errstr\n" );
}
}
$sth->finish();
}
sub decrement_rating {
$sth = $dbh->prepare("SELECT * FROM ratings WHERE object = ?");
$sth->execute($O_OBJECT);
my @data = $sth->fetchrow_array();
if ( scalar( @data ) == 0 ) {
printf( "Object %s is not rated - use -s to specify a rating first\n", $O_OBJECT );
} else {
my $current_rating = $data[1];
if ( $current_rating <= $MIN_SPEC ) {
printf( "Object %s already has minimum rating of %s\n", $O_OBJECT, $MIN_SPEC );
} else {
$sth = $dbh->prepare("UPDATE ratings SET rating = ? WHERE object = ?");
$sth->execute(--$current_rating,$O_OBJECT) || die ( "Could not update table: $DBI::errstr\n" );
}
}
$sth->finish();
}
sub specify_rating {
$sth = $dbh->prepare("SELECT * FROM ratings WHERE object = ?");
$sth->execute($O_OBJECT);
my @data = $sth->fetchrow_array();
if ( scalar( @data ) == 0 ) {
if ( $O_SPEC < $MIN_SPEC || $O_SPEC > $MAX_SPEC ) {
printf( "Rating %s is out of bounds\n", $O_SPEC );
} else {
$sth = $dbh->prepare("INSERT INTO ratings VALUES (?,?)");
$sth->execute($O_OBJECT, $O_SPEC);
}
} else {
my $current_rating = $data[1];
if ( $O_SPEC <= $MIN_SPEC ) {
printf( "Object %s already has minimum rating of %s\n", $O_OBJECT, $MIN_SPEC );
} elsif ( $O_SPEC >= $MAX_SPEC ) {
printf( "Object %s already has maximum rating of %s\n", $O_OBJECT, $MAX_SPEC );
} else {
$sth = $dbh->prepare("UPDATE ratings SET rating = ? WHERE object = ?");
$sth->execute($O_SPEC,$O_OBJECT) || die ( "Could not update table: $DBI::errstr\n" );
}
}
$sth->finish();
}
sub remove_rating {
$sth = $dbh->prepare("SELECT * FROM ratings WHERE object = ?");
$sth->execute($O_OBJECT);
my @data = $sth->fetchrow_array();
if ( scalar( @data ) == 0 ) {
printf( "Object %s is not rated - nothing to do\n", $O_OBJECT );
} else {
$sth = $dbh->prepare("DELETE FROM ratings WHERE object = ?");
$sth->execute($O_OBJECT) || die ( "Could not delete from table: $DBI::errstr\n" );
}
$sth->finish();
}
sub generate_report {
if ( $O_REPORT eq "by_object" ) {
$sth = $dbh->prepare("SELECT * FROM ratings");
$sth->execute();
my @data = $sth->fetchrow_array();
if ( scalar( @data ) == 0 ) {
printf( "No ratings exist\n", $O_OBJECT );
} else {
$sth = $dbh->prepare("SELECT * FROM ratings ORDER BY object");
$sth->execute();
while ( @data = $sth->fetchrow_array() ) {
printf( "%-30s%2s\n", $data[0], $data[1] );
}
}
$sth->finish();
} elsif ( $O_REPORT eq "by_rating" ) {
$sth = $dbh->prepare("SELECT * FROM ratings");
$sth->execute();
my @data = $sth->fetchrow_array();
if ( scalar( @data ) == 0 ) {
printf( "No ratings exist\n", $O_OBJECT );
} else {
$sth = $dbh->prepare("SELECT * FROM ratings ORDER BY rating DESC");
$sth->execute();
while ( @data = $sth->fetchrow_array() ) {
printf( "%-30s%2s\n", $data[0], $data[1] );
}
}
$sth->finish();
} elsif ( $O_REPORT eq "by_rating" ) {
} else {
printf( STDERR "Unknown report type: %s\n", $O_REPORT );
}
}
sub do_stuff {
if ( $O_CHECK > 0 ) {
&check_rating;
} elsif ( $O_DEC > 0 ) {
&decrement_rating;
} elsif ( $O_INC > 0 ) {
&increment_rating;
} elsif ( $O_REMOVE > 0 ) {
&remove_rating;
} elsif ( $O_SPEC > 0 ) {
&specify_rating;
} elsif ( $O_REPORT ne "" ) {
&generate_report;
}
}
#
# main()
#
&process_arguments();
&db_connect();
&do_stuff();
&db_disconnect();
exit 0;
__END__
kevin@barfly ~/ratetool $ mysql -u testuser -p testdb -h localhost <<EoF
> explain ratings;
> EoF
Enter password:
Field Type Null Key Default Extra
object varchar(255) NO PRI
rating int(2) YES NULL