Update for changes to groups and irc channels

diff --git a/Commands/Channel.pm b/Commands/Channel.pm
index ad627b3d835e37f9891c670405375faf70755db5..d309ce00ae1ac4e533f08f66f90101db2fe28159 100644 (file)
--- 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 1731689e12689e673d06cec3e0d1543e9acbb80c..80b02fdf454d560b161857085461c3defe577062 100644 (file)
--- 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 <b>$callnr</b> $callinfo is covered.");

diff --git a/Commands/Members.pm b/Commands/Members.pm
index 1a759b7495348a9cb08cee7003f5988e821fb974..b1f4da9a998bf8aa083a829063810a8c1ff1d18a 100644 (file)
--- 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 265384ddd9dae941b2e6c1f5d266d028fb3a5838..bc0200c08403007300dfccfcc4adb6cda19a59af 100644 (file)
--- 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 e85638562653df4206abff60a1ae6a90c578f42d..78c02b81d24431a0a9cf7b6d3156d72c59017fb8 100644 (file)
--- 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("<b>$count</b> Users with flag <b>$flag</b>: $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
                });
        }