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;
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;
17 CREATE FUNCTION alliance_name(id INTEGER) RETURNS TEXT AS $$
18 SELECT alliance FROM alliances WHERE aid = $1
19 $$ LANGUAGE SQL STABLE;
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;
27 DROP FUNCTION alliance_name(INTEGER);
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;
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
47 WHERE tick = ( SELECT max(tick) AS max FROM planet_stats)
49 NATURAL JOIN planets p
50 LEFT JOIN alliances a USING (alliance);
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
58 JOIN current_planet_stats p USING (pid)
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
63 JOIN current_planet_stats p USING (pid)
64 WHERE uid IN ( SELECT uid FROM groupmembers WHERE gid = 2)
67 CREATE OR REPLACE VIEW current_planet_stats_full AS
73 WHERE tick = ( SELECT max(tick) AS max FROM planet_stats)
75 LEFT JOIN alliances USING (alliance);
77 CREATE OR REPLACE FUNCTION change_member() RETURNS trigger
80 IF TG_OP = 'INSERT' THEN
82 UPDATE planets SET alliance = 'NewDawn' WHERE
83 pid = (SELECT pid FROM users WHERE uid = NEW.uid);
85 ELSIF TG_OP = 'DELETE' THEN
87 UPDATE planets SET alliance = NULL WHERE
88 pid = (SELECT pid FROM users WHERE uid = OLD.uid);
94 $_X$ LANGUAGE plpgsql;
96 CREATE OR REPLACE VIEW current_planet_scans AS
97 SELECT DISTINCT ON (pid) ps.*
99 ORDER BY pid, tick DESC, id DESC;
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;
106 CREATE OR REPLACE FUNCTION update_user_planet() RETURNS trigger AS $_X$
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;
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;
120 $_X$ LANGUAGE plpgsql;
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$_$
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$_$
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)
142 SELECT *, RANK() OVER(PARTITION BY alliance ORDER BY score DESC) AS rank FROM planet_estimates
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
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
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)
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
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;
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
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
188 DROP AGGREGATE array_accum(anyelement);