1 DROP VIEW IF EXISTS full_defcalls;
2 DROP VIEW IF EXISTS defcalls;
4 CREATE TABLE call_statuses (
5 status TEXT PRIMARY KEY
8 INSERT INTO call_statuses VALUES('Open'),('Covered'),('Ignored');
10 ALTER TABLE calls ADD COLUMN status TEXT NOT NULL REFERENCES call_statuses(status)
13 UPDATE calls SET status = (CASE WHEN covered THEN 'Covered' WHEN NOT OPEN THEN
14 'Ignored' ELSE 'Open' END);
16 ALTER TABLE calls DROP COLUMN open;
17 ALTER TABLE calls DROP COLUMN covered;
18 ALTER TABLE calls DROP COLUMN shiptypes;
19 ALTER TABLE calls RENAME id TO call;
20 ALTER TABLE calls RENAME member TO uid;
21 ALTER TABLE incomings RENAME id TO inc;
23 CREATE OR REPLACE VIEW defcalls AS
24 SELECT call, status,c.uid, c.landing_tick
25 ,dc.username AS dc, (c.landing_tick - tick()) AS curreta
26 ,array_agg(COALESCE(race::text,'')) AS race
27 ,array_agg(COALESCE(amount,0)) AS amount
28 ,array_agg(COALESCE(eta,0)) AS eta
29 ,array_agg(COALESCE(shiptype,'')) AS shiptype
30 ,array_agg(COALESCE(alliance,'?')) AS alliance
31 ,array_agg(coords(p2.x,p2.y,p2.z)) AS attackers
33 LEFT OUTER JOIN incomings i USING (call)
34 LEFT OUTER JOIN current_planet_stats p2 USING (pid)
35 LEFT OUTER JOIN users dc ON c.dc = dc.uid
36 GROUP BY call,c.uid,dc.username, c.landing_tick, status;
38 CREATE OR REPLACE VIEW full_defcalls AS
39 SELECT call,status,x,y,z,pid,landing_tick,dc,curreta
40 ,defprio, c.race, amount, c.eta, shiptype, c.alliance, attackers
41 ,COUNT(NULLIF(f.back = f.landing_tick + f.eta - 1, FALSE)) AS fleets
43 JOIN current_planet_stats p USING (pid)
44 JOIN defcalls c USING (uid)
45 LEFT OUTER JOIN launch_confirmations f USING (pid,landing_tick)
46 GROUP BY call, x,y,z,pid,landing_tick,dc,curreta,defprio,c.race,amount,c.eta,shiptype,c.alliance,attackers, status
49 CREATE OR REPLACE FUNCTION add_call() RETURNS trigger
54 INSERT INTO forum_threads (fbid,subject,uid)
55 VALUES(-3,NEW.uid || ': ' || NEW.landing_tick,-3) RETURNING ftid
60 $_X$ LANGUAGE plpgsql;