Need SQL help & Epoch help (maintenance/setDamage)

mgm · June 20, 2014

Hey guys,

I would like to improve my current base maintenance set up. First, copied below is what I have at the moment.

My current setDamageOnAge.sql which is executed every 3 hours after server is automatically shutdown via script and just before it is automatically restarted.

-- EXECUTION FREQUENCY: 		To be executed before each server restart.
-- SUMMARY:				Sets minor damage to walls so the game can identify when a maintain option should be available. 
-- 					It it recommended to have this set to half of your CleanupPlacedAfterDays value. 
--					The default is 6 days so "INTERVAL 3 DAY" should be used as shown below.
UPDATE `object_data` 
		SET `Damage`=0.1 
	WHERE `ObjectUID` <> 0 
		AND `CharacterID` <> 0 
		AND `Datestamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY) 
		AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') )
		AND ( (`Classname` NOT LIKE '%Vault%') AND (`Classname` NOT LIKE '%Lockbox%') )

From what I understand the above code checks the Datestamp of any buildables and then sets Damage to "0.1" if it is older than 24 hours. (I added the last line it might be unnecessary - I'm not sure).

Goal:

The above being the current setup, what I would like to do next is, update my SQL query so that setDamage adds 10% every day, so that, if a user does not maintain for:

1 day, the damage will be 10%

2 days, the damage will be 20%

3 days, the damage will be 30%

...

9 days, the damage will be 90%

10 days, the damage will be 100% << from what I read in the forums here, then it will be automatically removed because the damage will be '1'.

(Furthermore, my "CleanupPlacedAfterDays" is set to 10 days so it will definitely go - exactly what I want).

Question #1: (SQL) So how can I accomplish this goal? Is there a simple SQL trigger such as "Damage = +0.1"?

Question #2: (Epoch) Once the SQL side is sorted, how can I setup an Epoch system to charge players skipping maintenance accordingly AND reward (not punish) diligent players paying daily?

Just to clarify with an example

( a ) Let's imagine a diligent player who logs in every day & maintains his base so he will be paying the maintenance fee for his items every day.In a 10 day period, he obviously will pay 10 times daily maintenance.

( b ) Let's also imagine a lazy-slash-busy player who (as you can guess) doesn't even log in to do any maintenance but logs in just before 10th day, so maintains every 9 days, thus pays 1 time daily maintenance every 10 days. This guy will be getting unfair discount which also means diligent dude will be getting punished for his diligence!

If anything, I would like to give the diligent players a discount (nothing much -business slow already!-, let's say 5% discount for first day maintenance, 4% for second day, 3% for third day, 2% for fourth day, and only 1% for fifth day)...

Let me add: I acknowledge the tiny (5% to 1%) discounts won't matter with gold bar payment system but I am still hoping we will reach our day, so it will make a difference when the day comes. Today, while we're at it I'd like to update maintenance related SQL queries in a way that the bonus code is active even though it won't matter for the next few week/months.

Thanks in advance for any input...

mgm · June 22, 2014

bump :(

Sven2157 · June 22, 2014

Answer #1

For the Damage you could try:

UPDATE `object_data` SET `Damage` = `Damage` * 1.1
WHERE `LastUpdated` >= now() - INTERVAL 1 DAY
AND `Classname` REGEXP 'wall|wood|door'

The difference here is the SET `Damage` is multiplied by 110%; to raise it by 10%. This will then add 10% more damage when the LastUpdate is more than 24 hours. You can add classname parts to the regular expression( regexp ); just separate them with the pipe '|' symbol.

Answer #2

You will need to script into the currency system or the maintenance system, then retrieve the LastUpdated information from the database, and then set the price for maintenance based on a simple math equation. Though I am not sure that you want to get that in depth.

Personally I would just average out the price and set it for 5 days of being gone.

Hope that helps! ;-)

=170= Sven2157

mgm · June 22, 2014

Answer #1

For the Damage you could try:
UPDATE `object_data` SET `Damage` = `Damage` * 1.1
WHERE `LastUpdated` >= now() - INTERVAL 1 DAY
AND `Classname` REGEXP 'wall|wood|door' 
The difference here is the SET `Damage` is multiplied by 110%; to raise it by 10%. This will then add 10% more damage when the LastUpdate is more than 24 hours. You can add classname parts to the regular expression( regexp ); just separate them with the pipe '|' symbol.

Thanks for responding. I can't see how would this work?

The day player builds the base (or the day he pays the maintenance fees) damage is set to zero, therefore the next day, if the above formula is used:

NewDamage = [previous day's damage value] * [1.1]

NewDamage = [0.0] * [1.1] = 0.0.

No? Every single day we will be multiplying it with zero and getting zero. Am I missing something here? :)

Answer #2

You will need to script into the currency system or the maintenance system, then retrieve the LastUpdated information from the database, and then set the price for maintenance based on a simple math equation. Though I am not sure that you want to get that in depth.

Personally I would just average out the price and set it for 5 days of being gone.

Okay if we can get step 1 sorted, I will start investigating how the maintenance bill is calculated maybe I can do something there...

Sven2157 · June 22, 2014

The buildings take damage over time, thus the need for maintenance, correct? So Damage of 0.75 in 24 hours will be 0.825 ( 0.75 * 1.1 = 0.825 ). Then the building will take more damage in a 24 period, and when your script runs again, it increases damage by 10%.

That is what you wanted right?

If you have decay turned off, then you would need something more like this:

UPDATE `object_data`
IF( `Damage` = 0.0, SET `Damage` = 0.1, SET `Damage` = `Damage` * 1.1 )
WHERE `LastUpdated` >= now() - INTERVAL 1 DAY
AND `Classname` REGEXP 'wall|wood|door'

MySQL IF statements are a bit different than other languages. They start with a condition, in this case whether damage is 0.0 or not, and that have two options: what to do if true and what to do if false. So in one line it is its own IF ... ELSE statement.

Hope that helps! ;-)

=170= Sven2157

mgm · June 22, 2014

The buildings take damage over time, thus the need for maintenance, correct? So Damage of 0.75 in 24 hours will be 0.825 ( 0.75 * 1.1 = 0.825 ). Then the building will take more damage in a 24 period, and when your script runs again, it increases damage by 10%.

That is what you wanted right?

If you have decay turned off, then you would need something more like this:
UPDATE `object_data`
IF( `Damage` = 0.0, SET `Damage` = 0.1, SET `Damage` = `Damage` * 1.1 )
WHERE `LastUpdated` >= now() - INTERVAL 1 DAY
AND `Classname` REGEXP 'wall|wood|door' 
MySQL IF statements are a bit different than other languages. They start with a condition, in this case whether damage is 0.0 or not, and that have two options: what to do if true and what to do if false. So in one line it is its own IF ... ELSE statement.

Hope that helps! ;-)

=170= Sven2157

Yes buildings need maintenance because they do take damage over time but from what I understand the "buildings take damage over time" bit is actually happening in MySQL using SQL queries.

If the query is only a "multiplication based" query, no building will ever take damage because the initial state of every building is 0.0 damage thus damage field cannot be increased by multiplication... << maybe I'm wrong but that's what I believe at the moment..

Your updated query, (if it is zero, then set it to 0.1 -- if it is NOT zero, then use multiplication) is what I needed - that should work. Trying now & thanks a lot.

Sven2157 · June 22, 2014

Yes buildings need maintenance because they do take damage over time but from what I understand the "buildings take damage over time" bit is actually happening in MySQL using SQL queries.

Well the script is updating the database, with the new damage figure. All a database does, is store information, so the actual decay is being calculated by the scripts first.

If the query is only a "multiplication based" query, no building will ever take damage because the initial state of every building is 0.0 damage thus damage field cannot be increased by multiplication... << maybe I'm wrong but that's what I believe at the moment..

If decay is left on, then the first query work. If you are unsure using the second query will be just fine.

Your updated query, (if it is zero, then set it to 0.1 -- if it is NOT zero, then use multiplication) is what I needed - that should work. Trying now & thanks a lot.

You are welcome! Let me know how it goes.

=170= Sven2157

*** EDIT ***

I forgot to mention: The RegExp will find ALL instances that contain the words you use. So WOOD is probably not a good word, as it will increase damage on ALL Items that have WOOD in their classname. I wouldd stick to 'wall|floor|door', etc, etc...

;)

mgm · June 22, 2014

Just to be clear, when you say "if decay is left on", you mean hiveext.ini setting "CleanupPlacedAfterDays", right? That setting was "10" (I want to have a 10 day maintenance window).

I now changed it to -1 (disabled it) as I am planning to do everything in SQL >> I will SetDamage in SQL and cleanup the buildables when they reach 1.0 damage.

Back to SQL, after much googling and testing, I came up with this code below which seems to be working. As always, any comments will be much appreciated. Thanks!


-- EXECUTION FREQUENCY: 		To be executed before each server restart (every 3 hours).
-- SUMMARY:				Apply 10% damage to all buildables that have not been updated for longer than 24 hours (i.e.: not maintained by the owner).
UPDATE `object_data` SET 
			`Damage` = CASE
			WHEN `Damage` = '0.0' THEN '0.1'
			WHEN `Damage` = '0.1' THEN '0.2'
			WHEN `Damage` = '0.2' THEN '0.3'
			WHEN `Damage` = '0.3' THEN '0.4'
			WHEN `Damage` = '0.4' THEN '0.5'
			WHEN `Damage` = '0.5' THEN '0.6'
			WHEN `Damage` = '0.6' THEN '0.7'
			WHEN `Damage` = '0.7' THEN '0.8'
			WHEN `Damage` = '0.8' THEN '0.9'
			WHEN `Damage` = '0.9' THEN '1.0'
			ELSE '1.0'
			END
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 HOUR)
		AND `Classname` REGEXP 'wall|floor|door|ladder|stairs'
		AND `ObjectUID` <> 0
		AND `CharacterID` <> 0
		AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]') )

By moving to the code above, any buildable in my MySQL db will have one of the following 11 states.

State 1 – Damage is: 0.0 (maintenance have just been completed (less than 24 hours passed) -- or the buildable has been recently placed)
State 2 – Damage is: 0.1 (no maintenance fee paid for more than 24 hours)
State 3 – Damage is: 0.2 (no maintenance fee paid for 2 days)
State 4 – Damage is: 0.3 ...
State 5 – Damage is: 0.4 ...
State 6 – Damage is: 0.5 ...
State 7 – Damage is: 0.6 ...
State 8 – Damage is: 0.7 ...
State 9 – Damage is: 0.8 ...
State 10 – Damage is: 0.9 (no maintenance fee paid for 9 days)
State 11 – Damage is: 1.0 (no maintenance fee paid for 10 days) -- items will be deleted if the maintenance fee is not paid till next restart (or if the items are not manually maintained).

Moving on with the plan, now I will need to find a way to apply maintenance fee depending on state...

Sven2157 · June 22, 2014

Did my code not work?

UPDATE `object_data` SET 
			`Damage` = CASE
			WHEN `Damage` = '0.0' THEN '0.1'
			WHEN `Damage` = '0.1' THEN '0.2'
			WHEN `Damage` = '0.2' THEN '0.3'
			WHEN `Damage` = '0.3' THEN '0.4'
			WHEN `Damage` = '0.4' THEN '0.5'
			WHEN `Damage` = '0.5' THEN '0.6'
			WHEN `Damage` = '0.6' THEN '0.7'
			WHEN `Damage` = '0.7' THEN '0.8'
			WHEN `Damage` = '0.8' THEN '0.9'
			WHEN `Damage` = '0.9' THEN '1.0'
			ELSE '1.0'
			END
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 HOUR)
		AND `Classname` REGEXP 'wall|floor|door|ladder|stairs'
		AND `ObjectUID` <> 0
		AND `CharacterID` <> 0
		AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]

You do know that what I provided does exactly this. There is no reason to query the database for ObjectUID <> 0, CharacterID <> 0 or for the inventory stuff. All <> 0 means is NOT NULL; a 0( zero ) will produce not null. There should be NO objects in your world without an ID. Any objects in your regex will placed by the player anyway.

If you disabled decay in the server scripting, then the second query I provided will do this. No need for ALL of those cases; you can't be damaged more that 100%. Just run a DELETE all objects that have damage 100% first. Database servers are not used like web servers - you need to keep the queries short and simple.

Anyway, glad you got it worked out...

=170= Sven2157

*** EDIT ***

-- EXECUTION FREQUENCY: 		To be executed before each server restart (every 3 hours).
-- SUMMARY:				Apply 10% damage to all buildables that have not been updated for longer than 24 hours (i.e.: not maintained by the owner).

Just to be clear. Your original post and the above script, say you want to increase the damage by 10%, when in fact you want to increment by 10, all the way to 100. Those are not the same thing. i.e. 10% of 10 = 11 - not 20.

So this is what you really want - note the additon of 0.1, not multiplication by 1.1:

UPDATE `object_data`
IF( `Damage` = 0.0, SET `Damage` = 0.1, SET `Damage` = `Damage` + 0.1 )
WHERE `LastUpdated` >= now() - INTERVAL 1 DAY
AND `Classname` REGEXP 'wall|wood|door'

... I am planning to do everything in SQL ...

You do know that SQL ( Standard Query Language ), is NOT a scripting language, correct? It is a data management language. By trying to use it as a scripting language, you may overburden your database, with disastrous consequences. Just be careful.

=170= Sven2157

mgm · June 22, 2014

Did my code not work?
UPDATE `object_data` SET 
			`Damage` = CASE
			WHEN `Damage` = '0.0' THEN '0.1'
			WHEN `Damage` = '0.1' THEN '0.2'
			WHEN `Damage` = '0.2' THEN '0.3'
			WHEN `Damage` = '0.3' THEN '0.4'
			WHEN `Damage` = '0.4' THEN '0.5'
			WHEN `Damage` = '0.5' THEN '0.6'
			WHEN `Damage` = '0.6' THEN '0.7'
			WHEN `Damage` = '0.7' THEN '0.8'
			WHEN `Damage` = '0.8' THEN '0.9'
			WHEN `Damage` = '0.9' THEN '1.0'
			ELSE '1.0'
			END
WHERE `LastUpdated` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 HOUR)
		AND `Classname` REGEXP 'wall|floor|door|ladder|stairs'
		AND `ObjectUID` <> 0
		AND `CharacterID` <> 0
		AND ( (`Inventory` IS NULL) OR (`Inventory` = '[]
You do know that what I provided does exactly this. There is no reason to query the database for ObjectUID <> 0, CharacterID <> 0 or for the inventory stuff. All <> 0 means is NOT NULL; a 0( zero ) will produce not null. There should be NO objects in your world without an ID. Any objects in your regex will placed by the player anyway.

If you disabled decay in the server scripting, then the second query I provided will do this. No need for ALL of those cases; you can't be damaged more that 100%. Just run a DELETE all objects that have damage 100% first. Database servers are not used like web servers - you need to keep the queries short and simple.

Anyway, glad you got it worked out...

=170= Sven2157

I appreciate the quick responses.

I have not run your 2nd query in SQL server with 10 day simulation however I checked it in spreadsheet (picture copied below) and it doesn't seem to provide the desired results.

Just to clarify, I would like to see the results listed in cases (no damage should be 0.0, one day of skipped maintenance should result with a damage level of 0.1, 2 days 0.2....., and finally 10 days is total damage 1.0).

I should not see any other values such as 0.11, 0.12 etc.

Below is what I get when I simulate your code in spreadsheet:

Maybe I'm doing something wrong?

I understand the desired method of keeping SQL queries short and simple but the query above runs under 0.10 second so we do not have a performance issue, not today, not within the next 10 years with this MySQL server hardware lol.

About ObjectUID <> 0, CharacterID <> 0,

you are right they should not be there but vbawol is using that trigger in his SQL maintenance script. I don't know why but if he's using it, maybe there's a reason, so I simply copied that part from him. As per above, since we do not have a need to improve performance by 0.08 seconds, I'm fine with keeping this tiny extra bits and attached slowdown.

With regards to the cases & impossibility of being damaged more than 1.0:

Again, you are right, we cannot be more damaged than 1.0. I did not claim we could be more damaged than 1.0 however.

And the cases are there because as of now I am unable to set my NewDamage to a clean 0.10, 0.20 unless I use cases.

They are just checking the precise existingDamage level so that the newDamage can be incremented precisely.

(Obviously all these nice SQL damage levels won't help at all if I cannot somehow manage, on the Epoch end, to read this values and present corresponding "maintenance fees". I'm hoping I'll find a way...)

Sven2157 · June 22, 2014

I guess I should have just posted this in a separate post. You were typing when I added this:

*** EDIT ***
Just to be clear. Your original post and the above script, say you want to increase the damage by 10%, when in fact you want to increment by 10, all the way to 100. Those are not the same thing. i.e. 10% of 10 = 11 - not 20.
So this is what you really want - note the additon of 0.1, not multiplication by 1.1:
UPDATE `object_data`
IF( `Damage` = 0.0, SET `Damage` = 0.1, SET `Damage` = `Damage` + 0.1 )
WHERE `LastUpdated` >= now() - INTERVAL 1 DAY
AND `Classname` REGEXP 'wall|wood|door'
You do know that SQL ( Standard Query Language ), is NOT a scripting language, correct? It is a data management language. By trying to use it as a scripting language, you may overburden your database, with disastrous consequences. Just be careful.
=170= Sven2157

That should take care of your increments. As for the ObjectUID, and others. If you don't understand why it is there, you really should not include it. If you have a problem down the road, and you don't fully know what it is that you are writing, how are you going to be able to tell if that is the problem or not?

=170= Sven2157

mgm · June 22, 2014

I guess I should have just posted this in a separate post. You were typing when I added this:

That should take care of your increments. As for the ObjectUID, and others. If you don't understand why it is there, you really should not include it. If you have a problem down the road, and you don't fully know what it is that you are writing, how are you going to be able to tell if that is the problem or not?

=170= Sven2157

Well, I understand the SQL query above runs on objects that are NOT '0' charID. In other words it is not affecting any rows with charid=0.

It is not the query that I do not understand, I just don't know in what circumstances epoch database might have that (mine surely does not have that at the moment and I will not add it manually but apparently it might somehow be generated. There must be a reason they added it there. My assumption is the lead developer knows what he is doing so I will just leave it there. If there are no objects with charid 0 that code won't hurt anyway.

About the 10% additional damage thing, 10% of total health and 10% of existing damage are different things yes I'm aware - I didn't really focus on wording. What I meant was 10% damage increments. I think it is better worded now?

-- SUMMARY:						Apply 0.10 damage to all buildables that have not been updated for longer than 24 hours (i.e.: not maintained by the owner).
-- 							(Set the initial damage level to 0.10 if no damage exists).

I indeed was typing at the time and missed your edit.

I am now aware of your latest code ==>> (( `Damage` = 0.0, SET `Damage` = 0.1, SET `Damage` = `Damage` + 0.1 ))

This is exactly what I wanted to do so it looks perfect. However when I copy & paste into MySQL (version: mysql.exe Ver 14.14 Distrib 5.6.19, for Win64 (x86_64)) in my Navicat (v9) client it does not execute successfully.

I only modified the 'wood' part as you can see in the picture below -- otherwise it is direct copy & paste from your post.

What shall I change here?

Sven2157 · June 22, 2014

Sorry it was late. This should work:

UPDATE `object_data`
SET `Damage` = IF( `Damage` = 0.0, 0.1, `Damage` + 0.1 )
WHERE `LastUpdated` <= DATE_SUB(NOW(), INTERVAL 1 DAY)
AND `Classname` REGEXP 'wall|floor|door|ladder|stair'

Let me know ...

=170= Sven2157

mgm · June 22, 2014

Sorry it was late. This should work:

UPDATE `object_data`
SET `Damage` = IF( `Damage` = 0.0, 0.1, `Damage` + 0.1 )
WHERE `LastUpdated` <= DATE_SUB(NOW(), INTERVAL 1 DAY)
AND `Classname` REGEXP 'wall|floor|door|ladder|stair'

Let me know ...

=170= Sven2157

Sorry took too much of your time.I have copy & pasted & executed this - no errors.

My active setdamage script contains this code now, I'll monitor it and will report if any unexpected issues come up. Thanks for an elegant solution.

Sven2157 · June 22, 2014

Sorry took too much of your time.

No worries man. If I didn't want to help, I could have just stopped posting! ;)

I have copy & pasted & executed this - no errors.
My active setdamage script contains this code now, I'll monitor it and will report if any unexpected issues come up. Thanks for an elegant solution.

Let know how it goes. You are welcome!

=170= Sven2157

Need SQL help & Epoch help (maintenance/setDamage)

Question

Link to comment

Share on other sites

14 answers to this question

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Similar Content