Arma 2 DayZ Epoch SQL dead body cleaner

Question

I have been trying to figure out a way to do something like what this Delete all records except the most recent one? But I have been unable to apply it to my circumstance.

My circumstance: https://gyazo.com/178b2493e42aa4ec4e1a9ce0cbdb95d3

SELECT * FROM dayz_epoch.character_data;

CharacterID, PlayerUID, InstanceID, Datestamp, LastLogin, Alive, Generation

5 |76561198068668633|11|2016-05-31 18:21:37|2016-06-01 15:58:03|0|1
6 |76561198068668633|11|2016-06-01 15:58:20|2016-10-08 21:30:36|0|2
7 |76561198068668633|11|2016-10-08 21:30:52|2016-10-09 18:59:07|1|3
9 |76561198010759031|11|2016-10-08 21:48:32|2016-10-08 21:53:31|0|2
10|76561198010759031|11|2016-10-08 21:53:55|2016-10-09 19:07:28|1|3

(Look at image above) So I am currently trying to make a better method for deleting dead bodies from my database for my DayZ Epoch server. I need a code to delete Where ALIVE = 0 if that same PlayerUID has another instance where it is ALIVE = 1.

The other thing the code could do is just delete all players except the most recent one for each PlayerUID. I hope this makes sense. It's hard to explain. The first link explains better for me.

But basically, I want to delete any dead player that now has an alive player with that same PlayerUID. If I were better at coding, I could see many variables I could use like PlayerUID (a must), Datestamp, Alive, and generation. Probably only need 2 of those, one being the PlayerUID.

Thanks a bunch.

The easiest to me seems like it would be something like: SORT by PlayerUID AND FOR EACH PlayerUID DELETE ALL EXCEPT(?) newest Datestamp. This would keep the player stats from their dead body in case they do not create a new character before this script is called.

Please post plain-text data as text, not as a screenshot. That picture can't be put into a tool like [SQLFiddle](http://sqlfiddle.com) to test answers. — tadman, Oct 11 '16 at 07:53
That makes it a lot more helpful for people who want to answer this question. — tadman, Oct 11 '16 at 20:35
Did you tried with a trigger on insert ? I guess the only way to have `ALIVE=1` is with a new line, so an insert. On this insert, take the new line PlayerUID and delete the other lines (can there be to with ALIVE=1 ?) — AxelH, Oct 12 '16 at 07:10
@AxelH Not sure if I fully understand. For each PlayerUID there can only be ALIVE=1 one time. Alive=1 means that player is alive. If I were to just delete all players that were ALIVE=0 (meaning they are dead), then if a player didn't have a character that was ALIVE=1, that player's stats would be wiped (when the character loads in, they use the last data for their PlayerUID that is in the SQL database, and that's what gives them their stats), so we have to have their most recent body in the database. Really hard to explain. — looter809, Oct 12 '16 at 20:24
But basically, I need to sort by PlayerUID, and then Delete each body of each PlayerUID except their most recent body. I just don't know the code for such a thing. It's a lot like the "Delete all records except the most recent one" link above, except I think they have two tables they are looking at, and I have all of my data on one table. — looter809, Oct 12 '16 at 20:26
@AxelH, but no I did not try a trigger on insert, not sure how I would do that. For each PlayerUID (each player) there is only one possible ALIVE=1. — looter809, Oct 12 '16 at 20:29

AxelH · Answer 1 · 2016-10-18T06:29:21.673

0

So basicly, you need to be sure that on a Insert (or update of ALIVE to 1) of a player, you removed all previous (just in case, normaly there should be only one) player with the same PlayerUID as the new one.

The easiest is to create a trigger that will run before the insert (and on UPDATE if this is possible to update ALIVE to 1 to revieve one). Using the UID of the new player to run a delete on the table for the specific UID. This is that simple ;)

For the trigger, this should look like this

Create trigger CLEAR_PLAYER 
   before insert on dayz_epoch.character_data
   For Each Row
       Delete from dayz_epoch.character_data 
           where PlayerUID = NEW.PlayerUID
           and   Alive = 0 --Just in case, but what will happen if there where a line with Alive = 1 ?

