Use DBIx::Class

For now this is a relatively quick-and-dirty transition, but this
enables us to reuse ASM::DB from the website. Yay

7 files changed, 302 insertions, 275 deletions

diff --git a/lib/ASM/Commander.pm b/lib/ASM/Commander.pm
index c2012d3..39a566f 100644
--- a/lib/ASM/Commander.pm
+++ b/lib/ASM/Commander.pm
@@ -30,9 +30,6 @@ my $cmdtbl = {
 		'cmd' => \&cmd_mship },
 	'^;source$' => {
 		'cmd' => \&cmd_source },
-	'^;sql (?<db>main|log) (?<string>.*)' => {
-		'flag' => 'd',
-		'cmd' => \&cmd_sql },
 	'^;monitor (?<chan>\S+) *$' => {
 		'flag' => 's',
 		'cmd' => \&cmd_monitor },
@@ -345,18 +342,6 @@ sub cmd_source {
 	$conn->privmsg($event->replyto, 'source is at http://asm.rocks/source');
 }
 
-sub cmd_sql {
-	my ($conn, $event) = @_;
-
-	if (!defined $::db) {
-		$conn->privmsg($event->replyto, "I am set to run without a database, fool.");
-		return;
-	}
-	
-	my $dbh = $::db->{DBH};
-	$::db->raw($conn, $event->{to}->[0], $dbh, $+{string});
-}
-
 sub cmd_monitor {
 	my ($conn, $event) = @_;
 
@@ -456,13 +441,29 @@ sub cmd_db {
 	$conn->privmsg($event->replyto, "db is at http://antispammeta.net/query.html");
 }
 
+sub sql_wildcard {
+	my ($str) = @_;
+	# Ugh ...
+	$str =~  s/\*/%/g;
+	$str =~ s/_/\\_/g;
+	$str =~  s/\?/_/g;
+
+	return $str;
+}
+
 sub cmd_query {
 	my ($conn, $event) = @_;
 
 	return unless defined $::db;
 	my $channel = defined($2) ? $1 : '%';
-	my @nuh = split(/(\!|\@)/, defined($2) ? $2 : $1);
-	my $result = $::db->query($channel, $nuh[0], $nuh[2], $nuh[4]);
+	my ($nick, $user, $host) = split(/(\!|\@)/, defined($2) ? $2 : $1);
+
+	my $result = $::db->resultset('Alertlog')->count( {
+			channel => { like => sql_wildcard($channel) },
+			nick => { like => sql_wildcard($nick) },
+			user => { like => sql_wildcard($user) },
+			host => { like => sql_wildcard($host) },
+		});
 	$conn->privmsg($event->replyto, "$result results found.");
 }
 
@@ -479,33 +480,62 @@ sub cmd_investigate {
 	my $person = $::sn{$nick};
 	my $user = lc $person->{user};
 	my $gecos = lc $person->{gecos};
-	my $dbh = $::db->{DBH};
+	my $acc = $person->{account};
 	
-	my $mnicks = $dbh->do("SELECT * from $::db->{ACTIONTABLE} WHERE nick like " . $dbh->quote($nick) . ';');
-	my $musers = ($user ~~ $::mysql->{ignoredidents}) ? "didn't check" : $dbh->do("SELECT * from $::db->{ACTIONTABLE} WHERE user like " . $dbh->quote($person->{user}) . ';');
-	my $mhosts = $dbh->do("SELECT * from $::db->{ACTIONTABLE} WHERE host like " . $dbh->quote($person->{host}) . ';');
-	my $maccts = $dbh->do("SELECT * from $::db->{ACTIONTABLE} WHERE account like " . $dbh->quote($person->{account}) . ';');
-	my $mgecos = ($gecos ~~ $::mysql->{ignoredgecos}) ? "didn't check" : $dbh->do("SELECT * from $::db->{ACTIONTABLE} WHERE gecos like " . $dbh->quote($person->{gecos}) . ';');
+	my $actions = $::db->resultset('Actionlog');
+	my $mnicks = $actions->count({ nick => $nick });
+	my $mhosts = $actions->count({ host => $person->{host} });
+	my $maccts;
+	my $musers;
+	my $mgecos;
+
+	if (defined $acc and $acc ne '0' and $acc ne '*') {
+		$maccts = $actions->count({ account => $acc });
+	}
+
+	if ($user !~ $::mysql->{ignoredidents}) {
+		$musers = $actions->count({ user => $person->{user} });
+	}
+
+	if ($gecos !~ $::mysql->{ignoredgecos}) {
+		$mgecos = $actions->count({ gecos => $person->{gecos} });
+	}
 	
+	my $matchedip;
 	my $ip = ASM::Util->getNickIP($nick);
-	my $matchedip = 0;
-	$matchedip = $dbh->do("SELECT * from $::db->{ACTIONTABLE} WHERE ip = " . $dbh->quote($ip) . ';') if defined($ip);
-	$mnicks =~ s/0E0/0/;
-	$musers =~ s/0E0/0/;
-	$mhosts =~ s/0E0/0/;
-	$maccts =~ s/0E0/0/;
-	$mgecos =~ s/0E0/0/;
-	$matchedip =~ s/0E0/0/;
-	my $dq = '';
+	if (defined $ip) {
+		$matchedip = $actions->count({ ip => $ip });
+	}
+
+	my $dq;
 	if (defined($ip)) {
 		$dq = join '.', unpack 'C4', pack 'N', $ip;
 	}
-	$conn->privmsg($event->replyto, "I found $mnicks matches by nick ($nick), $musers by user ($person->{user}), $mhosts by hostname ($person->{host}), " .
-		       "$maccts by NickServ account ($person->{account}), $mgecos by gecos field ($person->{gecos}), and $matchedip by real IP ($dq). " .
-		       ASM::Shortener->shorturl('https://antispammeta.net/cgi-bin/secret/investigate.pl?nick=' . uri_escape($nick) .
-		       (($user ~~ $::mysql->{ignoredidents}) ? '' : '&user=' . uri_escape($person->{user})) .
-		       '&host=' . uri_escape($person->{host}) . '&account=' . uri_escape($person->{account}) .
-		       (($gecos ~~ $::mysql->{ignoredgecos}) ? '' : '&gecos=' . uri_escape($person->{gecos})) . '&realip=' . $dq));
+
+	my $found_by_fmt = '%s%s by %s (%s)';
+	my @found;
+
+	push @found, sprintf $found_by_fmt, $mnicks,             ' matches', 'nick',             $nick;
+	push @found, sprintf $found_by_fmt, ($musers // "didn't check"), '', 'user',             $person->{user};
+	push @found, sprintf $found_by_fmt, $mhosts,                     '', 'hostname',         $person->{host};
+	push @found, sprintf $found_by_fmt, $maccts,                     '', 'NickServ account', $person->{account} if defined $maccts;
+	push @found, sprintf $found_by_fmt, ($mgecos // "didn't check"), '', 'gecos field',      $person->{gecos};
+	push @found, sprintf $found_by_fmt, $matchedip,                  '', 'real IP',          $dq if defined $ip;
+
+	my $all_found = ASM::Util->commaAndify(@found);
+
+	my @queries;
+	push @queries, 'nick='    . uri_escape($nick);
+	push @queries, 'user='    . uri_escape($person->{user}) if defined $musers;
+	push @queries, 'host='    . uri_escape($person->{host});
+	push @queries, 'account=' . uri_escape($person->{account}) if defined $maccts;
+	push @queries, 'gecos='   . uri_escape($person->{gecos}) if defined $mgecos;
+	push @queries, 'realip='  . uri_escape($dq) if defined $ip;
+
+	my $query_string = join '&', @queries;
+
+	$conn->privmsg( $event->replyto, "I found $all_found. " .
+		ASM::Shortener->shorturl("https://antispammeta.net/cgi-bin/secret/investigate.pl?$query_string") );
 }
 
 sub cmd_investigate2 {
@@ -515,46 +545,53 @@ sub cmd_investigate2 {
 	my $nick = lc $+{nick};
 	my $skip = 0;
 	$skip = $+{skip} if (defined($+{skip}) and ($+{skip} ne ""));
+	$skip = 1 if $skip < 1;
+	$skip = int(2**31-1) if $skip > int(2**31-1);
 	cmd_investigate($conn, $event);
 	unless (defined($::sn{$nick})) {
 		return;
 	}
 	my $person = $::sn{$nick};
-	my $dbh = $::db->{DBH};
 
-	my $query = "SELECT * from $::db->{ACTIONTABLE} WHERE nick like " . $dbh->quote($nick) .
-	             ((lc $person->{user} ~~ $::mysql->{ignoredidents}) ? '' : ' or user like ' . $dbh->quote($person->{user})) .
-	             ' or host like ' . $dbh->quote($person->{host}) .
-		     ' or account like ' . $dbh->quote($person->{account}) .
-	             ((lc $person->{gecos} ~~ $::mysql->{ignoredgecos}) ? '' : ' or gecos like ' . $dbh->quote($person->{gecos}));
+	my $acc = $person->{account};
+	undef $acc if $acc eq '0' or $acc eq '*';
+
 	my $ip = ASM::Util->getNickIP($nick);
-	if (defined($ip)) {
-		$query = $query . ' or ip = ' . $dbh->quote($ip);
-	}
-	$query = $query . " order by time desc limit $skip,10;";
-	ASM::Util->dprint($query, 'mysql');
-	my $query_handle = $dbh->prepare($query);
-	$query_handle->execute();
-	my @data = @{$query_handle->fetchall_arrayref()};
-	if (@data) {
+
+	my $query = [
+		nick => $nick,
+		host => $person->{host},
+		(defined $acc ? (account => $acc) : ()),
+		(defined $ip ? (ip => $ip) : ()),
+		($person->{user} ~~ $::mysql->{ignoredidents} ? () : (user => $person->{user})),
+		($person->{gecos} ~~ $::mysql->{ignoredgecos} ? () : (gecos => $person->{gecos})),
+	];
+
+	my @incidents = $::db->resultset('Actionlog')->search($query, {
+			order_by => { -desc => 'time' },
+			rows => 10,
+			page => $skip,
+		})->all;
+
+	if (@incidents) {
 		$conn->privmsg($event->replyto, 'Sending you the results...');
 	} else {
 		$conn->privmsg($event->replyto, 'No results to send!');
+		return;
 	}
-#		 reverse @data;
-#$data will be an array of arrays,
-	my ($xindex, $xtime, $xaction, $xreason, $xchannel, $xnick, $xuser, $xhost, $xip, $xgecos, $xaccount, $xbynick, $xbyuser, $xbyhost, $xbygecos, $xbyaccount ) = ( 0 .. 15 );
-	foreach my $line (@data) {
-		my $reason = ''; my $channel = '';
-		$reason = ' (' . $line->[$xreason] . ')' if defined($line->[$xreason]);
-		$channel = ' on ' . $line->[$xchannel] if defined($line->[$xchannel]);
-		$conn->privmsg($event->nick, '#' . $line->[$xindex] . ': ' . $line->[$xtime] . ' ' .
-			       $line->[$xnick] . '!' . $line->[$xuser] . '@' . $line->[$xhost] . ' (' . $line->[$xgecos] . ') ' . 
-			       $line->[$xaction] . $reason . $channel . ' by ' . $line->[$xbynick]); # . "\n";
-	}
-	if (@data) {
-		$conn->privmsg($event->nick, "Only 10 results are shown at a time. For more, do ;investigate2 $nick " . ($skip+10) . '.');
+
+	my $format = '#%d: %s %s!%s@%s (%s) %s%s%s%s';
+	for my $line (@incidents) {
+		my $out = sprintf $format, ($line->index, $line->time, $line->nick, $line->user, $line->host, $line->gecos, $line->action,
+			(defined $line->reason ? ' (' . $line->reason . ')' : ''),
+			(defined $line->channel ? ' on ' . $line->channel : ''),
+			(defined $line->bynick ? ' by ' . $line->bynick : ''),
+		);
+
+		$conn->privmsg($event->nick, $out);
 	}
+
+	$conn->privmsg($event->nick, "Only 10 results are shown at a time. For more, do ;investigate2 $nick " . ($skip+1) . '.');
 }
 
 sub cmd_user_add {
diff --git a/lib/ASM/DB.pm b/lib/ASM/DB.pm
index b8c7b63..6bd9519 100644
--- a/lib/ASM/DB.pm
+++ b/lib/ASM/DB.pm
@@ -1,205 +1,11 @@
+use utf8;
 package ASM::DB;
-no autovivification;
 
-use warnings;
 use strict;
-use DBI;
-use Data::Dumper;
-no if $] >= 5.017011, warnings => 'experimental::smartmatch';
-
-sub new {
-  my $module = shift;
-  my ($db, $host, $port, $user, $pass, $table, $actiontable, $dblog) = @_;
-  my $self = {};
-  $self->{DBH} = DBI->connect("DBI:mysql:database=$db;host=$host;port=$port", $user, $pass);
-  $self->{DBH}->{mysql_auto_reconnect} = 1;
-  $self->{TABLE} = $table;
-  $self->{ACTIONTABLE} = $actiontable;
-  bless($self);
-  return $self;
-}
-
-sub raw
-{
-  my $self = shift;
-  my ($conn, $tgt, $dbh, $qry) = @_;
-  my $sth = $dbh->prepare($qry);
-  $sth->execute;
-  my $names = $sth->{'NAME'};
-  my $numFields = $sth->{'NUM_OF_FIELDS'};
-  my $string = "";
-  for (my $i = 0; $i < $numFields; $i++) {
-    $string = $string . sprintf("%s%s", $i ? "," : "", $$names[$i]);
-  }
-  $conn->privmsg($tgt, $string);
-  while (my $ref = $sth->fetchrow_arrayref) {
-    $string = "";
-    for (my $i = 0; $i < $numFields; $i++) {
-      $string = $string . sprintf("%s%s", $i ? "," : "", $$ref[$i]);
-    }
-    $conn->privmsg($tgt, $string);
-  }
-}
-
-sub record
-{
-  my $self = shift;
-  my ($channel, $nick, $user, $host, $gecos, $level, $id, $reason) = @_;
-  $gecos //= "NOT_DEFINED";
-
-  my $dbh = $self->{DBH};
-  $dbh->do("INSERT INTO $self->{TABLE} (channel, nick, user, host, gecos, level, id, reason) VALUES (" .
-             $dbh->quote($channel) . ", " . $dbh->quote($nick) . ", " . $dbh->quote($user) .
-             ", " . $dbh->quote($host) . ", " . $dbh->quote($gecos) . ", " . $dbh->quote($level) . ", " .
-             $dbh->quote($id) . ", " . $dbh->quote($reason) . ");");
-}
-
-sub actionlog
-{
-  my ($self, $event, $modedata1, $modedata2) = @_;
-  my $dbh = $self->{DBH};
-  my ($action, $reason, $channel,
-        $nick,   $user,   $host,   $gecos,   $account, $ip,
-      $bynick, $byuser, $byhost, $bygecos, $byaccount);
-  my ($lcnick, $lcbynick);
-
-  if ($event->{type} eq 'mode') {
-    $action = $modedata1;
-    $nick = $modedata2;
-    $channel = lc $event->{to}->[0];
-    $bynick = $event->{nick};
-    $byuser = $event->{user};
-    $byhost = $event->{host};
-  } elsif ($event->{type} eq 'quit') {
-    my $quitmsg = $event->{args}->[0];
-    if ($quitmsg =~ /^Killed \((\S+) \((.*)\)\)$/) {
-      $bynick = $1;
-      $reason = $2 unless ($2 eq '<No reason given>');
-      return if (($reason // '') =~ /Nickname regained by services/);
-      $action = 'kill';
-    } elsif ($quitmsg =~ /^K-Lined$/) {
-      $action = 'k-line';
-    } else {
-      return; #quit not forced/tracked
-    }
-    $nick = $event->{nick};
-    $user = $event->{user};
-    $host = $event->{host};
-  } elsif (($event->{type} eq 'part') && ($event->{args}->[0] =~ /^requested by (\S+) \((.*)\)/)) {
-    $bynick = $1;
-    $reason = $2 unless (lc $2 eq lc $event->{nick});
-    $action = 'remove';
-    $nick = $event->{nick};
-    $user = $event->{user};
-    $host = $event->{host};
-    $channel = $event->{to}->[0];
-  } elsif ($event->{type} eq 'kick') {
-    $action = 'kick';
-    $bynick = $event->{nick};
-    $byuser = $event->{user};
-    $byhost = $event->{host};
-    $reason = $event->{args}->[1] unless ($event->{args}->[1] eq $event->{to}->[0]);
-    $nick = $event->{to}->[0];
-    $channel = $event->{args}->[0];
-  }
-  return unless defined($action);
-  $lcbynick = lc $bynick if defined $bynick; #we will lowercase the NUHGA info later.
-  if ( (defined($bynick)) && (defined($::sn{$lcbynick})) ) { #we have the nick taking the action available, fill in missing NUHGA info
-    $byuser //= $::sn{$lcbynick}{user};
-    $byhost //= $::sn{$lcbynick}{host};
-    $bygecos //= $::sn{$lcbynick}{gecos};
-    $byaccount //= $::sn{$lcbynick}{account};
-    if (($byaccount eq '0') or ($byaccount eq '*')) {
-      $byaccount = undef;
-    }
-  }
-  $lcnick = lc $nick if defined $nick;
-  if ( (defined($nick)) && (defined($::sn{$lcnick})) ) { #this should always be true, else something has gone FUBAR
-    $user //= $::sn{$lcnick}{user};
-    $host //= $::sn{$lcnick}{host};
-    $gecos //= $::sn{$lcnick}{gecos};
-    $account //= $::sn{$lcnick}{account};
-    if (($account eq '0') or ($account eq '*')) {
-      $account = undef;
-    }
-    $ip = ASM::Util->getNickIP($lcnick);
-  }
-#  my ($action, $reason, $channel,
-#        $nick,   $user,   $host,   $gecos,   $account, $ip
-#      $bynick, $byuser, $byhost, $bygecos, $byaccount);
-#Now, time to escape/NULLify everything
-  $action = $dbh->quote($action);
-  if (defined($reason))  {  $reason = $dbh->quote($reason);     } else {  $reason = 'NULL'; }
-## removed lc's from everything except IP
-  if (defined($channel)) { $channel = $dbh->quote($channel); } else { $channel = 'NULL'; }
-
-  if (defined($nick))    {    $nick = $dbh->quote($nick);    } else {    $nick = 'NULL'; }
-  if (defined($user))    {    $user = $dbh->quote($user);    } else {    $user = 'NULL'; }
-  if (defined($host))    {    $host = $dbh->quote($host);    } else {    $host = 'NULL'; }
-  if (defined($gecos))   {   $gecos = $dbh->quote($gecos);   } else {   $gecos = 'NULL'; }
-  if (defined($account)) { $account = $dbh->quote($account); } else { $account = 'NULL'; }
-  if (defined($ip))      {      $ip = $dbh->quote($ip);         } else {      $ip = 'NULL'; }
-
-  if (defined($bynick))    {    $bynick = $dbh->quote($bynick);    } else {    $bynick = 'NULL'; }
-  if (defined($byuser))    {    $byuser = $dbh->quote($byuser);    } else {    $byuser = 'NULL'; }
-  if (defined($byhost))    {    $byhost = $dbh->quote($byhost);    } else {    $byhost = 'NULL'; }
-  if (defined($bygecos))   {   $bygecos = $dbh->quote($bygecos);   } else {   $bygecos = 'NULL'; }
-  if (defined($byaccount)) { $byaccount = $dbh->quote($byaccount); } else { $byaccount = 'NULL'; }
-  my $sqlstr = "INSERT INTO $self->{ACTIONTABLE} " .
-           "(action, reason, channel, " .
-           "nick, user, host, gecos, account, ip, " .
-           "bynick, byuser, byhost, bygecos, byaccount)" .
-           " VALUES " .
-           "($action, $reason, $channel, " .
-           "$nick, $user, $host, $gecos, $account, $ip, " .
-           "$bynick, $byuser, $byhost, $bygecos, $byaccount);";
-  ASM::Util->dprint( $sqlstr, 'mysql' );
-  $dbh->do( $sqlstr );
-  return $dbh->last_insert_id(undef, undef, $self->{ACTIONTABLE}, undef);
-# $::sn{ow} looks like:
-#$VAR1 = { 
-#          "account" => "afterdeath",
-#          "gecos" => "William Athanasius Heimbigner",
-#          "user" => "icxcnika",
-#          "mship" => [ 
-#                       "#baadf00d",
-#                       "#antispammeta-debug",
-#                       "#antispammeta"
-#                     ],
-#          "host" => "freenode/weird-exception/network-troll/afterdeath"
-#        };
-
-}
-  
-sub query
-{
-  my $self = shift;
-  my ($channel, $nick, $user, $host) = @_;
-  my $dbh = $self->{DBH};
-  $channel = $dbh->quote($channel);
-  $nick    = $dbh->quote($nick);
-  $user    = $dbh->quote($user);
-  $host    = $dbh->quote($host);
+use warnings;
 
-  $nick =~  s/\*/%/g;
-  $nick =~ s/_/\\_/g;
-  $nick =~  s/\?/_/g;
+use parent 'DBIx::Class::Schema';
 
-  $user =~  s/\*/%/g;
-  $user =~ s/_/\\_/g;
-  $user =~  s/\?/_/g;
+__PACKAGE__->load_namespaces;
 
-  $host =~  s/\*/%/g;
-  $host =~ s/_/\\_/g;
-  $host =~  s/\?/_/g;
-  my $sth = $dbh->prepare("SELECT * from $self->{TABLE} WHERE channel like $channel and nick like $nick and user like $user and host like $host;");
-  $sth->execute;
-  my $i = 0;
-  while (my $ref = $sth->fetchrow_arrayref) {
-    $i++;
-  }
-  return $i;
-}
-  
 1;
-# vim: ts=2:sts=2:sw=2:expandtab
diff --git a/lib/ASM/DB/Result/Actionlog.pm b/lib/ASM/DB/Result/Actionlog.pm
new file mode 100644
index 0000000..5385650
--- /dev/null
+++ b/lib/ASM/DB/Result/Actionlog.pm
@@ -0,0 +1,37 @@
+use utf8;
+package ASM::DB::Result::Actionlog;
+
+use strict;
+use warnings;
+
+use parent 'DBIx::Class::Core';
+
+__PACKAGE__->load_components('InflateColumn::DateTime');
+__PACKAGE__->table('actionlog');
+__PACKAGE__->add_columns(
+    index => { data_type => 'bigint', is_auto_increment => 1, is_nullable => 0 },
+    time => {
+        data_type => 'timestamp',
+        datetime_undef_if_invalid => 1,
+        default_value => \'current_timestamp',
+        is_nullable => 0,
+    },
+    action    => { data_type => 'varchar', is_nullable => 0, size => 20 },
+    reason    => { data_type => 'varchar', is_nullable => 1, size => 512 },
+    channel   => { data_type => 'varchar', is_nullable => 1, size => 51 },
+    nick      => { data_type => 'varchar', is_nullable => 0, size => 17 },
+    user      => { data_type => 'varchar', is_nullable => 1, size => 11 },
+    host      => { data_type => 'varchar', is_nullable => 1, size => 64 },
+    ip        => { data_type => 'integer', is_nullable => 1, extra => { unsigned => 1 } },
+    gecos     => { data_type => 'varchar', is_nullable => 1, size => 512 },
+    account   => { data_type => 'varchar', is_nullable => 1, size => 17 },
+    bynick    => { data_type => 'varchar', is_nullable => 1, size => 17 },
+    byuser    => { data_type => 'varchar', is_nullable => 1, size => 11 },
+    byhost    => { data_type => 'varchar', is_nullable => 1, size => 64 },
+    bygecos   => { data_type => 'varchar', is_nullable => 1, size => 512 },
+    byaccount => { data_type => 'varchar', is_nullable => 1, size => 17 },
+);
+
+__PACKAGE__->set_primary_key('index');
+
+1;
diff --git a/lib/ASM/DB/Result/Alertlog.pm b/lib/ASM/DB/Result/Alertlog.pm
new file mode 100644
index 0000000..8ce22ff
--- /dev/null
+++ b/lib/ASM/DB/Result/Alertlog.pm
@@ -0,0 +1,30 @@
+use utf8;
+package ASM::DB::Result::Alertlog;
+
+use strict;
+use warnings;
+
+use parent 'DBIx::Class::Core';
+
+__PACKAGE__->load_components('InflateColumn::DateTime');
+__PACKAGE__->table('alertlog');
+__PACKAGE__->add_columns(
+    time => {
+        data_type => 'timestamp',
+        datetime_undef_if_invalid => 1,
+        default_value => \'current_timestamp',
+        is_nullable => 0,
+    },
+    channel => { data_type => 'text',     is_nullable => 0 },
+    nick    => { data_type => 'text',     is_nullable => 0 },
+    user    => { data_type => 'text',     is_nullable => 0 },
+    host    => { data_type => 'text',     is_nullable => 0 },
+    gecos   => { data_type => 'text',     is_nullable => 0 },
+    level   => { data_type => 'tinytext', is_nullable => 0 },
+    id      => { data_type => 'tinytext', is_nullable => 0 },
+    reason  => { data_type => 'text',     is_nullable => 0 },
+);
+
+__PACKAGE__->set_primary_key('id');
+
+1;
diff --git a/lib/ASM/Event.pm b/lib/ASM/Event.pm
index fac5c06..5e0435c 100644
--- a/lib/ASM/Event.pm
+++ b/lib/ASM/Event.pm
@@ -222,8 +222,8 @@ sub on_part
   my $nick = lc $event->{nick};
   my $chan = lc $event->{to}->[0];
   # Ignore channels that are +s and not monitored
-  if (defined $::db and $event->{args}->[0] =~ /^requested by/ and (not ((grep { /^s$/ } @{$::sc{$chan}{modes}} ) && ($::channels->{channel}->{$chan}->{monitor} eq "no"))) ) {
-    my $idx = $::db->actionlog( $event);
+  if ($event->{args}->[0] =~ /^requested by/ and (not ((grep { /^s$/ } @{$::sc{$chan}{modes}} ) && ($::channels->{channel}->{$chan}->{monitor} eq "no"))) ) {
+    my $idx = $::log->actionlog( $event);
     $::log->sqlIncident($chan, $idx) if $idx;
   }
   if (defined($::sn{$nick}) && defined($::sn{$nick}->{mship})) {
@@ -299,7 +299,7 @@ sub on_quit
   }
   $event->{to} = \@channels;
   if (defined $::db) {
-      my $idx = $::db->actionlog($event);
+      my $idx = $::log->actionlog($event);
       # Ignore channels that are +s and not monitored
       my @actionlog_channels = grep { not ((grep { /^s$/ } @{$::sc{$_}{modes}}) && ($::channels->{channel}->{$_}->{monitor} eq "no")) } @channels;
       $::log->sqlIncident( join(',', @actionlog_channels), $idx ) if $idx;
@@ -425,7 +425,7 @@ sub on_kick {
   if (defined $::db) {
       # Ignore channels that are +s and not monitored
       if ( not ((grep { /^s$/ } @{$::sc{$chan}{modes}}) && ($::channels->{channel}->{$chan}->{monitor} eq "no")) ) {
-          my $idx = $::db->actionlog($event);
+          my $idx = $::log->actionlog($event);
           $::log->sqlIncident($chan, $idx) if $idx;
       }
   }
@@ -567,7 +567,7 @@ sub on_mode
             foreach my $victim (@affected) {
               # Ignore channels that are +s and not monitored
               if ( not ((grep { /^s$/ } @{$::sc{$chan}{modes}}) && ($::channels->{channel}->{$chan}->{monitor} eq "no")) ) {
-                my $idx = $::db->actionlog($event, 'ban', $victim);
+                my $idx = $::log->actionlog($event, 'ban', $victim);
                 $::log->sqlIncident( $chan, $idx ) if $idx;
               }
             }
@@ -594,7 +594,7 @@ sub on_mode
             foreach my $victim (@affected) {
               # Ignore channels that are +s and not monitored
               if ( not ((grep { /^s$/ } @{$::sc{$chan}{modes}}) && ($::channels->{channel}->{$chan}->{monitor} eq "no")) ) {
-                my $idx = $::db->actionlog($event, 'quiet', $victim);
+                my $idx = $::log->actionlog($event, 'quiet', $victim);
                 $::log->sqlIncident( $chan, $idx ) if $idx;
               }
             }
diff --git a/lib/ASM/Inspect.pm b/lib/ASM/Inspect.pm
index d85d10d..d6121fb 100644
--- a/lib/ASM/Inspect.pm
+++ b/lib/ASM/Inspect.pm
@@ -125,14 +125,11 @@ sub inspect {
       return unless (ASM::Util->notRestricted($nick, "notrigger") && ASM::Util->notRestricted($nick, "no$id"));
       $xresult = $dct{$id}{xresult};
       my $nicereason = interpolate($dct{$id}{reason});
-      if (defined $::db) {
-          $::db->record($chan, $displaynick, $event->{user}, $event->{host}, $::sn{lc $nick}->{gecos}, $dct{$id}{risk}, $id, $nicereason);
-      }
       $txtz = "\x03" . $::RCOLOR{$::RISKS{$dct{$id}{risk}}} . "\u$dct{$id}{risk}\x03 risk threat [\x02$chan\x02] - ".
               "\x02$displaynick\x02 - ${nicereason}; ping ";
       $txtz = $txtz . ASM::Util->commaAndify(ASM::Util->getAlert($chan, $dct{$id}{risk}, 'hilights')) if (ASM::Util->getAlert($chan, $dct{$id}{risk}, 'hilights'));
       $txtz = $txtz . ' !att-' . $chan . '-' . $dct{$id}{risk};
-      my $uuid = $::log->incident($chan, "$chan: $dct{$id}{risk} risk: $displaynick - $nicereason\n");
+      my $uuid = $::log->incident($chan, $displaynick, $event->{user}, $event->{host}, $::sn{lc $nick}->{gecos}, $dct{$id}{risk}, $id, $nicereason);
       $txtz = $txtz . ' ' . ASM::Shortener->shorturl($::settings->{web}->{detectdir} . $uuid . '.txt');
       if ($id eq 'last_measure_regex') { #TODO: Note that this is another example of things that shouldn't be hardcoded, but are.
 
diff --git a/lib/ASM/Log.pm b/lib/ASM/Log.pm
index 46bda4c..e9a6d6f 100644
--- a/lib/ASM/Log.pm
+++ b/lib/ASM/Log.pm
@@ -34,12 +34,118 @@ sub new
   return $self;
 }
 
+sub actionlog
+{
+  my ($self, $event, $modedata1, $modedata2) = @_;
+  my ($action, $reason, $channel,
+        $nick,   $user,   $host,   $gecos,   $account, $ip,
+      $bynick, $byuser, $byhost, $bygecos, $byaccount);
+  my ($lcnick, $lcbynick);
+
+  if ($event->{type} eq 'mode') {
+    $action = $modedata1;
+    $nick = $modedata2;
+    $channel = lc $event->{to}->[0];
+    $bynick = $event->{nick};
+    $byuser = $event->{user};
+    $byhost = $event->{host};
+  } elsif ($event->{type} eq 'quit') {
+    my $quitmsg = $event->{args}->[0];
+    if ($quitmsg =~ /^Killed \((\S+) \((.*)\)\)$/) {
+      $bynick = $1;
+      $reason = $2 unless ($2 eq '<No reason given>');
+      return if (($reason // '') =~ /Nickname regained by services/);
+      $action = 'kill';
+    } elsif ($quitmsg =~ /^K-Lined$/) {
+      $action = 'k-line';
+    } else {
+      return; #quit not forced/tracked
+    }
+    $nick = $event->{nick};
+    $user = $event->{user};
+    $host = $event->{host};
+  } elsif (($event->{type} eq 'part') && ($event->{args}->[0] =~ /^requested by (\S+) \((.*)\)/)) {
+    $bynick = $1;
+    $reason = $2 unless (lc $2 eq lc $event->{nick});
+    $action = 'remove';
+    $nick = $event->{nick};
+    $user = $event->{user};
+    $host = $event->{host};
+    $channel = $event->{to}->[0];
+  } elsif ($event->{type} eq 'kick') {
+    $action = 'kick';
+    $bynick = $event->{nick};
+    $byuser = $event->{user};
+    $byhost = $event->{host};
+    $reason = $event->{args}->[1] unless ($event->{args}->[1] eq $event->{to}->[0]);
+    $nick = $event->{to}->[0];
+    $channel = $event->{args}->[0];
+  }
+  return unless defined($action);
+  $lcbynick = lc $bynick if defined $bynick; #we will lowercase the NUHGA info later.
+  if ( (defined($bynick)) && (defined($::sn{$lcbynick})) ) { #we have the nick taking the action available, fill in missing NUHGA info
+    $byuser //= $::sn{$lcbynick}{user};
+    $byhost //= $::sn{$lcbynick}{host};
+    $bygecos //= $::sn{$lcbynick}{gecos};
+    $byaccount //= $::sn{$lcbynick}{account};
+    if (($byaccount eq '0') or ($byaccount eq '*')) {
+      $byaccount = undef;
+    }
+  }
+  $lcnick = lc $nick if defined $nick;
+  if ( (defined($nick)) && (defined($::sn{$lcnick})) ) { #this should always be true, else something has gone FUBAR
+    $user //= $::sn{$lcnick}{user};
+    $host //= $::sn{$lcnick}{host};
+    $gecos //= $::sn{$lcnick}{gecos};
+    $account //= $::sn{$lcnick}{account};
+    if (($account eq '0') or ($account eq '*')) {
+      $account = undef;
+    }
+    $ip = ASM::Util->getNickIP($lcnick);
+  }
+
+  return $::db->resultset('Actionlog')->create({
+      action => $action,
+      reason => $reason,
+      channel => $channel,
+
+      nick    => $nick,
+      user    => $user,
+      host    => $host,
+      gecos   => $gecos,
+      account => $account,
+      ip      => $ip,
+
+      bynick    => $bynick,
+      byuser    => $byuser,
+      byhost    => $byhost,
+      bygecos   => $bygecos,
+      byaccount => $byaccount,
+    })->id;
+# $::sn{ow} looks like:
+#$VAR1 = { 
+#          "account" => "afterdeath",
+#          "gecos" => "William Athanasius Heimbigner",
+#          "user" => "icxcnika",
+#          "mship" => [ 
+#                       "#baadf00d",
+#                       "#antispammeta-debug",
+#                       "#antispammeta"
+#                     ],
+#          "host" => "freenode/weird-exception/network-troll/afterdeath"
+#        };
+
+}
+
 sub incident
 {
   my $self = shift;
-  my ($chan, $header) = @_;
+  my ($chan, $nick, $user, $host, $gecos, $risk, $id, $reason) = @_;
   $chan = lc $chan;
   my $uuid = $self->{UUID}->create_str();
+
+  my $header = "$chan: $risk risk: $nick - $reason\n";
+
   open(FH, '>', $self->{CONFIG}->{detectdir} . $uuid . '.txt');
   print FH $header;
   if (defined($self->{backlog}->{$chan})) {
@@ -47,6 +153,20 @@ sub incident
   }
   print FH "\n\n";
   close(FH);
+
+  $gecos //= "NOT_DEFINED";
+
+  $::db->resultset('Alertlog')->create({
+      channel => $chan,
+      nick    => $nick,
+      user    => $user,
+      host    => $host,
+      gecos   => $gecos,
+      level   => $risk,
+      id      => $id,
+      reason  => $reason,
+    });
+
   return $uuid;
 }