-ALTER TABLE fleets ADD COLUMN sender INTEGER NOT NULL REFERENCES planets(id) ON UPDATE CASCADE ON DELETE CASCADE;
-
-ALTER TABLE fleets DROP COLUMN fleet;
-
-ALTER TABLE fleets ALTER COLUMN target DROP NOT NULL;
-
-ALTER TABLE fleets ALTER COLUMN back DROP NOT NULL;
-
-ALTER TABLE fleets ALTER COLUMN eta DROP NOT NULL;
-
-ALTER TABLE fleets ADD COLUMN amount INTEGER;
-
-ALTER TABLE fleets ADD COLUMN name TEXT NOT NULL;
-
-ALTER TABLE fleets ADD COLUMN ingal BOOLEAN NOT NULL DEFAULT false;
-
-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;
-
-ALTER TABLE scans ADD COLUMN type TEXT;
-
-ALTER TABLE scans ADD COLUMN uid INTEGER NOT NULL DEFAULT -1 REFERENCES users(uid) ON UPDATE RESTRICT ON DELETE RESTRICT;
-
-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 ADD COLUMN id SERIAL PRIMARY KEY;
-
-ALTER TABLE scans ADD UNIQUE (scan_id, tick, groupscan);
-
-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;
-
-CREATE OR REPLACE FUNCTION planetcoords(IN id integer,IN tick integer, OUT x integer,OUT y integer,OUT z integer)
- AS $_$SELECT x,y,z FROM planet_stats WHERE id = $1 AND (tick >= $2 OR tick =( SELECT max(tick) FROM planet_stats)) ORDER BY tick ASC LIMIT 1$_$
- LANGUAGE sql STABLE;
-
-CREATE TABLE planet_data_types (
- id SERIAL PRIMARY KEY,
- category TEXT NOT NULL,
- name TEXT NOT NULL,
- UNIQUE (category,name)
-) WITHOUT OIDS;
-
-INSERT INTO planet_data_types (category,name) VALUES('roid','Metal');
-INSERT INTO planet_data_types (category,name) VALUES('roid','Crystal');
-INSERT INTO planet_data_types (category,name) VALUES('roid','Eonium');
-INSERT INTO planet_data_types (category,name) VALUES('resource','Metal');
-INSERT INTO planet_data_types (category,name) VALUES('resource','Crystal');
-INSERT INTO planet_data_types (category,name) VALUES('resource','Eonium');
-INSERT INTO planet_data_types (category,name) VALUES('tech','Space Travel');
-INSERT INTO planet_data_types (category,name) VALUES('tech','Infrastructure');
-INSERT INTO planet_data_types (category,name) VALUES('tech','Hulls');
-INSERT INTO planet_data_types (category,name) VALUES('tech','Waves');
-INSERT INTO planet_data_types (category,name) VALUES('tech','Core Extraction');
-INSERT INTO planet_data_types (category,name) VALUES('tech','Covert Ops');
-INSERT INTO planet_data_types (category,name) VALUES('tech','Asteroid Mining');
-INSERT INTO planet_data_types (category,name) VALUES('struc','Light Factory');
-INSERT INTO planet_data_types (category,name) VALUES('struc','Medium Factory');
-INSERT INTO planet_data_types (category,name) VALUES('struc','Heavy Factory');
-INSERT INTO planet_data_types (category,name) VALUES('struc','Wave Amplifier');
-INSERT INTO planet_data_types (category,name) VALUES('struc','Wave Distorter');
-INSERT INTO planet_data_types (category,name) VALUES('struc','Metal Refinery');
-INSERT INTO planet_data_types (category,name) VALUES('struc','Crystal Refinery');
-INSERT INTO planet_data_types (category,name) VALUES('struc','Eonium Refinery');
-INSERT INTO planet_data_types (category,name) VALUES('struc','Research Laboratory');
-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),
- 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)
-) WITHOUT OIDS;
-
-DROP TABLE intel;
+DROP VIEW planet_scans;
+DROP VIEW structure_scans;
+
+CREATE TABLE planet_scans (
+ id integer PRIMARY KEY REFERENCES scans(id),
+ planet integer NOT NULL REFERENCES planets(id),
+ tick integer NOT NULL,
+ metal bigint NOT NULL,
+ crystal bigint NOT NULL,
+ eonium bigint NOT NULL,
+ hidden bigint NOT NULL,
+ metal_roids integer NOT NULL,
+ crystal_roids integer NOT NULL,
+ eonium_roids integer NOT NULL,
+ agents integer NOT NULL,
+ guards integer NOT NULL,
+ light TEXT NOT NULL,
+ medium TEXT NOT NULL,
+ heavy TEXT NOT NULL
+);
+
+
+CREATE TABLE structure_scans (
+ id integer PRIMARY KEY REFERENCES scans(id),
+ planet integer NOT NULL REFERENCES planets(id),
+ tick integer NOT NULL,
+ light_fac integer NOT NULL,
+ medium_fac integer NOT NULL,
+ heavy_fac integer NOT NULL,
+ amps integer NOT NULL,
+ distorters integer NOT NULL,
+ metal_ref integer NOT NULL,
+ crystal_ref integer NOT NULL,
+ eonium_ref integer NOT NULL,
+ reslabs integer NOT NULL,
+ fincents integer NOT NULL,
+ seccents integer NOT NULL,
+ total integer NOT NULL
+);
+
+CREATE TABLE tech_scans (
+ id integer PRIMARY KEY REFERENCES scans(id),
+ planet integer NOT NULL REFERENCES planets(id),
+ tick integer NOT NULL,
+ travel integer NOT NULL,
+ infra integer NOT NULL,
+ hulls integer NOT NULL,
+ waves integer NOT NULL,
+ extraction integer NOT NULL,
+ covert integer NOT NULL,
+ mining integer NOT NULL
+);
+
+CREATE OR REPLACE VIEW current_planet_scans AS
+SELECT DISTINCT ON (planet) ps.*
+FROM planet_scans ps
+ORDER BY planet, tick DESC, id DESC;
+
+CREATE OR REPLACE VIEW current_structure_scans AS
+SELECT DISTINCT ON (planet) ss.*
+FROM structure_scans ss
+ORDER BY planet, tick DESC, id DESC;
+
+CREATE OR REPLACE VIEW current_tech_scans AS
+SELECT DISTINCT ON (planet) ts.*
+FROM tech_scans ts
+ORDER BY planet, tick DESC, id DESC;
+
+CREATE INDEX planet_scans_planet_index ON planet_scans(planet,tick);
+CREATE INDEX structure_scans_planet_index ON structure_scans(planet,tick);
+CREATE INDEX tech_scans_planet_index ON tech_scans(planet,tick);
+
+DROP TABLE planet_data;
+DROP TABLE planet_data_types;
projects / ndwebbie.git / blobdiff