database/available_ships.sql

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