From c7f705958b79e5a22564aa9525817a029adaeeca Mon Sep 17 00:00:00 2001 From: dms Date: Mon, 1 Oct 2001 13:01:19 +0000 Subject: [PATCH] - second round of changes from lear@OPN. thanks! git-svn-id: https://svn.code.sf.net/p/infobot/code/trunk/blootbot@514 c11ca15a-4712-0410-83d8-924469b57eb5 --- src/Factoids/Misc.pl | 2 +- src/Factoids/Reply.pl | 27 +++++++++++------ src/Factoids/Update.pl | 2 +- src/IRC/IrcHooks.pl | 5 ++-- src/IRC/Schedulers.pl | 19 ++++++++---- src/Modules/Countdown.pl | 10 ++++++- src/Modules/Factoids.pl | 10 ++----- src/Modules/Freshmeat.pl | 17 ++++++++++- src/Process.pl | 2 +- src/db_mysql.pl | 9 ++---- src/db_pgsql.pl | 62 ++++++++++++++++------------------------ 11 files changed, 94 insertions(+), 71 deletions(-) diff --git a/src/Factoids/Misc.pl b/src/Factoids/Misc.pl index aa058e8..ec0addb 100644 --- a/src/Factoids/Misc.pl +++ b/src/Factoids/Misc.pl @@ -309,7 +309,7 @@ sub FactoidStuff { if (defined $result) { my $author = &getFactInfo($from, "created_by"); - if (&IsFlag("m") or $author =~ /^\Q$who\E\!/i) { + if (0 and !&IsFlag("m") or $author !~ /^\Q$who\E\!/i) { &msg($who, "It's not yours to modify."); return; } diff --git a/src/Factoids/Reply.pl b/src/Factoids/Reply.pl index c920b32..e73a73e 100644 --- a/src/Factoids/Reply.pl +++ b/src/Factoids/Reply.pl @@ -67,7 +67,7 @@ sub getReply { &setFactInfo($lhs,"requested_time", time()); &setFactInfo($lhs,"requested_count", $count); } else { - &dbReplace("factoids", ( + &dbReplace("factoids", "factoid_key", ( factoid_key => $lhs, requested_by => $nuh, requested_time => time(), @@ -252,6 +252,7 @@ sub substVars { $date =~ s/\:\d+(\s+\w+)\s+\d+$/$1/; $reply =~ s/\$date/$date/gi; $date =~ s/\w+\s+\w+\s+\d+\s+//; + # todo: support UTC. $reply =~ s/\$time/$date/gi; # dollar variables. @@ -274,20 +275,30 @@ sub substVars { if ($reply =~ /\$rand/) { my $rand = rand(); - my $randp = int($rand*100); - $reply =~ s/\$randpercentage/$randp/g; # ??? - # randnick. + + # $randnick. if ($reply =~ /\$randnick/) { my @nicks = keys %{ $channels{$chan}{''} }; my $randnick = $nicks[ int($rand*$#nicks) ]; - s/\$randnick/$randnick/; + $reply =~ s/\$randnick/$randnick/g; } + # eg: $rand100.3 ### TODO: number of digits. 'x.y' - if ($reply =~ /\$rand(\d+)/) { - # will this work as it does in C? - $rand = sprintf("%*f", $1, $rand); + # too hard. + if ($reply =~ /\$rand(\d+)(\.(\d+))?/) { + my $max = $1; + my $dot = $3 || 0; + &status("dot => $dot, max => $max, rand=>$rand"); + $rand = sprintf("%.*f", $dot, $rand*$max); + my $orig = $&; + + &status("swapping $orig to $rand"); + &status("reply => $reply"); + $reply =~ s/$orig/$rand/eg; + &status("reply => $reply"); } + $reply =~ s/\$rand/$rand/g; } diff --git a/src/Factoids/Update.pl b/src/Factoids/Update.pl index ee6c6a8..5df526a 100644 --- a/src/Factoids/Update.pl +++ b/src/Factoids/Update.pl @@ -114,7 +114,7 @@ sub update { } else { ### BROKEN!!! # I'd prefer to use dbReplace but it don't work. - &dbReplace("factoids", ( + &dbReplace("factoids", "factoid_key", ( factoid_key => $lhs, created_by => time(), factoid_value => $rhs, diff --git a/src/IRC/IrcHooks.pl b/src/IRC/IrcHooks.pl index b4b31f6..1a77e32 100644 --- a/src/IRC/IrcHooks.pl +++ b/src/IRC/IrcHooks.pl @@ -353,7 +353,8 @@ sub on_disconnect { $ircstats{'DisconnectTime'} = time(); $ircstats{'DisconnectReason'} = $what; $ircstats{'DisconnectCount'}++; - $ircstats{'TotalTime'} += time() - $ircstats{'ConnectTime'}; + $ircstats{'TotalTime'} += time() - $ircstats{'ConnectTime'} + if ($ircstats{'ConnectTime'}); # clear any variables on reconnection. $nickserv = 0; @@ -858,7 +859,7 @@ sub on_public { ); - &dbReplace("stats", %hash); + &dbReplace("stats", "nick", %hash); # does not work, atleast with old mysql!!! :( # &dbReplace("stats", (nick => $who, type => $x, -counter => "counter+1") ); } diff --git a/src/IRC/Schedulers.pl b/src/IRC/Schedulers.pl index b5bca95..1a2d48c 100644 --- a/src/IRC/Schedulers.pl +++ b/src/IRC/Schedulers.pl @@ -246,8 +246,17 @@ sub seenFlushOld { my $max_time = &getChanConfDefault("seenMaxDays", 30) *60*60*24; my $delete = 0; - if ($param{'DBType'} =~ /^pg|postgres|mysql/i) { - my $query = "SELECT nick,time FROM seen GROUP BY nick HAVING UNIX_TIMESTAMP() - time > $max_time"; + if ($param{'DBType'} =~ /^pgsql|mysql/i) { + my $query; + + if ($param{'DBType'} =~ /^mysql$/i) { + $query = "SELECT nick,time FROM seen GROUP BY nick HAVING ". + "UNIX_TIMESTAMP() - time > $max_time"; + } else { # pgsql. + $query = "SELECT nick,time FROM seen WHERE ". + "extract(epoch from timestamp 'now') - time > $max_time"; + } + my $sth = $dbh->prepare($query); $sth->execute; @@ -364,7 +373,6 @@ sub chanlimitCheck { } my $str = join(' ', &ChanConfList("chanlimitcheck") ); - &DEBUG("chanlimitCheck: str => $str"); foreach $chan ( &ChanConfList("chanlimitcheck") ) { next unless (&validChan($chan)); @@ -383,6 +391,7 @@ sub chanlimitCheck { &status("chanlimit: netsplit; removing it for $chan."); &rawout("MODE $chan -l"); $cache{chanlimitChange}{$chan} = time(); + &status("chanlimit: netsplit; removed."); } next; @@ -541,9 +550,9 @@ sub seenFlush { $stats{'new'} = 0; $stats{'old'} = 0; - if ($param{'DBType'} =~ /^mysql|pg|postgres/i) { + if ($param{'DBType'} =~ /^(mysql|pgsql)$/i) { foreach $nick (keys %seencache) { - my $retval = &dbReplace("seen", ( + my $retval = &dbReplace("seen", "nick", ( "nick" => $seencache{$nick}{'nick'}, "time" => $seencache{$nick}{'time'}, "host" => $seencache{$nick}{'host'}, diff --git a/src/Modules/Countdown.pl b/src/Modules/Countdown.pl index 0fdbdd3..abd1099 100644 --- a/src/Modules/Countdown.pl +++ b/src/Modules/Countdown.pl @@ -44,16 +44,24 @@ sub Countdown { ### SQL SPECIFIC. my ($to_days,$dayname,$monname); - if ($param{'DBType'} =~ /pg|postgres|mysql/i) { + if ($param{'DBType'} =~ /^mysql$/i) { $to_days = (&dbRawReturn("SELECT TO_DAYS(NOW()) - TO_DAYS('$sqldate')"))[0]; $dayname = (&dbRawReturn("SELECT DAYNAME('$sqldate')"))[0]; $monname = (&dbRawReturn("SELECT MONTHNAME('$sqldate')"))[0]; + + } elsif ($param{'DBType'} =~ /^pgsql$/i) { + $to_days = (&dbRawReturn("SELECT date_trunc('day', + 'now'::timestamp - '$sqldate')"))[0]; + $dayname = qw(Sun Mon Tue Wed Thu Fri Sat)[(&dbRawReturn("SELECT extract(dow from timestamp '$sqldate')"))[0]]; + $monname = qw(BAD Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec)[(&dbRawReturn("SELECT extract(month from timestamp '$sqldate')"))[0]]; + } elsif ($param{'DBType'} =~ /^dbm$/i) { &DEBUG("Countdown: FIXME!!!"); # $to_days = # $dayname = # $monname = return 1; + } else { &ERROR("Countdown: invalid DBType?"); return 1; diff --git a/src/Modules/Factoids.pl b/src/Modules/Factoids.pl index 5c2fc06..caadae6 100644 --- a/src/Modules/Factoids.pl +++ b/src/Modules/Factoids.pl @@ -19,16 +19,10 @@ sub CmdFactInfo { return; } - my $i = 0; - my %factinfo; - my @factinfo = &getFactInfo($faqtoid,"*"); - foreach ( &dbGetColInfo("factoids") ) { - $factinfo{$_} = $factinfo[$i] || ''; - $i++; - } + my %factinfo = &dbGetColNiceHash("factoids", "*", "factoid_key=".&dbQuote($faqtoid)); # factoid does not exist. - if (scalar @factinfo <= 1) { + if (scalar (keys %factinfo) <= 1) { &performReply("there's no such factoid as \002$faqtoid\002"); return; } diff --git a/src/Modules/Freshmeat.pl b/src/Modules/Freshmeat.pl index 269eb3e..c33685f 100644 --- a/src/Modules/Freshmeat.pl +++ b/src/Modules/Freshmeat.pl @@ -186,7 +186,7 @@ sub downloadIndex { # set the last refresh time. fixes multiple spawn bug. &::dbSet("freshmeat", { "projectname_short" => "_" }, - { "latest_version" => time() + { "latest_version" => time(), "desc_short" => "" } ); @@ -326,6 +326,9 @@ sub xml_end { $pkg{$text} = $string; if ($expat->depth == 0) { + + # old code. + if (0) { for (my $j=0; $jfetchrow_array) { - # reverse it to make it easier to count. - } + %retval = %{ $sth->fetchrow_hashref() }; $sth->finish; @@ -273,10 +270,10 @@ sub dbInsert { } ##### -# Usage: &dbReplace($table, %hash); +# Usage: &dbReplace($table, $key %hash); # Note: dbReplace does optional dbQuote. sub dbReplace { - my ($table, %hash) = @_; + my ($table, $key, %hash) = @_; my (@keys, @vals); foreach (keys %hash) { diff --git a/src/db_pgsql.pl b/src/db_pgsql.pl index 3e32930..e32d0b7 100644 --- a/src/db_pgsql.pl +++ b/src/db_pgsql.pl @@ -13,7 +13,8 @@ sub openDB { $dbh = DBI->connect($connectstr, $param{'SQLUser'}, $param{'SQLPass'}); if (!$dbh->err) { - &status("Opened PgSQL connection to $param{'SQLHost'}"); + &status("Opened pgSQL connection". + (exists $param{'SQLHost'} ? " to ".$param{'SQLHost'} : "")); } else { &ERROR("cannot connect to $param{'SQLHost'}."); &ERROR("pgSQL: ".$dbh->errstr); @@ -29,7 +30,7 @@ sub openDB { sub closeDB { return 0 unless ($dbh); - &status("Closed pgSQL connection to $param{'SQLHost'}."); + &status("Closed pgSQL connection."); $dbh->disconnect(); return 1; @@ -147,11 +148,7 @@ sub dbGetColNiceHash { return; } - # todo: get column names, do $hash{$primkey}{blah} = ... - while (my @row = $sth->fetchrow_array) { - # todo: reverse it to make it easier to count. - } - + %retval = %{ $sth->fetchrow_hashref() }; $sth->finish; return %retval; @@ -162,8 +159,8 @@ sub dbGetColNiceHash { sub dbGetColInfo { my ($table) = @_; -# my $query = "SELECT * FROM $table LIMIT 1;"; - my $query = "SHOW COLUMNS from $table"; + my $query = "SELECT * FROM $table LIMIT 1;"; +# my $query = "SHOW COLUMNS from $table"; my %retval; my $sth = $dbh->prepare($query); @@ -175,18 +172,10 @@ sub dbGetColInfo { return; } - if (0) { - %retval=%{$sth->fetchrow_hashref()}; - return keys %retval; - } - - my @cols; - while (my @row = $sth->fetchrow_array) { - push(@cols, $row[0]); - } + %retval = %{ $sth->fetchrow_hashref() }; $sth->finish; - return @cols; + return keys %retval; } ##### @@ -257,21 +246,15 @@ sub dbUpdate { ##### # Usage: &dbInsert($table, $primkey, $primval, %hash); sub dbInsert { - my ($table, $primkey, $primval, %hash, $delay) = @_; + my ($table, $primkey, $primval, %hash) = @_; my (@keys, @vals); - my $p = ""; - - if ($delay) { - &DEBUG("dbI: delay => $delay"); - $p = " DELAYED"; - } foreach (keys %hash) { push(@keys, $_); push(@vals, &dbQuote($hash{$_})); } - &dbRaw("Insert($table)", "INSERT $p INTO $table (".join(',',@keys). + &dbRaw("Insert($table)", "INSERT INTO $table (".join(',',@keys). ") VALUES (".join(',',@vals).")" ); @@ -279,13 +262,15 @@ sub dbInsert { } ##### -# Usage: &dbReplace($table, %hash); +# Usage: &dbReplace($table, $key, %hash); # Note: dbReplace does optional dbQuote. sub dbReplace { - my ($table, %hash) = @_; + my ($table, $key, %hash) = @_; my (@keys, @vals); - my $iquery = "INSERT INTO $table "; - my $uquery = "UPDATE $table SET "; + my $where = "WHERE $key=".&dbQuote($hash{$key}); + my $squery = "SELECT $key FROM $table $where;"; + my $iquery = "INSERT INTO $table "; + my $uquery = "UPDATE $table SET "; foreach (keys %hash) { if (s/^-//) { # as is. @@ -297,19 +282,22 @@ sub dbReplace { } $uquery .= "$keys[-1] = $vals[-1], "; } - $uquery = ~s/, $/;/; + $uquery = ~s/, $/ $where;/; $iquery .= "(". join(',',@keys) .") VALUES (". join(',',@vals) .");"; - &DEBUG($query) if (0); + &DEBUG($squery) if (0); - if(!&dbRaw("Replace($table)", $iquery)) { + if(&dbRawReturn($squery)) { &dbRaw("Replace($table)", $uquery); - } + } else { + &dbRaw("Replace($table)", $iquery); + } + return 1; } -##### +##### MADE REDUNDANT BY LEAR. # Usage: &dbSetRow($table, $vref, $delay); # Note: dbSetRow does dbQuote. sub dbSetRow ($@$) { @@ -407,7 +395,7 @@ sub sumKey { sub randKey { my ($table, $select) = @_; my $rand = int(rand(&countKeys($table) - 1)); - my $query = "SELECT $select FROM $table LIMIT $rand,1"; + my $query = "SELECT $select FROM $table LIMIT 1,$rand"; my $sth = $dbh->prepare($query); &SQLDebug($query); -- 2.39.2