From d8c6f0648fbd22c5c71163d3e712ae16c969e376 Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Sun, 26 Feb 2017 15:09:50 +0100 Subject: [PATCH] Make use of the newish planet id from stats --- database/functions/escape_html.sql | 13 +++++++++++ database/functions/find_planet_id.sql | 31 +++++++++++++++++++++++++++ database/functions/planets.sql | 24 --------------------- database/ndawn.sql | 2 ++ scripts/fetchdumps.pl | 6 ++---- scripts/parseplanets.pl | 8 +++---- scripts/parsetick.sh | 6 ++++++ 7 files changed, 58 insertions(+), 32 deletions(-) create mode 100644 database/functions/escape_html.sql create mode 100644 database/functions/find_planet_id.sql create mode 100755 scripts/parsetick.sh diff --git a/database/functions/escape_html.sql b/database/functions/escape_html.sql new file mode 100644 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, '<', '<'); + _unescaped := replace(_unescaped, '>', '>'); + 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 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 2276518..d6d190e 100644 --- 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 5234e0b..5567337 100644 --- 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 883c90f..71c0c2b 100755 --- 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 518a887..8866435 100755 --- 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 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 -- 2.39.2