Life is Feudal Official Forum

[Chessmaster42]

Ironically my old trigger for undo'ing the tunnel is now broken. However I have a working procedure for removing tunnels. It's not 100% where I want it but it does work. Downside is that a server restart is required but upside is that it doesn't delete any data

Code: Select all

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_deleteTunnel`(IN `geoDataID` INT, IN `altitude` INT)
   LANGUAGE SQL
   NOT DETERMINISTIC
   MODIFIES SQL DATA
   SQL SECURITY DEFINER
   COMMENT 'Experimental procedure to undo tunnels in terrain'
BEGIN
   IF true THEN
      UPDATE geo_patch gp SET gp.IsServerOnly = 1 WHERE gp.GeoDataID = geoDataID AND gp.IsServerOnly = 0;
      
      SET @terID = 0;
      SET @newAltitude = 0;
      SELECT gp.TerID, gp.Altitude FROM geo_patch gp WHERE gp.GeoDataID = geoDataID AND ChangeIndex = 1 GROUP BY Altitude LIMIT 1 INTO @terID, @newAltitude;
      
      IF altitude > 0 THEN
         SET @newAltitude = altitude;
      END IF;
      
      SET @result = f_addGeoPatch(1, 0, @terID, 1, geoDataID, @newAltitude, 3, 128, 60000, 42);
   END IF;
END

Once I get the new trigger working that calls this procedure I'll post it as well. Let me know how this proc works for you guys.

[Chessmaster42]

Well, it took less time to fix up than I thought. Here is the working trigger that will run the delete tunnel SQL proc:

Code: Select all

CREATE DEFINER=`root`@`localhost` TRIGGER `t_adminUndoTunnel` AFTER UPDATE ON `items` FOR EACH ROW BEGIN
   IF NEW.ObjectTypeID = 1062 THEN
      SET @ownerID = (SELECT OwnerID FROM movable_objects mo JOIN containers con ON con.ID = mo.RootContainerID WHERE con.ID = NEW.ContainerID);
      
      SET @location = (SELECT GeoID FROM `character` WHERE ID = @ownerID);
      SET @altitude = (SELECT GeoAlt FROM `character` WHERE ID = @ownerID);
      SET @tunnelCount = (SELECT COUNT(*) FROM geo_patch gp WHERE gp.GeoDataID = @location AND gp.Substance = 0 AND gp.LevelFlags & 1 = 0);
      IF @tunnelCount > 0 THEN
         CALL p_deleteTunnel(@location, 0);
      END IF;
   END IF;
END

All you have to do in order for this to work is to drop an item of ID 1062 (God's Favor) into a new dropped items bag while standing on the tunnel tile that you wish removed. On the next server restart the tunnel entrance will then be gone and you will be back to normal terrain

[Adamld85]

I'm unable to run these queries because apparently there are SQL syntax errors on lines:

9 - the 0; is highlighted as an error.

15 - the IF statement is underlined.

17, 20, 21 - END statement is underlined.

[Chessmaster42]

I'm unable to run these queries because apparently there are SQL syntax errors on lines:

9 - the 0; is highlighted as an error.

15 - the IF statement is underlined.

17, 20, 21 - END statement is underlined.

Could you be more specific about which lines and which errors? None of what you've given can I match up to the SQL code. Everything works on my end so something must have gotten lost in translation somewhere. Which tool are you using to run/install the trigger and procedure?

EDIT - What tool are you using to work with the SQL? I use HeidiSQL as it came with MariaDB but any MySQL 5.5 compatible connector should work. This might be because of the tool you're using.

[Adamld85]

I'm unable to run these queries because apparently there are SQL syntax errors on lines:

9 - the 0; is highlighted as an error.

15 - the IF statement is underlined.

17, 20, 21 - END statement is underlined.

Could you be more specific about which lines and which errors? None of what you've given can I match up to the SQL code. Everything works on my end so something must have gotten lost in translation somewhere. Which tool are you using to run/install the trigger and procedure?

EDIT - What tool are you using to work with the SQL? I use HeidiSQL as it came with MariaDB but any MySQL 5.5 compatible connector should work. This might be because of the tool you're using.

They are syntax errors, here is a sceenshot of the query window in MYSQL workbench 6.5:



Is this just meant to be run in a normal SQL query window because it seems to have an issue with the END / IF statements.

phpmyadmin also had a problem with the syntax. I am running MySQL 5.6.21.

Are you just running this in a query window in Heidi or are you doing something different?

[Tinman]

I added the code just fine with HeidiSQL but the trigger doesn't seem to be working. However, the currency is removed from the bag. Odd. I'll keep playing. Thanks for getting us this far.

[Chessmaster42]

You can't just run it in a query window because of the line delimiters. However if you want to add it using the GUI just create a new procedure and trigger and copy over the body of each and set the name, definer, etc. accordingly until the contents of the 'CREATE code' tab matches what I posted. Alternatively you can put it into a SQL text file and import it into the DB.

I'll see if I can come up with a version of the proc and trigger that can be loaded just via a query window.

[Adamld85]

You can't just run it in a query window because of the line delimiters. However if you want to add it using the GUI just create a new procedure and trigger and copy over the body of each and set the name, definer, etc. accordingly until the contents of the 'CREATE code' tab matches what I posted. Alternatively you can put it into a SQL text file and import it into the DB.

I'll see if I can come up with a version of the proc and trigger that can be loaded just via a query window.

So I can just save it as a .SQL file and load it that way?

[Chessmaster42]

So I can just save it as a .SQL file and load it that way?

Yeah, should be able to. Alternatively you can try putting it into patch.sql and restarting the server. That should load it for you as well. If not then just create new procs/triggers in the GUI and copy/paste over the body and set the right name, definer, etc.

EDIT: Another thing to note is that once the Gods Favor item is dropped where you wish to remove the tunnel all terraforming will cease to work on the server until you restart and the client+server terrain data is sync'd back up again with the SQL data.

[Frydon]

Screen shots for the local server log for when you finish completing a tunnel if this helps anyone track the save state.

http://imgur.com/M4TsaZS,7WG4SVg

Looking into how to change that tunnel with Chessmaster's Function. When I find out how to open this up in Heidi. I work with MSSQL not MySQL so its a bit of a curve finding how to host the Lif DB.

I figure I'll put the top 10 rows into a table then do a sample query on it with the logic he built so it can display the function as his code creates it to how what it does.

[Chessmaster42]

I managed to decode the data for GeoDataID:

GeoDataID = X + Y * 512 + T * 512 * 512

X is your east-west position within the terrain block. Y is your north-south position within the terrain block. And T is the terrain block ID. So a position of say 64x 135y in terrain block 443 is a GeoDataID of 116198976.

Some SQL to show an example of how to decode the values:

Code: Select all

SELECT gp.*, ((gp.GeoDataID & 0xFFFC0000) >> 18) AS TerrainID, ((gp.GeoDataID & 0x000001FF) >> 0) AS TerrainX, ((gp.GeoDataID & 0x0003FE00) >> 9) AS TerrainY
FROM geo_patch gp LIMIT 1;

Hope this helps you guys out as well

[Maegfaer]

Thanks for sharing the solution to that puzzle Chestmaster, good to know!

[Vamyan]

There's already a function in the database to encode/decode those...

f_toGeoID

Code: Select all

BEGIN
   return ((terID << 18) | (y << 9) | x);
END

and
f_fromGeoID

Code: Select all

BEGIN
   select
      (geoID >> 18) as terID,
      (geoID & ((1 << 9) - 1)) as `x`,
      ((geoID >> 9) & ((1 << (9)) - 1)) as `y`;
END

The code from those is pretty straightforward, as long as you're familiar with bit shifting

[Kento472]

When i use HeidiSQL gui to enter chessmasters code everything seems to get entered fine, but when i log into the game and stand in the tile of the tunnel, drop the 1062 item and wait about 30 seconds, log out, restart the server and log back in, nothing was changed. one time we did successfully get the process to remove the item from the bag and leave an empty bag.... but tunnel was still there.

To note i am running the server on a windows 2012 r2 server and using the mariadb that the steam DL instructions suggest.

Any ideas, or possibly like a step by step how to perform suggested actions in game and on server? ( with times? like wait 3 minutes here blah blah)

Thanks in advance.