PokerTracker Data Schema
This will probably confuse a lot of my readers, but I’ve been looking at this a lot lately…
You can also get a good overview by looking at this image (it’s big).
Basically, PokerTracker has to take a bunch of text and organize it into variables, which can then be queried to look at compiled data. That’s the parsing process. But then it needs an organized way to store those values and that’s where this schema comes in. I count 25 tables that it uses to store the data, which is about the same number that we use over at thepokerdb. But we have duplicate tables for each site, a user table, a session table, etc. In terms of storing actual tournament results, we only need a few tables. Storing hand histories results is a lot more complicated.
If you actually go into PokerTracker and double-click on any column header, it will take you to the “SQL Debugger” where you can view the query being used for that column.
Here is the query under the “General Info” tab double-clicking on the hand listing columns for player H@££INGGOL in Stars $10/$20 NL:
SELECT hole_cards as starting_hand,
game_players.card_order1 as card_order1,
game_players.card_order2 as card_order2,
game_players.card_order3 as card_order3,
sum(game_players.won_hand) as won_hand,
sum( CASE WHEN game_players.won_hand = 1 THEN game_players.saw_flop_n ELSE 0 END ) as won_wsf,
sum((total_won – total_bet)) as amt_won,
sum(game_players.big_blind_n) as dealt_bb,
sum(game_players.small_blind_n) as dealt_sb,
sum(game_players.saw_flop_n) as saw_flop ,
sum(game_players.pre_flop_raise_n) as raised_pf,
sum(game_players.raised_first_pf) as first_to_raise,
game_players.pair_hand as pair_hand,
game_players.connector_hand as connector_hand,
sum(game_players.vol_put_money_in_pot) as vol_saw_flop,
sum(game_players.limp_with_prev_callers) as limped_in,
sum(game_players.went_to_showdown_n) as went_to_sd,
sum( CASE WHEN game_players.went_to_showdown_n = 1 THEN game_players.won_hand ELSE 0 END ) as won_sd ,
count(*) as times_dealt,
sum(cold_call_pf) as times_cc,
sum((total_won – total_bet) / (game_level_big_bet + (game_level_big_bet * pl_nl))) as bb_won
FROM game, game_players, game_level
WHERE ( game.game_id = game_players.game_id ) and
( game_players.player_id = 1048 ) AND
( game_players.hole_card_1 is not NULL ) AND
( game.game_level_id = game_level.game_level_id )
AND ( game.game_level_id IN (6) ) AND ( game.site_id IN (2) ) GROUP BY game_players.hole_cards, game_players.card_order1, game_players.card_order2,
game_players.card_order3, game_players.pair_hand, game_players.connector_hand
Some basic SQL commands are used here, such as SELECT, WHERE, AND, GROUP BY, SUM, etc. (it’s conventional to put the SQL in caps and variables in lower case, but that’s not always the case)
A really basic SQL line might look like this: SELECT player_id FROM table_id WHERE name = ‘N 82 50 24′
That’s basically saying get the “player id” associated with the name “N 82 50 24″ from the “table_id” table. Obviously the PokerTracker SQL is a lot more detailed and is SELECTing a lot more information, but the basic concept is the same.
Anyway, I find this stuff interesting and I want to explore ways to make hand history data schemas more advanced and capable of more detailed information extraction from parsed hand histories. This schema might be as good as it gets and capable of just about anything, but I want to find out if that’s the case.
Related posts:
- Start of thepokerdb
- thepokerdb v3.0: HH paster, tabs, filters and player linking!
- Main event continues, some more pictures
- Recap of last couple days
- thepokerdb being down the past 2+ days
If you enjoyed this post, please consider leaving a comment or subscribing to the feed to get future posts delivered to your feed reader.
Comments
No comments yet.
Sorry, the comment form is closed at this time.