SNGs - Constructing real opponent ranges

Constructing real opponent ranges #1 - Background


This is going to be a series of articles that look at constructing real shoving and calling ranges for our opponents. In the first article I'm going to start laying some groundwork. Later articles will cover more SQL code, custom stats and reports. I hope this series will improve your game and help you get the best out of WIZ.

I think one of the most complicated things to do in SNGs coming to grips with pushing and shoving ranges. Everyone tells you not to use the default ranges in WIZ, but how do we know what ranges we should be using? I've played thousands of SNGs and, while I'm better at approximating ranges than when I first began, I still don't have an entirely clear picture of these ranges. I'm sure I'm not alone in this boat and I'm sure there are load of people that are interested in doing something exactly something like this.

Armed with my quest of finding ranges, I fired up Hold em Manager (HEM) with the intention of getting these ranges. I quickly learned that while HEM is quite good for analysing your own play, it's almost entirely useless for constructing your opponents ranges. HEM was simply never intended to be used in this way, so there are no useful reports, stats or methods for building these ranges. A lot of what I'm going to show you here is information I have found and tweaked for myself. I don't guarantee it will work for everyone, nor is it going to be error free.

Disclaimer

Right from the outset I'm going to say that this isn't for the feint-hearted. I'm going to show you code that you will have to run yourself. This isn't quick or easy to do and involves some work on your part. While none of what I'm going to show you should damage your database, there is a possibility that it could happen. I don't accept any responsibility if you break anything. Also this guide applies to HEM, but this method could work for other databases that use PostgreSQL.

Why can't I just ask HEM for the ranges?

The first annoying thing you are going to come across in HEM is that it doesn't allow you to work with groups of your opponents. While you can run a fantastic array of stats on yourself, you can't run them on a group of other players …. or can you? There are work-arounds to achieve this like this awesome blog article that I stumbled across Learn Good Them SNGs #2 - Hand Ranges | SickRead.com . I was impressed by the level of ingenuity in the article and applied the same method to my database, but I was left craving more when I didn't have the fine level control I wanted. I wanted better control over what constituted a shove and the analysis to be cleaner when more than 2 players are in the hand.

Aliases

The answer lies in aliases in HEM. This allows you to group together players as one entity, in the same way as you group your screen names into an alias so you can analyse your play across poker sites. This is going to allow us to group together all our villains together and analyse them as an average player … happiness! Unfortunately HEM only allows you add player at a time to an alias. That's great if you have a bored grandmother who knows what a computer mouse is and not so great if you don't. Fortunately for you, I have a cunning plan. We are going to make an alias and automatically add players in the HEM backend program, PostgreSQL.

I first have to explain a bit of how HEM works. HEM is a gigantic database driven by a database management program called PostgreSQL. Some you may have heard of MySQL, that's Oracle's version of SQL, a database language. PostgreSQL is an open source version that basically does the same thing. Think of it as Open Office versus MS Office. Behind the pretty HEM Graphical User Interface (GUI) is PostgreSQL. Every time you run a report, results, look at hands, you are interacting with tables and columns in the PostgreSQL database. Every time you use a filter, it's actually writing a SQL query in the background and fetching the information. When you make a new alias in HEM, it basically does the same thing we are about to do without you having to run all the code manually.

In the next instalment of this article series (read – when I have time), we are going to make a new alias that you can work in the HEM GUI. I'll give you the code, some screen shots and a good dose of courage!

Superb!

you've really earned your biltong today, Nak!

:D

more please...

Quote:

---

Originally Posted by Nakamura

Disclaimer - While none of what I'm going to show you should damage your database, there is a possibility that it could happen...

---

So remember to back up your databases, kids!

but you're doing that already, right?

...right?

:facepalm:

Nice. Should be interesting to see what kind of data you can get your hands on

Good stuff. I really need to do more of this for the fiddy fiddys so I'm interested in seeing what you can get out of this. I'm using PT3 (PostgreSQL).

Quote:

---

Originally Posted by Nakamura

Some you may have heard of MySQL, that's Oracle's version of SQL, a database language. PostgreSQL is an open source version that basically does the same thing

---

MySQL is opensource too. I don't think it has any relationship with Oracle. Hey, I'm picking nits but if I can't do this for stuff that involves my regular day job, when can I?

Constructing real opponent ranges - #2 Aliases


A little house-keeping...

