StatsLab SQL Utilities for OOTPX

[fhomess]

Originally Posted by dangarion

Ok just going to free flow some observations and some suggestions. Just to give you a background I used to run BOSI for the RWBL as well, so I have used tools like this in the past.

It would be nice if the tool would show the SQL import progress. Especially since the import of a large league (that has the full MLB history data) takes upwards of 10 minutes.

Also if there is an issue with a SQL import is there a way to revert and do it again?

I'm also having some type of issue with the financials since my SQL Log keeps showing this for all the teams in the league.

LOADING: team_last_financials.mysql.sql ... 237
   insert into `team_last_financials` VALUES (31, 41306087, 29565975, 16372701, 0, 0, 0, 10000000, 79733000, 7200000, 0, 2630961, 0, 0, 0, 0.0000, 0, 0, 91200000, 0, 0, ,1,1);
 -> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1)' at line 1
   insert into `team_last_financials` VALUES (32, 44920259, 34783500, 21813997, 0, 0, 0, 10000000, 95662500, 7930000, 0, 2861163, 0, 0, 0, 0.0000, 0, 0, 108200000, 0, 0, ,1,1);
 -> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1)' at line 1
   insert into `team_last_financials` VALUES (33, 41306087, 29565975, 18711658, 0, 0, 0, 10000000, 81197000, 8970000, 0, 2630961, 0, 0, 0, 0.0000, 0, 0, 94500000, 0, 0, ,1,1);
 -> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1)' at line 1
   insert into `team_last_financials` VALUES (34, 51794560, 45218550, 25992582, 0, 0, 0, 10000000, 123155308, 7280000, 0, 2999106, 0, 0, 0, 0.0000, 0, 0, 135500000, 0, 0, ,1,1);
 -> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1)' at line 1
   insert into `team_last_financials` VALUES (35, 44920259, 34783500, 22734952, 0, 0, 0, 10000000, 97257500, 8000000, 0, 2861163, 0, 0, 0, 0.0000, 0, 0, 109900000, 0, 0, ,1,1);
 -> You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,1)' at line 1
   insert into `team_last_financials` VALUES (36, 57363462, 45218550, 31280296, 0, 0, 0, 10000000, 136253500, 7590000, 0, 3321567, 0, 0, 0, 0.0000, 0, 0, 149200000, 0, 0, ,1,1);

Yeah, large loads can take a while due to the size of the files and sheer quantity of data to import. There's a couple of things that can be done. First, don't load SQL files you don't need. For example, the team_last_financials.mysql.sql file that you're getting the above error in isn't used by the utilities anywhere. There are actually a handful of other SQL files that will throw errors when imported by the utility that you don't need, and these will slow down the load. Second, I've uploaded a modification to the utilties that will allow you to load files individually. There's some detail in the documentation and a new link on the main admin page to another page where you can load individual files. Some folks have had issues with the load script timing out if the file is too big and their PHP install isn't configured to accept the script's request for additional time. This individual page load will hopefully help with that.

There isn't a way to revert the SQL and do it again because of the way OOTP has decided to provide the data. Their method is to delete the whole table, then rebuild it completely, so once it's deleted it's lost.

Originally Posted by dangarion

I know there are team filters but it would also be nice to see all the data without a filter, and even positional filters in the areas that it's possible (any area that has the drop downs to only via each team, career metrics, development, ratings).

I know you did it for security reasons, but all the blank fields on the admin screen can be sort of confusing to people, especially to those that are used to seeing information in fields like that listed in other web scripts (phpBB, etc). Maybe include some text that they are blank for security reasons, still difficult to tell if the settings are correct though, not sure what you could do.

Talking about security, I've been forced to move our league exports to a separate UNIX user on the server, and unfortunately that makes it impossible for the script to see the folders that user has access to (the commish user is the web access account, and the export user only has access to it's own folders, unsure if there is a way to work around that with the export tracker or not, don't worry about it, just thought I would mention it.) I'm guessing there wouldn't be a way for me to resolve this unless if I did it on my own by like running a cron job or something, but even that would be a PITA.

Overall this tool is awesome! Really like what I'm seeing, and love that it was integrated in with the existing league reports.

I'll have to think about some of these and see what I might do.

Originally Posted by dangarion

