X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=database%2Fscans.sql;h=b561e33cca3cfc54003564595c1519cf08c785d7;hb=fb2bbffe375681514dff83be2f9ed25e0d54bde3;hp=cbd44fe7cad130709c5f463aad5d8bd009dd96d2;hpb=14e523fcb4dc6eb3ce780e0780959187c2610a6f;p=ndwebbie.git

diff --git a/database/scans.sql b/database/scans.sql
index cbd44fe..b561e33 100644
--- a/database/scans.sql
+++ b/database/scans.sql
@@ -1,3 +1,4 @@
+DELETE FROM fleets;
 ALTER TABLE fleets ADD COLUMN sender INTEGER NOT NULL REFERENCES planets(id) ON UPDATE CASCADE ON DELETE CASCADE;
 
 ALTER TABLE fleets DROP COLUMN fleet;
@@ -18,12 +19,6 @@ ALTER TABLE fleets RENAME COLUMN landing_tick TO tick;
 
 ALTER TABLE fleet_ships RENAME COLUMN fleet TO id;
 
-
-CREATE TABLE fleet_scans (
-	id INTEGER PRIMARY KEY REFERENCES fleets(id),
-	scan INTEGER NOT NULL REFERENCES scans(id)
-) WITHOUT OIDS;
-
 ALTER TABLE scans DROP COLUMN scan;
 
 ALTER TABLE scans DROP COLUMN type;
@@ -36,10 +31,17 @@ ALTER TABLE scans ADD COLUMN groupscan BOOLEAN NOT NULL DEFAULT False;
 
 ALTER TABLE scans ADD COLUMN parsed BOOLEAN NOT NULL DEFAULT False;
 
+ALTER TABLE scans DROP CONSTRAINT scans_pkey;
+
 ALTER TABLE scans ADD COLUMN id SERIAL PRIMARY KEY;
 
 ALTER TABLE scans ADD UNIQUE (scan_id, tick, groupscan);
 
+CREATE TABLE fleet_scans (
+	id INTEGER PRIMARY KEY REFERENCES fleets(id),
+	scan INTEGER NOT NULL REFERENCES scans(id)
+) WITHOUT OIDS;
+
 CREATE OR REPLACE FUNCTION planetid(x integer, y integer, z integer, tick integer) RETURNS integer
     AS $_$SELECT id FROM planet_stats WHERE x = $1 AND y = $2 AND z = $3 AND (tick >= $4  OR tick =( SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC LIMIT 1$_$
     LANGUAGE sql STABLE;
@@ -125,4 +127,7 @@ FROM scans s
 		WHERE rid = 18) AS d USING (id)
 	JOIN (SELECT scan AS id,amount AS seccents FROM planet_data
 		WHERE rid = 24) AS sc USING (id)
-ORDER BY planet,tick DESC, id DESC
+ORDER BY planet,tick DESC, id DESC;
+
+
+CREATE INDEX planet_data_id_index ON planet_data (id);