• Implementing: Character History

Posted: **Thu Apr 11, 2013 6:38 pm**

Jab, since you love distractions, I have something I've been struggling with for years (literally) and while I generally grasp the concepts, I just cannot seem to implement... so I need help.

I have a table in SQL to store many different bits of data that happen during a players career - called "character_history", or events, or progress, or whatever we want to call it. Looks something like this:

Code: Select all

CREATE TABLE `character_history` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `char_id` int(10) unsigned NOT NULL DEFAULT '0',
  `type` enum('None','Death','Discovery','XP') COLLATE latin1_general_ci NOT NULL DEFAULT 'None',
  `subtype` enum('None','Adventure','Tradeskill','Quest','AA','Item','Location') COLLATE latin1_general_ci NOT NULL DEFAULT 'None',
  `value` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `location` varchar(200) COLLATE latin1_general_ci DEFAULT '',
  `event_id` int(10) unsigned NOT NULL DEFAULT '0',
  `event_date` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `CharHistoryIDX` (`char_id`,`type`,`subtype`),
  CONSTRAINT `FK_character_history` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

Examples, when a player:

- dies in combat, it is a "Death", "Adventure", value is the spawn_id, location could be the zone name, location name, etc...
- discovers a location: "Discovery", "Location", location_id, etc.
- discovers an item: "Discovery", "Item", item_id, blah blah blah.

Re-usable enums, like we do with Rules. The list I came up with is not definitive...

Anyway, the part I cannot comprehend is the C++ side, of course. Years ago, Scat told me it should be a Player class map, one for each thing you're tracking (Discovery Locs for instance), so it would be like in Player, map<zone_id, vector<list_of_locs_discovered> >, then the player would also have a list of their Achievements progress - map<achievement_id, vector<progress> >, and maybe a few other smaller maps for tracking other stuff.

Things that are "stats-like" could be handled like we currently do World/Player stats now (server_stats table), only move the player stuff out of there to this new system. Deaths are not mapped, just stored immediately. Like leveling up, discovering an item, being the first to create an item via tradeskills, all that just gets written to `character_history` as it's happening.

It's the tracking of Discovery Locations that I think need to be in memory so we can flip through them quickly. Achievements as well, to see if you just passed that 500th kill.

I need to nail this system down soon so we can move forward with Achievements - not because it's an important system, but because we started it, and I'd like to finish it. And, it's kinda cool.

If my initial plan is completely whack, that's okay. Tell me how you would do this, and please find some time in your busy distractions

to help me get this off my plate once and for all. It's haunting me

Thanks

Ref:
http://eq2emulator.net/phpBB3/viewtopic ... 575#p13575

http://eq2emulator.net/phpBB3/viewtopic ... 969#p21969

Posted: **Thu Apr 11, 2013 7:39 pm**

Player history will also be needed for AA and there are several elements in the char sheet that would use the history. Did you already start adding some of this code in or is this

Code: Select all

((Player*)spawn)->UpdatePlayerStatistic(STAT_PLAYER_TOTAL_NPC_KILLS, 1);

for the `statistics` table?

Still thinking this over so not much to add right now.

Posted: **Sat Apr 13, 2013 12:43 am**

That was "Round 1" of mine and Scatman's design. Originally, I just wanted Server Stats - like when the server started, how many connections, max concurrent connections, all those defines you see.

Code: Select all

// Server Utilization
#define STAT_SERVER_OS_TYPE						1		// what OS this server is running on
#define STAT_SERVER_CPU_TYPE					2		// cpu type/speed (ie., Intel P4 3.0GHz)
#define STAT_SERVER_CPU_CURRENT					3		// current CPU usage by EQ2World.exe process
#define STAT_SERVER_CPU_PEAK					4		// highest CPU usage by EQ2World.exe this session
#define STAT_SERVER_PHYSICAL_RAM_TOTAL			5		// total RAM in server
#define STAT_SERVER_PHYSICAL_RAM_CURRENT		6		// current RAM usage by EQ2World.exe
#define STAT_SERVER_PHYSICAL_RAM_PEAK			7		// highest RAM usage by EQ2World.exe this session
#define STAT_SERVER_VIRTUAL_RAM_TOTAL			8		// total vRAM in server
#define STAT_SERVER_VIRTUAL_RAM_CURRENT			9		// current vRAM usage by EQ2World.exe
#define STAT_SERVER_VIRTUAL_RAM_PEAK			10		// highest vRAM usage by EQ2World.exe this session
#define STAT_SERVER_DISK_USAGE					11		// size of /eq2emulator folder and contents
#define STAT_SERVER_THREAD_COUNT				12		// thread count of EQ2World.exe process
#define STAT_SERVER_AVG_LATENCY					13		// network latency between world and loginserver

