oops, need to do things in the right order

[ndwebbie.git] / database / scans.sql

diff --git a/database/scans.sql b/database/scans.sql
index 5f789697aa235809afef93a2dfb5240f5d2f4a0c..b561e33cca3cfc54003564595c1519cf08c785d7 100644 (file)
--- 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;
@@ -81,17 +83,12 @@ INSERT INTO planet_data_types (category,name) VALUES('struc','Finance Centre');
 INSERT INTO planet_data_types (category,name) VALUES('struc','Security Centre');
 
 CREATE TABLE planet_data (
-       id SERIAL PRIMARY KEY,
-       uid INTEGER NOT NULL REFERENCES users(uid),
-       planet INTEGER NOT NULL REFERENCES planets(id),
+       id INTEGER NOT NULL REFERENCES planets(id),
+       scan INTEGER NOT NULL REFERENCES scans(id),
        tick INTEGER NOT NULL,
        rid INTEGER NOT NULL REFERENCES planet_data_types(id),
-       amount INTEGER NOT NULL
-) WITHOUT OIDS;
-
-CREATE TABLE data_scans (
-       id INTEGER PRIMARY KEY REFERENCES planet_data(id),
-       scan INTEGER NOT NULL REFERENCES scans(id)
+       amount INTEGER NOT NULL,
+       PRIMARY KEY(rid,scan)
 ) WITHOUT OIDS;
 
 DROP TABLE intel;
@@ -101,3 +98,36 @@ CREATE INDEX fleets_target_index ON fleets (target);
 CREATE INDEX fleets_sender_index ON fleets (sender);
 CREATE INDEX fleets_mission_index ON fleets (mission);
 CREATE INDEX fleets_ingal_index ON fleets (ingal);
+
+DROP TABLE covop_targets ;
+
+CREATE OR REPLACE VIEW planet_scans AS
+SELECT DISTINCT ON (planet) id,planet,tick,metal,crystal,eonium,metal_roids,crystal_roids,eonium_roids
+FROM scans s
+       JOIN (SELECT scan AS id,amount AS metal_roids FROM planet_data
+               WHERE rid = 1) AS mr USING (id)
+       JOIN (SELECT scan AS id,amount AS crystal_roids FROM planet_data
+               WHERE rid = 2) AS cr USING (id)
+       JOIN (SELECT scan AS id,amount AS eonium_roids FROM planet_data
+               WHERE rid = 3) AS er USING (id)
+       JOIN (SELECT scan AS id,amount AS metal FROM planet_data
+               WHERE rid = 4) AS m USING (id)
+       JOIN (SELECT scan AS id,amount AS crystal FROM planet_data
+               WHERE rid = 5) AS c USING (id)
+       JOIN (SELECT scan AS id,amount AS eonium FROM planet_data
+               WHERE rid = 6) AS e USING (id)
+ORDER BY planet,tick DESC,id DESC;
+
+CREATE OR REPLACE VIEW structure_scans AS
+SELECT DISTINCT ON (planet) id,planet,tick, total,distorters,seccents
+FROM scans s
+       JOIN (SELECT scan AS id, SUM(amount) AS total FROM planet_data
+               WHERE rid >= 14 AND rid <= 24 GROUP BY scan) AS t USING (id)
+       JOIN (SELECT scan AS id,amount AS distorters FROM planet_data
+               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;
+
+
+CREATE INDEX planet_data_id_index ON planet_data (id);