From 1ccb344994c1b517c662da9f236cf1c640780177 Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Sun, 16 Aug 2009 00:16:18 +0200 Subject: [PATCH] Update for changes to groups and irc channels --- Commands/Channel.pm | 32 +++++++++++++++----------------- Commands/Def.pm | 3 +-- Commands/Members.pm | 8 ++++---- Commands/SMS.pm | 2 +- Commands/Usermgm.pm | 37 ++++++++++++++++++------------------- 5 files changed, 39 insertions(+), 43 deletions(-) diff --git a/Commands/Channel.pm b/Commands/Channel.pm index ad627b3..d309ce0 100644 --- a/Commands/Channel.pm +++ b/Commands/Channel.pm @@ -33,26 +33,25 @@ sub op { my ($self,$c,$msg) = @_; - my ($access) = $self->name =~ /(op|voice)/; + $self->name =~ /(op|voice)/; + my @access = ($1); - my $where = ""; if ($msg =~ /^\s*$/){ $msg = $c->nick; - $where = "OR f.name = 'auto_$access'"; + push @access, "auto_$access[0]"; } - my $mode = qq{ -SELECT DISTINCT c.name FROM users u - JOIN groupmembers g ON g.uid = u.uid - JOIN channel_group_flags gf ON g.gid = gf.group - JOIN channels c ON gf.channel = c.id - JOIN channel_flags f ON f.id = gf.flag -WHERE u.hostmask ILIKE ? AND c.name = ? AND (f.name = '$access' $where); + my $mode = 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 channel = $2 AND f.name = ANY($3); }; if ($c->check_user_roles(qw/irc_masterop/)){ $mode = 1; }else{ - ($mode) = $c->model->selectrow_array($mode,undef,$c->host,$c->channel); + ($mode) = $c->model->selectrow_array($mode,undef,$c->host,$c->channel,\@access); } if ($mode){ $c->server->command($self->name . " " . $c->channel . " $msg"); @@ -76,12 +75,11 @@ sub invite my @access = ('auto_invite'); push @access, 'invite' if $channel; my $channels = $c->model->prepare(q{ -SELECT DISTINCT c.name FROM users u - JOIN groupmembers g ON g.uid = u.uid - JOIN channel_group_flags gf ON g.gid = gf.group - JOIN channels c ON gf.channel = c.id - JOIN channel_flags f ON f.id = gf.flag -WHERE u.hostmask ILIKE $1 AND COALESCE(c.name = $2,TRUE) +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) ) }); $channels->execute($c->host,$channel,\@access); diff --git a/Commands/Def.pm b/Commands/Def.pm index 1731689..80b02fd 100644 --- a/Commands/Def.pm +++ b/Commands/Def.pm @@ -55,7 +55,7 @@ sub defcall my $st = $dbh->prepare(q{ SELECT status ,c.landing_tick - (SELECT value::integer FROM misc WHERE id = 'TICK') AS eta - ,concat(i.shiptype||'/') AS shiptype + ,array_to_string(array_agg(i.shiptype),'/') AS shiptype FROM calls c JOIN incomings i USING (call) LEFT OUTER JOIN users dc ON dc.uid = c.dc @@ -68,7 +68,6 @@ ORDER BY c.landing_tick; $c->reply("No call with id: $callnr"); return; } - chop($call->{shiptype}); $callinfo = "(Anti $call->{shiptype} ETA: $call->{eta})"; if($call->{status} eq 'Covered'){ $c->reply("Call $callnr $callinfo is covered."); diff --git a/Commands/Members.pm b/Commands/Members.pm index 1a759b7..b1f4da9 100644 --- a/Commands/Members.pm +++ b/Commands/Members.pm @@ -32,8 +32,9 @@ sub def { my ($self,$c,$msg) = @_; my $f = $c->model->prepare(q{ -SELECT (c.landing_tick - tick()) AS eta, concat(i.shiptype||'/') AS shiptype - , dc.username +SELECT (c.landing_tick - tick()) AS eta + ,array_to_string(array_agg(i.shiptype),'/') AS shiptype + ,dc.username FROM calls c JOIN incomings i USING (call) LEFT OUTER JOIN users dc ON dc.uid = c.dc @@ -44,7 +45,6 @@ ORDER BY c.landing_tick; $f->execute(); my $calls = ""; while (my @row = $f->fetchrow()){ - chop($row[1]); my $dc = $row[2] // ''; $calls .= " (Anti $row[1] ETA: $row[0] DC: $dc) |" } @@ -63,7 +63,7 @@ sub raids my $f = $c->model->prepare(q{ SELECT id FROM raids WHERE open AND not removed AND tick + waves - 7 > tick() -AND id IN (SELECT raid FROM raid_access WHERE gid = 2) +AND id IN (SELECT raid FROM raid_access WHERE gid = 'M') }); $f->execute(); my $calls = ""; diff --git a/Commands/SMS.pm b/Commands/SMS.pm index 265384d..bc0200c 100644 --- a/Commands/SMS.pm +++ b/Commands/SMS.pm @@ -45,7 +45,7 @@ sub sendsms }else{ my ($sms) = $dbh->selectrow_array(q{ SELECT sms FROM users WHERE username ilike $1 - AND uid IN (SELECT uid FROM groupmembers WHERE gid = 2) + AND uid IN (SELECT uid FROM groupmembers WHERE gid = 'M') },undef,$number); $sms //= 'No number, or invalid user'; if ($sms =~ /^\+([1-9]\d+)$/){ diff --git a/Commands/Usermgm.pm b/Commands/Usermgm.pm index e856385..78c02b8 100644 --- a/Commands/Usermgm.pm +++ b/Commands/Usermgm.pm @@ -102,20 +102,21 @@ sub chattr my $update; if ($flags =~ /^(-)/){ $update = $dbh->prepare(q{ -DELETE FROM groupmembers WHERE uid = $1 AND - gid IN (SELECT gid FROM groups WHERE flag = ANY($2)) +DELETE FROM groupmembers WHERE uid = $1 AND gid = ANY($2) }); }else{ $update = $dbh->prepare(q{ -INSERT INTO groupmembers (uid,gid) (SELECT $1,gid FROM groups - WHERE flag = ANY($2) AND gid NOT IN (SELECT gid FROM groupmembers WHERE uid = $1)) +INSERT INTO groupmembers (uid,gid) + (SELECT $1,gid FROM unnest($2::text[]) gid WHERE + gid NOT IN (SELECT gid FROM groupmembers WHERE uid = $1) + AND gid IN (SELECT gid FROM groups)) }); } my @flags = split /\W*/,$flags; $update->execute($user->{uid},\@flags); $update = $dbh->prepare(q{ -SELECT concat(flag) -FROM (SELECT uid,flag FROM groupmembers NATURAL JOIN groups ORDER BY uid,flag ) g +SELECT array_to_string(array_agg(gid),'') +FROM (SELECT uid,gid FROM groupmembers ORDER BY uid,gid ) g WHERE uid = ? }); $flags = $dbh->selectrow_array($update,undef,$user->{uid}); @@ -139,17 +140,17 @@ sub whois unless ($nick){ my ($flags) = $dbh->selectrow_array(q{ -SELECT TRIM(', ' FROM concat(flag||':'||groupname||', ')) FROM groups +SELECT array_to_string(array_agg(gid||':'||groupname),', ') FROM groups }); $c->reply("Current flags: $flags"); return; } my $f = $dbh->prepare(q{ -SELECT username, pnick, hostmask, concat(flag) AS flags +SELECT username, pnick, hostmask, array_to_string(array_agg(gid),'') AS flags FROM users u - LEFT OUTER JOIN (SELECT uid,flag FROM groupmembers - NATURAL JOIN groups ORDER BY uid,flag ) g USING (uid) + LEFT OUTER JOIN (SELECT uid,gid FROM groupmembers ORDER BY uid,gid + ) g USING (uid) WHERE username ILIKE ? GROUP BY username,pnick,hostmask LIMIT 5 }); @@ -170,11 +171,10 @@ sub flag my ($flag) = $msg =~ /^(\w)$/ or die 'ARGS'; my $f = $c->model->prepare(q{ -SELECT TRIM(', ' FROM concat(username||', ')),count(username) +SELECT array_to_string(array_agg(username),', '),count(username) FROM (SELECT uid, username FROM users ORDER BY username) u JOIN groupmembers gm USING (uid) - JOIN groups g USING (gid) -WHERE flag = $1 +WHERE gid = $1 }); my ($users,$count) = $c->model->selectrow_array($f,undef,$flag); $c->reply("$count Users with flag $flag: $users"); @@ -201,12 +201,11 @@ FROM (SELECT username (SELECT max(timestamp) FROM raid_claims WHERE uid = u.uid)) AS lastclaim FROM users u NATURAL JOIN groupmembers - NATURAL JOIN groups - WHERE flag = $1 + WHERE gid = $1 ) a WHERE COALESCE(last >= $2,TRUE) AND COALESCE(lastforum >= $3,TRUE) AND COALESCE(lastclaim >= $4,TRUE) -ORDER BY last DESC, lastforum DESC, lastclaim DESC +ORDER BY a.last DESC, a.lastforum DESC, a.lastclaim DESC }); $f->execute($flag,$min,$forum,$claim); @@ -234,7 +233,7 @@ SELECT username, COALESCE(date_part('day',now() - laston)::text,'?') AS last ,COALESCE(date_part('day',now() - (SELECT max(timestamp) FROM raid_claims WHERE uid = u.uid))::text,'?') AS lastclaim FROM users u -WHERE username ILIKE $1 ORDER BY lower(username) +WHERE username ILIKE $1 ORDER BY username }); $f->execute($username); @@ -264,14 +263,14 @@ FROM users u ORDER BY pid,tick DESC,fid DESC ) f USING (pid) JOIN fleet_ships fs USING (fid) -WHERE ship ILIKE $1 AND uid IN (SELECT uid FROM groupmembers WHERE gid = 2) +WHERE ship ILIKE $1 AND uid IN (SELECT uid FROM groupmembers WHERE gid = 'M') GROUP BY username ORDER BY amount DESC }); if ($self->name eq 'shipshome'){ $f = $dbh->prepare(q{ SELECT username,SUM(amount) AS amount FROM available_ships -WHERE ship ILIKE ? AND uid IN (SELECT uid FROM groupmembers WHERE gid = 2) +WHERE ship ILIKE ? AND uid IN (SELECT uid FROM groupmembers WHERE gid = 'M') GROUP BY username ORDER BY amount DESC }); } -- 2.39.2