Make use of the newish planet id from stats

diff --git a/database/functions/escape_html.sql b/database/functions/escape_html.sql
new file mode 100644 (file)
index 0000000..1c58cdb
--- /dev/null
+++ b/database/functions/escape_html.sql
@@ -0,0 +1,13 @@
+CREATE OR REPLACE FUNCTION escape_html(_unescaped text) RETURNS text
+    AS $_$
+DECLARE
+BEGIN
+       _unescaped := replace(_unescaped, '&', '&');
+       _unescaped := replace(_unescaped, '"', '"');
+       _unescaped := replace(_unescaped, '<', '&lt;');
+       _unescaped := replace(_unescaped, '>', '&gt;');
+       RETURN _unescaped;
+END;
+$_$
+    LANGUAGE plpgsql IMMUTABLE;
+

diff --git a/database/functions/find_planet_id.sql b/database/functions/find_planet_id.sql
new file mode 100644 (file)
index 0000000..0649fb6
--- /dev/null
+++ b/database/functions/find_planet_id.sql
@@ -0,0 +1,31 @@
+DROP FUNCTIOn IF EXISTS find_planet_id(text,text,race);
+CREATE OR REPLACE FUNCTION find_planet_id(_id text, _ruler text, _planet text, _race race) RETURNS integer
+    AS $_$
+DECLARE
+       p RECORD;
+       planet_id INTEGER;
+       thread INTEGER;
+BEGIN
+       SELECT pid, ftid, race, ruler, planet INTO p FROM planets WHERE id = _id;
+       IF FOUND THEN
+               IF _race <> p.race OR _planet <> p.planet OR _ruler <> p.ruler THEN
+                       UPDATE planets SET race = _race, planet = _planet, ruler = _ruler WHERE pid = p.pid;
+                       UPDATE forum_threads SET subject = escape_html(_ruler) || ' OF ' || escape_html(_planet)
+                               WHERE ftid = p.ftid;
+                       INSERT INTO forum_posts (ftid, uid, message) VALUES(p.ftid, -2, 'Planet changed data from ('
+                               || escape_html(p.ruler) || ', ' || escape_html(p.planet) || ', ' || p.race || ') to ('
+                               || escape_html(_ruler) || ', ' || escape_html(_planet) || ', ' || _race || ').');
+               END IF;
+               planet_id := p.pid;
+       ELSE
+               INSERT INTO forum_threads (fbid,uid,subject) VALUES(-2, -3,
+                               escape_html(_ruler) || ' OF ' || escape_html(_planet))
+                       RETURNING ftid INTO thread;
+               INSERT INTO planets(id, ruler,planet,race,ftid) VALUES(_id, _ruler,_planet,_race,thread)
+                       RETURNING pid INTO planet_id;
+       END IF;
+       RETURN planet_id;
+END;
+$_$
+    LANGUAGE plpgsql;
+

