Jump to content
  • 0

Tracking player name changes...


PetuniaEpoch

Question

Hey all, 

 

Does anyone know of a way to track players who change their names?

 

I've thought about rcon tools etc, but I really want to make players in-game name changes viewable to the public, on our website.

 

I can query our gameserver database no probs, but not sure how to go about tracking name changes, as on each login, it obviously overwrites the player_data table.

 

I can add a new table to the database etc if needed, just not sure how to store names at logins, cross check against PlayerUID, and then show all names from any give PlayerUID :(

 

Anyone done this and have a solution?

 

I don't want to reveal players UID's to the public, but do want to list all alt-versions of a players name :S

 

Any ideas?

Link to comment
Share on other sites

Recommended Posts

  • 0

 

Hey guys, I was just looking into getting this set up on our server and I can create the table fine but making the trigger is giving me troubles.

 

I may be going about it wrong but I created the player_alias table by executing an sql file with the first block of code.

 

Then when I try to add the trigger using the same method I get an error back that reads:

[Err] 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 'DELIMITER' at line 1
[Err] DELIMITER ;
[Msg] Finished - Unsuccessfully
--------------------------------------------------
 
I am running a MySQL database through Navicat Lite.
 
Am I completely missing how to add this trigger properly or has something changed since the code was released?

 

 

Have a look at the post in my

 

MGM was having the same problem with navicat and delimiters.  scroll down for for suggestions in that thread to fix. 

 

I suspect it is Navicat 'helping'users causing the issue.  As an alternative, take a look at MySQL Workbench which is free and comes with MySQL.

Link to comment
Share on other sites

  • 0

RimBlock -  Thanks for the advice, that worked for me. 

 

I ran the code in 2 separate blocks and cut out the delimiter parts.

 

Part 1:

DROP TRIGGER IF EXISTS `updateName`;

Part 2:

CREATE TRIGGER `updateName`
AFTER UPDATE ON player_data
FOR EACH ROW
BEGIN
	#IF (NEW.PlayerName IS NULL OR NEW.PlayerName = '' OR NEW.PlayerName = OLD.PlayerName) THEN
	IF (SELECT EXISTS(SELECT uid FROM player_alias WHERE `uid` =  NEW.PlayerUID))  THEN   
		UPDATE player_alias SET PlayerNames = CONCAT_WS(',',PlayerNames,NEW.PlayerName) WHERE uid = NEW.PlayerUID;
	ELSE
		INSERT INTO player_alias (uid, PlayerNames) VALUES (NEW.PlayerUID,NEW.PlayerName);
	END IF;
END;

This was of course after running the 1st block of code posted by itsatrap on the first page to create the player_alias table. Maybe I could have run the 2 blocks of code together without the delimiter but either way this worked for me.

Link to comment
Share on other sites

  • 0

Ok, so here is a first round of code.

 

It adds an old & new row per playeruid / alias.  There is also a note column.  The code does not care if that playerUID / alias entry already exists or not.

 

As always, test before using on your live data.  There may be possibilities that have not been considered.

 

Create the player alias table

DROP TABLE IF EXISTS `player_alias`;


CREATE TABLE IF NOT EXISTS `player_alias` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tstamp` datetime, 
  `action` varchar(3),
  `playeruid` varchar(255) DEFAULT NULL,
  `PlayerName` text,
  `notes` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Create new trigger

DELIMITER ;
DROP TRIGGER IF EXISTS `updateName`;

DELIMITER //
CREATE TRIGGER `updateName`
BEFORE UPDATE ON player_data
FOR EACH ROW
BEGIN
  INSERT INTO player_alias (tstamp, action, playeruid, PlayerName, notes) select now(), "old", PlayerUID,playerName, "" from player_data where PlayerUID = NEW.playerUID;
  INSERT INTO player_alias (tstamp, action, playeruid, PlayerName, notes) VALUES (now(), "new", NEW.PlayerUID,NEW.PlayerName, "");
END//
DELIMITER ;
What you should get is something like the following in the new player_alias table.
 
ID        tdate            action  playerUID    playername     note
1    2014-06-22 12:36:00    old    999999999    Armchair        ""
2    2014-06-22 12:36:00    new    999999999    Right Angle     ""
3    2014-06-22 12:36:00    old    111111111    Hairy Harry     ""
4    2014-06-22 12:36:00    new    11111111     Awesome         ""
5    2014-06-22 12:36:50    old    999999999    Right Angle     ""
6    2014-06-22 12:36:50    new    999999999    Avacado21       ""

The table can grow rapidly depending on how often players change their names (and how many players you have).

 

A housekeeping script will keep the number of entries down.

DROP EVENT IF EXISTS `housekeepName`;
DELIMITER ;;
CREATE EVENT `housekeepName` ON SCHEDULE EVERY 1 DAY COMMENT 'Housekeeps the player_alias table' DO DELETE FROM `player_alias` WHERE `tstamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 30 DAY) 
;;
DELIMITER ;

