gistfile1.txt

#!/usr/bin/perl
use warnings;
use strict;
use DBI;
use POSIX qw(strftime);
use Text::ASCIITable;

my $db_file = $ENV{'HOME'} . "/.log.db";
my $dbh = DBI->connect( "dbi:SQLite:" . $db_file );
my $table = Text::ASCIITable->new();
$table->setCols( 'Time', 'IP', 'Message' );

$dbh->do( "CREATE TABLE IF NOT EXISTS `entry` (
		id INTEGER PRIMARY KEY,
		timestamp int,
		remote VARCHAR( 2048 ),
		entry TEXT )" );

if( $ARGV[0] && $ARGV[0] eq '--report' ) {
	my $sql = 'SELECT * FROM `entry`';
	if( $ARGV[1] && $ARGV[1] eq 'day' ) {
		$sql .= ' WHERE `timestamp` > ' . $dbh->quote( time()-86400 );
	} elsif( $ARGV[1] && $ARGV[1] eq 'week' ) {
		$sql .= ' WHERE `timestamp` > ' . $dbh->quote( time()-604800 );
	} elsif( $ARGV[1] && $ARGV[1] eq 'month' ) {
		$sql .= ' WHERE `timestamp` > ' . $dbh->quote( time()-2629743 );
	} elsif( $ARGV[1] && $ARGV[1] eq 'year' ) {
		$sql .= ' WHERE `timestamp` > ' . $dbh->quote( time()-31556926 );
	} elsif( $ARGV[1] && $ARGV[1] eq 'all' ) {
	} elsif( $ARGV[1] && $ARGV[1] eq 'search' ) {
		if( $ARGV[2] ) {
			$sql .= ' WHERE `entry` LIKE ' . $dbh->quote( '%' . $ARGV[2] . '%' );
		} else {
			print "Unknown search string\n";
			exit 1;
		}
	} else {
		print "Unknown report requested\n";
		exit 1;
	}

	my $sth = $dbh->prepare( $sql );
	$sth->execute();
	while( my $row = $sth->fetchrow_hashref() ) {
		my $ts = strftime( '%H:%M:%S %m/%d/%Y', localtime( $row->{'timestamp'} ) );
		$table->addRow( $ts, $row->{'remote'}, $row->{'entry'} );
	}
	print $table . "\n";

} elsif( $ARGV[0] ) {
	my $remote = 'Unknown';
	my $message = '';
	for( @ARGV ) {
		$message .= $_ . " ";		
	}
	if( $ENV{'SSH_CLIENT'} && $ENV{'SSH_CLIENT'} =~ /^(\d+\.\d+\.\d+\.\d+)\s+\d+\s+\d+$/ ) {
		$remote = $1;
	}
	$dbh->do( "INSERT INTO `entry` (`id`, `timestamp`, `remote`, `entry`) VALUES(NULL, " . $dbh->quote( time() ) . ", " . $dbh->quote( $remote ) . ", " . $dbh->quote( $message ) . ")");
	print "Logged '" . $message . "' from '" . $remote . "'\n";
} else {
	print "Usage: log <--report [day|month|year|all|search]> <message>\n";
	exit 0;
}