From: Michael Andreen Date: Sun, 30 Dec 2007 23:22:13 +0000 (+0100) Subject: planet_scans and structure_scans views created, small change in the planet_data stora... X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=14e523fcb4dc6eb3ce780e0780959187c2610a6f planet_scans and structure_scans views created, small change in the planet_data storage and Check page uppdated --- diff --git a/NDWeb/Pages/Check.pm b/NDWeb/Pages/Check.pm index 38553fe..5df1265 100644 --- a/NDWeb/Pages/Check.pm +++ b/NDWeb/Pages/Check.pm @@ -78,7 +78,7 @@ sub render_body { $extra_columns = ", planet_status,hit_us, alliance,relationship"; } - my $query = $DBH->prepare(qq{Select id,coords(x,y,z), ((ruler || ' OF ') || p.planet) as planet,race, + my $query = $DBH->prepare(qq{Select p.id,coords(x,y,z), ((ruler || ' OF ') || p.planet) as planet,race, size, size_gain, size_gain_day, score,score_gain,score_gain_day, value,value_gain,value_gain_day, @@ -87,7 +87,15 @@ sub render_body { scorerank,scorerank_gain,scorerank_gain_day, valuerank,valuerank_gain,valuerank_gain_day, xprank,xprank_gain,xprank_gain_day, - p.value - p.size*200 - coalesce(c.metal+c.crystal+c.eonium,0)/150 - coalesce(c.structures,(SELECT avg(structures) FROM covop_targets)::int)*1500 AS fleetvalue,(c.metal+c.crystal+c.eonium)/100 AS resvalue $extra_columns from current_planet_stats_full p LEFT OUTER JOIN covop_targets c ON p.id = c.planet where x = ? AND y = ? $where order by x,y,z asc}); + p.value - p.size*200 - + COALESCE(ps.metal+ps.crystal+ps.eonium,0)/150 - + COALESCE(ss.total ,(SELECT COALESCE(avg(total),0) FROM structure_scans)::int)*1500 AS fleetvalue + ,(metal+crystal+eonium)/100 AS resvalue $extra_columns + FROM current_planet_stats_full p + LEFT OUTER JOIN planet_scans ps ON p.id = ps.planet + LEFT OUTER JOIN structure_scans ss ON p.id = ss.planet + WHERE x = ? AND y = ? $where ORDER BY x,y,z ASC + }); if (defined $z){ $query->execute($x,$y,$z); @@ -130,19 +138,19 @@ sub render_body { my $query = $DBH->prepare(q{ SELECT i.id,i.mission, i.name, i.tick AS landingtick,MIN(eta) AS eta - , i.amount, coords(t.x,t.y,t.z) AS target + , i.amount, coords(x,y,z) AS target FROM fleets i LEFT OUTER JOIN (planets NATURAL JOIN planet_stats) t ON i.target = t.id - AND t.tick = ( SELECT max(tick) FROM planet_stats) + AND t.tick = ( SELECT MAX(tick) FROM planet_stats) WHERE i.uid = -1 AND i.sender = ? AND (i.tick > tick() - 14 OR i.mission = 'Full Fleet') - GROUP BY i.id,t.x,t.y,t.z,i.mission,i.tick,i.name,i.amount,i.ingal,i.uid - ORDER BY i.tick,t.x,t.y,t.z + GROUP BY i.id,x,y,z,i.mission,i.tick,i.name,i.amount,i.ingal,i.uid + ORDER BY i.tick,x,y,z }); $query->execute($planet_id); - my $ships = $DBH->prepare(q{SELECT ship,amount FROM fleet_ships where id = ?}); + my $ships = $DBH->prepare(q{SELECT ship,amount FROM fleet_ships WHERE id = ?}); my @missions; $i = 0; while (my $mission = $query->fetchrow_hashref){ @@ -161,6 +169,38 @@ sub render_body { } $BODY->param(Missions => \@missions); + $query = $DBH->prepare(q{ + SELECT i.id,i.mission, i.name, i.tick AS landingtick,MIN(eta) AS eta + , i.amount, coords(x,y,z) AS sender + FROM fleets i + LEFT OUTER JOIN (planets + NATURAL JOIN planet_stats) s ON i.sender = s.id + AND s.tick = ( SELECT MAX(tick) FROM planet_stats) + WHERE i.uid = -1 + AND i.target = ? + AND (i.tick > tick() - 14 OR i.mission = 'Full Fleet') + GROUP BY i.id,x,y,z,i.mission,i.tick,i.name,i.amount,i.ingal,i.uid + ORDER BY i.tick,x,y,z + }); + $query->execute($planet_id); + my @incomings; + $i = 0; + while (my $mission = $query->fetchrow_hashref){ + $mission->{ODD} = $i++ % 2; + $mission->{CLASS} = $mission->{mission}; + my @ships; + $ships->execute($mission->{id}); + my $j = 0; + while (my $ship = $ships->fetchrow_hashref){ + $ship->{ODD} = $j++ % 2; + push @ships,$ship; + } + push @ships, {ship => 'No', amount => 'ships'} if @ships == 0; + $mission->{ships} = \@ships; + push @incomings,$mission; + } + $BODY->param(Incomings => \@incomings); + $query = $DBH->prepare(q{SELECT value,value_gain AS gain,tick FROM planet_stats WHERE id = ? AND tick > tick() - 24}); $query->execute($planet_id); diff --git a/database/scans.sql b/database/scans.sql index 5f78969..cbd44fe 100644 --- a/database/scans.sql +++ b/database/scans.sql @@ -81,17 +81,12 @@ INSERT INTO planet_data_types (category,name) VALUES('struc','Finance Centre'); INSERT INTO planet_data_types (category,name) VALUES('struc','Security Centre'); CREATE TABLE planet_data ( - id SERIAL PRIMARY KEY, - uid INTEGER NOT NULL REFERENCES users(uid), - planet INTEGER NOT NULL REFERENCES planets(id), + id INTEGER NOT NULL REFERENCES planets(id), + scan INTEGER NOT NULL REFERENCES scans(id), tick INTEGER NOT NULL, rid INTEGER NOT NULL REFERENCES planet_data_types(id), - amount INTEGER NOT NULL -) WITHOUT OIDS; - -CREATE TABLE data_scans ( - id INTEGER PRIMARY KEY REFERENCES planet_data(id), - scan INTEGER NOT NULL REFERENCES scans(id) + amount INTEGER NOT NULL, + PRIMARY KEY(rid,scan) ) WITHOUT OIDS; DROP TABLE intel; @@ -101,3 +96,33 @@ CREATE INDEX fleets_target_index ON fleets (target); CREATE INDEX fleets_sender_index ON fleets (sender); CREATE INDEX fleets_mission_index ON fleets (mission); CREATE INDEX fleets_ingal_index ON fleets (ingal); + +DROP TABLE covop_targets ; + +CREATE OR REPLACE VIEW planet_scans AS +SELECT DISTINCT ON (planet) id,planet,tick,metal,crystal,eonium,metal_roids,crystal_roids,eonium_roids +FROM scans s + JOIN (SELECT scan AS id,amount AS metal_roids FROM planet_data + WHERE rid = 1) AS mr USING (id) + JOIN (SELECT scan AS id,amount AS crystal_roids FROM planet_data + WHERE rid = 2) AS cr USING (id) + JOIN (SELECT scan AS id,amount AS eonium_roids FROM planet_data + WHERE rid = 3) AS er USING (id) + JOIN (SELECT scan AS id,amount AS metal FROM planet_data + WHERE rid = 4) AS m USING (id) + JOIN (SELECT scan AS id,amount AS crystal FROM planet_data + WHERE rid = 5) AS c USING (id) + JOIN (SELECT scan AS id,amount AS eonium FROM planet_data + WHERE rid = 6) AS e USING (id) +ORDER BY planet,tick DESC,id DESC; + +CREATE OR REPLACE VIEW structure_scans AS +SELECT DISTINCT ON (planet) id,planet,tick, total,distorters,seccents +FROM scans s + JOIN (SELECT scan AS id, SUM(amount) AS total FROM planet_data + WHERE rid >= 14 AND rid <= 24 GROUP BY scan) AS t USING (id) + JOIN (SELECT scan AS id,amount AS distorters FROM planet_data + WHERE rid = 18) AS d USING (id) + JOIN (SELECT scan AS id,amount AS seccents FROM planet_data + WHERE rid = 24) AS sc USING (id) +ORDER BY planet,tick DESC, id DESC diff --git a/scripts/scans.pl b/scripts/scans.pl index 999ba86..40cfc35 100755 --- a/scripts/scans.pl +++ b/scripts/scans.pl @@ -38,7 +38,7 @@ $dbh->do(q{SET CLIENT_ENCODING TO 'LATIN1';}); my $scangroups = $dbh->prepare(q{SELECT id,scan_id,tick,uid FROM scans WHERE groupscan AND NOT parsed}); my $oldscan = $dbh->prepare(q{SELECT scan_id FROM scans WHERE scan_id = ? AND tick >= tick() - 168}); -my $addScan = $dbh->prepare(q{INSERT INTO scans (scan_id,tick,uid) VALUES (?,?,COALESCE(?,'-1'))}); +my $addScan = $dbh->prepare(q{INSERT INTO scans (scan_id,tick,uid) VALUES (?,?,?)}); my $parsedscan = $dbh->prepare(q{UPDATE scans SET tick = ?, type = ?, planet = ?, parsed = TRUE WHERE id = ?}); my $addpoints = $dbh->prepare(q{UPDATE users SET scan_points = scan_points + ? WHERE uid = ? }); my $delscan = $dbh->prepare(q{DELETE FROM scans WHERE id = ?}); @@ -67,8 +67,7 @@ my $findcoords = $dbh->prepare(q{SELECT * FROM planetcoords(?,?)}); my $addfleet = $dbh->prepare(q{INSERT INTO fleets (name,mission,sender,target,tick,eta,back,amount,ingal,uid) VALUES(?,?,?,?,?,?,?,?,?,-1) RETURNING id}); my $fleetscan = $dbh->prepare(q{INSERT INTO fleet_scans (id,scan) VALUES(?,?)}); my $addships = $dbh->prepare(q{INSERT INTO fleet_ships (id,ship,amount) VALUES(?,?,?)}); -my $addpdata = $dbh->prepare(q{INSERT INTO planet_data (planet,tick,rid,amount,uid) VALUES(?,?,(SELECT id FROM planet_data_types WHERE category = ? AND name = ?), ?,-1) RETURNING id}); -my $pdatascan = $dbh->prepare(q{INSERT INTO data_scans (id,scan) VALUES(?,?)}); +my $addpdata = $dbh->prepare(q{INSERT INTO planet_data (id,tick,scan,rid,amount) VALUES(?,?,?,(SELECT id FROM planet_data_types WHERE category = ? AND name = ?), ?)}); $newscans->execute or die $dbh->errstr; while (my $scan = $newscans->fetchrow_hashref){ @@ -101,12 +100,10 @@ while (my $scan = $newscans->fetchrow_hashref){ if ($type eq 'Planet'){ $file =~ s/(\d),(\d)/$1$2/g; while($file =~ m/(Metal|Crystal|Eonium)\D+(\d+)\D+(\d+)/g){ - my $id = $dbh->selectrow_array($addpdata,undef,$planet,$tick + $addpdata->execute($planet,$tick,$scan->{id} ,'roid',$1, $2) or die $dbh->errstr; - $pdatascan->execute($id,$scan->{id}); - $id = $dbh->selectrow_array($addpdata,undef,$planet,$tick + $addpdata->execute($planet,$tick,$scan->{id} ,'resource',$1, $3) or die $dbh->errstr; - $pdatascan->execute($id,$scan->{id}); } }elsif ($type eq 'Jumpgate'){ while ($file =~ m/(\d+):(\d+):(\d+)\D+"left"\>(Attack|Defend|Return)<\/td>([^<]*)<\/td>(\d+)\D+(\d+)/g){ @@ -151,9 +148,8 @@ while (my $scan = $newscans->fetchrow_hashref){ } elsif($type eq 'Surface Analysis' || $type eq 'Technology Analysis'){ my $cat = ($type eq 'Surface Analysis' ? 'struc' : 'tech'); while($file =~ m{((?:[a-zA-Z]| )+)(\d+)}sg){ - my $id = $dbh->selectrow_array($addpdata,undef,$planet,$tick + $addpdata->execute($planet,$tick,$scan->{id} ,$cat,$1, $2) or die $dbh->errstr; - $pdatascan->execute($id,$scan->{id}); } } elsif($type eq 'Unit' || $type eq 'Advanced Unit'){ diff --git a/templates/check.tmpl b/templates/check.tmpl index e4f6f73..a4a14c1 100644 --- a/templates/check.tmpl +++ b/templates/check.tmpl @@ -78,17 +78,17 @@
- - - - - - - + + + + + + +
TickCoords
::
TickScan
TickCoords
::
@@ -114,6 +114,27 @@ + + + + + + + + + + + + + +
SenderNameMissionLanding tickETAAmount
stats