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.
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.
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.
Since I started usingGet 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.
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.
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)”.
Loyal Poker Copilot customer Alex reported what he believes – but hopes not – to be a bug:
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.
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.
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
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)
