labtech Database

Parameters

Name	Type	Mode

Definition

BEGIN
-- temp table
	CREATE TEMPORARY TABLE tgroup (KEY (`pri`))
	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);
	-- temp table for computer data 
	CREATE TEMPORARY TABLE tcomputer (KEY (`pri`,`ComputerId`))
	SELECT DISTINCT tgroup.pri, s.computerid FROM tgroup JOIN subgroups s USING(groupid);
	-- computer data to remove.
	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;
	-- computer data to add
	INSERT INTO subgroupwchildren (GroupID, ComputerID) SELECT v.pri, v.computerid FROM subgroupwchildren swc 
	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;
	-- remove temp table 
	DROP TEMPORARY TABLE tcomputer;
	-- temp table for contact data 
	CREATE TEMPORARY TABLE tcontact (KEY (`pri`,`contactId`))
	SELECT DISTINCT tgroup.pri, s.contactid FROM tgroup JOIN subgroupscontacts s USING(groupid);
	-- contacts data to remove
	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;
	-- contacts data to add
	INSERT INTO subgroupwchildrencontacts (GroupId, ContactId) SELECT v.pri, v.contactid FROM subgroupwchildrencontacts swcc 
	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;
	-- remove temp table 
	DROP TEMPORARY TABLE tcontact;
	-- temp table for network device data
	CREATE TEMPORARY TABLE tnetwork  (KEY (`pri`,`deviceId`))
	SELECT DISTINCT tgroup.pri, s.deviceid FROM tgroup JOIN subgroupsnetworkdevices s USING(groupid);	
	-- network device data to remove
	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;
	-- network device data to add
	INSERT INTO subgroupwchildrennetworkdevices (GroupId, DeviceId) SELECT v.pri, v.deviceid FROM subgroupwchildrennetworkdevices swcnd 
	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;
	-- remove temp table 
	DROP TEMPORARY TABLE tnetwork;
	-- remove initial temp table 
	DROP TEMPORARY TABLE tgroup;
END

sp_DoSubGroupChildren

Parameters

Definition