From: Michael Andreen Date: Sun, 17 Aug 2008 00:01:17 +0000 (+0200) Subject: New structure for planet, surface and tech scans X-Git-Url: https://ruin.nu/git/?p=ndwebbie.git;a=commitdiff_plain;h=436af6a696e3488f5d25e027553ec356da4cde01 New structure for planet, surface and tech scans --- diff --git a/database/scans.sql b/database/scans.sql new file mode 100644 index 0000000..bbf0da9 --- /dev/null +++ b/database/scans.sql @@ -0,0 +1,74 @@ +DROP VIEW planet_scans; +DROP VIEW structure_scans; + +CREATE TABLE planet_scans ( + id integer PRIMARY KEY REFERENCES scans(id), + planet integer NOT NULL REFERENCES planets(id), + tick integer NOT NULL, + metal bigint NOT NULL, + crystal bigint NOT NULL, + eonium bigint NOT NULL, + hidden bigint NOT NULL, + metal_roids integer NOT NULL, + crystal_roids integer NOT NULL, + eonium_roids integer NOT NULL, + agents integer NOT NULL, + guards integer NOT NULL, + light TEXT NOT NULL, + medium TEXT NOT NULL, + heavy TEXT NOT NULL +); + + +CREATE TABLE structure_scans ( + id integer PRIMARY KEY REFERENCES scans(id), + planet integer NOT NULL REFERENCES planets(id), + tick integer NOT NULL, + light_fac integer NOT NULL, + medium_fac integer NOT NULL, + heavy_fac integer NOT NULL, + amps integer NOT NULL, + distorters integer NOT NULL, + metal_ref integer NOT NULL, + crystal_ref integer NOT NULL, + eonium_ref integer NOT NULL, + reslabs integer NOT NULL, + fincents integer NOT NULL, + seccents integer NOT NULL, + total integer NOT NULL +); + +CREATE TABLE tech_scans ( + id integer PRIMARY KEY REFERENCES scans(id), + planet integer NOT NULL REFERENCES planets(id), + tick integer NOT NULL, + travel integer NOT NULL, + infra integer NOT NULL, + hulls integer NOT NULL, + waves integer NOT NULL, + extraction integer NOT NULL, + covert integer NOT NULL, + mining integer NOT NULL +); + +CREATE OR REPLACE VIEW current_planet_scans AS +SELECT DISTINCT ON (planet) ps.* +FROM planet_scans ps +ORDER BY planet, tick DESC, id DESC; + +CREATE OR REPLACE VIEW current_structure_scans AS +SELECT DISTINCT ON (planet) ss.* +FROM structure_scans ss +ORDER BY planet, tick DESC, id DESC; + +CREATE OR REPLACE VIEW current_tech_scans AS +SELECT DISTINCT ON (planet) ts.* +FROM tech_scans ts +ORDER BY planet, tick DESC, id DESC; + +CREATE INDEX planet_scans_planet_index ON planet_scans(planet,tick); +CREATE INDEX structure_scans_planet_index ON structure_scans(planet,tick); +CREATE INDEX tech_scans_planet_index ON tech_scans(planet,tick); + +DROP TABLE planet_data; +DROP TABLE planet_data_types; diff --git a/lib/NDWeb/Controller/Alliances.pm b/lib/NDWeb/Controller/Alliances.pm index bd25d9b..76ed462 100644 --- a/lib/NDWeb/Controller/Alliances.pm +++ b/lib/NDWeb/Controller/Alliances.pm @@ -224,9 +224,12 @@ sub resources : Local { ,((s.size::int8*(1400-tick())*250)/100 + score + (resources/planets*scoremem)/300 + (hidden/planets*scoremem)/100)::bigint AS nscore3 ,(s.size::int8*(1400-tick())*250)/100 AS scoregain3 - FROM (SELECT alliance_id AS id,sum(metal+crystal+eonium) AS resources, sum(hidden) AS hidden, count(*) AS planets - FROM planets p join planet_scans c ON p.id = c.planet GROUP by alliance_id) r - NATURAL JOIN alliances a + FROM (SELECT alliance_id AS id,sum(metal+crystal+eonium) AS resources + , sum(hidden) AS hidden, count(*) AS planets + FROM planets p join current_planet_scans c ON p.id = c.planet + GROUP by alliance_id + ) r + NATURAL JOIN alliances a LEFT OUTER JOIN (SELECT *,LEAST(members,60) AS scoremem FROM alliance_stats WHERE tick = (SELECT max(tick) FROM alliance_stats)) s ON a.id = s.id ORDER BY $order diff --git a/lib/NDWeb/Controller/CovOp.pm b/lib/NDWeb/Controller/CovOp.pm index 516e45d..21a47eb 100644 --- a/lib/NDWeb/Controller/CovOp.pm +++ b/lib/NDWeb/Controller/CovOp.pm @@ -60,8 +60,8 @@ sub list : Private { ,(SELECT value FROM current_planet_stats WHERE id = ?)) AS MaxResHack , planet_status, relationship,gov FROM current_planet_stats p - LEFT OUTER JOIN planet_scans ps ON p.id = ps.planet - LEFT OUTER JOIN structure_scans ss ON p.id = ss.planet + LEFT OUTER JOIN current_planet_scans ps ON p.id = ps.planet + LEFT OUTER JOIN current_structure_scans ss ON p.id = ss.planet ) AS foo LEFT OUTER JOIN (SELECT id,max(tick) AS tick FROM covop_attacks GROUP BY id) co USING (id) WHERE (metal IS NOT NULL OR seccents IS NOT NULL) diff --git a/lib/NDWeb/Controller/Intel.pm b/lib/NDWeb/Controller/Intel.pm index 0a683c0..4cc3b7e 100644 --- a/lib/NDWeb/Controller/Intel.pm +++ b/lib/NDWeb/Controller/Intel.pm @@ -294,8 +294,8 @@ sub naps : Local { ,(metal+crystal+eonium)/100 AS resvalue, planet_status,hit_us , alliance,relationship,nick FROM current_planet_stats p - LEFT OUTER JOIN planet_scans ps ON p.id = ps.planet - LEFT OUTER JOIN structure_scans ss ON p.id = ss.planet + LEFT OUTER JOIN current_planet_scans ps ON p.id = ps.planet + LEFT OUTER JOIN current_structure_scans ss ON p.id = ss.planet WHERE planet_status IN ('Friendly','NAP') order by x,y,z asc }); $query->execute; diff --git a/lib/NDWeb/Controller/Raids.pm b/lib/NDWeb/Controller/Raids.pm index 86465bc..ba56fa8 100644 --- a/lib/NDWeb/Controller/Raids.pm +++ b/lib/NDWeb/Controller/Raids.pm @@ -109,17 +109,17 @@ sub view : Local { $c->stash(landingtick => $raid->{tick}); my $targetquery = $dbh->prepare(qq{SELECT r.id, r.planet, size, score, value , p.x,p.y,p.z, race - , p.value - p.size*200 - - COALESCE(ps.metal+ps.crystal+ps.eonium,0)/150 - + , 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, comment , hidden, light, medium, heavy - FROM current_planet_stats p - JOIN raid_targets r ON p.id = r.planet - LEFT OUTER JOIN planet_scans ps ON p.id = ps.planet - LEFT OUTER JOIN structure_scans ss ON p.id = ss.planet + FROM current_planet_stats p + JOIN raid_targets r ON p.id = r.planet + LEFT OUTER JOIN current_planet_scans ps ON p.id = ps.planet + LEFT OUTER JOIN current_structure_scans ss ON p.id = ss.planet WHERE r.raid = ? $noingal ORDER BY size}); diff --git a/lib/NDWeb/Controller/Stats.pm b/lib/NDWeb/Controller/Stats.pm index 2579859..1796605 100644 --- a/lib/NDWeb/Controller/Stats.pm +++ b/lib/NDWeb/Controller/Stats.pm @@ -73,7 +73,8 @@ sub galaxy : Local { }; } - $query = $dbh->prepare(qq{SELECT p.id,coords(x,y,z), ruler, p.planet,race, + $query = $dbh->prepare(qq{SELECT DISTINCT ON (x,y,z,p.id) + p.id,coords(x,y,z), ruler, p.planet,race, size, size_gain, size_gain_day, score,score_gain,score_gain_day, value,value_gain,value_gain_day, @@ -83,10 +84,11 @@ sub galaxy : Local { valuerank,valuerank_gain,valuerank_gain_day, xprank,xprank_gain,xprank_gain_day $extra_columns - FROM current_planet_stats_full p + 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 = ? AND COALESCE(z = ?,TRUE) ORDER BY x,y,z ASC + WHERE x = ? AND y = ? AND COALESCE(z = ?,TRUE) + ORDER BY x,y,z,p.id,ps.tick DESC, ps.id DESC, ss.tick DESC, ss.id DESC }); $query->execute($x,$y,$z); @@ -177,12 +179,12 @@ sub planet : Local { } if ($c->check_user_roles(qw/stats_planetdata/)){ - my $query = $dbh->prepare(q{SELECT DISTINCT ON(rid) tick,category,name,amount - FROM planet_data pd JOIN planet_data_types pdt ON pd.rid = pdt.id - WHERE pd.id = $1 ORDER BY rid,tick DESC - }); - $query->execute($id); - $c->stash(planetdata => $query->fetchall_arrayref({}) ); + $c->stash(planetscan => $dbh->selectrow_hashref(q{SELECT * + FROM current_planet_scans WHERE planet = $1},undef,$id)); + $c->stash(structurescan => $dbh->selectrow_hashref(q{SELECT * + FROM current_structure_scans WHERE planet = $1},undef,$id)); + $c->stash(techscan => $dbh->selectrow_hashref(q{SELECT * + FROM current_tech_scans WHERE planet = $1},undef,$id)); } my $query = $dbh->prepare(q{SELECT value,value_gain AS gain,tick FROM planet_stats diff --git a/root/src/stats/planet.tt2 b/root/src/stats/planet.tt2 index dfae29a..d8d9f66 100644 --- a/root/src/stats/planet.tt2 +++ b/root/src/stats/planet.tt2 @@ -1,20 +1,90 @@ [% META title = 'Planet stats' %] [% PROCESS inc/stats.tt2 %] -[% IF planetdata %]
- - - [% FOR pd IN planetdata %] - - - - +[% IF planetscan.tick %] +
TickTypeAmount
[% pd.tick %][% pd.category %] [% pd.name %][% comma(pd.amount) %]
+ + + + + + + + + + + + + + + + + + + + + + + + +
Tick:[% planetscan.tick %]
TypeRoidsResources
Metal[% planetscan.metal_roids %][% comma(planetscan.metal) %]
Crystal[% planetscan.crystal_roids %][% comma(planetscan.crystal) %]
Eonium[% planetscan.eonium_roids %][% comma(planetscan.eonium) %]
AgentsGuards
[% planetscan.agents %][% planetscan.guards %]
Factory production
LightMediumHeavy
[% planetscan.light %][% planetscan.medium %][% planetscan.heavy %]
Hidden:[% comma(planetscan.hidden) %]
+[% END %] +[% IF structurescan.tick %] + + + + + + + + + + + + + + + + + + + + + + + + +
Tick:[% structurescan.tick %]
Factories
LightMediumHeavy
[% structurescan.light_fac %][% structurescan.medium_fac %][% structurescan.heavy_fac %]
AmpsDistorters
[% structurescan.amps %][% structurescan.distorters %]
Refinaries
MetalCrystalEonium
[% structurescan.metal_ref %][% structurescan.crystal_ref %][% structurescan.eonium_ref %]
ReslabsFinanceSeccents
[% structurescan.reslabs %][% structurescan.fincents %][% structurescan.seccents %]
+[% END %] +[% IF techscan.tick %] + + + + + + + + + + + + + + + + + + + + + + + - [% END %]
Tick:[% techscan.tick %]
ResearchLevel
Travel Time[% techscan.travel %]
Infrastructure[% techscan.infra %]
Hulls[% techscan.hulls %]
Waves[% techscan.waves %]
Core Extraction[% techscan.extraction %]
Covert Ops[% techscan.covert %]
Mining[% techscan.mining %]
-
[% END %] +
diff --git a/scripts/scans.pl b/scripts/scans.pl index bedd3df..c6d8fca 100755 --- a/scripts/scans.pl +++ b/scripts/scans.pl @@ -73,13 +73,24 @@ 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 (id,tick,scan,rid,amount) VALUES(?,?,?,(SELECT id FROM planet_data_types WHERE category = ? AND name = ?), ?)}); +my $addplanetscan = $dbh->prepare(q{INSERT INTO planet_scans + (id,tick,planet,metal_roids,metal,crystal_roids,crystal,eonium_roids,eonium + ,agents,guards,light,medium,heavy,hidden) + VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}); +my $addstrucscan = $dbh->prepare(q{INSERT INTO structure_scans + (id,tick,planet,light_fac,medium_fac,heavy_fac,amps,distorters + ,metal_ref,crystal_ref,eonium_ref,reslabs,fincents,seccents,total) + VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}); + +my $addtechscan = $dbh->prepare(q{INSERT INTO tech_scans + (id,tick,planet,travel,infra,hulls,waves,extraction,covert,mining) + VALUES(?,?,?,?,?,?,?,?,?,?)}); + $dbh->begin_work or die 'No transaction'; $newscans->execute or die $dbh->errstr; $dbh->pg_savepoint('scans') or die "No savepoint"; -my %production = (None => 0, Low => 35, Medium => 65, High => 100); while (my $scan = $newscans->fetchrow_hashref){ $dbh->pg_release('scans') or die "Couldn't save"; $dbh->pg_savepoint('scans') or die "Couldn't save"; @@ -114,36 +125,23 @@ while (my $scan = $newscans->fetchrow_hashref){ #TODO: something about planet being closed? } if ($type eq 'Planet'){ + my @values = ($scan->{id},$tick,$planet); + $file =~ s/(\d),(\d)/$1$2/g; + while($file =~ m/"center">(Metal|Crystal|Eonium)\D+(\d+)\D+([\d,]+)/g){ - my ($type,$roids,$res) = ($1,$2,$3); - $roids =~ s/,//g; - $addpdata->execute($planet,$tick,$scan->{id} - ,'roid',$type, $roids) or die $dbh->errstr; - $res =~ s/,//g; - $addpdata->execute($planet,$tick,$scan->{id} - ,'resource',$type, $res) or die $dbh->errstr; + push @values,$2,$3; } if($file =~ m{Security\ Guards .+? "center">(\d+) .+? "center">(\d+)}sx){ - $addpdata->execute($planet,$tick,$scan->{id} - ,'planet','Agents', $1) or die $dbh->errstr; - $addpdata->execute($planet,$tick,$scan->{id} - ,'planet','Security Guards', $2) or die $dbh->errstr; + push @values,$1,$2; } if($file =~ m{}){ - $addpdata->execute($planet,$tick,$scan->{id} - ,'planet','Light Usage', $production{$1}) or die $dbh->errstr; - $addpdata->execute($planet,$tick,$scan->{id} - ,'planet','Medium Usage', $production{$2}) or die $dbh->errstr; - $addpdata->execute($planet,$tick,$scan->{id} - ,'planet','Heavy Usage', $production{$3}) or die $dbh->errstr; + push @values,$1,$2,$3; } if($file =~ m{([\d,]+)}){ - my $res = $1; - $res =~ s/,//g; - $addpdata->execute($planet,$tick,$scan->{id} - ,'planet','Production', $res) or die $dbh->errstr; + push @values,$1; } + $addplanetscan->execute(@values); }elsif ($type eq 'Jumpgate'){ #print "$file\n"; while ($file =~ m{(\d+):(\d+):(\d+)\D+(Attack|Defend|Return)
TickValueGain
([A-Z][a-z]+)([A-Z][a-z]+)([A-Z][a-z]+)([^<]*)\D+(\d+)\D+(\d+)}g){ @@ -186,13 +184,21 @@ while (my $scan = $newscans->fetchrow_hashref){ $fleetscan->execute($id,$scan->{id}) or die $dbh->errstr; } } - } elsif($type eq 'Surface Analysis' || $type eq 'Technology Analysis'){ - my $cat = ($type eq 'Surface Analysis' ? 'struc' : 'tech'); + } elsif($type eq 'Surface Analysis'){ + my @values = ($scan->{id},$tick,$planet); + my $total = 0; while($file =~ m{((?:[a-zA-Z]| )+)(\d+)}sg){ - $addpdata->execute($planet,$tick,$scan->{id} - ,$cat,$1, $2) or die $dbh->errstr; + push @values,$2; + $total += $2; } - + push @values,$total; + $addstrucscan->execute(@values); + } elsif($type eq 'Technology Analysis'){ + my @values = ($scan->{id},$tick,$planet); + while($file =~ m{((?:[a-zA-Z]| )+)(\d+)}sg){ + push @values,$2; + } + $addtechscan->execute(@values); } elsif($type eq 'Unit' || $type eq 'Advanced Unit'){ my $id = addfleet($type,'Full fleet',$file,$planet,undef,$tick,undef,undef,undef); $fleetscan->execute($id,$scan->{id}) or die $dbh->errstr;