From: Michael Andreen <harv@ruin.nu>
Date: Fri, 14 Aug 2009 23:19:31 +0000 (+0200)
Subject: Change infrastructure for users, groups and channels
X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=84d8f30ecbac4d41009e3f979526a47557f49225

Change infrastructure for users, groups and channels

Dropping surrogate keys for groups, channels and channel flags. Instead
use the following changes.

* groups.flag changes name to groups.gid and is used as primary key.
  Also attributes referencing this attribute with foreign keys are named
  gid.

* channels.name changes name to channels.channel and is used as primary
  key.

* channel_flags gets a flag attribute used as primary key.

Change username, pnick and hostaname to use the citext type, also use
normal unique constraints instead of functional indexes on lower().

Since ships_home has to be dropped and recreated, also change this view and
available_ships to use the new infrastructure.
---

diff --git a/database/available_ships.sql b/database/available_ships.sql
index ecd2ad7..e7d70b7 100644
--- a/database/available_ships.sql
+++ b/database/available_ships.sql
@@ -1,38 +1,39 @@
-CREATE TABLE ticks (t INTEGER PRIMARY KEY);
+/*CREATE TABLE ticks (t INTEGER PRIMARY KEY);
 
 INSERT INTO ticks (SELECT * FROM generate_series(0,10000));
+*/
 
 CREATE OR REPLACE VIEW ships_home AS
-SELECT tick, uid,username, planet, ship
+SELECT tick, uid,username, pid, ship
 	, COALESCE(f.amount - o.amount,f.amount) AS amount
 	, COALESCE(fleets,3) AS fleets
 FROM users u JOIN (
-	SELECT t AS tick, planet,ship,amount
+	SELECT t AS tick, pid,ship,amount
 	FROM (
-		SELECT DISTINCT ON (t,planet,mission) t,planet,mission, fid
+		SELECT DISTINCT ON (t,pid,mission) t,pid,mission, fid
 		FROM ticks
 			CROSS JOIN fleets f
 		WHERE tick <= t
 			AND name IN ('Main','Advanced Unit')
 			AND mission = 'Full fleet'
-		ORDER BY t,planet,mission,tick DESC, fid DESC
+		ORDER BY t,pid,mission,tick DESC, fid DESC
 	) f
 		JOIN fleet_ships fs USING (fid)
 	
-) f USING (planet) LEFT OUTER JOIN (
-	SELECT t AS tick, planet, ship, SUM(fs.amount) AS amount
+) f USING (pid) LEFT OUTER JOIN (
+	SELECT t AS tick, pid, ship, SUM(fs.amount) AS amount
 		, 3 - COUNT(DISTINCT fid) AS fleets
 	FROM ticks
 		CROSS JOIN fleets f
-		JOIN launch_confirmations USING (fid)
+		JOIN (SELECT landing_tick, fid, back, eta FROM launch_confirmations) lc USING (fid)
 		JOIN fleet_ships fs USING (fid)
 	WHERE back > t
 		AND landing_tick - eta - 12 < t
-	GROUP BY t,planet,ship
-) o USING (tick,planet,ship)
+	GROUP BY t,pid,ship
+) o USING (tick,pid,ship)
 WHERE COALESCE(f.amount - o.amount,f.amount) > 0;
 
 CREATE OR REPLACE VIEW available_ships AS
-SELECT uid,username, planet, ship, amount, fleets
+SELECT uid,username, pid, ship, amount, fleets
 FROM ships_home
 WHERE tick = tick();
