PDA

View Full Version : Purging tournament players with Hands=0.



Gato
05-14-2010, 04:31 PM
I suppose this is an issue for all the tournament players. My database shows more than 500,000 players, but the ones that have played at least one hand in my tables are no more than 16,000.

The rest come from the tournament history files sent by the poker sites. Is there a way to purge them from the database to clean it up?

Regards.

a.k.a.Also
05-14-2010, 04:43 PM
*Disclaimer* Thread gets a little off topic but afaik this can't be done yet.

http://forums.holdemmanager.com/manager-general/29682-general-database-query.html

Gato
05-16-2010, 05:11 PM
*Disclaimer* Thread gets a little off topic but afaik this can't be done yet.

http://forums.holdemmanager.com/manager-general/29682-general-database-query.html

OK, thanks. That thread is funny thu.

Sarek
05-17-2010, 07:26 AM
There IS a solution, but it needs some manipulations with database itself. And because of this you need to backup DB before attempting (File - backup).
If you are not afraid of SQL-statements, try this:
1) Start -> Programs -> PostgreSQL 8.x -> pgAdmin III
2) Dbl-click on server name and connect to (providing correct password)
3) Find there your database in list
4) open this: Schemas -> public -> Tables -> players
5) Right-click on table Players and select View data - View filtered rows
6) Enter this filter text:
cashhands < 1 AND tourneyhands < 1
7) After some time (depends of DB size and PC's speed) you will have list of players with zero hands at all (no cash + no trny). Select them all: left click on first row header, then go to last row, press Shirt and left click too.
Then press Del on keyboard (or select menu item Edit - Delete) and confirm.
It can take some time - mission completed.
8) Make Vacuum (full) for this database after deletion

Gato
05-17-2010, 03:31 PM
There IS a solution, but it needs some manipulations with database itself. (...)


In my OP I asked if there is a way to do precisely this. You have answered my question. I guess it's no big deal for the developers to include a menu item in the "Players" part in the sidebar to do exactly this with a simple click for non-SQL savvy users -the great majority I suppose-

Thx for your answer, I will try the procedure you suggest.

Best.

Sarek
05-17-2010, 03:53 PM
there are many things that CAN be implemented, the problem is in resources and priorities
you are free to use suggestion forum - but read sticker there first

Gato
05-17-2010, 04:29 PM
Hello. I tried Sarek's procedure.

I assume that the "press shift and left click" part of the procedure is to enable an append blank sort of row. But when it comes to the point of deleting filtered players, the delete option is not available.

Below appear the filtered players with zero hands, the Primary Key column is marked, the last row is unmarked BUT the delete option is unabled.

http://i5.photobucket.com/albums/y196/Gato_Vago/Poker/DB.gif

Any help here will be much appreciated.

Regards.

Sarek
05-17-2010, 04:35 PM
you have to select entire rows, not columns
by clicking on its numbers on the left side
try to delete one - and you'll understand the idea

Gato
05-17-2010, 04:40 PM
you have to select entire rows, not columns
by clicking on its numbers on the left side
try to delete one - and you'll understand the idea

You're fast, thank you very much. Task completed. :)

Gato
05-30-2010, 12:28 AM
Just to return the favor, there is a much faster way to do this. I had to look for it since I had to repopulate my db and the 550000 player names were reloaded and I was just not in the mood to hold the shift-down keys like forever.

So, here is the method.

You go to the table named "players" the way pointed above. Right clic there.

Then -see figure below- you clic the Scripts tab and then the DELETE scripts tab.

http://i5.photobucket.com/albums/y196/Gato_Vago/Poker/deletesql.jpg

A window will appear. Instead of the default lines of code appearing there you type this two lines:



DELETE FROM players
WHERE tourneyhands = 0;

Then you press F5 and there you go.

Hope this helps.


warning by Sarek:
no, this deletes all "exclusive" cash players
to avoid, you need exactly
cashhands < 1 AND tourneyhands < 1
or =0

sidGucci
05-30-2010, 02:09 AM
when I right click on players and go to scripts....i'm not getting the option for Delete

Any suggestions?

Sarek
05-30-2010, 03:51 AM
no, this deletes all "exclusive" cash players
to avoid, you need exactly
cashhands < 1 AND tourneyhands < 1

or =0


I told how to delete on the 1st page
select rows and use menu item

Gato
05-30-2010, 12:31 PM
when I right click on players and go to scripts....i'm not getting the option for Delete

Any suggestions?

Hello. If the answer to he question in the figure below is NO -which is weird but go figure :confused:- then I guess you will have to proceed manually as per Sarek's instructions above.



http://i5.photobucket.com/albums/y196/Gato_Vago/Poker/DELETE.gif

sidGucci
05-30-2010, 05:01 PM
nope........

i only have the other 4 options.......Delete is not one of the options for some reason

lethalrose
05-30-2010, 06:17 PM
so i've only played 150 MTT's and I noticed i had 33k players..

here is how i removed them, this should work for anyone but i did it this way just for you Sid.

run this query


select playername from players where cashhands = '0' AND tourneyhands = '0'

-This will give you the names of all the players in your DB that have no hands. The number of rows returned is the number of players that will be missing once you delete them.

go into HEM and confirm the total players number MINUS what this query gave you = the number of players you should have in your db with at least 1 hand.

then, once you're sure the 1st query is giving you the names of people you dont want in your DB, do this.


DELETE from players where cashhands = '0' AND tourneyhands = '0'

after you do that you should have far less players in your DB and none of your stats shouldn't change.

do a backup before you do any of this, do 2 just incase.

it shouldnt matter but i closed HEM while i was deleting the players.

Gato
05-30-2010, 10:56 PM
Hello again sidGucci.

I was curious and make some experiment, it worked.

If you clic in say the UPDATE script tab, a query window appears with a text on it. This is a template for update a table, in this case the "players" table.

If you replace the text that appears there with this:


delete from players
where tourneyhands = 0;

And then press F5 you are done.

When finished you will see a report in the same template window telling you how many rows were deleted. Also you will be asked to save the query, say yes or no, does not matter.

Hope this helped.

Edited to add this: If you have aliases configured they will be deleted as well, so you will have to reset them.You might avoid this by adding some conditions in the "where" line but by now this is enough.

Gato
05-31-2010, 12:35 PM
Edited to add this: If you have aliases configured they will be deleted as well, so you will have to reset them.You might avoid this by adding some conditions in the "where" line but by now this is enough.


OK, here is the complete code for deleting all players with no tournament hands but the aliases.



DELETE from players
WHERE tourneyhands = 0 AND site_id > 0;

aliases are asigned site_id = -1

No big deal, hope this helps thu.