From a7e034b3599d41cdf5a158b14bd1715451f89a0c Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Sun, 23 Aug 2009 18:12:15 +0200 Subject: [PATCH] Introduce a uid member for the context and use it intead of the host in commands --- Commands/Channel.pm | 19 +++++++++---------- Commands/Def.pm | 4 ++-- Commands/Members.pm | 8 ++++---- Commands/PA.pm | 8 ++++---- Commands/Quotes.pm | 4 ++-- Commands/SMS.pm | 4 ++-- Commands/Scans.pm | 12 ++++++------ Commands/Usermgm.pm | 6 +++--- Context.pm | 34 ++++++++++++++++++++++++++-------- 9 files changed, 58 insertions(+), 41 deletions(-) diff --git a/Commands/Channel.pm b/Commands/Channel.pm index 9207b0c..f83969e 100644 --- a/Commands/Channel.pm +++ b/Commands/Channel.pm @@ -47,12 +47,12 @@ SELECT DISTINCT LOWER(flag) FROM users u JOIN groupmembers g USING (uid) JOIN channel_group_flags gf USING (gid) JOIN channel_flags f USING (flag) -WHERE u.hostmask ILIKE $1 AND channel = $2 AND f.name = ANY($3); +WHERE uid = $1 AND channel = $2 AND f.name = ANY($3); }; if ($c->check_user_roles(qw/irc_masterop/)){ $mode = substr $access[0], 0,1; }else{ - ($mode) = $c->model->selectrow_array($mode,undef,$c->host,$c->channel,\@access); + ($mode) = $c->model->selectrow_array($mode,undef,$c->uid,$c->channel,\@access); } if ($mode){ $c->command(mode => $c->channel, "$mod$mode", $msg); @@ -73,17 +73,16 @@ sub invite if ($channel && $c->check_user_roles('irc_masterinvite')){ push @channels,$channel; }else{ - my @access = ('auto_invite'); - push @access, 'invite' if $channel; + my @access = ('i'); + push @access, 'I' if $channel; my $channels = $c->model->prepare(q{ SELECT DISTINCT channel FROM users u JOIN groupmembers g USING (uid) JOIN channel_group_flags gf USING (gid) - JOIN channel_flags f USING (flag) -WHERE u.hostmask ILIKE $1 AND COALESCE(channel = $2,TRUE) - AND (f.name = ANY($3) ) +WHERE uid = $1 AND COALESCE(channel = $2,TRUE) + AND (flag = ANY($3) ) }); - $channels->execute($c->host,$channel,\@access); + $channels->execute($c->uid,$channel,\@access); while (my ($channel) = $channels->fetchrow()){ push @channels,$channel; } @@ -131,8 +130,8 @@ sub getpass my $password = generate_random_string 10; my $update = $dbh->do(q{ UPDATE users SET password = MD5( ? ) -WHERE hostmask ILIKE ? AND password ='' - },undef,$password,$c->host); +WHERE uid ? AND password ='' + },undef,$password,$c->uid); if ($update > 0){ $c->reply("Password set to: $password (you can change it on webbie)"); }else{ diff --git a/Commands/Def.pm b/Commands/Def.pm index d7862a3..7c57099 100644 --- a/Commands/Def.pm +++ b/Commands/Def.pm @@ -172,10 +172,10 @@ sub calltake $dbh->begin_work; my $rows = $dbh->do(q{ -UPDATE calls SET dc = (SELECT uid FROM users WHERE hostmask ILIKE $1) +UPDATE calls SET dc = $1 ,status = $3 WHERE call = $2 - },undef,$c->host,$id,$status); + },undef,$c->uid,$id,$status); if ($rows == 1){ $c->reply("Setting status on call $id to $status"); $c->def_log($id , "Changed status: [B]$status [/B]"); diff --git a/Commands/Members.pm b/Commands/Members.pm index b1f4da9..9607289 100644 --- a/Commands/Members.pm +++ b/Commands/Members.pm @@ -80,7 +80,7 @@ sub points { my ($self,$c,$msg) = @_; my $f; - my $nick = $c->host; + my $nick = $c->uid; if ($msg =~ /(\S+)/ && $c->check_user_roles(qw/irc_points_others/)){ $nick = $1; $f = $c->model->prepare(q{ @@ -90,7 +90,7 @@ FROM users WHERE username ILIKE ? LIMIT 5 }else{ $f = $c->model->prepare(q{ SELECT username, attack_points, defense_points, scan_points, humor_points -FROM users WHERE hostmask ILIKE ? +FROM users WHERE uid = ? }); } $f->execute($nick); @@ -138,8 +138,8 @@ SELECT uid,username FROM users WHERE username ILIKE ? }, undef, $1); }else{ $user = $dbh->selectrow_hashref(q{ -SELECT uid,username FROM users WHERE hostmask ILIKE ? - }, undef, $c->host); +SELECT uid,username FROM users WHERE uid = ? + }, undef, $c->uid); } if ($user){ my $unread = $dbh->selectrow_hashref(q{SELECT * FROM unread_posts($1)},undef,$user->{uid}); diff --git a/Commands/PA.pm b/Commands/PA.pm index 2dcb006..52fb3a0 100644 --- a/Commands/PA.pm +++ b/Commands/PA.pm @@ -126,8 +126,8 @@ sub xp my ($avalue,$ascore) = $c->model->selectrow_array(q{ SELECT value,score FROM current_planet_stats -WHERE pid = (SELECT pid FROM users WHERE hostmask ILIKE ?) - }, undef, $c->host); +WHERE pid = (SELECT pid FROM users WHERE uid = ?) + }, undef, $c->uid); my ($tvalue,$tscore,$tsize) = $c->model->selectrow_array(q{ SELECT value,score,size FROM current_planet_stats WHERE x = ? AND y = ? and z = ? @@ -167,8 +167,8 @@ sub fco my ($value,$score) = $c->model->selectrow_array(q{ SELECT value,score FROM planet_stats WHERE tick = $2 AND - pid = (SELECT pid FROM users WHERE hostmask ILIKE $1) - }, undef, $c->host,$tick); + pid = (SELECT pid FROM users WHERE uid = $1) + }, undef, $c->uid,$tick); unless ($value){ $c->reply("You don't have a planet registered."); return; diff --git a/Commands/Quotes.pm b/Commands/Quotes.pm index 7d18819..608832f 100644 --- a/Commands/Quotes.pm +++ b/Commands/Quotes.pm @@ -108,8 +108,8 @@ sub delquote $n = $n-1; if (exists $FILE[$n]){ my ($uid,$username) = $c->model->selectrow_array(q{ -SELECT uid,username FROM users where hostmask ILIKE ? - },undef,$c->host); +SELECT uid,username FROM users where uid = ? + },undef,$c->uid); my $text = $FILE[$n]; push @OLDFILE,"Removed by $username ($uid): $text"; diff --git a/Commands/SMS.pm b/Commands/SMS.pm index bc0200c..eba2d65 100644 --- a/Commands/SMS.pm +++ b/Commands/SMS.pm @@ -57,10 +57,10 @@ SELECT sms FROM users WHERE username ilike $1 } my $sms = $dbh->prepare(q{ INSERT INTO sms (uid,number,message) -VALUES((SELECT uid FROM users WHERE hostmask ilike $1),$2,$3) +VALUES($1,$2,$3) RETURNING id }); - $sms->execute($c->host,$number,$message); + $sms->execute($c->uid,$number,$message); my ($id) = $sms->fetchrow_array; $c->reply("Message added to queue, you can see the status with: .smsstatus $id"); } diff --git a/Commands/Scans.pm b/Commands/Scans.pm index 3159031..ffc8e18 100644 --- a/Commands/Scans.pm +++ b/Commands/Scans.pm @@ -70,10 +70,10 @@ sub gs }else{ my $req = $c->model->prepare(q{ SELECT * FROM scan_requests -WHERE uid = (SELECT uid FROM users WHERE hostmask ILIKE $1) +WHERE uid = $1 AND pid = $2 AND type = $3 AND NOT sent }); - $req->execute($c->host,$planet,$type); + $req->execute($c->uid,$planet,$type); my $id; if(my $scan = $req->fetchrow_hashref){ @@ -86,14 +86,14 @@ WHERE id = $2 }else{ $req = $c->model->prepare(q{ INSERT INTO scan_requests (uid,nick,pid,type) -VALUES((SELECT uid FROM users WHERE hostmask ILIKE $1),$2,$3,$4) RETURNING (id) +VALUES($1,$2,$3,$4) RETURNING (id) }); - $req->execute($c->host,$c->nick,$planet,$type); + $req->execute($c->uid,$c->nick,$planet,$type); $id = $req->fetchrow; } if ($id){ - $c->message(privmsg =>$c->disp->targets->{scans} + $c->message(privmsg => $c->disp->targets->{scan} ,"$id http://game.planetarion.com/waves.pl?id=$typeid&x=$x&y=$y&z=$z" . " ($x:$y:$z $type) | <".$c->nick."> $msg" ); @@ -217,7 +217,7 @@ WHERE groupscan = $1 AND scan_id = LOWER($2) AND tick >= tick() - 168 INSERT INTO scans (scan_id,tick,groupscan,uid) VALUES (LOWER($1),tick(),$2,COALESCE($3,-1)) }); my $user = $dbh->selectrow_hashref(q{SELECT uid,username, scan_points, tick() - FROM users WHERE hostmask ILIKE ? },undef,$c->host); + FROM users WHERE uid = ? },undef,$c->uid); my $groupscans = 0; my $scans = 0; eval { diff --git a/Commands/Usermgm.pm b/Commands/Usermgm.pm index 78c02b8..adda73f 100644 --- a/Commands/Usermgm.pm +++ b/Commands/Usermgm.pm @@ -40,7 +40,7 @@ sub adduser my $host = "$pnick.users.netgamers.org"; my ($username,$hostname,$p_nick) = $dbh->selectrow_array(q{ SELECT username, hostmask,pnick -FROM users WHERE username ILIKE ? OR hostmask ILIKE ? OR pnick ILIKE ? +FROM users WHERE username = $1 OR hostmask = $2 OR pnick = $3 },undef,$nick,$host,$pnick); if (defined $username){ @@ -351,7 +351,7 @@ SELECT uid,username,pnick,hostmask FROM users WHERE username ILIKE ? if($@){ if ($@ =~ /duplicate key value violates unique constraint/){ my ($username, $hostname) = $dbh->selectrow_array(q{ -SELECT username,hostmask FROM users WHERE hostmask ILIKE $1 +SELECT username,hostmask FROM users WHERE hostmask = $1 },undef,$host); $c->reply("Problem, $username already uses host $hostname."); }else{ @@ -387,7 +387,7 @@ sub setpnick if($@){ if ($@ =~ /duplicate key value violates unique constraint/){ my ($username, $hostname, $pnick) = $dbh->selectrow_array(q{ -SELECT username,hostmask,pnick FROM users WHERE hostmask ILIKE $1 OR pnick ILIKE $2 +SELECT username,hostmask,pnick FROM users WHERE hostmask = $1 OR pnick = $2 },undef,$hostname, $pnick); $c->reply("Problem, $username already uses host $hostname and pnick $pnick."); }else{ diff --git a/Context.pm b/Context.pm index 8a0a911..8c16a95 100644 --- a/Context.pm +++ b/Context.pm @@ -50,6 +50,12 @@ has roles => ( lazy_build => 1 ); +has uid => ( + is => 'ro', + isa => 'Int', + lazy_build => 1 +); + has disp => ( is => 'ro', isa => 'Object', @@ -143,21 +149,18 @@ sub intel_log { my ($c,$planet, $message) = @_; my $log = $c->model->prepare_cached(q{ INSERT INTO forum_posts (ftid,uid,message) VALUES( - (SELECT ftid FROM planets WHERE pid = $3) - ,(SELECT uid FROM users WHERE hostmask ILIKE $1) - ,$2) + (SELECT ftid FROM planets WHERE pid = $3),$1,$2) }); - $log->execute($c->host,$message,$planet); + $log->execute($c->uid,$message,$planet); } sub def_log { my ($c,$call, $message) = @_; my $log = $c->model->prepare(q{ INSERT INTO forum_posts (ftid,uid,message) VALUES( - (SELECT ftid FROM calls WHERE call = $3) - ,(SELECT uid FROM users WHERE hostmask ILIKE $1),$2) + (SELECT ftid FROM calls WHERE call = $3),$1,$2) }); - $log->execute($c->host,$message,$call); + $log->execute($c->uid,$message,$call); } sub _build_roles { @@ -166,7 +169,7 @@ sub _build_roles { my $query = $self->model->prepare(q{ SELECT role FROM group_roles WHERE gid IN (SELECT gid FROM groupmembers JOIN users USING (uid) - WHERE hostmask ILIKE $1) + WHERE hostmask = $1) }); $query->execute($self->host); @@ -177,5 +180,20 @@ WHERE gid IN (SELECT gid FROM groupmembers JOIN users USING (uid) return Set::Object->new(@roles); } +sub _build_uid { + my ($self) = @_; + + my $query = $self->model->prepare(q{ +SELECT uid FROM users +WHERE hostmask = $1 + }); + $query->execute($self->host); + + if (my ($uid) = $query->fetchrow_array){ + $query->finish; + return $uid; + } + return -4; +} 1; -- 2.39.2