From 3dc80cedd5deba53a4082992de317fbc3acf805c Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Mon, 9 Jan 2023 23:04:51 +0100 Subject: [PATCH] Updates for round 100 --- database/data/governments.sql | 7 +++++ database/data/races.sql | 11 +++++++ database/functions/covop_alert.sql | 3 +- database/functions/find_planet_id.sql | 3 +- database/functions/hostile_alliances.sql | 25 +++++++++++----- database/r100.sql | 37 ++++++++++++++++++++++++ database/tables/governments.sql | 11 +++++++ database/tables/races.sql | 12 ++++++++ lib/NDWeb/Controller/Raids.pm | 21 +++++++++----- scripts/parseplanets.pl | 2 +- 10 files changed, 112 insertions(+), 20 deletions(-) create mode 100644 database/data/governments.sql create mode 100644 database/data/races.sql create mode 100644 database/r100.sql create mode 100644 database/tables/governments.sql create mode 100644 database/tables/races.sql diff --git a/database/data/governments.sql b/database/data/governments.sql new file mode 100644 index 0000000..2c29bfc --- /dev/null +++ b/database/data/governments.sql @@ -0,0 +1,7 @@ +INSERT INTO governments (gov, gov_name) VALUES('', 'Unknown'); +INSERT INTO governments (gov, gov_name, mining, research, construction, alert) VALUES('Corp', 'Corporatism', 24, -15, -15, -5); +INSERT INTO governments (gov, gov_name, research, production_cost, stealth, alert) VALUES('Dem', 'Democracy', 30, -8, 10, -10); +INSERT INTO governments (gov, gov_name, mining, research, construction, production_time, alert) VALUES('Nat', 'Nationalism', 16, 20, -5, -10, 25); +INSERT INTO governments (gov, gov_name, mining, research, construction, production_time, stealth) VALUES('Soc', 'Socialism', 10, 10, 15, 20, 10); +INSERT INTO governments (gov, gov_name, research, construction, production_cost, production_time, alert) VALUES('Tot', 'Totalitarianism', -5, 25, -6, -10, 15); +INSERT INTO governments (gov, gov_name, mining, research, construction, production_time, alert) VALUES('Ana', 'Anarchy', -25, -25, -25, -20, -15); diff --git a/database/data/races.sql b/database/data/races.sql new file mode 100644 index 0000000..1bade9f --- /dev/null +++ b/database/data/races.sql @@ -0,0 +1,11 @@ +INSERT INTO races VALUES(1,'Ter','Terran', 80, 8, 125, 95, 10, 0, 25); +INSERT INTO races VALUES(2,'Cat','Cathaar', 80, 8, 105, 110, 0, 40, 25); +INSERT INTO races VALUES(3,'Xan','Xandathrii', 80, 6, 100, 105, 5, 0, 25); +INSERT INTO races VALUES(4,'Zik','Zikonian', 85, 6, 115, 95, 15, 0, 25); +INSERT INTO races VALUES(5,'Kin','Kinthia', 85, 6, 110, 105, 0, 30, 20); +INSERT INTO races VALUES(6,'Sly','Slythonian', 80, 6, 120, 100, 0, 10, 10); + +/* For things like asteroids and structures */ +INSERT INTO races VALUES(-1,'','None', 0, 0, 0, 0, 0, 0, 0); +/* For converting old data dumps */ +INSERT INTO races VALUES(-2,'Etd','Eitraides', 0, 0, 0, 0, 0, 0, 0); diff --git a/database/functions/covop_alert.sql b/database/functions/covop_alert.sql index 1d3d2a6..ea72128 100644 --- a/database/functions/covop_alert.sql +++ b/database/functions/covop_alert.sql @@ -1,6 +1,5 @@ - CREATE OR REPLACE FUNCTION covop_alert(secs integer, strucs integer, roids integer - , guards integer, gov governments, population integer) RETURNS integer + , guards integer, gov text, population integer) RETURNS integer AS $_$ SELECT ((50 + COALESCE($4*5.0/($3+1.0),$6)) * (1.0+2*LEAST(COALESCE($1::float/CASE $2 diff --git a/database/functions/find_planet_id.sql b/database/functions/find_planet_id.sql index 5890af9..94d01b9 100644 --- a/database/functions/find_planet_id.sql +++ b/database/functions/find_planet_id.sql @@ -1,5 +1,4 @@ -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 +CREATE OR REPLACE FUNCTION find_planet_id(_id text, _ruler text, _planet text, _race text) RETURNS integer AS $_$ DECLARE p RECORD; diff --git a/database/functions/hostile_alliances.sql b/database/functions/hostile_alliances.sql index 0c760c2..370f4b2 100644 --- a/database/functions/hostile_alliances.sql +++ b/database/functions/hostile_alliances.sql @@ -2,12 +2,22 @@ DROP FUNCTION IF EXISTS hostile_alliances(INT,INT); CREATE OR REPLACE FUNCTION hostile_alliances (first INT, last INT) RETURNS table(aid INT, alliance TEXT, hostile_count BIGINT, targeted BIGINT, targeted_raids BIGINT) AS $SQL$ -WITH hostile_alliances AS ( - SELECT COALESCE(aid,-1) AS aid, count(*) AS hostile_count +WITH hostile_fleets AS ( + SELECT DISTINCT s.pid, COALESCE(s.aid,-1) AS aid, i.target, i.tick + FROM intel i + JOIN current_planet_stats s ON s.pid = i.sender + JOIN current_planet_stats t ON t.pid = i.target + WHERE t.alliance = 'NewDawn' AND i.mission = 'Attack' AND i.tick BETWEEN $1 AND $2 + UNION DISTINCT + SELECT s.pid, COALESCE(s.aid,-1) AS aid, u.pid AS target, c.landing_tick FROM calls c JOIN incomings i USING (call) - JOIN current_planet_stats s USING (pid) - WHERE c.landing_tick BETWEEN $1 + i.eta AND $2 + i.eta + join users u USING (uid) + JOIN current_planet_stats s ON s.pid = i.pid + WHERE c.landing_tick BETWEEN $1 AND $2 +), hostile_alliances AS ( + SELECT COALESCE(aid,-1) AS aid, count(*) AS hostile_count + FROM hostile_fleets GROUP BY aid ), alliance_targets_1 AS ( SELECT COALESCE(aid,-1) AS aid, exists( @@ -19,15 +29,16 @@ WITH hostile_alliances AS ( JOIN current_planet_stats p USING (pid) JOIN fleets f USING (fid) WHERE f.mission = 'Attack' - AND lc.landing_tick BETWEEN $1 + lc.eta AND $2 + lc.eta + AND lc.landing_tick BETWEEN $1 AND $2 ), alliance_targets AS ( SELECT aid, count(*) AS targeted, count(NULLIF(raid_target,false)) AS targeted_raids FROM alliance_targets_1 GROUP BY aid ) SELECT aid, alliance, hostile_count, targeted, targeted_raids -FROM hostile_alliances +FROM alliances LEFT JOIN alliance_targets USING (aid) - LEFT JOIN alliances USING (aid) + LEFT JOIN hostile_alliances USING (aid) +WHERE hostile_count IS NOT NULL OR targeted IS NOT NULL $SQL$ LANGUAGE SQL STABLE; diff --git a/database/r100.sql b/database/r100.sql new file mode 100644 index 0000000..b5f644c --- /dev/null +++ b/database/r100.sql @@ -0,0 +1,37 @@ +ALTER TYPE governments RENAME TO govs; +\i tables/races.sql +\i data/races.sql +\i tables/governments.sql +\i data/governments.sql + +DROP VIEW full_defcalls; +DROP VIEW defcalls; +DROP VIEW full_intel; +DROP VIEW users_defprio; +DROP VIEW alliance_resources; +DROP VIEW current_planet_stats_full; +DROP VIEW current_planet_stats; +ALTER TABLE planets + ALTER COLUMN race TYPE TEXT, + ALTER COLUMN gov TYPE TEXT, + ALTER COLUMN gov SET DEFAULT ''; +ALTER TABLE planets ADD FOREIGN KEY (race) REFERENCES races(race) ON UPDATE CASCADE ON DELETE RESTRICT; +ALTER TABLE planets ADD FOREIGN KEY (gov) REFERENCES governments(gov) ON UPDATE CASCADE ON DELETE SET DEFAULT; +\d planets +\i views/current_planet_stats.sql +\i views/alliances_resources.sql +\i views/users_defprio.sql +\i views/full_intel.sql +\i views/defcalls.sql + +DROP FUNCTION IF EXISTS find_planet_id(text,text,text,race); +DROP FUNCTION covop_alert(integer, integer, integer, integer, govs, integer); +\i functions/find_planet_id.sql +\i functions/find_planet_id.sql +ALTER TABLE ship_stats ADD FOREIGN KEY (race) REFERENCES races(race) ON UPDATE CASCADE ON DELETE CASCADE; +\d ship_stats + +DROP TYPE race; +DROP TYPE govs; + +\i functions/hostile_alliances.sql diff --git a/database/tables/governments.sql b/database/tables/governments.sql new file mode 100644 index 0000000..584e2d3 --- /dev/null +++ b/database/tables/governments.sql @@ -0,0 +1,11 @@ +CREATE TABLE IF NOT EXISTS governments ( + gov TEXT PRIMARY KEY, + gov_name TEXT UNIQUE NOT NULL, + mining INTEGER NOT NULL DEFAULT 0, + research INTEGER NOT NULL DEFAULT 0, + construction INTEGER NOT NULL DEFAULT 0, + production_time INTEGER NOT NULL DEFAULT 0, + production_cost INTEGER NOT NULL DEFAULT 0, + alert INTEGER NOT NULL DEFAULT 0, + stealth INTEGER NOT NULL DEFAULT 0 +); diff --git a/database/tables/races.sql b/database/tables/races.sql new file mode 100644 index 0000000..802e3ed --- /dev/null +++ b/database/tables/races.sql @@ -0,0 +1,12 @@ +CREATE TABLE IF NOT EXISTS races ( + race_id INTEGER UNIQUE NOT NULL, + race TEXT PRIMARY KEY, + race_name TEXT UNIQUE NOT NULL, + max_stealth INTEGER NOT NULL, + stealth_growth INTEGER NOT NULL, + base_construction INTEGER NOT NULL, + base_research INTEGER NOT NULL, + production_time INTEGER NOT NULL, + salvage INTEGER NOT NULL, + universe_trade_tax INTEGER NOT NULL +); diff --git a/lib/NDWeb/Controller/Raids.pm b/lib/NDWeb/Controller/Raids.pm index 5248128..e948d66 100644 --- a/lib/NDWeb/Controller/Raids.pm +++ b/lib/NDWeb/Controller/Raids.pm @@ -610,9 +610,11 @@ WHERE rt.id = $1 AND r.open AND not r.removed },undef,$target,$c->user->id)); my $fleets = $dbh->prepare(q{ -SELECT DISTINCT ON (name) name, tick, fid, race +SELECT DISTINCT ON (name) name, tick, fid, race, race_id ,COALESCE($2,score) AS score, COALESCE($2, value) AS value -FROM fleets LEFT OUTER JOIN current_planet_stats p USING (pid) +FROM fleets + LEFT OUTER JOIN current_planet_stats p USING (pid) + LEFT OUTER JOIN races r USING (race) WHERE pid = $1 AND mission = 'Full fleet' ORDER BY name ASC, tick DESC }); @@ -643,9 +645,11 @@ WHERE uid = $1 AND fid = $2 },undef,$c->user->id,$fid)); my $fleets = $dbh->prepare(q{ -SELECT DISTINCT ON (name) name, tick, fid, race +SELECT DISTINCT ON (name) name, tick, fid, race, race_id ,score AS score, value AS value -FROM fleets LEFT OUTER JOIN current_planet_stats p USING (pid) +FROM fleets + LEFT OUTER JOIN current_planet_stats p USING (pid) + LEFT OUTER JOIN races r USING (race) WHERE pid = $1 AND mission = 'Full fleet' ORDER BY name ASC, tick DESC }); @@ -654,8 +658,10 @@ ORDER BY name ASC, tick DESC $c->stash(def => $fleets->fetchall_arrayref({})); $fleets = $dbh->prepare(q{ -SELECT tick, fid, race ,score , value -FROM fleets f LEFT OUTER JOIN current_planet_stats p USING (pid) +SELECT tick, fid, race, race_id, score, value +FROM fleets f + LEFT OUTER JOIN current_planet_stats p USING (pid) + LEFT OUTER JOIN races r USING (race) WHERE fid = $1 AND pid = $2 }); $fleets->execute($fid, $c->user->planet); @@ -687,7 +693,6 @@ SELECT id, amount FROM fleet_ships fs JOIN ship_stats s USING (ship) WHERE fid = $1 }); - my %races = (Ter => 1, Cat => 2, Xan => 3, Zik => 4, Etd => 5); for ('def','att'){ my $nrfleets = 0; my $tick = 0; @@ -698,7 +703,7 @@ WHERE fid = $1 ++$nrfleets; push @query, "${_}_planet_value_${nrfleets}=$fleet->{value}"; push @query, "${_}_planet_score_${nrfleets}=$fleet->{score}"; - push @query, "${_}_${nrfleets}_race=$races{$fleet->{race}}"; + push @query, "${_}_${nrfleets}_race=$fleet->{race_id}"; while (my $ship = $ships->fetchrow_hashref){ push @query, "${_}_${nrfleets}_$ship->{id}=$ship->{amount}"; } diff --git a/scripts/parseplanets.pl b/scripts/parseplanets.pl index 8866435..bf5f2dc 100755 --- a/scripts/parseplanets.pl +++ b/scripts/parseplanets.pl @@ -45,7 +45,7 @@ if (@_ = $dumps->fetchrow){ $_ = $_[0]; $hour = (gmtime($_[1]))[2]; 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){ + while (m/(\w+)\t(\d+)\t(\d+)\t(\d+)\t\"(.*)\"\t\"(.*)\"\t(\w+)\t(\d+)\t(\d+)\t(\d+)\t(\d+)/g){ $planetid->execute($1,$6,$5,$7); my @id = $planetid->fetchrow; 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]; -- 2.39.2