One more thing. I've also noticed that sometimes after it has appeared to run it's course of importing, if I attempt to go back to it, it hangs for quite sometime. I'm wondering if that is because it's actually still importing (this is why showing the activity might be useful. I wish the SQL files were so big, this is a pretty big import compared to what I'm used to.

I tried to petition the dev team to provide incremental SQL data dumps, like maybe for just the past season, but to no avail. Perhaps as this tool gets more widespread use, they'll see the benefits of making that available. There's really no need to include 90 years of history in the SQL dump every time.

Originally Posted by dangarion

Ah i got another one. If you go to career metrics and look at the team index. The W-L record for all the teams in 2008 is messed up.

Minnesota Twins History - Index - StatsLab for OOTPX

I'll send you a PM on this one.

Originally Posted by dangarion

Last one! I promise!

I didn't read anywhere in the manual about having to go to the Development Tracker admin and checking boxes to get the development to actually show. That explains why I wasn't able to see development.

Added to the documentation.

[dangarion]

Awesome! Thanks. If you need help testing things out I can always throw the data on another domain so we can look at it.

[dangarion]

How are you coming about the ratings? We've been seeing some issues with the ratings being off from what StatsLab shows and in game shows (we don't use coaches and scouts so we should only see true ratings).

We are using 1-10 for all our ratings we have a guy that shows in statslab to have Stuff Talent of 8, but in game it shows 9, and then in the CSV it shows 141.

Is this right?

[Neags23]

Originally Posted by dangarion

How are you coming about the ratings? We've been seeing some issues with the ratings being off from what StatsLab shows and in game shows (we don't use coaches and scouts so we should only see true ratings).

We are using 1-10 for all our ratings we have a guy that shows in statslab to have Stuff Talent of 8, but in game it shows 9, and then in the CSV it shows 141.

Is this right?

Getch just brought to my attention the fact that the ratings that show up on a player's profile and ratings exported from the game and in the editor are actually different. The "real" ratings that get exported go through a formula that takes GB % into effect (and possibly something else) to get the rating that shows up in the player's profile.

At this point, we don't know what that formula is or if we ever will.

[fhomess]

StatsLab merely divides the 1-200 actual range by the scale that you're using. As neags says, there appear to be some cases where OOTP reports it slightly different than what dividing up the scale evenly would give based on some additional ratings. If anyone gets the exact formula from Markus, I'll happily incorporate it.

[Killebrew]

Hmmm, I noticed some HTML/game ratings were way off from the statslab display, for example a 10/10 in statslab shows up as 7/10 in the game and the game HTML.

Originally Posted by fhomess

StatsLab merely divides the 1-200 actual range by the scale that you're using. As neags says, there appear to be some cases where OOTP reports it slightly different than what dividing up the scale evenly would give based on some additional ratings. If anyone gets the exact formula from Markus, I'll happily incorporate it.

This is pretty interesting. The CDL used the Getch utility last season and I am nearly certain it worked that same way, just dividing the actual rating to our ratings scale.

If that understanding is accurate doesn't that mean this is a significant change for the way the game displays player ratings?

[Killebrew]

Originally Posted by fhomess

StatsLab merely divides the 1-200 actual range....

Hey wait a sec, isn't the actual range is 1-250? D'oh, I hope that is a typo otherwise this is pretty significant bug in StatsLab:P.

[Killebrew]

....

[udbacker58]

I've noticed that for some reason the Player pictures don't seem to show up in the Career Metrics for the X version, but it seems to be ok for the ones for v9.

Player Report for #47 Dion James - StatsLab for OOTPX

Player Report for #26 Bud Hoffmann - StatsLab for OOTP9

[Neags23]

Originally Posted by Killebrew

This is pretty interesting. The CDL used the Getch utility last season and I am nearly certain it worked that same way, just dividing the actual rating to our ratings scale.

If that understanding is accurate doesn't that mean this is a significant change for the way the game displays player ratings?

Yes. My understanding is that Stuff and Movement are now a conglomeration of pitch ratings and GB % and possibly other things. Very different from past versions.

[Tony M]

Originally Posted by Neags23

Yes. My understanding is that Stuff and Movement are now a conglomeration of pitch ratings and GB % and possibly other things. Very different from past versions.

And each pitch also has an effect on the L-R split for pitching as well.

