X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=database%2Favailable_ships.sql;h=e7d70b7d95315cf8c3c798792103891287473606;hb=30262dafa6c73a1d1cbb493e66b59e267eaa6682;hp=ecd2ad788176a1f347b8a6d536b29745cca0c16c;hpb=3eb2598e22ae88c25f5129f169ccb4461fe6962d;p=ndwebbie.git

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