Home | Webstore
Latest News: - OOTP 15: Update #6 Released! - OOTP 15 Released! - FHM 2014: Version 1.6.19 Available! - iOOTP Baseball 2014 for iOS Available NOW! - Beyond the Sideline Football Announced! - Title Bout Championship Boxing 2.5 released!

OOTP 15 World Series Special: 50% Off!

  

Go Back   OOTP Developments Forums > Out of the Park Baseball 15 > OOTP Mods > OOTP Mods - Database Tools

OOTP Mods - Database Tools Do you need to take a dump? SQL gurus welcome

Reply
 
LinkBack Thread Tools
Old 07-08-2009, 03:36 PM   #41 (permalink)
Hall Of Famer
 
fhomess's Avatar
 
Join Date: Nov 2002
Posts: 3,232
Thanks: 106
Thanked 773x in 333 posts
Quote:
Originally Posted by dangarion View Post
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.
Code:
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.

Quote:
Originally Posted by dangarion View Post
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.

Quote:
Originally Posted by dangarion View Post
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.

Quote:
Originally Posted by dangarion View Post
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.

Quote:
Originally Posted by dangarion View Post
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.
__________________
StatsLab- PHP/MySQL based utilities for Online Leagues
Other Mods:
StatsLab11
19th Century: Schedules, FaceGen
BBCards: Full list of known templates
- Fictional Cards: 1996 fhomess, 2005 fhomess
FaceGen: 1960-Pres MLB, 32 Colleges, Backgrounds

PEBA - Connecticut Nutmeggers
fhomess is offline   Reply With Quote
Old 07-08-2009, 03:49 PM   #42 (permalink)
All Star Reserve
 
dangarion's Avatar
 
Join Date: Feb 2003
Location: Orange, CA
Posts: 569
Thanks: 16
Thanked 5x in 5 posts
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
GM - Los Angeles Dodgers The Peanuts and Cracker Jack Baseball League
PCJBL!
dangarion is offline   Reply With Quote
Old 07-11-2009, 06:06 PM   #43 (permalink)
All Star Reserve
 
dangarion's Avatar
 
Join Date: Feb 2003
Location: Orange, CA
Posts: 569
Thanks: 16
Thanked 5x in 5 posts
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?
__________________
DanGarion
GM - Los Angeles Dodgers The Peanuts and Cracker Jack Baseball League
PCJBL!
dangarion is offline   Reply With Quote
Old 07-12-2009, 12:19 AM   #44 (permalink)
All Star Starter
 
Neags23's Avatar
 
Join Date: Apr 2006
Posts: 1,507
Thanks: 23
Thanked 29x in 18 posts
Quote:
Originally Posted by dangarion View Post
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.
__________________
Former GM Washburn Sea Wolves Dog Days Baseball
1981 & 1986 Kennel Cup Champions!
Neags23 is offline   Reply With Quote
Old 07-12-2009, 02:39 AM   #45 (permalink)
Hall Of Famer
 
fhomess's Avatar
 
Join Date: Nov 2002
Posts: 3,232
Thanks: 106
Thanked 773x in 333 posts
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.
__________________
StatsLab- PHP/MySQL based utilities for Online Leagues
Other Mods:
StatsLab11
19th Century: Schedules, FaceGen
BBCards: Full list of known templates
- Fictional Cards: 1996 fhomess, 2005 fhomess
FaceGen: 1960-Pres MLB, 32 Colleges, Backgrounds

PEBA - Connecticut Nutmeggers
fhomess is offline   Reply With Quote
Old 07-12-2009, 05:56 PM   #46 (permalink)
Hall Of Famer
 
Killebrew's Avatar
 
Join Date: Dec 2001
Posts: 3,321
Thanks: 111
Thanked 45x in 23 posts
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.

Quote:
Originally Posted by fhomess View Post
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?

Last edited by Killebrew; 07-12-2009 at 06:27 PM.
Killebrew is offline   Reply With Quote
Old 07-12-2009, 06:26 PM   #47 (permalink)
Hall Of Famer
 
Killebrew's Avatar
 
Join Date: Dec 2001
Posts: 3,321
Thanks: 111
Thanked 45x in 23 posts
Quote:
Originally Posted by fhomess View Post
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 is offline   Reply With Quote
Old 07-13-2009, 09:16 PM   #48 (permalink)
Hall Of Famer
 
Killebrew's Avatar
 
Join Date: Dec 2001
Posts: 3,321
Thanks: 111
Thanked 45x in 23 posts
....

Last edited by Killebrew; 07-14-2009 at 11:21 AM. Reason: Wrong guess....
Killebrew is offline   Reply With Quote
Old 07-14-2009, 01:48 AM   #49 (permalink)
All Star Starter
 
udbacker58's Avatar
 
Join Date: Apr 2007
Posts: 1,039
Thanks: 68
Thanked 66x in 47 posts
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
__________________
USBA (Commish): New Orleans Ragin' Cajuns
BLSN: Atlanta Braves: BLB (Commish) - BLHB

Interested in a MLB league? Apply to join the BLB!....
Fictional more your taste? Send me a PM and Apply to join the USBA!
udbacker58 is offline   Reply With Quote
Old 07-14-2009, 10:08 AM   #50 (permalink)
All Star Starter
 
Neags23's Avatar
 
