database/planets.sql

   1 DROP VIEW users_defprio;
   2 DROP VIEW current_planet_stats;
   3 DROP VIEW current_planet_stats_full;
   4 DROP VIEW current_planet_scans;
   5 DROP VIEW current_development_scans;
   6
   7
   8 ALTER TABLE planets ALTER ruler TYPE text;
   9 ALTER TABLE planets ALTER planet TYPE text;
  10 ALTER TABLE planets ALTER nick TYPE citext;
  11 ALTER TABLE planets ALTER channel TYPE citext;
  12 ALTER TABLE alliances RENAME name TO alliance;
  13 ALTER TABLE alliances ALTER alliance TYPE text;
  14 ALTER TABLE alliances RENAME id TO aid;
  15 ALTER TABLE alliance_stats RENAME id TO aid;
  16
  17 CREATE FUNCTION alliance_name(id INTEGER) RETURNS TEXT AS $$
  18         SELECT alliance FROM alliances WHERE aid = $1
  19 $$ LANGUAGE SQL STABLE;
  20
  21 ALTER TABLE planets RENAME id TO pid;
  22 ALTER TABLE planets RENAME alliance_id TO alliance;
  23 ALTER TABLE planetS DROP CONSTRAINT planets_alliance_id_fkey;
  24 ALTER TABLE planets ALTER alliance TYPE text USING alliance_name(alliance);
  25 ALTER TABLE planetS ADD FOREIGN KEY (alliance) REFERENCES alliances(alliance) ON UPDATE CASCADE ON DELETE SET NULL;
  26
  27 DROP FUNCTION alliance_name(INTEGER);
  28
  29 ALTER TABLE planet_stats RENAME id TO pid;
  30 ALTER TABLE users RENAME planet TO pid;
  31 ALTER TABLE fleets RENAME planet TO pid;
  32 ALTER TABLE launch_confirmations RENAME target TO pid;
  33 ALTER TABLE development_scans RENAME planet TO pid;
  34 ALTER TABLE planet_scans RENAME planet TO pid;
  35 ALTER TABLE raid_targets RENAME planet TO pid;
  36 ALTER TABLE scan_requests RENAME planet TO pid;
  37 ALTER TABLE scans RENAME planet TO pid;
  38 ALTER TABLE covop_attacks RENAME id TO pid;
  39 ALTER TABLE incomings RENAME sender TO pid;
  40
  41 CREATE VIEW current_planet_stats AS
  42 SELECT p.pid, p.nick, p.planet_status, p.hit_us, ps.x, ps.y, ps.z, p.ruler, p.planet, p.race
  43         ,alliance, a.relationship, a.aid, p.channel, p.ftid, p.gov
  44         ,ps.size, ps.score, ps.value, ps.xp, ps.sizerank, ps.scorerank, ps.valuerank, ps.xprank
  45 FROM ( SELECT pid, tick, x, y, z, size, score, value, xp, sizerank, scorerank, valuerank, xprank
  46                 FROM planet_stats
  47                 WHERE tick = ( SELECT max(tick) AS max FROM planet_stats)
  48         ) ps
  49         NATURAL JOIN planets p
  50         LEFT JOIN alliances a USING (alliance);
  51
  52 CREATE OR REPLACE VIEW users_defprio AS
  53 SELECT u.*, (0.2 * (u.attack_points / GREATEST(a.attack, 1::numeric))
  54                 + 0.4 * (u.defense_points / GREATEST(a.defense, 1::numeric))
  55                 + 0.2 * (p.size::numeric / a.size) + 0.05 * (p.score::numeric / a.score)
  56                 + 0.15 * (p.value::numeric / a.value))::numeric(3,2) AS defprio
  57 FROM users u
  58         JOIN current_planet_stats p USING (pid)
  59         , (
  60                 SELECT avg(u.attack_points) AS attack, avg(u.defense_points) AS defense
  61                         ,avg(p.size) AS size, avg(p.score) AS score, avg(p.value) AS value
  62                 FROM users u
  63                         JOIN current_planet_stats p USING (pid)
  64                 WHERE uid IN ( SELECT uid FROM groupmembers WHERE gid = 2)
  65         ) a;
  66
  67 CREATE OR REPLACE VIEW current_planet_stats_full AS
  68 SELECT *
  69 FROM planets p
  70         NATURAL JOIN (
  71                 SELECT *
  72                 FROM planet_stats
  73                 WHERE tick = ( SELECT max(tick) AS max FROM planet_stats)
  74         ) ps
  75         LEFT JOIN alliances USING (alliance);
  76
  77 CREATE OR REPLACE FUNCTION change_member() RETURNS trigger
  78     AS $_X$
  79 BEGIN
  80         IF TG_OP = 'INSERT' THEN
  81                 IF NEW.gid = 2 THEN
  82                         UPDATE planets SET alliance = 'NewDawn' WHERE
  83                                 pid = (SELECT pid FROM users WHERE uid = NEW.uid);
  84                 END IF;
  85         ELSIF TG_OP = 'DELETE' THEN
  86                 IF OLD.gid = 2 THEN
  87                         UPDATE planets SET alliance = NULL WHERE
  88                                 pid = (SELECT pid FROM users WHERE uid = OLD.uid);
  89                 END IF;
  90         END IF;
  91
  92         return NEW;
  93 END;
  94 $_X$ LANGUAGE plpgsql;
  95
  96 CREATE OR REPLACE VIEW current_planet_scans AS
  97 SELECT DISTINCT ON (pid) ps.*
  98 FROM planet_scans ps
  99 ORDER BY pid, tick DESC, id DESC;
 100
 101 CREATE OR REPLACE VIEW current_development_scans AS
 102 SELECT DISTINCT ON (pid) ds.*
 103 FROM development_scans ds
 104 ORDER BY pid, tick DESC, id DESC;
 105
 106 CREATE OR REPLACE FUNCTION update_user_planet() RETURNS trigger AS $_X$
 107 BEGIN
 108         IF COALESCE(NEW.pid <> OLD.pid,TRUE) OR NEW.username <> OLD.username THEN
 109                 UPDATE planets SET nick = NULL WHERE pid = OLD.pid;
 110                 UPDATE planets SET nick = NEW.username WHERE pid = NEW.pid;
 111         END IF;
 112
 113         IF COALESCE(NEW.pid <> OLD.pid,TRUE)
 114                         AND (SELECT TRUE FROM groupmembers WHERE gid = 2 AND uid = NEW.uid) THEN
 115                 UPDATE planets SET alliance = NULL WHERE pid = OLD.pid;
 116                 UPDATE planets SET alliance = 'NewDawn' WHERE pid = NEW.pid;
 117         END IF;
 118         RETURN NEW;
 119 END;
 120 $_X$ LANGUAGE plpgsql;
 121
 122 CREATE OR REPLACE FUNCTION planetid(x integer, y integer, z integer, tick integer) RETURNS integer
 123     AS $_$SELECT pid FROM planet_stats WHERE x = $1 AND y = $2 AND z = $3 AND (tick >= $4  OR tick =( SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC LIMIT 1$_$
 124     LANGUAGE sql STABLE;
 125
 126 CREATE OR REPLACE FUNCTION planetcoords(id integer, tick integer, OUT x integer, OUT y integer, OUT z integer) RETURNS record
 127     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$_$
 128     LANGUAGE sql STABLE;
 129
 130 CREATE OR REPLACE VIEW alliance_resources AS
 131 WITH planet_estimates AS (
 132         SELECT ps.tick, alliance, hidden,size,score,(metal+crystal+eonium) AS resources
 133                 ,score + (metal+crystal+eonium)/300 + hidden/100 AS nscore2
 134                 ,score + (metal+crystal+eonium)/300 + hidden/100 + (endtick()-tick())*(
 135                         250*size + COALESCE(metal_ref + crystal_ref + eonium_ref,7)* 1000
 136                         + CASE extraction WHEN 0 THEN 3000 WHEN 1 THEN 11500 ELSE COALESCE(extraction,3)*3000*3 END
 137                 )*(1.35+0.005*COALESCE(fincents,20))/100 AS nscore3
 138         FROM current_planet_stats p
 139                 JOIN current_planet_scans ps USING (pid)
 140                 LEFT OUTER JOIN current_development_scans ds USING (pid)
 141 ), planet_ranks AS (
 142         SELECT *, RANK() OVER(PARTITION BY alliance ORDER BY score DESC) AS rank FROM planet_estimates
 143 ), top_planets AS (
 144         SELECT alliance, sum(resources) AS resources, sum(hidden) AS hidden
 145                 ,sum(nscore2)::bigint AS nscore2, sum(nscore3)::bigint AS nscore3
 146                 ,count(*) AS planets, sum(score) AS score, sum(size) AS size
 147                 ,avg(tick)::int AS avgtick
 148         FROM planet_ranks WHERE rank <= 60
 149         GROUP BY alliance
 150 )
 151 SELECT aid,alliance,a.relationship,s.members,r.planets
 152         ,s.score, r.score AS topscore, s.size, r.size AS topsize
 153         ,r.resources,r.hidden
 154         ,(s.score + (resources / 300) + (hidden / 100))::bigint AS nscore
 155         ,nscore2, nscore3, avgtick
 156 FROM alliances a
 157         JOIN top_planets r USING (alliance)
 158         LEFT OUTER JOIN (SELECT aid,score,size,members FROM alliance_stats
 159                 WHERE tick = (SELECT max(tick) FROM alliance_stats)) s USING (aid)
 160 ;
 161
 162 CREATE OR REPLACE VIEW defcalls AS
 163 SELECT c.id,c.member AS uid, c.landing_tick, covered, open
 164         ,dc.username AS dc, (c.landing_tick - tick()) AS curreta
 165         ,array_agg(COALESCE(race::text,'')) AS race
 166         ,array_agg(COALESCE(amount,0)) AS amount
 167         ,array_agg(COALESCE(eta,0)) AS eta
 168         ,array_agg(COALESCE(shiptype,'')) AS shiptype
 169         ,array_agg(COALESCE(alliance,'?')) AS alliance
 170         ,array_agg(coords(p2.x,p2.y,p2.z)) AS attackers
 171 FROM calls c
 172         LEFT OUTER JOIN incomings i ON c.id = i.call
 173         LEFT OUTER JOIN current_planet_stats p2 USING (pid)
 174         LEFT OUTER JOIN users dc ON c.dc = dc.uid
 175 GROUP BY c.id,c.member,dc.username, c.landing_tick, covered, open;
 176
 177 CREATE OR REPLACE VIEW full_defcalls AS
 178 SELECT id,covered,open,x,y,z,pid,landing_tick,dc,curreta
 179         ,defprio, c.race, amount, c.eta, shiptype, c.alliance, attackers
 180         ,COUNT(NULLIF(f.back = f.landing_tick + f.eta - 1, FALSE)) AS fleets
 181 FROM users_defprio u
 182         JOIN current_planet_stats p USING (pid)
 183         JOIN defcalls c USING (uid)
 184         LEFT OUTER JOIN launch_confirmations f USING (pid,landing_tick)
 185 GROUP BY id, x,y,z,pid,landing_tick,dc,curreta,defprio,c.race,amount,c.eta,shiptype,c.alliance,attackers, covered, open
 186 ;
 187
 188 DROP AGGREGATE array_accum(anyelement);