Hy all!! Sorry in advance for my bad bad English.
Tonight I did some work on HEM to realize a script that can create an alias for all players in our database, except for Hero. It can be very useful to analyze population tendency, but I have to work more on this because it can be very hard for our machine to analyze this huge sample of hands. So, every help his appreciated

In order to be able to do that, first of all open your HEM and create an alias clicking on Options>Player aliases>Add alias. In the name select whatever you want; then add just one player to the alias (except for a hero nicks obv) and make sure you remember his name. For semplicity, take a name with no special characters. Assume at this point you have add the nick 'Villain' to the alias 'Global'.

Now, you have to open your pgAdmin by Start>All programms>Postgresql 9.0> pgAdmin III.
NB:The directory can change based on your actual version of postgresql
Connect to your database double-clicking on PostgreSQL 9.0. Select your database of interest (for me his SNG HU), then click on Tools>Query tools (or something like that, I don't know the exact traduction cause I use an Italian version).

The first thing to do is to extract the id for our 'Global' alias. To do that, insert this code
You have to replace string Villain with the name of the player added to the alias

Then, click 'F5' to start the query. At this point, you have a table in the lower part of the screen. The first column of this table should be 'aliasplayer_id'. Take this number, it will be useful soon. Assume for simplicity this number is 1234.

At this point delete all the code you have insert and digit this new code
You have to replace 1234 in the second line with the exact id of the alias extracted with the first code
Again, click 'F5' to start the query.

At this point you have to restart your HEM to add the changes. To make sure all it's ok, click again on Options> Player aliases, select 'Global' and verify that there are enough nicknames associated to the alias.

Now, you're able to use this alias... if your machine is strong enough, obv

To reduce the size of the alias it could be possible to add to it only players that play hu at a certain buy-in level or from a certain period. But now, I still don't know how

Awesome work. This will come in handy for all the morons that use HEM

/end troll

Dave and I keep meaning to do something like this for PT4, but I don't know **** about SQL and Dave tells me that it doesn't use 'real' SQL or something.

There's some way of extracting all of villain's data and renaming every villain to a common name. Then you can use the data like you would you're own. In fact I would say it's may similar to what you have done, I don't know exactly what the code means. Maybe if Dave comes along he can explain it

But seriously, thanks.

Yes, I still haven't had time to test PT4, but it's in my "to do" things
But i think that PT4 also uses table like these and there aren't huge difference between this two databases.
I don't have many experience with postgresql, I usually use mySQL, but the two languages are similar. However, maybe there is someone more expert than me that can optimize the queries or the entire database to increase the speed (I have to wait also 2 minutes for a simple filter on my database from HEM).
So, more than an end point, this is a starting point to do some better works on our databases
I hope this is helpful for someone.

Cool stuff man! I think you include other aliases as well as Hero when you insert the players.

First, let's create the alias manually instead of using HEM:
This will return the aliasplayer_id that was just created.


Second query:
Replacing 1234 with the value from the first query and Screenname with your SN. If you have several screen names then just add another condition; it's not perfect but probably good enough, unless someone stole your SN and you played them.

We can limit the number of players in several ways:

1. Using a LIMIT 1000 for example in the INSERT INTO command.

2. Importing players who have played tournaments by adding... AND P1.tourneyhands > 0

3. By filtering by site... AND P1.site_id = 2 (if 2 is PokerStars on your computer)

4. By importing only recent players

5. Filtering by buy-in as you said

Would be cool if we could make it a function or use variables. My Google searches weren't of much help. I'm pretty much in the same boat as you, I've never worked with Postgres.


Edit: wow, I have a 14% ROI this month, Population has -23%. Rake must be brutal when you suck!

Nice Quimp, the query worked for me that I have a global alias for all my nicknames. But if you don't have one yes, it includes even your nickname in the alias, so your query is way better!! Tx a lot.

For the first query, I didn't know how to create a global alias by postgresql. I mean, when I create one, it didn't show the name in my HEM. Now the trick is revealed

Anyway, it shouldn't be too difficult to make a function; tomorrow I work on this. My main question are:
1) With mySQL, when you invoke the INSERT, you can also choice an option on ON DUPLICATED KEY; in postgresql, it's impossible. So, how it respond when we try to insert the same player in the global alias?
2) It's possible to select only players that played hu? A bias selection it's way too efficient, but I think there's no code for HU format on postgresql table.
3) There's a way to optimize the alias? I mean, after the two queries, I made a VACUUM and a REINDEX to the database, but it's still too slow.
I think that the problem is that every time it has to select the data of all players; it could be more more efficient to create a single player with all the datas rather than an alias, but I don't know how and I think it's too difficult.

EDIT: yes if someone has your nick it could be a problem. So the only way is to include an alias as 'HERO' and insert into that all hero nicks, then proceed with my query.

This is for HU <= 15$, mostly reg speeds.



There's a cool feature in HEM that lets you export the stats to a CSV file, meaning we could merge stats and weight them based on sample size.