[Killebrew]

Hmm, so what is the point of a dev changes app if the displayed dev does not match the game or the game HTML? You can tell there was some kind of ratings/talent movement, but that benefit seems to be nullified by the confusing numbers displayed.

[fhomess]

The issues people are seeing with ratings are due to the fact that the game now combines several internal ratings to determine the displayed ratings. I wasn't aware of this when I converted the OOTP9 version of these utils to OOTPX. If you review the threads in this forum for neags' dev tracker and Getch's OOTP OU, you'll see that they have the same problems.

Your options are the following:
1. Install and use given the understanding that the ratings reported are based on the internal ratings.
2. Do not use the ratings or development tracker until it's addressed.

Either way, pestering Markus and Andreas to either release the formula or export the reported ratings in the SQL dump would be helpful.

[Killebrew]

Originally Posted by fhomess

The issues people are seeing with ratings are due to the fact that the game now combines several internal ratings to determine the displayed ratings. I wasn't aware of this when I converted the OOTP9 version of these utils to OOTPX. If you review the threads in this forum for neags' dev tracker and Getch's OOTP OU, you'll see that they have the same problems.

Your options are the following:
1. Install and use given the understanding that the ratings reported are based on the internal ratings.
2. Do not use the ratings or development tracker until it's addressed.

Either way, pestering Markus and Andreas to either release the formula or export the reported ratings in the SQL dump would be helpful.

Makes sense. Thanks, and sorry for pestering you about this.

[fhomess]

Updated the download file with some fixes and enhancements:
1. Player pictures should appear correctly
2. There's now a sql_file_load.php page where you can load SQL files individually if your load button is causing timeout errors.
3. Leaderboards page now has a bottom 100 option so you can see who was the biggest waste of roster space in your league.
4. Leaderboards page now has additional draft round and draft team options.
5. Fixed date issue with dates prior to 1969 and post 2039 (php limitation).
6. Fixed a team record issue on Team Index page related to data from certain older versions of OOTP.
7. Other miscelaneous fixes.

[udbacker58]

Originally Posted by fhomess

Updated the download file with some fixes and enhancements:
1. Player pictures should appear correctly
2. There's now a sql_file_load.php page where you can load SQL files individually if your load button is causing timeout errors.
3. Leaderboards page now has a bottom 100 option so you can see who was the biggest waste of roster space in your league.
4. Leaderboards page now has additional draft round and draft team options.
5. Fixed date issue with dates prior to 1969 and post 2039 (php limitation).
6. Fixed a team record issue on Team Index page related to data from certain older versions of OOTP.
7. Other miscelaneous fixes.

Sweet.

[udbacker58]

Just messing around with the Awards utility for the v10 utilities, when I sort by a category and then try to select/add to list, it doesn't work. I dont' get an error, it just doesn't add the player(s) to the list.

[udbacker58]

Originally Posted by udbacker58

I've noticed that for some reason the Player pictures don't seem to show up in the Career Metrics for the X version, but it seems to be ok for the ones for v9.

Player Report for #47 Dion James - StatsLab for OOTPX

Player Report for #26 Bud Hoffmann - StatsLab for OOTP9

Ok, maybe it's just me. When I set up the utilities for my v9 league, I don't have player pics there either.

[huskerdan25]

Originally Posted by fhomess

Updated the download file with some fixes and enhancements:
1. Player pictures should appear correctly
2. There's now a sql_file_load.php page where you can load SQL files individually if your load button is causing timeout errors.
3. Leaderboards page now has a bottom 100 option so you can see who was the biggest waste of roster space in your league.
4. Leaderboards page now has additional draft round and draft team options.
5. Fixed date issue with dates prior to 1969 and post 2039 (php limitation).
6. Fixed a team record issue on Team Index page related to data from certain older versions of OOTP.
7. Other miscelaneous fixes.

Is there a simple update process or do we need to remove our current installations and re-install completely? Thanks!

Dan

[MrWorkrate]

Originally Posted by fhomess

Updated the download file with some fixes and enhancements:
3. Leaderboards page now has a bottom 100 option so you can see who was the biggest waste of roster space in your league.
4. Leaderboards page now has additional draft round and draft team options.

I'm not seeing these - did they make it in? leaderHist.php file is showing a mod date of 7/3/09