scripts/parsedumps.pl

   1 #!/usr/bin/perl
   2 q{
   3 /***************************************************************************
   4  *   Copyright (C) 2006 by Michael Andreen <harvATruinDOTnu>               *
   5  *                                                                         *
   6  *   This program is free software; you can redistribute it and/or modify  *
   7  *   it under the terms of the GNU General Public License as published by  *
   8  *   the Free Software Foundation; either version 2 of the License, or     *
   9  *   (at your option) any later version.                                   *
  10  *                                                                         *
  11  *   This program is distributed in the hope that it will be useful,       *
  12  *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
  13  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
  14  *   GNU General Public License for more details.                          *
  15  *                                                                         *
  16  *   You should have received a copy of the GNU General Public License     *
  17  *   along with this program; if not, write to the                         *
  18  *   Free Software Foundation, Inc.,                                       *
  19  *   51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.         *
  20  ***************************************************************************/
  21 };
  22
  23 use strict;
  24 use warnings;
  25 use DBI;
  26 use DBD::Pg qw(:pg_types);
  27
  28 use LWP::Simple;
  29
  30 use lib qw{/var/www/ndawn/};
  31
  32 use ND::Include;
  33
  34 our $dbh;
  35 for my $file ("/home/whale/db.pl")
  36 {
  37         unless (my $return = do $file){
  38                 warn "couldn't parse $file: $@" if $@;
  39                 warn "couldn't do $file: $!"    unless defined $return;
  40                 warn "couldn't run $file"       unless $return;
  41         }
  42 }
  43 #$dbh->trace("0","/tmp/parsedumps");
  44
  45 $ND::DBH = $dbh;
  46
  47 my $tick = $ARGV[0];
  48 my $hour;
  49 $dbh->begin_work;
  50 my $dumps = $dbh->prepare("SELECT dump,modified FROM dumps WHERE tick = ? and type = ? ORDER BY modified LIMIT 1");
  51 $dumps->execute($tick,"alliance");
  52 my @alliances;
  53 if (@_ = $dumps->fetchrow){
  54         $_ = $_[0];
  55         $hour = (gmtime($_[1]))[2];
  56         my $allianceid = $dbh->prepare(qq{SELECT find_alliance_id(?)});
  57         while (m/\d+\t\"(.+)\"\t(\d+)\t(\d+)\t(\d+)/g){
  58                 $allianceid->execute($1);
  59                 my ($id) = $allianceid->fetchrow;
  60                 push @alliances,[$tick,$id,$3,$2,$4,0,0,0,0,0,0,0,0,0,0,0,0];
  61         }
  62 }
  63
  64
  65 for my $i (3,4){
  66         @alliances = sort {$b->[$i] <=> $a->[$i]} @alliances;
  67         my $rank = 0;
  68         for my $alliance (@alliances) {
  69                 $rank++;
  70                 $alliance->[$i+2] = $rank;
  71     }
  72 }
  73 my $insert = $dbh->prepare(q{INSERT INTO alliance_stats (tick,id,members,
  74         size,score,
  75         sizerank,scorerank,
  76         size_gain,score_gain,
  77         sizerank_gain,scorerank_gain,
  78         size_gain_day,score_gain_day,
  79         sizerank_gain_day,scorerank_gain_day,
  80         members_gain,members_gain_day
  81         ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}) or die $dbh->errstr;
  82
  83 my $findalliance = $dbh->prepare(q{SELECT tick,id,members,
  84         size, score,
  85         sizerank, scorerank,
  86         size_gain, score_gain,
  87         sizerank_gain, scorerank_gain,
  88         size_gain_day, score_gain_day,
  89         sizerank_gain_day, scorerank_gain_day,
  90         members_gain,members_gain_day
  91 FROM alliance_stats WHERE id = $1 AND tick < $2 ORDER BY tick DESC LIMIT 1}) or die $dbh->errstr;
  92
  93 for my $alliance (@alliances) {
  94
  95         $findalliance->execute($alliance->[1],$tick) or die $dbh->errstr;
  96         if (my @oldAlliance = $findalliance->fetchrow_array){
  97                 for my $i (1,2){
  98                         $alliance->[$i+6] = $alliance->[$i+2] - $oldAlliance[$i+2];
  99                         $alliance->[$i+8] = $alliance->[$i+4] - $oldAlliance[$i+4];
 100                         $alliance->[$i+10] = $alliance->[$i+6] + $oldAlliance[$i+10] if $hour;
 101                         $alliance->[$i+12] = $alliance->[$i+8] + $oldAlliance[$i+12] if $hour;
 102                 }
 103                 $alliance->[15] = $alliance->[2] - $oldAlliance[+2];
 104                 $alliance->[16] = $alliance->[15] + $oldAlliance[16] if $hour;
 105
 106         }
 107         $insert->execute(@{$alliance}) or die $dbh->errstr;
 108 }
 109
 110 my @planets = ();
 111 $dumps->execute($tick,"planet");
 112 if (@_ = $dumps->fetchrow){
 113         $_ = $_[0];
 114         $hour = (gmtime($_[1]))[2];
 115         my $planetid = $dbh->prepare(qq{SELECT findplanetid(?,?,?)});
 116         while (m/(\d+)\t(\d+)\t(\d+)\t\"(.*)\"\t\"(.*)\"\t(Ter|Cat|Zik|Xan|Etd)\t(\d+)\t(\d+)\t(\d+)\t(\d+)/g){
 117                 $planetid->execute($5,$4,$6);
 118                 my @id = $planetid->fetchrow;
 119                 push @planets,[$tick,$id[0],$1,$2,$3,$7,$8,$9,$10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
 120         }
 121 }
 122
 123 for my $i (8,7,5,6){
 124         @planets = sort {$b->[$i] <=> $a->[$i]} @planets;
 125         my $rank = 0;
 126         for my $planet (@planets) {
 127                 $rank++;
 128                 $planet->[$i+4] = $rank;
 129     }
 130 }
 131
 132 my $findplanets = $dbh->prepare(q{SELECT tick,id, x, y, z,
 133         size, score, value, xp,
 134         sizerank, scorerank, valuerank, xprank,
 135         size_gain, score_gain, value_gain, xp_gain,
 136         sizerank_gain, scorerank_gain, valuerank_gain, xprank_gain,
 137         size_gain_day, score_gain_day, value_gain_day, xp_gain_day,
 138         sizerank_gain_day, scorerank_gain_day, valuerank_gain_day, xprank_gain_day
 139 FROM planet_stats WHERE tick = (SELECT MAX(tick) FROM planet_stats WHERE tick < $1)});
 140 $insert = $dbh->prepare(q{INSERT INTO planet_stats(tick,id, x, y, z,
 141         size, score, value,xp,
 142         sizerank,scorerank,valuerank,xprank,
 143         size_gain, score_gain, value_gain, xp_gain,
 144         sizerank_gain, scorerank_gain, valuerank_gain, xprank_gain,
 145         size_gain_day, score_gain_day, value_gain_day, xp_gain_day,
 146         sizerank_gain_day, scorerank_gain_day, valuerank_gain_day, xprank_gain_day)
 147         VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)});
 148 $findplanets->execute($tick) or die $dbh->errstr;
 149 my %oldStats;
 150 while (my @planet = $findplanets->fetchrow){
 151         $oldStats{$planet[1]} = \@planet;
 152 }
 153 for my $planet (@planets) {
 154         #print "$planet->[1]\n";
 155         my $oldPlanet = $oldStats{$planet->[1]};
 156
 157         if ($oldPlanet){
 158                 for my $i (1,2,3,4){
 159                         $planet->[$i+12] = $planet->[$i+4] - $oldPlanet->[$i+4];
 160                         $planet->[$i+16] = $planet->[$i+8] - $oldPlanet->[$i+8];
 161                         $planet->[$i+20] = $planet->[$i+12] + $oldPlanet->[$i+20] if $hour;
 162                         $planet->[$i+24] = $planet->[$i+16] + $oldPlanet->[$i+24] if $hour;
 163                 }
 164                 if (($planet->[2] != $oldPlanet->[2]) or
 165                         ($planet->[3] != $oldPlanet->[3]) or
 166                         ($planet->[4] != $oldPlanet->[4])){
 167                         #print "Planet has moved from $oldPlanet[2]:$oldPlanet[3]:$oldPlanet[4] to $planet->[2]:$planet->[3]:$planet->[4]\n";
 168                         intel_log -3, $planet->[1],"Planet has moved from $oldPlanet->[2]:$oldPlanet->[3]:$oldPlanet->[4] to $planet->[2]:$planet->[3]:$planet->[4] tick $tick";
 169                 }
 170         }
 171         #print "@{$oldPlanet}\n";
 172         #print "@{$planet}\n";
 173         $insert->execute(@{$planet}) or die $dbh->errstr;
 174 }
 175
 176
 177 $dumps->execute($tick,"galaxy");
 178 my @galaxies;
 179 if (@_ = $dumps->fetchrow){
 180         $_ = $_[0];
 181         $hour = (gmtime($_[1]))[2];
 182         while (m/(\d+)\t(\d+)\t\"(?:.+)\"\t(\d+)\t(\d+)\t(\d+)\t(\d+)/g){
 183                 push @galaxies,[$tick,$1,$2,$3,$4,$5,$6,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
 184         }
 185 }
 186
 187 for my $i (6,5,3,4){
 188         @galaxies = sort {$b->[$i] <=> $a->[$i]} @galaxies;
 189         my $rank = 0;
 190         for my $galaxy (@galaxies) {
 191                 $rank++;
 192                 $galaxy->[$i+4] = $rank;
 193     }
 194 }
 195
 196 my $findgalaxy = $dbh->prepare(q{SELECT tick,x, y,
 197         size, score, value, xp,
 198         sizerank, scorerank, valuerank, xprank,
 199         size_gain, score_gain, value_gain, xp_gain,
 200         sizerank_gain, scorerank_gain, valuerank_gain, xprank_gain,
 201         size_gain_day, score_gain_day, value_gain_day, xp_gain_day,
 202         sizerank_gain_day, scorerank_gain_day, valuerank_gain_day, xprank_gain_day,
 203         planets,planets_gain,planets_gain_day
 204 FROM galaxies WHERE x = $1 AND y = $2 AND tick < $3 ORDER BY tick DESC LIMIT 1});
 205 $insert = $dbh->prepare(q{INSERT INTO galaxies(tick, x, y,
 206         size, score, value,xp,
 207         sizerank,scorerank,valuerank,xprank,
 208         size_gain, score_gain, value_gain, xp_gain,
 209         sizerank_gain, scorerank_gain, valuerank_gain, xprank_gain,
 210         size_gain_day, score_gain_day, value_gain_day, xp_gain_day,
 211         sizerank_gain_day, scorerank_gain_day, valuerank_gain_day, xprank_gain_day,
 212         planets,planets_gain,planets_gain_day
 213         ) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)});
 214 my $countplanets = $dbh->prepare(q{SELECT count(*) from planet_stats where x = $1 and y = $2 and tick = $3});
 215 for my $galaxy (@galaxies) {
 216
 217         my ($planets) = $dbh->selectrow_array($countplanets,undef,$galaxy->[1],$galaxy->[2],$tick) or die $dbh->errstr;
 218         $galaxy->[27] = $planets;
 219         $findgalaxy->execute($galaxy->[1],$galaxy->[2],$tick) or die $dbh->errstr;
 220         if (my @oldGalaxy = $findgalaxy->fetchrow_array){
 221                 for my $i (1,2,3,4){
 222                         $galaxy->[$i+10] = $galaxy->[$i+2] - $oldGalaxy[$i+2];
 223                         $galaxy->[$i+14] = $galaxy->[$i+6] - $oldGalaxy[$i+6];
 224                         $galaxy->[$i+18] = $galaxy->[$i+10] + $oldGalaxy[$i+18] if $hour;
 225                         $galaxy->[$i+22] = $galaxy->[$i+14] + $oldGalaxy[$i+22] if $hour;
 226                 }
 227                 $galaxy->[28] = $galaxy->[27] - $oldGalaxy[27];
 228                 $galaxy->[29] = $galaxy->[28] + $oldGalaxy[29] if $hour;
 229
 230         }
 231         $insert->execute(@{$galaxy}) or die $dbh->errstr;
 232         #print "@{$galaxy}\n";
 233 }
 234
 235
 236 #$dbh->rollback;
 237 $dbh->commit;
 238
 239 $countplanets->finish;
 240 $findgalaxy->finish;
 241 $findalliance->finish;
 242 $dumps->finish;
 243 $dbh->disconnect;