Life is Feudal Official Forum

[Eslake]

This script is a little larger.
It serves two functions.
#1 - to reduce the objects_patch table significantly.
#2 - to recover worlds that have become broken by bad versioning within objects_patch.
--the world will load, but a section or the entire thing will load with the default map, no trees, and no objects -- although you still bump into those trees and objects.

An example of the reduction --
Objects_Patch before, total = 13,698 lines.
Objects_Patch after, total = 1,051 lines.

The first is a large image of the first 200 lines of a typical objects_patch - taken from an actual server backup.
The second is the same objects_patch after this procedure is called.
The entire useful data set from the first image fits into fewer than 20 lines in the reduced version.

Spoiler

Spoiler

USAGE
Run the script to create the procedure
proc_Reduce_objects_patch

Then, during maintenance, and AFTER BACKUP,
CALL proc_Reduce_objects_patch;

You can use it every maintenance, or just when the table starts to get cumbersome.

BACK UP the database before making any changes, specially before running scripts that modify data.
DO NOT call this procedure while the server is active. It is a maintenance script.


CREATE PROCEDURE `proc_Reduce_objects_patch`()
BEGIN
DECLARE goObjectID, goObjectTypeID, goTurnAngle, goIsComplete, goGeoDataID, goTerId, goSlope, goX, goY, goZ, goAltitude, PreviousID, Continuation INT;
-- go-Variables are those being translated from movable_ or unmovable_objects to objects_patch
DECLARE TerVers442, TerVers443, TerVers444, TerVers445, TerVers446, TerVers447, TerVers448, TerVers449, TerVers450, ThisVersion INT;
SET TerVers442=1, TerVers443=1, TerVers444=1, TerVers445=1, TerVers446=1, TerVers447=1, TerVers448=1, TerVers449=1, TerVers450=1;
-- TerVers variables store the current Version for each TerID to update the terrain_blocks table when the procedure is ending.

SET FOREIGN_KEY_CHECKS = 0;
SET PreviousID=0;
-- Begin as if the last item moved was ID=0 since the server always begins with 1 or higher
TRUNCATE TABLE `objects_patch`;
SELECT COUNT(*) INTO Continuation FROM unmovable_objects WHERE ID>PreviousID;
WHILE Continuation > 0 DO

SELECT `ID`, `ObjectTypeID`, `TurnAngle`, `IsComplete`, `GeoDataId`, `Slope` INTO goObjectID, goObjectTypeID, goTurnAngle, goIsComplete, goGeoDataID, goSlope FROM unmovable_objects WHERE ID>PreviousID ORDER BY `ID` LIMIT 1;
SET goTerID=(goGeoDataID >> 18); -- the TerID is the GeoDataID shifted right 18 bits
-- A set of quick IFs to increment the current TerID's Version and use that for the INSERT
IF goTerID=442 THEN SET TerVers442=TerVers442+1;SET ThisVersion=TerVers442;END IF;
IF goTerID=443 THEN SET TerVers443=TerVers443+1;SET ThisVersion=TerVers443;END IF;
IF goTerID=444 THEN SET TerVers444=TerVers444+1;SET ThisVersion=TerVers444;END IF;
IF goTerID=445 THEN SET TerVers445=TerVers445+1;SET ThisVersion=TerVers445;END IF;
IF goTerID=446 THEN SET TerVers446=TerVers446+1;SET ThisVersion=TerVers446;END IF;
IF goTerID=447 THEN SET TerVers447=TerVers447+1;SET ThisVersion=TerVers447;END IF;
IF goTerID=448 THEN SET TerVers448=TerVers448+1;SET ThisVersion=TerVers448;END IF;
IF goTerID=449 THEN SET TerVers449=TerVers449+1;SET ThisVersion=TerVers449;END IF;
IF goTerID=450 THEN SET TerVers450=TerVers450+1;SET ThisVersion=TerVers450;END IF;
INSERT INTO objects_patch (`TerID`, `Version`, `Action`, `ObjectSuperType`, `ObjectID`, `GeoDataID`, `ObjectTypeID`, `TurnAngle`, `IsComplete`, `Slope`) VALUES
(goTerID, ThisVersion, 1, 2, goObjectID, goGeoDataID, goObjectTypeID, goTurnAngle, goIsComplete, goSlope);
SET PreviousID=goObjectID;