1. You don't need to worry about it because of the WHERE clause...

So if you run the script twice, the second time 0 rows will be affected. However, I just realized there's something missing here. We should add a WHERE A2.aliasplayer_id = 1234 in the embedded SELECT to make sure we don't ignore players who may be in another alias (ex. a fish or reg list).

2. It's not possible to select only players that played HUSNGs because the alias works on a player basis. In HEM, you can select the number of players before running a report (you can also select a buy in range and game speed).

3. It would probably be faster yes. Probably complex and annoying to work through and maintain though. I think the best way to make it faster is to limit the number of players included. Do you have an idea how to randomize sampling?

One way I have in mind is to create a temporary table with an extra column with a random value ranging from 1 - 10, then select only 1's (10%) or 1-2's (20%), etc.

HEM loads the aliases when the program launches. Simply close it and reopen it to see the new alias.

I approve of this thread nice work everyone!

[QUOTE=Quimp;33360848]1. My solution is this and i think it's a good compromise.
a. create a new alias, naming it 'Hero' and adding into it all hero nicknames.
b. create a new alias, naming it 'All'
c. adding all the players to 'All' except those in the alias 'Hero' and those yet added
It isn't difficult to do to something like that, except for the italic part, on which i have to think about.

2. Yes, I know, but at the same time if you make the selection before the insert the result is less tuple to insert and more efficient database.

3. I don't know if a randomization can be helpul. I mean, I have 11k players in my db and in certain spots I have a sample of only 400-500 hands.

Yes, i wanted to say that before your post I didn't know how to add a new alias by postgresql and for this reason i suggested to create this by HEM

Assuming that:
a. we create an alias for hero nicknames naming it 'Hero'. It's simple to do that by HEM.
b. we create a new alias player with Quimp's query and that its id is 11084
c. the query to insert all the players except hero and those yet inserted is this

brilliant!

NERDS!!!

I just bought a shiny new SSD so maybe that things like this will go quickly for me.

Will this sort of code work on the PT4 DB in PostgreSQL?

I don't know if the tables used by PT4 are the same, and surely they have different names.
Also, I don't know if there's the possibility to make an alias in PT4.
In these days I'll download it and try to make something similar.
Ah and yes, an SSD is surely better and faster than my HDD

Where do you insert this code? in the SQL editor? and if yes what do you do with the stuffs already in there? delete it? sorry noob w postgreSQL

Hey genher, what's up?
On the new editor, insert the code and just press F5 to run it
Anyway, look at post #11, it's way more accurate than my first post
Let me know if you have some problem.

am I right in assuming that the bolded part of the code are the columns of the player table?

if so, in HEM2 the columns are slightly different : player_id, playername, pokersire_id, cashhands, tourneyhands, optimizationstatus, icon

so I thought of adapting the code to but obviously the player_id returns 0
or in this case can I just insert any player id numer ?

Help anyone?

Don't specify player_id, it will be automatically increamented and returned. Also, pokersiRe_id should be (?) pokersite_id? If so, make its value -1 to create an alias.

You'll have to look at the table's structure to see if optimizationstatus and icon are integers.

Yes, these are the columns of the table but no, you don't have to insert all the right values. I mean, player_id is derived automatically, so you don't have to insert it.
Another thing to do is to check if HEM 2 has the same way to memoryze aliases. In HEM1 it differenciates normal players from aliases by the site_id column: the value -1 indicates that the player is an alias.

So, first of all to check this create an alias with HEM2 and naming it 'proof'. Then open pgadmin3 and run the query
and checks that in the pokersite_id there is the -1 value. If it's true, run the query
Then run the point c in post #11, replacing 1234 with the number you have obtained in the previous query.

... and if they have to be not null, but i don't think.

I just downloaded PT4. There's a way to create an alias with it?

hem and PT databases are really quite different!

but yes, those are the columns of the named table "players", it's INSERT INTO tablename (columnname_A, columnname_B) VALUES (what_to_put_in_col_A, what_to_put_in_col_B);

The easiest way to figure out what are the relevant tables/columns is to enable full logging in postgresql (it's possibly on already), do whatever it is you're interested in (e.g. create a simple alias), open up the postgresql log file and see what lines of SQL got executed.

Or in PGAdmin just expand the tree on the left schema / public / tables iirc and examine them see if you can make sense of how it's storing the information.

ok

This works fine

so I adapted your code to and it does work and return a player_id number. So so far so good

BUT when I run the next query (c in post 11) I get ERROR: column p1.site_id does not exist!? what's p1.site_id ??

How it's possible to enable full logging? Where is the log file?

Yes, the main columns are there. But I don't know yet if there's a way to create an alias on PT4 (it's still importing hands from my previous database :S)

p1 is a "variable" equal to the table players. So, it's p1.pokersite_id

Can you post also the aliases columns?
SELECT * FROM aliases

Well, I think in PT4 there's a way to add only players who have played heads up. But still i don't know if there's a way to create an alias cause I can't be able to use the program until he imports all hands.
This is the code to show only hu players