If you are comfortable using SQL, here's some helpful info.
CaveatYou should only perform select queries. If you perform updates, inserts, or deletes, you may upset Poker Copilot.
OverviewPoker Copilot's database schema is heavily de-normalised. It follows loosely the concept of a data warehouse, optimised for ad-hoc queries that "roll up" data. Queries will seldom join more than two tables together. You'll mostly use one table with redundant data, the so-called "Fact Table" in data warehouse terminology.
The main fact table is playersummary. This contains data grouped by day, as well as several other key criteria: playerid, isplaymoney, tablesize, gametypeid, positionid, stakelevelid, and tournamentid. You should use this table most of the time.
There is a secondary fact table called gameplayersummary. This contains ungrouped data, one row per player per hand. Use this if you need a finer granularity than day.
Instead of date/time/timestamp fields, Poker Copilot often uses a integer field called dayid. This allows much faster querying than with regular date types.
Guidelines for fast queriesInclude in your where clause at a minimum dayid, ishero, istournament, and isplaymoney. This will cause the database to use a good index. For example,
Example queriesFetch the total times you've played real money ring games
where dayid>0 and
Fetch your vpip percentange in real money tournaments since September 1st, 2009
where dayid >= 14488
Fetch your vpip percentange with decimal points in real money tournaments since September 1st, 2009
where dayid >= 14488
Now something complicated: vpip and pfr on full tilt 6-max no-limit hold'em cash tables while on the button on days in 2009 when you were on the button at last 50 times
select year, month, day,
sum(vpip)*100/sum(timesseen) as vpip,
sum(preflopraised)*100/sum(timesseen) as pfr
natural join day
where year = 2009
and positionid=3 -- button
and casinoid=1 -- Full Tilt
and gametypeid=3 -- no limit hold'em
group by year, month, day
having sum(timesseen) > 50
order by year desc, month desc, day desc
More infoPoker Copilot uses the H2 embedded database engine.
H2 SQL Grammar
H2 built-in functions
Some helpful tablesThese tables are pre-populated. The ids are the same for every Poker Copilot user.
select * from casino;
1 Full Tilt Poker
3 Ongame Network
select * from gametype;
1 Limit Texas Hold'em
2 Pot Limit Texas Hold'em
3 No Limit Texas Hold'em
4 Cap No Limit Texas Hold'em
select * from position;
1 Big Blind
2 Small Blind
5 Cut-off - 1
6 Cut-off - 2
7 Cut-off - 3
8 Cut-off - 4
9 Cut-off - 5
10 Cut-off - 6
11 Cut-off - 7
select * from card;