X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=database%2Fscans.sql;h=bbf0da9a28be070f2b2f98dc154c8897b0101451;hb=7d478421939145912a4155865ad6b521e1bff08f;hp=bd7d3f3b621fe7311a1b57f67d7bf0b878fd5108;hpb=33e9d7fc67e7e25fd9dee023c753d80f31e21f54;p=ndwebbie.git diff --git a/database/scans.sql b/database/scans.sql index bd7d3f3..bbf0da9 100644 --- a/database/scans.sql +++ b/database/scans.sql @@ -1,131 +1,74 @@ -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 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, - PRIMARY KEY(rid,scan) -) WITHOUT OIDS; - -DROP TABLE intel; - -CREATE INDEX fleets_tick_index ON fleets (tick); -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); +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;