This will run every day and delete any entries older than 30 days.  Change the '30' in the command to increase or decrease the number of days.

 
You can then run sql to find all the playernames used by a playerUID without the need to chop up a text filed.  You can see when the name was changed, what it used to be, what it was changed to and any notes put in by, presumabily admins, relating the that playeruid and playername.
 
From the above example, playerUID changed their playername from "Armchair" to "Right Angle"and then to "Avacado21"

 

Any issues then let me know here or via PM.

 

I will be adding one of these for the object_data table allowing historical worldspace tracking of players and objects saved to the DB along with inventory changes, lockable code changes, key changes, the whole lot.  This will obviously have a DB impact but the advantages it could give are big.

Link to comment
Share on other sites

  • 0

HI Rimblock,

 

Thanks for putting your time and effort into this.

 

The only problem with doing it this way is that you will have to do a quick search by PUID for each player's name change info. That's not a big problem but it also means that you will get a new row every time the player toggles between names so, this will grow a lot. Especially for players that constantly toggle between two or three names very regularly. My admins and I change names quite frequently, some maybe 3 or 4 times a day. The reason being if a lot of admins are on and we are well covered, one of them that's done a lot of admin'ing that day may want to take a break from admin duties and actually play the game for a change.... :)

 

With the original, it would keep 1 row per player and append all the names into one player names column which, I prefer as I can leave the table sorted by its ID column to see newest first without any sql or searching. However, the downsides with the original was that it would keep appending the same names into the player name column rather than checking to see if a name was already in there and it wouldn't let you know the original name the player had changed from.

 

I see you have had to do it this way because of the addition of the timestamp column. Personally, I'm not that interested in 'when' they change their name but, would like to see on one row all the names that a player has used, is using and the original name they had before changing it for the first time. What also be really fantastic would be if the ID changed every time there was some sort of change so it kept the name changers to the top of the list when the column is sorted by ID...

 

However, rather than me getting a bespoke service, I suppose it's up to the wider community to comment.. :) But, what do you think...?

Link to comment
Share on other sites

  • 0

HI Rimblock,

 

Thanks for putting your time and effort into this.

 

The only problem with doing it this way is that you will have to do a quick search by PUID for each player's name change info. That's not a big problem but it also means that you will get a new row every time the player toggles between names so, this will grow a lot. Especially for players that constantly toggle between two or three names very regularly. My admins and I change names quite frequently, some maybe 3 or 4 times a day. The reason being if a lot of admins are on and we are well covered, one of them that's done a lot of admin'ing that day may want to take a break from admin duties and actually play the game for a change.... :)

 

With the original, it would keep 1 row per player and append all the names into one player names column which, I prefer as I can leave the table sorted by its ID column to see newest first without any sql or searching. However, the downsides with the original was that it would keep appending the same names into the player name column rather than checking to see if a name was already in there and it wouldn't let you know the original name the player had changed from.

 

I see you have had to do it this way because of the addition of the timestamp column. Personally, I'm not that interested in 'when' they change their name but, would like to see on one row all the names that a player has used, is using and the original name they had before changing it for the first time. What also be really fantastic would be if the ID changed every time there was some sort of change so it kept the name changers to the top of the list when the column is sorted by ID...

 

However, rather than me getting a bespoke service, I suppose it's up to the wider community to comment.. :) But, what do you think...?

I actually created a test table which had the unique PlayerID field (so anything in that row would be about that particular dude) and then:

entry1TimeStamp|entry1OldName|entry1NewName|entry2TimeStamp|entry2OldName|entry2NewName... 

I went till 10, I assumed 10 would provide a reasonable length of history for player name changes.

 

Columns above look good to me but I don't know if it's feasible in the trigger/update code side of things but just an ideaa... Would our in-house SQL guru maybe consider this approach?

Link to comment
Share on other sites

  • 0

HI Rimblock,

 

Thanks for putting your time and effort into this.

 

...

 

I see you have had to do it this way because of the addition of the timestamp column. Personally, I'm not that interested in 'when' they change their name but, would like to see on one row all the names that a player has used, is using and the original name they had before changing it for the first time. What also be really fantastic would be if the ID changed every time there was some sort of change so it kept the name changers to the top of the list when the column is sorted by ID...

 

However, rather than me getting a bespoke service, I suppose it's up to the wider community to comment.. :) But, what do you think...?

 

Yep it will grow a lot hence the tweekable housekeeping included.  If there are people you do not wish recorded it is also very easy to exclude them from being reported on.

 

On the 4th June someone reported a player called "BigPants" was causing massive problems destroying peoples cars for no reason.  You find 3 players have used the "BigPants"alias on your server.  Which one do you take action against or do you do nothing.

 

With the method I have put together you can track which of those players were using that alias on that date and what their current alias is.  Makes it a lot easier to reconstruct events.

 

