This is the script we've been running for a couple of months now. (modified for the new hives)
USAGE
Run the script to create the event
Run the second script to add the item insertion procedure
Run the third script to start the event timer
BACK UP the database before making any changes, specially before running scripts that modify data.
CREATE PROCEDURE `e_hive_production`()
BEGIN
DECLARE Continuation, prevHive, hiveDurability, contRoot INT;
SET prevHive=0;
SELECT COUNT(*) INTO Continuation
FROM unmovable_objects hives
JOIN movable_objects conts
ON hives.GeoDataID=conts.GeoDataID
WHERE hives.ObjectTypeID=1121 AND (conts.ObjectTypeID=101 OR conts.ObjectTypeID=103 OR conts.ObjectTypeID=104 OR conts.ObjectTypeID=106 OR conts.ObjectTypeID=167 OR conts.ObjectTypeID=168 OR conts.ObjectTypeID=169);
/*
Despite the length, this select is actually fairly simple.
It just JOINs unmovable and movable objects to find all instances where a hive and
a container occupy the same GeoData location.
*/
WHILE Continuation>0 DO
- -- Repeating the whole process until there are no more hive+container pairs found
SELECT hives.`ID`, hives.`Durability`, conts.RootContainerID INTO prevHive, hiveDurability, contRoot
FROM unmovable_objects hives
JOIN movable_objects conts
ON hives.GeoDataID=conts.GeoDataID
WHERE hives.ObjectTypeID=1121 AND (conts.ObjectTypeID=101 OR conts.ObjectTypeID=103 OR conts.ObjectTypeID=104 OR conts.ObjectTypeID=106 OR conts.ObjectTypeID=167 OR conts.ObjectTypeID=168 OR conts.ObjectTypeID=169) AND hives.`ID`>prevHive ORDER BY hives.`ID` LIMIT 1;
/* To adjust how difficult it is to get high quality honey, change 190 in the line of
script below to another value. Higher for Lower quality, Lower for Higher quality.
At 190 it requires a 19,000 or higher(max is 20,000) durability hive to get 100q honey.
Setting it over 200 makes 100q impossible.
*/
SET hiveDurability=FLOOR(LEAST(hiveDurability/190, 100));
CALL proc_insertStacks(contRoot, 376, 1, hiveDurability);
SELECT COUNT(*) INTO Continuation FROM unmovable_objects hives
JOIN movable_objects conts
ON hives.GeoDataID=conts.GeoDataID
WHERE hives.ObjectTypeID=1121 AND (conts.ObjectTypeID=101 OR conts.ObjectTypeID=103 OR conts.ObjectTypeID=104 OR conts.ObjectTypeID=106 OR conts.ObjectTypeID=167 OR conts.ObjectTypeID=168 OR conts.ObjectTypeID=169) AND hives.`ID`>prevHive;
END WHILE;
/*
If your server has been wiped/started Since the horses patch, you can remove the following section
It is here to provide Legacy support for the older, small, movable hives.
*/
-- Legacy Hive Support Begins
SELECT COUNT(*) INTO Continuation FROM movable_objects hives
JOIN movable_objects conts
ON hives.GeoDataID=conts.GeoDataID
WHERE hives.ObjectTypeID=122 AND (conts.ObjectTypeID=101 OR conts.ObjectTypeID=103 OR conts.ObjectTypeID=104 OR conts.ObjectTypeID=106 OR conts.ObjectTypeID=167 OR conts.ObjectTypeID=168 OR conts.ObjectTypeID=169);
WHILE Continuation>0 DO
- SELECT hives.`ID`, hives.`Durability`, conts.RootContainerID INTO prevHive, hiveDurability, contRoot
FROM movable_objects hives
JOIN movable_objects conts
ON hives.GeoDataID=conts.GeoDataID
WHERE hives.ObjectTypeID=122 AND (conts.ObjectTypeID=101 OR conts.ObjectTypeID=103 OR conts.ObjectTypeID=104 OR conts.ObjectTypeID=106 OR conts.ObjectTypeID=167 OR conts.ObjectTypeID=168 OR conts.ObjectTypeID=169) AND hives.`ID`>prevHive ORDER BY hives.`ID` LIMIT 1;
SET hiveDurability=FLOOR(LEAST(hiveDurability/190, 100));
CALL proc_insertStacks(contRoot, 376, 1, hiveDurability);
SELECT COUNT(*) INTO Continuation
FROM movable_objects hives
JOIN movable_objects conts
ON hives.GeoDataID=conts.GeoDataID
WHERE hives.ObjectTypeID=122 AND (conts.ObjectTypeID=101 OR conts.ObjectTypeID=103 OR conts.ObjectTypeID=104 OR conts.ObjectTypeID=106 OR conts.ObjectTypeID=167 OR conts.ObjectTypeID=168 OR conts.ObjectTypeID=169) AND hives.`ID`>prevHive;
-- Legacy Hive Support ends
END
[Cut and Paste version]
- Code: Select all
CREATE PROCEDURE `e_hive_production`()
BEGIN
DECLARE Continuation, prevHive, hiveDurability, contRoot INT;
SET prevHive=0;
SELECT COUNT(*) INTO Continuation
FROM unmovable_objects hives
JOIN movable_objects conts
ON hives.GeoDataID=conts.GeoDataID
WHERE hives.ObjectTypeID=1121 AND (conts.ObjectTypeID=101 OR conts.ObjectTypeID=103 OR conts.ObjectTypeID=104 OR conts.ObjectTypeID=106 OR conts.ObjectTypeID=167 OR conts.ObjectTypeID=168 OR conts.ObjectTypeID=169);
/*
Despite the length, this select is actually fairly simple.
It just JOINs unmovable and movable objects to find all instances where a hive and
a container occupy the same GeoData location.
*/
WHILE Continuation>0 DO
-- Repeating the whole process until there are no more hive+container pairs found
SELECT hives.`ID`, hives.`Durability`, conts.RootContainerID INTO prevHive, hiveDurability, contRoot
FROM unmovable_objects hives
JOIN movable_objects conts
ON hives.GeoDataID=conts.GeoDataID
WHERE hives.ObjectTypeID=1121 AND (conts.ObjectTypeID=101 OR conts.ObjectTypeID=103 OR conts.ObjectTypeID=104 OR conts.ObjectTypeID=106 OR conts.ObjectTypeID=167 OR conts.ObjectTypeID=168 OR conts.ObjectTypeID=169) AND hives.`ID`>prevHive ORDER BY hives.`ID` LIMIT 1;
/* To adjust how difficult it is to get high quality honey, change 190 in the line of
script below to another value. Higher for Lower quality, Lower for Higher quality.
At 190 it requires a 19,000 or higher(max is 20,000) durability hive to get 100q honey.
Setting it over 200 makes 100q impossible.
*/
SET hiveDurability=FLOOR(LEAST(hiveDurability/190, 100));
CALL proc_insertStacks(contRoot, 376, 1, hiveDurability);
SELECT COUNT(*) INTO Continuation FROM unmovable_objects hives
JOIN movable_objects conts
ON hives.GeoDataID=conts.GeoDataID
WHERE hives.ObjectTypeID=1121 AND (conts.ObjectTypeID=101 OR conts.ObjectTypeID=103 OR conts.ObjectTypeID=104 OR conts.ObjectTypeID=106 OR conts.ObjectTypeID=167 OR conts.ObjectTypeID=168 OR conts.ObjectTypeID=169) AND hives.`ID`>prevHive;
END WHILE;
/*
If your server has been wiped/started Since the horses patch, you can remove the following section
It is here to provide Legacy support for the older, small, movable hives.
*/
-- Legacy Hive Support Begins
SELECT COUNT(*) INTO Continuation FROM movable_objects hives
JOIN movable_objects conts
ON hives.GeoDataID=conts.GeoDataID
WHERE hives.ObjectTypeID=122 AND (conts.ObjectTypeID=101 OR conts.ObjectTypeID=103 OR conts.ObjectTypeID=104 OR conts.ObjectTypeID=106 OR conts.ObjectTypeID=167 OR conts.ObjectTypeID=168 OR conts.ObjectTypeID=169);
WHILE Continuation>0 DO
SELECT hives.`ID`, hives.`Durability`, conts.RootContainerID INTO prevHive, hiveDurability, contRoot
FROM movable_objects hives
JOIN movable_objects conts
ON hives.GeoDataID=conts.GeoDataID
WHERE hives.ObjectTypeID=122 AND (conts.ObjectTypeID=101 OR conts.ObjectTypeID=103 OR conts.ObjectTypeID=104 OR conts.ObjectTypeID=106 OR conts.ObjectTypeID=167 OR conts.ObjectTypeID=168 OR conts.ObjectTypeID=169) AND hives.`ID`>prevHive ORDER BY hives.`ID` LIMIT 1;
SET hiveDurability=FLOOR(LEAST(hiveDurability/190, 100));
CALL proc_insertStacks(contRoot, 376, 1, hiveDurability);
SELECT COUNT(*) INTO Continuation
FROM movable_objects hives
JOIN movable_objects conts
ON hives.GeoDataID=conts.GeoDataID
WHERE hives.ObjectTypeID=122 AND (conts.ObjectTypeID=101 OR conts.ObjectTypeID=103 OR conts.ObjectTypeID=104 OR conts.ObjectTypeID=106 OR conts.ObjectTypeID=167 OR conts.ObjectTypeID=168 OR conts.ObjectTypeID=169) AND hives.`ID`>prevHive;
END WHILE;
-- Legacy Hive Support ends
END
Second Script
Procedure to add stackable items to a container.
CREATE PROCEDURE `proc_insertStacks`(IN `contID` INT UNSIGNED, IN `insertType` INT UNSIGNED, IN `insertQnty` INT UNSIGNED, IN `insertQlty` INT UNSIGNED)
/*
CALL with (contID, insertType, insertQnty, insertQlty)
----------
contID is the RootContainerID, not the `ID` from movable_objects
insertType is the objects_types ID for the item(s) being added
insertQnty is the quantity to add
insertQlty is what quality to add
*/
BEGIN
DECLARE usingStack, stackHas, stackLimit, itemWeight, toAdd INT;
SELECT MaxStackSize, UnitWeight INTO stackLimit, itemWeight FROM objects_types WHERE ID=insertType;
SET stackLimit=FLOOR(LEAST(stackLimit, 2500000/itemWeight));
-- stackLimit is the lesser of the MaxStackSize for the time type, or a total weight of 250
WHILE insertQnty > 0 DO
- -- repeat adding until the Quantity to be added is reached
SET usingStack=0;
SELECT `ID`, `Quantity` INTO usingStack, stackHas FROM `items`
WHERE `ContainerID`=contID AND `Quality`=insertQlty AND `Quantity`<stackLimit ORDER BY `Quantity` LIMIT 1;
-- Find the smallest existing stack of this type of items in this quality in the container if there is such
IF usingStack>0 THEN
- SET toAdd =LEAST(insertQnty, (stackLimit-stackHas));
UPDATE `items` SET Quantity=(Quantity+toAdd) WHERE `ID`=usingStack;
SET insertQnty=insertQnty-toAdd;
- -- If no stacks of the correct type and size are here to add to, add new stacks
SET toAdd=LEAST(insertQnty, stackLimit);
INSERT INTO `items` (`ContainerID`, `ObjectTypeID`, `Quality`, `Quantity`) VALUES
(contID, insertType, insertQlty, toAdd);
SET insertQnty=insertQnty-toAdd;
END
[Cut and Paste version]
- Code: Select all
CREATE PROCEDURE `proc_insertStacks`(IN `contID` INT UNSIGNED, IN `insertType` INT UNSIGNED, IN `insertQnty` INT UNSIGNED, IN `insertQlty` INT UNSIGNED)
/*
CALL with (contID, insertType, insertQnty, insertQlty)
contID is the RootContainerID, not the `ID` from movable_objects
insertType is the objects_types ID for the item(s) being added
insertQnty is the quantity to add
insertQlty is what quality to add
*/
BEGIN
DECLARE usingStack, stackHas, stackLimit, itemWeight, toAdd INT;
SELECT MaxStackSize, UnitWeight INTO stackLimit, itemWeight FROM objects_types WHERE ID=insertType;
SET stackLimit=FLOOR(LEAST(stackLimit, 2500000/itemWeight));
-- stackLimit is the lesser of the MaxStackSize for the time type, or a total weight of 250
WHILE insertQnty > 0 DO
-- repeat adding until the Quantity to be added is reached
SET usingStack=0;
SELECT `ID`, `Quantity` INTO usingStack, stackHas FROM `items`
WHERE `ContainerID`=contID AND `Quality`=insertQlty AND `Quantity`<stackLimit ORDER BY `Quantity` LIMIT 1;
-- Find the smallest existing stack of this type of items in this quality in the container if there is such
IF usingStack>0 THEN
SET toAdd =LEAST(insertQnty, (stackLimit-stackHas));
UPDATE `items` SET Quantity=(Quantity+toAdd) WHERE `ID`=usingStack;
SET insertQnty=insertQnty-toAdd;
ELSE
-- If no stacks of the correct type and size are here to add to, add new stacks
SET toAdd=LEAST(insertQnty, stackLimit);
INSERT INTO `items` (`ContainerID`, `ObjectTypeID`, `Quality`, `Quantity`) VALUES
(contID, insertType, insertQlty, toAdd);
SET insertQnty=insertQnty-toAdd;
END IF;
END WHILE;
END
Third Script
Setting the Scheduler
CREATE EVENT `schedule_hives`
ON SCHEDULE EVERY 45 MINUTE
DO
CALL e_hive_production();
Obviously you can change the 45 minute schedule for how often hives produce.
But remember this script is operating all of the time. Setting it to 10 minutes means someone can log off today, come back tomorrow, and find 144 honey for each hive waiting.
P