database/views/available_ships.sql

   1 /*CREATE TABLE ticks (t INTEGER PRIMARY KEY);
   2
   3 INSERT INTO ticks (SELECT * FROM generate_series(0,10000));
   4 */
   5
   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
  10 FROM users u JOIN (
  11         SELECT t AS tick, pid,ship,amount
  12         FROM (
  13                 SELECT DISTINCT ON (t,pid,mission) t,pid,mission, fid
  14                 FROM ticks
  15                         CROSS JOIN fleets f
  16                 WHERE tick <= t
  17                         AND name IN ('Main','Advanced Unit')
  18                         AND mission = 'Full fleet'
  19                 ORDER BY t,pid,mission,tick DESC, fid DESC
  20         ) f
  21                 JOIN fleet_ships fs USING (fid)
  22
  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
  26         FROM ticks
  27                 CROSS JOIN fleets f
  28                 JOIN (SELECT landing_tick, fid, back, eta FROM launch_confirmations) lc USING (fid)
  29                 JOIN fleet_ships fs USING (fid)
  30         WHERE back > t
  31                 AND landing_tick - eta - 12 < t
  32         GROUP BY t,pid,ship
  33 ) o USING (tick,pid,ship)
  34 WHERE COALESCE(f.amount - o.amount,f.amount) > 0;
  35
  36 CREATE OR REPLACE VIEW available_ships AS
  37 SELECT uid,username, pid, ship, amount, fleets
  38 FROM ships_home
  39 WHERE tick = tick();