// Server Stats
#define STAT_SERVER_CREATED						100		// unix_timestamp of date server first came online
#define STAT_SERVER_START_TIME					101		// unix_timestamp of date/time server booted up
#define STAT_SERVER_ACCEPTED_CONNECTION			102		// successful connections since server startup
#define STAT_SERVER_MOST_CONNECTIONS			103		// most players online in history of server
#define STAT_SERVER_NUM_ACCOUNTS				104		// total number of unique accounts
#define STAT_SERVER_NUM_CHARACTERS				105		// total number of player characters
#define STAT_SERVER_AVG_CHAR_LEVEL				106		// average level of player characters
#define STAT_SERVER_NUM_ACTIVE_ZONES			107		// number of currently running/loaded zones
#define STAT_SERVER_NUM_ACTIVE_INSTANCES		108		// number of active zones that are "instances"

The stats grew as we attempted to implement player/guild and "Top Most" stats, and we just glommed all the data into `statistics` for now.

Code: Select all

// Player PvE counters
#define STAT_PLAYER_TOTAL_NPC_KILLS				1000	// total NPC kills by player
#define STAT_PLAYER_TOTAL_DEATHS				1001	// total non-PvP deaths of player
#define STAT_PLAYER_KVD_RATIO					1002	// kill-versus-death ratio of player
#define STAT_PLAYER_HIGHEST_MELEE_HIT			1003	// players highest melee hit to date
#define STAT_PLAYER_HIGHEST_MAGIC_HIT			1004	// players highest magic hit to date
#define STAT_PLAYER_HIGHEST_HO_HIT				1005	// players highest heroic opportunity hit
#define STAT_PLAYER_TOTAL_STATUS				1006	// player total status
#define STAT_PLAYER_TOTAL_WEALTH				1007	// player total wealth
#define STAT_PLAYER_QUESTS_COMPLETED			1008	// total quests completed
#define STAT_PLAYER_RECIPES_KNOWN				1009	// total recipes player knows
#define STAT_PLAYER_TOTAL_CRAFTED_ITEMS			1010	// total items crafted by player
#define STAT_PLAYER_ITEMS_DISCOVERED			1011	// total items discovered by player
#define STAT_PLAYER_RARES_HARVESTED				1012	// total rare harvests by player
#define STAT_PLAYER_ITEMS_HARVESTED				1013	// total rare harvests by player
#define STAT_PLAYER_MASTER_ABILITIES			1014	// total master abilities player has
#define STAT_PLAYER_HIGHEST_FALLING_HIT			1015	// player's highest damage amount taken from falling

// Player PvP counters
#define STAT_PLAYER_TOTAL_PVP_KILLS				1100	// total PVP kills by player
#define STAT_PLAYER_PVP_KILL_STREAK				1101	// longest PVP kill streak of player
#define STAT_PLAYER_TOTAL_PVP_DEATHS			1102	// total PVP deaths of player
#define STAT_PLAYER_PVP_DEATH_STREAK			1103	// longest PVP death streak of player
#define STAT_PLAYER_PVP_KVD_RATIO				1104	// PVP kill-versus-death ratio of player
#define STAT_PLAYER_TOTAL_ARENA_KILLS			1105	// total Arena kills of player

2 years later, I discovered that implementation will not work for player/guild-related stats like I wanted to track, and what we'll need for things like disco locs and Achievements. And you're right, the AA earnings for specific accomplishments.

Side note: these "Most" stats you see:

Code: Select all

// MOST stats for players
#define STAT_PLAYER_MOST_NPC_KILLS				1200	// IPvP: Player with most NPC kills
#define STAT_PLAYER_MOST_NPC_DEATHS				1201	// IPvP: Player with most non-PVP deaths
#define STAT_PLAYER_MOST_PVP_KILLS				1202	// IPvP: Player with most PvP kills
#define STAT_PLAYER_MOST_PVP_DEATHS				1203	// IPvP: Player with most PvP deaths
#define STAT_PLAYER_MOST_ARENA_KILLS			1204	// IPvP: Player with most Arena kills
#define STAT_PLAYER_MOST_STATUS					1205	// IPvP: Player with most Status
#define STAT_PLAYER_MOST_WEALTH					1206	// IPvP: Player with most Wealth

