Life is Feudal Official Forum

[Eslake]

Since brewing is added, and mead is in, but they didn't turn on the hives...

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;

END WHILE;
-- 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;
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

[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.

[Eslake]

I forgot to explain how it works.

Once you run all 3 scripts, your server will have working hives (old and new type)

To get honey, just place the hive and a container on the same location.

That is, the same initial square you drop them into, you can adjust them so they don't sit on top of one another with the sliders on the drop- tool, and they will still be connected.

After that, every # of minutes (you set in script # 3) the hive will create honey into that container.


As with all Creation/Add scripts, the honey won't show up until the container is Moved or the server reboots.

[HolyCrusader]

I forgot to explain how it works.

Once you run all 3 scripts, your server will have working hives (old and new type)

To get honey, just place the hive and a container on the same location.

That is, the same initial square you drop them into, you can adjust them so they don't sit on top of one another with the sliders on the drop- tool, and they will still be connected.

After that, every # of minutes (you set in script # 3) the hive will create honey into that container.


As with all Creation/Add scripts, the honey won't show up until the container is Moved or the server reboots.

Nice post and work & thank you for sharing!

[Eslake]

Nice post and work & thank you for sharing!

I should have posted it weeks ago - I guess it was useful for 1 day. P

[HolyCrusader]

Nice post and work & thank you for sharing!

I should have posted it weeks ago - I guess it was useful for 1 day. P

Its the act of sharing that counts! You're a good man