The sql needed would just be

select * from player_alias where playername = "BigPants";

Not really much different from the sql needed to quest the table the way you have it.  It may be the tool you are using is writing the sql in the background but it is sql getting the info out.

 

Going a bit further you can find out which players changed playernames between a set timeframe, how often players are changing their playernames, which are the most popular playernames, who is using playernames that break the server rules ("Admin") for example and keep notes on who, under which playername and when someone got a warning or was banned that all admins would have access too.  No need to trawl through emails or shared google docs etc.

 

Bottom line is that is it two different ways to resolve a problem.  Yours is fairly easy and simple to maintain.  Mine offers more possibilities but may require a bit more work.  Different people are likely to prefer one or the other depending on how they want to setup[ their server.  Now they have two options.

 

For your query, the better way if you are keeping to one row per playerUID is to add a timestamp column, a first username column (not sure why you would want this) and then just change the timestamp each time the alias changes.  Changing the id would mean deleting the row and inserting as new row and if the changes are as regular as you say you will be burning through ids quite quick.  Updating a row is likely to be much more efficient.

Link to comment
Share on other sites

  • 0
On 6/22/2014 at 1:47 AM, RimBlock said:

Ok, so here is a first round of code.

 

It adds an old & new row per playeruid / alias.  There is also a note column.  The code does not care if that playerUID / alias entry already exists or not.

 

As always, test before using on your live data.  There may be possibilities that have not been considered.

 

Create the player alias table


DROP TABLE IF EXISTS `player_alias`;


CREATE TABLE IF NOT EXISTS `player_alias` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tstamp` datetime, 
  `action` varchar(3),
  `playeruid` varchar(255) DEFAULT NULL,
  `PlayerName` text,
  `notes` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Create new trigger


DELIMITER ;
DROP TRIGGER IF EXISTS `updateName`;

DELIMITER //
CREATE TRIGGER `updateName`
BEFORE UPDATE ON player_data
FOR EACH ROW
BEGIN
  INSERT INTO player_alias (tstamp, action, playeruid, PlayerName, notes) select now(), "old", PlayerUID,playerName, "" from player_data where PlayerUID = NEW.playerUID;
  INSERT INTO player_alias (tstamp, action, playeruid, PlayerName, notes) VALUES (now(), "new", NEW.PlayerUID,NEW.PlayerName, "");
END//
DELIMITER ;
What you should get is something like the following in the new player_alias table.
 

ID        tdate            action  playerUID    playername     note
1    2014-06-22 12:36:00    old    999999999    Armchair        ""
2    2014-06-22 12:36:00    new    999999999    Right Angle     ""
3    2014-06-22 12:36:00    old    111111111    Hairy Harry     ""
4    2014-06-22 12:36:00    new    11111111     Awesome         ""
5    2014-06-22 12:36:50    old    999999999    Right Angle     ""
6    2014-06-22 12:36:50    new    999999999    Avacado21       ""

The table can grow rapidly depending on how often players change their names (and how many players you have).

 

A housekeeping script will keep the number of entries down.


DROP EVENT IF EXISTS `housekeepName`;
DELIMITER ;;
CREATE EVENT `housekeepName` ON SCHEDULE EVERY 1 DAY COMMENT 'Housekeeps the player_alias table' DO DELETE FROM `player_alias` WHERE `tstamp` < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 30 DAY) 
;;
DELIMITER ;

This will run every day and delete any entries older than 30 days.  Change the '30' in the command to increase or decrease the number of days.

 
You can then run sql to find all the playernames used by a playerUID without the need to chop up a text filed.  You can see when the name was changed, what it used to be, what it was changed to and any notes put in by, presumabily admins, relating the that playeruid and playername.
 
From the above example, playerUID changed their playername from "Armchair" to "Right Angle"and then to "Avacado21"

 

Any issues then let me know here or via PM.

 

I will be adding one of these for the object_data table allowing historical worldspace tracking of players and objects saved to the DB along with inventory changes, lockable code changes, key changes, the whole lot.  This will obviously have a DB impact but the advantages it could give are big.

Here is an slightly modified version which prevents duplicated on player update without name change.

DELIMITER ;
DROP TRIGGER IF EXISTS `updateName`;

DELIMITER //
CREATE TRIGGER `updateName`
BEFORE UPDATE ON player_data
FOR EACH ROW
BEGIN
 IF OLD.playerName != NEW.PlayerName THEN
  INSERT INTO player_alias (tstamp, action, playeruid, PlayerName, notes) select now(), "old", PlayerUID,playerName, "" from player_data where PlayerUID = NEW.playerUID;
  INSERT INTO player_alias (tstamp, action, playeruid, PlayerName, notes) VALUES (now(), "new", NEW.PlayerUID,NEW.PlayerName, "");
 END IF;
END//
DELIMITER ;

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Advertisement
  • Discord

×
×
  • Create New...