X-Git-Url: https://ruin.nu/git/?a=blobdiff_plain;f=database%2Fscans.sql;h=bbf0da9a28be070f2b2f98dc154c8897b0101451;hb=83195c3c8034fff520fa90d1d0fb8b7995f925d3;hp=5f789697aa235809afef93a2dfb5240f5d2f4a0c;hpb=c92ebd9fe99a4c1b64a46289c03424980ead0778;p=ndwebbie.git diff --git a/database/scans.sql b/database/scans.sql index 5f78969..bbf0da9 100644 --- a/database/scans.sql +++ b/database/scans.sql @@ -1,103 +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 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; - -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 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;