From 07aec9f5f20e4a605d65bbe2337a05663c0b16e7 Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Sat, 22 Aug 2009 19:51:40 +0200 Subject: [PATCH] Replaced intelquery function with a view --- database/intel.sql | 13 ++++++++++++ lib/NDWeb/Controller/Alliances.pm | 18 ++++++++-------- lib/NDWeb/Controller/Intel.pm | 35 +++++++++++++++++++------------ lib/NDWeb/Include.pm | 14 +------------ root/src/alliances/edit.tt2 | 4 ++-- root/src/intel/index.tt2 | 4 ++-- root/src/intel/planet.tt2 | 4 ++-- 7 files changed, 51 insertions(+), 41 deletions(-) create mode 100644 database/intel.sql diff --git a/database/intel.sql b/database/intel.sql new file mode 100644 index 0000000..38098fc --- /dev/null +++ b/database/intel.sql @@ -0,0 +1,13 @@ +DROP VIEW IF EXISTS full_intel; +CREATE VIEW full_intel AS +SELECT s.alliance AS salliance ,coords(s.x,s.y,s.z) AS scoords, i.sender, s.nick AS snick + ,t.alliance AS talliance,coords(t.x,t.y,t.z) AS tcoords, i.target, t.nick AS tnick + ,i.mission, i.tick, MIN(i.eta) AS eta, i.amount, i.ingal + ,uid,u.username +FROM intel i + JOIN users u USING (uid) + JOIN current_planet_stats t ON i.target = t.pid + JOIN current_planet_stats s ON i.sender = s.pid +GROUP BY i.tick,i.mission,t.x,t.y,t.z,s.x,s.y,s.z,i.amount,i.ingal,u.username,uid + ,t.alliance,s.alliance,t.nick,s.nick,i.sender,i.target +; diff --git a/lib/NDWeb/Controller/Alliances.pm b/lib/NDWeb/Controller/Alliances.pm index 348884a..5f33726 100644 --- a/lib/NDWeb/Controller/Alliances.pm +++ b/lib/NDWeb/Controller/Alliances.pm @@ -4,8 +4,6 @@ use strict; use warnings; use parent 'Catalyst::Controller'; -use NDWeb::Include; - =head1 NAME NDWeb::Controller::Alliances - Catalyst Controller @@ -95,13 +93,15 @@ sub edit : Local { my $ticks = $c->req->param('ticks') || 48; $c->stash(showticks => $ticks); - $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 = $1 OR t.alliance = $1) - AND (i.mission = 'Defend' OR i.mission = 'AllyDef') - AND COALESCE( t.alliance != o.alliance, TRUE) - AND i.tick > (tick() - $2) + $query = $dbh->prepare(q{ +SELECT salliance, scoords, sender, talliance, tcoords, target + ,mission, tick AS landingtick, eta, amount, ingal, username +FROM full_intel +WHERE NOT ingal AND (salliance = $1 OR talliance = $1) + AND (mission = 'Defend' OR mission = 'AllyDef') + AND COALESCE( talliance <> salliance, TRUE) + AND tick > (tick() - $2) +ORDER BY tick DESC, mission }); $query->execute($a->{name}, $ticks); $c->stash(intel => $query->fetchall_arrayref({}) ); diff --git a/lib/NDWeb/Controller/Intel.pm b/lib/NDWeb/Controller/Intel.pm index 015fe33..bafea58 100644 --- a/lib/NDWeb/Controller/Intel.pm +++ b/lib/NDWeb/Controller/Intel.pm @@ -31,13 +31,14 @@ sub index :Path : Args(0) { my $ticks = $c->req->param('ticks') || 48; $c->stash(showticks => $ticks); - my $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 ((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) + my $query = $dbh->prepare(q{ +SELECT salliance, scoords, sender, talliance, tcoords, target + ,mission, tick AS landingtick, eta, amount, ingal, username +FROM full_intel +WHERE NOT ingal AND tick > (tick() - $1) + AND ((COALESCE( talliance <> salliance,TRUE) AND (mission = 'Defend' OR mission = 'AllyDef' )) + OR ( talliance = salliance AND mission = 'Attack')) +ORDER BY tick DESC, mission }); $query->execute($ticks); $c->stash(intel => $query->fetchall_arrayref({}) ); @@ -82,15 +83,23 @@ sub planet : Local { $c->stash(govs => ["","Feu", "Dic", "Dem","Uni"]); $c->stash(planetstatus => ["","Friendly", "NAP", "Hostile"]); - $query = $dbh->prepare(intelquery q{i.sender - ,o.alliance AS oalliance,coords(o.x,o.y,o.z) AS ocoords - },q{i.target = $1 AND i.tick > (tick() - $2)}); + $query = $dbh->prepare(q{ +SELECT salliance, scoords, sender + ,mission, tick AS landingtick, eta, amount, ingal, username +FROM full_intel +WHERE target = $1 AND tick > (tick() - $2) +ORDER BY tick DESC, mission + }); $query->execute($id,$ticks); $c->stash(incoming => $query->fetchall_arrayref({}) ); - $query = $dbh->prepare(intelquery q{i.target - ,t.alliance AS talliance,coords(t.x,t.y,t.z) AS tcoords - },q{i.sender = $1 AND i.tick > (tick() - $2)}); + $query = $dbh->prepare(q{ +SELECT talliance, tcoords, target + ,mission, tick AS landingtick, eta, amount, ingal, username +FROM full_intel +WHERE sender = $1 AND tick > (tick() - $2) +ORDER BY tick DESC, mission + }); $query->execute($id,$ticks); $c->stash(outgoing => $query->fetchall_arrayref({}) ); diff --git a/lib/NDWeb/Include.pm b/lib/NDWeb/Include.pm index 1cbcca9..e619a22 100644 --- a/lib/NDWeb/Include.pm +++ b/lib/NDWeb/Include.pm @@ -27,7 +27,7 @@ use CGI qw/:standard/; our @ISA = qw/Exporter/; our @EXPORT = qw/parseMarkup - intelquery html_escape + html_escape comma_value array_expand/; sub html_escape($) { @@ -76,18 +76,6 @@ sub parseMarkup ($) { return $text; } -sub intelquery { - my ($columns,$where) = @_; - 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.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}; -} - sub array_expand ($) { my ($array) = @_; diff --git a/root/src/alliances/edit.tt2 b/root/src/alliances/edit.tt2 index 878fcfc..e7f6fef 100644 --- a/root/src/alliances/edit.tt2 +++ b/root/src/alliances/edit.tt2 @@ -71,8 +71,8 @@ [% FOR i IN intel %] - [% i.oalliance %] - [% i.ocoords %] + [% i.salliance %] + [% i.scoords %] [% i.mission %] [% i.landingtick %] [% i.eta %] diff --git a/root/src/intel/index.tt2 b/root/src/intel/index.tt2 index f4e067f..73a7eba 100644 --- a/root/src/intel/index.tt2 +++ b/root/src/intel/index.tt2 @@ -27,8 +27,8 @@ [% FOR i IN intel %] - [% i.oalliance %] - [% i.ocoords %] + [% i.salliance %] + [% i.scoords %] [% i.mission %] [% i.landingtick %] [% i.eta %] diff --git a/root/src/intel/planet.tt2 b/root/src/intel/planet.tt2 index 7d18e53..4e5eb63 100644 --- a/root/src/intel/planet.tt2 +++ b/root/src/intel/planet.tt2 @@ -69,8 +69,8 @@ [% FOR i IN incoming %] - [% i.oalliance %] - [% i.ocoords %] + [% i.salliance %] + [% i.scoords %] [% i.mission %] [% i.landingtick %] [% i.eta %] -- 2.39.2