diff --git a/database/groupschannels.sql b/database/groupschannels.sql
new file mode 100644
index 0000000..0a8834f
--- /dev/null
+++ b/database/groupschannels.sql
@@ -0,0 +1,184 @@
+DROP VIEW usersingroup;
+DROP VIEW full_defcalls;
+DROP VIEW defcalls;
+DROP VIEW users_defprio;
+DROP VIEW available_ships;
+DROP VIEW ships_home;
+
+ALTER TABLE groups RENAME gid TO id;
+ALTER TABLE groups RENAME flag TO gid;
+
+UPDATE groups SET gid = '' WHERE gid IS NULL;
+
+CREATE FUNCTION new_gid(id INTEGER) RETURNS CHAR AS $$
+	SELECT gid FROM groups WHERE id = $1
+$$ LANGUAGE SQL STABLE;
+
+ALTER TABLE channel_flags ADD flag CHAR(1);
+
+UPDATE channel_flags SET flag = 'o' WHERE name = 'auto_op';
+UPDATE channel_flags SET flag = 'O' WHERE name = 'op';
+UPDATE channel_flags SET flag = 'v' WHERE name = 'auto_voice';
+UPDATE channel_flags SET flag = 'V' WHERE name = 'voice';
+UPDATE channel_flags SET flag = 'i' WHERE name = 'auto_invite';
+UPDATE channel_flags SET flag = 'I' WHERE name = 'invite';
+
+CREATE FUNCTION new_flag(id INTEGER) RETURNS CHAR AS $$
+	SELECT flag FROM channel_flags WHERE id = $1
+$$ LANGUAGE SQL STABLE;
+
+ALTER TABLE channels RENAME name TO channel;
+ALTER TABLE channels ALTER channel TYPE citext;
+
+CREATE FUNCTION new_channel(id INTEGER) RETURNS citext AS $$
+	SELECT channel FROM channels WHERE id = $1
+$$ LANGUAGE SQL STABLE;
+
+ALTER TABLE channel_group_flags DROP CONSTRAINT channel_group_flags_group_fkey;
+ALTER TABLE channel_group_flags DROP CONSTRAINT channel_group_flags_flag_fkey;
+ALTER TABLE channel_group_flags DROP CONSTRAINT channel_group_flags_channel_fkey;
+ALTER TABLE group_roles DROP CONSTRAINT group_roles_gid_fkey;
+ALTER TABLE groupmembers DROP CONSTRAINT groupmembers_gid_fkey;
+ALTER TABLE raid_access DROP CONSTRAINT raid_access_gid_fkey;
+ALTER TABLE forum_access DROP CONSTRAINT forum_access_gid_fkey;
+ALTER TABLE wiki_namespace_access DROP CONSTRAINT wiki_namespace_access_gid_fkey;
+
+ALTER TABLE groups DROP CONSTRAINT groups_pkey;
+ALTER TABLE groups ADD PRIMARY KEY(gid);
+
+ALTER TABLE channel_flags DROP CONSTRAINT channel_flags_pkey;
+ALTER TABLE channel_flags ADD PRIMARY KEY(flag);
+
+ALTER TABLE channels DROP CONSTRAINT channels_pkey;
+ALTER TABLE channels DROP CONSTRAINT channels_name_key;
+ALTER TABLE channels ADD PRIMARY KEY(channel);
+
+ALTER TABLE channel_group_flags RENAME "group" TO gid;
+ALTER TABLE channel_group_flags ALTER flag TYPE CHAR(1) USING (new_flag(flag));
+ALTER TABLE channel_group_flags ADD FOREIGN KEY (flag) REFERENCES channel_flags(flag) ON UPDATE CASCADE ON DELETE CASCADE;
+ALTER TABLE channel_group_flags ALTER gid TYPE CHAR(1) USING (new_gid(gid));
+ALTER TABLE channel_group_flags ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE;
+ALTER TABLE channel_group_flags ALTER channel TYPE citext USING (new_channel(channel));
+ALTER TABLE channel_group_flags ADD FOREIGN KEY (channel) REFERENCES channels(channel) ON UPDATE CASCADE ON DELETE CASCADE;
+
+ALTER TABLE groupmembers ALTER gid TYPE CHAR(1) USING (new_gid(gid));
+ALTER TABLE groupmembers ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE;
+
+ALTER TABLE group_roles ALTER gid TYPE CHAR(1) USING (new_gid(gid));
+ALTER TABLE group_roles ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE;
+
+ALTER TABLE raid_access ALTER gid TYPE CHAR(1) USING (new_gid(gid));
+ALTER TABLE raid_access ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE;
+
+ALTER TABLE forum_access ALTER gid TYPE CHAR(1) USING (new_gid(gid));
+ALTER TABLE forum_access ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE;
+
+ALTER TABLE wiki_namespace_access ALTER gid TYPE CHAR(1) USING (new_gid(gid));
+ALTER TABLE wiki_namespace_access ADD FOREIGN KEY (gid) REFERENCES groups(gid) ON UPDATE CASCADE ON DELETE CASCADE;
+
+ALTER TABLE groups DROP id RESTRICT;
+ALTER TABLE groups DROP attack RESTRICT;
+ALTER TABLE channel_flags DROP id RESTRICT;
+ALTER TABLE channels DROP id RESTRICT;
+
+DROP FUNCTION new_gid(int);
+DROP FUNCTION new_flag(int);
+DROP FUNCTION new_channel(int);
+
+DROP INDEX users_pnick_key;
+DROP INDEX users_hostmask_key;
+DROP INDEX users_username_key;
+
+ALTER TABLE users ALTER username TYPE citext;
+ALTER TABLE users ALTER pnick TYPE citext;
+ALTER TABLE users ALTER hostmask TYPE citext;
+
+ALTER TABLE users ALTER pnick SET NOT NULL;
+ALTER TABLE users ALTER hostmask SET NOT NULL;
+
+ALTER TABLE users ADD UNIQUE(username);
+ALTER TABLE users ADD UNIQUE(pnick);
+ALTER TABLE users ADD UNIQUE(hostmask);
+
+
+CREATE OR REPLACE VIEW users_defprio AS
+SELECT u.*, (0.2 * (u.attack_points / GREATEST(a.attack, 1::numeric))
+		+ 0.4 * (u.defense_points / GREATEST(a.defense, 1::numeric))
+		+ 0.2 * (p.size::numeric / a.size) + 0.05 * (p.score::numeric / a.score)
+		+ 0.15 * (p.value::numeric / a.value))::numeric(3,2) AS defprio
+FROM users u
+	JOIN current_planet_stats p USING (pid)
+	, (
+		SELECT avg(u.attack_points) AS attack, avg(u.defense_points) AS defense
+			,avg(p.size) AS size, avg(p.score) AS score, avg(p.value) AS value
+		FROM users u
+			JOIN current_planet_stats p USING (pid)
+		WHERE uid IN ( SELECT uid FROM groupmembers WHERE gid = 'M')
+	) a;
+
+CREATE OR REPLACE VIEW defcalls AS
+SELECT call, status,c.uid, c.landing_tick
+	,dc.username AS dc, (c.landing_tick - tick()) AS curreta
+	,array_agg(COALESCE(race::text,'')) AS race
+	,array_agg(COALESCE(amount,0)) AS amount
+	,array_agg(COALESCE(eta,0)) AS eta
+	,array_agg(COALESCE(shiptype,'')) AS shiptype
+	,array_agg(COALESCE(alliance,'?')) AS alliance
+	,array_agg(coords(p2.x,p2.y,p2.z)) AS attackers
+FROM calls c
+	LEFT OUTER JOIN incomings i USING (call)
+	LEFT OUTER JOIN current_planet_stats p2 USING (pid)
+	LEFT OUTER JOIN users dc ON c.dc = dc.uid
+GROUP BY call,c.uid,dc.username, c.landing_tick, status;
+
+CREATE OR REPLACE VIEW full_defcalls AS
+SELECT call,status,x,y,z,pid,landing_tick,dc,curreta
+	,defprio, c.race, amount, c.eta, shiptype, c.alliance, attackers
+	,COUNT(NULLIF(f.back = f.landing_tick + f.eta - 1, FALSE)) AS fleets
+FROM users_defprio u
+	JOIN current_planet_stats p USING (pid)
+	JOIN defcalls c USING (uid)
+	LEFT OUTER JOIN launch_confirmations f USING (pid,landing_tick)
+GROUP BY call, x,y,z,pid,landing_tick,dc,curreta,defprio,c.race,amount,c.eta,shiptype,c.alliance,attackers, status
+;
+
+
+CREATE OR REPLACE FUNCTION change_member() RETURNS trigger
+    AS $_X$
+BEGIN
+	IF TG_OP = 'INSERT' THEN
+		IF NEW.gid = 'M' THEN
+			UPDATE planets SET alliance = 'NewDawn' WHERE
+				pid = (SELECT pid FROM users WHERE uid = NEW.uid);
+		END IF;
+	ELSIF TG_OP = 'DELETE' THEN
+		IF OLD.gid = 'M' THEN
+			UPDATE planets SET alliance = NULL WHERE
+				pid = (SELECT pid FROM users WHERE uid = OLD.uid);
+		END IF;
+	END IF;
+
+	return NEW;
+END;
+$_X$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION update_user_planet() RETURNS trigger AS $_X$
+BEGIN
+	IF COALESCE(NEW.pid <> OLD.pid,TRUE) OR NEW.username <> OLD.username THEN
+		UPDATE planets SET nick = NULL WHERE pid = OLD.pid;
+		UPDATE planets SET nick = NEW.username WHERE pid = NEW.pid;
+	END IF;
+
+	IF COALESCE(NEW.pid <> OLD.pid,TRUE)
+			AND (SELECT TRUE FROM groupmembers WHERE gid = 'M' AND uid = NEW.uid) THEN
+		UPDATE planets SET alliance = NULL WHERE pid = OLD.pid;
+		UPDATE planets SET alliance = 'NewDawn' WHERE pid = NEW.pid;
+	END IF;
+	RETURN NEW;
+END;
+$_X$ LANGUAGE plpgsql;
+
+DROP FUNCTION groups(int);
+CREATE OR REPLACE FUNCTION groups(uid integer) RETURNS SETOF CHAR
+    AS $_$SELECT gid FROM groupmembers WHERE uid = $1 UNION SELECT ''$_$
+    LANGUAGE sql STABLE;
diff --git a/lib/NDWeb/Auth/User.pm b/lib/NDWeb/Auth/User.pm
index 0e49a2e..c5738c3 100644
--- a/lib/NDWeb/Auth/User.pm
+++ b/lib/NDWeb/Auth/User.pm
@@ -31,7 +31,7 @@ sub load {
 
 	if (exists $authinfo->{id}){
 		$self->{id} = $dbh->selectrow_array(q{
-			SELECT uid FROM users WHERE lower(username) = lower(?)
+			SELECT uid FROM users WHERE username = ?
 		},undef,$authinfo->{id});
 	}elsif (exists $authinfo->{uid}){
 		$self->{id} = $authinfo->{uid};
diff --git a/lib/NDWeb/Controller/Calls.pm b/lib/NDWeb/Controller/Calls.pm
index 467c732..76d7f63 100644
--- a/lib/NDWeb/Controller/Calls.pm
+++ b/lib/NDWeb/Controller/Calls.pm
@@ -135,7 +135,7 @@ sub edit : Local {
 	}
 
 	my $available = $dbh->prepare(q{
-SELECT ship,amount from ships_home WHERE planet = $1 AND tick = $2
+SELECT ship,amount from ships_home WHERE pid = $1 AND tick = $2
 		});
 	$available->execute($call->{pid}, $call->{landing_tick});
 	my $fleet = {fid => $call->{member}, mission => 'Available'
diff --git a/lib/NDWeb/Controller/Forum.pm b/lib/NDWeb/Controller/Forum.pm
index a2f1cb8..caeeb1c 100644
--- a/lib/NDWeb/Controller/Forum.pm
+++ b/lib/NDWeb/Controller/Forum.pm
@@ -472,7 +472,7 @@ sub findUsers : Private {
 
 	my $query = $dbh->prepare(q{SELECT uid,username FROM users
 		WHERE uid > 0 AND uid IN (SELECT uid FROM groupmembers)
-		ORDER BY LOWER(username)});
+		ORDER BY username});
 	$query->execute;
 
 	$c->stash(users => $query->fetchall_arrayref({}) );
diff --git a/lib/NDWeb/Controller/Intel.pm b/lib/NDWeb/Controller/Intel.pm
index 64fe55a..015fe33 100644
--- a/lib/NDWeb/Controller/Intel.pm
+++ b/lib/NDWeb/Controller/Intel.pm
@@ -237,7 +237,7 @@ sub members : Local {
 				JOIN raid_targets rt ON r.id = rt.raid) rt ON rt.pid = i.target
 			AND (rt.tick + 12) > i.tick AND rt.tick <= i.tick
 		LEFT OUTER JOIN raid_claims rc ON rt.id = rc.target AND rc.uid = u.uid AND i.tick = rt.tick + rc.wave - 1
-		WHERE gm.gid = 2
+		WHERE gm.gid = 'M'
 		GROUP BY u.uid,u.username,u.attack_points, u.defense_points,n.tick
 		ORDER BY }. " $order DESC" );
 	$query->execute;
