database/scans.sql

   1 DELETE FROM fleets;
   2 ALTER TABLE fleets ADD COLUMN sender INTEGER NOT NULL REFERENCES planets(id) ON UPDATE CASCADE ON DELETE CASCADE;
   3
   4 ALTER TABLE fleets DROP COLUMN fleet;
   5
   6 ALTER TABLE fleets ALTER COLUMN target DROP NOT NULL;
   7
   8 ALTER TABLE fleets ALTER COLUMN back DROP NOT NULL;
   9
  10 ALTER TABLE fleets ALTER COLUMN eta DROP NOT NULL;
  11
  12 ALTER TABLE fleets ADD COLUMN amount INTEGER;
  13
  14 ALTER TABLE fleets ADD COLUMN name TEXT NOT NULL;
  15
  16 ALTER TABLE fleets ADD COLUMN ingal BOOLEAN NOT NULL DEFAULT false;
  17
  18 ALTER TABLE fleets RENAME COLUMN landing_tick TO tick;
  19
  20 ALTER TABLE fleet_ships RENAME COLUMN fleet TO id;
  21
  22
  23 CREATE TABLE fleet_scans (
  24         id INTEGER PRIMARY KEY REFERENCES fleets(id),
  25         scan INTEGER NOT NULL REFERENCES scans(id)
  26 ) WITHOUT OIDS;
  27
  28 ALTER TABLE scans DROP COLUMN scan;
  29
  30 ALTER TABLE scans DROP COLUMN type;
  31
  32 ALTER TABLE scans ADD COLUMN type TEXT;
  33
  34 ALTER TABLE scans ADD COLUMN uid INTEGER NOT NULL DEFAULT -1 REFERENCES users(uid) ON UPDATE RESTRICT ON DELETE RESTRICT;
  35
  36 ALTER TABLE scans ADD COLUMN groupscan BOOLEAN NOT NULL DEFAULT False;
  37
  38 ALTER TABLE scans ADD COLUMN parsed BOOLEAN NOT NULL DEFAULT False;
  39
  40 ALTER TABLE scans DROP CONSTRAINT scans_pkey;
  41
  42 ALTER TABLE scans ADD COLUMN id SERIAL PRIMARY KEY;
  43
  44 ALTER TABLE scans ADD UNIQUE (scan_id, tick, groupscan);
  45
  46 CREATE OR REPLACE FUNCTION planetid(x integer, y integer, z integer, tick integer) RETURNS integer
  47     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$_$
  48     LANGUAGE sql STABLE;
  49
  50 CREATE OR REPLACE FUNCTION planetcoords(IN id integer,IN tick integer, OUT x integer,OUT y integer,OUT z integer)
  51     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$_$
  52     LANGUAGE sql STABLE;
  53
  54 CREATE TABLE planet_data_types (
  55         id SERIAL PRIMARY KEY,
  56         category TEXT NOT NULL,
  57         name TEXT NOT NULL,
  58         UNIQUE (category,name)
  59 ) WITHOUT OIDS;
  60
  61 INSERT INTO planet_data_types (category,name) VALUES('roid','Metal');
  62 INSERT INTO planet_data_types (category,name) VALUES('roid','Crystal');
  63 INSERT INTO planet_data_types (category,name) VALUES('roid','Eonium');
  64 INSERT INTO planet_data_types (category,name) VALUES('resource','Metal');
  65 INSERT INTO planet_data_types (category,name) VALUES('resource','Crystal');
  66 INSERT INTO planet_data_types (category,name) VALUES('resource','Eonium');
  67 INSERT INTO planet_data_types (category,name) VALUES('tech','Space Travel');
  68 INSERT INTO planet_data_types (category,name) VALUES('tech','Infrastructure');
  69 INSERT INTO planet_data_types (category,name) VALUES('tech','Hulls');
  70 INSERT INTO planet_data_types (category,name) VALUES('tech','Waves');
  71 INSERT INTO planet_data_types (category,name) VALUES('tech','Core Extraction');
  72 INSERT INTO planet_data_types (category,name) VALUES('tech','Covert Ops');
  73 INSERT INTO planet_data_types (category,name) VALUES('tech','Asteroid Mining');
  74 INSERT INTO planet_data_types (category,name) VALUES('struc','Light Factory');
  75 INSERT INTO planet_data_types (category,name) VALUES('struc','Medium Factory');
  76 INSERT INTO planet_data_types (category,name) VALUES('struc','Heavy Factory');
  77 INSERT INTO planet_data_types (category,name) VALUES('struc','Wave Amplifier');
  78 INSERT INTO planet_data_types (category,name) VALUES('struc','Wave Distorter');
  79 INSERT INTO planet_data_types (category,name) VALUES('struc','Metal Refinery');
  80 INSERT INTO planet_data_types (category,name) VALUES('struc','Crystal Refinery');
  81 INSERT INTO planet_data_types (category,name) VALUES('struc','Eonium Refinery');
  82 INSERT INTO planet_data_types (category,name) VALUES('struc','Research Laboratory');
  83 INSERT INTO planet_data_types (category,name) VALUES('struc','Finance Centre');
  84 INSERT INTO planet_data_types (category,name) VALUES('struc','Security Centre');
  85
  86 CREATE TABLE planet_data (
  87         id INTEGER NOT NULL REFERENCES planets(id),
  88         scan INTEGER NOT NULL REFERENCES scans(id),
  89         tick INTEGER NOT NULL,
  90         rid INTEGER NOT NULL REFERENCES planet_data_types(id),
  91         amount INTEGER NOT NULL,
  92         PRIMARY KEY(rid,scan)
  93 ) WITHOUT OIDS;
  94
  95 DROP TABLE intel;
  96
  97 CREATE INDEX fleets_tick_index ON fleets (tick);
  98 CREATE INDEX fleets_target_index ON fleets (target);
  99 CREATE INDEX fleets_sender_index ON fleets (sender);
 100 CREATE INDEX fleets_mission_index ON fleets (mission);
 101 CREATE INDEX fleets_ingal_index ON fleets (ingal);
 102
 103 DROP TABLE covop_targets ;
 104
 105 CREATE OR REPLACE VIEW planet_scans AS
 106 SELECT DISTINCT ON (planet) id,planet,tick,metal,crystal,eonium,metal_roids,crystal_roids,eonium_roids
 107 FROM scans s
 108         JOIN (SELECT scan AS id,amount AS metal_roids FROM planet_data
 109                 WHERE rid = 1) AS mr USING (id)
 110         JOIN (SELECT scan AS id,amount AS crystal_roids FROM planet_data
 111                 WHERE rid = 2) AS cr USING (id)
 112         JOIN (SELECT scan AS id,amount AS eonium_roids FROM planet_data
 113                 WHERE rid = 3) AS er USING (id)
 114         JOIN (SELECT scan AS id,amount AS metal FROM planet_data
 115                 WHERE rid = 4) AS m USING (id)
 116         JOIN (SELECT scan AS id,amount AS crystal FROM planet_data
 117                 WHERE rid = 5) AS c USING (id)
 118         JOIN (SELECT scan AS id,amount AS eonium FROM planet_data
 119                 WHERE rid = 6) AS e USING (id)
 120 ORDER BY planet,tick DESC,id DESC;
 121
 122 CREATE OR REPLACE VIEW structure_scans AS
 123 SELECT DISTINCT ON (planet) id,planet,tick, total,distorters,seccents
 124 FROM scans s
 125         JOIN (SELECT scan AS id, SUM(amount) AS total FROM planet_data
 126                 WHERE rid >= 14 AND rid <= 24 GROUP BY scan) AS t USING (id)
 127         JOIN (SELECT scan AS id,amount AS distorters FROM planet_data
 128                 WHERE rid = 18) AS d USING (id)
 129         JOIN (SELECT scan AS id,amount AS seccents FROM planet_data
 130                 WHERE rid = 24) AS sc USING (id)
 131 ORDER BY planet,tick DESC, id DESC;
 132
 133
 134 CREATE INDEX planet_data_id_index ON planet_data (id);