1 /*CREATE TABLE ticks (t INTEGER PRIMARY KEY);
3 INSERT INTO ticks (SELECT * FROM generate_series(0,10000));
6 CREATE OR REPLACE VIEW ships_home AS
7 SELECT tick, uid,username, pid, ship
8 , COALESCE(f.amount - o.amount,f.amount) AS amount
9 , COALESCE(fleets,3) AS fleets
11 SELECT t AS tick, pid,ship,amount
13 SELECT DISTINCT ON (t,pid,mission) t,pid,mission, fid
17 AND name IN ('Main','Advanced Unit')
18 AND mission = 'Full fleet'
19 ORDER BY t,pid,mission,tick DESC, fid DESC
21 JOIN fleet_ships fs USING (fid)
23 ) f USING (pid) LEFT OUTER JOIN (
24 SELECT t AS tick, pid, ship, SUM(fs.amount) AS amount
25 , 3 - COUNT(DISTINCT fid) AS fleets
28 JOIN (SELECT landing_tick, fid, back, eta FROM launch_confirmations) lc USING (fid)
29 JOIN fleet_ships fs USING (fid)
31 AND landing_tick - eta - 12 < t
33 ) o USING (tick,pid,ship)
34 WHERE COALESCE(f.amount - o.amount,f.amount) > 0;
36 CREATE OR REPLACE VIEW available_ships AS
37 SELECT uid,username, pid, ship, amount, fleets