-
Notifications
You must be signed in to change notification settings - Fork 0
/
EVE2monoQueries.sql
69 lines (52 loc) · 1.86 KB
/
EVE2monoQueries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
use CS340_nickersr;
DROP PROCEDURE IF EXISTS SP_insert_ship_deep;
DROP PROCEDURE IF EXISTS SP_insert_station_deep;
DELIMITER //
DROP PROCEDURE IF EXISTS SP_insert_ship_deep //
CREATE PROCEDURE SP_insert_ship_deep()
BEGIN
INSERT INTO view_CS_aggregate (id)
SELECT CS.id FROM EVE2_CargoSpace as CS
WHERE CS.object_id IN (
SELECT id FROM view_obj_aggregate
);
INSERT INTO view_obj_aggregate (id)
SELECT obj.id FROM EVE2_Objects as obj
WHERE obj.cargoSpace_id IN (
SELECT id FROM view_CS_aggregate
);
END //
DROP PROCEDURE IF EXISTS SP_insert_station_deep //
CREATE PROCEDURE SP_insert_station_deep()
BEGIN
INSERT INTO view_CS_aggregate (id)
SELECT CS.id FROM EVE2_CargoSpace as CS
WHERE CS.object_id IN (
SELECT id FROM view_obj_aggregate
);
INSERT INTO view_obj_aggregate (id)
SELECT obj.id FROM EVE2_Objects as obj
WHERE obj.cargoSpace_id IN (
SELECT id FROM view_CS_aggregate
);
DROP VIEW if exists temp;
CREATE VIEW temp as SELECT id FROM view_CS_aggregate;
INSERT INTO view_CS_aggregate (id)
SELECT CS.id FROM EVE2_CargoSpace as CS
WHERE CS.object_id IN (
SELECT id FROM view_obj_aggregate
) AND NOT IN (
SELECT id FROM temp
);
DROP VIEW IF EXISTS temp;
CREATE VIEW temp as SELECT id FROM view_obj_aggregate;
INSERT INTO view_obj_aggregate (id)
SELECT obj.id FROM EVE2_Objects as obj
WHERE obj.cargoSpace_id IN (
SELECT id FROM view_CS_aggregate
) AND NOT IN (
SELECT id FROM temp
);
DROP VIEW IF EXISTS temp;
END //
DELIMITER ;