From b6d8351387cf06b88e362a458aad1e0982e575dd Mon Sep 17 00:00:00 2001
From: Michael Andreen <harv@ruin.nu>
Date: Tue, 28 Jul 2009 02:51:51 +0200
Subject: [PATCH] Change database structure for planets and alliances + PG 8.4
 changes

Change the id for planets from id to pid. Similar change for alliances where
id is changed to aid. This makes them unique in the database, which
makes it possible to replace several 'JOIN .. ON' with 'JOIN .. USING'
or 'NATURAL JOIN'.

Also, use the real alliance name as foreign key instead of the
surrogate key.

Some bigger queries are moved into views, mainly defense calls and
alliance resource estimation.

Together with this, use some new PostgreSQL 8.4 features.

* CTE and Window function for alliance resource estimate, so only the
  top60 planets are picked.

* Use the new array_agg() instead of the custom array_accum()

Also, since 0000 is not a valid year anymore, raid.js is changed to use
0001 instead.
---
 database/noperl.sql                |  29 ++---
 database/planets.sql               | 188 +++++++++++++++++++++++++++++
 lib/NDWeb/Auth/User.pm             |   2 +-
 lib/NDWeb/Controller/Alliances.pm  | 109 +++++++----------
 lib/NDWeb/Controller/Calls.pm      | 100 +++++++--------
 lib/NDWeb/Controller/CovOp.pm      |  16 +--
 lib/NDWeb/Controller/Graphs.pm     |  36 +++---
 lib/NDWeb/Controller/Intel.pm      |  42 +++----
 lib/NDWeb/Controller/JSRPC.pm      |   4 +-
 lib/NDWeb/Controller/Members.pm    |  60 ++++-----
 lib/NDWeb/Controller/Raids.pm      |  67 +++++-----
 lib/NDWeb/Controller/Rankings.pm   |   4 +-
 lib/NDWeb/Controller/Root.pm       |   8 +-
 lib/NDWeb/Controller/Stats.pm      |  48 ++++----
 lib/NDWeb/Controller/TextExport.pm |   2 +-
 lib/NDWeb/Controller/Users.pm      |  10 +-
 lib/NDWeb/Include.pm               |   6 +-
 root/src/intel/planet.tt2          |   2 +-
 root/static/js/raid.js             |   2 +-
 scripts/createretal.pl             |   8 +-
 scripts/ndrank.pl                  |   2 +-
 scripts/parsealliances.pl          |   6 +-
 scripts/parseplanets.pl            |  18 +--
 scripts/scans.pl                   |  20 +--
 24 files changed, 460 insertions(+), 329 deletions(-)
 create mode 100644 database/planets.sql

diff --git a/database/noperl.sql b/database/noperl.sql
index f882134..48fcbe3 100644
--- a/database/noperl.sql
+++ b/database/noperl.sql
@@ -2,47 +2,48 @@ CREATE OR REPLACE FUNCTION find_planet_id(_ruler text, _planet text, _race race)
     AS $_$
 DECLARE
 	p RECORD;
-	pid INTEGER;
+	id INTEGER;
 	thread INTEGER;
 BEGIN
-	SELECT id, race INTO p FROM planets WHERE ruler = _ruler AND planet = _planet;
+	SELECT pid, race INTO p FROM planets WHERE ruler = _ruler AND planet = _planet;
 	IF FOUND THEN
 		IF _race <> p.race THEN
-			UPDATE planets SET race = _race WHERE id = p.id;
+			UPDATE planets SET race = _race WHERE pid = p.pid;
 		END IF;
-		pid := p.id;
+		id := p.pid;
 	ELSE
 		INSERT INTO forum_threads (fbid,subject,uid) VALUES(-2, _ruler || ' OF ' || _planet, -3)
 			RETURNING ftid INTO thread;
 		INSERT INTO planets(ruler,planet,race,ftid) VALUES(_ruler,_planet,_race,thread)
-			RETURNING id INTO pid;
+			RETURNING pid INTO id;
 	END IF;
-	RETURN pid;
+	RETURN id;
 END;
 $_$
     LANGUAGE plpgsql;
 
-DROP FUNCTION findplanetid(character varying, character varying, character varying);
+DROP FUNCTION IF EXISTS findplanetid(character varying, character varying, character varying);
 
-CREATE OR REPLACE FUNCTION find_alliance_id(alliance text) RETURNS integer
+CREATE OR REPLACE FUNCTION find_alliance_id(alli text) RETURNS integer
     AS $_$
 DECLARE
-	aid INTEGER;
+	id INTEGER;
 BEGIN
-	SELECT id FROM INTO aid alliances WHERE name = alliance;
+	SELECT aid FROM INTO id alliances WHERE alliance = alli;
 	IF NOT FOUND THEN
-		INSERT INTO alliances(name) VALUES($1)
-			RETURNING id INTO aid;
+		INSERT INTO alliances(alliance) VALUES($1)
+			RETURNING aid INTO id;
 	END IF;
-	RETURN aid;
+	RETURN id;
 END;
 $_$
     LANGUAGE plpgsql;
 
-DROP FUNCTION find_alliance_id(character varying);
+DROP FUNCTION IF EXISTS find_alliance_id(character varying);
 
 CREATE OR REPLACE FUNCTION coords(x integer, y integer, z integer) RETURNS text
     AS $_$
 SELECT $1 || ':' || $2 || ':' || $3
 $_$
     LANGUAGE sql IMMUTABLE;