-- As long as there are any entries in unmovable_objects of a higher ID than we've copied, continue
SELECT COUNT(*) INTO Continuation FROM unmovable_objects WHERE ID>PreviousID;

END WHILE;

-- Repeat the entire process for movable_objects, using the more complete listing of placement data associated with movable objects
SET PreviousID=0;
SELECT COUNT(*) INTO Continuation FROM movable_objects WHERE ID>PreviousID;
WHILE Continuation > 0 DO

SELECT `ID`, `ObjectTypeID`, `OffsetX`, `OffsetY`, `OffsetZ`, `TurnAngle`, `GeoDataID`, `Altitude`, `IsComplete` INTO goObjectID, goObjectTypeID, goX, goY, goZ, goTurnAngle, goGeoDataID, goAltitude, goIsComplete FROM movable_objects WHERE ID>PreviousID ORDER BY ID LIMIT 1;
SET goTerID=(goGeoDataID >> 18);
IF goTerID=442 THEN SET TerVers442=TerVers442+1;SET ThisVersion=TerVers442;END IF;
IF goTerID=443 THEN SET TerVers443=TerVers443+1;SET ThisVersion=TerVers443;END IF;
IF goTerID=444 THEN SET TerVers444=TerVers444+1;SET ThisVersion=TerVers444;END IF;
IF goTerID=445 THEN SET TerVers445=TerVers445+1;SET ThisVersion=TerVers445;END IF;
IF goTerID=446 THEN SET TerVers446=TerVers446+1;SET ThisVersion=TerVers446;END IF;
IF goTerID=447 THEN SET TerVers447=TerVers447+1;SET ThisVersion=TerVers447;END IF;
IF goTerID=448 THEN SET TerVers448=TerVers448+1;SET ThisVersion=TerVers448;END IF;
IF goTerID=449 THEN SET TerVers449=TerVers449+1;SET ThisVersion=TerVers449;END IF;
IF goTerID=450 THEN SET TerVers450=TerVers450+1;SET ThisVersion=TerVers450;END IF;
INSERT INTO `objects_patch` (`TerID`, `Version`, `Action`, `ObjectSuperType`, `ObjectID`, `GeoDataID`, `ObjectTypeID`, `TurnAngle`, `Altitude`, `OffsetX`, `OffsetY`, `OffsetZ`, `IsComplete`, `Slope`) VALUES
(goTerID, ThisVersion, 1, 1, goObjectID, goGeoDataID, goObjectTypeID, goTurnAngle, goAltitude, goX, goY, goZ, goIsComplete, goSlope);
SET PreviousID=goObjectID;
SELECT COUNT(*) INTO Continuation FROM movable_objects WHERE ID>PreviousID;

END WHILE;

/*

VITAL!

Update the terrain_blocks ObjectsVersion entries to match the new entries in objects_patch
Otherwise any segments that have changed will load as the default terrain, devoid of all objects and trees.

*/
UPDATE terrain_blocks SET ObjectsVersion=TerVers442 WHERE ID=442;
UPDATE terrain_blocks SET ObjectsVersion=TerVers443 WHERE ID=443;
UPDATE terrain_blocks SET ObjectsVersion=TerVers444 WHERE ID=444;
UPDATE terrain_blocks SET ObjectsVersion=TerVers445 WHERE ID=445;
UPDATE terrain_blocks SET ObjectsVersion=TerVers446 WHERE ID=446;
UPDATE terrain_blocks SET ObjectsVersion=TerVers447 WHERE ID=447;
UPDATE terrain_blocks SET ObjectsVersion=TerVers448 WHERE ID=448;
UPDATE terrain_blocks SET ObjectsVersion=TerVers449 WHERE ID=449;
UPDATE terrain_blocks SET ObjectsVersion=TerVers450 WHERE ID=450;
SET FOREIGN_KEY_CHECKS = 1;
END

-- As a copy/paste code snippet

Code: Select all

CREATE PROCEDURE `proc_Reduce_objects_patch`()
BEGIN
DECLARE goObjectID, goObjectTypeID, goTurnAngle, goIsComplete, goGeoDataID, goTerId, goSlope, goX, goY, goZ, goAltitude, PreviousID, Continuation INT;
-- go-Variables are those being translated from movable_ or unmovable_objects to objects_patch
DECLARE TerVers442, TerVers443, TerVers444, TerVers445, TerVers446, TerVers447, TerVers448, TerVers449, TerVers450, ThisVersion INT;
SET TerVers442=1, TerVers443=1, TerVers444=1, TerVers445=1, TerVers446=1, TerVers447=1, TerVers448=1, TerVers449=1, TerVers450=1;
-- TerVers variables store the current Version for each TerID to update the terrain_blocks table when the procedure is ending.

