--- /dev/null
+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);
--- /dev/null
+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);
-
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
-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;
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(
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;
--- /dev/null
+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
--- /dev/null
+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
+);
--- /dev/null
+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
+);
},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
});
},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
});
$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);
WHERE fid = $1
});
- my %races = (Ter => 1, Cat => 2, Xan => 3, Zik => 4, Etd => 5);
for ('def','att'){
my $nrfleets = 0;
my $tick = 0;
++$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}";
}
$_ = $_[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];
projects / ndwebbie.git / commitdiff