From 8518049dc07ba0554aea4224d6c195bbacd9b8a6 Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Sun, 20 Sep 2009 15:06:02 +0200 Subject: [PATCH] Restructured launch confirmation and made it slightly more intelligent --- database/claims.sql | 46 ++++ database/triggers.sql | 28 --- lib/NDWeb/Controller/Members.pm | 280 +++++++++++++++--------- root/src/members/launchConfirmation.tt2 | 5 +- 4 files changed, 232 insertions(+), 127 deletions(-) create mode 100644 database/claims.sql diff --git a/database/claims.sql b/database/claims.sql new file mode 100644 index 0000000..3155575 --- /dev/null +++ b/database/claims.sql @@ -0,0 +1,46 @@ +DROP TRIGGER IF EXISTS update_target ON raid_claims; +DROP TRIGGER IF EXISTS unclaim_target ON raid_claims; + +DROP FUNCTION IF EXISTS updated_target(); +DROP FUNCTION IF EXISTS unclaim_target(); + +CREATE OR REPLACE FUNCTION updated_claim() RETURNS trigger + AS $_X$ +DECLARE + target INTEGER; +BEGIN + CASE TG_OP + WHEN 'INSERT' THEN + target := NEW.target; + WHEN 'UPDATE' THEN + target := NEW.target; + IF NEW.launched AND NOT OLD.launched THEN + UPDATE users + SET attack_points = attack_points + 1 + WHERE uid = OLD.uid; + + INSERT INTO forum_posts (ftid,uid,message) + VALUES((SELECT ftid FROM users WHERE uid = NEW.uid),NEW.uid + ,'Gave attack point for confirmation of attack on target ' + || NEW.target || ', wave ' || NEW.wave + ); + END IF; + WHEN 'DELETE' THEN + target := OLD.target; + + IF OLD.launched THEN + UPDATE users + SET attack_points = attack_points - 1 + WHERE uid = OLD.uid; + END IF; + END CASE; + UPDATE raid_targets SET modified = NOW() WHERE id = target; + RETURN NEW; +END; +$_X$ LANGUAGE plpgsql; + + +CREATE TRIGGER updated_claim + AFTER INSERT OR DELETE OR UPDATE ON raid_claims + FOR EACH ROW EXECUTE PROCEDURE updated_claim(); + diff --git a/database/triggers.sql b/database/triggers.sql index 2e6b44c..9e9fd56 100644 --- a/database/triggers.sql +++ b/database/triggers.sql @@ -83,31 +83,3 @@ BEGIN RETURN NEW; END; $_X$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION updated_target() RETURNS trigger - AS $_X$ -DECLARE - target INTEGER; -BEGIN - IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN - target := NEW.target; - ELSIF TG_OP = 'DELETE' THEN - target := OLD.target; - END IF; - UPDATE raid_targets SET modified = NOW() WHERE id = target; - RETURN NEW; -END; -$_X$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION unclaim_target() RETURNS trigger - AS $_X$ -BEGIN - IF OLD.launched THEN - UPDATE users - SET attack_points = attack_points - 1 - WHERE uid = OLD.uid; - END IF; - RETURN NEW; -END; -$_X$ LANGUAGE plpgsql; - diff --git a/lib/NDWeb/Controller/Members.pm b/lib/NDWeb/Controller/Members.pm index cfd6d53..d5b634c 100644 --- a/lib/NDWeb/Controller/Members.pm +++ b/lib/NDWeb/Controller/Members.pm @@ -3,6 +3,7 @@ package NDWeb::Controller::Members; use strict; use warnings; use feature ":5.10"; +use Try::Tiny; use parent 'Catalyst::Controller'; use NDWeb::Include; @@ -455,34 +456,16 @@ sub postconfirmation : Local { my ( $self, $c ) = @_; my $dbh = $c->model; - eval { - my $missions = $c->req->param('mission'); + try { my $findplanet = $dbh->prepare("SELECT planetid(?,?,?,?)"); - my $findattacktarget = $dbh->prepare(q{SELECT c.target,c.wave,c.launched - 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.pid = ? - AND r.open AND not r.removed - }); - 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 - (fleet,call) VALUES (?,?) - }); - my $addattackpoint = $dbh->prepare(q{UPDATE users SET - attack_points = attack_points + 1 WHERE uid = ? - }); - my $launchedtarget = $dbh->prepare(q{UPDATE raid_claims SET launched = True - WHERE uid = ? AND target = ? AND wave = ? - }); my $addfleet = $dbh->prepare(q{INSERT INTO fleets (name,mission,pid,tick,amount) VALUES ($2,$3,(SELECT pid FROM users WHERE uid = $1),tick(),$4) RETURNING fid }); + my $updatefleet = $dbh->prepare(q{ +UPDATE launch_confirmations SET back = $2 WHERE fid = $1 + }); my $addconfirmation = $dbh->prepare(q{INSERT INTO launch_confirmations (fid,uid,pid,landing_tick,eta,back) VALUES ($1,$2,$3,$4,$5,$6) }); @@ -492,100 +475,201 @@ sub postconfirmation : Local { my $log = $dbh->prepare(q{INSERT INTO forum_posts (ftid,uid,message) VALUES( (SELECT ftid FROM users WHERE uid = $1),$1,$2) }); - my @missions; $dbh->begin_work; - while ($missions && $missions =~ m/([^\n]+)\s+(\d+):(\d+):(\d+)\s+(\d+):(\d+):(\d+) - \s+\((?:(\d+)\+)?(\d+)\).*?(?:\d+hrs\s+)?\d+mins?\s+ - (Attack|Defend|Return|Fake\ Attack|Fake\ Defend) - (.*?) - (?:Launching\ in\ tick\ (\d+),\ arrival\ in\ tick\ (\d+) - |ETA:\ \d+,\ Return\ ETA:\ (\d+) - |Return\ ETA:\ (\d+) - )/sgx){ - next if $10 eq 'Return'; - my %mission; - my $name = $1; - my $tick = $c->stash->{TICK}+$9; - $tick += $8 if defined $8; - $tick = $13 if defined $13; - my $eta = $9; - $eta += $14 if defined $14; - my $mission = $10; - my $x = $5; - my $y = $6; - my $z = $7; - my $back = $tick + $eta - 1; - $mission{tick} = $tick; - $mission{mission} = $mission; - $mission{target} = "$x:$y:$z"; - $mission{back} = $back; - - my ($planet_id) = $dbh->selectrow_array($findplanet,undef,$x,$y,$z,$c->stash->{TICK}); - - my $findtarget = $finddefensetarget; - if ($mission eq 'Attack'){ - $findtarget = $findattacktarget; - $findtarget->execute($c->user->id,$tick,$planet_id); - }elsif ($mission eq 'Defend'){ - $findtarget = $finddefensetarget; - $findtarget->execute($planet_id,$tick); + my @missions = parseconfirmations($c->req->param('mission'), $c->stash->{TICK}); + for my $m (@missions){ + if ($m->{mission} eq 'Return'){ + $c->forward("addReturnFleet", [$m]); + $updatefleet->execute($m->{fid},$m->{back}) if $m->{fid}; + next; } - - my $ships = $11; - my @ships; - my $amount = 0; - while ($ships =~ m/((?:\w+ )*\w+)\s+\w+\s+(?:(?:\w+|-)\s+){3}(?:Steal|Normal|Emp|Normal\s+Cloaked|Pod|Structure Killer)\s+(\d+)/g){ - $amount += $2; - push @ships,{ship => $1, amount => $2}; + $m->{pid} = $dbh->selectrow_array($findplanet,undef,@{$m->{target}},$c->stash->{TICK}); + unless ($m->{pid}){ + $m->{warning} = "No planet at @{$m->{target}}, try again next tick."; + next; } - $mission{ships} = \@ships; - if ($amount == 0){ - warn "No ships in: $ships"; + $c->forward("findDuplicateFleet", [$m]); + if ($m->{match}){ + $m->{warning} = "Already confirmed this fleet, changing back to to match this paste"; + $updatefleet->execute($m->{fid},$m->{back}); next; } - my $fleet = $dbh->selectrow_array($addfleet,undef,$c->user->id,$name - ,$mission,$amount); - $addconfirmation->execute($fleet,$c->user->id,$planet_id,$tick,$eta,$back); - $mission{fleet} = $fleet; - for my $ship (@ships){ - $addships->execute($fleet,$ship->{ship},$ship->{amount}); - } - if ($findtarget->rows == 0){ - $mission{warning} = 'No matching target!'; - }elsif ($mission eq 'Attack'){ - my $claim = $findtarget->fetchrow_hashref; - if ($claim->{launched}){ - $mission{warning} = "Already launched on this target:$claim->{target},$claim->{wave},$claim->{launched}"; - }else{ - $addattackpoint->execute($c->user->id); - $launchedtarget->execute($c->user->id,$claim->{target},$claim->{wave}); - $mission{warning} = "OK:$claim->{target},$claim->{wave},$claim->{launched}"; - $log->execute($c->user->id,"Gave attack point for confirmation on $mission mission to $x:$y:$z, landing tick $tick"); - } - }elsif ($mission eq 'Defend'){ - my $call = $findtarget->fetchrow_hashref; - $informDefChannel->execute($fleet,$call->{call}); + $m->{fleet} = $dbh->selectrow_array($addfleet,undef,$c->user->id,$m->{name} + ,$m->{mission},$m->{amount}); + if ($m->{mission} eq 'Attack'){ + $c->forward("addAttackFleet", [$m]); + }elsif ($m->{mission} eq 'Defend'){ + $c->forward("addDefendFleet", [$m]); } - $log->execute($c->user->id,"Pasted confirmation for $mission mission to $x:$y:$z, landing tick $tick"); - push @missions,\%mission; + $addconfirmation->execute($m->{fleet},$c->user->id,$m->{pid},$m->{tick},$m->{eta},$m->{back}); + + for my $ship (@{$m->{ships}}){ + $addships->execute($m->{fleet},$ship->{ship},$ship->{amount}); + } + $log->execute($c->user->id,"Pasted confirmation for $m->{mission} mission to @{$m->{target}}, landing tick $m->{tick}"); } $dbh->commit; $c->flash(missions => \@missions); $c->signal_bots; - }; - if ($@){ + } catch { $dbh->rollback; - if ($@ =~ m/insert or update on table "fleet_ships" violates foreign key constraint "fleet_ships_ship_fkey"\s+DETAIL:\s+Key \(ship\)=\(([^)]+)\)/){ + when (/insert or update on table "fleet_ships" violates foreign key constraint "fleet_ships_ship_fkey"\s+DETAIL:\s+Key \(ship\)=\(([^)]+)\)/){ $c->flash( error => "'$1' is NOT a valid ship"); - }else{ - $c->flash( error => $@); } + default{ + $c->flash( error => $_); + } + }; + $c->res->redirect($c->uri_for('launchConfirmation')); +} + +sub parseconfirmations { + my ( $missions, $tick ) = @_; + return unless $missions; + my @missions; + while ($missions =~ m/\s*([^\n]+?)\s+(\d+):(\d+):(\d+)\s+(\d+):(\d+):(\d+) + \s+\((?:(\d+)\+)?(\d+)\).*?(?:\d+hrs\s+)?\d+mins?\s+ + (Attack|Defend|Return|Fake\ Attack|Fake\ Defend) + (.*?) + (?:Launching\ in\ tick\ (\d+),\ arrival\ in\ tick\ (\d+) + |ETA:\ \d+,\ Return\ ETA:\ (\d+) + |Return\ ETA:\ (\d+) + )/sgx){ + my $tick = $tick; + $tick += $9; + $tick += $8 if defined $8; + $tick = $13 if defined $13; + my $eta = $9; + $eta += $14 if defined $14; + my %mission = ( + name => $1, + mission => $10, + tick => $tick, + eta => $eta, + back => $10 eq 'Return' ? $tick : $tick + $eta - 1, + target => [$5,$6,$7], + from => [$2,$3,$4], + ); + my $ships = $11; + my @ships; + $mission{amount} = 0; + while ($ships =~ m/((?:\w+ )*\w+)\s+\w+\s+(?:(?:\w+|-)\s+){3}(?:Steal|Normal|Emp|Normal\s+Cloaked|Pod|Structure Killer)\s+(\d+)/g){ + $mission{amount} += $2; + push @ships,{ship => $1, amount => $2}; + } + $mission{ships} = \@ships; + + if ($mission{amount} == 0){ + warn "No ships in: $ships"; + next; + } + push @missions,\%mission; } + return @missions; +} + +sub findDuplicateFleet : Private { + my ( $self, $c, $m ) = @_; + my $dbh = $c->model; + + my $findfleet = $dbh->prepare(q{ +SELECT fid FROM fleets f + JOIN launch_confirmations lc USING (fid) +WHERE uid = $1 AND name = $2 AND mission = $3 AND amount = $4 + AND lc.pid = $5 AND landing_tick = $6 + }); + my $fid = $dbh->selectrow_array($findfleet,undef,$c->user->id,$m->{name} + ,$m->{mission},$m->{amount}, $m->{pid}, $m->{tick}); + $c->forward("matchShips", [$m,$fid]); + $m->{fid} = $fid if $m->{match}; +} + +sub addAttackFleet : Private { + my ( $self, $c, $m ) = @_; + my $dbh = $c->model; + + my $findattacktarget = $dbh->prepare(q{ +SELECT c.target,c.wave,c.launched +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.pid = ? + AND r.open AND not r.removed + }); + my $launchedtarget = $dbh->prepare(q{ +UPDATE raid_claims SET launched = TRUE +WHERE uid = ? AND target = ? AND wave = ? + }); + my $claim = $dbh->selectrow_hashref($findattacktarget,undef,$c->user->id,$m->{tick},$m->{pid}); + if ($claim->{launched}){ + $m->{warning} = "Already launched on this target:$claim->{target},$claim->{wave},$claim->{launched}"; + }elsif(defined $claim->{launched}){ + $launchedtarget->execute($c->user->id,$claim->{target},$claim->{wave}); + $m->{warning} = "OK:$claim->{target},$claim->{wave},$claim->{launched}"; + }else{ + $m->{warning} = "You haven't claimed this target"; + } +} + +sub addDefendFleet : Private { + my ( $self, $c, $m ) = @_; + my $dbh = $c->model; + + 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 (fleet,call) VALUES (?,?) + }); + my $call = $dbh->selectrow_hashref($finddefensetarget,undef,$m->{pid},$m->{tick}); + if ($call->{call}){ + $informDefChannel->execute($m->{fleet},$call->{call}); + }else{ + $m->{warning} = "No call for @{$m->{target}} landing tick $m->{tick}"; + } +} + +sub addReturnFleet : Private { + my ( $self, $c, $m ) = @_; + my $dbh = $c->model; + + my $findfleet = $dbh->prepare(q{ +SELECT fid FROM fleets f + JOIN launch_confirmations lc USING (fid) +WHERE uid = $1 AND name = $2 AND amount = $3 + AND back >= $4 + }); + my $fid = $dbh->selectrow_array($findfleet,undef,$c->user->id,$m->{name} + ,$m->{amount}, $m->{tick}); + $c->forward("matchShips", [$m,$fid]); + if ($m->{match}){ + $m->{fid} = $fid; + $m->{warning} = "Return fleet, changed back tick to match the return eta."; + } else { + $m->{warning} = "Couldn't find a fleet matching this returning fleet. Recall manually."; + } +} + +sub matchShips : Private { + my ( $self, $c, $m, $fid ) = @_; + return unless $fid; + my $dbh = $c->model; + + my $ships = $dbh->prepare(q{ +SELECT ship, amount FROM fleet_ships WHERE fid = $1 ORDER BY num + }); + $ships->execute($fid); + for my $s (@{$m->{ships}}){ + my $s2 = $ships->fetchrow_hashref; + return unless $s->{ship} eq $s2->{ship} && $s->{amount} == $s2->{amount}; + } + $m->{match} = 1; - $c->res->redirect($c->uri_for('launchConfirmation')); } sub defenders : Local { diff --git a/root/src/members/launchConfirmation.tt2 b/root/src/members/launchConfirmation.tt2 index 88d1f89..fd5487d 100644 --- a/root/src/members/launchConfirmation.tt2 +++ b/root/src/members/launchConfirmation.tt2 @@ -2,10 +2,13 @@ [% FOR m IN missions %] [% IF m.warning %]

[% m.warning %]

[% END %]

Adding the following fleet: [% m.fleet %] -
Target: [% m.target %] +
Name: [% m.name %] +
Target: [% m.target.join(":") %]
Mission: [% m.mission %]
Landing tick: [% m.tick %] +
ETA: [% m.eta %]
Back: [% m.back %] +
Amount: [% m.amount %]

[% FOR s IN m.ships %] -- 2.39.2