Player Profiles

Ok here's the pitch. Wouldn't it be cool if you went to an address like these
http://mc.shadecrest.com/MCStats/profile.php?player=CyberVic
http://mc.shadecrest.com/MCStats/profile.php?player=Jesho5
http://mc.shadecrest.com/MCStats/profile.php?player=Dynodamon
http://mc.shadecrest.com/MCStats/profile.php?player=ndvenckus1
http://mc.shadecrest.com/MCStats/profile.php?player=Mega_Spud
http://mc.shadecrest.com/MCStats/profile.php?player=the_reminator98
http://mc.shadecrest.com/MCStats/profile.php?player=SkittlesQueen
http://mc.shadecrest.com/MCStats/profile.php?player=THEdeadRETURNED
http://mc.shadecrest.com/MCStats/profile.php?player=majestic_moose

just change the last part of the URL to be your player name

Let me know what kind of cool stats you would like to see. Basically this kind of page would just be a glorified query of our database showing the player's stats in real time. This would be very similar to the pages I re-wrote for the ChestShop Averages page in the sense that these would be very efficient queries against MySQL and would not cause performance problems.

The only limitation to this stats page is that I would limit it to just what I could query from MySQL. If it's anything that requires me to read files off of the server, it's not worth doing. McMMO, IConomy, ChestShop, LWC, Home, Towny, Vote, and Prism are all in the database so that's a LOT of stuff I could be querying in real time about any given player.

TODO:

• General
  • Milliseconds / Seconds to time formatting
  • Miliseconds / Seconds to date formatting
  • Better currency formatting
    
  • Better non-currency numeric formatting
  • Aliasing world names
  • Pretty up the CSS tags
  • Add more layout / design instead of just a list
• Ontime-Players
  • First / last seen stats
  • Ontime stats
  • Votes
• Home
• Towny
  • Town
  • Town Rank
  • Nation
• Warps
  • List of warps by world and private/public
  • Filter to only player worlds (ex. 'peaceful_new', 'pvp_new', 'mining', 'world_nether', 'world_the_end')
• Protections
  • List of protections by world
  • Filter to only player worlds (ex. 'peaceful_new', 'pvp_new', 'mining', 'world_nether', 'world_the_end')
    
  • Possibly break down the projections by protection type (ex. chests, hoppers, etc)
