Show members available ships on def calls

[ndwebbie.git] / database / available_ships.sql

diff --git a/database/available_ships.sql b/database/available_ships.sql
index e2905836739c24eae77675fa7d3f8d10d85d5ad5..ecd2ad788176a1f347b8a6d536b29745cca0c16c 100644 (file)
--- a/database/available_ships.sql
+++ b/database/available_ships.sql
@@ -1,28 +1,38 @@
-DROP VIEW available_ships;
-CREATE VIEW available_ships AS
-SELECT uid,username, planet, ship
+CREATE TABLE ticks (t INTEGER PRIMARY KEY);
+
+INSERT INTO ticks (SELECT * FROM generate_series(0,10000));
+
+CREATE OR REPLACE VIEW ships_home AS
+SELECT tick, uid,username, planet, ship
        , COALESCE(f.amount - o.amount,f.amount) AS amount
        , COALESCE(fleets,3) AS fleets
 FROM users u JOIN (
-       SELECT planet,ship,amount
+       SELECT t AS tick, planet,ship,amount
        FROM (
-               SELECT DISTINCT ON (planet,mission) planet,mission, fid
-               FROM fleets f
-               WHERE tick <= tick()
+               SELECT DISTINCT ON (t,planet,mission) t,planet,mission, fid
+               FROM ticks
+                       CROSS JOIN fleets f
+               WHERE tick <= t
                        AND name IN ('Main','Advanced Unit')
                        AND mission = 'Full fleet'
-               ORDER BY planet,mission,tick DESC, fid DESC
+               ORDER BY t,planet,mission,tick DESC, fid DESC
        ) f
                JOIN fleet_ships fs USING (fid)
        
 ) f USING (planet) LEFT OUTER JOIN (
-       SELECT planet, ship, SUM(fs.amount) AS amount, 3 - COUNT(DISTINCT fid) AS fleets
-       FROM fleets f
+       SELECT t AS tick, planet, ship, SUM(fs.amount) AS amount
+               , 3 - COUNT(DISTINCT fid) AS fleets
+       FROM ticks
+               CROSS JOIN fleets f
                JOIN launch_confirmations USING (fid)
                JOIN fleet_ships fs USING (fid)
-       WHERE back > tick()
-               AND landing_tick - eta - 12 < tick()
-       GROUP BY planet,ship
-) o USING (planet,ship)
-WHERE COALESCE(f.amount - o.amount,f.amount) > 0
+       WHERE back > t
+               AND landing_tick - eta - 12 < t
+       GROUP BY t,planet,ship
+) o USING (tick,planet,ship)
+WHERE COALESCE(f.amount - o.amount,f.amount) > 0;
 
+CREATE OR REPLACE VIEW available_ships AS
+SELECT uid,username, planet, ship, amount, fleets
+FROM ships_home
+WHERE tick = tick();