SET FOREIGN_KEY_CHECKS = 0;
SET PreviousID=0; -- Begin as if the last item moved was ID=0 since the server always begins with 1 or higher
TRUNCATE TABLE `objects_patch`;
SELECT COUNT(*) INTO Continuation FROM unmovable_objects WHERE ID>PreviousID;
WHILE Continuation > 0 DO
   SELECT `ID`, `ObjectTypeID`, `TurnAngle`, `IsComplete`, `GeoDataId`, `Slope` INTO goObjectID, goObjectTypeID, goTurnAngle, goIsComplete, goGeoDataID, goSlope FROM unmovable_objects WHERE ID>PreviousID ORDER BY `ID` LIMIT 1;
   SET goTerID=(goGeoDataID >> 18); -- the TerID is the GeoDataID shifted right 18 bits
   -- A set of quick IFs to increment the current TerID's Version and use that for the INSERT
   IF goTerID=442 THEN SET TerVers442=TerVers442+1;SET ThisVersion=TerVers442;END IF;
   IF goTerID=443 THEN SET TerVers443=TerVers443+1;SET ThisVersion=TerVers443;END IF;
   IF goTerID=444 THEN SET TerVers444=TerVers444+1;SET ThisVersion=TerVers444;END IF;
   IF goTerID=445 THEN SET TerVers445=TerVers445+1;SET ThisVersion=TerVers445;END IF;
   IF goTerID=446 THEN SET TerVers446=TerVers446+1;SET ThisVersion=TerVers446;END IF;
   IF goTerID=447 THEN SET TerVers447=TerVers447+1;SET ThisVersion=TerVers447;END IF;
   IF goTerID=448 THEN SET TerVers448=TerVers448+1;SET ThisVersion=TerVers448;END IF;
   IF goTerID=449 THEN SET TerVers449=TerVers449+1;SET ThisVersion=TerVers449;END IF;
   IF goTerID=450 THEN SET TerVers450=TerVers450+1;SET ThisVersion=TerVers450;END IF;
   INSERT INTO objects_patch (`TerID`, `Version`, `Action`, `ObjectSuperType`, `ObjectID`, `GeoDataID`, `ObjectTypeID`, `TurnAngle`, `IsComplete`, `Slope`) VALUES
    (goTerID, ThisVersion, 1, 2, goObjectID, goGeoDataID, goObjectTypeID, goTurnAngle, goIsComplete, goSlope);
   SET PreviousID=goObjectID;

   -- As long as there are any entries in unmovable_objects of a higher ID than we've copied, continue
   SELECT COUNT(*) INTO Continuation FROM unmovable_objects WHERE ID>PreviousID;
END WHILE;

-- Repeat the entire process for movable_objects, using the more complete listing of placement data associated with movable objects
SET PreviousID=0;
SELECT COUNT(*) INTO Continuation FROM movable_objects WHERE ID>PreviousID;
WHILE Continuation > 0 DO
   SELECT `ID`, `ObjectTypeID`, `OffsetX`, `OffsetY`, `OffsetZ`, `TurnAngle`, `GeoDataID`, `Altitude`, `IsComplete` INTO goObjectID, goObjectTypeID, goX, goY, goZ, goTurnAngle, goGeoDataID, goAltitude, goIsComplete FROM movable_objects WHERE ID>PreviousID ORDER BY ID LIMIT 1;
   SET goTerID=(goGeoDataID >> 18);
   IF goTerID=442 THEN SET TerVers442=TerVers442+1;SET ThisVersion=TerVers442;END IF;
   IF goTerID=443 THEN SET TerVers443=TerVers443+1;SET ThisVersion=TerVers443;END IF;
   IF goTerID=444 THEN SET TerVers444=TerVers444+1;SET ThisVersion=TerVers444;END IF;
   IF goTerID=445 THEN SET TerVers445=TerVers445+1;SET ThisVersion=TerVers445;END IF;
   IF goTerID=446 THEN SET TerVers446=TerVers446+1;SET ThisVersion=TerVers446;END IF;
   IF goTerID=447 THEN SET TerVers447=TerVers447+1;SET ThisVersion=TerVers447;END IF;
   IF goTerID=448 THEN SET TerVers448=TerVers448+1;SET ThisVersion=TerVers448;END IF;
   IF goTerID=449 THEN SET TerVers449=TerVers449+1;SET ThisVersion=TerVers449;END IF;
   IF goTerID=450 THEN SET TerVers450=TerVers450+1;SET ThisVersion=TerVers450;END IF;
   INSERT INTO `objects_patch` (`TerID`, `Version`, `Action`, `ObjectSuperType`, `ObjectID`, `GeoDataID`, `ObjectTypeID`, `TurnAngle`, `Altitude`, `OffsetX`, `OffsetY`, `OffsetZ`, `IsComplete`, `Slope`) VALUES
    (goTerID, ThisVersion, 1, 1, goObjectID, goGeoDataID, goObjectTypeID, goTurnAngle, goAltitude, goX, goY, goZ, goIsComplete, goSlope);
   SET PreviousID=goObjectID;
   SELECT COUNT(*) INTO Continuation FROM movable_objects WHERE ID>PreviousID;