diff --git a/lib/NDWeb/Controller/Members.pm b/lib/NDWeb/Controller/Members.pm
index fc440a3..89997ba 100644
--- a/lib/NDWeb/Controller/Members.pm
+++ b/lib/NDWeb/Controller/Members.pm
@@ -261,7 +261,7 @@ SELECT username,defense_points,attack_points
 	,(attack_points+defense_points+scan_points/20)::NUMERIC(4,0) as total_points
 	, count(NULLIF(rc.launched,FALSE)) AS raid_points
 FROM users_defprio u LEFT OUTER JOIN raid_claims rc USING (uid)
-WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 2)
+WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
 GROUP BY username,defense_points,attack_points,scan_points,humor_points,defprio
 ORDER BY } . "$order $limit"
 	);
@@ -555,13 +555,13 @@ SELECT uid,pid AS planet,username, to_char(NOW() AT TIME ZONE timezone,'HH24:MI'
 	,sms_note, call_if_needed, race, timezone
 FROM users u
 	JOIN current_planet_stats p USING (pid)
-WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 2)
-ORDER BY call_if_needed DESC, LOWER(username)
+WHERE uid IN (SELECT uid FROM groupmembers WHERE gid = 'M')
+ORDER BY call_if_needed DESC, username
 		});
 	$defenders->execute;
 
 	my $available = $dbh->prepare(q{
-SELECT ship,amount FROM available_ships WHERE planet = $1
+SELECT ship,amount FROM available_ships WHERE pid = $1
 		});
 
 	my @members;
