From: Michael Andreen <harv@ruin.nu>
Date: Thu, 6 Aug 2009 17:56:00 +0000 (+0200)
Subject: Update to new database structure
X-Git-Url: https://ruin.nu/git/?p=NDIRC.git;a=commitdiff_plain;h=ae37bc6b64270f8c65298a36fce1f4bad761231f

Update to new database structure
---

diff --git a/Commands/Def.pm b/Commands/Def.pm
index 9b2addb..1731689 100644
--- a/Commands/Def.pm
+++ b/Commands/Def.pm
@@ -53,25 +53,24 @@ sub defcall
 	my $callinfo = "";
 	if ($callnr){
 		my $st = $dbh->prepare(q{
-SELECT covered
+SELECT status
 	,c.landing_tick - (SELECT value::integer FROM misc WHERE id = 'TICK') AS eta
 	,concat(i.shiptype||'/') AS shiptype
 FROM calls c
-	JOIN incomings i ON i.call = c.id
+	JOIN incomings i USING (call)
 	LEFT OUTER JOIN users dc ON dc.uid = c.dc
-	JOIN users u ON u.uid = c.member
-WHERE c.id = ?
-GROUP BY c.id,c.landing_tick,c.covered
+WHERE c.call = ?
+GROUP BY c.call,c.landing_tick,c.status
 ORDER BY c.landing_tick;
 		});
 		my $call = $dbh->selectrow_hashref($st,undef,$callnr);
-		unless (defined $call->{covered}){
+		unless (defined $call->{status}){
 			$c->reply("No call with id: $callnr");
 			return;
 		}
 		chop($call->{shiptype});
 		$callinfo = "(Anti $call->{shiptype} ETA: $call->{eta})";
-		if($call->{covered}){
+		if($call->{status} eq 'Covered'){
 			$c->reply("Call <b>$callnr</b> $callinfo is covered.");
 			return;
 		}
@@ -91,10 +90,10 @@ sub settype
 	my $dbh = $c->model;
 
 	my $query = q{
-SELECT i.id,call,shiptype, coords(x,y,z),c.landing_tick - tick() AS eta
+SELECT inc,call,shiptype, coords(x,y,z),c.landing_tick - tick() AS eta
 FROM incomings i
-	JOIN current_planet_stats p ON i.sender = p.id
-	JOIN calls c ON i.call = c.id
+	JOIN current_planet_stats p USING (pid)
+	JOIN calls c USING (call)
 	};
 	my $fleets;
 	my $type;
@@ -104,20 +103,20 @@ FROM incomings i
 		($call,$type) = $msg =~ /^(\d+) (.*)$/ or die 'ARGS';
 
 		$fleets = $dbh->prepare($query . q{
-WHERE i.call = ?
+WHERE call = ?
 		});
 		$fleets->execute($call);
 	}else{
 		my ($id,$x,$y,$z,$t) = $msg =~ /^(\d+) (\d+):(\d+):(\d+) (.*)$/;
 		if (defined $id){
 			$fleets = $dbh->prepare($query . q{
-WHERE i.call = ? AND p.id = planetid(?,?,?,tick())
+WHERE call = ? AND pid = planetid(?,?,?,tick())
 			});
 			$fleets->execute($id,$x,$y,$z);
 		}else{
 			($id,$t) = $msg =~ /^(\d+) (.*)$/ or die 'ARGS';
 			$fleets = $dbh->prepare($query . q{
-WHERE i.id = ?
+WHERE inc = ?
 			});
 			$fleets->execute($id);
 		}
@@ -127,7 +126,7 @@ WHERE i.id = ?
 	$type = CGI::escapeHTML($type);
 	$dbh->begin_work;
 	my $deflog = '';
-	my $settype = $dbh->prepare(q{UPDATE incomings SET shiptype = ? WHERE id = ?});
+	my $settype = $dbh->prepare(q{UPDATE incomings SET shiptype = ? WHERE inc = ?});
 	while (my $inc = $fleets->fetchrow_hashref){
 		$call //= $inc->{call};
 		if ($inc->{eta} < 0){
@@ -137,9 +136,9 @@ WHERE i.id = ?
 			$dbh->rollback;
 			return;
 		}
-		$settype->execute($type,$inc->{id});
-		$deflog .= "Set fleet: [B]$inc->{id} [/B] to: [B]$type [/B]\n";
-		$c->reply("Set fleet $inc->{id} from $inc->{coords} on call $call to $type (previously $inc->{shiptype})");
+		$settype->execute($type,$inc->{inc});
+		$deflog .= "Set fleet: [B]$inc->{inc} [/B] to: [B]$type [/B]\n";
+		$c->reply("Set fleet $inc->{inc} from $inc->{coords} on call $call to $type (previously $inc->{shiptype})");
 	}
 	if ($fleets->rows == 0){
 		$c->reply("No matching fleets");
@@ -160,26 +159,26 @@ sub calltake
 	my ($id) = $msg =~ /^(\d+)$/ or die 'ARGS';
 	my $dbh = $c->model;
 
-	my $extra = '';
+	my $status = 'Open';
 
 	given ($self->{name}){
 		when('callignore'){
-			$extra = ',covered = FALSE, open = FALSE'
+			$status = 'Ignored';
 		}
 		when('callcov'){
-			$extra = ',covered = TRUE, open = FALSE'
+			$status = 'Covered';
 		}
 	}
 
 	$dbh->begin_work;
 	my $rows = $dbh->do(q{
 UPDATE calls SET dc = (SELECT uid FROM users WHERE hostmask ILIKE $1)
-	}. $extra .q{
-WHERE id = $2
-		},undef,$c->host,$id);
+	,status = $3
+WHERE call = $2
+		},undef,$c->host,$id,$status);
 	if ($rows == 1){
-		$c->reply("Marked call $id with ".$self->name);
-		$c->def_log($id , "Used: [B]".$self->name."[/B]");
+		$c->reply("Setting status on call $id to $status");
+		$c->def_log($id , "Changed status: [B]$status [/B]");
 		$dbh->commit;
 	}else{
 		$c->reply("$id is not a valid call");
diff --git a/Commands/Intel.pm b/Commands/Intel.pm
index 9643333..b9c00e9 100644
--- a/Commands/Intel.pm
+++ b/Commands/Intel.pm
@@ -37,7 +37,7 @@ sub sethostile
 	my $findid = $dbh->prepare(q{SELECT planetid(?,?,?,tick())});
 	my ($id) = $dbh->selectrow_array($findid,undef,$x,$y,$z);
 	$dbh->begin_work;
-	my $rv = $dbh->do(q{UPDATE planets SET planet_status = 'Hostile' WHERE id = $1}
+	my $rv = $dbh->do(q{UPDATE planets SET planet_status = 'Hostile' WHERE pid = $1}
 		,undef,$id);
 	if ($rv == 1){
 		$c->reply("$x:$y:$z is now marked s hostile");
@@ -55,23 +55,23 @@ sub setnick
 	my $dbh = $c->model;
 
 	my $findid = $dbh->prepare(q{
-SELECT nick, id, alliance_id FROM planets WHERE id = planetid(?,?,?,tick())
+SELECT nick, pid, alliance FROM planets WHERE pid = planetid(?,?,?,tick())
 		});
 	my $planet = $dbh->selectrow_hashref($findid,undef,$x,$y,$z);
-	unless($planet->{id}){
+	unless($planet->{pid}){
 		$c->reply("Couldn't find any planet with coords $x:$y:$z");
-	}elsif($planet->{alliance_id} ~~ 1){
+	}elsif($planet->{alliance} ~~ 'NewDawn'){
 		$c->reply("This is an ND planet.");
 	}else{
 		$dbh->begin_work;
-		$dbh->do(q{UPDATE planets SET nick = $1 WHERE id = $2}
-			,undef,$nick,$planet->{id});
+		$dbh->do(q{UPDATE planets SET nick = $1 WHERE pid = $2}
+			,undef,$nick,$planet->{pid});
 		if ($planet->{nick}){
 			$c->reply("$x:$y:$z nick has been changed from <b>$planet->{nick}</b> to <b>$nick</b>");
 		}else{
 			$c->reply("$x:$y:$z nick has been set to $nick");
 		}
-		$c->intel_log($planet->{id},"Set nick to: $nick");
+		$c->intel_log($planet->{pid},"Set nick to: $nick");
 		$dbh->commit;
 	}
 }
@@ -87,14 +87,14 @@ sub setally
 	my $aid;
 	if ($ally ne 'unknown'){
 		($aid,$ally) = $dbh->selectrow_array(q{
-SELECT id,name FROM alliances WHERE name ILIKE ?
+SELECT aid,alliance FROM alliances WHERE alliance ILIKE ?
 		},undef,$ally);
 	}
 	if ($aid ~~ 1){
 		$c->reply("Don't set ND planets manually.");
 	}elsif ($ally){
 		my $findid = $dbh->prepare(q{
-SELECT id,alliance,alliance_id FROM current_planet_stats WHERE x = ? AND y = ? and z = ?
+SELECT pid,alliance,aid FROM current_planet_stats WHERE x = ? AND y = ? and z = ?
 		});
 		my ($id,$alliance,$alliance_id) = $dbh->selectrow_array($findid,undef,$x,$y,$z);
 		unless ($id){
@@ -105,8 +105,8 @@ SELECT id,alliance,alliance_id FROM current_planet_stats WHERE x = ? AND y = ? a
 			$c->reply("$x:$y:$z is already set to $ally");
 		}else{
 			$dbh->begin_work;
-			$dbh->do(q{UPDATE planets SET alliance_id = $1 WHERE id = $2}
-				,undef,$aid,$id);
+			$dbh->do(q{UPDATE planets SET alliance = $1 WHERE pid = $2}
+				,undef,$ally,$id);
 			if (defined $alliance){
 				$c->reply("Changed $x:$y:$z from <b>$alliance</b> to <b>$ally</b>");
 			}else{
@@ -128,14 +128,14 @@ sub setchannel
 	my ($x,$y,$z,$channel) = $msg =~ /^(\d+)\D(\d+)\D(\d+) (\S+)$/ or die 'ARGS';
 	my $dbh = $c->model;
 
-	my $findid = $dbh->prepare_cached(q{SELECT id,channel FROM current_planet_stats
+	my $findid = $dbh->prepare_cached(q{SELECT pid,channel FROM current_planet_stats
 		WHERE x = ? AND y = ? and z = ?});
 	my ($id,$oc) = $dbh->selectrow_array($findid,undef,$x,$y,$z);
 	if ($channel ~~ $oc){
 		$c->reply("$x:$y:$z already got $oc as channel");
 	}elsif($id){
 		$dbh->begin_work;
-		$dbh->do(q{UPDATE planets SET channel = $1 WHERE id = $2}
+		$dbh->do(q{UPDATE planets SET channel = $1 WHERE pid = $2}
 			,undef,$channel,$id);
 		$c->intel_log($id,"Set channel to: $channel");
 		$dbh->commit;
diff --git a/Commands/Members.pm b/Commands/Members.pm
index 44cfe79..1a759b7 100644
--- a/Commands/Members.pm
+++ b/Commands/Members.pm
@@ -35,11 +35,10 @@ sub def
 SELECT (c.landing_tick - tick()) AS eta, concat(i.shiptype||'/') AS shiptype
 	, dc.username
 FROM calls c
-	JOIN incomings i ON i.call = c.id
+	JOIN incomings i USING (call)
 	LEFT OUTER JOIN users dc ON dc.uid = c.dc
-	JOIN users u ON u.uid = c.member
-WHERE open AND (c.landing_tick - tick()) >= 7
-GROUP BY c.id,c.landing_tick,dc.username
+WHERE status = 'Open' AND (c.landing_tick - tick()) >= 7
+GROUP BY call,c.landing_tick,dc.username
 ORDER BY c.landing_tick;
 		});
 	$f->execute();
diff --git a/Commands/PA.pm b/Commands/PA.pm
index 808f409..2dcb006 100644
--- a/Commands/PA.pm
+++ b/Commands/PA.pm
@@ -126,7 +126,7 @@ sub xp
 
 	my ($avalue,$ascore) = $c->model->selectrow_array(q{
 SELECT value,score FROM current_planet_stats
-WHERE id = (SELECT planet FROM users WHERE hostmask ILIKE ?)
+WHERE pid = (SELECT pid FROM users WHERE hostmask ILIKE ?)
 		}, undef, $c->host);
 	my ($tvalue,$tscore,$tsize) = $c->model->selectrow_array(q{
 SELECT value,score,size FROM current_planet_stats
@@ -167,7 +167,7 @@ sub fco
 
 	my ($value,$score) = $c->model->selectrow_array(q{
 SELECT value,score FROM planet_stats WHERE tick = $2 AND
-	id = (SELECT planet FROM users WHERE hostmask ILIKE $1)
+	pid = (SELECT pid FROM users WHERE hostmask ILIKE $1)
 		}, undef, $c->host,$tick);
 	unless ($value){
 		$c->reply("You don't have a planet registered.");
@@ -175,7 +175,7 @@ SELECT value,score FROM planet_stats WHERE tick = $2 AND
 	}
 	my $attackers = $c->model->prepare(q{
 SELECT coords(p.x,p.y,p.z), ruler, planet FROM current_planet_stats p
-	JOIN planet_stats ps using (id)
+	JOIN planet_stats ps using (pid)
 WHERE ps.tick = $1 AND trunc(2000.0*$2*$3/ps.value)::int = $4
 		});
 	$attackers->execute($tick,$agents,$value,$stolen);
diff --git a/Commands/Scans.pm b/Commands/Scans.pm
index ed0def8..31eea44 100644
--- a/Commands/Scans.pm
+++ b/Commands/Scans.pm
@@ -61,7 +61,7 @@ sub gs
 
 	my $query = $c->model->prepare(q{SELECT scan_id
 		FROM scans
-		WHERE planet = $1 AND type = $2 AND tick >= tick()});
+		WHERE pid = $1 AND type = $2 AND tick >= tick()});
 	$query->execute($planet,$type);
 
 	if (my $scan = $query->fetchrow_hashref){
@@ -71,7 +71,7 @@ sub gs
 		my $req = $c->model->prepare(q{
 SELECT * FROM scan_requests
 WHERE uid = (SELECT uid FROM users WHERE hostmask ILIKE $1)
-	AND planet = $2 AND type = $3 AND NOT sent
+	AND pid = $2 AND type = $3 AND NOT sent
 		});
 		$req->execute($c->host,$planet,$type);
 
@@ -85,7 +85,7 @@ WHERE id = $2
 			$id = $scan->{id};
 		}else{
 			$req = $c->model->prepare(q{
-INSERT INTO scan_requests (uid,nick,planet,type)
+INSERT INTO scan_requests (uid,nick,pid,type)
 VALUES((SELECT uid FROM users WHERE hostmask ILIKE $1),$2,$3,$4) RETURNING (id)
 			});
 			$req->execute($c->host,$c->nick,$planet,$type);
@@ -128,10 +128,10 @@ sub scanreqs
 	my $reqs = $c->model->prepare(q{
 SELECT min(sr.id) AS id, x,y,z,type
 FROM scan_requests sr
-	JOIN current_planet_stats p ON p.id = sr.planet
+	JOIN current_planet_stats p USING (pid)
 WHERE sr.time > NOW() - '30 min'::INTERVAL
 	AND NOT EXISTS (SELECT scan_id FROM scans
-		WHERE planet = sr.planet
+		WHERE pid = sr.pid
 			AND type = sr.type
 			AND tick >= sr.tick
 	)
@@ -161,7 +161,7 @@ sub scan
 
 	my $scan = $c->model->selectrow_hashref(q{
 SELECT id, scan_id, tick FROM scans
-WHERE type = $1 AND planet = planetid($2,$3,$4,0) AND COALESCE(tick < $5,TRUE)
+WHERE type = $1 AND pid = planetid($2,$3,$4,0) AND COALESCE(tick < $5,TRUE)
 ORDER BY tick DESC LIMIT 1
 			},undef,$type,$x,$y,$z,$tick);
 	unless ($scan->{id}){
diff --git a/Commands/Usermgm.pm b/Commands/Usermgm.pm
index 247fb33..e856385 100644
--- a/Commands/Usermgm.pm
+++ b/Commands/Usermgm.pm
@@ -259,10 +259,10 @@ sub getships
 	my $f = $dbh->prepare(q{
 SELECT username,SUM(fs.amount) AS amount
 FROM users u
-	JOIN (SELECT DISTINCT ON (planet) planet,fid FROM fleets
+	JOIN (SELECT DISTINCT ON (pid) pid,fid FROM fleets
 		WHERE mission = 'Full fleet' AND name <> 'Unit'
-		ORDER BY planet,tick DESC,fid DESC
-	) f  USING (planet)
+		ORDER BY pid,tick DESC,fid DESC
+	) f  USING (pid)
 	JOIN fleet_ships fs USING (fid)
 WHERE ship ILIKE $1 AND uid IN (SELECT uid FROM groupmembers WHERE gid = 2)
 GROUP BY username ORDER BY amount DESC
@@ -304,10 +304,10 @@ SELECT fs.ship, fs.amount, username
 FROM fleet_ships fs
 	JOIN (SELECT fid,username
 		FROM fleets f
-			JOIN users u USING (planet)
+			JOIN users u USING (pid)
 		WHERE mission = 'Full fleet' AND name <> 'Unit'
 			AND username ILIKE $1
-		ORDER BY planet,tick DESC,fid DESC
+		ORDER BY pid,tick DESC,fid DESC
 		LIMIT 1
 	) f  USING (fid)
 ORDER BY num
diff --git a/Context.pm b/Context.pm
index 124da62..abe59b2 100644
--- a/Context.pm
+++ b/Context.pm
@@ -113,7 +113,7 @@ sub intel_log {
 	my ($c,$planet, $message) = @_;
 	my $log = $c->model->prepare_cached(q{
 INSERT INTO forum_posts (ftid,uid,message) VALUES(
-	(SELECT ftid FROM planets WHERE id = $3)
+	(SELECT ftid FROM planets WHERE pid = $3)
 	,(SELECT uid FROM users WHERE hostmask ILIKE $1)
 	,$2)
 		});
@@ -124,7 +124,7 @@ sub def_log {
 	my ($c,$call, $message) = @_;
 	my $log = $c->model->prepare(q{
 INSERT INTO forum_posts (ftid,uid,message) VALUES(
-	(SELECT ftid FROM calls WHERE id = $3)
+	(SELECT ftid FROM calls WHERE call = $3)
 	,(SELECT uid FROM users WHERE hostmask ILIKE $1),$2)
 		});
 	$log->execute($c->host,$message,$call);
diff --git a/ndawn.pl b/ndawn.pl
index b8d35bb..0de8351 100644
--- a/ndawn.pl
+++ b/ndawn.pl
@@ -55,7 +55,7 @@ my $TICK = $DBH->selectrow_array('SELECT tick()');
 
 my $disp = new NDIRC::Dispatcher;
 
-$disp->load('Basic','SMS');
+$disp->load(qw/Basic SMS Channel Def Intel Members PA Quotes Scans Usermgm/);
 
 $ND::scanchan = '#testarmer';
 $ND::defchan = '#testarlite';