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.

H2 SQL Grammar

H2 built-in functions

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

Poker Copilot Update 2.12 is Here

“The best Poker Copilot of all time.” – Steve McLeod

You can download Poker Copilot 2.12 here

The slowness in loading hand histories may still occur sometimes. Quit Poker Copilot and start it again, and it should speed up.

I’m not going to release this version officially yet. There’s been a load of changes. So many, that I’m feeling a little uncertain. I’m hoping that the more enthusiastic Poker Copilot users will give this a go. I’m grateful for anyone who does.

Note for 2.09 users: the upgrade process isn’t available. You’ll only have the option to delete the current database and reload all hands.

Here’s what’s new:

  • More filters: buy-in for tournaments and stake level for ring games
  • Faster retrieval of data
  • Database Console
  • Work-around for more PokerStars character encoding problems
  • Fixed the suit mix-up in the hand replayer
  • Auto-detection mode, where hand history folders are detected whenever you start Poker Copilot. This is disabled by default for existing users, enabled for new users
  • Work-around for Full Tilt language mix-up. (Full Tilt sometimes writes some non-English text into english tournament summaries)
  • Work-arounds for some Snow Leopard issues. To be precise, these are Java 6 issues, which Poker Copilot uses on Snow Leopard because Apple removed Java 5.
  • Several small issues fixed

When Systems Go Wrong; an Unprogress Bar; Where’s the Next Update?

When Systems Go Wrong

My support system: try to send a helpful reply within 24 hours.

Problem case #1: The recipient’s spam filter eats my message. So he writes again. I answer again, this time writing in simpler terms, assuming he didn’t understand my last message. He writes again, now getting shirty because it seems I’m ignoring his e-mails.

Problem case #2: The recipient’s inbox is full and bouncing messages. Some people have tight e-mail inbox quotas. If this is you, please sign up with Google Mail. It’s free. It’s good. It has huge quotas, currently 7 gigabytes.

These things – and many others – thwart my support system. I don’t really have a good way to handle these. The first case I can solve by trying to send from my personal e-mail address, which I don’t really like doing. The second case is tough.

An Unprogress Bar

When is a progress bar not a progress bar? When it doesn’t progress. This happened last week to all of Poker Copilot’s progress bars, progress circles, progress indicators. It took me the best part of two frustrating days to solve the problem. It was evidence of a threading problem, so it needed to be solved. The culprit? Me, of course. A combination of changing a non-modal window into a modal window and not disposing of resources.

Where’s the Next Poker Copilot Update?

It’s more or less ready to go, except I’m not happy with the performance of loading 300K hands or more. I added lots of database indexes to make querying as fast as I can. This has led to slower hand history loading. So I’m investigating anything I can do to improve this. The time trials for this are very time-consuming. When I make one tiny tentative tweak, it takes an hour to find out whether hand loading is slower or faster after the 300K point.

Maybe I need to follow the golden rule in database work: users will tolerate slow database writes and fast database reads more than they will tolerate fast writes and slow reads.

Ideas, Ideas, Ideas

Since I started using Get Satisfaction with Poker Copilot to track ideas, problems, and questions, I’ve had 147 user-suggested ideas.

By coincidence, it is a year ago today since I started using Get Satisfaction. So that’s almost 3 ideas per week. Poker Copilot is a one-person operation. Which means, in technical terms, I have ideas coming out of my ears.

If you sometimes feel that it takes me a long time to implement your super-groovy, must-have, highly-voted-up idea, this should give you an idea why!

Nevertheless, keep the ideas coming. It’s your ideas that make Poker Copilot the product it is.

A Surprise New Feature in Poker Copilot

A surprise for me, that is!

Poker Copilot uses H2, an open-source embedded SQL database engine. I follow the H2 discussion forum daily, to ensure I am informed of new developments and possible problems. Today I was very surprised to read that H2 has a built-in SQL console, which I could add to Poker Copilot with one line of code (plus a few supporting

	org.h2.tools.Server.startWebServer(connection);

Immediately an SQL console opens in your browser.

Screen shot 2009-09-18 at 8.23.54 PM.png

I knew this console existed, as I often use it. I didn’t realise that you could embed it so easily.

These days commercial software builds upon numerous third-party products. Perhaps it was always that way. These third-party components are usually cheap and often open source and therefore free. Today you can add astounding power to an application easily.

The bad side of using third-party products: you inherit the bugs of each product. The good side: you inherit the hidden features of each component.

You’ll find this in the next update of Poker Copilot, under the Tools menu as “Database Console (Advanced)”.

From the "You Wish it Wasn’t a Bug" Department

Loyal Poker Copilot customer Alex reported what he believes – but hopes not – to be a bug:

whowantstobeamillionaire.png

Check out that take for the first “Satellite to the Daily Dollar” tournament.

Unfortunately for Alex, it is indeed a bug. It seems Full Tilt posted a bit of French into his tournament summary file. Poker Copilot, being mostly monolingual, choked on the French and decided it was the win of a lifetime.

MarsEdit Makes Blogging Saner

On my 1920 x 1200 iMac, blogger.com gives me a field measuring 700 pixels by 250 pixels. All that crisp, clear, Apple monitor, and I can only use 11% of it for writing blog posts.

Screen shot 2009-09-16 at 2.38.35 PM.png

This and other blogger.com shortcomings have long frustrated me. However it is free, reliable, and does have some pretty nifty features, so I don’t want this to seem like ungrateful complaining. It’s just that the blogger.com UI hasn’t advanced much in the last few years.

About a year ago I tried out every third-party Mac desktop client I could find for blogger.com. None of them impressed me enough to spend money, so I went back to the web interface.

A year is a long time in the software world, so I searched again for a desktop client for posting to blogger.com. I found what I wanted. It’s called MarsEdit. I’m using it right now. I’ve been trialling it for the last couple of weeks and I am sold.

Major pluses for MarsEdit:

  • General editing is much nicer
  • Working with image placement is easier
  • Real-time preview as I type
  • It seems to be under active development with a vibrant user community

Minuses for MarsEdit:

  • You have to manually enter HTML tags to get bullet point lists
  • Resizing images is a chore

Screen shot 2009-09-16 at 2.56.41 PM.png

Poker Copilot Update Available, but…

…this is not an official release.

Screen shot 2009-09-15 at 2.52.28 PM.png
Screen shot 2009-09-15 at 2.54.15 PM.png

I’m still trying to make the database auto-upgrade process work smoothly so I’ve omitted it from this update. This update simply obliterates your existing database. Your hand history files will be reloaded.

If you don’t mind that your database will be reset and you are keen to try the lots-a-filters goodness of this update, you can download Poker Copilot 2.09 here.

Because of the changes I had to make to the database structure, I spent much time rewriting and optimising all SQL queries. Also, this is first release I built under Snow Leopard. All these changes make me a little uncertain. So if you find any errors or instability please let me know.

Other changes:

  • auto-refresh is now on again, but only when all your hand histories are fully (or almost fully) loaded
  • Winamax support works for people running their Macs in French
  • The PokerStars tournament results importing now works with really big tournaments (25,000 players, for example)
  • Some small bug fixes and performance improvements

If you try this update and encounter problems, you can get Poker Copilot 2.08, the previous update, here.