// HIGHEST stats for players
#define STAT_PLAYER_HIGHEST_NPC_KVD_RATIO		1300	// IPvP: Player with highest NPC kill-versus-death ratio
#define STAT_PLAYER_HIGHEST_PVP_KILL_STREAK		1301	// IPvP: Player with longest PvP kill streak
#define STAT_PLAYER_HIGHEST_PVP_DEATH_STREAK	1302	// IPvP: Player with longest PvP death streak
#define STAT_PLAYER_HIGHEST_PVP_KVD_RATIO		1303	// IPvP: Player with highest PvP kill-versus-death ratio
#define STAT_PLAYER_HIGHEST_HP					1304	// IPvP: Player with highest HP on server
#define STAT_PLAYER_HIGHEST_POWER				1305	// IPvP: Player with highest Power on server
#define STAT_PLAYER_HIGHEST_RESISTS				1306	// IPvP: Player with highest Resists on server

may still remain server stats, calculated from player stats and stored in `statistics` - but right now I think I only use them on my web page for EQ2TC.

So, do not worry about Server Stats stuff. But anything PlayerStats you see, could be destined for this new system, as I'd like ONE place for all Player history to exist. Another place for Guild history, if there is such a thing (outside guild_events). What I need from you is how World will handle the data - ie., the player carrying around their stats, updating it in memory, then getting it to the DB. Together, we'll come up with a DB table that makes most sense, based on the example above.

No rush, just try not letting it slip into oblivion. I've put this one off long enough, and would like to tackle it in the next cycle.

Edit: You may find some barely thought out structs scattered around where I tried to start Player stats - usually noted by my initials. They can be removed and rethought.

Posted: **Wed Apr 24, 2013 6:53 pm**

Discovered locations should now work if set up properly. To set them up you need to create a location (/location in game) and then in the db manually set the discovery field in locations table, remember to /reload locations after you create and set the field. You will also have to set the "EnablePOIDiscovery" rule to 1. That is all, server will now keep track of discovered location. Code is on Dev SVN

Posted: **Mon May 06, 2013 5:43 pm**

Added a "value2" field to the table and made both value and value2 int32's. The extra value field was going to be needed for stuff like kills where we keep track of the spawn type (undead for example) and the number we have killed. As a bonus the extra value lets me save the zone id for discovered locations so the list we keep in memory will now only contain locations in the same zone as the player.

I have also changed the way I was doing locations to a more generic history function, this function will update the required lists that need to be maintained, currently only the location list, as well as save the new history into the db.

Code: Select all

void UpdatePlayerHistory(int8 type, int8 subtype, int32 value, int32 value2 = 0);

Type and sub type have HISTORY_TYPE_ and HISTORY_SUBTYPE_ defines in player.h. Using locations as an example this is all you need to call to add or update a new history event.

Code: Select all

player->UpdatePlayerHistory(HISTORY_TYPE_DISCOVERY, HISTORY_SUBTYPE_LOCATION, GetZoneID(), grid->id);

As you can see the type is discovery, subtype is location, value in this case is the zone id and value2 is the location id.

Code is on Dev SVN

Posted: **Wed May 08, 2013 6:24 pm**

Jab if I proprely remember SOE keep the timestamp / zone when you level up. It was in T5, removed after and see it back again in account management some times ago.
It also keep count of your death / kill (you can see ration in persona windows in SOE Player) and separe NPC and Player (PvP) kill / death.
Also the command /played return you the time of your session and overall time of personnage / account.
Hope it can be helpfull for you

Posted: **Sat May 16, 2015 6:12 pm**

I was thinking about how to do one time events recently, like a quest that doesn't show up in the journal (Templar epic) or an npc that has unique dialog the first time it is hailed by the player. I figured character history exposed to lua would be the best way to do that stuff, so I went looking into this again and not sure what I was thinking when I added to this, so I changed it to be more generic and not going to require hundreds of extra functions for every thing needed to be tracked.

First change was I made id/type/subtype/value a unique key in the DB.
Secondly all history will be loaded into a map (map<int8, map<int8, vector<HistoryData*> > >) this will keep all history in one spot instead of dozens of maps all over the place.

I also have location filled with the zone name and the date to the current unix timestamp when a new event is added.

Future Plans:

I plan to extend it into other areas so more then locations are saved.
Expose history to LUA.
Probably going to make a special type for LUA so only that category can be effected by LUA and not everything.

Now what I need is a list of what needs to be tracked, I know stuff like level, ts level, items crafted but what else, I would like to compile a list that includes those I mentioned and preferably what type/subtype they belong to.

Also will we need more then the types (None, Death, Discovery, XP) and subtypes (None, Adventure, Tradeskill, Quest, AA, Item, Location)?

Implementing: Character History

Implementing: Character History

Re: Implementing: Player History

Re: Implementing: Player History

Re: Implementing: Player History

Re: Implementing: Player History

Re: Implementing: Player History

Re: Implementing: Character History