Welcome to part 2 of this series. In this part we will address aliases and how to manipulate them. Before we start messing around with the HEM backend, we are going to do a little house-keeping first. We are going to start with a backup of your HEM database. Please follow this guide FAQ - Hold'em Manager Poker Tracking Software :: Backup & Restore Database. Do not run the restore part of that article; that will only be run in the unlikely event that you break your HEM database. This step is really important as it will allow you to recover your database if you screw anything up, so please don't skip this step.

When you finish that part, you want to head back to pgAdmin III and we are going to fiddle with some of the settings in the Postgres config file. By altering the buffer and cache size, we are going to help it cope with the massive amount of information we are asking it to deal with. If you don't follow this step, HEM will likely have a hernia, fall over and run out of stack memory. The bigger your database, the more likely that is to happen.

From the HEM forums:

Quote:

---

Edit your PosgreSQL.conf file in the SQL 8.x/data folder.
Do not use the Tuning Wizard. On some systems it will make changes to the postgresql.conf it shouldn't, resulting in being unable to connect to the database!
If you experience this problem--> simply go to the 8.4/data folder, undo the changes you made and reboot.
Recommended changes:
shared_buffers = MB (set this to 20% of your system memory, but not more than 1000 MB.... round it off to 128 MB, 256 MB, 512 MB or 768 MB)
effective_cache_size = MB (set this to 2x the size of the shared_buffers)

---

Building your first alias in SQL

Having done all that, you are ready to jump into the fun bit! We are going to build a simple alias that should run on all systems as long as you have a reasonable number of HHs. This alias will only include hands we have played against regular players. Later on we will play around with including data from all players, but for now we will start with something simple.

Open up pgAdmin III again. Double-click on whatever version of postgreSQL you are using. Double-click on your database. It may ask for a password, by default the password will be “postgrespass”. Click the icon to run a SQL query. Highlight and delete everything in the SQL Editor box. Copy and paste the following code into the editor and click the execute query button (a green arrow). That will run the code and a blank box will appear in the output pane when it is finished.

Code by redlotus on HEM forums

Code:

---

CREATE OR REPLACE FUNCTION fn_alias_regs(alias_name text, min_hands integer)
  RETURNS void AS
$BODY$INSERT INTO players (site_id, playername, cashhands, tourneyhands, playertype_id)
  VALUES (-1, $1, 0,0,0);
 INSERT INTO aliases (aliasplayer_id, player_id)
  SELECT (SELECT MAX(player_id) FROM players), player_id FROM players
    WHERE tourneyhands>$2$BODY$
  LANGUAGE 'sql' VOLATILE
  COST 100;

SELECT fn_alias_regs('Tourney_Regs', 1000);

---

This example creates an alias called Tourney_Regs with all tournaments players on which you have 1000 hands. Note if you wanted cash players, you'd just change “WHERE tourneyhands>$2$BODY$” to “WHERE cashhands>$2$BODY$”.

What this means is that it will consider every hand (that meet the criteria) as if you played it and add it to the alias "Tourney_Regs". This allows us to work with our opponents data as if it is our own and run the same stats we would run on our data.

Clean up your alias

One final thing to do is to clean up the alias. The SQL query we just ran added your name to the alias too since HEM definitely has 1000 hands on you! We aren't really interested in our own stats as we are interested in how our opponents play, not how we play.

Open up HEM. Click on options ---> Player Aliases.... Click on the alias you just made and find all instances of your own screen name. Unfortunately player names are not stored in any particular order, so you just have to scroll through the list of player names. A helpful tip is that pushing the first letter of you player name will jump, in order, to all players starting with that letter. It speeds up the process significantly.

Now that you have an alias containing all players with more than 1000 hands, you can actually start to do some analysis on how regulars behave. With “Tourney_Regs” selected as the player name, run some reports and filters and see how often the average regular player is raising from particular spots. Just run with it and have a bit of fun.

The next article

There is no ETA for the next article as my migration to Windows 7 has cocked up the HEM import function. News from HEM support indicates that this is a known problem, but as we all know, HEM is devoting more time to other projects and there is no ETA on when a fix will be release. In my experience with HEM that means it's going to take quite a while. To run the next section I will need to have some hands in my database. When that happens we will look at building some ranges from all players, not just tourney regs as well as filtering down the selection criteria for adding players to the alias.

Quote:

---

Originally Posted by kevster

MySQL is opensource too. I don't think it has any relationship with Oracle. Hey, I'm picking nits but if I can't do this for stuff that involves my regular day job, when can I?