+
diff --git a/database/planets.sql b/database/planets.sql
new file mode 100644
index 0000000..1bb5ef9
--- /dev/null
+++ b/database/planets.sql
@@ -0,0 +1,188 @@
+DROP VIEW users_defprio;
+DROP VIEW current_planet_stats;
+DROP VIEW current_planet_stats_full;
+DROP VIEW current_planet_scans;
+DROP VIEW current_development_scans;
+
+
+ALTER TABLE planets ALTER ruler TYPE text;
+ALTER TABLE planets ALTER planet TYPE text;
+ALTER TABLE planets ALTER nick TYPE citext;
+ALTER TABLE planets ALTER channel TYPE citext;
+ALTER TABLE alliances RENAME name TO alliance;
+ALTER TABLE alliances ALTER alliance TYPE text;
+ALTER TABLE alliances RENAME id TO aid;
+ALTER TABLE alliance_stats RENAME id TO aid;
+
+CREATE FUNCTION alliance_name(id INTEGER) RETURNS TEXT AS $$
+	SELECT alliance FROM alliances WHERE aid = $1
+$$ LANGUAGE SQL STABLE;
+
+ALTER TABLE planets RENAME id TO pid;
+ALTER TABLE planets RENAME alliance_id TO alliance;
+ALTER TABLE planetS DROP CONSTRAINT planets_alliance_id_fkey;
+ALTER TABLE planets ALTER alliance TYPE text USING alliance_name(alliance);
+ALTER TABLE planetS ADD FOREIGN KEY (alliance) REFERENCES alliances(alliance) ON UPDATE CASCADE ON DELETE SET NULL;
+
+DROP FUNCTION alliance_name(INTEGER);
+
+ALTER TABLE planet_stats RENAME id TO pid;
+ALTER TABLE users RENAME planet TO pid;
+ALTER TABLE fleets RENAME planet TO pid;
+ALTER TABLE launch_confirmations RENAME target TO pid;
+ALTER TABLE development_scans RENAME planet TO pid;
+ALTER TABLE planet_scans RENAME planet TO pid;
+ALTER TABLE raid_targets RENAME planet TO pid;
+ALTER TABLE scan_requests RENAME planet TO pid;
+ALTER TABLE scans RENAME planet TO pid;
+ALTER TABLE covop_attacks RENAME id TO pid;
+ALTER TABLE incomings RENAME sender TO pid;
+
+CREATE VIEW current_planet_stats AS
+SELECT p.pid, p.nick, p.planet_status, p.hit_us, ps.x, ps.y, ps.z, p.ruler, p.planet, p.race
+	,alliance, a.relationship, a.aid, p.channel, p.ftid, p.gov
+	,ps.size, ps.score, ps.value, ps.xp, ps.sizerank, ps.scorerank, ps.valuerank, ps.xprank
+FROM ( SELECT pid, tick, x, y, z, size, score, value, xp, sizerank, scorerank, valuerank, xprank
+		FROM planet_stats
+		WHERE tick = ( SELECT max(tick) AS max FROM planet_stats)
+	) ps
+	NATURAL JOIN planets p
+	LEFT JOIN alliances a USING (alliance);
+
+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 = 2)
+	) a;
+
+CREATE OR REPLACE VIEW current_planet_stats_full AS
+SELECT *
+FROM planets p
+	NATURAL JOIN (
+		SELECT *
+		FROM planet_stats
+		WHERE tick = ( SELECT max(tick) AS max FROM planet_stats)
+	) ps
+	LEFT JOIN alliances USING (alliance);
+
+CREATE OR REPLACE FUNCTION change_member() RETURNS trigger
+    AS $_X$
+BEGIN
+	IF TG_OP = 'INSERT' THEN
+		IF NEW.gid = 2 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 = 2 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 VIEW current_planet_scans AS
+SELECT DISTINCT ON (pid) ps.*
+FROM planet_scans ps
+ORDER BY pid, tick DESC, id DESC;
+
+CREATE OR REPLACE VIEW current_development_scans AS
+SELECT DISTINCT ON (pid) ds.*
+FROM development_scans ds
+ORDER BY pid, tick DESC, id DESC;
+
+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 = 2 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;
+
+CREATE OR REPLACE FUNCTION planetid(x integer, y integer, z integer, tick integer) RETURNS integer
+    AS $_$SELECT pid FROM planet_stats WHERE x = $1 AND y = $2 AND z = $3 AND (tick >= $4  OR tick =( SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC LIMIT 1$_$
+    LANGUAGE sql STABLE;
+
+CREATE OR REPLACE FUNCTION planetcoords(id integer, tick integer, OUT x integer, OUT y integer, OUT z integer) RETURNS record
+    AS $_$SELECT x,y,z FROM planet_stats WHERE pid = $1 AND (tick >= $2  OR tick =( SELECT max(tick) FROM planet_stats))  ORDER BY tick ASC LIMIT 1$_$
+    LANGUAGE sql STABLE;
+
+CREATE OR REPLACE VIEW alliance_resources AS
+WITH planet_estimates AS (
+	SELECT ps.tick, alliance, hidden,size,score,(metal+crystal+eonium) AS resources
+		,score + (metal+crystal+eonium)/300 + hidden/100 AS nscore2
+		,score + (metal+crystal+eonium)/300 + hidden/100 + (endtick()-tick())*(
+			250*size + COALESCE(metal_ref + crystal_ref + eonium_ref,7)* 1000
+			+ CASE extraction WHEN 0 THEN 3000 WHEN 1 THEN 11500 ELSE COALESCE(extraction,3)*3000*3 END
+		)*(1.35+0.005*COALESCE(fincents,20))/100 AS nscore3
+	FROM current_planet_stats p
+		JOIN current_planet_scans ps USING (pid)
+		LEFT OUTER JOIN current_development_scans ds USING (pid)
+), planet_ranks AS (
+	SELECT *, RANK() OVER(PARTITION BY alliance ORDER BY score DESC) AS rank FROM planet_estimates
+), top_planets AS (
+	SELECT alliance, sum(resources) AS resources, sum(hidden) AS hidden
+		,sum(nscore2)::bigint AS nscore2, sum(nscore3)::bigint AS nscore3
+		,count(*) AS planets, sum(score) AS score, sum(size) AS size
+		,avg(tick)::int AS avgtick
+	FROM planet_ranks WHERE rank <= 60
+	GROUP BY alliance
+)
+SELECT aid,alliance,a.relationship,s.members,r.planets
+	,s.score, r.score AS topscore, s.size, r.size AS topsize
+	,r.resources,r.hidden
+	,(s.score + (resources / 300) + (hidden / 100))::bigint AS nscore
+	,nscore2, nscore3, avgtick
+FROM alliances a
+	JOIN top_planets r USING (alliance)
+	LEFT OUTER JOIN (SELECT aid,score,size,members FROM alliance_stats
+		WHERE tick = (SELECT max(tick) FROM alliance_stats)) s USING (aid)
+;
+
+CREATE OR REPLACE VIEW defcalls AS
+SELECT c.id,c.member AS uid, c.landing_tick, covered, open
+	,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 ON c.id = i.call
+	LEFT OUTER JOIN current_planet_stats p2 USING (pid)
+	LEFT OUTER JOIN users dc ON c.dc = dc.uid
+GROUP BY c.id,c.member,dc.username, c.landing_tick, covered, open;
+
+CREATE OR REPLACE VIEW full_defcalls AS
+SELECT id,covered,open,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 id, x,y,z,pid,landing_tick,dc,curreta,defprio,c.race,amount,c.eta,shiptype,c.alliance,attackers, covered, open
+;
+
+DROP AGGREGATE array_accum(anyelement);
diff --git a/lib/NDWeb/Auth/User.pm b/lib/NDWeb/Auth/User.pm
index c19ba2b..0e49a2e 100644
--- a/lib/NDWeb/Auth/User.pm
+++ b/lib/NDWeb/Auth/User.pm
@@ -42,7 +42,7 @@ sub load {
 	}
 
 	($self->{planet},$self->{username},$self->{css}) = $dbh->selectrow_array(q{
-		SELECT planet,username,css FROM users WHERE uid = ?
+		SELECT pid,username,css FROM users WHERE uid = ?
 		},undef,$self->{id}) or die $dbh->errstr;
 
 	return $self;
diff --git a/lib/NDWeb/Controller/Alliances.pm b/lib/NDWeb/Controller/Alliances.pm
index c2d40ff..efec3dd 100644
--- a/lib/NDWeb/Controller/Alliances.pm
+++ b/lib/NDWeb/Controller/Alliances.pm
@@ -40,7 +40,7 @@ sub list : Local {
 		$order = "score DESC";
 	}
 	my $query = $dbh->prepare(q{
-		SELECT DISTINCT a.id,name,COALESCE(s.score,SUM(p.score)) AS score
+		SELECT aid AS id,alliance AS name,COALESCE(s.score,SUM(p.score)) AS score
 			,COALESCE(s.size,SUM(p.size)) AS size,s.members,count(p.score) AS kmem
 			,COALESCE(SUM(p.score),-1) AS kscore
 			,COALESCE(SUM(p.size),-1) AS ksize
@@ -54,9 +54,9 @@ sub list : Local {
 			,COALESCE(AVG(p.value)::int,-1) AS kvalavg
 		FROM alliances a
 			LEFT OUTER JOIN (SELECT * FROM alliance_stats
-				WHERE tick = (SELECT max(tick) FROM alliance_stats)) s ON s.id = a.id
-			LEFT OUTER JOIN current_planet_stats p ON p.alliance_id = a.id
-		GROUP BY a.id,a.name,s.score,s.size,s.members
+				WHERE tick = (SELECT max(tick) FROM alliance_stats)) s USING (aid)
+			LEFT OUTER JOIN current_planet_stats p USING (alliance,aid)
+		GROUP BY aid,alliance,s.score,s.size,s.members
 		HAVING s.score IS NOT NULL OR count(p.score) > 0
 		ORDER BY
 		} . $order);
@@ -68,7 +68,7 @@ sub edit : Local {
 	my ( $self, $c, $id, $order ) = @_;
 	my $dbh = $c->model;
 
-	my $query = $dbh->prepare(q{SELECT id,name, relationship FROM alliances WHERE id = ?});
+	my $query = $dbh->prepare(q{SELECT aid AS id,alliance AS name, relationship FROM alliances WHERE aid = ?});
 	my $a = $dbh->selectrow_hashref($query,undef,$id);
 	$c->stash(a => $a);
 
@@ -83,13 +83,13 @@ sub edit : Local {
 	$order .= ' DESC' if $order eq 'hit_us';
 
 	my $members = $dbh->prepare(q{
-		SELECT id, coords(x,y,z), nick, ruler, planet, race, size, score, value, xp
+		SELECT pid AS id, coords(x,y,z), nick, ruler, planet, race, size, score, value, xp
 			,planet_status,hit_us, sizerank, scorerank, valuerank, xprank
 		FROM current_planet_stats p
-		WHERE p.alliance_id = ?
+		WHERE p.alliance = ?
 		ORDER BY
 		} . $order);
-	$members->execute($a->{id});
+	$members->execute($a->{name});
 	$c->stash(members => $members->fetchall_arrayref({}) );
 
 	my $ticks = $c->req->param('ticks') || 48;
@@ -98,14 +98,12 @@ sub edit : Local {
 	$query = $dbh->prepare(intelquery q{
 			o.alliance AS oalliance ,coords(o.x,o.y,o.z) AS ocoords, i.sender
 			,t.alliance AS talliance,coords(t.x,t.y,t.z) AS tcoords, i.target
-		},q{NOT ingal AND (o.alliance_id = $1 OR t.alliance_id = $1)
+		},q{NOT ingal AND (o.alliance = $1 OR t.alliance = $1)
 			AND (i.mission = 'Defend' OR i.mission = 'AllyDef')
-			AND ((( t.alliance_id != o.alliance_id OR t.alliance_id IS NULL OR o.alliance_id IS NULL)))
-			AND i.sender NOT IN (SELECT planet FROM users u NATURAL JOIN groupmembers gm WHERE gid = 8 AND planet IS NOT NULL)
-			AND NOT (i.back IS NOT NULL AND i.back = i.tick + 4)
+			AND COALESCE( t.alliance != o.alliance, TRUE)
 			AND i.tick > (tick() - $2)
 		});
-	$query->execute($a->{id}, $ticks);
+	$query->execute($a->{name}, $ticks);
 	$c->stash(intel => $query->fetchall_arrayref({}) );
 }
 
@@ -113,35 +111,38 @@ sub postallianceupdate : Local {
 	my ( $self, $c, $id, $order ) = @_;
 	my $dbh = $c->model;
 
+	my $query = $dbh->prepare(q{SELECT aid,alliance, relationship FROM alliances WHERE aid = ?});
+	my $a = $dbh->selectrow_hashref($query,undef,$id);
+
 	$dbh->begin_work;
 	my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
 		(SELECT ftid FROM users WHERE uid = $1),$1,$2)
 		});
 	if ($c->req->param('crelationship')){
 		my $value = $c->req->param('relationship');
-		$dbh->do(q{UPDATE alliances SET relationship = ? WHERE id =?}
+		$dbh->do(q{UPDATE alliances SET relationship = ? WHERE aid =?}
 			,undef,$value,$id);
 		$log->execute($c->user->id
-			,"HC set alliance: $id relationship: $value");
+			,"HC set alliance: $a->{alliance} ($id) relationship: $value");
 	}
 	my $coords = $c->req->param('coords');
-	my $findplanet = $dbh->prepare(q{SELECT id FROM current_planet_stats
+	my $findplanet = $dbh->prepare(q{SELECT pid FROM current_planet_stats
 		WHERE x = ? AND y = ? AND z = ?});
 	my $addplanet = $dbh->prepare(q{
-		UPDATE planets SET alliance_id = $2, nick = coalesce($3,nick)
-		WHERE id = $1;
+		UPDATE planets SET alliance = $2, nick = coalesce($3,nick)
+		WHERE pid = $1;
 		});
 	my $text = '';
 	while ($coords =~ m/(\d+):(\d+):(\d+)(?:\s+nick=(\S+))?/g){
 		my ($planet) = $dbh->selectrow_array($findplanet,undef,$1,$2,$3);
-		$addplanet->execute($planet,$id,$4);
+		$addplanet->execute($planet,$a->{alliance},$4);
 		my $nick = '';
 		$nick = "(nick $4)" if defined $4;
 		$text .= "($planet) $1:$2:$3 $nick\n";
 	}
 	if ($text){
 		$log->execute($c->user->id
-			,"HC added the following planets to alliance $id:\n $text");
+			,"HC added the following planets to alliance $a->{alliance} ($id):\n $text");
 	}
 	$dbh->commit;
 
@@ -152,20 +153,23 @@ sub postremoveallplanets : Local {
 	my ( $self, $c, $id, $order ) = @_;
 	my $dbh = $c->model;
 
+	my $query = $dbh->prepare(q{SELECT aid,alliance, relationship FROM alliances WHERE aid = ?});
+	my $a = $dbh->selectrow_hashref($query,undef,$id);
+
 	$dbh->begin_work;
 	my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
 		(SELECT ftid FROM users WHERE uid = $1),$1,$2)
 		});
-	my ($coords) = $dbh->selectrow_array(q{SELECT CONCAT(coords(x,y,z) || ' ') 
-			FROM current_planet_stats where alliance_id = $1
-		},undef,$id);
+	my ($coords) = $dbh->selectrow_array(q{SELECT array_to_string(array_agg(coords(x,y,z)),' ')
+			FROM current_planet_stats where alliance = $1
+		},undef,$a->{alliance});
 	my $removeplanets = $dbh->prepare(q{
-		UPDATE planets SET alliance_id = NULL
-		WHERE alliance_id = $1;
+		UPDATE planets SET alliance = NULL
+		WHERE alliance = $1;
 	});
-	$removeplanets->execute($id);
+	$removeplanets->execute($a->{alliance});
 	$log->execute($c->user->id
-		,"HC cleaned alliance $id :\n\n$coords");
+		,"HC cleaned alliance $a->{alliance} (id) :\n\n$coords");
 	$dbh->commit;
 
 	$c->res->redirect($c->uri_for('edit',$id));
@@ -210,48 +214,17 @@ sub resources : Local {
 		$order = "resplanet DESC";
 	}
 
-	my $query = $dbh->prepare(qq{
-		SELECT a.id,a.name,a.relationship,s.members,s.score,s.size
-		,r.resources,r.hidden,r.planets
-		,(resources/planets)::bigint AS resplanet
-		,(hidden/planets)::bigint AS hidplanet
-		,(score + (resources / 300) + (hidden / 100))::bigint AS nscore
-		,(SELECT sum(score)::bigint FROM (
-SELECT score + (metal+crystal+eonium)/300 + hidden/100 AS score
-FROM current_planet_stats p
-	JOIN current_planet_scans ps ON p.id = ps.planet
-WHERE alliance_id = r.id
-ORDER BY score DESC
-LIMIT 60) a
-		) AS nscore2
-		,(SELECT sum(score)::bigint FROM (
-SELECT score + (metal+crystal+eonium)/300 + hidden/100 + (endtick()-tick())*(
-		250*size + COALESCE(metal_ref + crystal_ref + eonium_ref,7)* 1000
-		+ CASE extraction WHEN 0 THEN 3000 WHEN 1 THEN 11500 ELSE COALESCE(extraction,3)*3000*3 END
-	)*(1.35+0.005*COALESCE(fincents,20))/100 AS score
-FROM current_planet_stats p
-	JOIN current_planet_scans ps ON p.id = ps.planet
-	LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
-WHERE alliance_id = r.id
-ORDER BY score DESC
-LIMIT 60) a
-		) AS nscore3
-		FROM (SELECT alliance_id AS id,sum(metal+crystal+eonium) AS resources
-				, sum(hidden) AS hidden, count(*) AS planets
-				FROM planets p join current_planet_scans c ON p.id = c.planet
-				GROUP by alliance_id
-			) r
-			NATURAL JOIN alliances a
-			LEFT OUTER JOIN (SELECT *,LEAST(members,60) AS scoremem FROM alliance_stats
-				WHERE tick = (SELECT max(tick) FROM alliance_stats)) s ON a.id = s.id
-		ORDER BY $order
-		});
+	my $query = $dbh->prepare(q{
+SELECT aid AS id,alliance AS name,relationship,members,score,size
+	,resources,hidden,planets
+	,(resources/planets)::bigint AS resplanet
+	,(hidden/planets)::bigint AS hidplanet
+	, nscore, nscore2, nscore3
+FROM alliance_resources
+ORDER BY } . $order
+	);
 	$query->execute;
-	my @alliances;
-	while (my $alliance = $query->fetchrow_hashref){
-		push @alliances,$alliance;
-	}
-	$c->stash(alliances => \@alliances);
+	$c->stash(alliances => $query->fetchall_arrayref({}));
 }
 
 
