commit 151b01d1efd1a3ceacd8624d3e898f564c7c11af
from: Izzy Blacklock <izzyb@planetofnix.com>
date: Mon May 15 05:37:10 2023 UTC

Initial	commit of Bot::BasicBot::Plugable::Module::BotnowDB

Initial work to create a module that can access the botnow.db
database.  It currently can give a list of users in the bnc
table.  It only answers takes action of the message starts with
botnow.  Currently working poc commands are:

botnow users
--Returns list of users in the database.
botnow user $username
--Returns nick, email address, date last seen, channel and message
last post seen.

commit - /dev/null
commit + 151b01d1efd1a3ceacd8624d3e898f564c7c11af
blob - /dev/null
blob + 74f2048db939f0e17d33b3c1879963206ae8abb1 (mode 644)
--- /dev/null
+++ lib/Bot/BasicBot/Pluggable/Module/BotnowDB.pm
@@ -0,0 +1,117 @@
+package Bot::BasicBot::Pluggable::Module::BotnowDB;
+
+use warnings;
+use strict;
+use base qw(Bot::BasicBot::Pluggable::Module);
+
+# Database modules for botnow.db
+use DBI;
+use DBD::SQLite;
+
+# setup log level constents
+use constant {
+    NONE => 0,
+    ERRORS => 1,
+    WARNINGS => 2,
+    ALL => 3,
+};
+
+our $VERSION = '0.01';
+
+
+sub connectDB {
+	my $self=shift;
+	my $dbpath = $self->{database} || '/var/www/botnow/botnow.db';
+	my $dsn      = "dbi:SQLite:dbname=$dbpath";
+	my $user     = "";
+	my $password = "";
+	my $dbh = DBI->connect($dsn, $user, $password, {
+		PrintError       => 1,
+		RaiseError       => 1,
+		AutoCommit       => 1,
+		FetchHashKeyName => 'NAME_lc',
+	}) or die "Couldn't connect to database: " . $DBI::errstr;
+	if (!(-s "$dbpath")) {
+		main::debug(ALL, "Cant locate $dbpath");
+		exit 1;
+	}
+	main::debug(ALL, "connected to $dbpath");
+
+	if (!defined($dbh))  {
+		die "failed to connect to $dbpath";
+	}
+	return $dbh;
+}
+
+sub disconnectDB {
+	my $self=shift;
+	my $dbh = shift;
+	$dbh->disconnect();
+}
+
+sub listUsers {
+	my $self=shift;
+	my $dbh=$self->connectDB();
+	my $stmt=qq{SELECT DISTINCT username from bnc};
+	my $reply = join (', ', @{$dbh->selectcol_arrayref($stmt)});
+	$self->disconnectDB($dbh);
+	return $reply;
+}
+
+sub getUser {
+	my $self = shift;
+	my $user = shift;
+
+	my $dbh = $self->connectDB();
+	my $stmt = qq{select username,email from bnc where username is ?};
+	my @userDetails = $dbh->selectrow_array($stmt, undef, $user);
+	my $reply = $userDetails[0] . ": " . $userDetails[1];
+	my $seen = $self->bot->module('seen')->get("seen_$user");
+	if (defined $seen) {
+		$reply .= "\nLast Seen: " . $seen->{time} . " in " . $seen->{channel} . "\n";
+		$reply .= $seen->{what} . ".";
+	}
+	$self->disconnectDB($dbh);
+	return $reply;
+}
+
+
+#        my $stmt=qq{select * from bnc join irc on (bnc.ircid = irc.id) where username is ?};
+#        my $sth=$dbh->prepare($stmt);
+#        $sth->execute($username) or die "execution failed: $dbh->errstr()"; 
+#        while (my $row=$sth->fetchrow_hashref) {
+
+
+
+sub help {
+  return "botnowdb: Database interface for botnow.db";
+}
+
+sub told {
+  my ($self,$message) = @_;
+  # Only act if bot was addressed
+  if($message->{address}) {
+    my $body = $message->{body};
+    my $channel = $message->{channel};
+    if ($channel eq 'msg') { # was a private message so respond in private message
+      $channel = $message->{who};
+    }
+    if($body =~ /^botnow/i) { # Only reply if line begins with botnow
+		my @cmds = split(' ',$body);
+		shift @cmds; #pop botnow off command list
+		my %actions = (
+			users  => sub { return $self->listUsers(@_) },
+			user   => sub { return $self->getUser( @_ ) },
+			
+		);
+		if (!defined($actions{$cmds[0]})) {
+			return $self->help();
+		}
+		my $reply = $actions{$cmds[0]}->(@cmds[1,-1]) ;
+			return $reply;
+	}
+  }
+  return 0;
+}
+
+1;