---

I never knew it was opensource, I always thought it was a commercial product. There is apparently an Enterprise edition which is a pay product and has better support etc., but the standard edition is free and opensource.

As for the history it appears somewhat complicated but it used to belong to Sun MicroSystems and then Oracle bought Sun MicroSystems a short while ago. From Wiki...

Quote:

---

Corporate backing history

In October 2005, Oracle Corporation acquired Innobase OY, the Finnish company that developed the third-party InnoDB storage engine that allows MySQL to provide such functionality as transactions and foreign keys. After the acquisition, an Oracle press release mentioned that the contracts that make the company's software available to MySQL AB would be due for renewal (and presumably renegotiation) some time in 2006.[37] During the MySQL Users Conference in April 2006, MySQL issued a press release that confirmed that MySQL and Innobase OY agreed to a "multi-year" extension of their licensing agreement.[38]
In February 2006, Oracle Corporation acquired Sleepycat Software,[39] makers of the Berkeley DB, a database engine providing the basis for another MySQL storage engine. This had little effect, as Berkeley DB was not widely used, and was deprecated (due to lack of use) in MySQL 5.1.12, a pre-GA release of MySQL 5.1 released in October 2006.[40]
In January 2008, Sun Microsystems bought MySQL for US$1 billion.[41]
In April 2009, Oracle Corporation entered into an agreement to purchase Sun Microsystems,[42] then owners of MySQL copyright and trademark. Sun's board of directors unanimously approved the deal, it was also approved by Sun's shareholders, and by the U.S. government on August 20, 2009.[43] On December 14, 2009, Oracle pledged to continue to enhance MySQL[44] as it had done for the previous four years. A movement against Oracle's acquisition of MySQL, to "Save MySQL"[45] from Oracle was started by one of the MySQL founders, Monty Widenius. The petition of 50,000+ developers and users called upon the European Commission to block approval of the acquisition. At the same time, several Free Software opinion leaders (including Eben Moglen, Pamela Jones of Groklaw, Jan Wildeboer and Carlo Piana, who also acted as co-counsel in the merger regulation procedure) advocated for the unconditional approval of the merger. As part of the negotiations with the European Commission, Oracle committed that MySQL server will continue to use the dual-licensing strategy long used by MySQL AB with commercial and GPL versions available until at least 2015. The Oracle acquisition was eventually unconditionally approved by the European Commission on January 21, 2010.[46] Meanwhile, Monty Widenius has released a GPL only fork, MariaDB. MariaDB is based on the same code base as MySQL server and strives to maintain compatibility with Oracle provided versions.[47]
[edit]

---

Hi Nak,

cool stuff as usual.

some of that SQL syntax is unfamiliar, but i'm just wondering if you could skip the manual clean-up bit by suppressing your player_id in the WHERE clause?

Quote:

---

Originally Posted by DoubleJ

some of that SQL syntax is unfamiliar, but i'm just wondering if you could skip the manual clean-up bit by suppressing your player_id in the WHERE clause?

---

I guess so, I'm not very familiar with SQL coding. I just borrowed the code and tweaked it until it worked for me. I decided it wasn't worth the hour it would take me to figure out how to exclude my player id in SQL; it's easy enough to do manually.

That said I guess this might work

Code:

---

WHERE tourneyhands>$2$BODY$ AND player_id != 'YourPlayerID'

---

I think Player_IDs are stored as integers in SQL and you would need to figure out what your ID is. There is a way but I'm too lazy to do a Google search right now...

the easy (i.e. non-dynamic) way would be to run a simple query to get your player_id

SELECT

playername,
player_id

FROM

players

WHERE

playername = '<your name here>'

this will return a list of all the player_id associated w/ you - number will depend on how many sites you play at.

make a note of 'em, then amend the WHERE clause in your function to

WHERE

tourneyhands>$2 AND
player_id NOT IN (<copy the list of ids here, separated by commas>)$BODY$

EDIT: I'm MSSQL not Oracle, so you may have to amend for dialects. Same disclaimer as for Nak's code ;)

Cool, thanks JJ!

Quote:

---

Originally Posted by Nakamura

As for the history it appears somewhat complicated but it used to belong to Sun MicroSystems and then Oracle bought Sun MicroSystems a short while ago. From Wiki...

---

Live and learn.

Hoping to make some time to look at this soon. Don't stop posting.