Using Poker Copilot’s Database Console
If you are comfortable using SQL, here’s some helpful info.
Caveat
You should only perform select queries. If you perform updates, inserts, or deletes, you may upset Poker Copilot.
Overview
Poker 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 queries
Include in your where clause at a minimum dayid, ishero, istournament, and isplaymoney. This will cause the database to use a good index. For example,
select sum(timesseen)
from playersummary
where dayid>0
and ishero=true
and istournament=false
and isplaymoney=false
Example queries
Fetch the total times you’ve played real money ring games
select sum(timesseen)
from playersummary
where dayid>0 and
ishero=true and
istournament=false and
isplaymoney=false
Fetch your vpip percentange in real money tournaments since September 1st, 2009
select sum(vpip)*100/sum(timesseen)
from playersummary
where dayid >= 14488
and ishero=true
and istournament=true
and isplaymoney=false
Fetch your vpip percentange with decimal points in real money tournaments since September 1st, 2009
select sum(vpip)*100.0/sum(timesseen)
from playersummary
where dayid >= 14488
and ishero=true
and istournament=true
and isplaymoney=false
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
from playersummary
natural join day
where year = 2009
and ishero=true
and istournament=false
and isplaymoney=false
and positionid=3 -- button
and casinoid=1 -- Full Tilt
and gametypeid=3 -- no limit hold'em
and tableSize=6
group by year, month, day
having sum(timesseen) > 50
order by year desc, month desc, day desc
More info
Poker Copilot uses the H2 embedded database engine.
Some helpful tables
These tables are pre-populated. The ids are the same for every Poker Copilot user.
select * from casino;
CASINOID NAME
1 Full Tilt Poker
2 PokerStars
3 Ongame Network
select * from gametype;
GAMETYPEID DESCRIPTION
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;
POSITIONID NAME
0 Unknown
1 Big Blind
2 Small Blind
3 Button
4 Cut-off
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;
CARDID DESCRIPTION
1 Ah
2 Kh
3 Qh
4 Jh
5 Th
6 9h
7 8h
8 7h
9 6h
10 5h
11 4h
12 3h
13 2h
14 Ad
15 Kd
16 Qd
17 Jd
18 Td
19 9d
20 8d
21 7d
22 6d
23 5d
24 4d
25 3d
26 2d
27 As
28 Ks
29 Qs
30 Js
31 Ts
32 9s
33 8s
34 7s
35 6s
36 5s
37 4s
38 3s
39 2s
40 Ac
41 Kc
42 Qc
43 Jc
44 Tc
45 9c
46 8c
47 7c
48 6c
49 5c
50 4c
51 3c
52 2c