From: Michael Andreen Date: Thu, 30 Jul 2009 15:27:10 +0000 (+0200) Subject: Change the calls and incomings structure X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=aa42aa5497f10f957d426f5777a746f3af49f534 Change the calls and incomings structure Rename the call id to call, and the incoming id to inc, so it is unique within the database and thus can be used in natural join and join .. using. Remove covered and open variables and replace with a status. Different statuses can be added to call_statuses table, but at the moment only Open, Covered and Ignored is supported. --- diff --git a/database/calls.sql b/database/calls.sql new file mode 100644 index 0000000..0cd2410 --- /dev/null +++ b/database/calls.sql @@ -0,0 +1,60 @@ +DROP VIEW IF EXISTS full_defcalls; +DROP VIEW IF EXISTS defcalls; + +CREATE TABLE call_statuses ( + status TEXT PRIMARY KEY +); + +INSERT INTO call_statuses VALUES('Open'),('Covered'),('Ignored'); + +ALTER TABLE calls ADD COLUMN status TEXT NOT NULL REFERENCES call_statuses(status) +DEFAULT 'Open'; + +UPDATE calls SET status = (CASE WHEN covered THEN 'Covered' WHEN NOT OPEN THEN + 'Ignored' ELSE 'Open' END); + +ALTER TABLE calls DROP COLUMN open; +ALTER TABLE calls DROP COLUMN covered; +ALTER TABLE calls DROP COLUMN shiptypes; +ALTER TABLE calls RENAME id TO call; +ALTER TABLE calls RENAME member TO uid; +ALTER TABLE incomings RENAME id TO inc; + +CREATE OR REPLACE VIEW defcalls AS +SELECT call, status,c.uid, c.landing_tick + ,dc.username AS dc, (c.landing_tick - tick()) AS curreta + ,array_agg(COALESCE(race::text,'')) AS race + ,array_agg(COALESCE(amount,0)) AS amount + ,array_agg(COALESCE(eta,0)) AS eta + ,array_agg(COALESCE(shiptype,'')) AS shiptype + ,array_agg(COALESCE(alliance,'?')) AS alliance + ,array_agg(coords(p2.x,p2.y,p2.z)) AS attackers +FROM calls c + LEFT OUTER JOIN incomings i USING (call) + LEFT OUTER JOIN current_planet_stats p2 USING (pid) + LEFT OUTER JOIN users dc ON c.dc = dc.uid +GROUP BY call,c.uid,dc.username, c.landing_tick, status; + +CREATE OR REPLACE VIEW full_defcalls AS +SELECT call,status,x,y,z,pid,landing_tick,dc,curreta + ,defprio, c.race, amount, c.eta, shiptype, c.alliance, attackers + ,COUNT(NULLIF(f.back = f.landing_tick + f.eta - 1, FALSE)) AS fleets +FROM users_defprio u + JOIN current_planet_stats p USING (pid) + JOIN defcalls c USING (uid) + LEFT OUTER JOIN launch_confirmations f USING (pid,landing_tick) +GROUP BY call, x,y,z,pid,landing_tick,dc,curreta,defprio,c.race,amount,c.eta,shiptype,c.alliance,attackers, status +; + +CREATE OR REPLACE FUNCTION add_call() RETURNS trigger + AS $_X$ +DECLARE + thread INTEGER; +BEGIN + INSERT INTO forum_threads (fbid,subject,uid) + VALUES(-3,NEW.uid || ': ' || NEW.landing_tick,-3) RETURNING ftid + INTO STRICT thread; + NEW.ftid = thread; + RETURN NEW; +END; +$_X$ LANGUAGE plpgsql; diff --git a/lib/NDWeb/Controller/Alliances.pm b/lib/NDWeb/Controller/Alliances.pm index efec3dd..348884a 100644 --- a/lib/NDWeb/Controller/Alliances.pm +++ b/lib/NDWeb/Controller/Alliances.pm @@ -189,12 +189,12 @@ sub hostile : Local { } my $query = $dbh->prepare(q{ - SELECT s.alliance_id AS id,s.alliance AS name,count(*) AS hostile_count + SELECT s.aid AS id,s.alliance AS name,count(*) AS hostile_count FROM calls c - JOIN incomings i ON i.call = c.id - JOIN current_planet_stats s ON i.sender = s.id + JOIN incomings i USING (call) + JOIN current_planet_stats s USING (pid) WHERE c.landing_tick - i.eta > $1 and c.landing_tick - i.eta < $2 -GROUP BY s.alliance_id,s.alliance +GROUP BY s.aid,s.alliance ORDER BY hostile_count DESC }); $query->execute($begintick,$endtick); diff --git a/lib/NDWeb/Controller/Calls.pm b/lib/NDWeb/Controller/Calls.pm index 5c3298a..467c732 100644 --- a/lib/NDWeb/Controller/Calls.pm +++ b/lib/NDWeb/Controller/Calls.pm @@ -34,17 +34,17 @@ sub list : Local { my ( $self, $c, $type ) = @_; my $dbh = $c->model; - my $where = q{open AND landing_tick-6 > tick()}; + my $where = q{status = 'Open' AND landing_tick-6 > tick()}; my $order = q{landing_tick DESC, defprio DESC}; if (defined $type){ if ($type eq 'covered'){ - $where = 'covered'; + $where = q{status = 'Covered'}; }elsif ($type eq 'all'){ $where = 'true'; }elsif ($type eq 'uncovered'){ - $where = 'not covered'; + $where = q{status <> 'Covered'}; }elsif ($type eq 'recent'){ - $where = q{landing_tick > tick()}; + $where = q{landing_tick >= tick()}; $order = q{x,y,z}; } } @@ -90,6 +90,7 @@ sub edit : Local { mission = 'Full fleet' OR tick >= $2 - 12 ) + ORDER BY mission,name,tick DESC ) UNION ( SELECT 2 AS type, MAX(fid) AS fid,mission,name,landing_tick AS tick, eta, amount , coords(x,y,z), pid AS planet, back @@ -114,7 +115,7 @@ sub edit : Local { my $ships = $dbh->prepare(q{SELECT ship,amount FROM fleet_ships WHERE fid = ? ORDER BY num }); - $outgoings->execute($call->{planet},$call->{landing_tick}); + $outgoings->execute($call->{pid},$call->{landing_tick}); my @fleets; while (my $fleet = $outgoings->fetchrow_hashref){ if (defined $fleet->{back} && @@ -136,7 +137,7 @@ sub edit : Local { my $available = $dbh->prepare(q{ SELECT ship,amount from ships_home WHERE planet = $1 AND tick = $2 }); - $available->execute($call->{planet}, $call->{landing_tick}); + $available->execute($call->{pid}, $call->{landing_tick}); my $fleet = {fid => $call->{member}, mission => 'Available' , name => 'At home', ships => $available->fetchall_arrayref({}) }; @@ -159,7 +160,7 @@ WHERE mission = 'Defend' ORDER BY x,y,z }); - $defenders->execute($call->{planet},$call->{landing_tick}); + $defenders->execute($call->{pid},$call->{landing_tick}); my @defenders; while (my $fleet = $defenders->fetchrow_hashref){ $ships->execute($fleet->{fid}); @@ -176,16 +177,16 @@ 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,pid AS planet + ,i.fleet,i.shiptype,p.relationship,p.alliance,inc,pid FROM incomings i JOIN current_planet_stats p USING (pid) WHERE i.call = ? ORDER BY p.x,p.y,p.z }); - $attackers->execute($call->{id}); + $attackers->execute($call->{call}); my @attackers; while(my $attacker = $attackers->fetchrow_hashref){ - $outgoings->execute($attacker->{planet},$call->{landing_tick}); + $outgoings->execute($attacker->{pid},$call->{landing_tick}); my @missions; while (my $mission = $outgoings->fetchrow_hashref){ if ($mission->{fid}){ @@ -205,24 +206,30 @@ ORDER BY x,y,z $c->stash(attackers => \@attackers); $c->forward('/forum/findPosts',[$call->{ftid}]); + + my $statuses = $dbh->prepare(q{ +SELECT status FROM call_statuses + }); + $statuses->execute; + $c->stash(statuses => $statuses->fetchall_arrayref({})); } sub defleeches : Local { my ( $self, $c, $type ) = @_; my $dbh = $c->model; - my $query = $dbh->prepare(q{SELECT username,defense_points,count(id) AS calls + my $query = $dbh->prepare(q{SELECT username,defense_points,count(call) 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.back) AS fleets + FROM (SELECT username,defense_points,call,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 + FROM users u JOIN calls c USING (uid) LEFT OUTER JOIN ( SELECT lc.pid,landing_tick,eta,back FROM launch_confirmations lc JOIN fleets f USING (fid) WHERE mission = 'Defend' ) f USING (pid,landing_tick) - GROUP BY username,defense_points,c.id + GROUP BY username,defense_points,call ) d GROUP BY username,defense_points ORDER BY fleets DESC, defense_points }); @@ -238,7 +245,7 @@ sub postcallcomment : Local { $call = $c->stash->{call}; $c->forward('/forum/insertPost',[$call->{ftid}]); - $c->res->redirect($c->uri_for('edit',$call->{id})); + $c->res->redirect($c->uri_for('edit',$call->{call})); } sub postcallupdate : Local { @@ -254,42 +261,39 @@ sub postcallupdate : Local { $dbh->begin_work; if ($c->req->param('cmd') eq 'Submit'){ + my $logmess = ''; if ($c->req->param('ctick')){ - $dbh->do(q{UPDATE calls SET landing_tick = ? WHERE id = ?} - ,undef,$c->req->param('tick'),$call->{id}); - $log->execute($c->user->id,$call->{ftid} - ,"Updated landing tick from [B] $call->{landing_tick} [/B]"); + $dbh->do(q{UPDATE calls SET landing_tick = ? WHERE call = ?} + ,undef,$c->req->param('tick'),$call->{call}); + $logmess .= "Updated landing tick from [B] $call->{landing_tick} [/B]\n"; } if ($c->req->param('cinfo')){ - $dbh->do(q{UPDATE calls SET info = ? WHERE id = ?} - ,undef,$c->req->param('info'),$call->{id}); - $log->execute($c->user->id,$call->{ftid},"Updated info"); + $dbh->do(q{UPDATE calls SET info = ? WHERE call = ?} + ,undef,$c->req->param('info'),$call->{call}); + $logmess .= "Updated info\n"; } if ($c->req->param('ccalc')){ my $calc = $c->req->param('calc'); - $dbh->do(q{UPDATE calls SET calc = ? WHERE id = ?} - ,undef,$calc,$call->{id}); - $log->execute($c->user->id,$call->{ftid},html_escape('Updated calc to: [URL]'.$calc.'[/URL]')); + $dbh->do(q{UPDATE calls SET calc = ? WHERE call = ?} + ,undef,$calc,$call->{call}); + $logmess .= html_escape('Updated calc to: [URL]'.$calc."[/URL]\n"); } - }elsif($c->req->param('cmd') =~ /^(Cover|Uncover|Ignore|Open|Take) call$/){ - my $extra_vars = ''; - if ($1 eq 'Cover'){ - $extra_vars = ", covered = TRUE, open = FALSE"; - }elsif ($1 eq 'Uncover'){ - $extra_vars = ", covered = FALSE, open = TRUE"; - }elsif ($1 eq 'Ignore'){ - $extra_vars = ", covered = FALSE, open = FALSE"; - }elsif ($1 eq 'Open'){ - $extra_vars = ", covered = FALSE, open = TRUE"; + if ($c->req->param('cstatus')){ + $dbh->do(q{UPDATE calls SET status = $1, dc = $2 WHERE call = $3} + ,undef,$c->req->param('status'),$c->user->id,$call->{call}); + $logmess .= "Changed status to: ".$c->req->param('status')."\n"; } - $dbh->do(qq{UPDATE calls SET dc = ? $extra_vars WHERE id = ?}, - ,undef,$c->user->id,$call->{id}); + $log->execute($c->user->id,$call->{ftid},$logmess) if $log; + }elsif($c->req->param('cmd') =~ /^Take call$/){ + my $extra_vars = ''; + $dbh->do(q{UPDATE calls SET dc = ? WHERE call = ?}, + ,undef,$c->user->id,$call->{call}); $log->execute($c->user->id,$call->{ftid} ,'Changed status to: [B]'.$c->req->param('cmd').'[/B]'); } $dbh->commit; - $c->res->redirect($c->uri_for('edit',$call->{id})); + $c->res->redirect($c->uri_for('edit',$call->{call})); } @@ -306,24 +310,24 @@ sub postattackerupdate : Local { $dbh->begin_work; if($c->req->param('cmd') eq 'Remove'){ - my $query = $dbh->prepare(q{DELETE FROM incomings WHERE id = ? AND call = ?}); - my $inc = $dbh->prepare(q{SELECT sender,eta,amount FROM incomings WHERE id = $1}); + my $query = $dbh->prepare(q{DELETE FROM incomings WHERE inc = ? AND call = ?}); + my $inc = $dbh->prepare(q{SELECT pid,eta,amount FROM incomings WHERE inc = $1}); for my $param ($c->req->param()){ if ($param =~ /^change:(\d+)$/){ my ($planet,$eta,$amount) = $dbh->selectrow_array($inc,undef,$1); - $query->execute($1,$call->{id}); + $query->execute($1,$call->{call}); $log->execute($c->user->id,$call->{ftid} ,"Deleted fleet: [B] $1 [/B] ($planet:$eta:$amount)"); } } }elsif($c->req->param('cmd') eq 'Change'){ my $query = $dbh->prepare(q{UPDATE incomings SET shiptype = ? - WHERE id = ? AND call = ? + WHERE inc = ? AND call = ? }); for my $param ($c->req->param()){ if ($param =~ /^change:(\d+)$/){ my $shiptype = html_escape($c->req->param("shiptype:$1")); - $query->execute($shiptype,$1,$call->{id}); + $query->execute($shiptype,$1,$call->{call}); $log->execute($c->user->id,$call->{ftid} ,"set fleet: [B] $1 [/B] to: [B] $shiptype [/B]"); } @@ -331,7 +335,7 @@ sub postattackerupdate : Local { } $dbh->commit; - $c->res->redirect($c->uri_for('edit',$call->{id})); + $c->res->redirect($c->uri_for('edit',$call->{call})); } sub findCall : Private { @@ -339,14 +343,14 @@ sub findCall : Private { my $dbh = $c->model; 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 - ,p.pid AS planet, u.username AS member, u.sms,c.ftid,calc - FROM calls c - JOIN users u ON c.member = u.uid + SELECT call, coords(p.x,p.y,p.z), c.landing_tick, c.info, status + ,dc.username AS dc, u.defense_points,c.uid + ,p.pid, u.username AS member, u.sms,c.ftid,calc + FROM calls c + JOIN users u USING (uid) JOIN current_planet_stats p USING (pid) LEFT OUTER JOIN users dc ON c.dc = dc.uid - WHERE c.id = ? + WHERE call = ? }); $call = $dbh->selectrow_hashref($query,undef,$call); diff --git a/lib/NDWeb/Controller/Intel.pm b/lib/NDWeb/Controller/Intel.pm index 388204b..64fe55a 100644 --- a/lib/NDWeb/Controller/Intel.pm +++ b/lib/NDWeb/Controller/Intel.pm @@ -227,15 +227,15 @@ sub members : Local { ,count(CASE WHEN i.mission = 'Attack' THEN 1 ELSE NULL END) AS attacks ,count(CASE WHEN (i.mission = 'Defend' OR i.mission = 'AllyDef') THEN 1 ELSE NULL END) AS defenses ,count(CASE WHEN i.mission = 'Attack' AND rt.id IS NULL THEN 1 ELSE NULL END) AS solo - ,count(CASE WHEN i.mission = 'Defend' OR i.mission = 'AllyDef' THEN NULLIF(i.ingal OR (t.alliance_id = 1),TRUE) ELSE NULL END) AS bad_def + ,count(CASE WHEN i.mission = 'Defend' OR i.mission = 'AllyDef' THEN NULLIF(i.ingal OR (t.alliance = 'NewDawn'),TRUE) ELSE NULL END) AS bad_def FROM users u JOIN groupmembers gm USING (uid) - LEFT OUTER JOIN (SELECT DISTINCT ON (planet) planet,tick from scans where type = 'News' ORDER BY planet,tick DESC) n USING (planet) - LEFT OUTER JOIN (SELECT DISTINCT name,eta,tick,sender,target,mission,ingal FROM intel WHERE amount IS NULL) i ON i.sender = u.planet - LEFT OUTER JOIN current_planet_stats t ON i.target = t.id - LEFT OUTER JOIN (SELECT rt.id,planet,tick FROM raids r - JOIN raid_targets rt ON r.id = rt.raid) rt ON rt.planet = i.target - AND (rt.tick + 12) > i.tick AND rt.tick <= i.tick + LEFT OUTER JOIN (SELECT DISTINCT ON (pid) pid,tick from scans where type = 'News' ORDER BY pid,tick DESC) n USING (pid) + LEFT OUTER JOIN (SELECT DISTINCT name,eta,tick,sender,target,mission,ingal FROM intel WHERE amount IS NULL) i ON i.sender = u.pid + LEFT OUTER JOIN current_planet_stats t ON i.target = t.pid + LEFT OUTER JOIN (SELECT rt.id,pid,tick FROM raids r + JOIN raid_targets rt ON r.id = rt.raid) rt ON rt.pid = i.target + AND (rt.tick + 12) > i.tick AND rt.tick <= i.tick LEFT OUTER JOIN raid_claims rc ON rt.id = rc.target AND rc.uid = u.uid AND i.tick = rt.tick + rc.wave - 1 WHERE gm.gid = 2 GROUP BY u.uid,u.username,u.attack_points, u.defense_points,n.tick @@ -251,16 +251,16 @@ sub member : Local { my $query = $dbh->prepare(q{ SELECT coords(t.x,t.y,t.z), i.eta, i.tick, rt.id AS ndtarget, rc.launched, inc.landing_tick FROM users u - LEFT OUTER JOIN (SELECT DISTINCT eta,tick,sender,target,mission,name FROM intel WHERE amount IS NULL) i ON i.sender = u.planet - LEFT OUTER JOIN current_planet_stats t ON i.target = t.id - LEFT OUTER JOIN (SELECT rt.id,planet,tick FROM raids r - JOIN raid_targets rt ON r.id = rt.raid) rt ON rt.planet = i.target - AND (rt.tick + 12) > i.tick AND rt.tick <= i.tick + LEFT OUTER JOIN (SELECT DISTINCT eta,tick,sender,target,mission,name FROM intel WHERE amount IS NULL) i ON i.sender = u.pid + LEFT OUTER JOIN current_planet_stats t ON i.target = t.pid + LEFT OUTER JOIN (SELECT rt.id,pid,tick FROM raids r + JOIN raid_targets rt ON r.id = rt.raid) rt ON rt.pid = i.target + AND (rt.tick + 12) > i.tick AND rt.tick <= i.tick LEFT OUTER JOIN raid_claims rc ON rt.id = rc.target AND rc.uid = u.uid AND i.tick = rt.tick + rc.wave - 1 - LEFT OUTER JOIN (SELECT sender, eta, landing_tick FROM calls c - JOIN incomings i ON i.call = c.id) inc ON inc.sender = i.target + LEFT OUTER JOIN (SELECT pid, eta, landing_tick FROM calls c + JOIN incomings i USING (call)) inc ON inc.pid = i.target AND (inc.landing_tick + inc.eta) >= i.tick - AND (inc.landing_tick - inc.eta - 1) <= (i.tick - i.eta) + AND (inc.landing_tick - inc.eta - 1) <= (i.tick - i.eta) WHERE u.uid = $1 AND i.mission = 'Attack' ORDER BY (i.tick - i.eta) }); @@ -286,10 +286,10 @@ sub member : Local { $c->stash(attacks => \@attacks); $query = $dbh->prepare(q{ - SELECT coords(t.x,t.y,t.z),t.alliance_id, t.alliance, i.eta, i.tick, i.ingal + SELECT coords(t.x,t.y,t.z),t.aid, t.alliance, i.eta, i.tick, i.ingal FROM users u - JOIN (SELECT DISTINCT name,eta,tick,sender,target,mission,ingal FROM intel WHERE amount IS NULL) i ON i.sender = u.planet - LEFT OUTER JOIN current_planet_stats t ON i.target = t.id + JOIN (SELECT DISTINCT name,eta,tick,sender,target,mission,ingal FROM intel WHERE amount IS NULL) i ON i.sender = u.pid + LEFT OUTER JOIN current_planet_stats t ON i.target = t.pid WHERE u.uid = $1 AND (i.mission = 'Defend' OR i.mission = 'AllyDef') ORDER BY (i.tick - i.eta) }); @@ -299,7 +299,7 @@ sub member : Local { my @other_def; while (my $intel = $query->fetchrow_hashref){ my $def = {target => $intel->{coords}, other => $intel->{alliance}, tick => $intel->{tick}}; - if (defined $intel->{alliance_id} && $intel->{alliance_id} == 1){ + if ($intel->{alliance} ~~ 'NewDawn'){ push @nd_def, $def; }elsif($intel->{ingal}){ push @ingal_def, $def; diff --git a/lib/NDWeb/Controller/Members.pm b/lib/NDWeb/Controller/Members.pm index 10e8fbd..fc440a3 100644 --- a/lib/NDWeb/Controller/Members.pm +++ b/lib/NDWeb/Controller/Members.pm @@ -61,6 +61,7 @@ sub index : Path : Args(0) { my $calls = $dbh->prepare(q{ SELECT * FROM defcalls WHERE uid = $1 AND landing_tick >= tick() +ORDER BY landing_tick DESC }); $calls->execute($c->user->id); $c->stash(calls => $calls->fetchall_arrayref({}) ); @@ -423,8 +424,8 @@ sub postconfirmation : Local { 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 + my $finddefensetarget = $dbh->prepare(q{SELECT call FROM calls c + JOIN users u USING (uid) WHERE u.pid = $1 AND c.landing_tick = $2 }); my $informDefChannel = $dbh->prepare(q{INSERT INTO defense_missions @@ -524,7 +525,7 @@ sub postconfirmation : Local { } }elsif ($mission eq 'Defend'){ my $call = $findtarget->fetchrow_hashref; - $informDefChannel->execute($fleet,$call->{id}); + $informDefChannel->execute($fleet,$call->{call}); } $log->execute($c->user->id,"Pasted confirmation for $mission mission to $x:$y:$z, landing tick $tick"); diff --git a/root/src/calls/edit.tt2 b/root/src/calls/edit.tt2 index 7a809c8..9779a0c 100644 --- a/root/src/calls/edit.tt2 +++ b/root/src/calls/edit.tt2 @@ -1,7 +1,7 @@ [% META title = 'Edit call' %] -
-
Call details, call# [% call.id %] + +
Call details, call# [% call.call %]
@@ -23,17 +23,18 @@
  • Calc:
  • Change Notes?
    • +
  • Status:
    • -
    • -
    • Attackers: -
    + @@ -49,18 +50,18 @@ [% FOR a IN attackers %] - + - + - + - +
    [% a.coords %] [% a.planet_status %] [% a.alliance %] ([% a.relationship %]) [% a.race %] [% a.eta %] [% a.amount %] [% a.fleet %]