diff --git a/lib/NDWeb/Controller/Raids.pm b/lib/NDWeb/Controller/Raids.pm
index d7125f7..83b519e 100644
--- a/lib/NDWeb/Controller/Raids.pm
+++ b/lib/NDWeb/Controller/Raids.pm
@@ -223,7 +223,12 @@ sub edit : Local {
 
 	$c->stash(errors => $c->flash->{errors});
 
-	my $groups = $dbh->prepare(q{SELECT g.gid,g.groupname,raid FROM groups g LEFT OUTER JOIN (SELECT gid,raid FROM raid_access WHERE raid = ?) AS ra ON g.gid = ra.gid WHERE g.attack});
+	my $groups = $dbh->prepare(q{
+SELECT g.gid,g.groupname,raid
+FROM groups g
+	LEFT OUTER JOIN (SELECT gid,raid FROM raid_access WHERE raid = ?) AS ra USING (gid)
+WHERE gid IN (SELECT gid FROM group_roles WHERE role = 'attack_menu')
+		});
 	$groups->execute($raid ? $raid->{id} : undef);
 
 	my @addgroups;
@@ -312,24 +317,19 @@ sub postraidupdate : Local {
 
 	$c->forward('log',[$raid, 'BC updated raid']);
 
-	my $groups = $dbh->prepare(q{SELECT gid,groupname FROM groups WHERE attack});
-	my $delgroup = $dbh->prepare(q{DELETE FROM raid_access WHERE raid = ? AND gid = ?});
-	my $addgroup = $dbh->prepare(q{INSERT INTO raid_access (raid,gid) VALUES(?,?)});
+	my $delgroups = $dbh->prepare(q{DELETE FROM raid_access WHERE raid = $1 AND gid = ANY($2)});
+	my $addgroups = $dbh->prepare(q{INSERT INTO raid_access (raid,gid) VALUES($1,unnest($2::text[]))});
 
-	$groups->execute();
-	while (my $group = $groups->fetchrow_hashref){
-		my $query;
-		next unless defined $c->req->param($group->{gid});
-		my $command = $c->req->param($group->{gid});
-		if ( $command eq 'remove'){
-			$query = $delgroup;
-		}elsif($command eq 'add'){
-			$query = $addgroup;
-		}
-		if ($query){
-			$query->execute($raid,$group->{gid});
-			$c->forward('log',[$raid, "BC '$command' access for $group->{gid} ($group->{groupname})"]);
-		}
+	if ($c->req->param('add_group')){
+		my @groups = $c->req->param('add_group');
+		warn "GROUPS!!!!: @groups";
+		$addgroups->execute($raid,\@groups);
+		$c->forward('log',[$raid, "BC added access to groups: @groups"]);
+	}
+	if ($c->req->param('remove_group')){
+		my @groups = $c->req->param('remove_group');
+		$delgroups->execute($raid,\@groups);
+		$c->forward('log',[$raid, "BC removed access for groups: @groups"]);
 	}
 	$dbh->commit;
 
@@ -535,7 +535,7 @@ sub postcreate : Local {
 		$addtarget->execute($raid,\@targets,\@gals,$c->req->param('sizelimit'));
 		$c->forward('log',[$raid,"BC added planets (@targets) and the gals for (@gals)"]);
 	}
-	$dbh->do(q{INSERT INTO raid_access (raid,gid) VALUES(?,2)}
+	$dbh->do(q{INSERT INTO raid_access (raid,gid) VALUES(?,'M')}
 		,undef,$raid);
 	$dbh->commit;
 
diff --git a/lib/NDWeb/Controller/Users.pm b/lib/NDWeb/Controller/Users.pm
index c2a521e..a77d93d 100644
--- a/lib/NDWeb/Controller/Users.pm
+++ b/lib/NDWeb/Controller/Users.pm
@@ -28,11 +28,11 @@ sub index :Path :Args(0) {
 	my ( $self, $c ) = @_;
 	my $dbh = $c->model;
 
-	my $query = $dbh->prepare(qq{SELECT u.uid,username,TRIM(',' FROM concat(g.groupname||',')) AS groups
-		FROM users u LEFT OUTER JOIN (groupmembers gm NATURAL JOIN groups g) ON gm.uid = u.uid
-		WHERE u.uid > 0
+	my $query = $dbh->prepare(qq{SELECT uid,username,array_to_string(array_agg(g.groupname),', ') AS groups
+		FROM users u LEFT OUTER JOIN (groupmembers gm NATURAL JOIN groups g) USING (uid)
+		WHERE uid > 0
 		GROUP BY u.uid,username
-		ORDER BY lower(username)});
+		ORDER BY username});
 	$query->execute;
 
 	my @users;
@@ -49,10 +49,12 @@ sub edit : Local {
 	$c->forward('findUser');
 	$user = $c->stash->{u};
 
-	my $groups = $dbh->prepare(q{SELECT g.gid,g.groupname,uid 
-		FROM groups g 
-		LEFT OUTER JOIN (SELECT gid,uid FROM groupmembers WHERE uid = ?)
-			AS gm ON g.gid = gm.gid
+	my $groups = $dbh->prepare(q{
+SELECT g.gid,g.groupname,uid
+FROM groups g
+	LEFT OUTER JOIN (SELECT gid,uid FROM groupmembers WHERE uid = ?)
+	AS gm USING(gid)
+WHERE gid <> ''
 		});
 	$groups->execute($user->{uid});
 
@@ -84,8 +86,8 @@ sub updateUser : Local {
 			(SELECT ftid FROM users WHERE uid = $1),$1,$2)
 			});
 
-		my $delgroup = $dbh->prepare(q{DELETE FROM groupmembers WHERE uid = ? AND gid = ?});
-		my $addgroup = $dbh->prepare(q{INSERT INTO groupmembers (uid,gid) VALUES(?,?)});
+		my $delgroups = $dbh->prepare(q{DELETE FROM groupmembers WHERE uid = $1 AND gid = ANY($2) });
+		my $addgroups = $dbh->prepare(q{INSERT INTO groupmembers (uid,gid) (SELECT $1,unnest($2::text[]))});
 		for my $param ($c->req->param()){
 			if ($param =~ /^c:(planet|\w+_points|hostmask|info|username|email|sms)$/){
 				my $column = $1;
@@ -103,19 +105,14 @@ sub updateUser : Local {
 				$dbh->do(qq{UPDATE users SET $column = ? WHERE uid = ? }
 					,undef,$value,$user->{uid});
 				$log->execute($c->user->id,"HC changed $column from $user->{$column} to $value for user: $user->{uid} ($user->{username})");
-			}elsif ($param =~ /^gr:(\d+)$/){
-				my $query;
-				if ($c->req->param($param) eq 'remove'){
-					$query = $delgroup;
-				}elsif($c->req->param($param) eq 'add'){
-					$query = $addgroup;
-				}
-				if ($query){
-					$query->execute($user->{uid},$1);
-					my ($action,$a2) = ('added','to');
-					($action,$a2) = ('removed','from') if $c->req->param($param) eq 'remove';
-					$log->execute($c->user->id,"HC $action user: $user->{uid} ($user->{username}) $a2 group: $1");
-				}
+			}elsif ($param eq 'add_group'){
+				my @groups = $c->req->param($param);
+				$addgroups->execute($user->{uid},\@groups);
+				$log->execute($c->user->id,"HC added user: $user->{uid} ($user->{username}) to groups: @groups");
+			}elsif ($param eq 'remove_group'){
+				my @groups = $c->req->param($param);
+				$delgroups->execute($user->{uid},\@groups);
+				$log->execute($c->user->id,"HC removed user: $user->{uid} ($user->{username}) from groups: @groups");
 			}
 		}
 		$dbh->commit;
@@ -151,7 +148,7 @@ sub mail : Local {
 	$c->stash(subject => $c->flash->{subject});
 	$c->stash(message => $c->flash->{message});
 
-	my $groups = $dbh->prepare(q{SELECT gid,groupname FROM groups WHERE gid > 0 ORDER BY gid});
+	my $groups = $dbh->prepare(q{SELECT gid,groupname FROM groups WHERE gid <> '' ORDER BY gid});
 	$groups->execute;
 	my @groups;
 	push @groups,{gid => -1, groupname => 'Pick a group'};
diff --git a/root/src/raids/edit.tt2 b/root/src/raids/edit.tt2
index e332c91..a835441 100644
--- a/root/src/raids/edit.tt2
+++ b/root/src/raids/edit.tt2
@@ -17,7 +17,7 @@
 			<tr><th>Group</th><th>Remove</th></tr>
 			[% FOR g IN removegroups %]
 			<tr><td>[% g.groupname %]</td><td><input type="checkbox"
-				name="[% g.gid %]" value="remove"></td>
+				name="remove_group" value="[% g.gid %]"></td>
 			</tr>
 			[% END %]
 			</table>
@@ -28,7 +28,7 @@
 			<tr><th>Group</th><th>Add</th></tr>
 			[% FOR g IN addgroups %]
 			<tr><td>[% g.groupname %]</td><td><input type="checkbox"
-				name="[% g.gid %]" value="add"></td>
+				name="add_group" value="[% g.gid %]"></td>
 			</tr>
 			[% END %]
 			</table>
diff --git a/root/src/users/edit.tt2 b/root/src/users/edit.tt2
index db1db84..ccc1006 100644
--- a/root/src/users/edit.tt2
+++ b/root/src/users/edit.tt2
@@ -51,7 +51,7 @@
 	<table>
 		<tr><th>Group</th><th>Remove</th></tr>
 	[% FOR g IN  membergroups %]
-		<tr><td>[% g.groupname %]</td><td><input type="checkbox" name="gr:[% g.gid %]" value="remove"></td></tr>
+		<tr><td>[% g.groupname %]</td><td><input type="checkbox" name="remove_group" value="[% g.gid %]"></td></tr>
 	[% END %]
 	</table>
 [% END %]
@@ -60,7 +60,7 @@
 	<table>
 		<tr><th>Group</th><th>Add</th></tr>
 	[% FOR g IN  othergroups %]
-		<tr><td>[% g.groupname %]</td><td><input type="checkbox" name="gr:[% g.gid %]" value="add"></td></tr>
+		<tr><td>[% g.groupname %]</td><td><input type="checkbox" name="add_group" value="[% g.gid %]"></td></tr>
 	[% END %]
 	</table>
 [% END %]