diff --git a/lib/NDWeb/Controller/Calls.pm b/lib/NDWeb/Controller/Calls.pm
index 7edc10b..5c3298a 100644
--- a/lib/NDWeb/Controller/Calls.pm
+++ b/lib/NDWeb/Controller/Calls.pm
@@ -34,7 +34,7 @@ sub list : Local {
 	my ( $self, $c, $type ) = @_;
 	my $dbh = $c->model;
 
-	my $where = q{open AND c.landing_tick-6 > tick()};
+	my $where = q{open AND landing_tick-6 > tick()};
 	my $order = q{landing_tick DESC, defprio DESC};
 	if (defined $type){
 		if ($type eq 'covered'){
@@ -44,7 +44,7 @@ sub list : Local {
 		}elsif ($type eq 'uncovered'){
 			$where = 'not covered';
 		}elsif ($type eq 'recent'){
-			$where = q{c.landing_tick > tick()};
+			$where = q{landing_tick > tick()};
 			$order = q{x,y,z};
 		}
 	}
@@ -53,35 +53,10 @@ sub list : Local {
 	$c->stash(maxprio => $dbh->selectrow_array($pointlimits,undef,'DEFMAXPRIO'));
 
 	my $query = $dbh->prepare(qq{
-		SELECT id,coords(x,y,z),planet,landing_tick,dc,curreta,fleets
-			,defprio
-			,array_accum(COALESCE(race::text,'')) AS race
-			,array_accum(COALESCE(amount,0)) AS amount
-			,array_accum(COALESCE(eta,0)) AS eta
-			,array_accum(COALESCE(shiptype,'')) AS shiptype
-			,array_accum(COALESCE(alliance,'?')) AS alliance
-			,array_accum(coords) AS attackers
-		FROM (SELECT c.id, p.x,p.y,p.z,p.id AS planet, defprio
-			,u.defense_points, u.attack_points, c.landing_tick
-			,dc.username AS dc, (c.landing_tick - tick()) AS curreta
-			,p2.race, i.amount, i.eta, i.shiptype, p2.alliance
-			,coords(p2.x,p2.y,p2.z), COUNT(DISTINCT f.fid) AS fleets
-			FROM calls c
-				JOIN users_defprio u ON c.member = u.uid
-				JOIN current_planet_stats p ON u.planet = p.id
-				LEFT OUTER JOIN incomings i ON i.call = c.id
-				LEFT OUTER JOIN current_planet_stats p2 ON i.sender = p2.id
-				LEFT OUTER JOIN users dc ON c.dc = dc.uid
-				LEFT OUTER JOIN launch_confirmations f ON f.target = u.planet
-					AND f.landing_tick = c.landing_tick AND f.back = f.landing_tick + f.eta - 1
-			WHERE $where
-			GROUP BY c.id, p.x,p.y,p.z,p.id, defprio, c.landing_tick
-				,u.defense_points,u.attack_points,dc.username
-				,p2.race,i.amount,i.eta,i.shiptype,p2.alliance,p2.x,p2.y,p2.z
-			) c
-		GROUP BY id, x,y,z,planet,landing_tick, defense_points, attack_points
-			,dc,curreta,fleets, defprio
-		ORDER BY $order
+SELECT *, pid AS planet, coords(x,y,z)
+FROM full_defcalls
+WHERE $where
+ORDER BY $order
 		});
 	$query->execute;
 	$c->stash(calls => $query->fetchall_arrayref({}));
@@ -105,34 +80,36 @@ sub edit : Local {
 
 	my $outgoings = $dbh->prepare(q{ 
 (
-	SELECT DISTINCT ON (mission,name) fid,mission,name,tick, NULL AS eta
-		,amount, NULL AS coords, planet, NULL AS back, NULL AS recalled
+	SELECT DISTINCT ON (mission,name) 1 AS type, fid,mission,name,tick, NULL AS eta
+		,amount, NULL AS coords, pid AS planet, NULL AS back, NULL AS recalled
 	FROM fleets f
-	WHERE planet = $1 AND tick <= $2 AND (
+	WHERE pid = $1 AND tick <= $2 AND (
 			mission = 'Full fleet'
 			OR fid IN (SELECT fid FROM fleet_scans)
 		) AND (
 			mission = 'Full fleet'
 			OR tick >= $2 - 12
 		)
-	ORDER BY mission,name,tick DESC
 ) UNION (
-	SELECT fid,mission,name,landing_tick AS tick, eta, amount
-		, coords(x,y,z), t.id AS planet, back
+	SELECT 2 AS type, MAX(fid) AS fid,mission,name,landing_tick AS tick, eta, amount
+		, coords(x,y,z), pid AS planet, back
 		, (back <> landing_tick + eta - 1) AS recalled
-	FROM fleets f
-		JOIN launch_confirmations USING (fid)
-	LEFT OUTER JOIN current_planet_stats t ON target = t.id
-	WHERE f.planet = $1 AND back >= $2 AND landing_tick - eta - 12 < $2
+	FROM launch_confirmations
+		JOIN (
+			SELECT fid,amount,name,mission FROM fleets WHERE pid = $1
+		) f  USING (fid)
+		LEFT OUTER JOIN current_planet_stats t USING (pid)
+	WHERE back >= $2 AND landing_tick - eta - 12 < $2
+	GROUP BY mission,name,landing_tick,eta,amount,back,x,y,z,pid
 ) UNION (
 	SELECT DISTINCT ON (tick,x,y,z,mission,name,amount)
-		NULL as fid, i.mission, i.name, i.tick,eta
-		, i.amount, coords(x,y,z), t.id AS planet, back, NULL AS recalled
+		3 AS type, NULL as fid, i.mission, i.name, i.tick,eta
+		, i.amount, coords(x,y,z), t.pid AS planet, back, NULL AS recalled
 	FROM intel i
-	LEFT OUTER JOIN current_planet_stats t ON i.target = t.id
+	LEFT OUTER JOIN current_planet_stats t ON i.target = t.pid
 	WHERE uid = -1 AND i.sender = $1 AND i.tick > $2 - 14 AND i.tick < $2 + 14
 	ORDER BY i.tick,x,y,z,mission,name,amount,back
-)
+) ORDER BY type, mission,name,tick DESC
 	});
 	my $ships = $dbh->prepare(q{SELECT ship,amount FROM fleet_ships
 		WHERE fid = ? ORDER BY num
@@ -167,14 +144,18 @@ SELECT ship,amount from ships_home WHERE planet = $1 AND tick = $2
 
 	$c->stash(fleets => \@fleets);
 
-	my $defenders = $dbh->prepare(q{ 
-SELECT DISTINCT ON (x,y,z,s.id,name,amount,back) fid,mission, name, eta
-	, amount, coords(x,y,z) AS coords, landing_tick AS tick, f.planet
+	my $defenders = $dbh->prepare(q{
+SELECT DISTINCT ON (x,y,z,pid,name,amount,back) fid,mission, name, eta
+	, amount, coords(x,y,z) AS coords, landing_tick AS tick, pid AS planet
 	,back, (back <> landing_tick + eta - 1) AS recalled
-FROM launch_confirmations lc
-	JOIN fleets f USING (fid)
-	LEFT OUTER JOIN current_planet_stats s ON f.planet = s.id
-WHERE target = ? AND landing_tick = ? AND mission = 'Defend'
+FROM fleets f
+	LEFT OUTER JOIN current_planet_stats s USING (pid)
+	JOIN (
+		SELECT fid,back,eta,landing_tick
+		FROM launch_confirmations
+		WHERE pid = $1 AND landing_tick = $2
+	) lc USING (fid)
+WHERE mission = 'Defend'
 ORDER BY x,y,z
 	});
 
@@ -195,9 +176,9 @@ ORDER BY x,y,z
 
 	my $attackers = $dbh->prepare(q{
 		SELECT coords(p.x,p.y,p.z), p.planet_status, p.race,i.eta,i.amount
-			,i.fleet,i.shiptype,p.relationship,p.alliance,i.id,p.id AS planet
+			,i.fleet,i.shiptype,p.relationship,p.alliance,i.id,pid AS planet
 		FROM incomings i
-			JOIN current_planet_stats p ON i.sender = p.id
+			JOIN current_planet_stats p USING (pid)
 		WHERE i.call = ?
 		ORDER BY p.x,p.y,p.z
 	});
@@ -233,13 +214,14 @@ sub defleeches : Local {
 	my $query = $dbh->prepare(q{SELECT username,defense_points,count(id) AS calls
 		, SUM(fleets) AS fleets, SUM(recalled) AS recalled
 		,count(NULLIF(fleets,0)) AS defended_calls
-		FROM (SELECT username,defense_points,c.id,count(f.target) AS fleets
+		FROM (SELECT username,defense_points,c.id,count(f.back) AS fleets
 			, count(NULLIF(f.landing_tick + f.eta -1 = f.back,TRUE)) AS recalled
 			FROM users u JOIN calls c ON c.member = u.uid
 				LEFT OUTER JOIN (
-					SELECT * FROM launch_confirmations JOIN fleets USING (fid)
+					SELECT lc.pid,landing_tick,eta,back
+					FROM launch_confirmations lc JOIN fleets f USING (fid)
 					WHERE mission = 'Defend'
-				) f ON u.planet = f.target AND c.landing_tick = f.landing_tick
+				) f USING (pid,landing_tick)
 			GROUP BY username,defense_points,c.id
 		) d
 		GROUP BY username,defense_points ORDER BY fleets DESC, defense_points
@@ -359,11 +341,11 @@ sub findCall : Private {
 	my $query = $dbh->prepare(q{
 		SELECT c.id, coords(p.x,p.y,p.z), c.landing_tick, c.info, covered
 			,open, dc.username AS dc, u.defense_points,c.member AS uid
-			,u.planet, u.username AS member, u.sms,c.ftid,calc
+			,p.pid AS planet, u.username AS member, u.sms,c.ftid,calc
 		FROM calls c 
 		JOIN users u ON c.member = u.uid
+		JOIN current_planet_stats p USING (pid)
 		LEFT OUTER JOIN users dc ON c.dc = dc.uid
-		JOIN current_planet_stats p ON u.planet = p.id
 		WHERE c.id = ?
 		});
 	$call = $dbh->selectrow_hashref($query,undef,$call);
diff --git a/lib/NDWeb/Controller/CovOp.pm b/lib/NDWeb/Controller/CovOp.pm
index ed9d2c8..7e3d2e5 100644
--- a/lib/NDWeb/Controller/CovOp.pm
+++ b/lib/NDWeb/Controller/CovOp.pm
@@ -52,7 +52,7 @@ sub distwhores : Local {
 sub marktarget : Local {
 	my ( $self, $c, $target ) = @_;
 	my $dbh = $c->model;
-	my $update = $dbh->prepare(q{INSERT INTO covop_attacks (uid,id,tick) VALUES(?,?,tick())});
+	my $update = $dbh->prepare(q{INSERT INTO covop_attacks (uid,pid,tick) VALUES(?,?,tick())});
 	eval{
 		$update->execute($c->user->id,$target);
 	};
@@ -65,11 +65,11 @@ sub list : Private {
 
 	my $query = $dbh->prepare(q{
 	SELECT * FROM (
-		SELECT *
+		SELECT *, pid AS id
 			,(2*pvalue::float/cvalue) :: Numeric(4,1) AS max_bank_hack
 			,max_bank_hack(metal,crystal,eonium,pvalue,cvalue,5) AS hack5
 			,max_bank_hack(metal,crystal,eonium,pvalue,cvalue,13) AS hack13
-		FROM (SELECT p.id,coords(x,y,z),x,y,z,size
+		FROM (SELECT pid,coords(x,y,z),x,y,z,size
 			,metal + metal_roids * (tick()-ps.tick) * 125 AS metal
 			,crystal + crystal_roids * (tick()-ps.tick) * 125 AS crystal
 			,eonium + eonium_roids * (tick()-ps.tick) * 125 AS eonium
@@ -79,12 +79,12 @@ sub list : Private {
 			, planet_status, relationship,gov,ps.tick AS pstick, ds.tick AS dstick
 			, p.value AS pvalue, c.value AS cvalue
 			FROM current_planet_stats p
-				LEFT OUTER JOIN current_planet_scans ps ON p.id = ps.planet
-				LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
-				CROSS JOIN (SELECT value FROM current_planet_stats WHERE id = $1) c
+				LEFT OUTER JOIN current_planet_scans ps USING (pid)
+				LEFT OUTER JOIN current_development_scans ds USING (pid)
+				CROSS JOIN (SELECT value FROM current_planet_stats WHERE pid = $1) c
 			) AS foo
-			LEFT OUTER JOIN (SELECT id,max(tick) AS lastcovop FROM covop_attacks
-				GROUP BY id) co USING (id)
+			LEFT OUTER JOIN (SELECT pid,max(tick) AS lastcovop FROM covop_attacks
+				GROUP BY pid) co USING (pid)
 		WHERE (metal IS NOT NULL OR distorters IS NOT NULL)
 			AND (NOT planet_status IN ('Friendly','NAP'))
 			AND  (relationship IS NULL OR NOT relationship IN ('Friendly','NAP'))
diff --git a/lib/NDWeb/Controller/Graphs.pm b/lib/NDWeb/Controller/Graphs.pm
index d2ea8df..9bac96a 100644
--- a/lib/NDWeb/Controller/Graphs.pm
+++ b/lib/NDWeb/Controller/Graphs.pm
@@ -54,7 +54,7 @@ sub planetranks : Local {
 	my $query = $dbh->prepare(q{SELECT tick,-scorerank AS score,-sizerank AS size
 		,-valuerank AS value,-xprank AS xp
 		FROM planets NATUral JOIN planet_stats
-		WHERE id = $1 ORDER BY tick ASC
+		WHERE pid = $1 ORDER BY tick ASC
 		});
 	$query->execute($planet);
 	$c->stash(query => $query);
@@ -69,7 +69,7 @@ sub planetstats : Local {
 	$c->stash->{settings}->{y_min_value} = '0';
 	my $query = $dbh->prepare(q{SELECT tick,score,size,value,xp*60 AS "xp*60"
 		FROM planets NATURAL JOIN planet_stats
-		WHERE id = $1 ORDER BY tick ASC
+		WHERE pid = $1 ORDER BY tick ASC
 		});
 	$query->execute($planet);
 	$c->stash(query => $query);
@@ -122,9 +122,9 @@ sub planetvsnd : Local {
 	my $query = $dbh->prepare(q{SELECT a.tick,a.score/LEAST(members,60) AS NDscore
 		,a.size/members as NDsize,memsize, memscore
 		FROM (SELECT tick,size AS memsize,score AS memscore
-			FROM planets p JOIN planet_stats ps USING (id) WHERE p.id = $1) p
+			FROM planets p JOIN planet_stats ps USING (pid) WHERE pid = $1) p
 		JOIN alliance_stats a ON a.tick = p.tick
-		WHERE a.id = 1 ORDER BY tick
+		WHERE aid = 1 ORDER BY tick
 		});
 	$query->execute($planet);
 	$c->stash(query => $query);
@@ -140,12 +140,14 @@ sub alliancevsintel : Local {
 	$c->stash->{settings}->{y2_label} = 'score';
 
 	my $query = $dbh->prepare(q{SELECT a.tick,a.score,a.size,memsize, memscore
-		FROM (SELECT tick,SUM(size) AS memsize,SUM(score) AS memscore
-			FROM planets p JOIN planet_stats ps USING (id)
-			WHERE p.alliance_id = $1 GROUP BY tick) p
-			JOIN alliance_stats a ON a.tick = p.tick
-		WHERE a.id = $1
-			AND a.tick > (SELECT max(tick) - 50 FROM alliance_stats)
+		FROM (SELECT tick,aid,SUM(size) AS memsize,SUM(score) AS memscore
+			FROM alliances a
+				JOIN planets p USING (alliance)
+				JOIN planet_stats ps USING (pid)
+			GROUP BY tick,aid ) p
+			JOIN alliance_stats a USING (aid,tick)
+		WHERE aid = $1
+			AND tick > (SELECT max(tick) - 50 FROM alliance_stats)
 		ORDER BY tick
 		});
 	$query->execute($alliance);
@@ -162,12 +164,14 @@ sub avgalliancevsintel : Local {
 
 	my $query = $dbh->prepare(q{SELECT a.tick,a.score/LEAST(members,60) AS score
 		,a.size/members AS size,memsize, memscore
-		FROM (SELECT tick,AVG(size) AS memsize,AVG(score) AS memscore
-			FROM planets p JOIN planet_stats ps USING (id) WHERE p.alliance_id = $1
-			GROUP BY tick) p
-		JOIN alliance_stats a ON a.tick = p.tick
-		WHERE a.id = $1
-			AND a.tick > (SELECT max(tick) - 50 FROM alliance_stats)
+		FROM (SELECT tick,aid,AVG(size) AS memsize,AVG(score) AS memscore
+			FROM alliances a
+				JOIN planets p USING (alliance)
+				JOIN planet_stats ps USING (pid)
+			GROUP BY tick,aid) p
+		JOIN alliance_stats a USING (aid,tick)
+		WHERE aid = $1
+			AND tick > (SELECT max(tick) - 50 FROM alliance_stats)
 		ORDER BY tick
 		});
 	$query->execute($alliance);
diff --git a/lib/NDWeb/Controller/Intel.pm b/lib/NDWeb/Controller/Intel.pm
index bbdf0f7..388204b 100644
--- a/lib/NDWeb/Controller/Intel.pm
+++ b/lib/NDWeb/Controller/Intel.pm
@@ -35,10 +35,8 @@ sub index :Path : Args(0) {
 			o.alliance AS oalliance ,coords(o.x,o.y,o.z) AS ocoords, i.sender
 			,t.alliance AS talliance,coords(t.x,t.y,t.z) AS tcoords, i.target
 		},q{not ingal
-			AND ((( t.alliance_id != o.alliance_id OR t.alliance_id IS NULL OR o.alliance_id IS NULL) AND (i.mission = 'Defend' OR i.mission = 'AllyDef' ))
-			OR ( t.alliance_id = o.alliance_id AND i.mission = 'Attack'))
-			AND i.sender NOT IN (SELECT planet FROM users u NATURAL JOIN groupmembers gm WHERE gid = 8 AND planet IS NOT NULL)
-			AND NOT (i.back IS NOT NULL AND i.back = i.tick + 4)
+			AND ((COALESCE( t.alliance != o.alliance,TRUE) AND (i.mission = 'Defend' OR i.mission = 'AllyDef' ))
+				OR ( t.alliance = o.alliance AND i.mission = 'Attack'))
 			AND i.tick > (tick() - $1)
 		});
 	$query->execute($ticks);
@@ -50,7 +48,7 @@ sub index :Path : Args(0) {
 		while ($coords =~ m/(\d+:\d+:\d+)/g){
 			push @coords,$1;
 		}
-		my $planets = $dbh->prepare(q{SELECT id,coords(x,y,z), alliance, nick
+		my $planets = $dbh->prepare(q{SELECT pid AS id,coords(x,y,z), alliance, nick
 			FROM current_planet_stats p
 			WHERE coords(x,y,z) = ANY($1)
 			ORDER BY alliance, p.x, p.y, p.z
@@ -71,8 +69,8 @@ sub planet : Local {
 	my $ticks = $c->req->param('ticks') || 48;
 	$c->stash(showticks => $ticks);
 
-	my $query = $dbh->prepare(q{SELECT id,coords(x,y,z),alliance,nick,channel
-		FROM current_planet_stats WHERE channel ILIKE ?
+	my $query = $dbh->prepare(q{SELECT pid AS id,coords(x,y,z),alliance,nick,channel
+		FROM current_planet_stats WHERE channel = $1
 		ORDER BY alliance,x,y,z
 		});
 	$query->execute($p->{channel});
@@ -102,16 +100,16 @@ sub channels : Local {
 	my ( $self, $c, $order ) = @_;
 	my $dbh = $c->model;
 
-	if ($order ~~ /(alliance|channel)/){
+	if ($order ~~ /(alliance)/){
 		$order = "lower($1) ASC";
 	}elsif ($order ~~ /(coords)/){
 		$order = "x,y,z";
 	}else{
-		$order = 'lower(channel)';
+		$order = 'channel';
 	}
 
 	my $query = $dbh->prepare(q{
-SELECT id,coords(x,y,z),nick,channel,alliance FROM current_planet_stats
+SELECT pid AS id,coords(x,y,z),nick,channel,alliance FROM current_planet_stats
 WHERE channel <> '' and channel IS NOT NULL
 ORDER BY } . $order
 	);
@@ -142,33 +140,33 @@ sub postplanetupdate : Local {
 		});
 	if ($c->req->param('cnick')){
 		my $value = html_escape $c->req->param('nick');
-		$dbh->do(q{UPDATE planets SET nick = ? WHERE id =?}
+		$dbh->do(q{UPDATE planets SET nick = ? WHERE pid =?}
 			,undef,$value,$p->{id});
 		$log->execute($c->user->id,$p->{ftid},"Set nick to: $value");
 	}
 	if ($c->req->param('cchannel')){
 		my $value = html_escape $c->req->param('channel');
-		$dbh->do(q{UPDATE planets SET channel = ? WHERE id =?}
+		$dbh->do(q{UPDATE planets SET channel = ? WHERE pid =?}
 			,undef,$value,$p->{id});
 		$log->execute($c->user->id,$p->{ftid},"Set channel to: $value");
 	}
 	if ($c->req->param('cstatus')){
 		my $value = $c->req->param('status');
-		$dbh->do(q{UPDATE planets SET planet_status = ? WHERE id =?}
+		$dbh->do(q{UPDATE planets SET planet_status = ? WHERE pid =?}
 			,undef,$value,$p->{id});
 		$log->execute($c->user->id,$p->{ftid},"Set planet_status to: $value");
 	}
 	if ($c->req->param('cgov')){
 		my $value = $c->req->param('gov');
-		$dbh->do(q{UPDATE planets SET gov = ? WHERE id =?}
+		$dbh->do(q{UPDATE planets SET gov = ? WHERE pid =?}
 			,undef,$value,$p->{id});
 		$log->execute($c->user->id,$p->{ftid},"Set gov to: $value");
 	}
 	if ($c->req->param('calliance')){
 		my $value = $c->req->param('alliance');
-		$dbh->do(q{UPDATE planets SET alliance_id = NULLIF(?,-1) WHERE id =?}
+		$dbh->do(q{UPDATE planets SET alliance = NULLIF(?,'') WHERE pid = ?}
 			,undef,$value,$p->{id});
-		$log->execute($c->user->id,$p->{ftid},"Set alliance_id to: $value");
+		$log->execute($c->user->id,$p->{ftid},"Set alliance to: $value");
 	}
 	$dbh->commit;
 
@@ -188,7 +186,7 @@ sub find : Local {
 			,undef,$1,$2,$3,$4);
 		$c->res->redirect($c->uri_for('planet',$planet));
 	}else{
-		my $query = $dbh->prepare(q{SELECT id,coords(x,y,z),nick
+		my $query = $dbh->prepare(q{SELECT pid AS id,coords(x,y,z),nick
 			FROM current_planet_stats p
 			WHERE nick ilike $1
 		});
@@ -206,10 +204,10 @@ sub findPlanet : Private {
 	my ( $self, $c, $id ) = @_;
 	my $dbh = $c->model;
 
-	my $query = $dbh->prepare(q{SELECT x,y,z,id, nick, alliance,alliance_id
+	my $query = $dbh->prepare(q{SELECT x,y,z,pid AS id, nick, alliance,aid
 		, planet_status,channel,ftid,gov
 		FROM current_planet_stats
-		WHERE id = $1
+		WHERE pid = $1
 		});
 	$query->execute($id);
 	$c->stash(p => $query->fetchrow_hashref);
@@ -320,7 +318,7 @@ sub naps : Local {
 	my ( $self, $c ) = @_;
 	my $dbh = $c->model;
 
-	my $query = $dbh->prepare(q{SELECT p.id,coords(x,y,z)
+	my $query = $dbh->prepare(q{SELECT pid AS id,coords(x,y,z)
 		,ruler, p.planet,race, size, score, value
 		, xp, sizerank, scorerank, valuerank, xprank, p.value - p.size*200 
 			- COALESCE(ps.metal+ps.crystal+ps.eonium,0)/150
@@ -329,8 +327,8 @@ sub naps : Local {
 		,(metal+crystal+eonium)/100 AS resvalue, planet_status,hit_us
 		, alliance,relationship,nick
 		FROM current_planet_stats p
-			LEFT OUTER JOIN current_planet_scans ps ON p.id = ps.planet
-			LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
+			LEFT OUTER JOIN current_planet_scans ps USING (pid)
+			LEFT OUTER JOIN current_development_scans ds USING (pid)
 		WHERE planet_status IN ('Friendly','NAP') order by x,y,z asc
 		});
 	$query->execute;
diff --git a/lib/NDWeb/Controller/JSRPC.pm b/lib/NDWeb/Controller/JSRPC.pm
index 6e21a94..80912c1 100644
--- a/lib/NDWeb/Controller/JSRPC.pm
+++ b/lib/NDWeb/Controller/JSRPC.pm
@@ -41,12 +41,12 @@ sub update : Local {
 		my ($timestamp) = $dbh->selectrow_array(q{SELECT MAX(modified)::timestamp AS modified
 			FROM raid_targets WHERE raid = $1},undef,$raid->{id});
 		$c->stash(timestamp => $timestamp);
-		$targets = $dbh->prepare(q{SELECT r.id,r.planet FROM raid_targets r
+		$targets = $dbh->prepare(q{SELECT r.id,r.pid FROM raid_targets r
 			WHERE r.raid = ? AND modified > ?
 		});
 		$targets->execute($raid->{id},$from);
 	}elsif($target){
-		$targets = $dbh->prepare(q{SELECT r.id,r.planet FROM raid_targets r
+		$targets = $dbh->prepare(q{SELECT r.id,r.pid FROM raid_targets r
 			WHERE r.raid = $1 AND r.id = $2
 		});
 		$targets->execute($raid->{id},$target);
diff --git a/lib/NDWeb/Controller/Members.pm b/lib/NDWeb/Controller/Members.pm
index 354bdbe..ae1e8e6 100644
--- a/lib/NDWeb/Controller/Members.pm
+++ b/lib/NDWeb/Controller/Members.pm
@@ -30,7 +30,7 @@ sub index : Path : Args(0) {
 
 	$c->stash(error => $c->flash->{error});
 
-	$c->stash(u => $dbh->selectrow_hashref(q{SELECT planet,defense_points
+	$c->stash(u => $dbh->selectrow_hashref(q{SELECT pid AS planet,defense_points
 			,attack_points,scan_points,humor_points
 			, (attack_points+defense_points+scan_points/20)::NUMERIC(5,1) as total_points
 			, sms,rank,hostmask,call_if_needed,sms_note,defprio
@@ -38,13 +38,13 @@ sub index : Path : Args(0) {
 			},undef,$c->user->id)
 	);
 
-	$c->stash(groups => $dbh->selectrow_array(q{SELECT array_accum(groupname)
+	$c->stash(groups => $dbh->selectrow_array(q{SELECT array_agg(groupname)
 		FROM groups g NATURAL JOIN groupmembers gm
 		WHERE uid = $1
 			},undef,$c->user->id)
 	);
 
-	$c->stash(p => $dbh->selectrow_hashref(q{SELECT id,x,y,z, ruler, planet,race,
+	$c->stash(p => $dbh->selectrow_hashref(q{SELECT pid AS id,x,y,z, ruler, planet,race,
 		size, size_gain, size_gain_day,
 		score,score_gain,score_gain_day,
 		value,value_gain,value_gain_day,
@@ -54,32 +54,14 @@ sub index : Path : Args(0) {
 		valuerank,valuerank_gain,valuerank_gain_day,
 		xprank,xprank_gain,xprank_gain_day
 		from current_planet_stats_full p
-			WHERE id = ?
+			WHERE pid = ?
 			},undef,$c->user->planet)
 	);
 
 	my $calls = $dbh->prepare(q{
-		SELECT id,landing_tick,dc,curreta
-			,array_accum(race::text) AS race
-			,array_accum(amount) AS amount
-			,array_accum(eta) AS eta
-			,array_accum(shiptype) AS shiptype
-			,array_accum(coords) AS attackers
-		FROM (SELECT c.id, c.landing_tick
-			,dc.username AS dc, (c.landing_tick - tick()) AS curreta
-			,p2.race, i.amount, i.eta, i.shiptype, p2.alliance
-			,coords(p2.x,p2.y,p2.z)
-			FROM calls c
-				LEFT OUTER JOIN incomings i ON i.call = c.id
-				LEFT OUTER JOIN current_planet_stats p2 ON i.sender = p2.id
-				LEFT OUTER JOIN users dc ON c.dc = dc.uid
-			WHERE c.member = $1 AND c.landing_tick >= tick()
-			GROUP BY c.id, c.landing_tick, dc.username
-				,p2.race,i.amount,i.eta,i.shiptype,p2.alliance,p2.x,p2.y,p2.z
-			) c
-		GROUP BY id, landing_tick,dc,curreta
+SELECT * FROM defcalls
+WHERE uid = $1 AND landing_tick >= tick()
 		});
-
 	$calls->execute($c->user->id);
 	$c->stash(calls => $calls->fetchall_arrayref({}) );
 
@@ -164,7 +146,7 @@ sub postfleetupdate : Local {
 		$dbh->begin_work;
 		eval{
 			my $insert = $dbh->prepare(q{INSERT INTO fleets
-				(planet,name,mission,tick,amount)
+				(pid,name,mission,tick,amount)
 				VALUES (?,'Main','Full fleet',tick(),?) RETURNING fid});
 			my ($id) = $dbh->selectrow_array($insert,undef
 				,$c->user->planet,$amount);
@@ -400,12 +382,12 @@ sub postconfirmation : Local {
 			FROM  raid_claims c
 				JOIN raid_targets t ON c.target = t.id
 				JOIN raids r ON t.raid = r.id
-			WHERE c.uid = ? AND r.tick+c.wave-1 = ? AND t.planet = ?
+			WHERE c.uid = ? AND r.tick+c.wave-1 = ? AND t.pid = ?
 				AND r.open AND not r.removed
 			});
 		my $finddefensetarget = $dbh->prepare(q{SELECT c.id FROM calls c
 				JOIN users u ON c.member = u.uid
-			WHERE u.planet = $1 AND c.landing_tick = $2
+			WHERE u.pid = $1 AND c.landing_tick = $2
 		});
 		my $informDefChannel = $dbh->prepare(q{INSERT INTO defense_missions
 			(fleet,call) VALUES (?,?)
@@ -417,12 +399,12 @@ sub postconfirmation : Local {
 			WHERE uid = ? AND target = ? AND wave = ?
 			});
 		my $addfleet = $dbh->prepare(q{INSERT INTO fleets
-			(name,mission,planet,tick,amount)
-			VALUES ($2,$3,(SELECT planet FROM users WHERE uid = $1),tick(),$4)
+			(name,mission,pid,tick,amount)
+			VALUES ($2,$3,(SELECT pid FROM users WHERE uid = $1),tick(),$4)
 			RETURNING fid
 			});
 		my $addconfirmation = $dbh->prepare(q{INSERT INTO launch_confirmations
-			(fid,uid,target,landing_tick,eta,back) VALUES ($1,$2,$3,$4,$5,$6)
+			(fid,uid,pid,landing_tick,eta,back) VALUES ($1,$2,$3,$4,$5,$6)
 			});
 		my $addships = $dbh->prepare(q{INSERT INTO fleet_ships (fid,ship,amount)
 			VALUES (?,?,?)
@@ -530,10 +512,10 @@ sub defenders : Local {
 	my $dbh = $c->model;
 
 	my $defenders = $dbh->prepare(q{
-SELECT uid,u.planet,username, to_char(NOW() AT TIME ZONE timezone,'HH24:MI') AS time
+SELECT uid,pid AS planet,username, to_char(NOW() AT TIME ZONE timezone,'HH24:MI') AS time
 	,sms_note, call_if_needed, race, timezone
 FROM users u
-	JOIN current_planet_stats p ON p.id = u.planet
+	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)
 		});
@@ -563,22 +545,22 @@ sub member_fleets {
 	my $query = $dbh->prepare(q{
 (
 	SELECT DISTINCT ON (mission,name) fid,name,tick, NULL AS eta
-		,amount, NULL AS coords, planet AS target, NULL AS back
+		,amount, NULL AS coords, pid AS target, NULL AS back
 		,NULL AS recalled, mission
 	FROM fleets f
-	WHERE planet = $2 AND tick <= tick() AND tick >= tick() -  24
+	WHERE pid = $2 AND tick <= tick() AND tick >= tick() -  24
 		AND name IN ('Main','Advanced Unit') AND mission = 'Full fleet'
 	ORDER BY mission,name,tick DESC, fid DESC
 ) UNION (
 	SELECT fid,name,landing_tick AS tick, eta, amount
-		, coords(x,y,z), target, back
+		, coords(x,y,z), lc.pid AS target, back
 		, (back <> landing_tick + eta - 1) AS recalled
 		,CASE WHEN landing_tick <= tick() OR (back <> landing_tick + eta - 1)
 			THEN 'Returning' ELSE mission END AS mission
-	FROM fleets f
-		JOIN launch_confirmations USING (fid)
-	LEFT OUTER JOIN current_planet_stats t ON target = t.id
-	WHERE uid = $1 AND f.planet = $2 AND back > tick()
+	FROM  launch_confirmations lc
+		LEFT OUTER JOIN current_planet_stats t USING (pid)
+		JOIN fleets f USING (fid)
+	WHERE uid = $1 AND f.pid = $2 AND back > tick()
 		AND landing_tick - eta - 12 < tick()
 )
 		});
diff --git a/lib/NDWeb/Controller/Raids.pm b/lib/NDWeb/Controller/Raids.pm
index 6b4f16c..d7125f7 100644
--- a/lib/NDWeb/Controller/Raids.pm
+++ b/lib/NDWeb/Controller/Raids.pm
@@ -110,12 +110,12 @@ sub view : Local {
 	$c->stash(raid => $raid->{id});
 	my $planet;
 	if ($c->user->planet){
-		my $query = $dbh->prepare("SELECT value, score,x,y FROM current_planet_stats WHERE id = ?");
+		my $query = $dbh->prepare(q{SELECT value, score,x,y FROM current_planet_stats WHERE pid = ?});
 		$planet = $dbh->selectrow_hashref($query,undef,$c->user->planet);
 	}
 	$c->stash(message => parseMarkup($raid->{message}));
 	$c->stash(landingtick => $raid->{tick});
-	my $targetquery = $dbh->prepare(q{SELECT r.id, r.planet, size, score, value
+	my $targetquery = $dbh->prepare(q{SELECT r.id, pid AS planet, size, score, value
 		, p.x,p.y,p.z, race
 		, p.value - p.size*200 -
 			COALESCE(ps.metal+ps.crystal+ps.eonium,0)/150 -
@@ -128,9 +128,9 @@ sub view : Local {
 		,amps, distorters, light_fac, medium_fac, heavy_fac
 		,hulls, waves
 		FROM current_planet_stats p
-			JOIN raid_targets r ON p.id = r.planet
-			LEFT OUTER JOIN current_planet_scans ps ON p.id = ps.planet
-			LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
+			JOIN raid_targets r USING (pid)
+			LEFT OUTER JOIN current_planet_scans ps USING (pid)
+			LEFT OUTER JOIN current_development_scans ds USING (pid)
 		WHERE r.raid = $1
 			AND NOT COALESCE(p.x = $2 AND p.y = $3,False)
 		ORDER BY size});
@@ -150,7 +150,7 @@ sub view : Local {
 		my $unitscans = $dbh->prepare(q{
 SELECT DISTINCT ON (name) fid, name, tick, amount
 FROM fleets
-WHERE planet = ?
+WHERE pid = ?
 	AND mission = 'Full fleet'
 GROUP BY fid,tick,name,amount
 ORDER BY name,tick DESC
@@ -247,22 +247,22 @@ sub edit : Local {
 	}
 
 	my $targetquery = $dbh->prepare(qq{SELECT r.id,coords(x,y,z),comment,size
-		,score,value,race,planet_status,relationship,r.planet, s.scans
+		,score,value,race,planet_status,relationship,pid AS planet, s.scans
 		,COALESCE(max(rc.wave),0) AS waves
 		FROM raid_targets r
-			JOIN current_planet_stats p ON p.id = r.planet
-			LEFT OUTER JOIN ( SELECT planet, array_accum(s::text) AS scans
-				FROM ( SELECT DISTINCT ON (planet,type) planet,scan_id,type, tick
+			JOIN current_planet_stats p USING (pid)
+			LEFT OUTER JOIN ( SELECT pid, array_agg(s::text) AS scans
+				FROM ( SELECT DISTINCT ON (pid,type) pid,scan_id,type, tick
 					FROM scans
 					WHERE tick > tick() - 24
-					ORDER BY planet,type ,tick DESC
+					ORDER BY pid,type ,tick DESC
 					) s
-				GROUP BY planet
-			) s ON s.planet = r.planet
+				GROUP BY pid
+			) s USING (pid)
 			LEFT OUTER JOIN raid_claims rc ON r.id = rc.target
 		WHERE r.raid = ?
 		GROUP BY r.id,x,y,z,comment,size,score,value,race
-			,planet_status,relationship,comment,r.planet, s.scans
+			,planet_status,relationship,comment,pid, s.scans
 			,sizerank,scorerank,xprank,valuerank
 		ORDER BY $order
 		});
@@ -344,8 +344,8 @@ sub postaddtargets : Local {
 	$sizelimit = -1 unless $sizelimit;
 
 	my $targets = $c->req->param('targets');
-	my $addtarget = $dbh->prepare(qq{INSERT INTO raid_targets(raid,planet) (
-		SELECT ?, id FROM current_planet_stats p
+	my $addtarget = $dbh->prepare(q{INSERT INTO raid_targets(raid,pid) (
+		SELECT ?, pid FROM current_planet_stats p
 		WHERE x = ? AND y = ? AND COALESCE(z = ?,TRUE)
 		AND p.size > ?
 		)});
@@ -367,13 +367,13 @@ sub postaddtargets : Local {
 			push @errors, $@;
 		}
 	}
-	if ($c->req->param('alliance') =~ /^(\d+)$/ && $1 != 1){
-		my $addtarget = $dbh->prepare(qq{INSERT INTO raid_targets(raid,planet) (
-			SELECT ?,id FROM current_planet_stats p WHERE alliance_id = ? AND p.size > ?)
+	if ($c->req->param('alliance') =~ /^(\d+)$/ && $1 ne 'NewDawn'){
+		my $addtarget = $dbh->prepare(q{INSERT INTO raid_targets(raid,pid) (
+			SELECT ?,pid FROM current_planet_stats p WHERE aid= ? AND p.size > ?)
 			});
 		eval {
 			$addtarget->execute($raid,$1,$sizelimit);
-			$c->forward('log',[$raid,"BC adding alliance $1 to raid"]);
+			$c->forward('log',[$raid,"BC adding alliance '$1' to raid"]);
 		};
 		if ($@ =~ /duplicate key value violates unique constraint "raid_targets_raid_key"/){
 			push @errors, "A planet from this alliance has already been added to the raid,"
@@ -524,11 +524,11 @@ sub postcreate : Local {
 		my @gals = $c->req->param('gal');
 		my @targets = $c->req->param('target');
 
-		my $addtarget = $dbh->prepare(q{INSERT INTO raid_targets(raid,planet) (
-			SELECT $1,id FROM current_planet_stats p WHERE (planet_status IN ('','Hostile')
+		my $addtarget = $dbh->prepare(q{INSERT INTO raid_targets(raid,pid) (
+			SELECT $1,pid FROM current_planet_stats p WHERE (planet_status IN ('','Hostile')
 				AND (relationship IS NULL OR relationship IN ('','Hostile')))
-				AND (id = ANY ($2) OR ( size > $4 AND (x,y) IN (
-					SELECT x,y FROM current_planet_stats WHERE id = ANY ($3)))
+				AND (pid = ANY ($2) OR ( size > $4 AND (x,y) IN (
+					SELECT x,y FROM current_planet_stats WHERE pid = ANY ($3)))
 				)
 			)
 		});
@@ -560,7 +560,7 @@ sub targetlist : Local {
 	$order = "p.$order" if $order =~ /rank$/;
 
 	my $query = $dbh->prepare(q{
-SELECT p.id, coords(p.x,p.y,p.z),p.x,p.y,p.alliance, p.score, p.value, p.size, p.xp,nfvalue, nfvalue - sum(pa.value) AS nfvalue2, p.race
+SELECT p.pid AS id, coords(p.x,p.y,p.z),p.x,p.y,p.alliance, p.score, p.value, p.size, p.xp,nfvalue, nfvalue - sum(pa.value) AS nfvalue2, p.race
 FROM current_planet_stats p
 	JOIN (SELECT g.x,g.y, sum(p.value) AS nfvalue
 		FROM galaxies g join current_planet_stats p on g.x = p.x AND g.y = p.y
@@ -568,13 +568,12 @@ FROM current_planet_stats p
 			AND (planet_status IN ('','Hostile')
 				AND (relationship IS NULL OR relationship IN ('','Hostile')))
 		GROUP BY g.x,g.y
-	) g ON p.x = g.x AND p.y = g.y
-	JOIN current_planet_stats pa ON pa.x = g.x AND pa.y = g.y
+	) g USING (x,y)
+	JOIN current_planet_stats pa USING (x,y,aid)
 WHERE p.x <> 200
-	AND p.alliance_id = ANY ($1)
-	AND pa.alliance_id = ANY ($1)
+	AND aid = ANY ($1)
 	AND p.relationship IN ('','Hostile')
-GROUP BY p.id, p.x,p.y,p.z,p.alliance, p.score, p.value, p.size, p.xp, nfvalue,p.race
+GROUP BY p.pid, p.x,p.y,p.z,p.alliance, p.score, p.value, p.size, p.xp, nfvalue,p.race
 	,p.scorerank,p.valuerank,p.sizerank,p.xprank
 ORDER BY
 		} . $order);
@@ -657,13 +656,13 @@ WHERE fid = $1
 sub listAlliances : Private {
 	my ($self, $c) = @_;
 	my @alliances;
-	my $query = $c->model->prepare(q{SELECT id,name FROM alliances
+	my $query = $c->model->prepare(q{SELECT aid AS id,alliance AS name FROM alliances
 		WHERE relationship IN ('','Hostile')
-			AND id IN (SELECT alliance_id FROM planets)
-		 ORDER BY LOWER(name)
+			AND alliance IN (SELECT alliance FROM planets)
+		 ORDER BY LOWER(alliance)
 		});
 	$query->execute;
-	push @alliances,{id => -1, name => ''};
+	push @alliances,{id => '', name => ''};
 	while (my $ally = $query->fetchrow_hashref){
 		push @alliances,$ally;
 	}
diff --git a/lib/NDWeb/Controller/Rankings.pm b/lib/NDWeb/Controller/Rankings.pm
index b34792b..45f2a79 100644
--- a/lib/NDWeb/Controller/Rankings.pm
+++ b/lib/NDWeb/Controller/Rankings.pm
@@ -56,7 +56,7 @@ sub planets : Local {
 		$extra_columns = ",planet_status,hit_us, alliance,relationship,nick";
 	}
 
-	my $query = $dbh->prepare(qq{SELECT id,x,y,z,ruler,planet,race,
+	my $query = $dbh->prepare(qq{SELECT pid AS id,x,y,z,ruler,planet,race,
 		size, size_gain, size_gain_day,
 		score,score_gain,score_gain_day,
 		value,value_gain,value_gain_day,
@@ -143,7 +143,7 @@ sub alliances : Local {
 	if ($order =~ /rank$/){
 		$browse = qq{WHERE $order > ? ORDER BY $order ASC LIMIT 100};
 	}
-	my $query = $dbh->prepare(qq{SELECT a.name,a.id,
+	my $query = $dbh->prepare(qq{SELECT alliance AS name,aid AS id,
 		size, size_gain, size_gain_day,
 		score,score_gain,score_gain_day,
 		avgsize,avgsize_gain,avgsize_gain_day,
diff --git a/lib/NDWeb/Controller/Root.pm b/lib/NDWeb/Controller/Root.pm
index a6bb56a..6fab7f2 100644
--- a/lib/NDWeb/Controller/Root.pm
+++ b/lib/NDWeb/Controller/Root.pm
@@ -131,7 +131,7 @@ sub listTargets : Private {
 FROM raid_claims c
 	JOIN raid_targets t ON c.target = t.id
 	JOIN raids r ON t.raid = r.id
-	JOIN current_planet_stats p ON t.planet = p.id
+	JOIN current_planet_stats p USING (pid)
 WHERE c.uid = $1 AND r.tick+c.wave > tick() AND r.open AND not r.removed
 ORDER BY r.tick+c.wave,x,y,z});
 	$query->execute($c->user->id) or die $dbh->errstr;
@@ -146,8 +146,8 @@ ORDER BY r.tick+c.wave,x,y,z});
 sub listAlliances : Private {
 	my ($self, $c) = @_;
 	my @alliances;
-	push @alliances,{id => -1, name => ''};
-	my $query = $c->model->prepare(q{SELECT id,name FROM alliances ORDER BY LOWER(name)});
+	push @alliances,{aid => '', alliance => ''};
+	my $query = $c->model->prepare(q{SELECT aid,alliance FROM alliances ORDER BY LOWER(alliance)});
 	$query->execute;
 	while (my $ally = $query->fetchrow_hashref){
 		push @alliances,$ally;
@@ -224,7 +224,7 @@ sub end : ActionClass('RenderView') {
 		my $fleetupdate = 0;
 		if ($c->check_user_roles(qw/member_menu/)){
 			$fleetupdate = $dbh->selectrow_array(q{
-SELECT tick FROM fleets WHERE planet = ? AND tick > tick() - 24
+SELECT tick FROM fleets WHERE pid = ? AND tick > tick() - 24
 AND mission = 'Full fleet' AND name IN ('Main','Advanced Unit');
 				},undef,$c->user->planet);
 			$fleetupdate = 0 unless defined $fleetupdate;
diff --git a/lib/NDWeb/Controller/Stats.pm b/lib/NDWeb/Controller/Stats.pm
index add0292..a1db245 100644
--- a/lib/NDWeb/Controller/Stats.pm
+++ b/lib/NDWeb/Controller/Stats.pm
@@ -56,8 +56,8 @@ FROM galaxies g
 			SELECT raid,p.tick,x,y,count(*), sum(size) AS size
 			FROM raids r
 				JOIN raid_targets rt ON r.id = rt.raid
-				JOIN planet_stats p ON p.id = rt.planet AND p.tick = r.tick-12
-			WHERE x = $1 and y = $2
+				JOIN planet_stats p USING (pid)
+			WHERE x = $1 and y = $2 AND p.tick = r.tick-12
 			GROUP BY raid,p.tick,x,y
 			) AS a
 			JOIN galaxies g USING (tick,x,y)
@@ -91,8 +91,8 @@ WHERE g.tick = ( SELECT max(tick) AS max FROM galaxies)
 		};
 	}
 
-	$query = $dbh->prepare(qq{SELECT DISTINCT ON (x,y,z,p.id)
-		p.id,coords(x,y,z), ruler, p.planet,race,
+	$query = $dbh->prepare(qq{SELECT DISTINCT ON (x,y,z,pid)
+		pid AS id,coords(x,y,z), ruler, p.planet,race,
 		size, size_gain, size_gain_day,
 		score,score_gain,score_gain_day,
 		value,value_gain,value_gain_day,
@@ -103,10 +103,10 @@ WHERE g.tick = ( SELECT max(tick) AS max FROM galaxies)
 		xprank,xprank_gain,xprank_gain_day
 		$extra_columns
 		FROM current_planet_stats_full p
-			LEFT OUTER JOIN planet_scans ps ON p.id = ps.planet
-			LEFT OUTER JOIN current_development_scans ds ON p.id = ds.planet
+			LEFT OUTER JOIN planet_scans ps USING (pid)
+			LEFT OUTER JOIN current_development_scans ds USING (pid)
 		WHERE x = ? AND y = ? AND COALESCE(z = ?,TRUE)
-		ORDER BY x,y,z,p.id,ps.tick DESC, ps.id DESC, ds.tick DESC, ds.id DESC
+		ORDER BY x,y,z,pid,ps.tick DESC, ps.id DESC, ds.tick DESC, ds.id DESC
 		});
 
 	$query->execute($x,$y,$z);
@@ -117,8 +117,8 @@ sub planet : Local {
 	my ( $self, $c, $id ) = @_;
 	my $dbh = $c->model;
 
-	my $p = $dbh->selectrow_hashref(q{SELECT id,x,y,z FROM current_planet_stats
-		WHERE id = $1},undef,$id);
+	my $p = $dbh->selectrow_hashref(q{SELECT pid AS id,x,y,z FROM current_planet_stats
+		WHERE pid = $1},undef,$id);
 
 	$c->detach('/default') unless $p;
 
@@ -129,9 +129,9 @@ sub planet : Local {
 		my $query = $dbh->prepare(q{
 (
 	SELECT DISTINCT ON (mission,name) fid,mission,name,tick, NULL AS eta
-		,amount, NULL AS coords, planet, NULL AS back
+		,amount, NULL AS coords, pid AS planet, NULL AS back
 	FROM fleets f
-	WHERE planet = $1 AND tick <= tick() AND (
+	WHERE pid = $1 AND tick <= tick() AND (
 			fid IN (SELECT fid FROM fleet_scans)
 		) AND (
 			mission = 'Full fleet'
@@ -141,9 +141,9 @@ sub planet : Local {
 ) UNION (
 	SELECT DISTINCT ON (tick,x,y,z,mission,name,amount)
 		NULL as fid, i.mission, i.name, i.tick,eta
-		, i.amount, coords(x,y,z), t.id AS planet, back
+		, i.amount, coords(x,y,z), pid AS planet, back
 	FROM intel i
-	LEFT OUTER JOIN current_planet_stats t ON i.target = t.id
+	LEFT OUTER JOIN current_planet_stats t ON i.target = pid
 	WHERE uid = -1 AND i.sender = $1 AND i.tick > tick() - 14 AND i.tick < tick() + 14
 	ORDER BY i.tick,x,y,z,mission,name,amount,back
 )
@@ -168,16 +168,16 @@ sub planet : Local {
 		$c->stash(outgoings => \@missions);
 
 		$query = $dbh->prepare(q{
-			SELECT DISTINCT ON (i.tick,x,y,z,s.id,i.name,i.amount) i.id,i.mission, i.name, i.tick,eta
-						, i.amount, coords(x,y,z) AS coords, s.id AS planet
+			SELECT DISTINCT ON (i.tick,x,y,z,pid,i.name,i.amount) i.id,i.mission, i.name, i.tick,eta
+						, i.amount, coords(x,y,z) AS coords, pid AS planet
 			FROM intel i
 			LEFT OUTER JOIN (planets
-				NATURAL JOIN planet_stats) s ON i.sender = s.id
+				NATURAL JOIN planet_stats) s ON i.sender = pid
 					AND s.tick = ( SELECT MAX(tick) FROM planet_stats)
 			WHERE  i.uid = -1
 				AND i.target = ?
 				AND i.tick > tick() - 3
-			ORDER BY i.tick,x,y,z,s.id,i.name,i.amount,i.eta
+			ORDER BY i.tick,x,y,z,pid,i.name,i.amount,i.eta
 		});
 		$query->execute($id);
 		my @incomings;
@@ -197,7 +197,7 @@ sub planet : Local {
 
 	if ($c->check_user_roles(qw/stats_scans/)){
 		my $query = $dbh->prepare(q{SELECT type,scan_id, tick FROM scans
-			WHERE planet = ? AND tick > tick() - 168
+			WHERE pid = ? AND tick > tick() - 168
 			ORDER BY tick,type DESC
 		});
 		$query->execute($id);
@@ -206,18 +206,18 @@ sub planet : Local {
 
 	if ($c->check_user_roles(qw/stats_planetdata/)){
 		$c->stash(planetscan => $dbh->selectrow_hashref(q{SELECT *
-			FROM current_planet_scans WHERE planet = $1},undef,$id));
+			FROM current_planet_scans WHERE pid = $1},undef,$id));
 		$c->stash(devscan => $dbh->selectrow_hashref(q{SELECT *
-			FROM current_development_scans WHERE planet = $1},undef,$id));
+			FROM current_development_scans WHERE pid = $1},undef,$id));
 	}
 
-	my $query = $dbh->prepare(q{SELECT value,value_gain AS gain,tick FROM planet_stats 
-		WHERE id = ? AND tick > tick() - 24});
+	my $query = $dbh->prepare(q{SELECT value,value_gain AS gain,tick FROM planet_stats
+		WHERE pid = ? AND tick > tick() - 24});
 	$query->execute($id);
 	$c->stash(values => $query->fetchall_arrayref({}) );
 
 	$query = $dbh->prepare(q{SELECT x,y,z,tick FROM planet_stats
-		WHERE id = ? ORDER BY tick ASC});
+		WHERE pid = ? ORDER BY tick ASC});
 	$query->execute($id);
 	my @coords;
 	my $co = {x => 0, y => 0, z => 0};
@@ -245,7 +245,7 @@ sub find : Local {
 	}elsif (/(\d+)(?: |:)(\d+)/){
 		$c->res->redirect($c->uri_for('galaxy',$1,$2));
 	}elsif($c->check_user_roles(qw/stats_find_nick/)) {
-		my $query = $dbh->prepare(q{SELECT id,coords(x,y,z),nick
+		my $query = $dbh->prepare(q{SELECT pid AS id,coords(x,y,z),nick
 			FROM current_planet_stats p
 			WHERE nick ilike $1
 		});
diff --git a/lib/NDWeb/Controller/TextExport.pm b/lib/NDWeb/Controller/TextExport.pm
index 74118d1..dc0b99d 100644
--- a/lib/NDWeb/Controller/TextExport.pm
+++ b/lib/NDWeb/Controller/TextExport.pm
@@ -32,7 +32,7 @@ sub alliance : Local {
 
 	my $query = $dbh->prepare(q{SELECT coords(x,y,z), size, score, value,race, COALESCE(nick,'') AS nick
 		FROM current_planet_stats
-		WHERE alliance_id = $1
+		WHERE aid = $1
 		ORDER BY x,y,z});
 	$query->execute($ally);
 
diff --git a/lib/NDWeb/Controller/Users.pm b/lib/NDWeb/Controller/Users.pm
index 9d70fc2..c2a521e 100644
--- a/lib/NDWeb/Controller/Users.pm
+++ b/lib/NDWeb/Controller/Users.pm
@@ -91,10 +91,11 @@ sub updateUser : Local {
 				my $column = $1;
 				my $value = $c->req->param($column);
 				if ($column eq 'planet'){
+					$column = 'pid';
 					if ($value eq ''){
 						$value = undef;
 					}elsif($value =~ /^(\d+)\D+(\d+)\D+(\d+)$/){
-						($value) = $dbh->selectrow_array(q{SELECT id FROM
+						($value) = $dbh->selectrow_array(q{SELECT pid FROM
 							current_planet_stats WHERE x = ? and y = ? and z =?}
 							,undef,$1,$2,$3);
 					}
@@ -131,9 +132,10 @@ sub findUser : Private {
 	my $dbh = $c->model;
 
 	my $query = $dbh->prepare(q{
-		SELECT uid,username,hostmask,CASE WHEN u.planet IS NULL THEN '' ELSE coords(x,y,z) END AS planet,attack_points,defense_points,scan_points,humor_points,info, email, sms
-		FROM users u LEFT OUTER JOIN current_planet_stats p ON u.planet = p.id
-		WHERE uid = ?;
+SELECT uid,username,hostmask,attack_points,defense_points,scan_points,humor_points,info, email, sms
+	,COALESCE(coords(x,y,z),'') AS planet
+FROM users u LEFT OUTER JOIN current_planet_stats p USING (pid)
+WHERE uid = ?;
 		});
 	$user = $dbh->selectrow_hashref($query,undef,$user);
 
diff --git a/lib/NDWeb/Include.pm b/lib/NDWeb/Include.pm
index b092122..1cbcca9 100644
--- a/lib/NDWeb/Include.pm
+++ b/lib/NDWeb/Include.pm
@@ -81,9 +81,9 @@ sub intelquery {
 	return qq{
 SELECT $columns, i.mission, i.tick AS landingtick,MIN(i.eta) AS eta, i.amount, i.ingal, u.username
 FROM (intel i NATURAL JOIN users u)
-	JOIN current_planet_stats t ON i.target = t.id
-	JOIN current_planet_stats o ON i.sender = o.id
-WHERE $where 
+	JOIN current_planet_stats t ON i.target = t.pid
+	JOIN current_planet_stats o ON i.sender = o.pid
+WHERE $where
 GROUP BY i.tick,i.mission,t.x,t.y,t.z,o.x,o.y,o.z,i.amount,i.ingal,u.username,t.alliance,o.alliance,t.nick,o.nick,i.sender,i.target
 ORDER BY i.tick DESC, i.mission};
 }
diff --git a/root/src/intel/planet.tt2 b/root/src/intel/planet.tt2
index cb7aae7..7d18e53 100644
--- a/root/src/intel/planet.tt2
+++ b/root/src/intel/planet.tt2
@@ -38,7 +38,7 @@
 		<tr><td>Alliance</td>
 			<td><select name="alliance">
 [% FOR a IN alliances %]
-				<option value="[% a.id %]" [% IF a.id == p.alliance_id %]selected[% END %]> [% a.name %]</option>
+				<option value="[% a.alliance %]" [% IF a.aid == p.aid %]selected[% END %]> [% a.alliance %]</option>
 [% END %]
 				</select></td>
 			<td><input type="checkbox" name="calliance"></td>
diff --git a/root/static/js/raid.js b/root/static/js/raid.js
index a2dbc2c..3afa58c 100644
--- a/root/static/js/raid.js
+++ b/root/static/js/raid.js
@@ -1,4 +1,4 @@
-modified = "0000-01-01";
+modified = "0001-01-01";
 
 function parseUpdate(xml){
 	if ($('timestamp',xml).text())
diff --git a/scripts/createretal.pl b/scripts/createretal.pl
index bfdce31..e2f8688 100755
--- a/scripts/createretal.pl
+++ b/scripts/createretal.pl
@@ -49,13 +49,13 @@ $dbh->do(q{INSERT INTO raid_access (raid,gid) VALUES(?,2)}
 my $addtarget = $dbh->prepare(q{INSERT INTO raid_targets(raid,planet,comment)
 	VALUES($1,$2,$3)});
 
-my $incs = $dbh->prepare(q{SELECT sender,array_accum(i.eta) AS eta,array_accum(amount) AS amount
-	,array_accum(shiptype) AS type,array_accum(fleet) AS name,array_accum(c.landing_tick) AS landing
+my $incs = $dbh->prepare(q{SELECT pid,array_agg(i.eta) AS eta,array_agg(amount) AS amount
+	,array_agg(shiptype) AS type,array_agg(fleet) AS name,array_agg(c.landing_tick) AS landing
 	FROM calls c
 		JOIN incomings i ON i.call = c.id
 	WHERe NOT c.covered AND c.landing_tick BETWEEN tick() AND tick() + 6
 		AND c.landing_tick + GREATEST(i.eta,7) > tick() + 10
-	GROUP BY sender
+	GROUP BY pid
 	});
 $incs->execute;
 
@@ -69,7 +69,7 @@ while (my $inc = $incs->fetchrow_hashref){
 		my $back = $landing + $eta;
 		$comment .= "$name: ETA=$eta Amount=$amount Type:'$type' Landing tick=$landing Estimated back:$back\n";
 	}
-	$addtarget->execute($raid,$inc->{sender},$comment);
+	$addtarget->execute($raid,$inc->{pid},$comment);
 }
 
 $dbh->commit;
diff --git a/scripts/ndrank.pl b/scripts/ndrank.pl
index acb1405..eff1d38 100755
--- a/scripts/ndrank.pl
+++ b/scripts/ndrank.pl
@@ -35,7 +35,7 @@ use ND::DB;
 our $dbh = ND::DB::DB();
 
 $dbh->begin_work;
-my $st = $dbh->prepare(q{SELECT uid FROM current_planet_stats p JOIN users u ON p.id = u.planet WHERE alliance_id = 1 ORDER BY score DESC});
+my $st = $dbh->prepare(q{SELECT uid FROM current_planet_stats p JOIN users u USING (pid) WHERE alliance = 'NewDawn' ORDER BY score DESC});
 my $update = $dbh->prepare(q{UPDATE users SET rank = ? WHERE uid = ?});
 $st->execute;
 my $rank = 1;
diff --git a/scripts/parsealliances.pl b/scripts/parsealliances.pl
index bc43e96..02e9a49 100755
--- a/scripts/parsealliances.pl
+++ b/scripts/parsealliances.pl
@@ -63,7 +63,7 @@ for my $i (3,4){
 		$alliance->[$i+2] = $rank;
     }
 }
-my $insert = $dbh->prepare(q{INSERT INTO alliance_stats (tick,id,members,
+my $insert = $dbh->prepare(q{INSERT INTO alliance_stats (tick,aid,members,
 	size,score,
 	sizerank,scorerank,
 	size_gain,score_gain,
@@ -73,7 +73,7 @@ my $insert = $dbh->prepare(q{INSERT INTO alliance_stats (tick,id,members,
 	members_gain,members_gain_day
 	) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}) or die $dbh->errstr;
 
-my $findalliance = $dbh->prepare(q{SELECT tick,id,members,
+my $findalliance = $dbh->prepare(q{SELECT tick,aid,members,
 	size, score,
 	sizerank, scorerank,
 	size_gain, score_gain,
@@ -81,7 +81,7 @@ my $findalliance = $dbh->prepare(q{SELECT tick,id,members,
 	size_gain_day, score_gain_day,
 	sizerank_gain_day, scorerank_gain_day,
 	members_gain,members_gain_day
-FROM alliance_stats WHERE id = $1 AND tick < $2 ORDER BY tick DESC LIMIT 1}) or die $dbh->errstr;
+FROM alliance_stats WHERE aid = $1 AND tick < $2 ORDER BY tick DESC LIMIT 1}) or die $dbh->errstr;
 
 for my $alliance (@alliances) {
 
diff --git a/scripts/parseplanets.pl b/scripts/parseplanets.pl
index 50225ce..7d3eda7 100755
--- a/scripts/parseplanets.pl
+++ b/scripts/parseplanets.pl
@@ -64,15 +64,15 @@ for my $i (8,7,5,6){
     }
 }
 
-my $findplanets = $dbh->prepare(q{SELECT tick,id, x, y, z, 
-	size, score, value, xp, 
-	sizerank, scorerank, valuerank, xprank, 
-	size_gain, score_gain, value_gain, xp_gain, 
-	sizerank_gain, scorerank_gain, valuerank_gain, xprank_gain, 
-	size_gain_day, score_gain_day, value_gain_day, xp_gain_day, 
+my $findplanets = $dbh->prepare(q{SELECT tick,pid, x, y, z,
+	size, score, value, xp,
+	sizerank, scorerank, valuerank, xprank,
+	size_gain, score_gain, value_gain, xp_gain,
+	sizerank_gain, scorerank_gain, valuerank_gain, xprank_gain,
+	size_gain_day, score_gain_day, value_gain_day, xp_gain_day,
 	sizerank_gain_day, scorerank_gain_day, valuerank_gain_day, xprank_gain_day
 FROM planet_stats WHERE tick = (SELECT MAX(tick) FROM planet_stats WHERE tick < $1)});
-my $insert = $dbh->prepare(q{INSERT INTO planet_stats(tick,id, x, y, z, 
+my $insert = $dbh->prepare(q{INSERT INTO planet_stats(tick,pid, x, y, z,
 	size, score, value,xp,
 	sizerank,scorerank,valuerank,xprank,
 	size_gain, score_gain, value_gain, xp_gain,
@@ -85,6 +85,8 @@ my %oldStats;
 while (my @planet = $findplanets->fetchrow){
 	$oldStats{$planet[1]} = \@planet;
 }
+my $intel = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES(
+		(SELECT ftid FROM planets WHERE pid = $2),$1,$3)});
 for my $planet (@planets) {
 	#print "$planet->[1]\n";
 	my $oldPlanet = $oldStats{$planet->[1]};
@@ -100,7 +102,7 @@ for my $planet (@planets) {
 			($planet->[3] != $oldPlanet->[3]) or 
 			($planet->[4] != $oldPlanet->[4])){
 			#print "Planet has moved from $oldPlanet[2]:$oldPlanet[3]:$oldPlanet[4] to $planet->[2]:$planet->[3]:$planet->[4]\n";
-			intel_log -3, $planet->[1],"Planet has moved from $oldPlanet->[2]:$oldPlanet->[3]:$oldPlanet->[4] to $planet->[2]:$planet->[3]:$planet->[4] tick $tick";
+			$intel->execute(-3, $planet->[1],"Planet has moved from $oldPlanet->[2]:$oldPlanet->[3]:$oldPlanet->[4] to $planet->[2]:$planet->[3]:$planet->[4] tick $tick");
 		}
 	}
 	#print "@{$oldPlanet}\n";
diff --git a/scripts/scans.pl b/scripts/scans.pl
index a7c25dd..943ad62 100755
--- a/scripts/scans.pl
+++ b/scripts/scans.pl
@@ -45,7 +45,7 @@ my $scangroups = $dbh->prepare(q{SELECT id,scan_id,tick,uid FROM scans
 });
 my $oldscan = $dbh->prepare(q{SELECT scan_id FROM scans WHERE scan_id = LOWER(?) AND tick >= tick() - 168});
 my $addScan = $dbh->prepare(q{INSERT INTO scans (scan_id,tick,uid) VALUES (LOWER(?),?,?)});
-my $parsedscan = $dbh->prepare(q{UPDATE scans SET tick = ?, type = ?, planet = ?, parsed = TRUE WHERE id = ?});
+my $parsedscan = $dbh->prepare(q{UPDATE scans SET tick = ?, type = ?, pid = ?, parsed = TRUE WHERE id = ?});
 my $addpoints = $dbh->prepare(q{UPDATE users SET scan_points = scan_points + ? WHERE uid = ? });
 my $delscan = $dbh->prepare(q{DELETE FROM scans WHERE id = ?});
 
@@ -73,23 +73,23 @@ my $newscans = $dbh->prepare(q{SELECT id,scan_id,tick,uid FROM scans
 	WHERE NOT groupscan AND NOT parsed FOR UPDATE
 });
 my $findplanet = $dbh->prepare(q{SELECT planetid(?,?,?,?)});
-my $findoldplanet = $dbh->prepare(q{SELECT id FROM planet_stats WHERE x = $1 AND y = $2 AND z = $3 AND tick <= $4 ORDER BY tick DESC LIMIT 1});
+my $findoldplanet = $dbh->prepare(q{SELECT pid FROM planet_stats WHERE x = $1 AND y = $2 AND z = $3 AND tick <= $4 ORDER BY tick DESC LIMIT 1});
 my $findcoords = $dbh->prepare(q{SELECT * FROM planetcoords(?,?)});
-my $addfleet = $dbh->prepare(q{INSERT INTO fleets (name,mission,planet,tick,amount) VALUES(?,?,?,?,?) RETURNING fid});
+my $addfleet = $dbh->prepare(q{INSERT INTO fleets (name,mission,pid,tick,amount) VALUES(?,?,?,?,?) RETURNING fid});
 my $fleetscan = $dbh->prepare(q{INSERT INTO fleet_scans (fid,id) VALUES(?,?)});
 my $addintel = $dbh->prepare(q{INSERT INTO intel (name,mission,sender,target,tick,eta,back,amount,ingal,uid)
 	VALUES(?,?,?,?,?,?,?,?,?,-1) RETURNING id});
 my $intelscan = $dbh->prepare(q{INSERT INTO intel_scans (intel,id) VALUES(?,?)});
 my $addships = $dbh->prepare(q{INSERT INTO fleet_ships (fid,ship,amount) VALUES(?,?,?)});
 my $addplanetscan = $dbh->prepare(q{INSERT INTO planet_scans
-	(id,tick,planet,metal_roids,metal,crystal_roids,crystal,eonium_roids,eonium
+	(id,tick,pid,metal_roids,metal,crystal_roids,crystal,eonium_roids,eonium
 		,agents,guards,light,medium,heavy,hidden)
 	VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)});
 
 sub parse_planet {
 	my ($scan,$file) = @_;
 
-	my @values = ($scan->{id},$scan->{tick},$scan->{planet});
+	my @values = ($scan->{id},$scan->{tick},$scan->{pid});
 	$file =~ s/(\d),(\d)/$1$2/g;
 
 	while($file =~ m{"center">(Metal|Crystal|Eonium)</td>\D+(\d+)\D+([\d,]+)}g){
@@ -113,7 +113,7 @@ sub parse_incoming {
 
 	while($file =~ m{class="left">Fleet:\s(.*?)</td><td\sclass="right">
 			Mission:\s(\w+)</td></tr>(.*?)Total\sShips:\s(\d+)}sxg){
-		my $id = addfleet($1,$2,$3,$scan->{planet},$scan->{tick},$4);
+		my $id = addfleet($1,$2,$3,$scan->{pid},$scan->{tick},$4);
 		$fleetscan->execute($id,$scan->{id}) or die $dbh->errstr;
 	}
 }
@@ -121,7 +121,7 @@ sub parse_incoming {
 sub parse_unit {
 	my ($scan,$file) = @_;
 
-	my $id = addfleet($scan->{type},'Full fleet',$file,$scan->{planet},$scan->{tick});
+	my $id = addfleet($scan->{type},'Full fleet',$file,$scan->{pid},$scan->{tick});
 	$fleetscan->execute($id,$scan->{id}) or die $dbh->errstr;
 }
 
@@ -132,7 +132,7 @@ sub parse_jumpgate {
 		my ($sender) = $dbh->selectrow_array($findplanet,undef,$1,$2,$3,$scan->{tick});
 		($sender) = $dbh->selectrow_array($findoldplanet,undef,$1,$2,$3,$scan->{tick})
 			if ((not defined $sender) && $4 eq 'Return');
-		my $id = addintel($5,$4,$sender,$scan->{planet},$scan->{tick}+$6,$6
+		my $id = addintel($5,$4,$sender,$scan->{pid},$scan->{tick}+$6,$6
 			,undef,$7, $scan->{x} == $1 && $scan->{y} == $2);
 		$intelscan->execute($id,$scan->{id});
 	}
@@ -140,7 +140,7 @@ sub parse_jumpgate {
 }
 
 my $adddevscan = $dbh->prepare(q{INSERT INTO development_scans
-	(id,tick,planet,light_fac,medium_fac,heavy_fac,amps,distorters
+	(id,tick,pid,light_fac,medium_fac,heavy_fac,amps,distorters
 		,metal_ref,crystal_ref,eonium_ref,reslabs,fincents,seccents
 		,travel,infra,hulls,waves,extraction,covert,mining,total)
 	VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
@@ -187,7 +187,7 @@ while (my $scan = $newscans->fetchrow_hashref){
 			next;
 		}
 		my ($planet) = $dbh->selectrow_array($findplanet,undef,$x,$y,$z,$tick);
-		$scan->{planet} = $planet;
+		$scan->{pid} = $planet;
 		unless ($planet){
 			$dbh->pg_rollback_to('scans') or die "rollback didn't work";
 			if ( $x == 0 && $y == 0 && $z == 0 ){
-- 
2.39.2