Join Date: Apr 2006
Posts: 1,507
Thanks: 23
Thanked 29x in 18 posts
Quote:
Originally Posted by Killebrew View Post
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.
__________________
Former GM Washburn Sea Wolves Dog Days Baseball
1981 & 1986 Kennel Cup Champions!
Neags23 is offline   Reply With Quote
Old 07-14-2009, 10:28 AM   #51 (permalink)
Global Moderator
 
Tony M's Avatar
 
Join Date: Feb 2006
Location: Here
Posts: 6,134
Blog Entries: 3
Thanks: 40
Thanked 312x in 182 posts
Quote:
Originally Posted by Neags23 View Post
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.
__________________
This signature is intentionally blank
Tony M is offline   Reply With Quote
Old 07-14-2009, 12:59 PM   #52 (permalink)
Hall Of Famer
 
Killebrew's Avatar
 
Join Date: Dec 2001
Posts: 3,321
Thanks: 111
Thanked 45x in 23 posts
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.
Killebrew is offline   Reply With Quote
Old 07-14-2009, 01:19 PM   #53 (permalink)
Hall Of Famer
 
fhomess's Avatar
 
Join Date: Nov 2002
Posts: 3,232
Thanks: 106
Thanked 773x in 333 posts
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.
__________________
StatsLab- PHP/MySQL based utilities for Online Leagues
Other Mods:
StatsLab11
19th Century: Schedules, FaceGen
BBCards: Full list of known templates
- Fictional Cards: 1996 fhomess, 2005 fhomess
FaceGen: 1960-Pres MLB, 32 Colleges, Backgrounds

PEBA - Connecticut Nutmeggers
fhomess is offline   Reply With Quote
Old 07-14-2009, 02:25 PM   #54 (permalink)
Hall Of Famer
 
Killebrew's Avatar
 
Join Date: Dec 2001
Posts: 3,321
Thanks: 111
Thanked 45x in 23 posts
Quote:
Originally Posted by fhomess View Post
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.
Killebrew is offline   Reply With Quote
Old 07-14-2009, 04:08 PM   #55 (permalink)
Hall Of Famer
 
fhomess's Avatar
 
Join Date: Nov 2002
Posts: 3,232
Thanks: 106
Thanked 773x in 333 posts
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.
__________________
StatsLab- PHP/MySQL based utilities for Online Leagues
Other Mods:
StatsLab11
19th Century: Schedules, FaceGen
BBCards: Full list of known templates
- Fictional Cards: 1996 fhomess, 2005 fhomess
FaceGen: 1960-Pres MLB, 32 Colleges, Backgrounds

PEBA - Connecticut Nutmeggers
fhomess is offline   Reply With Quote
Old 07-14-2009, 08:47 PM   #56 (permalink)
All Star Starter
 
udbacker58's Avatar
 
Join Date: Apr 2007
Posts: 1,039
Thanks: 68
Thanked 66x in 47 posts
Quote:
Originally Posted by fhomess View Post
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.
__________________
USBA (Commish): New Orleans Ragin' Cajuns
BLSN: Atlanta Braves: BLB (Commish) - BLHB

Interested in a MLB league? Apply to join the BLB!....
Fictional more your taste? Send me a PM and Apply to join the USBA!
udbacker58 is offline   Reply With Quote
Old 07-14-2009, 11:44 PM   #57 (permalink)
All Star Starter
 
udbacker58's Avatar
 
Join Date: Apr 2007
Posts: 1,039
Thanks: 68
Thanked 66x in 47 posts
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.
__________________
USBA (Commish): New Orleans Ragin' Cajuns
BLSN: Atlanta Braves: BLB (Commish) - BLHB

Interested in a MLB league? Apply to join the BLB!....
Fictional more your taste? Send me a PM and Apply to join the USBA!
udbacker58 is offline   Reply With Quote
Old 07-15-2009, 12:02 AM   #58 (permalink)
All Star Starter
 
udbacker58's Avatar
 
Join Date: Apr 2007
Posts: 1,039
Thanks: 68
Thanked 66x in 47 posts
Quote:
Originally Posted by udbacker58 View Post
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.
__________________
USBA (Commish): New Orleans Ragin' Cajuns
BLSN: Atlanta Braves: BLB (Commish) - BLHB

Interested in a MLB league? Apply to join the BLB!....
Fictional more your taste? Send me a PM and Apply to join the USBA!
udbacker58 is offline   Reply With Quote
Old 07-15-2009, 01:44 PM   #59 (permalink)
Major Leagues
 
huskerdan25's Avatar
 
Join Date: Mar 2002
Location: Colorado
Posts: 333
Thanks: 5
Thanked 1x in 1 post
Quote:
Originally Posted by fhomess View Post
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
huskerdan25 is offline   Reply With Quote
Old 07-15-2009, 05:11 PM   #60 (permalink)
All Star Starter
 
MrWorkrate's Avatar
 
Join Date: Mar 2002
Location: Raleigh, NC
Posts: 1,248
Thanks: 9
Thanked 3x in 3 posts
Quote:
Originally Posted by fhomess View Post
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
__________________
I used to come here a lot. Now, not so much.
MrWorkrate is offline   Reply With Quote
Reply

Bookmarks

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT -4. The time now is 07:33 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.
Search Engine Friendly URLs by vBSEO 3.6.1
Copyright © 2013 Out of the Park Developments