From 46829f2193bb4ebee5a85f123588d9b8334d8ddf Mon Sep 17 00:00:00 2001 From: djmcgrath Date: Sun, 21 Oct 2007 04:24:56 +0000 Subject: [PATCH 1/1] * Optimized and cleaned up SQL related to hehstats * Removed useless textstats_main sub * Fixed hehstats not being inserted into db bug git-svn-id: https://svn.code.sf.net/p/infobot/code/trunk@1590 c11ca15a-4712-0410-83d8-924469b57eb5 --- src/CommandStubs.pl | 162 ++++++++------------------------------------ src/DynaConfig.pl | 3 + src/IRC/IrcHooks.pl | 2 +- 3 files changed, 31 insertions(+), 136 deletions(-) diff --git a/src/CommandStubs.pl b/src/CommandStubs.pl index ed89824..2e0bd65 100644 --- a/src/CommandStubs.pl +++ b/src/CommandStubs.pl @@ -644,11 +644,6 @@ sub do_text_counters { $chan = $1; } - if ($message =~ /^_stats(\s+(\S+))$/i) { - &textstats_main($2); - return 1; - } - my ($type,$arg); if ($message =~ /^($z)stats(\s+(\S+))?$/i) { $type = $1; @@ -657,27 +652,21 @@ sub do_text_counters { return 0; } - # even more uglier with channel/time arguments. - my $c = $chan; -# my $c = $chan || 'PRIVATE'; - my $where = 'type='.&sqlQuote($type); - if (defined $c) { - &DEBUG("c => $c"); - $where .= ' AND channel='.&sqlQuote($c) if (defined $c); - } else { - &DEBUG('not using chan arg'); - } + my $c = $chan || 'PRIVATE'; - my $sum = (&sqlRawReturn('SELECT SUM(counter) FROM stats' - .' WHERE '.$where ))[0]; + # Define various types of stats in one place. + # Note: sqlSelectColHash has built in sqlQuote + my $where_chan_type = { channel => $c, type => $type }; + my $where_chan_type_nick = { channel => $c, type => $type, nick => $arg}; + + my $sum = (&sqlSelect('stats', 'SUM(counter)', $where_chan_type))[0]; if (!defined $arg or $arg =~ /^\s*$/) { - # this is way ugly. - # TODO: convert $where to hash + # get top 3 stats of $type in $chan my %hash = &sqlSelectColHash('stats', 'nick,counter', - { }, - $where.' ORDER BY counter DESC LIMIT 3', 1 + $where_chan_type, + 'ORDER BY counter DESC LIMIT 3', 1 ); my $i; my @top; @@ -702,34 +691,32 @@ sub do_text_counters { &performStrictReply("zero counter for \037$type\037."); } } else { - # TODO: convert $where to hash and use a sqlSelect - my $x = (&sqlRawReturn('SELECT SUM(counter) FROM stats'. - " WHERE $where AND nick=".&sqlQuote($arg) ))[0]; + my $x = (&sqlSelect('stats', 'SUM(counter)', $where_chan_type_nick))[0]; - if (!defined $x) { # !defined. + if (!defined $x) { # If no stats were found &performStrictReply("$arg has not said $type yet."); return 1; } - # defined. - # TODO: convert $where to hash - my @array = &sqlSelect('stats', 'nick', undef, - $where.' ORDER BY counter', 1 + # Get list of all nicks for channel $c and $type + my @array = &sqlSelectColArray('stats', 'nick', + $where_chan_type, + 'ORDER BY counter DESC' ); - my $good = 0; - my $i = 0; - for ($i=0; $i $c } : ''; - my $sum = &sqlSelect('stats', 'SUM(counter)', $where_href); - - if (!defined $arg or $arg =~ /^\s*$/) { - # this is way ugly. - &DEBUG('_stats: !arg'); - - my %hash = &sqlSelectColHash('stats', 'nick,counter', - $where_href, - ' ORDER BY counter DESC LIMIT 3', 1 - ); - my $i; - my @top; - - # unfortunately we have to sort it again! - my $tp = 0; - foreach $i (sort { $b <=> $a } keys %hash) { - foreach (keys %{ $hash{$i} }) { - my $p = sprintf("%.01f", 100*$i/$sum); - $tp += $p; - push(@top, "\002$_\002 -- $i ($p%)"); - } - } - - my $topstr = ''; - if (scalar @top) { - $topstr = '. Top '.scalar(@top).': '.join(', ', @top); - } - - if (defined $sum) { - &performStrictReply("total count of \037$type\037 on \002$c\002: $sum$topstr"); - } else { - &performStrictReply("zero counter for \037$type\037."); - } - - return; - } - - # TODO: add nick to where_href - my %hash = &sqlSelectColHash('stats', 'type,counter', - $where_href, ' AND nick='.&sqlQuote($arg) - ); - - # this is totally messed up... needs to be fixed... and cleaned up. - my $total; - my $good; - my $ii; - my $x; - - foreach (keys %hash) { - &DEBUG("_stats: hash{$_} => $hash{$_}"); - # ranking. - # TODO: convert $where to hash - my $where = ''; - my @array = &sqlSelect('stats', 'nick', undef, $where.' ORDER BY counter', 1); - $good = 0; - $ii = 0; - for(my $i=0; $i $i, good => $good, total => $total"); - $x .= ' '.$total.'blah blah'; - } - -# return; - - if (!defined $x) { # !defined. - &performStrictReply("$arg has not said $type yet."); - return; - } - - my $xtra = ''; - if ($total and $good) { - my $pct = sprintf("%.01f", 100*(1+$total-$ii)/$total); - $xtra = ", ranked $ii\002/\002$total (percentile: \002$pct\002 %)"; - } - - my $pct1 = sprintf("%.01f", 100*$x/$sum); - &performStrictReply("\002$arg\002 has said \037$type\037 \002$x\002 times (\002$pct1\002 %)$xtra"); -} - sub nullski { my ($arg) = @_; return unless (defined $arg); diff --git a/src/DynaConfig.pl b/src/DynaConfig.pl index 74e7e4c..69c7f1b 100644 --- a/src/DynaConfig.pl +++ b/src/DynaConfig.pl @@ -744,9 +744,11 @@ sub chanSet { if ($val eq '0') { &performStrictReply("Unsetting $what for $chan$was."); delete $chanconf{$chan}{$what}; + delete $cache{ircTextCounters} if $what eq 'ircTextCounters'; } else { &performStrictReply("Setting $what for $chan to '$val'$was."); $chanconf{$chan}{$what} = $val; + delete $cache{ircTextCounters} if $what eq 'ircTextCounters'; } $update++; @@ -763,6 +765,7 @@ sub chanSet { &performStrictReply("Setting $what for $chan to '$val'$was."); $chanconf{$chan}{$what} = $val; + delete $cache{ircTextCounters} if $what eq 'ircTextCounters'; $update++; diff --git a/src/IRC/IrcHooks.pl b/src/IRC/IrcHooks.pl index 47f9851..319d0c0 100644 --- a/src/IRC/IrcHooks.pl +++ b/src/IRC/IrcHooks.pl @@ -925,7 +925,7 @@ sub on_public { $v++; # don't allow ppl to cheat the stats :-) - if (defined $t && $time - $t > 60) { + if ((defined $t && $time - $t > 60) or (!defined $t)) { &sqlSet('stats', {'nick' => $who}, { type => $x, channel => $c, -- 2.39.2