Update to new database structure

diff --git a/Commands/Def.pm b/Commands/Def.pm
index 9b2addbd255489a8fc73b8485cb455fa6851c4a0..1731689e12689e673d06cec3e0d1543e9acbb80c 100644 (file)
--- 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 9643333461973995da6bb958cc78b31d1978bfc9..b9c00e91d20ebe6085208caca59c53321ea59f66 100644 (file)
--- 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 44cfe79d68ac3c62863fb2ec0d2c8f7d017af08f..1a759b7495348a9cb08cee7003f5988e821fb974 100644 (file)
--- 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 808f4092020ef9b67dc9addaa3ba0441009783b7..2dcb00671d0c90ccc3355f138606ffd50ce7100d 100644 (file)
--- 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 ed0def8dc5af3bd3adfafe4c23497ac377b41ea2..31eea44fb9f940800b58132568c4d9cfc673f0a2 100644 (file)
--- 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 247fb33a18f1ed865942b3eca44e4e49e4b81ff5..e85638562653df4206abff60a1ae6a90c578f42d 100644 (file)
--- 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 124da62e5ca301a33da1a18534b24ac6737c51c6..abe59b24cf538b477f794153241a11649a1865b7 100644 (file)
--- 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 b8d35bbd3c451094102097a4b0bdfdfd77100c61..0de8351ac4e80a5ab9a6dd7293f8657582724142 100644 (file)
--- 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';