This will be executed before the insert in the table dayz_epoch.character_data (so don't remove the new one). This will remove every line with the PlayerUID of the inserted line. If you want to add some security, you could add the and Alive= 0 in the condition.

Edit :

Didn't write trigger in a long time, but I use the official doc as a reminder. Take a look if you need.

edited Oct 18 '16 at 06:29

answered Oct 13 '16 at 06:27

AxelH

14,325
2
25
55

`Create trigger CLEAR_PLAYER before insert on dayz_epoch.character_data Delete from dayz_epoch.character_data where PlayerUID = NEW.PlayerUID AND 'ALIVE' = 0` – looter809 Oct 13 '16 at 20:53
It's saying Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Delete from dayz_epoch.character_data where PlayerUID = NEW.PlayerUID' at line 2 – looter809 Oct 13 '16 at 20:54
Okay, I added `For each row` and it accepted it. Will test soon and let you know. – looter809 Oct 13 '16 at 21:03
This won't work because it doesn't allow new characters to be created. :/ – looter809 Oct 14 '16 at 01:59
Like a said, didn't write a trigger in a long time ;) Won't allow ? Then you CAN'T delete characters because you will end up with no character fast. Why do you want to delete if you can't create ?.... – AxelH Oct 14 '16 at 06:22
So I want to delete the old bodies to keep the database clean and fast. When a player dies their ALIVE = 0 and then a new character is created. Also if a new player joins the server who has not been here before, they also create a new character (new CharacterID and whole line in the table). So what I want is (this could be an event of a trigger or maybe some other function) for if the PlayerUID (the PlayerUID is that person. It is their identity and is only to them) already exists then to delete the old one. Or as a event you could do something like: Sort by PlayerUID and repeats of PlayerUID. – looter809 Oct 14 '16 at 20:32
So any ideas. I don't know SQL very well. – looter809 Oct 17 '16 at 02:42
Well, the trigger is doing exactly what you want. Each time a new Character is inserted in the table, it will execute the code in the trigger, that means it will remove every row with the same playerUID, since this is execute BEFORE the insert, this will not delete the new row. So you end up only with the ONE row... if something is not working, then you are missing some details. – AxelH Oct 17 '16 at 10:57
I did exactly what you told me and got that error I mentioned above. It doesn't like the delete statement. And when I added FOR EACH ROW above the delete it made players not able to create a new character. It's probably something wrong on my end, since I'm the noob here, but I am copying it exactly like that, and I told you all the info. :/ – looter809 Oct 17 '16 at 20:21
@looter809 What is the error with the correction (for each row) ? (Did you read the doc to see how triggers works ? Everything is in there.) – AxelH Oct 18 '16 at 11:18
There isn't an error if I add "for each row" but it won't allow new characters to be added. As more players join, or respawn, it will add new characters in the database. I need it to check if there is already an old playerUID when a new one is being created and delete if that's the case. Or just to make an event everyday that checks all playeruids and deletes all but the most recent for each playeruid. – looter809 Oct 26 '16 at 01:32
@looter809 `it won't allow new characters to be added` why ? Unless I miss something, my trigger means _On an insert in this table, delete all row where the PlayerUID of a row equals the PlayerUID of the new row. When this is done, insert the new row._ So this does exactly what you asked, "remove all previous record of a player when this one is inserted again" (keep only the last one). If this doesn't work, you forgot to tell something! – AxelH Oct 26 '16 at 06:20
I don't know then. I tested it again today. It just doesn't let new players create a database entry. The server tells the player waiting for authentication while they load in (where the character is created), and it doesn't continue from there. – looter809 Oct 27 '16 at 01:11
Did you tried to do an insert with the trigger active before you put that in "production". If you can add the CREATE TABLE query, I would be able to put that on my server to test it myself before ;) – AxelH Oct 27 '16 at 06:34
Not quite sure what you mean. Sorry. Are you asking for the create table files for the sql? If it is what you mean, [link](https://github.com/EpochModTeam/DayZ-Epoch/blob/Release_1.0.5.1/Server%20Files/SQL/epoch.sql) but without the game with it you won't see that error when people try to load into the server – looter809 Oct 29 '16 at 02:03

Arma 2 DayZ Epoch SQL dead body cleaner

1 Answers1