END WHILE;

/*    VITAL!
   Update the terrain_blocks ObjectsVersion entries to match the new entries in objects_patch
   Otherwise any segments that have changed will load as the default terrain, devoid of all objects and trees.
*/
UPDATE terrain_blocks SET ObjectsVersion=TerVers442 WHERE ID=442;
UPDATE terrain_blocks SET ObjectsVersion=TerVers443 WHERE ID=443;
UPDATE terrain_blocks SET ObjectsVersion=TerVers444 WHERE ID=444;
UPDATE terrain_blocks SET ObjectsVersion=TerVers445 WHERE ID=445;
UPDATE terrain_blocks SET ObjectsVersion=TerVers446 WHERE ID=446;
UPDATE terrain_blocks SET ObjectsVersion=TerVers447 WHERE ID=447;
UPDATE terrain_blocks SET ObjectsVersion=TerVers448 WHERE ID=448;
UPDATE terrain_blocks SET ObjectsVersion=TerVers449 WHERE ID=449;
UPDATE terrain_blocks SET ObjectsVersion=TerVers450 WHERE ID=450;
SET FOREIGN_KEY_CHECKS = 1;
END

[Gms0012]

great thing !!!!

thanks a lot

dropped 80000 lines in the database

[Eslake]

great thing !!!!

thanks a lot

dropped 80000 lines in the database

Glad you like it
80k lines.. should speed their load times and cut the lag a tiny little bit.


Now if I can just merge these monstrous scripts into a single, coherent, functional Reduce_geo_patch script.. you'll see some real impact.

[Karashishi]

You're awesome sharing your script fu Eslake, Thanks!

[Gms0012]

do you really plan to improve the GEO PATCH ?

[HolyCrusader]

Thank you Eslake

[Razoreqx]

Nice work!

[Eslake]

do you really plan to improve the GEO PATCH ?

Yes.
It may be a few days though.

Right now it is in several different parts.
One to remove lines of ServerOnly that indicate someone has been harvesting mushrooms or plants.
One to remove the numerous hits on a block before it breaks during tunneling.
etc.
And of course one to take what remains and re-sequence it into a valid set of entries/versions.

Merging them all into a single usable procedure is going to be quite a challenge.
(mostly because it takes so long for a server run locally to boot up to the point of logging in and testing the outcome)

I -Could- make a simpler one, that makes a sort of 'virtual' version of the soils on the given plot, and walks itself through every change since the start to find out what is there now -- then removes them all, digs down to the lowest point, and fills it with only the Current materials.

That would be the least error-prone, but it would also be slow.. to the point that it could take literal hours to process the entire map on an aged server.

But then I could limit it to processing a single TerID segment per call.

I'll have some time this evening to think it over and see if there is a way to make it workable.

[Gms0012]

if you want to test it with a world with a lot of buildings , tunnels and terraformed places... just contact me

i could give you access to our testserver (there is a backup of our old world , which we wiped yesterday)

we have 1558093 rows in the geopoatch...

if u are interested, just contact me here or via steam (gms0012)

[MasterChief]

any news here about geo-patch ?