Proposing a MySQL Database Change

[Wheaticus]

So I'm in the process of trying to create an event that deletes items built by users that have not logged on in over 30 days. This should be fairly easy if not for two things, MySql has problems doing a table update from a sub query select from statement, and 2nd, the 1st would not be a problem if placed items were tied to the CharacterUID and not the CharacterID (which changes on character death).

 

I understand the reasoning back when everyone was running vanilla DayZ, when you die, in theory, you're a new person and can't pack up your previous life's tent, got it.

 

However this no longer really applies to Epoch.  If Epoch followed that same logic, upon death a players combo locks and safe combos should change so that a "new" character would have no prior knowledge of what those combos were.

 

Obviously this defeats the purpose of having combo locks and safes, if they were one life use, no one would use them.

 

So with that logic in mind, what would it take to change the database tables to base the items built keyed to the CharacterUID instead of the CharacterID?

 

I'll go ahead and post the script I was working on, maybe someone smarter than me with MySQL and can make it work without altering the DB tables, if so I'd love to see it.  This script does take into account the fact that the CharacterID of a user changes upon death.  

 

What it does not take into account, and cannot take into account is that if a user builds a base with another user.  If user 1 does not login for X number of days, the items would be deleted, leaving user 2 with a funky looking base.  In this case it would be user 2's responsibility to notify an admin that user 1 hasn't been on in a while and would like to take ownership of built items (not vehicle/safe items) before the X DELETE day mark.  There's no way around that with a script currently.

 

Anyway:

DELETE FROM object_data WHERE CharacterID in (
   SELECT CharacterID FROM object_data 
      WHERE CharacterID IN (
         SELECT CharacterID FROM character_data
            WHERE LastLogin < DATE_SUB(NOW(), INTERVAL 30 DAY)
               AND PlayerUID NOT IN (
                  SELECT PlayerUID FROM character_data
                     WHERE LastLogin > DATE_SUB(NOW(), INTERVAL 30 DAY))))