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