1
BEGIN
2
-- temp table
3
CREATE TEMPORARY TABLE tgroup (KEY (`pri`))
4
SELECT m2.groupid AS pri, m.groupid FROM mastergroups m, mastergroups m2 WHERE m.groupid=m2.groupid OR FIND_IN_SET(m.groupid,m2.children);
5
-- temp table for computer data
6
CREATE TEMPORARY TABLE tcomputer (KEY (`pri`,`ComputerId`))
7
SELECT DISTINCT tgroup.pri, s.computerid FROM tgroup JOIN subgroups s USING(groupid);
8
-- computer data to remove.
9
DELETE swc FROM subgroupwchildren swc LEFT JOIN tcomputer AS v ON swc.groupid=v.pri AND swc.computerid=v.computerid WHERE v.pri IS NULL AND v.computerid IS NULL;
10
-- computer data to add
11
INSERT INTO subgroupwchildren (GroupID, ComputerID) SELECT v.pri, v.computerid FROM subgroupwchildren swc
12
RIGHT JOIN tcomputer AS v ON swc.groupid= v.pri AND swc.computerid = v.computerid WHERE swc.groupid IS NULL AND swc.computerid IS NULL;
13
-- remove temp table
14
DROP TEMPORARY TABLE tcomputer;
15
-- temp table for contact data
16
CREATE TEMPORARY TABLE tcontact (KEY (`pri`,`contactId`))
17
SELECT DISTINCT tgroup.pri, s.contactid FROM tgroup JOIN subgroupscontacts s USING(groupid);
18
-- contacts data to remove
19
DELETE swcc FROM subgroupwchildrencontacts swcc LEFT JOIN tcontact AS v ON swcc.groupid=v.pri AND swcc.contactid=v.contactid WHERE v.pri IS NULL AND v.contactid IS NULL;
20
-- contacts data to add
21
INSERT INTO subgroupwchildrencontacts (GroupId, ContactId) SELECT v.pri, v.contactid FROM subgroupwchildrencontacts swcc
22
RIGHT JOIN tcontact AS v ON swcc.groupid=v.pri AND swcc.contactid=v.contactid WHERE swcc.groupid IS NULL AND swcc.contactid IS NULL;
23
-- remove temp table
24
DROP TEMPORARY TABLE tcontact;
25
-- temp table for network device data
26
CREATE TEMPORARY TABLE tnetwork (KEY (`pri`,`deviceId`))
27
SELECT DISTINCT tgroup.pri, s.deviceid FROM tgroup JOIN subgroupsnetworkdevices s USING(groupid);
28
-- network device data to remove
29
DELETE swcnd FROM subgroupwchildrennetworkdevices swcnd LEFT JOIN tnetwork AS v ON swcnd.groupid=v.pri AND swcnd.deviceid=v.deviceid WHERE v.pri IS NULL AND v.deviceid IS NULL;
30
-- network device data to add
31
INSERT INTO subgroupwchildrennetworkdevices (GroupId, DeviceId) SELECT v.pri, v.deviceid FROM subgroupwchildrennetworkdevices swcnd
32
RIGHT JOIN tnetwork AS v ON swcnd.groupid=v.pri AND swcnd.deviceid=v.deviceid WHERE swcnd.groupid IS NULL AND swcnd.deviceid IS NULL;
33
-- remove temp table
34
DROP TEMPORARY TABLE tnetwork;
35
-- remove initial temp table
36
DROP TEMPORARY TABLE tgroup;
37
END