• iConomy Balance
• Transaction stats
  • Most sold item (your shops, other's shops)
  • Most purchased item (your shops, other's shops)
  • Total sales (your shops, other's shops)
  • Total purchases (your shops, other's shops)
  • Profits from shops
• McMMO Stats
  • Total Levels
  • Levels per skills
• Prism stats
  • Kills (total / last month)
  • Deaths (total / last month)
    
  • Monsters killed (total / last month)
  • Most killed monster
  • Players killed (total / last month)
  • Most killed player
  • Ore mined (total / last month)
  • Wood harvested (total / last month)
  • Potions crafted (total / last month)
  • Fish caught (total / last month)

 

this sounds amazing cyber

 

Not a bad idea, wouldn't mind seeing how many monsters killed

follow the buzzards

 

Yeah working on those prism stats next Skittles.
Here's your link:
http://mc.shadecrest.com/MCStats/profile.php?player=SkittlesQueen

Unfortunately it's not going to be perfect stats, it will only be as accurate as Prism which is only as accurate as data since we started using Prism.

 

How cool

follow the buzzards

 

Prism stats seems to be working, but it seems to be VERY VERY slow to respond. The prism.prism_data_extra table seems to be poorly designed. The data column is a TEXT field instead of a VARCHAR(somelarge number) and as a result there is no index on the data field. This means when I query that table instead of it being fast due to the index, it takes a LONG time to come out with result set because it has to seek into the table.

Also there seems to be an asynchronous methodology between actions taken in game and it being written to the database. Semi-depressing though, I hope I can get it to work because that's where the most power is available in this player stat page system.

For now I'm testing the performance differences via this:
http://mc.shadecrest.com/MCStats/profile.php?player=CyberVic
http://mc.shadecrest.com/MCStats/profile.php?player=CyberVic&beta=true

I do find the fact that Rem's highest cause of death is from falling:
http://mc.shadecrest.com/MCStats/profile.php?player=the_reminator98&beta=true

 

Seems like a neato way to keep a track of how far behind I'm falling behind everyone else

As an additional line, might it be worth putting creative rank in there as well? Just a bit of trivia for anyone browsing profiles.

 

I like the prism results but it is a lot slower isn't it. Apparently I've killed 4,852 slime and I'm mostly killed by zombies. Would it be much slower to get a breakdown of all the ways you've died?

 

Id love too see both creative and overall if possible played and such.

Skickat från min GT-I9505 via Tapatalk

 

Prism clears results older than 8 weeks, so it's not a terribly reliable system for statistics, but I guess it's all we've got. Not clearing results would put an unnecessary strain on the server, especially with the way prism sets up its tables, so that's not an option either. It might make sense to create a new database/new tables to use for "prism" statistics (statistics you can only obtain from prism), so instead of having one row for each individual action a player makes, we have a row for each type of action from each player and a column with a counter. Every time I kill a zombie, you would

Then to get my total zombie kills, you just

You wouldn't be able to keep track of specific times, but you could have a month time stamp, so you could still make queries from the last month, year, etc.

Using this solution, we could quickly query any stat we want and never have to delete them. I could work with you on developing a plugin for it.

 

Poop. I didn't realize that Prism was self destructive like that. Yes we will have to then once a month, at the end of the month probably execute a series of queries which populates a monthly reports table of some kind. I'll work on the dataschema for what that database would look like. One advantage of the plugin keeping track of things is that it records data in real time. I could write some queries which initially populate the records based off the last 8 weeks, then from there the plugin could continue to maintain the counts.

Legend9468, yeah things like staff or creative rank are nice to haves. I will put that on the to-do list. There are other things I have yet to show on the list like LWC protections on Creative, Warps on Creative, maybe Plots on Creative. Would be nice if the creative folks helped me come up with a full to-do list just for the creative stats.
TehRawB, I'm limited to what I have available to me. Do we run Ontime for Creative?

 

I'd be quite interested in learning just how many fish I have caught.

Sent from the new and improved space phone.

 

It's still a seek either way and I'm doing a GROUP BY unique / distinct data. The problem with prism as a whole is the data column being poorly modeled as a TEXT field instead of VARCHAR so it can't be indexed. Maybe I can see if I can turn on the MySQL equivalent of full text indexing and change how I'm querying it. If so... that could dramatically speed things up because then text fields would get indexed automatically.

Either way we're still then limited by the fact that prism destroys data over 8 weeks old (which is lame, didn't know it had that limitation). I think some kind of player stats tracking plugin is necessary and the initial data could be seeded with SQL queries off of prism. They'll run slow, but just need to run once and pre-populate this tracking plugin's database. Unfortunately this does require us to brainstorm on all the different kind of things we want to track and get it all down upfront unless we get more clever and store the activity totals as if it where stored in prism, but merely counters instead of individual rows, then as each actions/data happens it updates the appropriate action/data row. This plugin Ndv was suggesting to write would essentially be a prism event listener which would capture events like prism does on the fly and take what prism would store as an individual row but instead increment a value for a player's action's list.

Quick brainstorm of what the tables would look like
prism_uniqueactions - UniqueActionId, ActionId, Data
prism_monthly_uniqueactions - MonthlyUniqueActionId, PlayerId, WorldId, UniqueActionId, Month, Year, Count

Example Data:
prism_worlds (existing table in prism)
world_id, world
2, Nether
3, PvP_New

prism_players (existing table in prism)
player_id, player
622, CyberVic

prism_actions (existing table in prism)
action_id, action
38, Player-Death
45, Player-Kill

prism_uniqueactions (based on possible actions and the important unique data, not all metadata)
unique_action_id, action_id, data
1, 45, "skeleton"
2, 45, "zobmbie"
3, 45, "wither"
4, 38, "fall"
5, 38, "skeleton"
6, 38, "lava"

prism_uniqueaction_totals
unique_action_total_id, player_id, world_id, unique_action_id, month, year, total
1, 622, 3, 1, 6, 2014, 5
2, 622, 3, 2, 6, 2014, 10
3, 622, 2, 3, 6, 2014, 1

As a proof of concept I could create these two tables, using a single sql query I could create all the rows for prism_uniqueactions, then I could build the prism_uniqueaction_totals based on the last 2 months worth of data. From there we could built the statistics page to query from prism_uniqueaction_totals and it should in theory be VERY fast much like the performance of the rest of the queries. From there I can work with Ndv and whomever on the plugin which in real time keeps the prism_uniqueaction_totals up to date.

 

I got a big event tonight I'm going to, so won't be able to work on this tonight. Possibly tomorrow. Think on this ndvenckus1 and let me know if you agree with the table design. I'll write a query to give a complete list of unique actions, we can decide whether that's good enough or if we want to cut down the uniqueness more. I think unless we do a lot of crazy parsing maybe a complete list of unique actions is better and will still be just as performant with the right indexes on the tables.

 

Ok so.... getting the full unique set of actions is insane, MySQL says "NO WAY" to that approach.
BUT, if we filter down the kinds of actions we care about Ex. player-kill, player-death, block-break, etc... then we can pre-filter the list to a much more reasonable subset and then generate a pretty decent set of unique actions. We are going to have to maintain the unique actions table as new actions are created in the system, but again this is just for the purpose of getting things "caught up" to where it is today and make it easier for our little plugin to keep up after today.

I've got a draft query to at least get a count of what these unqiue actions we'd want to track would be but I gotta go and will have to continue this another time.
SELECT COUNT(a.action_id + '|' + de.data )
FROM prism_survival.prism_data_extra de
INNER JOIN prism_survival.prism_data d ON d.id = de.data_id
INNER JOIN prism_survival.prism_actions a ON a.action_id = d.action_id
WHERE a.action_id IN (1, 7, 10, 12, 13, 14, 15, 19, 37, 39, 51, 53, 56, 75 )

Ok those not used to databases, this might sound like a scary number but here goes, the count of unique actions, now keep in mind, these aren't unique instances of these actions, but rather named actions with related metadata: 1,663,719 rows. Now the nice thing about this is that it's a subset of the 20,043,991 data_extra rows that exist (just getting the unique actions we care about). If we get more picky about what kind of actions we want to keep track of then the list gets even smaller. That took 38 seconds to query. Of course we could filter it down even further to filter out some of the metadata we don't care about. For example only track block-break on ores, but don't care about stupid stuff like breaking signs or crafted items.

Either way, this table just needs to be built once, then once we have that set of unique actions, as more unique actions take place in real time / asynchronously, we add rows to that table, and then keeping track of how many times a user does one of those things so it is easy and fast to query back.

 

Of these major actions, what do you guys think is worth tracking?

I think that 1, 7, 10, 11, 12, 13, 15, 19, 34, 51, 53, 56, 75 are worth tracking.

1block-break
2block-burn
3block-dispense
4block-fade
5block-fall
6block-form
7block-place
8block-shift
9block-spread
10block-use
11bonemeal-use
12bucket-fill
13cake-eat
14container-access
15craft-item
16creeper-explode
17crop-trample
18dragon-eat
19enchant-item
20enderman-pickup
21enderman-place
22entity-break
23entity-dye
24entity-explode
25entity-follow
26entity-form
27entity-kill
28entity-leash
29entity-shear
30entity-spawn
31entity-unleash
32fire-spread
33fireball
34firework-launch
35hangingitem-break
36hangingitem-place
37item-drop
38item-insert
39item-pickup
40item-remove
76item-rotate
41lava-break
42lava-bucket
43lava-flow
44lava-ignite
45leaf-decay
46lighter
47lightning
48mushroom-grow
49player-chat
50player-command
51player-death
52player-join
53player-kill
54player-quit
55player-teleport
56potion-splash
57prism-drain
58prism-extinguish
59prism-process
60prism-rollback
61sheep-eat
62sign-change
63spawnegg-use
64tnt-explode
65tnt-prime
66tree-grow
67vehicle-break
68vehicle-enter
69vehicle-exit
70vehicle-place
71water-break
72water-bucket
73water-flow
74world-edit
75xp-pickup

 

Looks like this query returns 568 unique actions that we give a crap about tracking:


-- block-break, block-place, block-use
SELECT DISTINCT action_id, block_id data
FROM prism_survival.prism_data
WHERE action_id IN (1, 7, 10) AND block_id != 0
UNION ALL
-- bucket-fill, cake-eat, craft-item, enchant-item, xp-pickup
SELECT DISTINCT action_id, block_id data
FROM prism_survival.prism_data
WHERE action_id IN (12, 13, 15, 19, 75 ) AND block_id != 0
UNION ALL
-- 51 = player-death
SELECT DISTINCT d.action_id, de.data
FROM prism_survival.prism_data_extra de
INNER JOIN prism_survival.prism_data d ON d.id = de.data_id
WHERE d.action_id = 51
UNION ALL
-- 53 = player-kill
SELECT DISTINCT d.action_id, LEFT(de.data,40) data
FROM prism_survival.prism_data_extra de
INNER JOIN prism_survival.prism_data d ON d.id = de.data_id
WHERE d.action_id = 53
AND de.data NOT LIKE '%custom_name%'
AND de.data NOT LIKE '%isAdult":false%'
UNION ALL
-- 56 = potion-splash
SELECT DISTINCT d.action_id, de.data
FROM prism_survival.prism_data_extra de
INNER JOIN prism_survival.prism_data d ON d.id = de.data_id
WHERE d.action_id = 56
ORDER BY action_id, data;

 

This is really cool. Though my first login time is around 2 years off :/

 

Currently it's reading from OnTime, so your "first login" can't be any earlier than when we got the plugin. We'll be importing FirstLastSeen data relatively soon so it will be accurate.

 

Okay, I wasn't sure if that was the case since others had correct times (and before we got the plugin). Thanks!