diff --git a/database/functions/planets.sql b/database/functions/planets.sql
index 2276518f13781b9a7a425b033ca7d9654db216c9..d6d190ec0564cc5d06a2a80cef76da7f5f0e6cc3 100644 (file)
--- a/database/functions/planets.sql
+++ b/database/functions/planets.sql
@@ -6,30 +6,6 @@ CREATE OR REPLACE FUNCTION planetcoords(id integer, tick integer, OUT x integer,
     AS $_$SELECT x,y,z FROM planet_stats WHERE pid = $1 AND (tick >= $2  OR tick =( SELECT max(tick) FROM planet_stats))  ORDER BY tick ASC LIMIT 1$_$
     LANGUAGE sql STABLE;
 
-CREATE OR REPLACE FUNCTION find_planet_id(_ruler text, _planet text, _race race) RETURNS integer
-    AS $_$
-DECLARE
-       p RECORD;
-       id INTEGER;
-       thread INTEGER;
-BEGIN
-       SELECT pid, race INTO p FROM planets WHERE ruler = _ruler AND planet = _planet;
-       IF FOUND THEN
-               IF _race <> p.race THEN
-                       UPDATE planets SET race = _race WHERE pid = p.pid;
-               END IF;
-               id := p.pid;
-       ELSE
-               INSERT INTO forum_threads (fbid,subject,uid) VALUES(-2, _ruler || ' OF ' || _planet, -3)
-                       RETURNING ftid INTO thread;
-               INSERT INTO planets(ruler,planet,race,ftid) VALUES(_ruler,_planet,_race,thread)
-                       RETURNING pid INTO id;
-       END IF;
-       RETURN id;
-END;
-$_$
-    LANGUAGE plpgsql;
-
 CREATE OR REPLACE FUNCTION find_alliance_id(alli text) RETURNS integer
     AS $_$
 DECLARE

diff --git a/database/ndawn.sql b/database/ndawn.sql
index 5234e0b3f75caec94663d61bf456d03d670fdb50..5567337c58914e035be38b2d174c2ef4a6cc7702 100644 (file)
--- a/database/ndawn.sql
+++ b/database/ndawn.sql
@@ -773,6 +773,8 @@ CREATE TABLE planets (
 )
 WITH (fillfactor='50');
 
+ALTER TABLE planets ADD COLUMN id text UNIQUE NOT NULL;
+
 
 ALTER TABLE planets OWNER TO ndawn;
 

diff --git a/scripts/fetchdumps.pl b/scripts/fetchdumps.pl
index 883c90f37c2a3edb4822c27d9ea913a91c9b6591..71c0c2b88c350c5eae4f48c88e309e885383d610 100755 (executable)
--- a/scripts/fetchdumps.pl
+++ b/scripts/fetchdumps.pl
@@ -55,10 +55,8 @@ for my $type ("planet","alliance","galaxy"){
 }
 
 if ($updated){
-       `perl $FindBin::Bin/parsealliances.pl $updated`;
-       `perl $FindBin::Bin/parseplanets.pl $updated`;
-       `perl $FindBin::Bin/parsegalaxies.pl $updated`;
-       `perl $FindBin::Bin/ndrank.pl`;
+       system("$FindBin::Bin/parsetick.sh", $updated);
+       system("$FindBin::Bin/ndrank.pl");
        $dbh->do(q{UPDATE misc SET value = ? WHERE id = 'TICK'}, undef, $updated);
        system 'killall','-USR1', 'ndbot.pl';
        local $dbh->{Warn} = 0;

diff --git a/scripts/parseplanets.pl b/scripts/parseplanets.pl
index 518a887201661cc63a413332e9662e05ff7720e4..8866435d7fcdb6adce037b71bebf2e632a7d5b90 100755 (executable)
--- a/scripts/parseplanets.pl
+++ b/scripts/parseplanets.pl
@@ -44,11 +44,11 @@ $dumps->execute($tick);
 if (@_ = $dumps->fetchrow){
        $_ = $_[0];
        $hour = (gmtime($_[1]))[2];
-       my $planetid = $dbh->prepare(q{SELECT find_planet_id($1,$2,$3)});
-       while (m/(\d+)\t(\d+)\t(\d+)\t\"(.*)\"\t\"(.*)\"\t(Ter|Cat|Zik|Xan|Etd)\t(\d+)\t(\d+)\t(\d+)\t(\d+)/g){
-               $planetid->execute($5,$4,$6);
+       my $planetid = $dbh->prepare(q{SELECT find_planet_id($1,$2,$3,$4)});
+       while (m/(\w+)\t(\d+)\t(\d+)\t(\d+)\t\"(.*)\"\t\"(.*)\"\t(Ter|Cat|Zik|Xan|Etd)\t(\d+)\t(\d+)\t(\d+)\t(\d+)/g){
+               $planetid->execute($1,$6,$5,$7);
                my @id = $planetid->fetchrow;
-               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];
+               push @planets,[$tick,$id[0],$2,$3,$4,$8,$9,$10,$11,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0];
        }
 }
 

diff --git a/scripts/parsetick.sh b/scripts/parsetick.sh
new file mode 100755 (executable)
index 0000000..cc2192f
--- /dev/null
+++ b/scripts/parsetick.sh
@@ -0,0 +1,6 @@
+#!/bin/sh
+SCRIPT_DIR="$(dirname "$(readlink -f "$0")")"
+
+$SCRIPT_DIR/parsealliances.pl $1
+$SCRIPT_DIR/parseplanets.pl $1
+$SCRIPT_DIR/parsegalaxies.pl $1